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