Oracle 存储过程返回结果集

Oracle 存储过程返回结果集

过程返回记录集： 
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 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'; 
          OPEN p_rc FOR sqlstr USING p_id; 
       END IF; 
    END get; 
END pkg_test; 
/ 
  
函数返回记录集: 
建立带ref cursor定义的包和包体及函数： 
CREATE OR REPLACE 
package pkg_test as 
/* 定义ref cursor类型 
    不加return类型，为弱类型，允许动态sql查询， 
    否则为强类型，无法使用动态sql查询; 
*/ 
   type myrctype is ref cursor;  
   
--函数申明 
   function get(intID number) return myrctype; 
end pkg_test; 
/ 
   
CREATE OR REPLACE 
package body pkg_test as 
--函数体 
    function get(intID number) return myrctype is 
      rc myrctype;  --定义ref cursor变量 
      sqlstr varchar2(500); 
    begin 
      if intID=0 then 
         --静态测试，直接用select语句直接返回结果 
         open rc for select id,name,sex,address,postcode,birthday from student; 
      else 
         --动态sql赋值，用:w_id来申明该变量从外部获得 
         sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; 
         --动态测试，用sqlstr字符串返回结果，用using关键词传递参数 
         open rc for sqlstr using intid; 
      end if; 
   
      return rc; 
    end get; 
   
end pkg_test; 
/