Common skills of Oracle stored procedure

Riveore 2021-11-25 18:44:21
common skills oracle stored procedure


We're doing pl/sql When programming, the most common thing to deal with is stored procedures . The structure of stored procedure is very simple , In addition to learning the basic structure of stored procedures , You will also learn some practical knowledge about writing stored procedures . Such as : Cursor processing , Exception handling , Selection of sets, etc  


1. Stored procedure structure  
1.1 The first stored procedure  
create or replace procedure proc1(   
  p_para1 varchar2,   
  p_para2 out varchar2,   
  p_para3 in out varchar2   
)as    
 v_name varchar2(20);   
begin   
  v_name := ' Zhang Sanfeng ';   
  p_para3 := v_name;   
  dbms_output.put_line('p_para3:'||p_para3);   
end;  




The above is the simplest stored procedure . A stored procedure is roughly divided into these parts : 
Create statement :create or replace procedure Stored procedure name  
without or replace sentence , Just create a new stored procedure . If the stored procedure exists in the system , May be an error .Create or replace procedure If there is no such stored procedure in the system, a new one will be created , If there is this stored procedure in the system, delete the original , Recreate a stored procedure . 
Stored procedure name definition : Including stored procedure name and parameter list . Parameter name and parameter type . Parameter name cannot be duplicate , Parameter transfer mode :IN, OUT, IN OUT 
IN Represents input parameter , Pass by value . 
OUT Represents the output parameter , It can be understood as passing by reference . It can be used as the output of the stored procedure , For external use . 
IN OUT Can be used as input parameters , It can also be used as an output parameter . 
The data type of the parameter only needs to indicate the type name , There is no need to specify the width . 
The width of the parameter is determined by the external caller . 
Procedures can have parameters , There can be no parameters  
Variable declaration block : Next to it as (is ) keyword , It can be understood as pl/sql Of declare keyword , Used to declare variables . 
The variable declaration block is used to declare the variables needed by the stored procedure , Its scope is the stored procedure . In addition, the variable declared here must specify the width . follow PL/SQL Variable declaration specification . 
Procedure statement block : from begin The keyword starts with the statement block of the procedure . The specific logic of the stored procedure is implemented here . 
Exception handling block : Keyword is exception , Exception generated for processing statement . This section is optional  
End block : from end Keyword results . 


1.2 Parameter passing method of stored procedure  
There are three ways to pass parameters to a stored procedure :IN,OUT,IN OUT . 
IN Pass by value , And it is not allowed to be reassigned in stored procedures . If the parameters of the stored procedure do not specify the parameter transfer type , The default is IN 
create or replace procedure proc1(   
  p_para1 varchar2,   
  p_para2 out varchar2,   
  p_para3 in out varchar2   
)as    
 v_name varchar2(20);   
begin   
  p_para1 :='aaa';   
  p_para2 :='bbb';   
  v_name := ' Zhang Sanfeng ';   
  p_para3 := v_name;   
  dbms_output.put_line('p_para3:'||p_para3);   
  null;   
end;   
       
Warning: Procedure created with compilation errors   
  
SQL> show error;   
Errors for PROCEDURE LIFEMAN.PROC1:   
  
LINE/COL ERROR   
-------- ----------------------------------------------------------------------   
8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target   
8/3      PL/SQL: Statement ignored  
This is different from other high-level languages . It is equivalent to java Add before parameter final keyword . 




OUT Parameters : As an output parameter , We need to pay attention to , When a parameter is specified as OUT Type , Even if the parameter is assigned before calling the stored procedure , In the stored procedure, the value of this parameter is still null. 
create or replace procedure proc1(   
  p_para1 varchar2,   
  p_para2 out varchar2,   
  p_para3 in out varchar2   
)as    
 v_name varchar2(20);   
begin   
  v_name := ' Zhang Sanfeng ';   
  p_para3 := v_name;   
  dbms_output.put_line('p_para1:'||p_para1);   
  dbms_output.put_line('p_para2:'||p_para2);   
  dbms_output.put_line('p_para3:'||p_para3);   
end;   
  
SQL> var p1 varchar2(10);   
SQL> var p2 varchar2(10);   
SQL> var p3 varchar2(10);   
SQL> exec :p1 :='aaaa';   
SQL> exec :p2 :='bbbb';   
SQL> exec :p3 :='cccc';   
SQL> exec proc1(:p1,:p2,:p3);   
p_para1:aaaa   
p_para2:   
p_para3: Zhang Sanfeng   
SQL> exec dbms_output.put_line(:p2);   
  
  
PL/SQL procedure successfully completed   
p2   
---------  


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


1.3 Stored procedure parameter width   
create or replace procedure proc1(   
  p_para1 varchar2,   
  p_para2 out varchar2,   
  p_para3 in out varchar2   
)as    
 v_name varchar2(2);   
begin   
  v_name := p_para1;   
end;   
  
SQL> var p1 varchar2(10);   
SQL> var p2 varchar2(20);   
SQL> var p3 varchar2(30);   
SQL> exec :p1 :='aaaaaa';   
SQL> exec proc1(:p1,:p2,:p3);   
       
       
ORA-06502: PL/SQL: numeric or value error: character string buffer too small   
ORA-06512: at "LIFEMAN.PROC1", line 8  
ORA-06512: at line 1  


First , We need to understand , We cannot specify the width of the storage parameter in the definition of the stored procedure , As a result, we cannot control the width of the incoming variable in the stored procedure . This width is completely determined by external input . 
Let's see OUT The width of the parameter of type . 
create or replace procedure proc1(   
  p_para1 varchar2,   
  p_para2 out varchar2,   
  p_para3 in out varchar2   
)as    
 v_name varchar2(2);   
begin   
  p_para2 :='aaaaaaaaaaaaaaaaaaaa';   
end;   
SQL> var p1 varchar2(1);   
SQL> var p2 varchar2(1);   
SQL> var p3 varchar2(1);   
SQL> exec :p2 :='a';   
SQL> exec proc1(:p1,:p2,:p3);  
In the process ,p_para2 Given to 20 Characters a. 
In the external call process ,p2 This parameter is only defined as varchar2(1). 
But the p2 Call this procedure as a parameter , But no error was reported . And its true value is 20 individual a 
SQL> select dump(:p2) from dual;   
DUMP(:P2)   
---------------------------------------------------------------------------   
Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97  
p2   
---------   
aaaaaaaaaaaaaaaaaaaa   
       
    Look again. IN OUT The width of the parameter   
create or replace procedure proc1(   
  p_para1 varchar2,   
  p_para2 out varchar2,   
  p_para3 in out varchar2   
)as    
 v_name varchar2(2);   
begin   
  p_para3 :='aaaaaaaaaaaaaaaaaaaa';   
end;   
  
SQL> var p1 varchar2(1);   
SQL> var p2 varchar2(1);   
SQL> var p3 varchar2(1);   
SQL> exec proc1(:p1,:p2,:p3);  
Perform this process , Still execute correctly . 


so , about IN Parameters , Its width is determined by the outside . 
about OUT and IN OUT Parameters , Its width is determined internally by the stored procedure . 
therefore , When writing stored procedures , It is very necessary to describe the width of the parameter , The wisest way is to use the data type of the parameter %type. So the two sides reached an agreement . 


1.3 The default value of the parameter  
The parameters of the stored procedure can be set to default values  
create or replace procedure procdefault(p1 varchar2,   
                                        p2 varchar2 default 'mark')   
as    
begin   
  dbms_output.put_line(p2);   
end;   
  
SQL> set serveroutput on;   
SQL> exec procdefault('a');  
mark 
Can pass default Keyword specifies the default value for the parameters of the stored procedure . When calling a stored procedure , You can omit the default value . 
It should be noted that : The default value only supports IN Parameters of transport type .OUT and IN OUT Default value... Cannot be specified  


For parameters with default values, it is not the last case . 
create or replace procedure procdefault2(p1 varchar2 default 'remark',   
                                        p2 varchar2 )   
as    
begin   
  dbms_output.put_line(p1);   
end;  
The first parameter has a default value , The second parameter does not . If we want to use the default value of the first parameter  
exec procdefault2('aa'); 
This is a mistake . 
How does that change ? You can specify the value of the parameter . 
SQL> exec procdefault2(p2 =>'aa');  


remark 
So you OK 了 , Appoint aa Pass to parameter p2 




2. Internal block of stored procedure  
2.1 Internal block  
We know the structure of stored procedures , Statement blocks are created by begin Start , With end end . These blocks can be nested . You can nest any of the following blocks in a statement block . 
Declare … begin … exception … end;   
create or replace procedure innerBlock(p1 varchar2)   
as    
  o1 varchar2(10) := 'out1';   
begin   
  dbms_output.put_line(o1);   
  declare    
    inner1 varchar2(20);   
  begin   
    inner1 :='inner1';   
    dbms_output.put_line(inner1);   
  
    declare    
      inner2 varchar2(20);   
    begin   
      inner2 := 'inner2';   
      dbms_output.put_line(inner2);   
    end;   
  exception    
    when others then   
      null;   
  end;   
end;  
You need to pay attention to the scope of the variable . 


3. Common techniques for stored procedures  
3.1 What kind of collection ? 
When we use stored procedures, we often need to deal with recordsets , That is, multiple data records . It is divided into single column and multiple rows and multiple columns and multiple rows , These types can be called collection types . Let's compare these collection types here , In order to make the right choice when programming . 
Index table , Also known as pl/sql surface , Cannot be stored in the database , There is no limit to the number of elements , Subscript can be negative . 
type t_table is table of varchar2(20) index by binary_integer;   
 v_student t_table;  
varchar2(20) Represents the data type of the element ,binary_integer Represents the data type of the element subscript . 
Nested table , The index table does not index by Clause is a nested table , It can be stored in data , The number of elements is infinite , Subscript from 1 Start , And initialization is required  
type t_nestTable is table of varchar2(20);   
v_class t_nestTable ;  
This statement alone cannot be used , Nested tables must be initialized , To initialize a nested table, you can use its constructor  
v_class :=t_nestTable('a','b','c');  
Variable length array , Variable length arrays are very similar to array types in high-level languages , Subscript with 1 Start , The number of elements is limited . 
type t_array is varray (20) of varchar2(20);  


varray(20) It defines that the maximum number of elements of a variable length array is 20 individual  
Variable length arrays are the same as nested tables , It can also be the data type of the data table column . 
meanwhile , The use of variable length arrays also needs to be initialized in advance . 


type Can be stored in database Element number Whether to initialize Initial subscript value  
Index table no Infinite No need  
Nested table can Infinite Need to be 1 
Variable array can Co., LTD. ( Customize ) Need to be 1 


thus it can be seen , If it is only used as a collection variable in a stored procedure , The index table is the best choice . 


3.2 What kind of cursor ? 
The display cursor is divided into : Normal cursor , Parameterized cursors and cursor variables . 
The following is a process to illustrate  
create or replace procedure proccursor(p varchar2)   
as    
v_rownum number(10) := 1;   
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;   
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;   
v_postype varchar2(20);   
begin   
  open c_postype;   
  fetch c_postype into v_postype;   
  dbms_output.put_line(v_postype);   
  close c_postype;   
  open c_postype1;   
  fetch c_postype1 into v_postype;   
  dbms_output.put_line(v_postype);   
  close c_postype1;   
  open c_postype2(1);   
  fetch c_postype2 into v_postype;   
  dbms_output.put_line(v_postype);   
  close c_postype2;   
  open c_postype3 for select pos_type from pos_type_tbl where rownum =1;   
  fetch c_postype3 into v_postype;   
  dbms_output.put_line(v_postype);   
  close c_postype3;   
end;  


cursor c_postype is select pos_type from pos_type_tbl where rownum =1 
This sentence defines the most common cursor , Write the whole query dead , No changes can be made when calling . 
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum; 
This sentence is not dead , Query parameters consist of variables v_rownum To decide . 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 , Can implement dynamic queries for cursors . But it further narrows the scope of parameters . But the readability is reduced a lot . 
type t_postype is ref cursor ; 
c_postype3 t_postype; 
First, a reference cursor type is defined , Then declare a cursor variable . 
open c_postype3 for select pos_type from pos_type_tbl where rownum =1; 
And then use open for To open a query . It should be noted that it can be used multiple times , Used to open different queries . 
In terms of dynamics , Cursor variables are best used , But reading is also the worst . 
Be careful , Cursors can only be defined with the keyword IS, It is associated with AS Not universal . 


3.3 Cursor loop best strategy  
We're doing PL/SQL When programming , It is often necessary to read the data of the result set circularly . Line by line , This process needs to loop the cursor . There are many ways to loop cursors , Let's analyze them one by one . 
create or replace procedure proccycle(p varchar2)   
as    
cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6;   
v_postype varchar2(20);   
v_description varchar2(50);   
begin   
open c_postype;   
  if c_postype%found then   
    dbms_output.put_line('found true');   
  elsif c_postype%found = false then   
    dbms_output.put_line('found false');   
  else  
    dbms_output.put_line('found null');   
  end if;   
  loop   
   fetch c_postype into v_postype,v_description ;   
   exit when c_postype%notfound;   
   dbms_output.put_line('postype:'||v_postype||',description:'||v_description);   
  end loop;   
  close c_postype;   
dbms_output.put_line('---loop end---');   
  open c_postype;   
    fetch c_postype into v_postype,v_description;   
    while c_postype%found loop   
      dbms_output.put_line('postype:'||v_postype||',description:'||v_description);   
      fetch c_postype into v_postype,v_description ;   
    end loop;   
  
  close c_postype;   
dbms_output.put_line('---while end---');   
  for v_pos in c_postype loop   
    v_postype := v_pos.pos_type;   
    v_description := v_pos.description;   
    dbms_output.put_line('postype:'||v_postype||',description:'||v_description);   
  end loop;   
  dbms_output.put_line('---for end---');   
end;  


You need to open the cursor before using it ,open cursor, Close the cursor after the loop close cursor. 
This is a rule that should be kept in mind when using cursors . 
The above procedure demonstrates three methods of cursor loop . 
Before discussing the loop method , Let's see %found and %notfound The properties of these cursors . 


open c_postype;   
 if c_postype%found then   
   dbms_output.put_line('found true');   
 elsif c_postype%found = false then   
   dbms_output.put_line('found false');   
 else  
   dbms_output.put_line('found null');   
 end if;  
After opening a cursor , Check its... Right away %found or %notfound attribute , The result is not true Neither false. It is null. A must be implemented fetch After the statement , These attributes have values . 


First use loop loop  
loop   
   fetch c_postype into v_postype,v_description ;   
   exit when c_postype%notfound;   
   ……   
end loop  
Here we need to pay attention to ,exit when The statement must be followed by fetch after . Unnecessary data processing must be avoided . 
Processing logic needs to follow exit when after . This requires more care . 
Remember to close the cursor at the end of the loop . 


Second use while loop . 
   fetch c_postype into v_postype,v_description;   
while c_postype%found loop   
   ……   
      fetch c_postype into v_postype,v_description ;   
end loop;  


We know that after a cursor is opened , It has to be done once fetch sentence , The properties of the cursor will work . So use while loop , You need to do it once before the cycle fetch action . 
And the data processing action must be placed in the loop fetch Before method . Circulating in the body fetch The method should be put at the end . Otherwise, it will be handled once more . This should also be very careful . 
All in all , Use while To loop through cursors is the most complex method . 


The third kind of for loop  
for v_pos in c_postype loop   
   v_postype := v_pos.pos_type;   
   v_description := v_pos.description;   
   …   
 end loop;  
so for Loop is a relatively simple and practical method . 
First , It will automatically open and close The cursor . It solves the problem that you forget to open or close the cursor . 
Other , Automatically defines a record type and the variables that declare it , And automatically fetch Data into this variable . 
We need to pay attention v_pos This variable does not need to be declared outside the loop , There is no need to specify a data type for it . 
It should be a record type , The specific structure is determined by the cursor . 
The scope of this variable is only within the loop . 
hold v_pos Just think of it as a record variable , If you want to get a certain value, just like the call record . 
Such as v_pos.pos_type 
thus it can be seen ,for Loop is the best way to loop a cursor . Efficient , concise , Security . 
But unfortunately , The first method is often seen . So I have to change this habit from now on . 


3.4 select into A problem that cannot be ignored  
We know that pl/sql To assign a value to a variable from a data table , Need to use select into Clause . 
But it will lead to some problems , If the query has no records , Will throw out no_data_found abnormal . 
If there are multiple records , Will throw out too_many_rows abnormal . 
This is worse . Once an exception is thrown , Will interrupt the process . especially no_data_found This kind of abnormality , Not serious enough to interrupt the program , It can be completely handled by the program . 
create or replace procedure procexception(p varchar2)   
as    
  v_postype varchar2(20);   
begin   
   select pos_type into v_postype from pos_type_tbl where 1=0;   
    dbms_output.put_line(v_postype);   
end;   
      
Perform this process  
SQL> exec procexception('a');   
Report errors   
ORA-01403: no data found   
ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6  
ORA-06512: at line 1  


There are three ways to deal with this  
1. Add exception handling directly . 
create or replace procedure procexception(p varchar2)   
as    
  v_postype varchar2(20);   
     
begin   
   select pos_type into v_postype from pos_type_tbl where 1=0;   
    dbms_output.put_line(v_postype);   
exception    
  when no_data_found then   
    dbms_output.put_line(' No data found ');   
end;  
In this way, change the soup without changing the dressing , The program is still interrupted . Maybe this is not what we want . 
2. select into As a separate block , Exception handling in this block  
create or replace procedure procexception(p varchar2)   
as    
  v_postype varchar2(20);   
     
begin   
  begin   
   select pos_type into v_postype from pos_type_tbl where 1=0;   
    dbms_output.put_line(v_postype);   
 exception    
  when no_data_found then   
    v_postype := '';   
  end;   
  dbms_output.put_line(v_postype);   
end;  
This is a better way to deal with . This exception will not cause program interruption . 
3. Use cursors  
create or replace procedure procexception(p varchar2)   
as    
  v_postype varchar2(20);   
  cursor c_postype is select pos_type  from pos_type_tbl where 1=0;   
begin   
  open c_postype;   
    fetch c_postype into v_postype;   
  close c_postype;   
  dbms_output.put_line(v_postype);   
end;  
This completely avoids no_data_found abnormal . It's entirely up to the programmer to control . 


The second situation is too_many_rows An abnormal problem . 
Too_many_rows This problem is better than no_data_found It's a little bit more complicated . 
When you assign a variable , But the query result has multiple records . 
There are two ways to deal with this problem : 
1. Multiple pieces of data are acceptable , That is, just take a value from the result set . This situation should be very extreme , If that happens , It also shows that there are problems with the rigor of the procedure . 
2. Multiple pieces of data are not acceptable , In this case, there must be something wrong with the logic of the program , In other words, I never thought it would produce multiple records . 
For the first case , You have to use cursors to handle , In the second case, you must use internal blocks to deal with , Rethrow exception . 
Multiple pieces of data are acceptable , Take any one , This heel no_data_found It's the same way , Use cursors . 
I'll just talk about the second case , Multiple pieces of data are not acceptable , But don't forget to deal with no_data_found Oh . You can't use cursors , Internal blocks must be used . 
create or replace procedure procexception2(p varchar2)   
as    
  v_postype varchar2(20);   
    
begin   
  begin   
    select pos_type into v_postype from pos_type_tbl where rownum < 5;   
  exception   
    when no_data_found then   
      v_postype :=null;   
    when too_many_rows then   
      raise_application_error(-20000,' Yes v_postype assignment , Multiple pieces of data found ');   
  end;   
 dbms_output.put_line(v_postype);   
end;  
It should be noted that you must add the right no_data_found To deal with , In case of multiple records, continue to throw exceptions , Let the next level deal with . 
In short, for select into You need to pay attention to these two situations . It needs to be handled properly . 


3.5 Returns a result set in a stored procedure  
We use stored procedures that return a single value , Sometimes we need to return a collection from the process . That is, multiple pieces of data . There are several solutions . The simpler way is to write a temporary table , But this approach is inflexible . And maintenance is troublesome . We can use nested tables to achieve . No collection type can be associated with java Of jdbc Type match . This is the impedance between objects and relational databases . Database objects cannot be completely converted into programming language objects , You must also use the relational database approach . 


create or replace package procpkg is   
   type refcursor is ref cursor;   
   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);   
end procpkg;   
  
create or replace package body procpkg is   
  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)   
  is   
    v_posTypeList PosTypeTable;   
  begin   
    v_posTypeList :=PosTypeTable();-- Initialize nested tables   
    v_posTypeList.extend;   
    v_posTypeList(1) := PosType('A001',' Customer information changes ');   
    v_posTypeList.extend;   
    v_posTypeList(2) := PosType('A002',' Group data change ');   
    v_posTypeList.extend;   
    v_posTypeList(3) := PosType('A003',' Change of beneficiary ');   
    v_posTypeList.extend;   
    v_posTypeList(4) := PosType('A004',' Renewal payment method change ');   
    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));   
  end;   
end procpkg;  


A cursor variable is defined in the header , And take it as the parameter type of the stored procedure . 
A nested table variable is defined in the stored procedure , Write data into nested tables , Then type the nested table into table, Cursor variables are queried from this nested table . An external program calls this cursor . 
So this process needs to define two types . 
create or replace type PosType as Object (   
  posType varchar2(20),   
  description varchar2(50)   
);  
create or replace type PosTypeTable is table of PosType; 
We need to pay attention to , These two types cannot be defined in the header , Must be defined separately , such java Layer can be used . 


Pass outside pl/sql To call this process is very simple . 
set serveroutput on;   
declare    
  type refcursor is ref cursor;   
  v_ref_postype refcursor;   
  v_postype varchar2(20);   
  v_desc varchar2(50);   
begin   
  procpkg.procrefcursor('a',v_ref_postype);   
  loop   
    fetch  v_ref_postype into v_postype,v_desc;   
    exit when v_ref_postype%notfound;   
    dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);   
  end loop;   
end;  


Be careful : For cursor variables , Out of commission for Loop to handle . because for The loop implicitly executes open action . And by open for To open the cursor %isopen Is for true Of . Which is opened by default .Open One has been open The cursor is wrong . So it can't be used for Loop to handle cursor variables . 


Our main discussion is how to pass jdbc Call to process this output parameter . 
conn = this.getDataSource().getConnection();   
CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");   
call.setString(1, null);   
call.registerOutParameter(2, OracleTypes.CURSOR);   
call.execute();   
ResultSet rsResult = (ResultSet) call.getObject(2);   
while (rsResult.next()) {   
  String posType = rsResult.getString("posType");   
  String description = rsResult.getString("description");   
  ......   
}  


This is it. jdbc Treatment method . 


Ibatis processing method : 
1. Parameter configuration  
<parameterMap id="PosTypeMAP" class="java.util.Map">    
 <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" />    
 <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />    
</parameterMap>   
  
2. Call the process   
  <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">   
      {call procpkg.procrefcursor(?,?)}   
  </procedure>   
  
3. Define your own processor   
  public class CursorHandlerCallBack implements TypeHandler{   
    public Object getResult(CallableStatement cs, int index) throws SQLException {   
        ResultSet rs = (ResultSet)cs.getObject(index);   
        List result = new ArrayList();   
        while(rs.next()) {   
            String postype =rs.getString(1);   
            String description = rs.getString(2);   
            CodeTableItemDTO posTypeItem = new CodeTableItemDTO();   
            posTypeItem.setCode(postype);   
            posTypeItem.setDescription(description);   
            result.add(posTypeItem);   
        }   
        return result;   
    }   
  
  
  
4. dao Method   
    public List procPostype() {   
        String p = "";   
        Map para = new HashMap();   
        para.put("p",p);   
        para.put("p_ref_postypeList",null);   
         this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);   
         return (List)para.get("p_ref_postypeList");   
    }  


This heel jdbc In a very similar way . 
We use ibatis Of 2.0 edition , More trouble . 
If using 2.2 The above version is very simple . 
Because you can parameterMap Define a resultMap. This eliminates the need to define your own processor . 
You can analyze 2.0 and 2.0 Of dtd The file knows . 


Both of the above methods are very complex , If you just need to return a result set , Then you can use functions to implement . 
create or replace package procpkg is   
   type refcursor is ref cursor;   
   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);   
   function procpostype(p varchar2) return PosTypeTable;    
end procpkg;   
  
create or replace package body procpkg is   
  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)   
  is   
    v_posTypeList PosTypeTable;   
  begin   
    v_posTypeList :=PosTypeTable();-- Initialize nested tables   
    v_posTypeList.extend;   
    v_posTypeList(1) := PosType('A001',' Customer information changes ');   
    v_posTypeList.extend;   
    v_posTypeList(2) := PosType('A002',' Group data change ');   
    v_posTypeList.extend;   
    v_posTypeList(3) := PosType('A003',' Change of beneficiary ');   
    v_posTypeList.extend;   
    v_posTypeList(4) := PosType('A004',' Renewal payment method change ');   
    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));   
  end;   
  
  function procpostype(p varchar2) return PosTypeTable   
  as   
   v_posTypeList PosTypeTable;   
  begin   
      v_posTypeList :=PosTypeTable();-- Initialize nested tables   
    v_posTypeList.extend;   
    v_posTypeList(1) := PosType('A001',' Customer information changes ');   
    v_posTypeList.extend;   
    v_posTypeList(2) := PosType('A002',' Group data change ');   
    v_posTypeList.extend;   
    v_posTypeList(3) := PosType('A003',' Change of beneficiary ');   
    v_posTypeList.extend;   
    v_posTypeList(4) := PosType('A004',' Renewal payment method change ');   
    return  v_posTypeList;   
  end;   
end procpkg;  


ibatis To configure  
<resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO">   
   <result property="code" column="posType"/>   
   <result property="description" column="description"/>   
 </resultMap>   
  
  <select id="procPostype" resultMap="posTypeResultMap">   
    select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable))   
  </select>  
Dao It is written in the same way as an ordinary query  
public List queryPostype() {   
  return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);   
}  


There are several points to pay attention to , Index tables cannot be used here , But nested tables . 
The other is to cast the nested table into an ordinary table .

版权声明
本文为[Riveore]所创,转载请带上原文链接,感谢
https://javamana.com/2021/11/20211109102332569j.html

  1. GitHub上霸榜久居不下的《Java面试突击宝典》,java图形用户界面设计基础
  2. GitHub上访问下载破百万的神仙文档《Java面试神技》看完我呆了,java面试问项目中遇到的问题
  3. GitHub上标星75k 超牛的《Java面试突击版,java高级工程师技能
  4. Docker development environment Preview
  5. JavaScript高級深入淺出:掌握 this 指向
  6. JavaScript Advanced Insight and outside: Mastering this direction
  7. Vue de l'application pratique de Javascript, drop drag Event
  8. docker 安装部署 Jenkins 2.322
  9. kafka安装
  10. 近九万字图文详解RabbitMQ
  11. Engaged in Java for one and a half years, how to break through yourself
  12. 输出9*9乘法表----java
  13. 判断一个数是不是素数-------java
  14. java项目,记录页面修改值,内部打“官司”用
  15. Docker installation Deployment Jenkins 2.322
  16. Comment porter un pantalon en hiver? Les petits hommes, les jambes épaisses et la largeur de l'entrejambe peuvent être vus. 3 techniques pour éviter la foudre
  17. MySQL下载和安装教程
  18. In depth analysis of rocketmq source code - message storage module
  19. Spring transaction management
  20. mysql恢复ibd数据,为何频频报错?
  21. [skills with annual salary of 60W] after working for 5 years, do you really understand netty and why to use it? (deep dry goods)
  22. Pourquoi MySQL récupère - t - il fréquemment les données ibd?
  23. Tutoriels de téléchargement et d'installation MySQL
  24. In IntelliJ idea, develop a fishing and reading plug-in
  25. Talk about how to integrate SPI with spring
  26. 重学Spring系列之Swagger2.0和Swagger3.0
  27. Hadoop05【命令操作】,Java400道面试题通关宝典助你进大厂
  28. Guava Cache缓存设计原理,java基础入门
  29. Gson:GitHub 标星 18K 的 JSON 解析器,Google 出品的 Java JSON 解析器
  30. gRPC学习之六:gRPC-Gateway集成swagger,java语言程序设计与数据结构进阶版
  31. Projet Java, enregistrer la valeur de modification de la page, utilisé pour les litiges internes
  32. Déterminer si un nombre est un nombre premier - - Java
  33. Sortie 9 * 9 tableau de multiplication - Java
  34. Plusieurs façons de réinitialiser le déplacement du Groupe de consommateurs Kafka
  35. GRPC Learning six: GRPC Gateway Integrated Swagger, Java language programming and data structure Advanced Edition
  36. python数据结构:数组、列表、栈、队列及实现
  37. Gson: json Parser for github Star 18k, Java json Parser from Google
  38. hive学习笔记之二:复杂数据类型,五分钟搞懂MySQL主从复制原理
  39. hive学习笔记之一:基本数据类型,java实现分页技术详解
  40. HDU-3038-How Many Answers Are Wrong【 带权并查集 】题解,java实战项目论坛
  41. HDU 1078 FatMouse and Cheese(记忆化搜索,DP,rocketmq教程教程
  42. Principes de conception du cache Guava, Introduction à la base Java
  43. Un pantalon beige = élégant, blanc + haut de gamme, si beau cet hiver!
  44. Hadoop05 【 commande 】, Java 400 questions d'entrevue pour vous aider à entrer dans l'usine
  45. [Java multithreading] static synchronized method and synchronized (class) code block
  46. [Java multithreading] synchronized statement block
  47. [Java multithreading] synchronized synchronization method
  48. [Java multithreading] thread priority and guard thread
  49. [Java multithreading] stop the thread
  50. Several methods of [Java multithreading] thread
  51. [Java multithreading] Java multithreading skills
  52. Structure des données Python: tableaux, listes, piles, files d'attente et implémentations
  53. Re - Learning Swagger 2.0 and Swagger 3.0 of Spring Series
  54. Error editing HTML5 using webstorm
  55. 【Spring】mybatis-spring
  56. JavaScript post request returned an exception, possibly Cross - Domain request
  57. Springboot + mybatis Plus + springsecurity + JWT implements user stateless request authentication (front and back end separation)
  58. SQL injection and XSS attack of springboot security vulnerability
  59. Docker employee readme: Why did docker lose to kubernetes?
  60. 19 high force linux commands, hurry to use them!