??????? new?? 8:???? v_deptno:=10;
??????? CLARK
??????? KING
??????? MILLER
??
??? --例：游标属性使用示例(使用%isopen和%rowcount属性)
??????? scott@ORCL> get /u01/bk/scripts/emp_cur3.sql
????????? 1? DECLARE
????????? 2????? v_deptno emp.deptno%type;
????????? 3????? type ename_table_type is table of varchar2(10);
????????? 4????? ename_table ename_table_type;
????????? 5????? cursor emp_cur is
????????? 6???? select ename from emp where deptno=v_deptno;
????????? 7? BEGIN
????????? 8????? v_deptno:=&inputno;
????????? 9????? if not emp_cur%isopen then?????? --判断游标是否打开，如未打开，则打开游标
???????? 10????????? open emp_cur;
???????? 11????? end if;
???????? 12????? fetch emp_cur bulk collect into ename_table;
???????? 13????? dbms_output.put_line
???????? 14????????? ('All record counts from cursor is : '||emp_cur%rowcount);? --使用cursor_name%rowcount 统计游标的记录数
???????? 15????? close emp_cur;
???????? 16* END;
????????
??????? scott@ORCL> start /u01/bk/scripts/emp_cur3.sql
??????? Enter value for inputno: 20
??????? old?? 8:???? v_deptno:=&inputno;
??????? new?? 8:???? v_deptno:=20;
??????? All record counts from cursor is : 5
?
??? --例:基于游标定义记录变量(该方式大大简化了所需要定义的变量个数)
??????? scott@ORCL> get /u01/bk/scripts/emp_cur4.sql
????????? 1? DECLARE
????????? 2????? cursor emp_cur is select ename,sal from emp;
????????? 3????? emp_record emp_cur%rowtype;????????? --定义游标类型记录变量
????????? 4? BEGIN
????????? 5????? open emp_cur;
?? ???????6????? loop
????????? 7????????? fetch emp_cur into emp_record;
????????? 8????????? exit when emp_cur%notfound;
????????? 9????????? dbms_output.put_line
? ???????11????? end loop;
???????? 12????? close emp_cur;
???????? 13* END;
????????
??????? scott@ORCL> start /u01/bk/scripts/emp_cur4.sql
??????? Employee Name : SMITH ,Sal: 800
??????? Employee Name : ALLEN ,Sal: 1600
??????? Employee Name : WARD ,Sal: 1250
???????????????? .......
??????
三、使用游标更新记录??????
??? 通过游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据
??? 如果要通过游标更新和删除数据，在定义游标时必须要带有FOR UPDATE子句
??????? 格式：
??????? CURSOR cursor_name IS select_statement FOR UPDATE [ OF column_reference ][NOWAIT]
??????
????? ??FOR UPDATE ：子句用于在游标结果集数据上加行共享锁，以防止其它用户在相应行上执行DML操作
??????? OF ：子句用于游标子查询到多张表时来确定哪些表要加锁，如未指定，则select语句所引用的全部表将被加锁
??????? NOWAIT ：子句指定不等待锁
??????? 使用DML语句操作游标中的当前行时，需要在update或delete语句中引用where current of子句
??????
??????????? UPDATE tbname set col1=.. WHERE CURRENT OF cursor_name;
??????????? DELETE tbname? WHERE CURRENT OF cursor_name;
??????????
??????? --例：使用游标修改所有记录的工资，根据JOB来作不同的修改。
?
??????????? scott@ORCL> create table tb_emp as select * from emp;
?????????? ????????
??????????? scott@ORCL> get /u01/bk/scripts/emp_cur6.sql
????????????? 1? DECLARE
????????????? 2????? v_job tb_emp.job%TYPE;
????????????? 3????? CURSOR emp_cur IS SELECT job FROM tb_emp FOR UPDATE;? --定义时，使用FOR UPDATE
????????????? 4? BEGIN
??? ??????????5????? OPEN emp_cur;
????????????? 6????? LOOP
????????????? 7????????? FETCH emp_cur INTO v_job;
????????????? 8????????? EXIT WHEN emp_cur%NOTFOUND;
????????????? 9????????? CASE?
???????????? 10????????????? WHEN v_job='CLERK' THEN
????????? ???11????????????????? UPDATE tb_emp SET sal=sal*1.1 WHERE CURRENT OF emp_cur;? --注意，需要使用WHERE