子程序一(Procedure、Function的宣告和刪除) (PL/SQL 十一)

※Procedure

Procedure和Function最大的差異有兩個
1.Procedure沒有顯式的回傳值(必需用OUT或IN OUT)
2.function的参數和回傳值不能使用 boolean，官網連結

※宣告無參的Procedure

CREATE OR REPLACE PROCEDURE xxx_proc IS 
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello procedure!');
END;

※OR REPLACE 是可選的，第一次執行可不用，之後要覆寫就一定要打

※無參數時，方法名稱後面不可加「()」

※要呼叫時，PL/SQL Developer可用如下的方式：

※Oracle SQL Developer一樣是DBMS輸出

※寫的時候，無參不能加「()」，但呼叫時，有沒有「()」都可以

※宣告完的程序和函數，就會在上圖左邊的紅框裡

※不可以用select~from的方式

※宣告有參的Procedure

CREATE OR REPLACE PROCEDURE xxx_proc(dno DEPT.DEPTNO%TYPE) IS
    xxx_count NUMBER;
    xxx_dname DEPT.DNAME%TYPE;
    xxx_loc DEPT.LOC%TYPE;
BEGIN
    SELECT COUNT(DEPTNO) INTO xxx_count FROM DEPT WHERE DEPTNO = dno;
    
    IF xxx_count != 0 THEN
        SELECT DNAME, LOC INTO xxx_dname, xxx_loc FROM DEPT WHERE DEPTNO = dno;
        DBMS_OUTPUT.PUT_LINE(xxx_dname || '|' || xxx_loc);
    END IF;
END;

※

※Function

※宣告Function

CREATE OR REPLACE FUNCTION xxx_func(eno EMP.EMPNO%TYPE) RETURN NUMBER IS
    xxx_sal EMP.SAL%TYPE;
BEGIN
    SELECT SAL + NVL(COMM, 0) INTO xxx_sal FROM EMP WHERE EMPNO = eno;
    RETURN xxx_sal;
END;

※OR REPLACE 是可選的，第一次執行可不用，之後要覆寫就一定要打

※無參數時，方法名稱後面不可加「()」

※一定要寫 RETURN TYPE，否則編譯就錯了

※呼叫時用一般的SQL，如：SELECT xxx_func(7369) FROM DUAL;不可以用exec

※使用PL/SQL呼叫Procedure/Function

上面的呼叫方法為：
．Procedure：使用「EXEC Procedure名稱」； 如果是在PL/SQL Developer，就要在Command Window裡

．Function：一般的SQL下法

．兩者混用就會發生錯誤



以下是用PL/SQL呼叫：

※呼叫Function

DECLARE 
    sal_count NUMBER;
BEGIN
    sal_count := xxx_func(7369);
    DBMS_OUTPUT.PUT_LINE(sal_count);
END;

※宣告一個變數來接並印出

※一定要用回傳值去接，否則會出「'xxx' is not a procedure or is undefined」的錯

※呼叫Procedure

DECLARE 
BEGIN
    xxx_proc(40);
END;

※因沒有回傳值，所以直接印出

※Procedure裡有Function

CREATE OR REPLACE PROCEDURE proc_call_func IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(xxx_func(7369));
END;

※EXEC proc_call_func;

※Fuinction裡有Procedure

CREATE OR REPLACE FUNCTION func_call_proc RETURN BOOLEAN IS
BEGIN
    xxx_proc(40);
    RETURN TRUE;
END;

※SELECT func_call_proc() FROM DUAL;

※使用官方的Oracle SQL Developer，「DBMS輸出」看不出結果，但PL/SQL在DBMS Output活頁標籤看的到，Command Window也都看的到

※刪除Procedure和Function

DROP PROCEDURE procedure名稱;
DROP FUNCTION function名稱;
或者最簡的方法，就是上面的圖，直接選Procedure或Function按右鍵就有很多選擇，其中一個就是DROP