ITPub博客

首页 > 应用开发 > IT综合 > REF CURSOR 小结 (3)

REF CURSOR 小结 (3)

原创 IT综合 作者:zhyuh 时间:2005-10-27 11:28:23 0 删除 编辑
使用Weak REF CURSOR例子[@more@]

--procedure open_cv---------------------------------------

create or replace procedure open_cv(choice IN INT,
return_cv OUT SYS_REFCURSOR) is
--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义
begin
if choice = 1 then
open return_cv for 'select * from emp';
elsif choice = 2 then
open return_cv for 'select * from dept';
end if;
end open_cv;

--procedure retrieve_data------------------------------------

create or replace procedure retrieve_data(choice IN INT) is
emp_rec emp%rowtype;
dept_rec dept%rowtype;
return_cv SYS_REFCURSOR;
invalid_choice exception;
begin
if choice=1 then
dbms_output.put_line('employee information');
open_cv(1,return_cv); --调用procedure open_cv;
loop
fetch return_cv into emp_rec;
exit when return_cv%notfound;
dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);
end loop;
elsif choice=2 then
dbms_output.put_line('department information');
open_cv(2,return_cv);
loop
fetch return_cv into dept_rec;
exit when return_cv%notfound;
dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);
end loop;
else
raise invalid_choice;
end if;

exception
when invalid_choice then
dbms_output.put_line('The CHOICE should be one of 1 and 2!');
when others then
dbms_output.put_line('Errors in procedure retrieve_data');
end retrieve_data;

-----------------------------------------------------------------

执行:
SQL> exec retrieve_data(1);
employee information
7369-SMITH-800
7499-ALLEN-1600
7521-WARD-1250
7566-JONES-2975
7654-MARTIN-1250
7698-BLAKE-2850
......
PL/SQL procedure successfully completed

SQL> exec retrieve_data(2);
department information
10-ACCOUNTING-NEW YORK
20-RESEARCH-DALLAS
30-SALES-CHICAGO
40-OPERATIONS-BOSTON
PL/SQL procedure successfully completed

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-808723/,如需转载,请注明出处,否则将追究法律责任。

上一篇: REF CURSOR 小结 (2)
下一篇: REF CURSOR 小结 (4)
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2014074