集合的例外、FORALL、BULK COLLECT (PL/SQL 八)

※處理集合例外

※COLLECTION_IS_NULL

DECLARE 
    TYPE xxx_testnested IS VARRAY(3) OF VARCHAR2(10);
    xxx xxx_testnested;
BEGIN
    xxx(0) := '10';
    
EXCEPTION
    WHEN COLLECTION_IS_NULL THEN
        DBMS_OUTPUT.PUT_LINE('集合未初始化！');
END;

※SUBSCRIPT_BEYOND_COUNT

DECLARE 
    TYPE xxx_testnested IS VARRAY(3) OF VARCHAR2(10);
    xxx xxx_testnested := xxx_testnested('a', 'b');
BEGIN
    xxx(3) := '10';
    
EXCEPTION
    WHEN SUBSCRIPT_BEYOND_COUNT THEN
        DBMS_OUTPUT.PUT_LINE('超過index的個數！');
END;

※SUBSCRIPT_OUTSIDE_LIMIT

DECLARE 
    TYPE xxx_testnested IS VARRAY(3) OF VARCHAR2(10);
    xxx xxx_testnested := xxx_testnested('a', 'b');
BEGIN
    xxx(4) := '10';
    
EXCEPTION
    WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN
        DBMS_OUTPUT.PUT_LINE('超過最大極限！');
END;

※VALUE_ERROR

DECLARE 
    TYPE xxx_testnested IS VARRAY(3) OF VARCHAR2(10);
    xxx xxx_testnested := xxx_testnested('a', 'b');
BEGIN
    xxx('2') := '1';
    xxx('x') := '2';
EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('index類型錯誤！');
END;

※NO_DATA_FOUND

DECLARE 
    TYPE xxx_testnested IS TABLE OF VARCHAR2(10);
    xxx xxx_testnested := xxx_testnested('a', 'b');
BEGIN
    xxx.DELETE(1);
    DBMS_OUTPUT.PUT_LINE(xxx(1));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('找不到資料！');
END;

※FORALL

FORALL是將SQL一次性的發送到資料庫執行

※一般的修改

DECLARE 
    TYPE emp_empno IS VARRAY(8) OF EMP.EMPNO%TYPE;
    xxx emp_empno := emp_empno(7369, 7902);
BEGIN
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        UPDATE EMP SET sal = sal * 1.2 WHERE empno = xxx(i); 
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('修改成功！');
END;

※這樣子修改比較粍效能

※使用FORALL

DECLARE 
    TYPE emp_empno IS VARRAY(8) OF EMP.EMPNO%TYPE;
    xxx emp_empno := emp_empno(7369, 7902);
    cou number := 0;
BEGIN
    FORALL i IN xxx.FIRST..xxx.LAST
    UPDATE EMP SET sal = sal * 1.2 WHERE empno = xxx(i); 
    
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        cou := cou + SQL%BULK_ROWCOUNT(i);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('修改了'|| cou || '筆！');
END;

※使用SQL%BULK_ROWCOUNT(i)可取得修改的筆數

※BULK COLLECT

BULK COLLECT是從資料庫一次性的取出多條資料

※取得一個欄位的集合

DECLARE 
    TYPE emp_ename IS VARRAY(8) OF EMP.ENAME%TYPE;
    xxx emp_ename;
BEGIN
    SELECT ename BULK COLLECT INTO xxx FROM EMP WHERE DEPTNO = 10;
    
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i));
    END LOOP;
END;

※取得多個欄位的集合

DECLARE 
    TYPE emp_all IS TABLE OF EMP%ROWTYPE;
    xxx emp_all;
BEGIN
    SELECT * BULK COLLECT INTO xxx FROM EMP;
    
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i).empno);
        DBMS_OUTPUT.PUT_LINE(xxx(i).ename);
        DBMS_OUTPUT.PUT_LINE(xxx(i).deptno || CHR(10));
    END LOOP;
END;