存储过程、函数、触发器和包
一：前言：
一：存储过程
1：创建储存过程
View Code1 2 create procedure pro_insertDept is 3 4 begin 5 insert into dept values(77,'市场拓展部','家啊的发放');/*插入记录数据*/ 6 commit; 7 dbms_output.put_line('插入新记录成功'); 8 end pro_insertDept; 9 / 10
View Code1 create or replace procedure pro_insertDept is 2 3 begin 4 insert into dept values(77,'市场拓展部','家啊的发放');/*插入记录数据*/ 5 commit; 6 dbms_output.put_line('插入新记录成功'); 7 end pro_insertDept; 8 /
从运行结果中可以看出，执行存储过程是成功的；另外，代码中的“execute”命令也可以简写为 “exec”；但有时候需要在一个PL/SQL
程序快中调用某个储存过程
2：存储过程的参数
2-1：in 模式参数
View Code1 2 create or replace procedure insert_dept( 3 num_deptno in number,/*定义in模式的变量，它储存部门编号*/ 4 var_ename in varchar2,/*定义in模式的变量，它储存部门名称*/ 5 var_loc in varchar2 6 ) is 7 begin 8 insert into dept values(num_deptno,var_ename,var_loc); /*向dept表中插入记录*/ 9 commit; /*提交数据库*/ 10 end insert_dept; 11 / 12 13
View Code1 SQL> exec insert_dept(38,var_loc=>'adfasd',var_ename=>'adfasd'); 2 3 PL/SQL procedure successfully completed
2-2： out 模式参数
View Code1 create or replace procedure select_dept( 2 num_deptno in number,/*定义 in 模式变量 要求输入部门编号*/ 3 var_dname out dept.dname%type,/*定义out模式变量，可以储存部门名称并输出*/ 4 var_loc out dept.loc%type 5 ) 6 is 7 8 begin 9 select dname,loc into var_dname,var_loc from dept where deptno=num_deptno; 10 exception 11 when no_data_found then 12 dbms_output.put_line('该部门编号不存在'); 13 14 end select_dept; 15 /
View Code1 set serveroutput on 2 declare 3 var_dname dept.dname%type;/*声明变量，对应过程中的out模式的var_dname */ 4 var_loc dept.loc%type;/*声明变量，对应过程这哦功能的out模式的var_loc*/ 5 begin 6 select_dept(99,var_dname ,var_loc );/*传入部门编号然后输出部门名称和位置信息*/ 7 dbms_output.put_line(var_dname||'位于：'||var_loc); /*输出部门信息*/ 8 end; 9 /
在上面代码中，把声明的两个变量传入熬存储过程中，当存储过程执行时，其中的out参数会被赋值，当存储过程执行完毕，out参数的值会在调用处返回，这样定义的两个变量就可以得到out参数被赋予的值，最后这两个值就可以在储存过程外任意使用了。
View Code1 variable var_dname varchar2(50); 2 variable var_loc varchar2(50); 3 exec select_dept(15,:var_dname,:var_loc ); 4
2-3：in out 模式参数
1 create or replace procedure pro_square( 2 num in out number ,/*计算它的平方或者是平方根，这是一个‘int out ’参数*/ 3 flag in boolean 4 )is 5 i int :=2; 6 begin 7 if flag then /*true*/ 8 num:=power(num,i); 9 else 10 num :=sqrt(num); 11 12 end if; 13 14 end pro_square; 15 /
1 2 declare 3 var_number number;/*储存要进行运算的值和运算后的结果*/ 4 var_temp number;/*储存要进行运算的值*/ 5 boo_flag boolean;/*平方或者 平方根的逻辑标记*/ 6 begin 7 var_temp:=3;/*变量赋值*/ 8 var_number:=var_temp; 9 boo_flag:=false;/*false 表示计算平方根 true 表示计算平方*/ 10 pro_square(var_number,boo_flag); 11 if boo_flag then 12 dbms_output.put_line(var_temp ||'的平方是：'||var_number);/*输出计算结果*/ 13 else 14 dbms_output.put_line(var_temp ||'的平方根是：'||var_number);/*输出计算结果*/ 15 end if; 16 end; 17 / 18
2-4：in 参数的默认值
1 create or replace procedure insert_dept1( 2 num_deptno in number,/*定义储存部门编号的in参数*/ 3 var_dname in varchar2 default '综合部',/*定义储存部门名称的in 参数 并初始化默认值*/ 4 var_loc in varchar2 default '北京' 5 ) is 6 7 begin 8 insert into dept values (num_deptno,var_dname,var_loc) ; 9 10 end insert_dept1; 11 / 12
1 declare 2 row_dept dept%rowtype ;/*定义行变量，与dept表的一行类型相同*/ 3 begin 4 insert_dept1(57,var_loc => '太原');/*调用 insert_dept存储过程，插入参数*/ 5 commit; 6 select * into row_dept from dept where deptno =57 ;/*查询新插入的记录*/ 7 dbms_output.put_line('部门名称是:'||row_dept.dname||',位置是：'||row_dept.loc); 8 end; 9 /
3：存储过程示例：
3-1：中规中矩的 loop while 循环。常态是拼接游标的方式循环数据
1 create or replace procedure proc_yszxtz_aduit ( 2 yszxtz_ids in varchar2 /* 预算执行调整表id，其值为： '1,2,3,4,5,6,7,8' */ 3 ) is 4 5 rowvar_yszxtz ys_zxtz%rowtype ; /*声明了：预算执行调整主表 单条数据对象 */ 6 --- 预算执行调整主表的id的 游标 7 var_sql varchar2(4000); 8 cursor yszxtz_cursor is select * into rowvar_yszxtz from ys_zxtz z where 1=1 ; 9 begin 10 dbms_output.put_line('----===========预算执行调整审批：审结后业务处理开始-----====== '); 11 /*进行：入参*/ 12 if yszxtz_ids is null then 13 dbms_output.put_line(' 预算执行调整主表的id值不允许 null'); /*则输出异常提示信息*/ 14 rollback; /*回滚插入的数据记录*/ 15 end if; 16 17 --循环开始 18 19 /*判断 split_cursor 是否已经打开*/ 20 if not yszxtz_cursor%isopen then 21 open yszxtz_cursor ; /*打开游标 */ 22 end if; 23 fetch yszxtz_cursor into rowvar_yszxtz;/*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/ 24 while yszxtz_cursor%found loop 25 dbms_output.put_line('预算执行调整的id:' || rowvar_yszxtz.id ); 26 27 28 29 fetch yszxtz_cursor into rowvar_yszxtz;/*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/ 30 /*退出循环的条件*/ 31 exit when yszxtz_cursor%notfound or yszxtz_cursor%notfound is null; 32 33 dbms_output.put_line('---------------------------------------------预算执行调整的 业务逻辑处理结束---------------------------------------------' ); 34 end loop; 35 close yszxtz_cursor;/*关闭游标*/ 36 dbms_output.put_line('---------------------------------------------预算执行调整的 业务逻辑处理结束:关闭游标---------------------------------------------' ); 37 38 end proc_yszxtz_aduit; 39
3-2：动态拼接 游标的sql语句和参数条件的方式 进行 loop循环
1 create or replace procedure proc_yszxtz_aduit_1 ( 2 yszxtz_ids in varchar2 /* 预算执行调整表id，其值为： 1,2,3,4,5,6,7,8 */ 3 ) 4 is 5 type ref_cursor_type is ref cursor; --定义一个动态游标 6 yszxtz_cursor ref_cursor_type; 7 rowvar_yszxtz ys_zxtz%rowtype ; /*声明：预算执行调整主表 单条数据对象 */ 8 rowvar_adjustMx view_yszxtzms_adjustmx%rowtype;/*声明：view_yszxtzms_adjustmx 视图 行数据对象*/ 9 var_gkadjustid number(20);/*声明：获取 gk_adjust 表主键 对象*/ 10 11 begin 12 dbms_output.put_line('============================================预算执行调整审批：审结后业务处理开始============================================ '); 13 /*进行：入参*/ 14 if yszxtz_ids is null then 15 dbms_output.put_line(' 预算执行调整主表的id值不允许 null'); /*则输出异常提示信息*/ 16 rollback; /*回滚插入的数据记录*/ 17 end if; 18 19 --循环开始 20 --打开游标 21 open yszxtz_cursor for 'select * from ys_zxtz z where 1=1 and z.id in ('|| yszxtz_ids ||' )' ; 22 dbms_output.put_line('============================================ 预算执行调整的 业务逻辑处理 开始：执行的sql为： '|| 'select * from ys_zxtz z where 1=1 and z.id in ('|| yszxtz_ids ||' )============================================' ); /*则输出异常提示信息*/ 23 fetch yszxtz_cursor into rowvar_yszxtz;/*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/ 24 while yszxtz_cursor%found loop 25 var_gkadjustid := nextid('GK_ADJUST_SEQ');/*获取预算执行调整主表的id值*/ 26 dbms_output.put_line('============================================预算执行调整的 业务逻辑处理 开始： 其id:' || rowvar_yszxtz.id ||';gk_adjust 表主键值：var_gkadjustid='||var_gkadjustid||'===========================================' ); 27 28 29 fetch yszxtz_cursor into rowvar_yszxtz;/*先让指针指向结果集中的第一行，并将值保存到emp_row 中*/ 30 /*退出循环的条件*/ 31 exit when yszxtz_cursor%notfound or yszxtz_cursor%notfound is null; 32 dbms_output.put_line('============================================预算执行调整的 业务逻辑处理 结束：其id:' || rowvar_yszxtz.id ||'=====================================================================================' ); 33 end loop; 34 close yszxtz_cursor;/*关闭游标*/ 35 dbms_output.put_line('============================================预算执行调整的 业务逻辑处理结束:关闭游标============================================' ); 36 37 end proc_yszxtz_aduit_1; 38
3-3:使用包头、包体的形式
1 --包头 2 create or replace package mypackage as 3 type empcursor is ref cursor; --声明一个光标类型 4 procedure queryEmpList(dno in number,empList out empcursor); 5 end; 6 7 --创建包体 8 create or replace package body mypackage as 9 procedure queryEmpList(dno in number,empList out empcursor) as 10 begin 11 --打开光标 12 open empList for select * from emp where deptno=dno; 13 end; 14 end; 15
3-4: 使用存储过程，返回游标的形式
1 --定义一个返回程序集的引用游标 2 CREATE OR REPLACE PACKAGE BAF_QUERY_TABLE AS 3 TYPE P_CURSOR IS ref CURSOR; 4 END BAF_QUERY_TABLE; 5 6 --创建存储过程，并返回游标的形式返回程序集 7 create or replace procedure getList(p_eno number, p_out_cursor out BAF_QUERY_TABLE.P_CURSOR) is 8 begin 9 --没有给定员工ID则返回所有员工信息 10 if p_eno is null then 11 open p_out_cursor for select * from emp; 12 else 13 --返回指定ID的员工信息 14 open p_out_cursor for select * from emp where empno = p_eno; 15 end if; 16 17 end getList; 18 19 20 --以上创建的包还可以给存储函数使用 21 create or replace function sp_ListEmp return BAF_QUERY_TABLE.P_CURSOR 22 as 23 l_cursor BAF_QUERY_TABLE.P_CURSOR; 24 begin 25 open l_cursor for select ename, empno from emp order by ename; 26 return l_cursor; 27 end;
3-5:使用sys_refcursor类型
1 create or replace procedure rsCursor(p_eno number,rs_cursor out SYS_REFCURSOR) 2 AS 3 BEGIN 4 --没有给定员工ID则返回所有员工信息 5 if p_eno is null then 6 OPEN rs_cursor for select * from emp; 7 else 8 --返回指定ID的员工信息 9 OPEN rs_cursor for select * from emp where deptno = p_eno ; 10 end if; 11 12 END;
java中调用
1 Connection conn = null; 2 //sql语句 (一定要写上包名) 3 String sql = "{call mypackage.queryEmpList(?,?)}"; 4 5 try { 6 //获取数据库的连接 7 conn = JDBCUtil.getConnection(); 8 //通过连接创建statment 9 CallableStatement call = conn.prepareCall(sql); 10 11 //对于IN参数需要赋值 12 call.setInt(1,10); 13 14 //对于OUT参数需要先申明 15 call.registerOutParameter(2,OracleTypes.CURSOR); 16 17 //执行调用 18 call.execute(); 19 20 //取出该部门中所有员工信息(注意这里) 21 ResultSet rs = ((OracleCallableStatement)call).getCursor(2); 22 23 while(rs.next()){ 24 //可以取出sql语句中查询的所有字段(这里只取几个演示下) 25 int empno = rs.getInt("empno"); 26 String ename = rs.getString("ename"); 27 double sal = rs.getDouble("sal"); 28 System.out.println("=================================================="); 29 System.out.println("empno:"+empno+"\t ename:"+ename+"\t sal:"+sal); 30 System.out.println("=================================================="); 31 }
3-6：存储过程的常用技巧
注意：本段内容来源：《oracle 存储过程，游标、异常处理，集合使用入门详解》
在进行pl/sql编程时打交道最多的就是存储过程了。存储过程的结构是非常的简单的，我们在这里除了学习存储过程的基本结构外，还会学习编写存储过程时相关的一些实用的知识。如：游标的处理，异常的处理，集合的选择等等
1.存储过程结构
1.1 第一个存储过程
1 create or replace procedure proc1( 2 p_para1 varchar2, 3 p_para2 out varchar2, 4 p_para3 in out varchar2 5 )as 6 v_name varchar2(20); 7 begin 8 v_name := '张三丰'; 9 p_para3 := v_name; 10 dbms_output.put_line('p_para3:'||p_para3); 11 end;上面就是一个最简单的存储过程。一个存储过程大体分为这么几个部分:
创建语句：create or replace procedure 存储过程名
如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。
存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT
IN 表示输入参数，按值传递方式。
OUT 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。
IN OUT 即可作输入参数，也可作输出参数。
参数的数据类型只需要指明类型名即可，不需要指定宽度。
参数的宽度由外部调用者决定。
过程可以有参数，也可以没有参数
变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。
变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选
结束块：由end关键字结果。1.2 存储过程的参数传递方式
存储过程的参数传递有三种方式:IN,OUT,IN OUT .
IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN1 create or replace procedure proc1( 2 p_para1 varchar2, 3 p_para2 out varchar2, 4 p_para3 in out varchar2 5 )as 6 v_name varchar2(20); 7 begin 8 p_para1 :='aaa'; 9 p_para2 :='bbb'; 10 v_name := '张三丰'; 11 p_para3 := v_name; 12 dbms_output.put_line('p_para3:'||p_para3); 13 null; 14 end; 15 16 Warning: Procedure created with compilation errors 17 18 SQL> show error; 19 Errors for PROCEDURE LIFEMAN.PROC1: 20 21 LINE/COL ERROR 22 -------- ---------------------------------------------------------------------- 23 8/3 PLS-00363: expression 'P_PARA1' cannot be used as an assignment target 24 8/3 PL/SQL: Statement ignored这一点与其它高级语言都不同。它相当于java在参数前面加上final关键字。
OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.1 create or replace procedure proc1( 2 p_para1 varchar2, 3 p_para2 out varchar2, 4 p_para3 in out varchar2 5 )as 6 v_name varchar2(20); 7 begin 8 v_name := '张三丰'; 9 p_para3 := v_name; 10 dbms_output.put_line('p_para1:'||p_para1); 11 dbms_output.put_line('p_para2:'||p_para2); 12 dbms_output.put_line('p_para3:'||p_para3); 13 end; 14 15 SQL> var p1 varchar2(10); 16 SQL> var p2 varchar2(10); 17 SQL> var p3 varchar2(10); 18 SQL> exec :p1 :='aaaa'; 19 SQL> exec :p2 :='bbbb'; 20 SQL> exec :p3 :='cccc'; 21 SQL> exec proc1(:p1,:p2,:p3); 22 p_para1:aaaa 23 p_para2: 24 p_para3:张三丰 25 SQL> exec dbms_output.put_line(:p2); 26 27 28 PL/SQL procedure successfully completed 29 p2 30 --------- 31INOUT 是真正的按引用传递参数。即可作为传入参数也可以作为传出参数。
1 1.3 存储过程参数宽度 2 create or replace procedure proc1( 3 p_para1 varchar2, 4 p_para2 out varchar2, 5 p_para3 in out varchar2 6 )as 7 v_name varchar2(2); 8 begin 9 v_name := p_para1; 10 end; 11 12 SQL> var p1 varchar2(10); 13 SQL> var p2 varchar2(20); 14 SQL> var p3 varchar2(30); 15 SQL> exec :p1 :='aaaaaa'; 16 SQL> exec proc1(:p1,:p2,:p3); 17 18 19 ORA-06502: PL/SQL: numeric or value error: character string buffer too small 20 ORA-06512: at "LIFEMAN.PROC1", line 8 21 ORA-06512: at line 1首先，我们要明白，我们无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。
我们再来看看OUT类型的参数的宽度1 create or replace procedure proc1( 2 p_para1 varchar2, 3 p_para2 out varchar2, 4 p_para3 in out varchar2 5 )as 6 v_name varchar2(2); 7 begin 8 p_para2 :='aaaaaaaaaaaaaaaaaaaa'; 9 end; 10 SQL> var p1 varchar2(1); 11 SQL> var p2 varchar2(1); 12 SQL> var p3 varchar2(1); 13 SQL> exec :p2 :='a'; 14 SQL> exec proc1(:p1,:p2,:p3);
在该过程中，p_para2被赋予了20个字符a.
而在外部的调用过程中，p2这个参数仅仅被定义为varchar2(1).
而把p2作为参数调用这个过程，却并没有报错。而且它的真实值就是20个a1 SQL> select dump(:p2) from dual; 2 DUMP(:P2) 3 --------------------------------------------------------------------------- 4 Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97 5 p2 6 --------- 7 aaaaaaaaaaaaaaaaaaaa 8 9 再来看看IN OUT参数的宽度 10 create or replace procedure proc1( 11 p_para1 varchar2, 12 p_para2 out varchar2, 13 p_para3 in out varchar2 14 )as 15 v_name varchar2(2); 16 begin 17 p_para3 :='aaaaaaaaaaaaaaaaaaaa'; 18 end; 19 20 SQL> var p1 varchar2(1); 21 SQL> var p2 varchar2(1); 22 SQL> var p3 varchar2(1); 23 SQL> exec proc1(:p1,:p2,:p3);
执行这个过程，仍然正确执行。
可见，对于IN参数，其宽度是由外部决定。
对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。
因此，在写存储过程时，对参数的宽度进行说明是非常有必要的，最明智的方法就是参数的数据类型使用%type。这样双方就达成了一致。1.3 参数的默认值
存储过程的参数可以设置默认值1 create or replace procedure procdefault(p1 varchar2, 2 p2 varchar2 default 'mark') 3 as 4 begin 5 dbms_output.put_line(p2); 6 end; 7 8 SQL> set serveroutput on; 9 SQL> exec procdefault('a');mark
可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。
需要注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值
对于有默认值的参数不是排在最后的情况。1 create or replace procedure procdefault2(p1 varchar2 default 'remark', 2 p2 varchar2 ) 3 as 4 begin 5 dbms_output.put_line(p1); 6 end;第一个参数有默认值，第二个参数没有。如果我们想使用第一个参数的默认值时
exec procdefault2('aa');
这样是会报错的。
那怎么变呢？可以指定参数的值。
emark
这样就OK了，指定aa传给参数p22. 存储过程内部块
2.1 内部块
我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块。1 Declare … begin … exception … end; 2 create or replace procedure innerBlock(p1 varchar2) 3 as 4 o1 varchar2(10) := 'out1'; 5 begin 6 dbms_output.put_line(o1); 7 declare 8 inner1 varchar2(20); 9 begin 10 inner1 :='inner1'; 11 dbms_output.put_line(inner1); 12 13 declare 14 inner2 varchar2(20); 15 begin 16 inner2 := 'inner2'; 17 dbms_output.put_line(inner2); 18 end; 19 exception 20 when others then 21 null; 22 end; 23 end;
需要注意变量的作用域。
3.存储过程的常用技巧
3.1 哪种集合?
我们在使用存储过程的时候经常需要处理记录集，也就是多条数据记录。分为单列多行和多列多行，这些类型都可以称为集合类型。我们在这里进行比较这些集合类型，以便于在编程时做出正确的选择。
索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。varchar2(20)表示存放元素的数据类型，binary_integer表示元素下标的数据类型。
嵌套表，索引表没有 index by子句就是嵌套表，它可以存放于数据中，元素个数无限，下标从1开始，并且需要初始化
仅是这样声明是不能使用的，必须对嵌套表进行初始化，对嵌套表进行初始化可以使用它的构造函数
1 v_class :=t_nestTable('a','b','c');变
长数组，变长数组与高级语言的数组类型非常相似，下标以1开始，元素个数有限。
varray(20)就定义了变长数组的最大元素个数是20个
变长数组与嵌套表一样，也可以是数据表列的数据类型。
同时，变长数组的使用也需要事先初始化。
类型 可存储于数据库 元素个数 是否需初始化 初始下标值
索引表 否 无限 不需
嵌套表 可 无限 需 1
可变数组 可 有限(自定义) 需 1
由此可见，如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。3.2 选用何种游标?
显示游标分为：普通游标，参数化游标和游标变量三种。
下面以一个过程来进行说明1 create or replace procedure proccursor(p varchar2) 2 as 3 v_rownum number(10) := 1; 4 cursor c_postype is select pos_type from pos_type_tbl where rownum =1; 5 cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum; 6 cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum; 7 type t_postype is ref cursor ; 8 c_postype3 t_postype; 9 v_postype varchar2(20); 10 begin 11 open c_postype; 12 fetch c_postype into v_postype; 13 dbms_output.put_line(v_postype); 14 close c_postype; 15 open c_postype1; 16 fetch c_postype1 into v_postype; 17 dbms_output.put_line(v_postype); 18 close c_postype1; 19 open c_postype2(1); 20 fetch c_postype2 into v_postype; 21 dbms_output.put_line(v_postype); 22 close c_postype2; 23 open c_postype3 for select pos_type from pos_type_tbl where rownum =1; 24 fetch c_postype3 into v_postype; 25 dbms_output.put_line(v_postype); 26 close c_postype3; 27 end; 28
cursor c_postype is select pos_type from pos_type_tbl where rownum =1
这一句是定义了一个最普通的游标，把整个查询已经写死,调用时不可以作任何改变。
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
这一句并没有写死，查询参数由变量v_rownum来决定。需要注意的是v_rownum必须在这个游标定义之前声明。
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
这一条语句与第二条作用相似，都是可以为游标实现动态的查询。但是它进一步的缩小了参数的作用域范围。但是可读性降低了不少。
type t_postype is ref cursor ;
c_postype3 t_postype;
先定义了一个引用游标类型，然后再声明了一个游标变量。
open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
然后再用open for 来打开一个查询。需要注意的是它可以多次使用,用来打开不同的查询。
从动态性来说，游标变量是最好用的，但是阅读性也是最差的。
注意，游标的定义只能用使关键字IS，它与AS不通用。3.3 游标循环最佳策略
我们在进行PL/SQL编程时，经常需要循环读取结果集的数据。进行逐行处理，这个过程就需要对游标进行循环。对游标进行循环的方法有多种，我们在此一一分析。
1 create or replace procedure proccycle(p varchar2) 2 as 3 cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6; 4 v_postype varchar2(20); 5 v_description varchar2(50); 6 begin 7 open c_postype; 8 if c_postype%found then 9 dbms_output.put_line('found true'); 10 elsif c_postype%found = false then 11 dbms_output.put_line('found false'); 12 else 13 dbms_output.put_line('found null'); 14 end if; 15 loop 16 fetch c_postype into v_postype,v_description ; 17 exit when c_postype%notfound; 18 dbms_output.put_line('postype:'||v_postype||',description:'||v_description); 19 end loop; 20 close c_postype; 21 dbms_output.put_line('---loop end---'); 22 open c_postype; 23 fetch c_postype into v_postype,v_description; 24 while c_postype%found loop 25 dbms_output.put_line('postype:'||v_postype||',description:'||v_description); 26 fetch c_postype into v_postype,v_description ; 27 end loop; 28 29 close c_postype; 30 dbms_output.put_line('---while end---'); 31 for v_pos in c_postype loop 32 v_postype := v_pos.pos_type; 33 v_description := v_pos.description; 34 dbms_output.put_line('postype:'||v_postype||',description:'||v_description); 35 end loop; 36 dbms_output.put_line('---for end---'); 37 end;
使用游标之前需要开打游标,open cursor，循环完后再关闭游标close cursor.
这是使用游标应该慎记于心的法则。
上面的过程演示了游标循环的三种方法。
在讨论循环方法之前，我们先看看%found和%notfound这些游标的属性。1 open c_postype; 2 if c_postype%found then 3 dbms_output.put_line('found true'); 4 elsif c_postype%found = false then 5 dbms_output.put_line('found false'); 6 else 7 dbms_output.put_line('found null'); 8 end if;在打开一个游标之后，马上检查它的%found或%notfound属性，它得到的结果即不是true也不是false.而是null.必须执行一条fetch语句后，这些属性才有值。
第一种使用loop 循环
1 loop 2 fetch c_postype into v_postype,v_description ; 3 exit when c_postype%notfound; 4 …… 5 end loop
这里需要注意，exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。
处理逻辑需要跟在exit when之后。这一点需要多加小心。
循环结束后要记得关闭游标。第二种使用while循环。
1 fetch c_postype into v_postype,v_description; 2 while c_postype%found loop 3 …… 4 fetch c_postype into v_postype,v_description ; 5 end loop;我们知道了一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用。所以使用while 循环时，就需要在循环之前进行一次fetch动作。
而且数据处理动作必须放在循环体内的fetch方法之前。循环体内的fetch方法要放在最后。否则就会多处理一次。这一点也要非常的小心。
总之，使用while来循环处理游标是最复杂的方法。第三种 for循环
1 for v_pos in c_postype loop 2 v_postype := v_pos.pos_type; 3 v_description := v_pos.description; 4 … 5 end loop;.
可见for循环是比较简单实用的方法。
首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。
其它,自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。
我们需要注意v_pos 这个变量无需要在循环外进行声明，无需要为其指定数据类型。
它应该是一个记录类型，具体的结构是由游标决定的。
这个变量的作用域仅仅是在循环体内。
把v_pos看作一个记录变量就可以了，如果要获得某一个值就像调用记录一样就可以了。
如v_pos.pos_type
由此可见，for循环是用来循环游标的最好方法。高效，简洁，安全。
但遗憾的是，常常见到的却是第一种方法。所以从今之后得改变这个习惯了。3.4 select into不可乎视的问题
我们知道在pl/sql中要想从数据表中向变量赋值，需要使用select into 子句。
但是它会带动来一些问题，如果查询没有记录时，会抛出no_data_found异常。
如果有多条记录时，会抛出too_many_rows异常。
这个是比较糟糕的。一旦抛出了异常，就会让过程中断。特别是no_data_found这种异常，没有严重到要让程序中断的地步，可以完全交给由程序进行处理。1 create or replace procedure procexception(p varchar2) 2 as 3 v_postype varchar2(20); 4 begin 5 select pos_type into v_postype from pos_type_tbl where 1=0; 6 dbms_output.put_line(v_postype); 7 end;执行这个过程
1 SQL> exec procexception('a'); 2 报错 3 ORA-01403: no data found 4 ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6 5 ORA-06512: at line 1 6处理这个有三个办法
1． 直接加上异常处理。1 create or replace procedure procexception(p varchar2) 2 as 3 v_postype varchar2(20); 4 5 begin 6 select pos_type into v_postype from pos_type_tbl where 1=0; 7 dbms_output.put_line(v_postype); 8 exception 9 when no_data_found then 10 dbms_output.put_line('没找到数据'); 11 end;这样做换汤不换药，程序仍然被中断。可能这样不是我们所想要的。
2. select into做为一个独立的块，在这个块中进行异常处理1 create or replace procedure procexception(p varchar2) 2 as 3 v_postype varchar2(20); 4 5 begin 6 begin 7 select pos_type into v_postype from pos_type_tbl where 1=0; 8 dbms_output.put_line(v_postype); 9 exception 10 when no_data_found then 11 v_postype := ''; 12 end; 13 dbms_output.put_line(v_postype); 14 end;这是一种比较好的处理方式了。不会因为这个异常而引起程序中断。
3.使用游标1 create or replace procedure procexception(p varchar2) 2 as 3 v_postype varchar2(20); 4 cursor c_postype is select pos_type from pos_type_tbl where 1=0; 5 begin 6 open c_postype; 7 fetch c_postype into v_postype; 8 close c_postype; 9 dbms_output.put_line(v_postype); 10 end;
这样就完全的避免了no_data_found异常。完全交由程序员来进行控制了。
第二种情况是too_many_rows 异常的问题。
Too_many_rows 这个问题比起no_data_found要复杂一些。
给一个变量赋值时，但是查询结果有多个记录。
处理这种问题也有两种情况：
1． 多条数据是可以接受的，也就是说从结果集中随便取一个值就行。这种情况应该很极端了吧，如果出现这种情况，也说明了程序的严谨性存在问题。
2． 多条数据是不可以被接受的，在这种情况肯定是程序的逻辑出了问题，也说是说原来根本就不会想到它会产生多条记录。
对于第一种情况，就必须采用游标来处理，而对于第二种情况就必须使用内部块来处理，重新抛出异常。
多条数据可以接受,随便取一条，这个跟no_data_found的处理方式一样，使用游标。
我这里仅说第二种情况，不可接受多条数据，但是不要忘了处理no_data_found哦。这就不能使用游标了，必须使用内部块。1 create or replace procedure procexception2(p varchar2) 2 as 3 v_postype varchar2(20); 4 5 begin 6 begin 7 select pos_type into v_postype from pos_type_tbl where rownum < 5; 8 exception 9 when no_data_found then 10 v_postype :=null; 11 when too_many_rows then 12 raise_application_error(-20000,'对v_postype赋值时，找到多条数据'); 13 end; 14 dbms_output.put_line(v_postype); 15 end;
需要注意的是一定要加上对no_data_found的处理，对出现多条记录的情况则继续抛出异常，让上一层来处理。
总之对于select into的语句需要注意这两种情况了。需要妥当处理啊。
3.5 在存储过程中返回结果集
我们使用存储过程都是返回值都是单一的，有时我们需要从过程中返回一个集合。即多条数据。这有几种解决方案。比较简单的做法是写临时表，但是这种做法不灵活。而且维护麻烦。我们可以使用嵌套表来实现.没有一个集合类型能够与java的jdbc类型匹配。这就是对象与关系数据库的阻抗吧。数据库的对象并不能够完全转换为编程语言的对象，还必须使用关系数据库的处理方式。
1 create or replace package procpkg is 2 type refcursor is ref cursor; 3 procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor); 4 end procpkg; 5 6 create or replace package body procpkg is 7 procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor) 8 is 9 v_posTypeList PosTypeTable; 10 begin 11 v_posTypeList :=PosTypeTable();--初始化嵌套表 12 v_posTypeList.extend; 13 v_posTypeList(1) := PosType('A001','客户资料变更'); 14 v_posTypeList.extend; 15 v_posTypeList(2) := PosType('A002','团体资料变更'); 16 v_posTypeList.extend; 17 v_posTypeList(3) := PosType('A003','受益人变更'); 18 v_posTypeList.extend; 19 v_posTypeList(4) := PosType('A004','续期交费方式变更'); 20 open p_ref_postypeList for select * from table(cast (v_posTypeList as PosTypeTable)); 21 end; 22 end procpkg;
在包头中定义了一个游标变量,并把它作为存储过程的参数类型。
在存储过程中定义了一个嵌套表变量，对数据写进嵌套表中，然后把嵌套表进行类型转换为table,游标变量从这个嵌套表中进行查询。外部程序调用这个游标。
所以这个过程需要定义两个类型。1 create or replace type PosType as Object ( 2 posType varchar2(20), 3 description varchar2(50) 4 );create or replace type PosTypeTable is table of PosType;
需要注意，这两个类型不能定义在包头中，必须单独定义，这样java层才能使用。
在外部通过pl/sql来调用这个过程非常简单。1 set serveroutput on; 2 declare 3 type refcursor is ref cursor; 4 v_ref_postype refcursor; 5 v_postype varchar2(20); 6 v_desc varchar2(50); 7 begin 8 procpkg.procrefcursor('a',v_ref_postype); 9 loop 10 fetch v_ref_postype into v_postype,v_desc; 11 exit when v_ref_postype%notfound; 12 dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc); 13 end loop; 14 end;
注意：对于游标变量，不能使用for循环来处理。因为for循环会隐式的执行open动作。而通过open for来打开的游标%isopen是为true的。也就是默认打开的。Open一个已经open的游标是错误的。所以不能使用for循环来处理游标变量。
我们主要讨论的是如何通过jdbc调用来处理这个输出参数。1 conn = this.getDataSource().getConnection(); 2 CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}"); 3 call.setString(1, null); 4 call.registerOutParameter(2, OracleTypes.CURSOR); 5 call.execute(); 6 ResultSet rsResult = (ResultSet) call.getObject(2); 7 while (rsResult.next()) { 8 String posType = rsResult.getString("posType"); 9 String description = rsResult.getString("description"); 10 ...... 11 }
这就是jdbc的处理方法。
Ibatis处理方法：
1.参数配置
1 <parameterMap id="PosTypeMAP" class="java.util.Map"> 2 <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" /> 3 <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" /> 4 </parameterMap> 5 6 2.调用过程 7 <procedure id ="procrefcursor" parameterMap ="PosTypeMAP"> 8 {call procpkg.procrefcursor(?,?)} 9 </procedure> 10 11 3.定义自己的处理器 12 public class CursorHandlerCallBack implements TypeHandler{ 13 public Object getResult(CallableStatement cs, int index) throws SQLException { 14 ResultSet rs = (ResultSet)cs.getObject(index); 15 List result = new ArrayList(); 16 while(rs.next()) { 17 String postype =rs.getString(1); 18 String description = rs.getString(2); 19 CodeTableItemDTO posTypeItem = new CodeTableItemDTO(); 20 posTypeItem.setCode(postype); 21 posTypeItem.setDescription(description); 22 result.add(posTypeItem); 23 } 24 return result; 25 } 26 27 28 29 4. dao方法 30 public List procPostype() { 31 String p = ""; 32 Map para = new HashMap(); 33 para.put("p",p); 34 para.put("p_ref_postypeList",null); 35 this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor", para); 36 return (List)para.get("p_ref_postypeList"); 37 }
这个跟jdbc的方式非常的相似.
我们使用的是ibatis的2.0版本，比较麻烦。
如果是使用2.2以上版本就非常简单的。
因为可以在parameterMap中定义一个resultMap.这样就无需要自己定义处理器了。
可以从分析2.0和2.0的dtd文件知道。
上面的两种方式都是非常的复杂，如果仅仅是需要返回一个结果集，那就完全可以使用函数来实现了。 .1 create or replace package procpkg is 2 type refcursor is ref cursor; 3 procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor); 4 function procpostype(p varchar2) return PosTypeTable; 5 end procpkg; 6 7 create or replace package body procpkg is 8 procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor) 9 is 10 v_posTypeList PosTypeTable; 11 begin 12 v_posTypeList :=PosTypeTable();--初始化嵌套表 13 v_posTypeList.extend; 14 v_posTypeList(1) := PosType('A001','客户资料变更'); 15 v_posTypeList.extend; 16 v_posTypeList(2) := PosType('A002','团体资料变更'); 17 v_posTypeList.extend; 18 v_posTypeList(3) := PosType('A003','受益人变更'); 19 v_posTypeList.extend; 20 v_posTypeList(4) := PosType('A004','续期交费方式变更'); 21 open p_ref_postypeList for select * from table(cast (v_posTypeList as PosTypeTable)); 22 end; 23 24 function procpostype(p varchar2) return PosTypeTable 25 as 26 v_posTypeList PosTypeTable; 27 begin 28 v_posTypeList :=PosTypeTable();--初始化嵌套表 29 v_posTypeList.extend; 30 v_posTypeList(1) := PosType('A001','客户资料变更'); 31 v_posTypeList.extend; 32 v_posTypeList(2) := PosType('A002','团体资料变更'); 33 v_posTypeList.extend; 34 v_posTypeList(3) := PosType('A003','受益人变更'); 35 v_posTypeList.extend; 36 v_posTypeList(4) := PosType('A004','续期交费方式变更'); 37 return v_posTypeList; 38 end; 39 end procpkg;
ibatis配置
1 <resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO"> 2 <result property="code" column="posType"/> 3 <result property="description" column="description"/> 4 </resultMap> 5 6 <select id="procPostype" resultMap="posTypeResultMap"> 7 select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable)) 8 </select>
Dao的写法跟普通查询一样
1 public List queryPostype() { 2 return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null); 3 }
有几点需要注意，这里不能使用索引表，而是嵌套表。
另外就是把嵌套表强制转换为普通表。
4：Oracle动态游标实现动态SQL循环遍历，和静态游标的比较。
注: 本文 来源与：《 Oracle动态游标实现动态SQL循环遍历，和静态游标的比较。 》
动态游标可以遍历动态的表，
格式：
如果查询的表的数据不同的，动态变化的，这时候可以用动态游标。
需要注意的是，动态游标的定义，
在普通存储过程中：需要放在 is 后面的第一行。
动态游标通过：open 游标 for 字符串，形式使用，遍历。
1 create or replace procedure P_TEST_SQL 2 is 3 TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标 4 tablename varchar2(200) default 'ess_client'; 5 v_sql varchar2(1000); 6 mobile varchar2(15); 7 usrs ref_cursor_type; 8 begin 9 --使用连接符拼接成一条完整SQL 10 v_sql := 'select usrmsisdn from '||tablename||' where rownum < 11'; 11 --打开游标 12 open usrs for v_sql ; 13 loop 14 fetch usrs into mobile; 15 exit when usrs%notfound; 16 insert into tmp(usrmsisdn) values(mobile); 17 end loop; 18 close usrs; 19 commit; 20 end P_TEST_SQL;下面是一个自己做过的一个实例，代码只保留了可参考部分。
1 create or replace procedure DDGZ1--订单跟踪 2 ( 3 P_flag varchar,--订单类型 4 P_operate varchar,--操作类型 5 P_BH varchar --传入的编号ID 6 ) 7 is 8 TYPE ref_cursor_type IS REF CURSOR; 9 v_cur_CKD ref_cursor_type; 10 11 begin 12 open v_cur_CKD for 'select v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jlsj from tmp_DDGZ1'; 13 fetch v_cur_CKD into v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jzsj; 14 while v_cur_CKD%found 15 loop 16 insert into DRPDDGZJL2 (JLID,DRP,ERP,JXS,JXSMC,JLSJ,GZJL,CZR,BHID) values(SYS_GUID(),v_drp,v_erp,v_jxs,v_jxsmc,v_jzsj,v_DWMC||'受托代销成品库接收订单，组织发货',v_CZR,'出库单'||P_BH); 17 fetch v_cur_CKD into v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jzsj; 18 end loop; 19 close v_cur_CKD; 20 end;
而对于包头包体的存储过程。经常是如下形式：
在包头中：
1 CREATE OR REPLACE PACKAGE BIA_FYJSDCX 2 3 is --发运结算单查询 4 type T_CURSOR is ref cursor; 5 procedure ERP_HY_FYJSDCX 6 ( 7 Re_CURSOR out T_CURSOR 8 ); 9 end BIA_FYJSDCX;包体中：
1 procedure ERP_HY_FYJSDCX 2 ( 3 Re_CURSOR out T_CURSOR 4 ) 5 begin 6 v_sql:='select * from T_FYJSDCX4'; 7 Open Re_CURSOR For v_sql; 8 9 end;
3-7：oracle存储过程异常捕获
注： 本段内容来自于：《 oracle存储过程异常捕获 》
oracle存储过程异常捕获学习，执行及演示过程：
存储过程：
1 CREATE OR REPLACE PROCEDURE sp_test_2 2 ( 3 param1 in int, --输入参数 4 param2 in int, 5 out_return out varchar2 --返回结果 6 ) 7 is 8 --全局变量 9 val int; 10 errorException exception; --申明异常 11 errorCode number; --异常代号 12 errorMsg varchar2(1000); --异常信息 13 flag varchar2(10); 14 begin 15 flag := 'true'; 16 out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 17 val := param1/param2; 18 --/* 19 exception 20 when errorException then 21 errorCode := SQLCODE; 22 errorMsg := SUBSTR(SQLERRM, 1, 200); 23 flag := 'false'; 24 out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 25 when others then 26 errorCode := SQLCODE; 27 errorMsg := SUBSTR(SQLERRM, 1, 200); 28 flag := 'false'; 29 out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 30 --dbms_output.put_line(errorCode || ',' || errorMsg); 31 --*/ 32 end sp_test_2;演示存储过程：
1 DECLARE 2 out_return varchar2(1000); 3 val int; --全局变量 4 errorException exception; --申明异常 5 errorCode number; --异常编码 6 errorMsg varchar2(1000); --异常信息 7 flag varchar2(10); 8 begin 9 flag := 'true'; 10 out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 11 val := 1/0; 12 exception --异常捕捉，不要把有需要的代码放在异常捕捉后面，有异常才会执行异常代码下所有代码，没有异常不会执行 13 when errorException then 14 errorCode := SQLCODE; 15 errorMsg := SUBSTR(SQLERRM, 1, 200); 16 flag := 'false'; 17 out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 18 when others then 19 errorCode := SQLCODE; 20 errorMsg := SUBSTR(SQLERRM, 1, 200); 21 flag := 'false'; 22 out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg; 23 24 dbms_output.put_line(out_return); 25 end;sqlplus中执行存储过程：
1 DECLARE 2 out_return varchar2(1000); 3 begin 4 sp_test_2(1,0,out_return); 5 dbms_output.put_line(out_return); --打印结果 6 end;
执行存储过程 1除以0 结果：
oracle 之定义数组类型
注：本文来源：《oracle 之定义数组类型 》
oracle 数组类型，没有现成的类型，但是可以自己随意定义，很方便。
Oracle 数组可以分为定长数组和可变长的数组两类。以下主要是一维数组介绍：
1：定长数组：
1 /*定长字符数组，数组大小为10*/ 2 declare 3 type v_arr is varray(10) of varchar2(30); 4 my_arr v_arr; 5 my_arr:=v_arr('1','2','3'); 6 begin 7 for i in 1..my_arr.count 8 loop 9 dbms_output_line(my_arr(i)); 10 end loop; 11 end; 12
2：变长数组：
1 /*可变长字符数组，元素大小30，索引标号integer类型自增长*/ 2 declare 3 type v_table is table of varchar2(30) index by binary_integer; 4 --类型可以是前面的类型定义，index by binary_integer子句代表以符号整数为索引， 5 --这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。 6 my_table v_table; 7 begin 8 for i in 1..20 9 loop 10 my_table(i):=i; 11 dbms_output.put_line(my_table(i)); 12 end loop; 13 end; 14
Oracle存储过程 数组集合的使用
注：本文来源《Oracle存储过程 数组集合的使用 》
1 说明
1.1 RECORD
定义记录数据类型。它类似于C语言中的结构数据类型(STRUCTURE)，PL/SQL提供了将几个相关的、分离的、基本数据类型的变量组成一个整体的方法，即RECORD复合数据类型。在使用记录数据类型变量时，需要在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。
定义记录数据类型的语法如下：
1 TYPE RECORD_NAME IS RECORD( 2 3 V1 DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE], 4 5 V2 DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE], 6 7 VN DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]); 81.2 VARRAY
数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY（variable array，即可变数组）。
定义VARRAY数据类型的语法如下：
其中，varray_name是VARRAY数据类型的名称，size是正整数，表示可以容纳的成员的最大数量，每个成员的数据类型是element_typeo默认时，成员可以取空值，否则需要使用NOT NULL加以限制。
1.3 TABLE
定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于C语言中的二维数组，使得可以在PL/SQL中模仿数据库中的表。
定义记录表类型的语法如下：
1 TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL] 2 3 INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2]; 4关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。
BINARY_INTEGER的说明
如语句：TYPE NUMBERS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”后，NUMBERS类型的下标就是自增长，NUMBERS类型在插入元素时，不需要初始化，不需要每次EXTEND增加一个空间。
而如果没有这句话“INDEXBY BINARY_INTEGER”，那就得要显示对初始化，且每插入一个元素到NUMBERS类型的TABLE中时，都需要先EXTEND。
2 举例
2.1 创建表结构以及数据准备
1 --组织机构结构表 2 CREATE TABLE SF_ORG 3 ( 4 ORG_ID INT NOT NULL, --组织机构主键ID 5 ORG_NAME VARCHAR2(50),--组织机构名称 6 PARENT_ID INT--组织机构的父级 7 ) 8 9 --一级组织机构 10 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0); 11 12 --二级部门 13 14 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1); 15 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1); 16 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二级部门4',1);
2.2 RECORD的使用举例
先定义一个只与SF_ORG表中某几个列的数据类型相同的记录数据类型TYPE_ORG_RECORD，然后声明一个该数据类型的记录变量V_ORG_RECORD，最后用替换变量&ORG_ID接受输入的雇员编码，查询并显示该雇员的这几列中的信息。注意，在使用RECORD数据类型的变量时要用“.”运算符指定记录变量名限定词。
一个记录类型的变量只能保存从数据库中查询出的一行记录，如果查询出了多行记录，就会出现错误。
1 DECLARE 2 TYPE TYPE_ORG_RECORD IS RECORD( 3 V_ORG_NAME SF_ORG.ORG_NAME%TYPE, 4 V_PARENT_ID SF_ORG.PARENT_ID%TYPE); 5 V_ORG_RECORD TYPE_ORG_RECORD; 6 BEGIN 7 SELECT ORG_NAME,PARENT_ID INTO V_ORG_RECORD 8 FROM SF_ORG SO 9 WHERE SO.ORG_ID=&ORG_ID; 10 DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_RECORD.V_ORG_NAME); 11 DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_ORG_RECORD.V_PARENT_ID)); 12 END;
2.3 VARRAY的使用举例
先定义一个能保存5个VARCHAR2(25)数据类型的成员的VARRAY数据类型ORG_VARRAY_TYPE，然后声明一个该数据类型的VARRAY变量V_ORG_VARRAY，最后用与ORG_VARRAY_TYPE数据类型同名的构造函数语法给V_ORG_VARRAY变量赋予初值并显示赋值结果。
注意，在引用数组中的成员时．需要在一对括号中使用顺序下标，下标从1开始而不是从0开始。
1 DECLARE 2 TYPE ORG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR2(25); 3 V_ORG_VARRAY ORG_VARRAY_TYPE; 4 BEGIN 5 V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5'); 6 DBMS_OUTPUT.PUT_LINE('输出1：' || V_ORG_VARRAY(1) || '、'|| V_ORG_VARRAY(2) || '、'|| V_ORG_VARRAY(3) || '、'|| V_ORG_VARRAY(4)); 7 DBMS_OUTPUT.PUT_LINE('输出2：' || V_ORG_VARRAY(5)); 8 V_ORG_VARRAY(5) := '5001'; 9 DBMS_OUTPUT.PUT_LINE('输出3：' || V_ORG_VARRAY(5)); 10 END;
2.4 TABLE使用举例
2.4.1 存储单列多行
这个和VARRAY类似。但是赋值方式稍微有点不同，不能使用同名的构造函数进行赋值。具体的如下：
1 DECLARE 2 TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25) 3 INDEX BY BINARY_INTEGER; 4 V_ORG_TABLE ORG_TABLE_TYPE; 5 BEGIN 6 V_ORG_TABLE(1) := '1'; 7 V_ORG_TABLE(2) := '2'; 8 V_ORG_TABLE(3) := '3'; 9 V_ORG_TABLE(4) := '4'; 10 V_ORG_TABLE(5) := '5'; 11 DBMS_OUTPUT.PUT_LINE('输出1：' || V_ORG_TABLE(1) || '、'|| V_ORG_TABLE(2) || '、'|| V_ORG_TABLE(3) || '、'|| V_ORG_TABLE(4)); 12 DBMS_OUTPUT.PUT_LINE('输出2：' || V_ORG_TABLE(5)); 13 END;
2.4.2 存储多列多行和ROWTYPE结合使用
采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。
1 DECLARE 2 TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE; 3 V_TYPE T_TYPE; 4 BEGIN 5 SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE 6 FROM SF_ORG 7 WHERE SF_ORG.ORG_ID <= 3; 8 9 FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP 10 DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || '' || V_TYPE(V_INDEX).C2); 11 END LOOP; 12 END;
2.4.3 存储多列多行和RECORD结合使用
采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。
1 DECLARE 2 TYPE TEST_EMP IS RECORD 3 ( 4 C1 SF_ORG.ORG_NAME%TYPE, 5 C2 SF_ORG.PARENT_ID%TYPE 6 ); 7 TYPE T_TYPE IS TABLE OF TEST_EMP; 8 V_TYPE T_TYPE; 9 BEGIN 10 SELECT ORG_NAME, PARENT_ID BULK COLLECT INTO V_TYPE 11 FROM SF_ORG 12 WHERE SF_ORG.ORG_ID <= 3; 13 14 FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP 15 DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || '' || V_TYPE(V_INDEX).C2); 16 END LOOP; 17 END;
Oracle存储过程自定义数组定义与使用
最近为公司项目数据库通过存储过程做归档，需要用到自定义数组
百度结果中很多写的都不是很清晰，可变长数组定义好后，如何使用。
在此做个记录：
定义：
type id_array is table of number(15) index by binary_integer;
acb_ids id_array;
这个定义方式适用在package，如果是纯粹在存储过程中自定义类型请百度 。
使用：
acb_ids(acb_ids.count+1) := c_account_books_cbs_rec.acb_id;
网上很多实例是这样的:acb_ids(acb_ids.count) := c_account_books_cbs_rec.acb_id;这样写是没有用的，因为这个时候acb_ids是空的，那么acb_ids.count也是无效的，acb_ids在使用时回会报"未找到任何数据",因此需要acb_ids.count+1。
c_account_books_cbs_rec为游标遍历中的一个对象，上述代码意思是将游标数据中每行记录的ID放到自定义数组中，方便存储过程的值返回或者游标遍历外程序体中使用。
for x in 1 .. acb_ids.count loop
do something；
end loop;
存储过程中定义参数类型为数组
注意：本文来源：《存储过程中定义参数类型为数组》
1：存储过程
1 Procedure Update_Batch_Id(p_entity_id in Number, 2 p_vdr_id In fnd_table_of_number) is 3 begin 4 5 For i In 1 .. p_vdr_id.COUNT Loop 6 update cux_table_header cvs 7 set cvs.attribute10 = p_entity_id 8 where cvs.header_id = p_vdr_id(i); 9 End Loop; 10 end;
2：JAVA中调用
1 List list = new ArrayList(); 2 ... 3 list.add(row.getHeaderId()); 4 ... 5 6 7 OracleCallableStatement statement = null; 8 OracleConnection oracleConnection = (OracleConnection)tsn.getJdbcConnection(); 9 int size = list.size(); 10 if (size>0) 11 { 12 Number[] vdrIdArray = (Number[])list.toArray(new Number[size]); 13 ARRAY vdrArray=null; 14 try { 15 ArrayDescriptor tableOfNumber = 16 oracle.sql.ArrayDescriptor.createDescriptor("FND_TABLE_OF_NUMBER", 17 oracleConnection); 18 vdrArray = new ARRAY(tableOfNumber, oracleConnection, vdrIdArray); 19 String sql = 20 "BEGIN cux_XXXXXXX_pkg.Update_Batch_Id(:1,:2);end;"; 21 statement = (OracleCallableStatement)oracleConnection.prepareCall(sql); 22 23 statement.setObject(1, batchid); 24 statement.setARRAY(2, vdrArray); 25 statement.execute(); 26 }catch (Exception ex) { 27 String[][] stra2 = { { "123456wewee", ex.getMessage() }, }; 28 LogUtil.of(stra2, this).print(pageContext); 29 ex.printStackTrace(); 30 System.out.println(ex.getMessage()); 31 } 32 }
——————————————————————————————————————————————————————————————————————————————————————————
二：函数
1：创建函数
1 create or replace function get_avg_pay(num_deptno number )return number is /*创建一个函数，该函数实现计算某个部门的平均工资，传入部门编号参数*/ 2 num_avg_pay number; /*保存平均工资的内部变量*/ 3 begin 4 select avg(sal) into num_avg_pay from emp where deptno=num_deptno;/*某个部门的平均工资*/ 5 return (round(num_avg_pay)); 6 exception 7 when no_data_found then /*如果此部门编号不存在*/ 8 dbms_output.put_line('该部门编号不存在'); 9 return(0); /*返回平均工资0*/ 10 end; 11 /
2：调用函数
1 set serveroutput on 2 declare 3 avg_pay number ;/*定义变量，储存函数返回值*/ 4 begin 5 avg_pay :=get_avg_pay(10); 6 dbms_output.put_line('平均工资是：'||avg_pay); 7 end; 8 / 9
3：删除函数
三:触发器
1：触发器钙素
2：语句级触发器
1 2 /*在scott模式下 创建dept_log数据表，并在其中定义连个字段，分别用来 3 储存操作种类信息和操作日期*/ 4 create table dept_log 5 ( 6 operate_tag varchar2(10),/*定义字段，储存操作种类信息*/ 7 operate_time date /*定义字段，储存操作日期*/ 8 ); 9 10 11
1 2 /*创建一个触发器 tri_dept,该触发器在 insert、update、delete 事件下 3 都可以被触发，并且操作的数据对象是dept，要求在触发器执行时输出对dept表所做的具体操作*/ 4 create or replace trigger tri_dept 5 before insert or update or delete 6 on dept /*创建触发器，当dept表发生插入、修改、删除等操作时引起的触发器执行*/ 7 declare 8 var_tag varchar2(10); 9 begin 10 if inserting then /*当触发器事件是 insert 时*/ 11 var_tag:='插入'; 12 elsif updating then /*当触发器事件是 update 时*/ 13 var_tag:='修改'; 14 elsif deleting then /*当触发事件是delete时*/ 15 var_tag:='删除'; 16 end if; 17 insert into dept_log values(var_tag,sysdate); 18 end tri_dept; 19 /
1 insert into dept values(66,'adfasdf','fsdafd'); 2 update dept set loc='w235f' where deptno=66; 3 delete from dept where deptno=66; 4
3：行级别触发器
1 /*在scott模式下，创建一个用于储存商品种类的数据表，其中包括商品序号和商品名称*/ 2 create table goods( 3 id int primary key, 4 good_name varchar2(50) 5 );
1 2 /*使用create sequence语句创建一个序列，命名为seq_id */ 3 create sequence seq_id; 4
1 /*创建一个行级别触发器，该触发器在数据表goods插入数据时被触发，并且在该触发器的主体中实现设置 2 goods表的id列的值。*/ 3 create or replace trigger tri_insert_good 4 before insert 5 on goods /*关于goods 数据表，在向其插入新记录之前，引发该触发器的运行*/ 6 for each row /*创建行触发器*/ 7 begin 8 select seq_id.nextval into :new.id from dual; /*从序列号中生成 一个新的数值，赋值给当前插入的行的id*/ 9 end; 10 / 11
1 2 /*向goods表中插入两条记录，其中一条记录不指定id列的值，由序列seq_id来产生；另一条记录指定id的值*/ 3 insert into goods(good_name) values('asdfasdf'); 4 insert into goods(id,good_name) values(9,'asdtet'); 5 6
4：替换触发器
1 With the Partitioning, OLAP, Data Mining and Real Application Testing options 2 [oracle@localhost ~]$ sqlplus / as sysdba; 3 4 SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 7 16:38:32 2018 5 6 Copyright (c) 1982, 2011, Oracle. All rights reserved. 7 8 9 Connected to: 10 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 11 With the Partitioning, OLAP, Data Mining and Real Application Testing options 12 13 SYS@orcl> alter user system identified by oracle; 14 15 User altered. 16 17 SYS@orcl> conn system/oracle; 18 Connected. 19 SYSTEM@orcl> grant create view to scott; 20 21 Grant succeeded. 22 23 SYSTEM@orcl> conn scott/scott; 24 Connected. 25 SQL> create view view_emp_dept 26 2 as 27 3 select empno,ename,dept.deptno,dept.dname,job,hiredate from emp,dept where emp.deptno=dept.deptno; 28 29 View created 30
1 2 create or replace trigger tri_insert_view 3 instead of insert 4 on view_emp_dept /*创建一个关于 view_emp_dept视图的替换触发器*/ 5 for each row /*行级别视图*/ 6 declare 7 row_dept dept%rowtype; 8 begin 9 select * into row_dept from dept where deptno =:new.deptno;/*查询指定部门编号的记录行*/ 10 if sql%notfound then 11 insert into dept(deptno,dname) values(:new.deptno,:new.dname);/*向dept表中插入数据*/ 12 end if; 13 insert into emp(empno,ename,deptno,job,hiredate) 14 values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);/*向emp表中插入数据*/ 15 end tri_insert_view; 16 / 17
5：用户事件触发器
1 2 /* 3 常见用户事件： 4 create 、alter 、drop、analyze、comment、grant、revoke、rename、truncate、uspend、logon、logoff 5 使用create table 语句创建一个日志信息表，该表保存的日志信息包括数据对象、数据对象类型、操作行为、操作用户 等操作日期等。 6 */ 7 create table ddl_oper_log( 8 db_obj_name varchar2(20),/*数据对象名称*/ 9 db_obj_type varchar2(20),/*数据对象类型*/ 10 oper_action varchar2(20),/*操作行为*/ 11 oper_user varchar2(20),/*操作用户*/ 12 oper_date date /*操作日期*/ 13 );
1 create or replace trigger tri_ddl_oper 2 3 /*关于scott用户的ddl 操作，（这里包括 create alter drop ） 4 创建一个触发器，然后讲DDL操作的相关信息插入到 ddl_oper_log 日志表中*/ 5 before create or alter or drop 6 on scott.schema /*在scott模式下，在创建、修改、删除数据库对象之前将引发触发器运行*/ 7 begin 8 insert into ddl_oper_log values( 9 ora_dict_obj_name,/*操作的数据对象名称*/ 10 ora_dict_obj_type ,/*操作的数据对象类型 */ 11 ora_sysevent,/*系统事件名称*/ 12 ora_login_user,/*登录用户*/ 13 sysdate ); 14 15 end; 16 /
View Code1 SQL> create table tb_test(id number); 2 3 Table created 4 5 SQL> create view view_test as select empno,ename from emp; 6 7 View created 8 9 SQL> alter table tb_test add (name varchar2(10)); 10 11 Table altered 12 13 SQL> drop view view_test; 14 15 View dropped 16 17 SQL> select * from ddl_oper_log; 18 19 DB_OBJ_NAME DB_OBJ_TYPE OPER_ACTION OPER_USER OPER_DATE 20 -------------------- -------------------- -------------------- -------------------- ----------- 21 TB_TEST TABLE CREATE SCOTT 2018/1/7 21 22 VIEW_TEST VIEW CREATE SCOTT 2018/1/7 21 23 TB_TEST TABLE ALTER SCOTT 2018/1/7 21 24 VIEW_TEST VIEW DROP SCOTT 2018/1/7 21 25
四：程序包
1：程序包的规范
View Code1 2 /*创建一个程序包的规范，首先在该程序包中声明一个可以获取指定部门的平均工资的函数， 3 然后在声明一个可以实现按照指定比例上调职务的工资的储存过程*/ 4 create or replace package pack_emp is 5 function fun_avg_sal(num_deptno number) return number;/*获取指定部门的平均工资*/ 6 procedure pro_regulate_sal(var_job varchar2,num_proportion number) ;/*按照指定比例上调指定职务的工资*/ 7 end pack_emp; 8 /
2：程序包主体
1 create or replace package body pack_emp is 2 function fun_avg_sal(num_deptno number) return number is --引入“规范”中的函数 3 num_avg_sal number;--定义内部变量 4 begin 5 select avg(sal) 6 into num_avg_sal 7 from emp 8 where deptno = num_deptno;--计算某个部门的平均工资 9 return(num_avg_sal);--返回平均工资 10 exception 11 when no_data_found then--若未发现记录 12 dbms_output.put_line('该部门编号不存在雇员记录'); 13 return 0;--返回0 14 end fun_avg_sal; 15 16 procedure pro_regulate_sal(var_job varchar2,num_proportion number) is--引入“规范”中的存储过程 17 begin 18 update emp 19 set sal = sal*(1+num_proportion) 20 where job = var_job;--为指定的职务调整工资 21 end pro_regulate_sal; 22 end pack_emp; 23 / 24 25 26 27 28 29 30 31 32 33 34 35 36 37
1 set serveroutput on 2 declare 3 num_deptno emp.deptno%type;--定义部门编号变量 4 var_job emp.job%type;--定义职务变量 5 num_avg_sal emp.sal%type;--定义工资变量 6 num_proportion number;--定义工资调整比例变量 7 begin 8 num_deptno:=10;--设置部门编号为10 9 num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);--计算部门编号为10的平均工资 10 dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);--输出平均工资 11 12 var_job:='SALESMAN';--设置职务名称 13 num_proportion:=0.1;--设置调整比例 14 pack_emp.pro_regulate_sal(var_job,num_proportion);--调整指定部门的工资 15 end; 16 / 17
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
