--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;
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;