ITPub博客

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

REF CURSOR 小结 (2)

原创 IT综合 作者:zhyuh 时间:2000-01-01 00:00:35 0 删除 编辑
使用Strong REF CURSOR例子[@more@]

CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR
PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选择不同的CURSOR
PROCEDURE retrieve_data(choice INT); --通过调用procedure open_emp_cv,返回指定的结果集。
END emp_data;

=============================================

CREATE OR REPLACE PACKAGE BODY emp_data AS

--procedure open_emp_cv--------------------------------------------------------

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS --emp_cv作为传入/传出的CURSOR PARAMETER
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE empno < 7800;
ELSIF choice = 2 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE SAL < 1000;
ELSIF choice = 3 THEN
OPEN emp_cv FOR
SELECT * FROM emp WHERE ename like 'J%';
END IF;
END;

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

PROCEDURE retrieve_data(choice INT) IS
return_cv empcurtyp; --定义传入open_emp_cv的CURSOR变量
return_row emp%ROWTYPE;
invalid_choice EXCEPTION;
BEGIN
open_emp_cv(return_cv, choice); --调用 procedure OPEN_EMP_CV

IF choice = 1 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less than 7800');
ELSIF choice = 2 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');
ELSIF choice = 3 THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');
ELSE
RAISE invalid_choice;
END IF;

LOOP
FETCH return_cv
INTO return_row;
EXIT WHEN return_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||
return_row.sal);
END LOOP;

EXCEPTION
WHEN invalid_choice THEN
DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');
END;

END emp_data;

=================================================

执行:
SQL> EXEC emp_data.retrieve_data(1);
EMPLOYEES with empno less than 7800
7369--SMITH--800
7499--ALLEN--1600
7521--WARD--1250
7566--JONES--2975
7654--MARTIN--1250
7698--BLAKE--2850
7782--CLARK--2450
7788--SCOTT--3000
PL/SQL procedure successfully completed

SQL> EXEC emp_data.retrieve_data(2);
EMPLOYEES with salary less than 1000
7369--SMITH--800
7900--JAMES--950
PL/SQL procedure successfully completed

SQL> EXEC emp_data.retrieve_data(3);
EMPLOYEES with name starts with 'J'
7566--JONES--2975
7900--JAMES--950
PL/SQL procedure successfully completed

SQL> EXEC emp_data.retrieve_data(34);
The CHOICE should be in one of (1,2,3)!
PL/SQL procedure successfully completed

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

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