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
记录一下实践结果，哈哈哈