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;
/