Oracle-PLSQL Stored Procedure cursor when output parameter

Oracle-PLSQL Stored Procedure cursor when output parameter

Baotou:

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;

Package body:

Create or replace package body ProdureceCursorData is

PROCEDURE Procedure1 (cur out curtype)
As
Begin
Open cur for select * from DEPT;
End;

End ProdureceCursorData;

Test:

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

Record the practice results, hahaha