子程序三(內部子程序、互相調用、重載、和集合配合、遞歸) (PL/SQL 十三)

※內部子程序

※Procedure裡面還有兩個Procedure

CREATE OR REPLACE PROCEDURE dept_ins_proc(
    xxx_dno DEPT.DEPTNO%TYPE,
    xxx_dname DEPT.DNAME%TYPE,
    xxx_loc DEPT.LOC%TYPE,
    xxx_rtn OUT BOOLEAN
) IS
    xxx_count NUMBER;
    PROCEDURE dept_count_proc(
        ooo_dno DEPT.DEPTNO%TYPE,
        ooo_count OUT NUMBER
    )IS
    BEGIN
        SELECT COUNT(DEPTNO) INTO ooo_count FROM DEPT WHERE DEPTNO = ooo_dno;
    END; -- END dept_count_proc;
    
    PROCEDURE dept_ins_do_proc(
        xox_dno DEPT.DEPTNO%TYPE,
        xox_dname DEPT.DNAME%TYPE,
        xox_loc DEPT.LOC%TYPE,
        xox_count NUMBER,
        xox_rtn OUT BOOLEAN
    )IS
    BEGIN
        IF xox_count > 0 THEN
            xox_rtn := FALSE;
        ELSE
            INSERT INTO DEPT VALUES(xox_dno, xox_dname, xox_loc);
            xox_rtn := TRUE;
            COMMIT;
        END IF;
    END;
BEGIN
    DEPT_COUNT_PROC(xxx_dno, xxx_count);
    DEPT_INS_DO_PROC(xxx_dno, xxx_dname, xxx_loc, xxx_count, xxx_rtn);
END;
    
    
    
DECLARE
    rtn BOOLEAN;
BEGIN
    DEPT_INS_PROC(50, '採購部', '芝加哥', rtn);
    IF rtn THEN
        DBMS_OUTPUT.PUT_LINE('true');
    ELSE
        DBMS_OUTPUT.PUT_LINE('false');
    END IF;
END;

※因為使用DBMS印rtn會錯，只好這樣子寫了

※-- END dept_count_proc可以只寫END，但全寫比較清楚是什麼東西結束了

※尤於內部可以訪問外部，所以可以簡化，如下：

CREATE OR REPLACE PROCEDURE dept_ins_proc(
    xxx_dno DEPT.DEPTNO%TYPE,
    xxx_dname DEPT.DNAME%TYPE,
    xxx_loc DEPT.LOC%TYPE,
    xxx_rtn OUT BOOLEAN
) IS
    xxx_count NUMBER;
    PROCEDURE dept_count_proc IS
    BEGIN
        SELECT COUNT(DEPTNO) INTO xxx_count FROM DEPT WHERE DEPTNO = xxx_dno;
    END;
    
    PROCEDURE dept_ins_do_proc IS
    BEGIN
        IF xxx_count > 0 THEN
            xxx_rtn := FALSE;
        ELSE
            INSERT INTO DEPT VALUES(xxx_dno, xxx_dname, xxx_loc);
            xxx_rtn := TRUE;
            COMMIT;
        END IF;
    END;
BEGIN
    DEPT_COUNT_PROC;
    DEPT_INS_DO_PROC();
END;

※此時最後的BEGIN和END就不需要傳參數了

※但子程序裡面的procedure不會出現在資料庫字典裡，所以只有自己能調用，如果需求是自己才需要，那就這樣子寫，不然還是要寫一隻在外面呼叫，所謂資料庫字典就是如下的樣子，圖片是集合的，procedure和function，在左邊是Procedures和Functions，右邊是程序和函數

※互相調用、重載(overloading)

互相調用就是a調用b，b也調用a
重載就是方法名稱一樣，但參數個數或型態不同

※錯誤的例子

CREATE OR REPLACE PROCEDURE xxx(p NUMBER) IS
    -- PROCEDURE b_proc(p2 VARCHAR2);
    PROCEDURE a_proc(p1 NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('aaa');
        b_proc('yeah!');
    END;
    
    PROCEDURE b_proc(p2 VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('bbb');
        a_proc(1);
    END;
BEGIN
    a_proc(p);
END;
    
    
    
DECLARE
BEGIN
    xxx(10);
END;

※因為a調用b時，b還沒有定義，解法是將上面的註解打開就可以了

※但上面的程式碼沒給結束點，所以是個無限迴圈

※因為有支援重載，而上面的兩個procedure參數類型不同，所以名稱如果都一樣，也是可以執行的

※和集合配合

※以巢狀表為例

DECLARE
    TYPE xxx_nested IS TABLE OF EMP%ROWTYPE;
    xxx xxx_nested;
    
    FUNCTION dept_of_emp(edno EMP.DEPTNO%TYPE) RETURN xxx_nested
    IS
        ooo xxx_nested;
    BEGIN
        SELECT * BULK COLLECT INTO ooo FROM EMP WHERE DEPTNO = edno;
    RETURN ooo;
    END;
BEGIN
    xxx := dept_of_emp(30);
    FOR i IN xxx.FIRST..xxx.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(xxx(i).ENAME);
        DBMS_OUTPUT.PUT_LINE(xxx(i).JOB);
        DBMS_OUTPUT.put_line(xxx(i).SAL || CHR(10));
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('沒這個部門');
END;

※呼叫完後，跑迴圈印出

※遞歸

就是呼叫自己，所以如果不給一個結束點，就是無限迴圈

※100以下的數字加到100的小程式

DECLARE
    FUNCTION x_to_hundred(i NUMBER) RETURN NUMBER IS
    xxx_sum NUMBER := 0;
    BEGIN
        IF i > 100 THEN
            RETURN xxx_sum;
        ELSE
            xxx_sum := i + x_to_hundred(i + 1);
            RETURN xxx_sum;
        END IF;
    END;
BEGIN
    DBMS_OUTPUT.PUT_LINE(x_to_hundred(1));
END;