《Oracle 存储过程返回结果集》由会员分享,可在线阅读,更多相关《Oracle 存储过程返回结果集(2页珍藏版)》请在金锄头文库上搜索。
1、Oracle 存储过程返回结果集*过程返回记录集:CREATE OR REPLACE PACKAGE pkg_testASTYPE myrctype IS REF CURSOR;PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);END pkg_test;/CREATE OR REPLACE PACKAGE BODY pkg_testASPROCEDURE get (p_id NUMBER, p_rc OUT myrctype)ISsqlstr VARCHAR2 (500);BEGINIF p_id = 0 THENOPEN p_rc FORSELEC
2、T ID, NAME, sex, address, postcode, birthdayFROM student;ELSEsqlstr :=select id,name,sex,address,postcode,birthdayfrom student where id=:w_id;OPEN p_rc FOR sqlstr USING p_id;END IF;END get;END pkg_test;/函数返回记录集:建立带 ref cursor 定义的包和包体及函数:CREATE OR REPLACEpackage pkg_test as/* 定义 ref cursor 类型不加 retur
3、n 类型,为弱类型,允许动态 sql 查询,否则为强类型,无法使用动态 sql 查询;*/type myrctype is ref cursor;-函数申明function get(intID number) return myrctype;end pkg_test;/CREATE OR REPLACEpackage body pkg_test as-函数体function get(intID number) return myrctype isrc myrctype; -定义 ref cursor 变量sqlstr varchar2(500);beginif intID=0 then-静态测
4、试,直接用 select 语句直接返回结果open rc for select id,name,sex,address,postcode,birthday fromstudent;else-动态 sql 赋值,用:w_id 来申明该变量从外部获得sqlstr := select id,name,sex,address,postcode,birthday fromstudent where id=:w_id;-动态测试,用 sqlstr 字符串返回结果,用 using 关键词传递参数open rc for sqlstr using intid;end if;return rc;end get;end pkg_test;