自定义类型create or replace type myRow is object (id int,name varchar(20));create or replace type myTable is table of myRow;create or replace procedure testProcedure(p_mytable out myTable) as v_myrow myRow := myRow(null,null);
SQL> create or replace function f_test return dbms_sql.Varchar2_Table as 2 v_ename_table dbms_sql.Varchar2_Table; 3 begin 4 select ename bulk collect into v_ename_table from emp; 5 return v_ename_table; 6 end; 7 /
Function created
SQL> SQL> set serveroutput on; SQL> declare 2 v_ename_table dbms_sql.Varchar2_Table; 3 begin 4 v_ename_table := f_test; 5 for i in 1..v_ename_table.count loop 6 dbms_output.put_line(v_ename_table(i)); 7 end loop; 8 end; 9 /
SMIT H1 ALLE N WARD JONE S MART IN BLAK E CLAR K SCOT T KING TURN ER ADAM S JAME S FORD MILL ER
PL/SQL procedure successfully completed
在procedure中用一个cursor返回需要的结果集,设置一个标记变量,用于fetch 该cursor时记录cursor的状态。将每一次fetch的内容和标记变量同时传出。 在应用程序中循环调用该procedure,通过返回的标记变量判断是否退出该循环。 大致过程如下:create or replace procedure test(id IN INT, cont OUT VARCHAR2, flg OUT CHAR) IS CURSOR test_cur(v_id) IS SELECT ... flg := '0'; v_record test_cur%TYPE; begin IF test_cur IS NOT OPEN THEN OPEN test_cur(id); END IF; IF test_cur IS OPEN THEN FETCH test_cur INTO v_record
cont := v_record.... IF test_cur NOT FOUND THEN flg:='1'; END IF; END IF; end test; DECLARE flg CHAR; cont VARCHAR2; BEGIN LOOP test(1,cont,flg); ... --deal with cont EXIT WHEN flg='1'; END LOOP;END;
as
v_myrow myRow := myRow(null,null);
begin
p_mytable := myTable(); v_myrow.id := 100;
v_myrow.name := 'a';
p_mytable.extend;
p_mytable(p_mytable.count) := v_myrow; v_myrow.id := 200;
v_myrow.name := 'b';
p_mytable.extend;
p_mytable(p_mytable.count) := v_myrow; v_myrow.id := 300;
v_myrow.name := 'c';
p_mytable.extend;
p_mytable(p_mytable.count) := v_myrow;
end testProcedure;
SQL> create or replace function f_test return dbms_sql.Varchar2_Table as
2 v_ename_table dbms_sql.Varchar2_Table;
3 begin
4 select ename bulk collect into v_ename_table from emp;
5 return v_ename_table;
6 end;
7 /
Function created
SQL>
SQL> set serveroutput on;
SQL> declare
2 v_ename_table dbms_sql.Varchar2_Table;
3 begin
4 v_ename_table := f_test;
5 for i in 1..v_ename_table.count loop
6 dbms_output.put_line(v_ename_table(i));
7 end loop;
8 end;
9 /
SMIT H1
ALLE N
WARD
JONE S
MART IN
BLAK E
CLAR K
SCOT T
KING
TURN ER
ADAM S
JAME S
FORD
MILL ER
PL/SQL procedure successfully completed
在应用程序中循环调用该procedure,通过返回的标记变量判断是否退出该循环。
大致过程如下:create or replace procedure test(id IN INT, cont OUT VARCHAR2, flg OUT CHAR) IS
CURSOR test_cur(v_id) IS
SELECT ...
flg := '0';
v_record test_cur%TYPE;
begin
IF test_cur IS NOT OPEN THEN
OPEN test_cur(id);
END IF; IF test_cur IS OPEN THEN
FETCH test_cur
INTO v_record
cont := v_record.... IF test_cur NOT FOUND THEN
flg:='1';
END IF; END IF;
end test;
DECLARE
flg CHAR;
cont VARCHAR2;
BEGIN
LOOP
test(1,cont,flg);
... --deal with cont
EXIT WHEN flg='1';
END LOOP;END;