Change from:

http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html

Stored procedures are the most common things we deal with in pl/sql programming. The structure of stored procedures is very simple. In addition to learning the basic structure of stored procedures, we will also learn some practical knowledge about how to write stored procedures. For example, cursor handling, exception handling, set selection, etc.

1. Stored procedure structure
1.1 First stored procedure

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 := 'Zhang Sanfeng';   
9.   p_para3 := v_name;   
10.   dbms_output.put_line('p_para3:'||p_para3);   
11. end;  

The above is the simplest stored procedure. A stored procedure is roughly divided into several parts:
Create statement: create or replace procedure stored procedure name
If there is no or replace statement, it simply creates a new stored procedure. If the stored procedure exists in the system, an error will be reported. Create or replace procedure If there is no stored procedure in the system, create a new one. If there is one stored procedure in the system, delete the original one and create a new one.  
Stored procedure name definition: Includes stored procedure name and parameter list. Parameter name and type. Parameter name can not be repeated, parameter passing mode: IN, OUT, IN OUT
IN represents the input parameters and is passed by value.  
OUT represents the output parameters, which can be understood as passing by reference. It can be used as the output of stored procedures for external callers.  
IN OUT can be used as both input and output parameters.  
The data type of the parameter only needs to specify the type name, without specifying the width.  
The width of the parameter is determined by the external caller.  
Processes may or may not have parameters.
Variable declaration block: The as (is) keyword that follows closely can be understood as the declaration keyword of pl/sql, which is used to declare variables.  
The variable declaration block is used to declare the variables that the stored procedure needs, and its scope is the stored procedure. In addition, the variables declared here must specify the width. Follow the PL/SQL variable declaration specification.  
Procedural Statement Block: A statement block that starts with the begin keyword as a procedure. The specific logic of stored procedures is implemented here.  
Exception processing block: The keyword is exception, which is used to handle exceptions generated by statements. This section is optional.
End block: Resulted by end keyword.  

1.2 Parametric Transfer of Stored Procedures
There are three ways to pass parameters of stored procedures: IN,OUT,IN OUT.
IN is passed by value, and it does not allow it to be reassigned in stored procedures. If the parameters of the stored procedure do not specify the transfer type of the storage parameters, the default is IN.

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 := 'Zhang Sanfeng';   
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  

This is different from other high-level languages. It is equivalent to java putting final keywords before parameters.  


OUT parameter: As an output parameter, it should be noted that when a parameter is specified as OUT type, even if the parameter is assigned before calling the stored procedure, the value of the parameter in the stored procedure is still 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 := 'Zhang Sanfeng';   
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:Zhang Sanfeng   
25. SQL> exec dbms_output.put_line(:p2);   
26.   
27.   
28. PL/SQL procedure successfully completed   
29. p2   
30. ---------  

INOUT is really passing parameters by reference. It can be used as either an incoming parameter or an outgoing parameter.  

1. 1.3 Stored procedure parameter width   
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  

First, we need to understand that we cannot specify the width of storage parameters in the definition of stored procedures, which leads to the inability to control the width of incoming variables in stored procedures. This width is entirely determined by the external inflow.  
Let's look again at the width of the OUT type parameter.  

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);  

In this process, p_para2 is assigned 20 characters a.
In the external invocation process, the parameter p2 is only defined as varchar2(1).
The procedure is called with p2 as a parameter, but no error is reported. And its true value is 20 a.

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.     Look again. IN OUT Width of parameters   
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);  

This process is still carried out correctly.  

It can be seen that for the IN parameter, its width is determined externally.  
For OUT and IN OUT parameters, their widths are determined internally by stored procedures.  
Therefore, when writing stored procedures, it is necessary to specify the width of parameters. The most sensible way is to use% type for the data type of parameters. So the two sides reached an agreement.  

Default value of 1.3 parameter
The stored procedure parameters can be set to default values.

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 
The default keyword can be used to specify default values for stored procedure parameters. Default values can be omitted when calling stored procedures.  
It should be noted that the default values only support parameters of IN transport type. OUT and IN OUT cannot specify default values

For parameters with default values, it is not the last case.  

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;  

The first parameter has a default value and the second parameter does not. If we want to use the default value of the first parameter,
exec procdefault2('aa'); 
That would be a mistake.  
How does that change? You can specify the value of the parameter.  

1. SQL> exec procdefault2(p2 =>'aa');  

remark 
So OK, specify aa to pass to parameter p2


2. Internal blocks of stored procedures
2.1 Internal Block
We know the structure of the stored procedure. The statement block starts with begin and ends with end. These blocks can be nested. You can nest any of the following blocks in a statement block.  

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;  

Attention should be paid to the scope of variables.  

3. Common Skills of Stored Procedures
3.1 Which set?
When we use stored procedures, we often need to process recordsets, that is, multiple data records. It can be divided into single column, multi-row and multi-column and multi-row. These types can be called set types. Here we compare these collection types in order to make the right choices in programming.  
Index tables, also known as pl/sql tables, can not be stored in the database, the number of elements is unlimited, subscripts can be negative.  

1. type t_table is table of varchar2(20) index by binary_integer;   
2.  v_student t_table;  

varchar2(20) denotes the data type of the storage element, and binary_integer denotes the data type of the subscript of the element.  
Nested table, index table without index by clause is nested table, it can be stored in data, the number of elements is infinite, subscripts start from 1, and need to be initialized.

1. type t_nestTable is table of varchar2(20);   
2. v_class t_nestTable ;  

This declaration alone is not usable. Nested tables must be initialized. Nested tables can be initialized using its constructor.

1. v_class :=t_nestTable('a','b','c');  

Variable-length arrays. Variable-length arrays are very similar to the type of arrays in high-level languages. The subscript begins with 1 and the number of elements is limited.  

1. type t_array is varray (20) of varchar2(20);  

varray(20) defines that the maximum number of elements in a variable-length array is 20.
Variable-length arrays, like nested tables, can also be data types of data table columns.  
At the same time, the use of variable-length arrays also needs to be initialized in advance.  

Does the number of elements stored in the database need to initialize the initial subscript value?
Is indexed table unlimited?
Nested tables can be indefinitely required 1
Variable arrays can be limited (customized) requiring 1

Thus, if only used as a collection variable in the stored procedure, the index table is the best choice.  

3.2 Which cursor should be chosen?
Display cursor can be divided into three types: ordinary cursor, parameterized cursor and cursor variable.  
Here's a process to illustrate.

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;  

cursor c_postype is select pos_type from pos_type_tbl where rownum =1 
This sentence defines the most common cursor, the whole query has been written to death, can not make any changes when calling.  
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum; 
This sentence is not written to death. The query parameters are determined by the variable v_rownum. It should be noted that v_rownum must be declared before this cursor definition.  
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum; 
This statement is similar to the second one in that it can dynamically query cursors. But it further narrows the scope of parameters. But readability has been reduced a lot.  
type t_postype is ref cursor ; 
c_postype3 t_postype; 
First, a reference cursor type is defined, and then a cursor variable is declared.  
open c_postype3 for select pos_type from pos_type_tbl where rownum =1; 
Then open a query with open for. It should be noted that it can be used many times to open different queries.  
In terms of dynamics, cursor variables are the best to use, but reading is also the worst.  
Note that the definition of cursor can only be used to make the keyword IS, which is not common with AS.  

3.3 The Best Strategy of Cursor Cycle
In PL/SQL programming, we often need to read the data of the result set in a loop. For line-by-line processing, the process involves looping the cursor. There are many ways to circle cursors, and we will analyze them one by one.  

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;  

You need to open cursor before using cursor, and close cursor after loop.
This is a rule that should be carefully remembered when using cursors.  
The above procedure demonstrates three methods of cursor looping.  
Before discussing the loop method, let's look at the properties of the cursors% founds and% notfound s.  

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;  

After opening a cursor, check its% foundation or% notfound ation attributes immediately, and the result is neither true nor false. It is null. These attributes are valid only after a fetch statement has to be executed.  

The first uses loop loops

1. loop   
2.    fetch c_postype into v_postype,v_description ;   
3.    exit when c_postype%notfound;   
4.    ……   
5. end loop  

It is important to note that the exit when statement must follow the fetch. Surplus data processing must be avoided.  
Processing logic needs to follow exit when. This requires more care.  
At the end of the loop, remember to close the cursor.  

The second uses a while loop.  

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;  

We know that when a cursor is opened, the fetch statement must be executed once before the cursor's properties will work. So when you use the while loop, you need to fetch before the loop.  
Moreover, the data processing action must precede the fetch method in the circulating body. The fetch method in the circulating body should be put at the end. Otherwise, it will be dealt with more than once. This should also be very careful.  
In short, using while to loop cursors is the most complex method.  

The third for loop

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;  

It can be seen that for loop is a relatively simple and practical method.  
First, it automatically opens and closes cursors. Solves the problem of you forgetting to open or close the cursor.  
Otherwise, a record type is automatically defined and a variable of that type is declared, and fetch data is automatically fetched into the variable.  
We need to note that the variable v_pos does not need to be declared outside the loop, nor does it need to specify a data type for it.  
It should be a record type, and the specific structure is determined by the cursor.  
The scope of this variable is only in the circulating body.  
Think of v_pos as a record variable, and if you want to get a value, it's just like calling a record.  
For example, v_pos.pos_type
Thus, for loop is the best way to loop cursors. Efficient, concise and safe.  
Unfortunately, the first method is often seen. So we have to change this habit from now on.  

3.4 select into issues that cannot be ignored
We know that in pl/sql to assign values to variables from the data table, we need to use the select into clause.  
However, it can cause some problems, and if the query is not recorded, no_data_found ation exception will be thrown.  
If there are more than one record, the too_many_rows exception is thrown.  
This one is worse. Once an exception is thrown, the process is interrupted. Especially the exception no_data_found ation, which is not serious enough to interrupt the program, can be completely handled by the program.  

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.       

Implementing this process

1. SQL> exec procexception('a');   
2. Report errors   
3. ORA-01403: no data found   
4. ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6  
5. ORA-06512: at line 1  

There are three ways to deal with this.
1. Add exception handling directly.  

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('no data found');   
11. end;  

In this way, the procedure is still interrupted. Maybe that's not what we want.  
2. Selectinto acts as a separate block for exception handling.

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;  

This is a better way to deal with it. This exception does not cause program interruption.  
3. Use cursors

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;  

This completely avoids the no_data_found ation exception. It's entirely up to the programmer to control it.  

The second case is too_many_rows exception.  
The problem of Too_many_rows is more complex than that of no_data_found ation.  
When assigning a variable, the query results have multiple records.  
There are two ways to deal with this problem:
1. Multiple data is acceptable, that is to say, just take a random value from the result set. This situation should be very extreme, if this happens, it also shows that the rigor of the procedure exists problems.  
2. Multiple data is unacceptable. In this case, the logic of the program must be out of order, that is to say, it would never have been thought that it would produce multiple records.  
For the first case, the cursor must be used to deal with, and for the second case, the internal block must be used to deal with, re-throwing the exception.  
Multiple pieces of data are acceptable, just pick one. This is the same as no_data_found ation, using cursors.  
I'm just going to say the second case. It's not acceptable to have multiple data, but don't forget to deal with no_data_found ation. This prevents the use of cursors and requires the use of internal blocks.  

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,'Yes v_postype When assigning, find multiple data');   
13.   end;   
14.  dbms_output.put_line(v_postype);   
15. end;  

It is important to note that the processing of no_data_found ation must be added, and the case of multiple records will continue to throw exceptions for the upper layer to handle.  
In short, you need to pay attention to these two situations for the select into statement. It needs to be handled properly.  

3.5 Return result set in stored procedure
We use stored procedures with a single return value, and sometimes we need to return a collection from the process. That is, multiple data. There are several solutions. The simpler way is to write temporary tables, but this is not flexible. And maintenance is troublesome. We can use nested tables. No set type matches the jdbc type of java. This is the impedance between object and relational database. The object of database can not be completely converted into the object of programming language, and it must be processed by relational database.  

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();--Initialize nested tables   
12.     v_posTypeList.extend;   
13.     v_posTypeList(1) := PosType('A001','Customer Data Change');   
14.     v_posTypeList.extend;   
15.     v_posTypeList(2) := PosType('A002','Group Data Change');   
16.     v_posTypeList.extend;   
17.     v_posTypeList(3) := PosType('A003','Beneficiary Changed');   
18.     v_posTypeList.extend;   
19.     v_posTypeList(4) := PosType('A004','Change of renewal mode of payment');   
20.     open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));   
21.   end;   
22. end procpkg;  

A cursor variable is defined in the package header and used as the parameter type of the stored procedure.  
In the stored procedure, a nested table variable is defined, and the data is written into the nested table. Then the nested table is transformed into a table, and the cursor variable is queried from the nested table. The cursor is called by an external program.  
So this process needs to define two types.  

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; 
It is important to note that these two types cannot be defined in the package header and must be defined separately so that the java layer can use them.  

Calling this process externally through pl/sql is very simple.  

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;  

Note: For cursor variables, the for loop cannot be used. Because the for loop implicitly executes the open action. The cursor% isopen opened through open for is true. That is to say, it is opened by default. An open cursor is wrong. So you can't use the for loop to handle cursor variables.  

Our main discussion is how to process this output parameter through jdbc calls.  

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. }  

This is how jdbc handles it.  

Ibatis processing method:
1. Parameter configuration

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.Calling process   
7.   <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">   
8.       {call procpkg.procrefcursor(?,?)}   
9.   </procedure>   
10.   
11. 3.Define your own processor   
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 Method   
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.     }  

This is very similar to jdbc's approach.
We use version 2.0 of ibatis, which is troublesome.  
It's very simple to use version 2.2 or more.  
Because you can define a resultMap in a parameterMap. You don't need to define your own processor.  
You can learn from the analysis of 2.0 and 2.0 dtd files.  

Both of the above approaches are very complex, and if you just need to return a result set, you can use functions to implement them.  

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();--Initialize nested tables   
13.     v_posTypeList.extend;   
14.     v_posTypeList(1) := PosType('A001','Customer Data Change');   
15.     v_posTypeList.extend;   
16.     v_posTypeList(2) := PosType('A002','Group Data Change');   
17.     v_posTypeList.extend;   
18.     v_posTypeList(3) := PosType('A003','Beneficiary Changed');   
19.     v_posTypeList.extend;   
20.     v_posTypeList(4) := PosType('A004','Change of renewal mode of payment');   
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();--Initialize nested tables   
29.     v_posTypeList.extend;   
30.     v_posTypeList(1) := PosType('A001','Customer Data Change');   
31.     v_posTypeList.extend;   
32.     v_posTypeList(2) := PosType('A002','Group Data Change');   
33.     v_posTypeList.extend;   
34.     v_posTypeList(3) := PosType('A003','Beneficiary Changed');   
35.     v_posTypeList.extend;   
36.     v_posTypeList(4) := PosType('A004','Change of renewal mode of payment');   
37.     return  v_posTypeList;   
38.   end;   
39. end procpkg;  

ibatis configuration

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 is written in the same way as regular queries.

1. public List queryPostype() {   
2.   return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);   
3. }  

There are a few points to note. Instead of using index tables, nested tables are used.  
The other is to cast nested tables into regular tables.