Stored Procedure of the returned result set in Oracle

The stored procedure for returning a result set in Oracle, Unlike SQL Server, In the stored procedure, Oracle uses Select to return a result set. Instead, it uses Out-type parameters to return a result set. It is actually using REF CURSOR
-- Procedure returned record set: ---------------------- declare a Package -------------- create or replace package pkg_testASTYPEmyrctypeIS 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 Package Body, that is, the content in the Package above, including Procedure get --------------------- www.2cto.com create or replace package body pkg_testASPROCEDURE get_r (p_id NUMBER, p_rc OUT Myrctype) ISsqlstr VARCHAR2 (500); BEGINIF p_id = 0 THENOPEN p_rc forselect id, NAME, sex, address, postcode, birthdayFROM student; ELSEsqlstr: = 'select id, name, sex, address, postcode, birthdayfrom student where id =: w_id '; -- w_id is a parameter. -- The following p_rc is a ref cursor type and an OUT parameter, returns a record set. USING p_id is an example of returning a record set from the preceding SQL statement: w_id value pulling: OPEN p_rc FOR sqlstr USING p_id; END IF; END get; END pkg_test; www.2cto.com -- function, the principle is the same as above, but the return value of function is used to return the record set.
Function return record set: CREATE a package and package with ref cursor defined and function: create or REPLACEpackage pkg_test as type myrctype is ref cursor; function get_r (intID number) return myrctype; end pkg_test; www.2cto.com/create or REPLACEpackage body pkg_test as -- function body function get_r (intID number) return myrctype isrc myrctype; -- Define the ref cursor variable sqlstr varchar2 (500 ); begin www.2cto.com if intID = 0 then
-- Static Test: directly return the results of open rc for select id, name, sex, address, postcode, birthday from student; else -- dynamic SQL assignment using the following statement: w_id to declare that the variable is obtained from the external sqlstr: = 'select id, name, sex, address, postcode, birthday from student where id =: w_id '; -- dynamic test, return results with sqlstr string, use using keywords to pass the parameter open rc for sqlstr using intid; end if; return rc; end get; end pkg_test; this article is from the site