??????????? DECLARE
??????????? *
??????????? ERROR at line 1:
??????????? ORA-00054: resource busy and acquire with NOWAIT specified
??????????? ORA-06512: at line 5
??????????? ORA-06512: at line 7
??????
四、游标FOR循环
??? 游标FOR循环是为了简化游标使用过程而设计的。使用游标FOR循环检索游标时，游标的打开、数据提取、数据是否检索到的判断与游标
??? 的关闭都是ORACLE系统自动进行的。
?
??? 游标FOR循环两种语句格式：
??????? 格式一：
??????????? 先在定义部分定义游标，然后在游标FOR循环中引用该游标
???? ??????
??????????? FOR record_name IN cursor_name LOOP
??????????????? statement1;
??????????????? statement2;
??????????? END LOOP;
?
??????? 格式二：
??????????? 在FOR循环中直接引用子查询，隐式定义游标
?
??????????? FOR record_name IN subquery LOOP
??????????????? statement;
??????????? END LOOP;
?
??? --例：定义游标并使用for循环逐个显示记录
??
??????? DECLARE
??????????? v_job emp.job%TYPE;
??????????? CURSOR emp_cur IS SELECT ename,sal FROM emp WHERE job=v_job;
??????? BEGIN
??????????? v_job:='&inputjob';
??????????? DBMS_OUTPUT.PUT_LINE('NO.???? Name?????? Sal');
??????????? FOR emp_record IN emp_cur LOOP
??????????????? DBMS_OUTPUT.PUT_LINE(emp_cur%ROWCOUNT||'??? '||
??????????????? emp_record.ename||'??? '||emp_record.sal);
??????????? END LOOP;
??????? END;
??????? /
?
??????? scott@ORCL> start /u01/bk/scripts/emp_cur10.sql
??????? Enter value for inputjob: SALESMAN
??????? old?? 5:??????????????????????? v_job:='&inputjob';
??????? new?? 5:??????????????????????? v_job:='SALESMAN';
??????? NO.???? Name?????? Sal
??????? 1??? ALLEN??? 1600
??????? 2??? WARD??? 1250
??????? 3??? MARTIN??? 1250
??????? 4??? TURNER??? 1500
??????
??? --例：直接在游标for循环中使用子查询来逐个显示记录
??????????? v_job emp.job%TYPE;
??????? BEGIN
??????????? v_job:='&inputjob';
??????????? DBMS_OUTPUT.PUT_LINE('Name???? Sal');
??????????? FOR emp_record IN (SELECT ename,sal FROM emp WHERE job=v_job) LOOP
??????????????? DBMS_OUTPUT.PUT_LINE(emp_record.ename||'??? '
??????????????????? ||emp_record.sal);
??????????? END LOOP;
??????? END;
??? ????/
?
五、参数游标
??? 参数游标是指带有参数的游标。当定义了参数游标后，使用不同的参数值多次打开游标则会生成不同的结果集。
??? 定义参数游标：
??
??????? CURSOR cursor_name (para_name1 datatype [,para_name2 datatype ...])
??????? IS select_statement;
?
??? 注：datatype 只指定数据类型即可，不能指定参数的长度、精度、刻度
?
??? 打开参数游标：
??????? OPEN cursor_name [(vlaues)]
?
??????? 参数个数、类型必须与定义时的形参相匹配。
??????? 对于定义的参数游标，一定要在游标子查询的where子句中指定定义的参数，否则将使得参数游标失去意义
?
??? 例：用部门编号deptno作形参，显示每个人的姓名和工资
?
??? scott@ORCL> get /u01/bk/scripts/emp_cur5.sql
????? 1? DECLARE
????? 2????? v_deptno emp.deptno%type;
????? 3????? cursor emp_cur(v_deptno emp.deptno%type) is??? --定义游标时指定了参数v_deptno及类型
????? 4???? select ename,sal from emp where deptno=v_deptno;?? --必须在where子句中指定定义的参数
????? 5????? emp_record emp_cur%rowtype;
????? 6? BEGIN
????? 7????? v_deptno:=&inputno;
????? 8????? open emp_cur(v_deptno);
????? 9????? loop
???? 10????????? fetch emp_cur into emp_record;
???? 11????????? exit when emp_cur%notfound;
???? 12????????? dbms_output.put_line
???? 13?????????????? ('Employe Name is :'||emp_record.ename||' ,Sal:'||emp_record.sal);
???? 14????? end loop;
???? 15????? close emp_cur;
???? 16* END;
???? 17? /
??? Enter value for inputno: 10
??? old?? 7:???? v_deptno:=&inputno;
??? new?? 7:???? v_deptno:=10;
??? Employe Name is :CLARK ,Sal:2450
??? Employe Name is :KING ,Sal:5000
??? Employe Name is :MILL