Detailed description of Oracle Stored Procedure return cursor instance

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)
Copy codeThe 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
Copy codeThe 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)
Copy codeThe 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
Copy codeThe 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
Copy codeThe 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;