Stored Procedure sharing of returned result sets in Oracle

Unlike SQL Server, Oracle uses Select to return the result set in the stored procedure, but returns the result set through Out parameters. It is actually using REF CURSOR
Copy codeThe Code is as follows:
-- 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 is a ref cursor type and is an OUT parameter. A record set is returned. USING p_id is used to replace the w_id value in the preceding SQL :)
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.

Function return record set:
Create a package and package body and function defined with ref cursor:
Copy codeThe Code is as follows:
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 keywords
Open rc for sqlstr using intid;
End if;
Return rc;
End get;
End pkg_test;