Shared _oracle of stored procedures that return result sets in Oracle

Instead of using a select in a stored procedure like SQL Server, Oracle can return a result set, which is returned by a result set of an out type parameter. is actually using ref CURSOR

Copy Code code as follows:

--procedure returns the recordset:
----------------------Declare a package--------------
CREATE OR REPLACE PACKAGE pkg_test
As
Typemyrctypeis REF CURSOR;

PROCEDURE Get_r (p_id number,p_rc out myrctype); Procedure (only interface has no content) declared in--package
End Pkg_test;

-----------------Declare package body, the contents of 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 for
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 is a REF CURSOR cursor type, and is an out parameter, you can return a recordset. USING p_id is to replace the above SQL: w_id value Pull:)
OPEN P_RC for sqlstr USING p_id;
End IF;
End get;
End Pkg_test;

--function Returns an example of a recordset, which is the same as the above, but returns the recordset using the return value of the function.

function returns the recordset:
Create packages and packages and functions with REF CURSOR definitions:

Copy Code code as follows:

CREATE OR REPLACE
Package pkg_test as

type myrctype is ref c Ursor;
Function Get_r (intID number) return myrctype;
End Pkg_test;
/
CREATE OR REPLACE
Package Body pkg_test as
--function bodies
function Get_r (intID number) return Myrctyp E is
RC Myrctype;--Define REF CURSOR variable
SQLSTR VARCHAR2 (500);
Begin
If Intid=0 then

-static testing, directly returning results directly with a SELECT statement
Open RC for select Id,name,sex,address,postcode , birthday from student;
Else
--Dynamic SQL assignment, using: w_id to declare that the variable is obtained externally
sqlstr: = ' Select Id,name,sex,address,postcode,birthday from student wher e id=:w_id ';
--Dynamic test, return result with SQLSTR string, pass parameter with using keyword
Open rc for sqlstr using IntID;
End If;
return RC;
End Get;
End Pkg_test;