--pl/sql包,游标,存储过程,块

--1创建一个包,在该包中,定义类型my_cursor,是个游标。
create or replace package my_package as
type my_cursor is ref cursor;
end my_package;


--2创建存储过程
create or replace  procedure query_emp(deptno_in in emp.deptno%type,
c_cursor_out out my_package.my_cursor) is
begin
     open c_cursor_out for select ename,sal,job from emp where deptno=deptno_in;
end query_emp;


--3调用存储过程
declare
v_cursor my_package.my_cursor; 
c_dept  emp.deptno%type:=10;
v_ename emp.ename%type;
v_sal   emp.sal%type;
v_job   emp.job%type;
begin
  query_emp(c_dept,v_cursor);
  loop
    fetch v_cursor into v_ename,v_sal,v_job;
    exit when v_cursor%notfound;
    dbms_output.put_line('雇员:'||v_ename||'薪资:'||v_sal||'职位:'||v_job);
  end loop;
  close v_cursor;
end;