存储过程&函数返回结果集

--创建测试表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;