Detailed description of Oracle Stored Procedure return cursor instance

Source: Internet
Author: User
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;
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.