Oracle 游标-引用游标

SQL> set severoutput on;
SP2-0735: unknown SET option beginning "severoutpu..."
SQL> set serveroutput on;
SQL> REMARK 引用游标
SQL> REMARK
SQL> REMARK .........
SQL> REMARK 引用游标:无法在游标声明的时候就确定游标的查询语句，需要根据业务逻辑过程确定查询，引用游标就是拥有这个灵活性；
SQL> REMARK 弱类型引用游标的案例：
SQL> DECLARE
2 TYPE CUREMPTYPE IS REF CURSOR;
3 CUREMP_REF CUREMPTYPE;
4 COMMAND CHAR(1);
5 CURROW EMP%ROWTYPE;
6 THEENAME EMP.ENAME%TYPE;
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE('请输入操作命令('||'1-查找DEPTNO=30员工的姓名，'||'其它查找job=manager的员工信息');
9 COMMAND:=&m命令;
10 IF COMMAND='1' THEN
11 DBMS_OUTPUT.PUT_LINE('输入命令1----');
12 OPEN CUREMP_REF FOR SELECT EMPNO FROM EMP WHERE DEPTNO=30;
13 LOOP
14 FETCH CUREMP_REF INTO THEENAME;
15 EXIT WHEN CUREMP_REF%NOTFOUND;
16 DBMS_OUTPUT.PUT_LINE(THEENAME);
17 END LOOP;
18 CLOSE CUREMP_REF;
19 ELSE
20 DBMS_OUTPUT.PUT_LINE('输入的命令是：2...');
21 OPEN CUREMP_REF FOR SELECT * FROM EMP WHERE JOB='MANAGER';
22 LOOP
23 FETCH CUREMP_REF INTO CURROW;
24 EXIT WHEN CUREMP_REF%NOTFOUND;
25 DBMS_OUTPUT.PUT_LINE('编号：'||CURROW.EMPNO);
26 END LOOP;
27 END IF;
28 END;
29 /

Enter value for m命令: 2
old 9: COMMAND:=&m命令;
new 9: COMMAND:=2;
请输入操作命令1-查找DEPTNO=30员工的姓名，其它查找job=manager的员工信息
输入的命令是：2...
编号：7698
编号：7782

PL/SQL procedure successfully completed.

SQL> REMARK 强类型游标的定义
SQL> DECLARE
2 TYPE CUREMPTYPE IS REF CURSOR RETURN EMP%ROWTYPE;
3 CUREMP_REF CUREMPTYPE;
4 COMMAND CHAR(1);
5 CURROW EMP%ROWTYPE;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE('请输入操作指令：1 和 2');
8 COMMAND :=&命令;
9 IF COMMAND='1' THEN
10 DBMS_OUTPUT.PUT_LINE('1.....');
11 OPEN CUREMP_REF FOR SELECT * FROM EMP WHERE DEPTNO=30;
12 LOOP
13 FETCH CUREMP_REF INTO CURROW;
14 EXIT WHEN CUREMP_REF%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE('编号：'||CURROW.EMPNO||'姓名：'||CURROW.ENAME);
16 END LOOP;
17 CLOSE CUREMP_REF;
18 ELSE
19 DBMS_OUTPUT.PUT_LINE('2...');
20 OPEN CUREMP_REF FOR SELECT * FROM EMP WHERE JOB ='MANAGER';
21 LOOP
22 FETCH CUREMP_REF INTO CURROW;
23 EXIT WHEN CUREMP_REF%NOTFOUND;
24 DBMS_OUTPUT.PUT_LINE('编号：'||CURROW.EMPNO||'姓名：'||CURROW.ENAME);
25 END LOOP;
26 CLOSE CUREMP_REF;
27 END IF;
28 END;
29 /
Enter value for 命令: 1
old 8: COMMAND :=&命令;
new 8: COMMAND :=1;
请输入操作指令：1 和 2
1.....
编号：7499姓名：ALLEN
编号：7521姓名：WARD
编号：7654姓名：MARTIN
编号：7698姓名：BLAKE
编号：7844姓名：TURNER
编号：7900姓名：JAMES

PL/SQL procedure successfully completed.

SQL> SPOOL OFF;
[color=olive][/color][size=medium][/size]