游標一(隱式游標、顯式游標的查詢) (PL/SQL 九)

游標效能很低，所以要使用時要先想一下是否有必要使用，分成隱式游標(Implicit)和顯式游標(Explicit)，可參考官網的 隱式游標 和 顯式游標

※隱式游標

就是不用打出「CURSOR」關鍵字，有四種，參考官網

※%ROWCOUNT

DECLARE 
    xxx_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO xxx_count FROM EMP;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    
    INSERT INTO DEPT VALUES(50, '業務部', '舊金山');
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    
    UPDATE DEPT SET DNAME='採購部' WHERE DEPTNO = 50;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    
    DELETE FROM DEPT WHERE DEPTNO = 50;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;

※使用%ROWCOUNT可得知操作的行數，此例的結果是4個1

※%FOUND、%NOTFOUND

DECLARE 
BEGIN
    UPDATE EMP SET SAL = SAL * 1.2;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('修改了' || SQL%ROWCOUNT || '筆');
    ELSE
        DBMS_OUTPUT.PUT_LINE('沒有資料被修改');
    END IF;
END;

※顯式游標的查詢

就是要明確的打出「CURSOR」關鍵字

※和LOOP、WHILE迴圈配合使用

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM DEPT;
    xxx_dept DEPT%ROWTYPE;
BEGIN
    IF NOT xxx_cur%ISOPEN THEN
        OPEN xxx_cur;
    END IF;
    
    /*
    FETCH xxx_cur INTO xxx_dept;
    WHILE xxx_cur%FOUND LOOP
        DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT);
        DBMS_OUTPUT.PUT_LINE('=' || xxx_dept.dname);
        FETCH xxx_cur INTO xxx_dept;
    END LOOP;
    */
    
    LOOP
        FETCH xxx_cur INTO xxx_dept;
        EXIT WHEN xxx_cur%NOTFOUND;
        DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT);
        DBMS_OUTPUT.PUT_LINE('=' || xxx_dept.dname);
    END LOOP;
    
    CLOSE xxx_cur;
END;

※以上是兩種迴圈和游標的使用

※CURSOR xxx_cur IS SELECT * FROM DEPT 是省略寫法，全部是
CURSOR xxx_cur RETURN DEPT%ROWTYPE IS SELECT * FROM DEPT，也就是多了RETURN DEPT%ROWTYPE，因為查的是DEPT，所以預設也回傳DEPT的ROWTYPE，所以可以省略

※和FOR迴圈配合使用

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM DEPT;
    xxx_dept DEPT%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('方法一');
    FOR xxx_dept IN xxx_cur LOOP
        DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT);
        DBMS_OUTPUT.PUT_LINE('=' || xxx_dept.dname);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '方法二');
    FOR xxx_dept IN (SELECT * FROM DEPT) LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_dept.dname);
    END LOOP;
END;

※結果：
方法一
1=ACCOUNTING
2=RESEARCH
3=SALES
4=OPERATIONS

方法二
ACCOUNTING
RESEARCH
SALES
OPERATIONS

※和FOR迴圈配合時，不需要打開和關閉游標，也不用判斷哪時跳離迴圈，一切都交給系統控制，所以大部分的人都使用這一種

※方法一和方法二的差別就在方法二直接將SQL寫進去了，所以也無法使用%ROWCOUNT

※有參數的游標

DECLARE 
    CURSOR xxx_cur(ooo DEPT.DEPTNO%TYPE) IS SELECT * FROM DEPT WHERE DEPTNO = ooo;
BEGIN
    FOR xxx_dept IN xxx_cur(&pk) LOOP
        DBMS_OUTPUT.PUT(xxx_cur%ROWCOUNT || ' ');
        DBMS_OUTPUT.PUT(xxx_dept.deptno || ' ');
        DBMS_OUTPUT.PUT(xxx_dept.dname || ' ');
        DBMS_OUTPUT.PUT_LINE(xxx_dept.loc);
    END LOOP;
END;

※結果：
1 10 ACCOUNTING NEW YORK

※&pk為使用者輸入的參數值

 ※LOOP和WHILE要寫在OPEN xxx_cur(&pk)

※注意傳進去的參數不能用來判斷，如果是in的話，網路有用一種子查詢的方式，但我沒試出來，我用的是第26篇的動態SQL解決in的問題

※和陣列配合使用

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM DEPT;
    TYPE xxx_index IS TABLE OF DEPT%ROWTYPE INDEX BY PLS_INTEGER;
    xxx xxx_index;
BEGIN
    FOR xxx_dept IN xxx_cur LOOP
        xxx(xxx_dept.deptno) := xxx_dept;
        DBMS_OUTPUT.PUT_LINE(xxx(xxx_dept.deptno).dname);
    END LOOP;
END;

※結果：
ACCOUNTING
RESEARCH
SALES
OPERATIONS

※和巢狀表配合使用

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM DEPT;
    TYPE xxx_nested IS TABLE OF DEPT%ROWTYPE;
    xxx xxx_nested;
BEGIN
    IF NOT xxx_cur%ISOPEN THEN
        OPEN xxx_cur;
    END IF;
    
    FETCH xxx_cur BULK COLLECT INTO xxx;
    CLOSE xxx_cur;
    
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i).dname);
    END LOOP;
END;

※結果：
ACCOUNTING
RESEARCH
SALES
OPERATIONS

※使用BULK COLLECT一次取出，所以可以馬上關閉游標

※和VARRAY配合使用

DECLARE 
    CURSOR xxx_cur IS SELECT * FROM DEPT;
    TYPE xxx_varray IS VARRAY(3) OF DEPT%ROWTYPE;
    xxx xxx_varray;
BEGIN
    IF NOT xxx_cur%ISOPEN THEN
        OPEN xxx_cur;
    END IF;
    
    FETCH xxx_cur BULK COLLECT INTO xxx LIMIT 3;
    CLOSE xxx_cur;
    
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i).dname);
    END LOOP;
END;

※結果：
ACCOUNTING
RESEARCH
SALES

※BULK COLLECT INTO ~~~ LIMIT 可以限制資料量