一：前言：
一：存储过程
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------