The stored procedure in which Oracle calls another returned cursor in one Stored Procedure

Source: Internet
Author: User

The first case is that the returned cursor is the data of a specific table or view, for example:
SQL-code: CopyCode The Code is as follows: Create or replace procedure p_testa (
Presult out sys_refcursor
)
As
Begin
Open presult for select * from users;
End p_testa;

Users is a table in the database. You only need to declare the rowtype of the table during the call:
SQL-code:Copy codeThe Code is as follows: Create or replace procedure p_testb
As
Varcursor sys_refcursor;
R Users % rowtype;
Begin
P_testa (varcursor );
Loop
Fetch varcursor into R;
Exit when varcursor % notfound;
Dbms_output.put_line (R. Name );
End loop;
End p_testb;

In the second case, we do not return all columns of the table, but perhaps only one or two of them, for example:
SQL-code:Copy codeThe Code is as follows: Create or replace procedure p_testa (
Presult out sys_refcursor
)
As
Begin
Open presult for select ID, name from users;
End p_testa;

Here we only return the users table ID and name columns, so you must modify the values when calling them:
SQL-code:Copy codeThe Code is as follows: Create or replace procedure p_testb
As
Varcursor sys_refcursor;
Cursor tmpcursor is select ID, name from users where rownum = 1;
R tmpcursor % rowtype;
Begin
P_testa (varcursor );
Loop
Fetch varcursor into R;
Exit when varcursor % notfound;
Dbms_output.put_line (R. ID );
End loop;
End p_testb;

The difference is that we have declared a cursor type variable tmpcursor. Note that the structure of tmpcursor must be consistent with the cursor structure returned by the Stored Procedure p_testa; otherwise, an error will occur. Similarly, as long as the two cursor types have the same structure, they can be freely called.

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.