程序博客网 > 粤语听古仔软件下载

存储过程常用技巧

来源：互联网 发布：粤语听古仔软件下载 编辑：程序博客网 时间：2024/06/07 22:57

1.存储过程结构 
1.1 第一个存储过程

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure proc1(  
  7.   p_para1 varchar2,  
  8.   p_para2 out varchar2,  
  9.   p_para3 in out varchar2  
  10. )as   
  11.  v_name varchar2(20);  
  12. begin  
  13.   v_name := '张三丰';  
  14.   p_para3 := v_name;  
  15.   dbms_output.put_line('p_para3:'||p_para3);  
  16. 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 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

Java代码
  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.   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] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure proc1(  
  7.   p_para1 varchar2,  
  8.   p_para2 out varchar2,  
  9.   p_para3 in out varchar2  
  10. )as   
  11.  v_name varchar2(20);  
  12. begin  
  13.   p_para1 :='aaa';  
  14.   p_para2 :='bbb';  
  15.   v_name := '张三丰';  
  16.   p_para3 := v_name;  
  17.   dbms_output.put_line('p_para3:'||p_para3);  
  18.   null;  
  19. end;  
  20.       
  21. Warning: Procedure created with compilation errors  
  22.   
  23. SQL> show <span class="hilite5">error</span>  
  24.   
  25.   
  26.   
  27.   
  28. ;  
  29. Errors for PROCEDURE LIFEMAN.PROC1:  
  30.   
  31. <span class="hilite13">LINE</span>  
  32.   
  33.   
  34.   
  35.   
  36. /COL <span class="hilite5">ERROR</span>  
  37.   
  38.   
  39.   
  40.   
  41.   
  42. -------- ----------------------------------------------------------------------  
  43. 8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target  
  44. 8/3      PL/SQL: Statement ignored  

这一点与其它高级语言都不同。它相当于java在参数前面加上final关键字。 


OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.

Java代码
  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. ---------  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure proc1(  
  7.   p_para1 varchar2,  
  8.   p_para2 out varchar2,  
  9.   p_para3 in out varchar2  
  10. )as   
  11.  v_name varchar2(20);  
  12. begin  
  13.   v_name := '张三丰';  
  14.   p_para3 := v_name;  
  15.   dbms_output.put_line('p_para1:'||p_para1);  
  16.   dbms_output.put_line('p_para2:'||p_para2);  
  17.   dbms_output.put_line('p_para3:'||p_para3);  
  18. end;  
  19.   
  20. SQL> var p1 varchar2(10);  
  21. SQL> var p2 varchar2(10);  
  22. SQL> var p3 varchar2(10);  
  23. SQL> exec :p1 :='aaaa';  
  24. SQL> exec :p2 :='bbbb';  
  25. SQL> exec :p3 :='cccc';  
  26. SQL> exec proc1(:p1,:p2,:p3);  
  27. p_para1:aaaa  
  28. p_para2:  
  29. p_para3:张三丰  
  30. SQL> exec dbms_output.put_line(:p2);  
  31.   
  32.   
  33. PL/SQL procedure successfully completed  
  34. p2  
  35. ---------  


INOUT 是真正的按引用传递参数。即可作为传入参数也可以作为传出参数。

Java代码
  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   
[java] view plaincopy
  1. 1.3 存储过程参数宽度  
  2. create <span class="hilite3">or</span>  
  3.   
  4.   
  5.   
  6.   
  7.  replace procedure proc1(  
  8.   p_para1 varchar2,  
  9.   p_para2 out varchar2,  
  10.   p_para3 in out varchar2  
  11. )as   
  12.  v_name varchar2(2);  
  13. begin  
  14.   v_name := p_para1;  
  15. end;  
  16.   
  17. SQL> var p1 varchar2(10);  
  18. SQL> var p2 varchar2(20);  
  19. SQL> var p3 varchar2(30);  
  20. SQL> exec :p1 :='aaaaaa';  
  21. SQL> exec proc1(:p1,:p2,:p3);  
  22.       
  23.       
  24. <span class="hilite15">ORA</span>  
  25.   
  26.   
  27.   
  28.   
  29. -<span class="hilite16">06502</span>  
  30.   
  31.   
  32.   
  33.   
  34. : PL/SQL: <span class="hilite2">numeric</span>  
  35.   
  36.   
  37.   
  38.   
  39.  <span class="hilite3">or</span>  
  40.   
  41.   
  42.   
  43.   
  44.  <span class="hilite4">value</span>  
  45.   
  46.   
  47.   
  48.   
  49.  <span class="hilite5">error</span>  
  50.   
  51.   
  52.   
  53.   
  54. : <span class="hilite6">character</span>  
  55.   
  56.   
  57.   
  58.   
  59.  <span class="hilite7">string</span>  
  60.   
  61.   
  62.   
  63.   
  64.  <span class="hilite8">buffer</span>  
  65.   
  66.   
  67.   
  68.   
  69.  <span class="hilite9">too</span>  
  70.   
  71.   
  72.   
  73.   
  74.  <span class="hilite10">small</span>  
  75.   
  76.   
  77.   
  78.   
  79.   
  80. <span class="hilite11">ORA-06512</span>  
  81.   
  82.   
  83.   
  84.   
  85. : <span class="hilite12">at</span>  
  86.   
  87.   
  88.   
  89.   
  90.  "LIFEMAN.PROC1", <span class="hilite13">line</span>  
  91.   
  92.   
  93.   
  94.   
  95.  8  
  96. <span class="hilite11">ORA-06512</span>  
  97.   
  98.   
  99.   
  100.   
  101. : <span class="hilite12">at</span>  
  102.   
  103.   
  104.   
  105.   
  106.  <span class="hilite13">line</span>  
  107.   
  108.   
  109.   
  110.   
  111.  1  


首先，我们要明白，我们无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。 
我们再来看看OUT类型的参数的宽度。

Java代码
  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);  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure proc1(  
  7.   p_para1 varchar2,  
  8.   p_para2 out varchar2,  
  9.   p_para3 in out varchar2  
  10. )as   
  11.  v_name varchar2(2);  
  12. begin  
  13.   p_para2 :='aaaaaaaaaaaaaaaaaaaa';  
  14. end;  
  15. SQL> var p1 varchar2(1);  
  16. SQL> var p2 varchar2(1);  
  17. SQL> var p3 varchar2(1);  
  18. SQL> exec :p2 :='a';  
  19. SQL> exec proc1(:p1,:p2,:p3);  

在该过程中，p_para2被赋予了20个字符a. 
而在外部的调用过程中，p2这个参数仅仅被定义为varchar2(1). 
而把p2作为参数调用这个过程，却并没有报错。而且它的真实值就是20个a

Java代码
  1. 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);  
[java] view plaincopy
  1. 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 <span class="hilite3">or</span>  
  11.   
  12.   
  13.   
  14.   
  15.  replace procedure proc1(  
  16.   p_para1 varchar2,  
  17.   p_para2 out varchar2,  
  18.   p_para3 in out varchar2  
  19. )as   
  20.  v_name varchar2(2);  
  21. begin  
  22.   p_para3 :='aaaaaaaaaaaaaaaaaaaa';  
  23. end;  
  24.   
  25. SQL> var p1 varchar2(1);  
  26. SQL> var p2 varchar2(1);  
  27. SQL> var p3 varchar2(1);  
  28. SQL> exec proc1(:p1,:p2,:p3);  

执行这个过程，仍然正确执行。 

可见，对于IN参数，其宽度是由外部决定。 
对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。 
因此，在写存储过程时，对参数的宽度进行说明是非常有必要的，最明智的方法就是参数的数据类型使用%type。这样双方就达成了一致。 

1.3 参数的默认值 
存储过程的参数可以设置默认值

Java代码
  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' );  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procdefault(p1 varchar2,  
  7.                                         p2 varchar2 default 'mark')  
  8. as   
  9. begin  
  10.   dbms_output.put_line(p2);  
  11. end;  
  12.   
  13. SQL> set serveroutput on;  
  14. SQL> exec procdefault('a');  

mark 
可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。 
需要注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值 

对于有默认值的参数不是排在最后的情况。

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procdefault2(p1 varchar2 default 'remark',  
  7.                                         p2 varchar2 )  
  8. as   
  9. begin  
  10.   dbms_output.put_line(p1);  
  11. end;  

第一个参数有默认值，第二个参数没有。如果我们想使用第一个参数的默认值时 
exec procdefault2('aa'); 
这样是会报错的。 
那怎么变呢？可以指定参数的值。

Java代码
  1. SQL> exec procdefault2(p2 => 'aa' );  
[java] view plaincopy
  1. SQL> exec procdefault2(p2 =>'aa');  


remark 
这样就OK了，指定aa传给参数p2 


2. 存储过程内部块 
2.1 内部块 
我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块。

Java代码
  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;  
[java] view plaincopy
  1. Declare … begin … exception … end;  
  2. create <span class="hilite3">or</span>  
  3.   
  4.   
  5.   
  6.   
  7.  replace procedure innerBlock(p1 varchar2)  
  8. as   
  9.   o1 varchar2(10) := 'out1';  
  10. begin  
  11.   dbms_output.put_line(o1);  
  12.   declare   
  13.     inner1 varchar2(20);  
  14.   begin  
  15.     inner1 :='inner1';  
  16.     dbms_output.put_line(inner1);  
  17.   
  18.     declare   
  19.       inner2 varchar2(20);  
  20.     begin  
  21.       inner2 := 'inner2';  
  22.       dbms_output.put_line(inner2);  
  23.     end;  
  24.   exception   
  25.     when others then  
  26.       null;  
  27.   end;  
  28. end;  

需要注意变量的作用域。 

3.存储过程的常用技巧 
3.1 哪种集合? 
我们在使用存储过程的时候经常需要处理记录集，也就是多条数据记录。分为单列多行和多列多行，这些类型都可以称为集合类型。我们在这里进行比较这些集合类型，以便于在编程时做出正确的选择。 
索引表,也称为pl/sql表，不能存储于数据库中，元素的个数没有限制,下标可以为负值。

Java代码
  1. type t_table is table of varchar2( 20 ) index by binary_integer;  
  2.  v_student t_table;  
[java] view plaincopy
  1. type t_table is table of varchar2(20) index by binary_integer;  
  2.  v_student t_table;  

varchar2(20)表示存放元素的数据类型，binary_integer表示元素下标的数据类型。 
嵌套表，索引表没有 index by子句就是嵌套表，它可以存放于数据中，元素个数无限，下标从1开始，并且需要初始化

Java代码
  1. type t_nestTable is table of varchar2( 20 );  
  2. v_class t_nestTable ;  
[java] view plaincopy
  1. type t_nestTable is table of varchar2(20);  
  2. v_class t_nestTable ;  

仅是这样声明是不能使用的，必须对嵌套表进行初始化，对嵌套表进行初始化可以使用它的构造函数

Java代码
  1. v_class :=t_nestTable( 'a' , 'b' , 'c' );  
[java] view plaincopy
  1. v_class :=t_nestTable('a','b','c');  

变长数组，变长数组与高级语言的数组类型非常相似，下标以1开始，元素个数有限。

Java代码
  1. type t_array is varray ( 20 ) of varchar2( 20 );  
[java] view plaincopy
  1. type t_array is varray (20) of varchar2(20);  


varray(20)就定义了变长数组的最大元素个数是20个 
变长数组与嵌套表一样，也可以是数据表列的数据类型。 
同时，变长数组的使用也需要事先初始化。 

类型 可存储于数据库 元素个数 是否需初始化 初始下标值 
索引表 否 无限 不需
嵌套表 可 无限 需 1 
可变数组 可 有限(自定义) 需 1 

由此可见，如果仅仅是在存储过程中当作集合变量使用，索引表是最好的选择。 

3.2 选用何种游标? 
显示游标分为：普通游标，参数化游标和游标变量三种。 
下面以一个过程来进行说明

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure proccursor(p varchar2)  
  7. as   
  8. v_rownum number(10) := 1;  
  9. cursor c_postype is select pos_type from pos_type_tbl where rownum =1;  
  10. cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;  
  11. cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;  
  12. type t_postype is ref cursor ;  
  13. c_postype3 t_postype;  
  14. v_postype varchar2(20);  
  15. begin  
  16.   open c_postype;  
  17.   fetch c_postype into v_postype;  
  18.   dbms_output.put_line(v_postype);  
  19.   close c_postype;  
  20.   open c_postype1;  
  21.   fetch c_postype1 into v_postype;  
  22.   dbms_output.put_line(v_postype);  
  23.   close c_postype1;  
  24.   open c_postype2(1);  
  25.   fetch c_postype2 into v_postype;  
  26.   dbms_output.put_line(v_postype);  
  27.   close c_postype2;  
  28.   open c_postype3 for select pos_type from pos_type_tbl where rownum =1;  
  29.   fetch c_postype3 into v_postype;  
  30.   dbms_output.put_line(v_postype);  
  31.   close c_postype3;  
  32. end;  


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编程时，经常需要循环读取结果集的数据。进行逐行处理，这个过程就需要对游标进行循环。对游标进行循环的方法有多种，我们在此一一分析。

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure proccycle(p varchar2)  
  7. as   
  8. cursor c_postype is select pos_type, description from pos_type_tbl where rownum < <span class="hilite14">6</span>  
  9.   
  10.   
  11.   
  12.   
  13. ;  
  14. v_postype varchar2(20);  
  15. v_description varchar2(50);  
  16. begin  
  17. open c_postype;  
  18.   if c_postype%found then  
  19.     dbms_output.put_line('found true');  
  20.   elsif c_postype%found = false then  
  21.     dbms_output.put_line('found false');  
  22.   else  
  23.     dbms_output.put_line('found null');  
  24.   end if;  
  25.   loop  
  26.    fetch c_postype into v_postype,v_description ;  
  27.    exit when c_postype%notfound;  
  28.    dbms_output.put_line('postype:'||v_postype||',description:'||v_description);  
  29.   end loop;  
  30.   close c_postype;  
  31. dbms_output.put_line('---loop end---');  
  32.   open c_postype;  
  33.     fetch c_postype into v_postype,v_description;  
  34.     while c_postype%found loop  
  35.       dbms_output.put_line('postype:'||v_postype||',description:'||v_description);  
  36.       fetch c_postype into v_postype,v_description ;  
  37.     end loop;  
  38.   
  39.   close c_postype;  
  40. dbms_output.put_line('---while end---');  
  41.   for v_pos in c_postype loop  
  42.     v_postype := v_pos.pos_type;  
  43.     v_description := v_pos.description;  
  44.     dbms_output.put_line('postype:'||v_postype||',description:'||v_description);  
  45.   end loop;  
  46.   dbms_output.put_line('---for end---');  
  47. end;  


使用游标之前需要开打游标,open cursor，循环完后再关闭游标close cursor. 
这是使用游标应该慎记于心的法则。 
上面的过程演示了游标循环的三种方法。 
在讨论循环方法之前，我们先看看%found和%notfound这些游标的属性。

Java代码
  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 ;  
[java] view plaincopy
  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 循环

Java代码
  1. loop  
  2.    fetch c_postype into v_postype,v_description ;  
  3.    exit when c_postype%notfound;  
  4.    ……  
  5. end loop  
[java] view plaincopy
  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循环。

Java代码
  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;  
[java] view plaincopy
  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循环

Java代码
  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;  
[java] view plaincopy
  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这种异常，没有严重到要让程序中断的地步，可以完全交给由程序进行处理。

Java代码
  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;  
  8.       
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procexception(p varchar2)  
  7. as   
  8.   v_postype varchar2(20);  
  9. begin  
  10.    select pos_type into v_postype from pos_type_tbl where 1=0;  
  11.     dbms_output.put_line(v_postype);  
  12. end;  
  13.       

执行这个过程

Java代码
  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   
[java] view plaincopy
  1. SQL> exec procexception('a');  
  2. 报错  
  3. <span class="hilite15">ORA</span>  
  4.   
  5.   
  6.   
  7.   
  8. -01403: no data found  
  9. <span class="hilite11">ORA-06512</span>  
  10.   
  11.   
  12.   
  13.   
  14. : <span class="hilite12">at</span>  
  15.   
  16.   
  17.   
  18.   
  19.  "LIFEMAN.PROCEXCEPTION", <span class="hilite13">line</span>  
  20.   
  21.   
  22.   
  23.   
  24.  <span class="hilite14">6</span>  
  25.   
  26.   
  27.   
  28.   
  29.   
  30. <span class="hilite11">ORA-06512</span>  
  31.   
  32.   
  33.   
  34.   
  35. : <span class="hilite12">at</span>  
  36.   
  37.   
  38.   
  39.   
  40.  <span class="hilite13">line</span>  
  41.   
  42.   
  43.   
  44.   
  45.  1  


处理这个有三个办法 
1． 直接加上异常处理。

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procexception(p varchar2)  
  7. as   
  8.   v_postype varchar2(20);  
  9.     
  10. begin  
  11.    select pos_type into v_postype from pos_type_tbl where 1=0;  
  12.     dbms_output.put_line(v_postype);  
  13. exception   
  14.   when no_data_found then  
  15.     dbms_output.put_line('没找到数据');  
  16. end;  

这样做换汤不换药，程序仍然被中断。可能这样不是我们所想要的。 
2. select into做为一个独立的块，在这个块中进行异常处理

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procexception(p varchar2)  
  7. as   
  8.   v_postype varchar2(20);  
  9.     
  10. begin  
  11.   begin  
  12.    select pos_type into v_postype from pos_type_tbl where 1=0;  
  13.     dbms_output.put_line(v_postype);  
  14.  exception   
  15.   when no_data_found then  
  16.     v_postype := '';  
  17.   end;  
  18.   dbms_output.put_line(v_postype);  
  19. end;  

这是一种比较好的处理方式了。不会因为这个异常而引起程序中断。 
3.使用游标

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procexception(p varchar2)  
  7. as   
  8.   v_postype varchar2(20);  
  9.   cursor c_postype is select pos_type  from pos_type_tbl where 1=0;  
  10. begin  
  11.   open c_postype;  
  12.     fetch c_postype into v_postype;  
  13.   close c_postype;  
  14.   dbms_output.put_line(v_postype);  
  15. end;  

这样就完全的避免了no_data_found异常。完全交由程序员来进行控制了。 

第二种情况是too_many_rows 异常的问题。 
Too_many_rows 这个问题比起no_data_found要复杂一些。 
给一个变量赋值时，但是查询结果有多个记录。 
处理这种问题也有两种情况： 
1． 多条数据是可以接受的，也就是说从结果集中随便取一个值就行。这种情况应该很极端了吧，如果出现这种情况，也说明了程序的严谨性存在问题。 
2． 多条数据是不可以被接受的，在这种情况肯定是程序的逻辑出了问题，也说是说原来根本就不会想到它会产生多条记录。 
对于第一种情况，就必须采用游标来处理，而对于第二种情况就必须使用内部块来处理，重新抛出异常。 
多条数据可以接受,随便取一条，这个跟no_data_found的处理方式一样，使用游标。 
我这里仅说第二种情况，不可接受多条数据，但是不要忘了处理no_data_found哦。这就不能使用游标了，必须使用内部块。

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace procedure procexception2(p varchar2)  
  7. as   
  8.   v_postype varchar2(20);  
  9.    
  10. begin  
  11.   begin  
  12.     select pos_type into v_postype from pos_type_tbl where rownum < 5;  
  13.   exception  
  14.     when no_data_found then  
  15.       v_postype :=null;  
  16.     when too_many_rows then  
  17.       raise_application_error(-20000,'对v_postype赋值时，找到多条数据');  
  18.   end;  
  19.  dbms_output.put_line(v_postype);  
  20. end;  

需要注意的是一定要加上对no_data_found的处理，对出现多条记录的情况则继续抛出异常，让上一层来处理。 
总之对于select into的语句需要注意这两种情况了。需要妥当处理啊。 

3.5 在存储过程中返回结果集 
我们使用存储过程都是返回值都是单一的，有时我们需要从过程中返回一个集合。即多条数据。这有几种解决方案。比较简单的做法是写临时表，但是这种 做法不灵活。而且维护麻烦。我们可以使用嵌套表来实现.没有一个集合类型能够与java的jdbc类型匹配。这就是对象与关系数据库的阻抗吧。数据库的对 象并不能够完全转换为编程语言的对象，还必须使用关系数据库的处理方式。

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace package procpkg is  
  7.    type refcursor is ref cursor;  
  8.    procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  
  9. end procpkg;  
  10.   
  11. create <span class="hilite3">or</span>  
  12.   
  13.   
  14.   
  15.   
  16.  replace package body procpkg is  
  17.   procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  
  18.   is  
  19.     v_posTypeList PosTypeTable;  
  20.   begin  
  21.     v_posTypeList :=PosTypeTable();--初始化嵌套表  
  22.     v_posTypeList.extend;  
  23.     v_posTypeList(1) := PosType('A001','客户资料变更');  
  24.     v_posTypeList.extend;  
  25.     v_posTypeList(2) := PosType('A002','团体资料变更');  
  26.     v_posTypeList.extend;  
  27.     v_posTypeList(3) := PosType('A003','受益人变更');  
  28.     v_posTypeList.extend;  
  29.     v_posTypeList(4) := PosType('A004','续期交费方式变更');  
  30.     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));  
  31.   end;  
  32. end procpkg;  


在包头中定义了一个游标变量,并把它作为存储过程的参数类型。 
在存储过程中定义了一个嵌套表变量，对数据写进嵌套表中，然后把嵌套表进行类型转换为table,游标变量从这个嵌套表中进行查询。外部程序调用这个游标。 
所以这个过程需要定义两个类型。

Java代码
  1. create or  replace type PosType as Object (  
  2.   posType varchar2(20 ),  
  3.   description varchar2(50 )  
  4. );  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace type PosType as Object (  
  7.   posType varchar2(20),  
  8.   description varchar2(50)  
  9. );  

create or replace type PosTypeTable is table of PosType; 
需要注意，这两个类型不能定义在包头中，必须单独定义，这样java层才能使用。 

在外部通过pl/sql来调用这个过程非常简单。

Java代码
  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;  
[java] view plaincopy
  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调用来处理这个输出参数。

Java代码
  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. }  
[java] view plaincopy
  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.   <span class="hilite7">String</span>  
  9.   
  10.   
  11.   
  12.   
  13.  posType = rsResult.getString("posType");  
  14.   <span class="hilite7">String</span>  
  15.   
  16.   
  17.   
  18.   
  19.  description = rsResult.getString("description");  
  20.   ......  
  21. }  


这就是jdbc的处理方法。 

Ibatis处理方法： 
1.参数配置

Java代码
  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.     }  
[java] view plaincopy
  1. <parameterMap id="PosTypeMAP" class="java.util.Map">   
  2.  <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.<span class="hilite7">String</span>  
  3.   
  4.   
  5.   
  6.   
  7. " />   
  8.  <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />   
  9. </parameterMap>  
  10.   
  11. 2.调用过程  
  12.   <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">  
  13.       {call procpkg.procrefcursor(?,?)}  
  14.   </procedure>  
  15.   
  16. 3.定义自己的处理器  
  17.   public class CursorHandlerCallBack implements TypeHandler{  
  18.     public Object getResult(CallableStatement cs, int index) throws SQLException {  
  19.         ResultSet rs = (ResultSet)cs.getObject(index);  
  20.         List result = new ArrayList();  
  21.         while(rs.next()) {  
  22.             <span class="hilite7">String</span>  
  23.   
  24.   
  25.   
  26.   
  27.  postype =rs.getString(1);  
  28.             <span class="hilite7">String</span>  
  29.   
  30.   
  31.   
  32.   
  33.  description = rs.getString(2);  
  34.             CodeTableItemDTO posTypeItem = new CodeTableItemDTO();  
  35.             posTypeItem.setCode(postype);  
  36.             posTypeItem.setDescription(description);  
  37.             result.add(posTypeItem);  
  38.         }  
  39.         return result;  
  40.     }  
  41.   
  42.   
  43.   
  44. 4. dao方法  
  45.     public List procPostype() {  
  46.         <span class="hilite7">String</span>  
  47.   
  48.   
  49.   
  50.   
  51.  p = "";  
  52.         Map para = new HashMap();  
  53.         para.put("p",p);  
  54.         para.put("p_ref_postypeList",null);  
  55.          this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);  
  56.          return (List)para.get("p_ref_postypeList");  
  57.     }  


这个跟jdbc的方式非常的相似. 
我们使用的是ibatis的2.0版本，比较麻烦。 
如果是使用2.2以上版本就非常简单的。 
因为可以在parameterMap中定义一个resultMap.这样就无需要自己定义处理器了。 
可以从分析2.0和2.0的dtd文件知道。 

上面的两种方式都是非常的复杂，如果仅仅是需要返回一个结果集，那就完全可以使用函数来实现了。

Java代码
  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;  
[java] view plaincopy
  1. create <span class="hilite3">or</span>  
  2.   
  3.   
  4.   
  5.   
  6.  replace package procpkg is  
  7.    type refcursor is ref cursor;  
  8.    procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  
  9.    function procpostype(p varchar2) return PosTypeTable;   
  10. end procpkg;  
  11.   
  12. create <span class="hilite3">or</span>  
  13.   
  14.   
  15.   
  16.   
  17.  replace package body procpkg is  
  18.   procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  
  19.   is  
  20.     v_posTypeList PosTypeTable;  
  21.   begin  
  22.     v_posTypeList :=PosTypeTable();--初始化嵌套表  
  23.     v_posTypeList.extend;  
  24.     v_posTypeList(1) := PosType('A001','客户资料变更');  
  25.     v_posTypeList.extend;  
  26.     v_posTypeList(2) := PosType('A002','团体资料变更');  
  27.     v_posTypeList.extend;  
  28.     v_posTypeList(3) := PosType('A003','受益人变更');  
  29.     v_posTypeList.extend;  
  30.     v_posTypeList(4) := PosType('A004','续期交费方式变更');  
  31.     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));  
  32.   end;  
  33.   
  34.   function procpostype(p varchar2) return PosTypeTable  
  35.   as  
  36.    v_posTypeList PosTypeTable;  
  37.   begin  
  38.       v_posTypeList :=PosTypeTable();--初始化嵌套表  
  39.     v_posTypeList.extend;  
  40.     v_posTypeList(1) := PosType('A001','客户资料变更');  
  41.     v_posTypeList.extend;  
  42.     v_posTypeList(2) := PosType('A002','团体资料变更');  
  43.     v_posTypeList.extend;  
  44.     v_posTypeList(3) := PosType('A003','受益人变更');  
  45.     v_posTypeList.extend;  
  46.     v_posTypeList(4) := PosType('A004','续期交费方式变更');  
  47.     return  v_posTypeList;  
  48.   end;  
  49. end procpkg;  


ibatis配置

Java代码
  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>  
[java] view plaincopy
  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(#<span class="hilite4">value</span>  
  8.   
  9.   
  10.   
  11.   
  12. #) as PosTypeTable))  
  13.   </select>  

Dao的写法跟普通查询一样

Java代码
  1. public  List queryPostype() {  
  2.   return   this .getSqlMapClientTemplate().queryForList( "pos_dayprocset.procPostype" , null );  
  3. }  
[java] view plaincopy
  1. public List queryPostype() {  
  2.   return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);  
  3. }  


有几点需要注意，这里不能使用索引表，而是嵌套表。 
另外就是把嵌套表强制转换为普通表。

 

本文转载原地址：http://www.javaeye.com/topic/311176

0 0
粤语听古仔软件下载 粤语听古仔软件下载
原创粉丝点击
热门IT博客
java directbytebufferjava directbytebuffer
淘宝怎么同城交易淘宝怎么同城交易
java 二叉树 节点
异世淘宝女王
卓凡考试软件
淘宝刷单兼职
举例数据挖掘的应用
100mb数据流量叠加卡
在线网络理财靠谱吗
9080端口
软件安全漏洞检测技术
佳能e618清零软件
最好用的编程软件
apache和oracle
无限量流量软件
超级玛丽for mac
linux中history d命令
php导出excel报表
js 数组由小到大排序
mac 搜狗输入法消失
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 小丑服装 道具 万圣节面具 魔术道具 化妆舞会 金木研面具 儿童面具 鬼面具 纳尼亚传奇1狮子女巫和魔衣橱 泳衣泳帽泳镜套装女 连帽毛衣外套女 连帽卫衣女 冬帽 睡帽 女士夏凉帽图片 客家凉帽 空顶帽 布帽 中老年女帽 秋冬女帽 秋天女帽 今年流行女帽 冬天女帽 香奈儿女帽 秋冬款女帽 芬迪女帽 冬季女帽 鸭舌女帽 春季女帽 女生帽子 帽子品牌 手工帽子 几款漂亮的夏凉帽钩法 女演员被老人强搂 女演员被老人强抱 女强文 女主强到爆却隐藏身份 女主被兽人强h 女主高中被男主强在她自己家 女强 御女宝典

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里