15 end loop;
16 close emp_cur;
17* END;
18 /
Enter value for inputno: 10
old 8: v_deptno:=&inputno;
new 8: v_deptno:=10;
Name Salary
CLARK 2450
KING 5000
MILLER 1300
--如果REF CURSOR指定RETURN子句的数据列于select子句的数据列不一致将收到如下的错误提示信息
scott@ORCL> start /u01/bk/scripts/emp_cur13.sql
Enter value for inputno: 10
old 8: v_deptno:=&inputno;
new 8: v_deptno:=10;
open emp_cur for select ename,sal,job from emp where deptno=v_deptno; --多出了一列
*
ERROR at line 9:
ORA-06550: line 9, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored
--例：游标变量的多次使用
scott@ORCL> get /u01/bk/scripts/emp_cur14.sql
1 DECLARE
3 scott_cur cur_type;
4 v_emp emp%rowtype;
5 v_dept dept%rowtype;
6 BEGIN
7 open scott_cur for select * from emp where deptno=10; --使用for select首次打开游标
8 dbms_output.put_line('No, Name');
9 loop
10 fetch scott_cur into v_emp;
11 exit when scott_cur%notfound;
12 dbms_output.put_line(scott_cur%rowcount||','||v_emp.ename);
13 end loop;
14 open scott_cur for select * from dept where deptno=10;--使用for select 再此打开游标，此次加载了不同数据
15 dbms_output.put_line('Deptno, Name');
16 loop
17 fetch scott_cur into v_dept;
18 exit when scott_cur%notfound;
19 dbms_output.put_line(v_dept.deptno||','||v_dept.dname);
20 end loop;
21* END;
22 /
No, Name
1,CLARK
2,KING
3,MILLER
Deptno, Name
10,ACCOUNTING