Oracle存储过程-返回一个查询的结果集

创建一个返回select结果集的存储过程： 

--创建procedure

create or replace procedure pro_emp(resule out sys_refcursor) is
begin
  open resule for select * from emp;
end;

 调用：

--调用
declare
  cur1   SYS_REFCURSOR;
  result_row      emp%rowtype;
begin

 pro_emp(cur1);

  loop
    fetch cur1
      into result_row;
    exit when cur1%notfound;

    dbms_output.put_line('员工姓名:' || result_row.ename);
  end loop;

  close cur1;

end;

打印输出结果：

员工姓名:SMITH
员工姓名:ALLEN
员工姓名:WARD
员工姓名:JONES
员工姓名:MARTIN
员工姓名:BLAKE
员工姓名:CLARK
员工姓名:SCOTT
员工姓名:KING
员工姓名:TURNER
员工姓名:ADAMS
员工姓名:JAMES
员工姓名:FORD
员工姓名:MILLER