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

--创建测试表
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)
as
begin
  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_getdatas
return sys_refcursor
as
  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_getdatas
as
  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_getdatas
as
  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;