Stored Procedure of the returned result set in Oracle
OracleUnlike SQL Server, you can use select to return data in the stored procedure.ResultThe result set is returned through the out parameter. It is actually using ref cursor
-- Procedure:
-------------------- Declare a package --------------
Create or replace package pkg_test
As
TypeMyrctypeIs ref cursor;
Procedure get_r (p_id number,P_rc out myrctype); -- Declare procedure named get in package (only the interface has no content)
End pkg_test;
--------------------------------------------------------
----------------- Declare the package body, that is, the content in the package above, including procedure get ---------------------
Create or replace package body pkg_test
As
Procedure get_r (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'; -- W_id is a parameter,
-- The following p_rc files areRef cursorThe cursor type, which is an out parameter, returns a record set. Using p_id is to replace the aboveSQLMedium: w_id value pulling :)
Open p_rc for sqlstr using p_id;
End if;
End get;
End pkg_test;
-- Function returns the record set in the same principle as above. Instead, the return value of the function is used to return the record set.
FunctionReturned record set:
Create a package and package body and function defined with ref cursor:
Create or replace
Package pkg_test
Type myrctype is ref cursor;
Function get_r (intid number) return myrctype;
End pkg_test;
/
Create or replace
Package body pkg_test
-- Function body
Function get_r (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;