存储过程&函数返回结果集
--创建测试表create table t01(id integer,name varchar2(10));--创建测试数据insert into t01(id,name) values (1,'a');insert into t01(id,name) values (2,'b');insert into t01(id,name) values (3,'c');--提交commit;--查询表的数据select * from t01;--创建返回结果集的存储过程(系统类型)create or replace procedure p_getdatas(v_cur out sys_refcursor)asbegin open v_cur for select id,name from t01;end;--测试结果的正确性declare id t01.id%type;name t01.name%type;v_cur sys_refcursor;begin p_getdatas(v_cur); loop fetch v_cur into id,name; exit when v_cur%notfound; dbms_output.put_line(name); end loop; close v_cur;end;--创建返回结果的函数(系统类型)create or replace function f_getdatasreturn sys_refcursoras v_cur sys_refcursor;begin open v_cur for select id,name from t01; return v_cur;end;--测试结果的正确性select f_getdatas from dual;--定义包(用户自定义类型)create or replace package pk_getdatasas type t_cur is ref cursor; procedure p_result(v_cur out pk_getdatas.t_cur); function f_result return pk_getdatas.t_cur;end;--创建包(用户自定义类型)create or replace package body pk_getdatasas procedure p_result(v_cur out pk_getdatas.t_cur) as begin open v_cur for select id,name from t01; end; function f_result return pk_getdatas.t_cur as v_cur pk_getdatas.t_cur; begin open v_cur for select id,name from t01; return v_cur; end;end;--测试结果的正确性declare id t01.id%type;name t01.name%type;v_cur pk_getdatas.t_cur;begin p_getdatas(v_cur); loop fetch v_cur into id,name; exit when v_cur%notfound; dbms_output.put_line(name); end loop; close v_cur;end;--测试结果的正确性select pk_getdatas.f_result from dual;
