PL/SQL - Procedure 與 Function

在一般程式語言中，Method (或 Function)是否有回傳值都稱作Method，但PL/SQL中根據是否有回傳值將Method分為兩種，Procedure與Function；Procedure為沒回傳值的Method，Function為有回傳值的Method。

Procedure

Procedure就是PL/SQL中的Method，Procedure將的一連串的操作行為包裝，提高重複使用性。

Procedure程式語法架構：

CREATE OR REPLACE PROCEDURE procedure_name
(       --參數宣告區
        parameter1 MODE DATATYPE [DEFAULT expression],
        parameter2 MODE DATATYPE [DEFAULT expression],
        ...
)
IS
[       --local變數宣告區
        variable1 DATATYPE;
        variable2 DATATYPE;
]
BEGIN
        statements
END;

在上面語法架構中有幾點需要注意：

MODE：
MODE為參數的存取層級，有IN, OUT, IN OUT三種；IN的參數代表Read Only，該參數值只能讀取，任何有修改的動作會發生錯誤；OUT參數代表Write Only，該參數在傳入時不能有初始化動作，在Procedure內才進行初始化與給值；IN OUT參數則擁有IN參數與OUT參數的特性。

DATATYPE：
DATATYPE為PL/SQL中的資料型態(VARCHAR, NUMBER, INTEGER...等)。

DEFAULT expression：
當參數需要Default值的時候，可以使用DEFAULT來指定初始值，例如:pi_param1 IN VARCHAR2 DEFAULT NULL,...，當然DEFAULT可以以:=取代變成pi_param1 IN VARCHAR2 := NULL。

Procedure範例：

CREATE OR REPLACE PROCEDURE EBS.OutputStringConcate
(
    pi_str1 IN VARCHAR2,
    pi_str2 IN VARCHAR2,
    po_strConcate OUT VARCHAR2
) 
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('OUTPUTSTRINGCONCATE-' || pi_str1);
    DBMS_OUTPUT.PUT_LINE('OUTPUTSTRINGCONCATE-' || pi_str2);
    po_strConcate := pi_str1 || pi_str2;
    DBMS_OUTPUT.PUT_LINE('OUTPUTSTRINGCONCATE-' || po_strConcate);
END;

編譯後會再Schema中的Global Function區中找到CombineTwoString。

Function

Function與Procedure功能類似，就只差在Function執行完後會回傳一個值，這個回傳值的型態定義在一開始Function宣告時定義好。很多人一定會疑惑，既然Procedure或Function都可以用OUT參數進行呼叫前後的傳值，那何必用Function呢?在Oreilly電子書中有提到，在Procedure中只用一個OUT參數回傳取代Function是一種不太好的Coding Style，建議當只有一個回傳值時使用Function，多個以上回傳值使用Procedure。

Function程式語法架構：

CREATE OR REPLACE FUNCTION procedure_name
(       --參數宣告區
        parameter1 MODE DATATYPE [DEFAULT expression],
        parameter2 MODE DATATYPE [DEFAULT expression],
        ...
) RETURN DATATYPE
IS
[       --local變數宣告區
        variable1 DATATYPE;
        variable2 DATATYPE;
]
BEGIN
        statements
END;

Function範例：

CREATE OR REPLACE FUNCTION EBS.CombineTwoString
(
    pi_str1 IN VARCHAR2,
    pi_str2 IN VARCHAR2
) RETURN VARCHAR2
IS
    lv_combine VARCHAR2(32);
BEGIN
    DBMS_OUTPUT.PUT_LINE('COMBINETOWSTRING-' || pi_str1);
    DBMS_OUTPUT.PUT_LINE('COMBINETOWSTRING-' || pi_str2);
    lv_combine := pi_str1 || pi_str2;
    DBMS_OUTPUT.PUT_LINE('COMBINETOWSTRING-' || lv_combine);
    RETURN lv_combine;
END CombineTwoString;

編譯後會再Schema中的Global Function區中找到CombineTwoString。

測試程式：

DECLARE
    STR1 VARCHAR2(32) := 'HELLO';
    STR2 VARCHAR2(32) := 'WORLD';
    STR3 VARCHAR2(32);
BEGIN
    OutputStringConcate(STR1, STR2, STR3);
    DBMS_OUTPUT.PUT_LINE(STR3);
    DBMS_OUTPUT.PUT_LINE(CombineTwoString(STR1, STR2));
END;

執行結果：
OUTPUTSTRINGCONCATE-HELLO
OUTPUTSTRINGCONCATE-WORLD
OUTPUTSTRINGCONCATE-HELLOWORLD
HELLOWORLD
COMBINETOWSTRING-HELLO
COMBINETOWSTRING-WORLD
COMBINETOWSTRING-HELLOWORLD
HELLOWORLD

測試程式裡各丟了兩個字串(HELLO & WORLD)進Procedure OutputStringConcate與Function CombineTwoString，其中OutputStringConcate要額外丟一個Out引數進去承接改變的值，因為CombineTwoString會直接回傳值，所以就直接印出來。




參考來源：
Learning Oracle PL/SQL - OReilly電子書