1. Stored procedure structure
1.1 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 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 way: 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 and becomes 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 and 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.
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 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.
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 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.
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 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 the real value is 20 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
//Let's have a look again.IN OUTWidth of parameters
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); 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
Stored procedure parameters 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
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.
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 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.
SQL> exec procdefault2(p2 =>'aa'); remark
So OK, specify aa to pass to parameter p2
- Stored Procedure Internal Block
2.1 Inner 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.
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; 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.
type t_table is table of varchar2(20) index by binary_integer;
v_student t_table; varchar2(20) denotes the data type of the element to be stored, and binary_integer denotes the data type of the element subscript.
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.
type t_nestTable is table of varchar2(20);
v_class t_nestTable ; This declaration alone is not usable. Nested tables must be initialized. Nested tables can be initialized using their constructors.
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.
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 that a type can store in the database need to initialize the initial subscript value?
Is Index Table Infinite?
Nested tables can require 1 indefinitely
Variable arrays can be limited (customized) by 1
Thus, if only used as a collection variable in the stored procedure, the index table is the best choice.
3.2 Which cursor to choose?
Display cursor can be divided into three types: ordinary cursor, parameterized cursor and cursor variable.
Here's 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, 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 Best Strategy for 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 requires looping the cursor. There are many ways to circle cursors, and we will 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; Before using the cursor, you need to open the cursor and close the cursor after the 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.
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% 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
loop
fetch c_postype into v_postype,v_description ;
exit when c_postype%notfound;
......
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.
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 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
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 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 as good as 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. In particular, the exception no_data_found ation is not serious enough to interrupt the program and 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, the procedure is still interrupted. Maybe that's not what we want.
2. Selectinto acts as a separate block for exception handling
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 it. This exception does 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 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, cursors must be used to handle it, and for the second case, internal blocks must be used to handle it and throw the exception again.
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.
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 When assigning, find multiple data');
end;
dbms_output.put_line(v_postype);
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 collection 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.
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 Data Change');
v_posTypeList.extend;
v_posTypeList(2) := PosType('A002','Group Data Change');
v_posTypeList.extend;
v_posTypeList(3) := PosType('A003','Beneficiary Changed');
v_posTypeList.extend;
v_posTypeList(4) := PosType('A004','Change of renewal mode of payment');
open p_ref_postypeList for select * from table(cast (v_posTypeList as PosTypeTable));
end;
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. 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.
create or replace type PosType as Object ( posType varchar2(20), description varchar2(50) );
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.
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;
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.
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 how jdbc handles it.
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 procedure
<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 is very similar to jdbc.
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.
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 Data Change');
v_posTypeList.extend;
v_posTypeList(2) := PosType('A002','Group Data Change');
v_posTypeList.extend;
v_posTypeList(3) := PosType('A003','Beneficiary Changed');
v_posTypeList.extend;
v_posTypeList(4) := PosType('A004','Change of renewal mode of payment');
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 Data Change');
v_posTypeList.extend;
v_posTypeList(2) := PosType('A002','Group Data Change');
v_posTypeList.extend;
v_posTypeList(3) := PosType('A003','Beneficiary Changed');
v_posTypeList.extend;
v_posTypeList(4) := PosType('A004','Change of renewal mode of payment');
return v_posTypeList;
end;
end procpkg;
ibatis configuration
<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 is written in the same way as regular queries
public List queryPostype() {
return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);
}
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.