包一(宣告、刪除、重載和Session) (PL/SQL 十五)

※包

．可以將procedure、function、cursor…等包起來一起管理，分成包和包體

．可以將包想成java的interface，而包體就是實作interface的class

．如果包沒定義，而包體寫了，外面就沒辦法訪問，就像是class雖然實作了interface，但裡面又定義了private的方法一樣，只有自己能調用

※宣告包

CREATE OR REPLACE PACKAGE xxx_pkg IS
    -- TYPE xxx_cur IS REF CURSOR;
    FUNCTION dept_of_emp(xxx_dno DEPT.DEPTNO%TYPE)
    RETURN SYS_REFCURSOR;
    -- RETURN xxx_cur;
    
    -- PROCEDURE aaa;
    -- CURSOR bbb RETURN DEPT%ROWTYPE;
    -- CURSOR ccc RETURN DEPT%ROWTYPE IS
    -- SELECT * FROM DEPT WHERE DEPTNO = 20;
END;

※上面是系統定義的弱游標類型，如果要自己定義弱游標，就將第一個註解打開，然後回傳值就是自定義的即可(就是第二個註解)

※剩下的註解就是可以定很多的意思，如procedure、function…等，和java不一樣的地方是它也可以定出內容，如最後的ccc，這樣子實作它的包體就可以直接拿來用，不用實作了

※宣告包體

CREATE OR REPLACE PACKAGE BODY xxx_pkg IS
    FUNCTION dept_of_emp(xxx_dno DEPT.DEPTNO%TYPE)
    RETURN SYS_REFCURSOR
    -- RETURN xxx_cur
    
    IS
    ooo_cur SYS_REFCURSOR;
    -- ooo_cur xxx_cur;
    BEGIN
        OPEN ooo_cur FOR SELECT * FROM EMP WHERE DEPTNO = xxx_dno;
        RETURN ooo_cur;
        CLOSE ooo_cur;
    END dept_of_emp;
    
    PROCEDURE aaa IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('aaa');
    END aaa;
END;

※注意包體第一行變成PACKAGE BODY，有BODY喔！

※註解的部分是自定弱游標的寫法

※測試

DECLARE
    ooo_cur SYS_REFCURSOR;
    -- ooo_cur xxx_pkg.xxx_cur;
    
    xxx_emp EMP%ROWTYPE;
BEGIN
    -- xxx_pkg.aaa;
    ooo_cur := xxx_pkg.dept_of_emp(20);
    
    /*
    FETCH ooo_cur INTO xxx_emp;
    WHILE ooo_cur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(xxx_emp.ename);
        FETCH ooo_cur INTO xxx_emp;
    END LOOP;
    */
    
    LOOP
        FETCH ooo_cur INTO xxx_emp;
        EXIT WHEN ooo_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(xxx_emp.ename);
    END LOOP;
    
    CLOSE ooo_cur;
END;

※如果是自定弱游標的類型，就要寫成「包.游標」

※xxx_pkg.aaa這一行必需在包有定義，不然就是最前面說的，只是個內部能調用的方法

※兩種迴圈都可以用，但我試FOR迴圈會錯，不知道為什麼

※USER_OBJECTS、USER_SOURCE這兩張表可以看到宣告的一些資訊

※刪除包和包體

DROP PACKAGE package名稱;
DROP PACKAGE BODY package名稱;

※重載和Session

就是包裡面可以有同樣的名稱，但參數個數或類型不同

CREATE OR REPLACE PACKAGE xxx_pkg IS
    -- PRAGMA SERIALLY_REUSABLE;
    ooo VARCHAR2(10) := 'ooo';
    
    PROCEDURE xxx(p EMP.ENAME%TYPE);
    PROCEDURE xxx(p EMP.EMPNO%TYPE);
    PROCEDURE xxx(p1 EMP.EMPNO%TYPE, p2 EMP.ENAME%TYPE);
END;

※宣告一個變數和三個procedure

CREATE OR REPLACE PACKAGE BODY xxx_pkg IS
    -- PRAGMA SERIALLY_REUSABLE;
    PROCEDURE xxx(p EMP.ENAME%TYPE) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('ename:' || p);
        DBMS_OUTPUT.PUT_LINE('ooo:' || ooo);
    END;
    
    PROCEDURE xxx(p EMP.EMPNO%TYPE) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('empno:' || p);
        ooo := 'xxx';
        DBMS_OUTPUT.PUT_LINE('ooo:' || ooo);
    END;
    
    PROCEDURE xxx(p1 EMP.EMPNO%TYPE, p2 EMP.ENAME%TYPE) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('2個參數:' || p1 || '和' || p2);
        DBMS_OUTPUT.PUT_LINE('ooo:' || ooo);
    END;
END;

※一個參數的EMPNO改變ooo變數的值

※測試

DECLARE
BEGIN
    -- xxx_pkg.ooo := 'aaa';
    
    -- xxx_pkg.xxx('xyz');
    -- xxx_pkg.xxx(123);
    xxx_pkg.xxx(321, 'zyx');
END;

※重點在xxx_pkg.ooo，如果打開後執行三個隨便一個procedure，然後再關閉xxx_pkg.ooo，這時會發現不是預設的ooo，因為這相當於全域變數的關係，解決方法有兩個，一種就是將視窗關閉再打開執行就可以了，因為視窗關閉，此session就關閉了

另一種方法是將PACKAGE和PACKAGE BODY的註解打開(就是PRAGMA SERIALLY_REUSABLE)，這樣就可以每次都幫我們加載-缷載的動作了，不過較浪費效能，所以不建議使用