???????????? 12????????????? WHEN v_job='SALESMAN' THEN
???????????? 13????????????????? UPDATE tb_emp SET sal=sal*1.08 WHERE CURRENT OF emp_cur;
??? ?????????14????????????? ELSE
???????????? 15????????????????? UPDATE tb_emp SET sal=sal*1.05 WHERE CURRENT OF emp_cur;
???????????? 16????????? END CASE;
???????????? 17????? END LOOP;
???????????? 18????? CLOSE emp_cur;
???????????? 19* END;
?
??????? --例：利用游标删除数据
??????????? scott@ORCL> get /u01/bk/scripts/emp_cur7.sql
????????????? 1? DECLARE
????????????? 2????? v_job tb_emp.job%type;
????????????? 3????? v_sal tb_emp.sal%type;
????????????? 4????? cursor emp_cur is select job,sal from tb_emp for update;
????????????? 5? BEGIN
????????????? 6????? open emp_cur;
????????????? 7????? fetch emp_cur into v_job,v_sal;
????????????? 8????? while emp_cur%found
????????????? 9????????? loop
???????????? 10????????????? if v_sal>3000 then
???????????? 11????????????????? delete from tb_emp where current of emp_cur;
???????????? 12????????????? end if;
???????????? 13????????????? fetch emp_cur into v_job,v_sal;
???????????? 14????????? end loop;
???????????? 15????? close emp_cur;
???????????? 16* END;
???????????? 17? /
??????
??????? --例：使用OF子句对特定的表加共享锁
??????????? scott@ORCL> get /u01/bk/scripts/emp_cur8.sql
????????????? 1? DECLARE
????????????? 2????? cursor emp_cur is
????????????? 3????????? select ename,sal,dname,e.deptno
????????????? 4??????? ??from tb_emp e join dept d
????????????? 5????????????? on e.deptno=d.deptno for update of e.deptno;
????????????? 6????? emp_record emp_cur%rowtype;
????????????? 7? BEGIN
????????????? 9????? loop
???????????? 10????????? fetch emp_cur into emp_record;
???????????? 11????????? exit when emp_cur%notfound;
???????????? 12????????? if emp_record.deptno=20 then
???????????? 13????????????? update tb_emp set sal=sal+100 where current of emp_cur;
??????? ?????14????????? end if;
???????????? 15????????? dbms_output.put_line('Ename: '||emp_record.ename||
???????????? 16?????????????????????????????? ',Sal: '||emp_record.sal||
???????????? 17?????????????????????????????? ',Deptname:'||emp_record.dname);
?? ??????????18????? end loop;
???????????? 19????? close emp_cur;
???????????? 20* END;
???????????? 21? /
??????????? Ename: SMITH,Sal: 880,Deptname:RESEARCH
??????????? Ename: ALLEN,Sal: 1728,Deptname:SALES
?????????????????????? ........
??????
??????? --例:NOWAIT子句的使用?
??????????? scott@ORCL> get /u01/bk/scripts/emp_cur9.sql
????????????? 1? DECLARE
????????????? 2????? v_ename tb_emp.ename%type;
????????????? 3????? v_oldsal tb_emp.sal%type;
????????????? 4????? cursor emp_cur is
????????????? 5??????? ??select ename,sal from tb_emp for update nowait;? --使用nowait子句指定不等待锁，会给出错误提示
????????????? 6? BEGIN
????????????? 7????? open emp_cur;
????????????? 8????? loop
????????????? 9????????? fetch emp_cur into v_ename,v_oldsal;
???????????? 10????????? exit when emp_cur%notfound;
???????????? 11????????? if v_oldsal<2000 then
???????????? 12????????????? update tb_emp set sal=sal+200 where current of emp_cur;
???????????? 13????????? end if;
???????????? 14????? end loop;
?????? ??????15????? close emp_cur;
???????????? 16* END;
????????????
??????????? scott@ORCL> start /u01/bk