Detailed description of Oracle Stored Procedure return cursor instance

There are two implementation methods for returning a cursor from an Oracle stored procedure. One is to declare a system cursor and the other is to declare a custom cursor. This article will introduce it in detail. For more information, see

There are two implementation methods for returning a cursor from an Oracle stored procedure. One is to declare a system cursor and the other is to declare a custom cursor. This article will introduce it in detail. For more information, see

There are two methods:
One is to declare the system cursor, the other is to declare the custom cursor, and then follow the same operation, the parameter type is
In out or out
(1) Declare the personal system cursor. (recommended)
The Code is as follows:
Create or replace p_temp_procedure
(
Cur_arg out sys_refcursor; -- Method 1
)
Begin
Open cur_arg for select * from tablename;
End

Call
The Code is as follows:
Declare
Cur_calling sys_refcursor;
Begin
P_temp_procedure (cur_calling); -- in this way, the cursor has a value.
For rec_next in cur_calling loop
....
End loop;
End;

(2) declare a game table type in the header, and then the caller declares a cursor variable of this type to pass it to the stored procedure that returns the cursor. The stored procedure is out of this result set, this method is very troublesome. the cursor type cannot be created using the create or replace type method like the index table. Therefore, the cursor type can only be declared in the package and must be executed using/. The subsequent Stored Procedure recognizes the cursor type. (not recommended, but it is recommended to know and use this method. After all, it has its own principle)
The Code is as follows:
-- Define global variables
Create or replace package pkg_package
As
Type type_cursor is ref cursor;
Type type_record is record
(
Test01 varchar2 (32 ),
Test02 varchar2 (32 ),
Test03 varchar2 (32)
);
End;

-- Create a stored procedure for the returned cursor
The Code is as follows:
Create or replace procedure p_temp_procedure
(
Cur_out_arg out pkg_package.type_cursor
)
Is
Begin
Open cur_out_arg for select * from test;
End;

-- Call
The Code is as follows:
Declare
Cur_out_arg pkg_package.type_cursor;
Rec_arg pkg_package.type_record;
Begin
P_temp_procedure (cur_out_arg );
Fetch cur_out_arg into rec_arg;
Dbms_output.put_line (rec_arg.test01 );
Dbms_output.put_line (rec_arg.test02 );
Dbms_output.put_line (rec_arg.test03 );
End;