oracle教程20 游标使用教程和举例（使用游标修改或删除数据for update\nowait子句\of子句）

不念不忘少年蓝@ 2022-05-27 05:56 536阅读 0赞

--按职工的职称涨工资，总裁涨1000元，经理涨500元，其他员工涨300元。
    --1：用显示游标的常规方式实现业务需求
    DECLARE
      --定义游标
      CURSOR empnew_cursor IS SELECT empno,job FROM empnew;
      v_empno empnew.empno%TYPE;
      v_job   empnew.job%TYPE;
    BEGIN
      --打开游标
      OPEN empnew_cursor;
      --提取数据
      LOOP
        FETCH empnew_cursor INTO v_empno,v_job;
        IF v_job='PRESIDENT' THEN
          UPDATE empnew SET sal = sal + 1000 WHERE empno = v_empno;
        ELSIF v_job='MANAGER' THEN
          UPDATE empnew SET sal = sal + 500 WHERE empno = v_empno;
        ELSE
          UPDATE empnew SET sal = sal + 300 WHERE empno = v_empno;
        END IF;
        EXIT WHEN empnew_cursor%NOTFOUND;
      END LOOP;
      COMMIT;
      --关闭游标
      CLOSE empnew_cursor;
    END;

--2：用游标FOR循环的方式实现业务需求
    DECLARE
      --定义游标
      CURSOR empnew_cursor IS SELECT empno,job FROM empnew; 
    BEGIN
      FOR empnew_record IN empnew_cursor LOOP    
          DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);      
          IF empnew_record.job = 'PRESIDENT' THEN
            UPDATE empnew SET sal = sal + 1000 WHERE empno = empnew_record.empno;
          ELSIF empnew_record.job = 'MANAGER' THEN
            UPDATE empnew SET sal = sal + 500 WHERE empno = empnew_record.empno;
          ELSE
            UPDATE empnew SET sal = sal + 300 WHERE empno = empnew_record.empno;
          END IF;
      END LOOP;
      --COMMIT;
    END;

![70][]

![70 1][]

![70 2][]

--3：使用游标添加或删除数据时，定义游标时利用FOR UPDATE 子句可以将游标提取出来的数据进行行级锁定
    DECLARE
      --定义游标  FOR UPDATE锁定
      CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE; 
    BEGIN
      FOR empnew_record IN empnew_cursor LOOP    
          DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);      
          IF empnew_record.job = 'PRESIDENT' THEN
            UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;
          ELSIF empnew_record.job = 'MANAGER' THEN
            UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;
          ELSE
            UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;
          END IF;
      END LOOP;
      COMMIT;
    END;
    
    SELECT * FROM empnew;

for update虽然好用，但是如果需要查询的表已经被其他操作锁定，那么该程序会一直处于等待状态。这是一个缺点，我们可以用for update nowait来避免死锁的问题。

![70 3][]

--FOR UPDATE NOWAIT 不等待锁，如发现所操作的数据行已经锁定，将不会等待，立即返回
    DECLARE
      --定义游标
      CURSOR empnew_cursor IS SELECT empno,job FROM empnew FOR UPDATE NOWAIT; 
    BEGIN
      FOR empnew_record IN empnew_cursor LOOP    
          DBMS_OUTPUT.put_line(empnew_record.empno||'----'||empnew_record.job);      
          IF empnew_record.job = 'PRESIDENT' THEN
            UPDATE empnew SET sal = sal + 1000 WHERE CURRENT OF empnew_cursor;
          ELSIF empnew_record.job = 'MANAGER' THEN
            UPDATE empnew SET sal = sal + 500 WHERE CURRENT OF empnew_cursor;
          ELSE
            UPDATE empnew SET sal = sal + 300 WHERE CURRENT OF empnew_cursor;
          END IF;
      END LOOP;
      COMMIT;
    END;

![70 4][]

--使用OF子句在特定表上加行共享锁 
    DECLARE
           CURSOR empnew_cursor IS
                  SELECT d.dname dname,e.ename ename 
                  FROM empnew e join dept d on e.deptno = d.deptno
                  WHERE e.deptno = &deptno
                  FOR UPDATE OF e.deptno;
    BEGIN
            FOR empnew_record IN empnew_cursor LOOP
                DBMS_OUTPUT.PUT_LINE('部门名称：'||empnew_record.dname||'员工名：'||empnew_record.ename);
                DELETE FROM empnew WHERE CURRENT OF empnew_cursor;
            END LOOP;
            COMMIT;
    END;

[70]: /images/20220527/acf9a41840294562b5ecb2f609f26481.png
[70 1]: /images/20220527/497a5702887d4828aaa63f83440a4274.png
[70 2]: /images/20220527/2ca339fac8c6449abb6cca77f1b56904.png
[70 3]: /images/20220527/29fff2522a8f47a386ed8c0a457ef8cf.png
[70 4]: /images/20220527/c43e640977504b879462c0592eae82ba.png