Why is it so hard for Oracle stored procedures to return result sets?

Source: Internet
Author: User

From: http://www.cnblogs.com/attraction/archive/2004/06/04/13489.aspx

 

Stored Procedure return record set:
Create or replace package pkg_test
As
Type myrctype is ref cursor;

Procedure get (p_id number, p_rc out myrctype );
End pkg_test;
/

Create or replace package body pkg_test
As
Procedure get (p_id number, P_rc out myrctype )
Is
Sqlstr varchar2 (500 );
Begin
If p_id = 0 then
Open p_rc
Select ID, name, sex, address, postcode, birthday
From student;
Else
Sqlstr: =
'Select ID, name, sex, address, postcode, birthday
From student where id =: w_id ';
Open p_rc for sqlstr using p_id;
End if;
End get;
End pkg_test;
/

Function return record set:
Create a package and package body and function defined with ref cursor:
Create or replace
Package pkg_test
/* Define the ref cursor type
The return type is not added. The return type is weak. dynamic SQL queries are allowed,
Otherwise, it is strongly typed and cannot be queried using dynamic SQL;
*/
Type myrctype is ref cursor;

-- Function declaration
Function get (intid number) Return myrctype ;
End pkg_test;
/

Create or replace
Package body pkg_test
-- Function body
Function get (intid number) return myrctype is
RC myrctype; -- defines the ref cursor variable
Sqlstr varchar2 (500 );
Begin
If intid = 0 then
-- The static test directly returns the result using the SELECT statement.
Open RC for select ID, name, sex, address, postcode, birthday from student;
Else
-- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
Sqlstr: = 'select ID, name, sex, address, postcode, birthday from student where id =: w_id ';
-- Dynamic Test: return results using sqlstr strings and PASS Parameters Using using keywords
Open RC for sqlstr using intid;
End if;

Return RC;
End get;

End pkg_test;
/

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.