游標二(顯式游標的修改、行級鎖定、動態游標) (PL/SQL 十)

※顯式游標的修改

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM EMP;
BEGIN
    FOR xxx_emp IN xxx_cur LOOP
        IF xxx_emp.deptno = 10 THEN
            IF xxx_emp.sal * 1.2 < 3000 THEN
                UPDATE EMP SET SAL = xxx_emp.sal * 1.2 WHERE EMPNO = xxx_emp.empno;
            END IF;
        ELSIF xxx_emp.deptno = 20 THEN
            IF xxx_emp.sal * 1.4 < 3000 THEN
                UPDATE EMP SET SAL = xxx_emp.sal * 1.4 WHERE EMPNO = xxx_emp.empno;
            END IF;
        END IF;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('修改成功');
EXCEPTION
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM=' || SQLERRM);
    ROLLBACK;
END;

※看起來沒什麼難度

※行級鎖定

就是再撈資料時，將table的行鎖起來，讓別人不能用，以免被人修改或刪除了，Hibernate把它變成悲觀鎖了

如：CURSOR xxx_cur IS SELECT * FROM EMP WHERE DEPTNO = 10 FOR UPDATE
．FOR UPDATE：鎖起來不讓別人用

．FOR UPDATE NOWAIT：因為有可能要用時已經被人鎖起來了，如果不加NOWAIT，就會一直等到釋放了才換自己做事；但加了就不等了，直接回來

．FOR UPDATE OF 欄位1, 欄位2...：在join很多表時，如果只使用FOR UPDATE不一定能更新成功，如果要確定能更新就必需要加上OF欄位名

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM DEPT WHERE DEPTNO = 10 FOR UPDATE OF LOC;
BEGIN
    FOR xxx_dept IN xxx_cur LOOP
        UPDATE DEPT SET DNAME = 'xxx' WHERE CURRENT OF xxx_cur;
        -- DELETE FORM DEPT WHERE CURRENT OF xxx_cur;
    DBMS_OUTPUT.PUT_LINE(xxx_cur%ROWCOUNT);
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('修改/刪除成功');
EXCEPTION
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM=' || SQLERRM);
    ROLLBACK;
END;

※使用「CURRENT OF游標」時，一定要有FOR UPDATE，它的功能是定位目前的資料行

※動態游標

動態的指定內容，看第二個範例就懂了

※宣告動態游標

DECLARE
    TYPE xxx_dynamic IS REF CURSOR; -- RETURN DEPT%ROWTYPE;
    xxx xxx_dynamic;
    xxx_dept DEPT%ROWTYPE;
BEGIN
    OPEN xxx FOR SELECT * FROM DEPT;
    LOOP
        FETCH xxx INTO xxx_dept;
        EXIT WHEN xxx%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(xxx_dept.dname);
    END LOOP;
    CLOSE xxx;
END;

※因為FETCH是INTO到DEPT%ROWTYPE，所以註解可加可不加，不加的又叫「弱游標」

※弱游標的好處

DECLARE
    -- xxx SYS_REFCURSOR;
    TYPE xxx_dynamic IS REF CURSOR;
    xxx xxx_dynamic;
    
    xxx_dept DEPT%ROWTYPE;
    xxx_emp EMP%ROWTYPE;
BEGIN
    OPEN xxx FOR SELECT * FROM DEPT;
    LOOP
        FETCH xxx INTO xxx_dept;
        EXIT WHEN xxx%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(xxx_dept.dname);
    END LOOP;
    CLOSE xxx;
    
    DBMS_OUTPUT.PUT_LINE('');
    
    OPEN xxx FOR SELECT * FROM EMP WHERE DEPTNO = 10;
    LOOP
        FETCH xxx INTO xxx_emp;
        EXIT WHEN xxx%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(xxx_emp.ename);
    END LOOP;
    CLOSE xxx;
END;

※結果：
ACCOUNTING
RESEARCH
SALES
OPERATIONS

CLARK
KING
MILLER

※因為是弱游標，所以兩次的FETCH~INTO不一樣，但是還是OK的，所以才叫動態游標

※在Oracle9i提供了一個寫法來代替弱游標，就是註解那一行，只要SYS_REFCURSOR就可以取代TYPE和xxx dynamic那兩行了