Oracle-PLSQL存儲過程游標當出參

Oracle-PLSQL存儲過程游標當出參

包頭:

create or replace package ProdureceCursorData is

type curtype is ref cursor;
type type_record is record
(
deptno NUMBER(2) ,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
PROCEDURE Procedure1(cur out curtype);

end ProdureceCursorData;

包體:

create or replace package body ProdureceCursorData is

PROCEDURE Procedure1(cur out curtype)
as
begin
open cur for select * from DEPT;
end;

end ProdureceCursorData;

測試:

SQL> select * from DEPT;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> set serveroutput on
SQL> declare
2 curoutarg ProdureceCursorData.curtype;
3 rec_arg ProdureceCursorData.type_record;
4 begin
5 dbms_output.put_line('------------------------');
6 ProdureceCursorData.Procedure1(curoutarg);
7 loop
8 fetch curoutarg into rec_arg;
9 exit when curoutarg%notfound;
10 dbms_output.put_line(rec_arg.deptno||' '||rec_arg.dname||' '||rec_arg.loc);
11 end loop;
12 end;
13 /
------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
PL/SQL procedure successfully completed

記錄一下實踐結果，哈哈哈