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

记录一下实践结果，哈哈哈

原文：http://blog.csdn.net/lifang123456/article/details/39188077