|
提供几种返回结果集的方法
创建示例表
[php]
create table tb_test
as
select cast(level as number(4)) as id,
cast(dbms_random.string('u', 8) as char(8)) as val
from dual
connect by level<5;
SQL> select * from tb_test;
ID VAL
---------- --------
1 JGSLQQQY
2 BLIAELER
3 WSXAJVHQ
4 FLTGINNS
4 rows selected
-------------
[/php]
创建返回结果集的示例package pkg_getRecords
[php]
CREATE OR REPLACE PACKAGE pkg_getRecords
IS
TYPE tCur IS REF CURSOR;
TYPE tRec1 IS table of tb_test%rowtype;
TYPE tRec2 IS table of tb_test%rowtype INDEX BY BINARY_INTEGER;
function f_GetCursor return tCur;
function f_GetRecord1 return tRec1;
function f_GetRecord2 return tRec2;
procedure p_GetRecords(pCur out tCur, pRec1 out tRec1, pRec2 out tRec2);
END pkg_getRecords;
/
CREATE OR REPLACE PACKAGE BODY pkg_getRecords
IS
FUNCTION f_GetCursor RETURN tCur
IS
cur tCur;
BEGIN
OPEN cur FOR
SELECT * FROM tb_test;
RETURN cur;
END;
FUNCTION f_GetRecord1 RETURN tRec1
IS
rec1 tRec1 := tRec1();
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM tb_test'
bulk collect into rec1;
RETURN rec1;
END;
FUNCTION f_GetRecord2 RETURN tRec2
IS
rec2 tRec2;
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM tb_test'
bulk collect into rec2;
RETURN rec2;
END;
PROCEDURE p_GetRecords(pCur out tCur, pRec1 out tRec1, pRec2 out tRec2)
AS
BEGIN
OPEN pCur FOR
SELECT * FROM tb_test;
EXECUTE IMMEDIATE 'SELECT * FROM tb_test'
bulk collect into pRec1;
EXECUTE IMMEDIATE 'SELECT * FROM tb_test'
bulk collect into pRec2;
END;
END pkg_getRecords;
/
-----------
[/php]
上面创建完所需的table和package, 下面看看package中function和procedure的调用和返回的结果集(提示: procedure中有三个out参数, 都是结果集)
[php]
SQL> set serveroutput on
SQL>
SQL>
SQL> declare
2 cur pkg_getRecords.tCur;
3 vrow tb_test%rowtype;
4 rec1 pkg_getRecords.tRec1;
5 rec2 pkg_getRecords.tRec2;
6 begin
7 cur := pkg_getRecords.f_GetCursor;
8
9 dbms_output.put_line('Output data of the cursor...');
10 LOOP
11 fetch cur into vrow;
12 EXIT WHEN cur%NOTFOUND;
13 dbms_output.put_line('id='||vrow.id||' & val='||vrow.val);
14 END LOOP;
15
16 rec1 := pkg_getRecords.f_GetRecord1;
17
18 dbms_output.put_line('Output data of the record1...');
19 for i in 1..rec1.count loop
20 dbms_output.put_line('id='||rec1(i).id||' & val='||rec1(i).val);
21 end loop;
22
23 rec2 := pkg_getRecords.f_GetRecord2;
24
25 dbms_output.put_line('Output data of the record2...');
26 for i in 1..rec1.count loop
27 dbms_output.put_line('id='||rec2(i).id||' & val='||rec2(i).val);
28 end loop;
29
30 -- execute procedure p_GetRecords to get cur, rec1 & rec2
31 pkg_getRecords.p_GetRecords(cur, rec1, rec2);
32
33 dbms_output.put_line('Output data of the cursor...');
34 LOOP
35 fetch cur into vrow;
36 EXIT WHEN cur%NOTFOUND;
37 dbms_output.put_line('id='||vrow.id||' & val='||vrow.val);
38 END LOOP;
39
40 dbms_output.put_line('Output data of the record1...');
41 for i in 1..rec1.count loop
42 dbms_output.put_line('id='||rec1(i).id||' & val='||rec1(i).val);
43 end loop;
44
45 dbms_output.put_line('Output data of the record2...');
46 for i in 1..rec1.count loop
47 dbms_output.put_line('id='||rec2(i).id||' & val='||rec2(i).val);
48 end loop;
49
50 end;
51 /
Output data of the cursor...
id=1 & val=JGSLQQQY
id=2 & val=BLIAELER
id=3 & val=WSXAJVHQ
id=4 & val=FLTGINNS
Output data of the record1...
id=1 & val=JGSLQQQY
id=2 & val=BLIAELER
id=3 & val=WSXAJVHQ
id=4 & val=FLTGINNS
Output data of the record2...
id=1 & val=JGSLQQQY
id=2 & val=BLIAELER
id=3 & val=WSXAJVHQ
id=4 & val=FLTGINNS
Output data of the cursor...
id=1 & val=JGSLQQQY
id=2 & val=BLIAELER
id=3 & val=WSXAJVHQ
id=4 & val=FLTGINNS
Output data of the record1...
id=1 & val=JGSLQQQY
id=2 & val=BLIAELER
id=3 & val=WSXAJVHQ
id=4 & val=FLTGINNS
Output data of the record2...
id=1 & val=JGSLQQQY
id=2 & val=BLIAELER
id=3 & val=WSXAJVHQ
id=4 & val=FLTGINNS
PL/SQL procedure successfully completed
-----------
[/php] |
|