day44_Oracle学习笔记_03

十三、PL/SQL程序设计

PL/SQL(Procedure Language/Structured Query Language)
1、PL/SQL是一种高级数据库程序设计语言,专门用于在各种环境下对Oracle数据库进行访问。该语言集成于数据库服务器中,所以PL/SQL代码可以对数据进行快速高效的处理。

2、PL/SQL是对SQL语言存储过程语言的扩展,是Oracle系统的核心语言。

3、PL/SQL程序由三个块组成:声明部分、执行部分、异常处理部分。

13.1、sqldeveloper工具的使用

先去Oracle官网去下载最新版本的sqldeveloper,下载地址:https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html 得到2个zip压缩包,如下图所示:

解压缩后,找到sqldeveloper.exe点击打开即可。 新建数据库连接

就可以使用了。 如果想要连接Mysql数据库,需要进行配置:工具 --> 首选项 --> 数据库 --> 第三方 JDBC 驱动包 --> 添加条目,添加所需要的jar包。如下图所示:

13.2、小案例

小案例-回顾条件表达式:

给员工涨工资:总裁涨1000元 经理涨800元 其他涨400元
写一段java的JDBC程序,我们这里写的是伪代码,伪代码不能够执行,但是可以帮助我们分析程序执行的过程和结构。

ResultSet rs = "select empno,job from emp";
while(rs.next()) {
    int eno = rs.getInt("empno");
    String job = rs.getString("job");
    if("PRESIDENT".eauals(job)) {
        update emp sal=sal+1000 where empno=eno;
    } else if ("MANAGER".eauals(job)) {
        update emp sal=sal+800 where empno=eno;
    } else {
        update emp sal=sal+400 where empno=eno;
}

PL/SQL = Procedure Language/SQL = 过程语言/SQL   PL/SQL程序从功能上来讲,与上面JDBC的程序想要完成的功能是一样的。 学习PL/SQL程序的目的:   1、PL/SQL是Oracle对SQL语言的过程化扩展,操作效率更高。   2、PL/SQL在SQL命令语言中增加了过程处理语句(分支、循环等),使SQL语言具有过程处理能力。 我们把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,使得PL/SQL面向过程但比过程语言简单、高效、灵活和实用。   Oracle中对SQL语言的扩展叫做PL/SQL。   SQL Server中对SQL语言的扩展叫做Transact-sql。

13.3、PL/SQL程序--打印输出Hello World

示例代码如下:

SQL> --声明部分
SQL> declare
  2  --说明部分
  3  begin
  4  --程序部分
  5     dbms_output.put_line('Hello World');
  6  end;
  7  --退出编辑环境,并执行PL/SQL程序
  8  /

PL/SQL 过程已成功完成。

SQL> --默认情况下,Oracle的输出开关是关闭的。
SQL> --如果要在屏幕上输出信息,需要将 serveroutput开关打开 set serveroutput on
SQL> set serveroutput on
SQL> /
Hello World

PL/SQL 过程已成功完成。

SQL>

13.4、变量和常量说明

PL/SQL程序结构截图如下:

PL/SQL程序结构完整截图如下:

变量和常量说明:

引用型变量示例代码:

--查询员工编号为7839的姓名和薪水
set serveroutput on

declare
  --定义变量保存姓名和薪水
  --pename varchar2(20);
  --psal   number;
  --定义引用型变量保存姓名和薪水
  pename emp.ename%type;
  psal   emp.sal%type;
begin
  --得到姓名和薪水
  --在PL/SQL中,赋值方式有两种方式,一种是 :=  一种是 使用关键字into
  select ename,sal into pename,psal from emp where empno=7839;

  dbms_output.put_line(pename||'的薪水是'||psal);
end;
/

记录型变量示例代码:

--查询员工编号为7839的姓名和薪水
set serveroutput on

declare
  --定义记录型变量:代表一行
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=7839;

  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/

如何定义常量呢?

  pename emp.ename%type;
  psal   emp.sal%type;
  加一个constant,就变成常量了。
  pename constant emp.ename%type;
  psal constant emp.sal%type;

13.5、分支

if语句示例代码:

set serveroutput on
--判断用户从键盘输入的数字

--接收键盘输入
--num: 地址值,在该地址上保存了输入的值。
accept num prompt '请输入一个数字';

declare
  --定义变量保存输入的数字
  pnum number := #
begin
  if pnum = 0 then dbms_output.put_line('您输入的是0');
    elsif pnum = 1 then dbms_output.put_line('您输入的是1');
    elsif pnum = 2 then dbms_output.put_line('您输入的是2');
    else dbms_output.put_line('其他数字');
  end if;
end;
/

13.6、循环

循环语句示例代码:

--打印1~10
set serveroutput on

declare
  pnum number := 1;
begin
  loop
    --退出条件
    exit when pnum > 10;

    dbms_output.put_line(pnum);
    --加一
    pnum := pnum + 1;
  end loop;
end;
/

13.7、光标Cursor(游标)== ResultSet

示例:按员工的工种长工资,总裁涨1000元,经理涨800元,其他员工涨400元。 示例代码截图:

光标Cursor(游标)详解如下图所示:

示例代码:

--查询并打印员工的姓名和薪水
/*
1. 光标的属性:
    %isopen(光标是否打开)    
    %rowcount(光标影响的行数)
    %found(光标找到内容)      
    %notfound(光标没有找到内容)

2. Oracle中默认,一个会话中只能打开300个光标
SQL> --修改光标个数需要管理员权限
SQL> show user
USER 为 "SCOTT"
SQL> conn sys/password@192.168.56.101:1521/orcl as sysdba
已连接。
SQL> show user
USER 为 "USER"
SQL> show parameter cursor

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------
cursor_sharing                       string                           FORCE
cursor_space_for_time                boolean                          FALSE
open_cursors                         integer                          300
session_cached_cursors               integer                          20

修改: alter system set open_cursors=400;

3. (思考):上面参数 cursor_sharing 什么作用? --> 对于数据库性能优化非常有用。
      EXACT(默认值), FORCE(应急使用), SIMILAR
*/

--示例:使用光标查询员工姓名和工资,并打印
set serveroutput on

declare
  --定义一个光标
  cursor cemp is select ename,sal from emp;
  --为这个光标定义所需要用到的对应的变量
  pename emp.ename%type;
  psal   emp.sal%type;
begin
  --打开光标
  open cemp;

  loop
    --取一条记录到变量中
    fetch cemp into pename,psal;
    --退出条件
    --exit when 没有取到记录;
    exit when cemp%notfound;

    --打印
    dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;

  --关闭光标
  close cemp;
end;
/

再来给员工涨工资代码:

--示例:按员工的工种长工资,总裁涨1000元,经理涨800元,其他员工涨400元。
set serveroutput on

declare 
  --alter table "SCOTT"."EMP" rename column "JOB" to empjob
  cursor cemp is select empno,empjob from emp;
  --为这个光标定义所需要用到的对应的变量
  pempno emp.empno%type;
  pjob   emp.empjob%type;
begin

  open cemp;
  loop
    --取一条记录到变量中
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;

    --判断职位
    if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
      elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
      else update emp set sal=sal+400 where empno=pempno;
    end if;

  end loop;
  close cemp;

  --Oracle是自动开启事务的
  --Oracle默认的隔离级别是:read committed
  --why? --> ACID
  commit;

  dbms_output.put_line('涨工资完成');
end;
/

带参数的光标 示例代码如下:

--查询某个部门的员工姓名
set serveroutput on

declare
  cursor cemp(dno number) is select ename from emp where deptno=dno; --不一样的地方
  pename emp.ename%type;
begin
  open cemp(20); --不一样的地方
  loop
    fetch cemp into pename;
    exit when cemp%notfound;

    dbms_output.put_line(pename);

  end loop;
  close cemp;
end;
/

光标习题:请点击链接:XXX

13.8、例外

例外:是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。 Oracle中对异常的处理   1、系统定义的例外      No_data_found (没有找到数据)      Too_many_rows (select … into 语句中匹配多个行)      Zero_Divide (被零除)      Value_error (算术或转换错误)      Timeout_on_resource (在等待资源时发生超时) 2、用户定义的例外 演示:系统定义的例外(被0除)

--系统例外:被0除
set serveroutput on

declare
  pnum number;
begin
  pnum := 1/0;

exception
  when zero_divide then dbms_output.put_line('1:0不能做分母');
                        dbms_output.put_line('2:0不能做分母');
  when value_error then dbms_output.put_line('算术或转换错误');
  when others then dbms_output.put_line('其他例外');
end;
/

演示:用户定义的例外以及处理例外

--查询50号部门的员工姓名
set serveroutput on

declare
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;

  --自定义例外
  no_emp_found exception;
begin
  open cemp;

  --取第一条记录
  fetch cemp into pename;

  if cemp%notfound then
    --抛出例外
    raise no_emp_found;
  end if;

  --回顾
  --Java中是通过IO流来操作硬盘中的文件,
  --Java中IO最终是通过什么方式操作硬盘上的文件呢?答:通过操作系统的进程。

  --Oracle中通过内存中的实例操作硬盘中的文件,
  --而内存中实例最终是怎么操作硬盘上的文件呢?答:也是通过操作系统的进程。

  --这句执行不到,Oracle中怎么办呢?答:通过进程监视器
  --pmon: process monitor 进程监视器
  close cemp;

exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外'); 
end;
/

13.9、实例

瀑布模型图解:

实例1:统计每年入职的员工人数

/*
SQL语句:
select to_char(hiredate,'yyyy') from emp;
--> 集合 --> 光标 --> 循环 --> 退出条件:notfound

变量:
    1. 初始值  
    2. 最终怎么得到

每年入职的员工人数:
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
set serveroutput on

declare
  cursor cemp is select to_char(hiredate,'yyyy') from emp;
  phiredate varchar2(4);

  --每年入职的员工人数:
  count80 number := 0;
  count81 number := 0;
  count82 number := 0;
  count87 number := 0;
begin
  open cemp;
  loop
    --取一个员工的入职年份到变量中
    fetch cemp into phiredate;
    --退出条件:notfound
    exit when cemp%notfound;

    --判断年份
    if phiredate = '1980' then count80:=count80+1;
      elsif phiredate = '1981' then count81:=count81+1;
      elsif phiredate = '1982' then count82:=count82+1;
      else count87:=count87+1;
    end if;
  end loop;
  close cemp;

  dbms_output.put_line('Total:'||(count80+count81+count82+count87));
  dbms_output.put_line('1980年入职的有:'||count80);
  dbms_output.put_line('1981年入职的有:'||count81);
  dbms_output.put_line('1982年入职的有:'||count82);
  dbms_output.put_line('1987年入职的有:'||count87);
end;
/

实例2:为员工涨工资,从最低工资调起每人涨10%,但工资总额不能超过5万元,请计算涨工资的人数和涨工资后的工资总额,并输出涨工资人数及工资总额。

/*
SQL语句:
select empno,sal from emp order by sal;
--> 光标 --> 退出条件:1. 工资总额 > 5w   2. notfound

变量:
    1. 初始值  
    2. 最终得到

涨工资的人数: countEmp number := 0;
涨后的工资总额: salTotal number;

    方式1. select sum(sal) into salTotal from emp;
    方式2. 涨后=涨前 + sal * 0.1

    写程序的原则:能不操作数据库就不要操作数据库。

练习:人数:7   总额:50205.325
*/
set serveroutput on

declare
  cursor cemp is select empno,sal from emp order by sal;
  pempno emp.empno%type;
  psal   emp.sal%type;

  --涨工资的人数: 
  countEmp number := 0;
  --涨后的工资总额: 
  salTotal number;
begin
  --得到初始的工资总额
  select sum(sal) into salTotal from emp;

  open cemp;
  loop

    --取一个员工出来到变量中
    fetch cemp into pempno,psal;
    --1. 工资总额 > 5w
    exit when salTotal > 50000;
    --2. notfound
    exit when cemp%notfound;

    --涨工资操作
    update emp set sal=sal*1.1 where empno=pempno;
    --人数+1
    countEmp := countEmp + 1;
    --2. 涨后工资总额=涨前工资总额 + sal * 0.1
    salTotal := salTotal + psal * 0.1;

  end loop;
  close cemp;

  commit;
  dbms_output.put_line('人数:'||countEmp||'   总额:'||salTotal);
end;
/

实例3:用PL/SQL语言编写一程序,实现按部门分段(6000以上、(6000,3000)、3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)

/*
SQL语句:
部门: select deptno from dept;
部门中员工的薪水:select sal from emp where deptno=???;    问号是部门编号

变量:
    1. 初始值  
    2. 最终得到

每个段的人数:
    count1 number; 
    count2 number; 
    count3 number;
部门的工资总额: 
    salTotal number := 0;

得到部门的工资总额的方式:
    1.select sum(sal) into salTotal from emp where deptno=???;
    2.累加
*/
set serveroutput on
declare
  --部门
  cursor cdept is select deptno from dept;
  pdeptno dept.deptno%type;

  --部门中员工的薪水
  cursor cemp(dno number) is select sal from emp where deptno=dno;
  psal emp.sal%type;

  --每个段的人数:
  count1 number; 
  count2 number; 
  count3 number;
  --部门的工资总额: 
  salTotal number := 0;

begin
  open cdept;
  loop
    --取一个部门
    fetch cdept into pdeptno;
    exit when cdept%notfound;

    --初始化
    --每个段的人数
    count1:=0;
    count2:=0;
    count3:=0;
    --得到部门的工资总额
    select sum(sal) into salTotal from emp where deptno=pdeptno;

    --取部门中员工的薪水
    open cemp(pdeptno);
    loop
      --取一个员工
      fetch cemp into psal;
      exit when cemp%notfound;

      --判断
      if psal < 3000 then count1:=count1+1;
        elsif psal>=3000 and psal<6000 then count2:=count2+1;
        else count3:=count3+1;
      end if;
    end loop;
    close cemp;

    --保存结果
    insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));

  end loop;
  close cdept;

  commit;

  dbms_output.put_line('完成');
end;
/

13.10、笔试题2道

笔试1脚本.txt

create table test1
(id int primary key,
 name varchar(20),
 money int);

insert into test1 values(1,'Tom',1000);
insert into test1 values(2,'Mary',2000);
insert into test1 values(3,'Mike',3000);
insert into test1 values(4,'Jeff',4000);
commit;

示例代码如下:

SQL> select * from test1;

        ID NAME                      MONEY
---------- -------------------- ----------
         1 Tom                        1000
         2 Mary                       2000
         3 Mike                       3000
         4 Jeff                       4000

SQL> select id,name,money,(select money from test1 where id=t.id-1) money1 from test1 t;

        ID NAME                      MONEY     MONEY1
---------- -------------------- ---------- ----------
         1 Tom                        1000
         2 Mary                       2000       1000
         3 Mike                       3000       2000
         4 Jeff                       4000       3000

SQL>

笔试2脚本.txt

create table pm_ci
(ci_id varchar(20) primary key,
 stu_ids varchar(100));

insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');

create table pm_stu
(stu_id varchar(20) primary key,
 stu_name varchar(20));

insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','赵六');
commit;

示例代码如下:

SQL> select * from pm_ci;

CI_ID                STU_IDS
-------------------- ----------------------------------------------------------------------------------------------------
1                    1,2,3,4
2                    1,4

SQL> select * from pm_stu;

STU_ID               STU_NAME
-------------------- --------------------
1                    张三
2                    李四
3                    王五
4                    赵六

SQL> select c.ci_id,s.stu_name
  2  from pm_ci c,pm_stu s
  3  where instr(c.stu_ids,s.stu_id)>0;

CI_ID                STU_NAME
-------------------- --------------------
1                    张三
1                    李四
1                    王五
1                    赵六
2                    张三
2                    赵六

已选择 6 行。

SQL> select ci_id,wm_concat(stu_name) namelist
  2  from(select c.ci_id,s.stu_name
  3       from pm_ci c,pm_stu s
  4       where instr(c.stu_ids,s.stu_id)>0)
  5  group by ci_id;

CI_ID
--------------------
NAMELIST
------------------------------------------------------------------------------------------------------------------------------------------------------
1
张三,李四,王五,赵六

2
张三,赵六


SQL> --设置列的宽度
SQL> col namelist for a50
SQL> select ci_id,wm_concat(stu_name) namelist
  2  from(select c.ci_id,s.stu_name
  3       from pm_ci c,pm_stu s
  4       where instr(c.stu_ids,s.stu_id)>0)
  5  group by ci_id;

CI_ID                NAMELIST
-------------------- --------------------------------------------------
1                    张三,李四,王五,赵六
2                    张三,赵六

SQL>

十四、存储过程和存储函数

14.1、存储过程

详解如下:

存储在数据库中供所有用户程序调用的子程序(用PL/SQL写的)叫存储过程、存储函数。

创建存储过程的语法:
create [or replace] PROCEDURE 过程名(参数列表) 
as PL/SQL子程序体;

示例代码1:

--打印Hello World,不传递参数
/*
调用存储过程的方式:
1. exec sayHelloWorld();
2. begin
    sayHelloWorld();
    sayHelloWorld();
    sayHelloWorld();
   end;
   /
*/
create or replace procedure sayHelloWorld    --注意Oracle中的命名规范,但是这里为了简便,我们使用java的命名规范
as
  --说明部分
begin
   dbms_output.put_line('Hello World');
end;
/

示例代码2:

--给指定的员工涨100,并且打印涨前和涨后的工资,传递单个参数
create or replace procedure raisesalary(eno in number)    --注意:需要指明参数是输入参数,还是输出参数
as
   --定义变量保存涨前的薪水
   psal emp.sal%type;
begin
   --得到涨前的薪水
   select sal into psal from emp where empno=eno;

   --涨100
   update emp set sal=sal+100 where empno=eno;
    --要不要commit呢?答:不要。
    --原则:一般情况下,我们不在存储过程和存储函数中commit和rollback数据,应该交由调用者去做。

   dbms_output.put_line('涨前:'||psal||'   涨后:'||(psal+100));
end;
/

示例代码3:

--给指定的员工涨指定额度的工资,传递多个参数
create or replace procedure raiseSalary(eno in number,rate in number)
as
    psal emp.sal%type;
begin
    --得到涨前的薪水
    select sal into psal from emp where empno=eno;
    --涨指定额度的工资
    update emp set sal=sal*rate where empno=eno;

    dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal*rate));
end;

14.2、存储函数

详解如下:

存储函数和存储过程的结构类似,但必须有一个return子句,用于返回函数值。
函数说明要指定函数名、结果值的类型,以及参数类型等。

创建存储函数的语法:
create [or replace] FUNCTION 函数名(参数列表) 
return 函数返回值类型
as PL/SQL子程序体;

示例代码1:

--查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as
   --定义变量保存月薪和奖金
   psal emp.sal%type;
   pcomm emp.comm%type;
begin
   select sal,comm into psal,pcomm from emp where empno=eno;

   --返回年收入
   return psal*12+nvl(pcomm,0);
end;
/

14.3、存储过程和存储函数中的in和out参数

详解如下:

一般来讲,存储过程和存储函数区别在于存储函数可以有一个返回值,而存储过程没有返回值。

但存储过程和存储函数都可以通过out指定一个或多个输出参数。我们可以利用out参数,在存储过程和存储函数中实现返回多个值。
这时存储函数的功能就被存储过程取代了,那为什么还要保留存储函数呢?答:为了版本的向下兼容。

什么时候使用存储过程/存储函数呢?
原则:
    一般而言,如果只有一个返回值,就用存储函数;否则,就用存储过程。

示例代码如下:

--查询某个员工的姓名 月薪 职位
create or replace procedure queryempinfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/

思考:   1. 查询某个员工的所有信息 --> 问题:out参数太多   2. 查询某个部门中的所有员工信息 --> 问题:返回的是集合

14.4、在Java中调用存储过程和存储函数

  • 在java中想要访问数据库,首先要得到Connection对象,通过该对象得到Statement对象(接口),我们使用Statement的子接口CallableStatement。

在Java中调用存储过程和存储函数 的示例代码:

    /*
    create or replace procedure queryempinfo(eno in number,
                                             pename out varchar2,
                                             psal   out number,
                                             pjob   out varchar2)
    as
    begin
      select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
    end;
    */
    @Test
    public void testProcedure() {
        // {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call queryempinfo(?,?,?,?)}";
        Connection conn = null;
        CallableStatement call = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);

            // 对于in参数,需要赋值
            call.setInt(1, 7839);

            // 对于out参数,需要声明
            call.registerOutParameter(2, OracleTypes.VARCHAR);
            call.registerOutParameter(3, OracleTypes.NUMBER);
            call.registerOutParameter(4, OracleTypes.VARCHAR);

            // 执行存储过程
            call.execute();

            // 取出结果
            String name = call.getString(2);
            double sal = call.getDouble(3);
            String job = call.getString(4);
            System.out.println(name + "\t" + sal + "\t" + job);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, null);
        }
    }

    /*
    create or replace function queryempincome(eno in number)
    return number
    as
       --定义变量保存月薪和奖金
       psal emp.sal%type;
       pcomm emp.comm%type;
    begin
       select sal,comm into psal,pcomm from emp where empno=eno;

       --返回年收入
       return psal*12+nvl(pcomm,0);
    end;
    */
    @Test
    public void testFunction(){
        // {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{?=call queryempincome(?)}";

        Connection conn = null;
        CallableStatement call = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);

            // 第一个是out参数,需要声明
            call.registerOutParameter(1, OracleTypes.NUMBER);
            // 第二个是in参数,需要赋值
            call.setInt(2, 7839);

            // 执行存储函数
            call.execute();

            // 取出年收入
            double income = call.getDouble(1);
            System.out.println(income);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, null);
        }       
    }

14.5、在out参数中使用光标

查询某个部门中所有员工的所有信息,返回的是集合。 我们需要声明包结构和创建包体,其中包和包体也是数据库的对象。 示例代码如下:

    /* 
        1. 查询某个员工的所有信息 --> 问题:out参数太多
        2. 查询某个部门中的所有员工信息 --> 问题:返回的是集合
     */
    // 在out参数中使用光标
    // 查询某个部门中所有员工的所有信息
    /*
    --声明包结构
    create or replace
    package myPackage as
        type empcursor is ref cursor;
        procedure queryEmpList(dno in number,emplist out empcursor);
    end myPackage;

    --创建包体
    create or replace 
    package body myPackage as
        procedure queryEmpList(dno in number,emplist out empcursor) as
        begin
            open emplist for select * from emp where deptno=dno;
        end queryEmpList;
    end myPackage;
     */
    @Test
    public void testCursor(){
        String sql = "{call myPackage.queryEmpList(?,?)}";

        Connection conn = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);

            // 对于in参数,需要赋值
            call.setInt(1, 20);
            // 对于out参数 ,需要声明
            call.registerOutParameter(2, OracleTypes.CURSOR);

            // 执行存储函数
            call.execute();

            // 取出结果
            rs = ((OracleCallableStatement)call).getCursor(2);
            while (rs.next()) {
                // 取出一个员工,示例只取出了两列
                String name = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println(name + "\t" + sal);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, rs);
        }
    }

十五、触发器

详解如下:

数据库触发器是一个与表相关联的、存储的PL/SQL程序。
每当一个特定的数据操作语句(insert、update、delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。

触发器的类型:
    语句级(表级)触发器:在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多上行。
    行级触发器(for each row):触发语句作用的每一条记录都被触发。在行级触发器中使用 :old 和 :new 伪记录变量来识别值的状态。

创建触发器的语法:
create or replace trigger 触发器名
before | after
insert | update | delete [of 列名]
on 表名
[for each row [when(条件)]]     --触发器的类型
declare
begin
    ......
end;

触发器的用途:
    1. 数据确认
    2. 实施复杂的安全性检查
    3. 做审计,跟踪表上所做的数据操作等(想要做什么事,不被查到,需要关闭数据库的审计功能)
    4. 数据的备份和同步

示例1:

--每当成功插入新员工后,自动打印“成功插入了新员工”
create or replace trigger abcd
after insert
on emp
declare
begin
    dbms_output.put_line('成功插入了新员工');
end;

15.1、触发器应用一:实施复杂的安全性检查

禁止在非工作时间向数据库中插入数据

周末:to_char(sysdate,'day') in ('星期六','星期日')
上班前 下班后:to_number(tochar(sysdate,'hh24')) not between 9 and 17
------------------------------------------------------------------
create or replace trigger securityemp
before insert
on emp
declare
begin
    if to_char(sysdate,'day') in ('星期六','星期日') or
        to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
        --禁止insert
        raise_application_error(-20002,'禁止在非工作时间向数据库中插入数据');   -- -20000到-20999之间
    end if;
end;
------------------------------------------------------------------
SQL> insert into emp(empno,ename,sal,deptno) values(1001, 'tom',3000, 20);
insert into emp(empno,ename,sal,deptno) values(1001, 'tom',3000, 20)
            *
第 1 行出现错误:
ORA-20002: 禁止在非工作时间向数据库中插入数据
ORA-06512: 在 "SCOTT.SECURITYEMP", line 6
ORA-04088: 触发器 'SCOTT.SECURITYEMP' 执行过程中出错

SQL>

15.2、触发器应用二:数据确认

检查emp表中的sal的修改值不低于原值
------------------------------------------------------------------
create or replace trigger checksalary
before update 
on emp
for each row
declare
begin
    if :new.sal<:old.sal then
        raise_application_error(-20001,'涨后的工资不能少于涨前的工资。涨前:'||:old.sal||'  涨后:'||:new.sal);   -- -20000到-20999之间
    end if;
end;
------------------------------------------------------------------
测试代码:
SQL> update emp set sal=sal+1 where empno=7839;

已更新 1 行。

SQL> update emp set sal=sal-1 where empno=7839;
update emp set sal=sal-1 where empno=7839
       *
第 1 行出现错误:
ORA-20001: 涨后的工资不能少于涨前的工资。涨前:7987  涨后:7986
ORA-06512: 在 "SCOTT.CHECKSALARY", line 4
ORA-04088: 触发器 'SCOTT.CHECKSALARY' 执行过程中出错

SQL>

15.3、练习:限制每个部门只招聘10名员工,超过计划则报出错误信息

限制每个部门只招聘10名员工,超过计划则报出错误信息
------------------------------------------------------------------
create or replace trigger limitEmpCount
before insert
on emp
declare
    count10 number := 0;
    count20 number := 0;
    count30 number := 0;
begin
    select count(*) into count10 from emp where deptno=10;
    select count(*) into count20 from emp where deptno=20;
    select count(*) into count30 from emp where deptno=30;

    if count10>=10 then raise_application_error(-20005,'部门:10,员工已有'||count10||'人'); 
        elsif count20>=10 then raise_application_error(-20005,'部门:20,员工已有'||count20||'人'); 
        elsif count30>=10 then raise_application_error(-20005,'部门:30,员工已有'||count30||'人'); 
    end if;
end;
------------------------------------------------------------------
测试代码:
SQL> insert into emp(empno,ename,sal,deptno) values(1030,'tom',3000, 30);
insert into emp(empno,ename,sal,deptno) values(1030,'tom',3000, 30)
            *
第 1 行出现错误:
ORA-20005: 部门:30,员工已有10人
ORA-06512: 在 "SCOTT.LIMITEMPCOUNT", line 12
ORA-04088: 触发器 'SCOTT.LIMITEMPCOUNT' 执行过程中出错

SQL>

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python

Django---ORM操作大全

前言 Django框架功能齐全自带数据库操作功能,本文主要介绍Django的ORM框架 到目前为止,当我们的程序涉及到数据库相关操作时,我们一般都会这么搞:...

1.1K100
来自专栏禹都一只猫博客

Python的flask:models.py来创建mysql数据库

1.4K90
来自专栏猿人谷

mybatis调用视图和存储过程

    现在的项目是以Mybatis作为O/R映射框架,确实好用,也非常方便项目的开发。MyBatis支持普通sql的查询、视图的查询、存储过程调用,是一种非常...

33650
来自专栏JAVA同学会

solr的基本概念

  大家可以把solr搜索引擎看成一个数据库,不过是基于内存的。它可以存储信息,并且根据你的查询条件返回你想要的信息。

17620
来自专栏跟着阿笨一起玩NET

C#常用工具类——Excel操作类

17310
来自专栏影子

oracle行转列、列转行、连续日期数字实现方式及mybatis下实现方式

-- 行转列 SELECT * from ( SELECT tt1.SAP_ID,TT1.dt,TT1.EFF from ( SELECT t1.SAP...

51920
来自专栏java系列博客

单例模式的各种实现

21660
来自专栏北京马哥教育

10分钟学会理解和解决MySQL乱码问题

本文将详细介绍MySQL乱码的成因和具体的解决方案。在阅读本文之前,强烈建议对字符集编码概念还比较模糊的同学 阅读下博主之前对相关概念的一篇科普:十分钟搞清字符...

31780
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战八(完结)——存储过程、函数+对象曹组

游标 在写java程序中有结果集的概念,那么在pl/sql中也会用到多条记录,这时候我们就要用到游标,游标可以存储查询返回的多条数据。 游标可以理解为是PL/S...

37660
来自专栏Python爬虫实战

设计模式:单例模式

想想一下这个场景,一个系统中可以存在多个打印任务,但是只有一个正在工作的任务。我们怎样才能保证一个类只有一个实例并且这个实例易于被访问呢?一个全局变量可以使得一...

8920

扫码关注云+社区

领取腾讯云代金券