SQL Server 預存程序移植 Oracle 實戰：語法對照、異常處理、游標與動態 SQL

原版 SQL Server 預存程序 (節錄)

CREATE PROCEDURE Zy_Make_SortDeliverLineID
    @BatchNo       INT,
    @ProcessType   INT,
    @o_err_no      INT OUTPUT,
    @o_err_msg     VARCHAR(1024) OUTPUT
AS
BEGIN
    DELETE FROM Zy_Sort_LineExitDeliverID WHERE BatchNo = @BatchNo;
    IF @@ERROR <> 0 GOTO ErrHandler;

    UPDATE Zy_Base_DeliverLine SET Surfix = ' ', DistributeType = 0
    WHERE SUBSTRING(DeliverLineID,1,4) <> '2000';
    IF @@ERROR <> 0 GOTO ErrHandler;

    IF @ProcessType = 1
    BEGIN
        EXEC Zy_Insert_TrunkSplitData @BatchNo, @o_err_no, @o_err_msg;
        IF @o_err_no <> 0 GOTO ErrHandler;
    END

    DELETE FROM Zy_Order_Details WHERE Quantity = 0 AND BatchNo = @BatchNo;
    IF @@ERROR <> 0 GOTO ErrHandler;

    DELETE FROM Zy_Order_Main WHERE BatchNo = @BatchNo
      AND OrderID NOT IN (SELECT DISTINCT OrderID FROM Zy_Order_Details WHERE BatchNo = @BatchNo);
    IF @@ERROR <> 0 GOTO ErrHandler;

    UPDATE Zy_Base_BatchNo SET OrderQuan = (SELECT COUNT(DISTINCT OrderID)
                                            FROM Zy_Order_Main WHERE BatchNo = @BatchNo)
    WHERE BatchNo = @BatchNo;
    IF @@ERROR <> 0 GOTO ErrHandler;

    SET @o_err_no = 0; SET @o_err_msg = '執行成功'; RETURN;

ErrHandler:
    SET @o_err_no = -1;
    SET @o_err_msg = '【出錯對象】{Zy_Insert_TrunkSplitData}' + @o_err_msg;
END;

Oracle 函數移植版 (關鍵差異標註)

CREATE OR REPLACE FUNCTION Zy_Make_SortDeliverLineID
    (BatchNo      IN  INTEGER,
     ProcessType  IN  INTEGER,
     o_err_no     OUT INTEGER,
     o_err_msg    OUT VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
    -- 1. 刪除 (獨立 BEGIN-END 塊捕獲異常)
    BEGIN
        DELETE FROM Zy_Sort_LineExitDeliverID WHERE BatchNo = BatchNo;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        o_err_no  := -1;
        o_err_msg := '刪除 [Zy_Sort_LineExitDeliverID] 時出錯！';
        RETURN;
    END;

    -- 2. 更新
    BEGIN
        UPDATE Zy_Base_DeliverLine
        SET    Surfix = ' ', DistributeType = 0
        WHERE  SUBSTR(DeliverLineID,1,4) <> '2000';  -- SUBSTRING → SUBSTR
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        o_err_no  := -1;
        o_err_msg := '更新 [Zy_Base_DeliverLine] 時出錯！';
        RETURN;
    END;

    -- 3. 條件呼叫子程序
    IF ProcessType = 1 THEN
        Zy_Insert_TrunkSplitData(BatchNo, o_err_no, o_err_msg);  -- 無 EXEC
        IF o_err_no <> 0 THEN ROLLBACK; RETURN; END IF;
    END IF;

    -- 4. 後續 DML 同理包裹 EXCEPTION
    BEGIN
        DELETE FROM Zy_Order_Details WHERE Quantity = 0 AND BatchNo = BatchNo;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK; o_err_no := -1; o_err_msg := '刪除 [Zy_Order_Details] 時出錯！'; RETURN;
    END;
    -- ... 略 ...

    COMMIT;  -- 顯式提交
    o_err_no  := 0;
    o_err_msg := '執行成功';
    RETURN o_err_msg;
END Zy_Make_SortDeliverLineID;

核心語法對照表

補充：Oracle 函數中處理游標異常的正確姿勢

CREATE OR REPLACE FUNCTION fn_getEmpName(p_str IN VARCHAR2, p_delim IN VARCHAR2)
RETURN VARCHAR2
IS
    v_str      VARCHAR2(4000) := '';
    v_name     VARCHAR2(4000);
    CURSOR c IS
        SELECT * FROM TABLE(CAST(fn_split(p_str, p_delim) AS ty_str_split));
    r c%ROWTYPE;
BEGIN
    OPEN c;
    LOOP
        FETCH c INTO r;
        EXIT WHEN c%NOTFOUND;
        BEGIN  -- 內層 BEGIN-END 針對單行查詢異常
            SELECT employee_name INTO v_name
            FROM mocha_osm_emp a, mocha_osm_accounts b
            WHERE a.id = b.emp_id AND b.user_id = r.column_value;
            v_str := v_str || v_name || p_delim;
        EXCEPTION WHEN NO_DATA_FOUND THEN
            v_name := '';  -- 忽略無匹配行
        END;
    END LOOP;
    CLOSE c;
    IF LENGTH(v_str) > 0 THEN v_str := SUBSTR(v_str, 1, LENGTH(v_str)-1); END IF;
    RETURN v_str;
END fn_getEmpName;

關鍵：游標迴圈內的單行查詢必須包裹在 BEGIN ... EXCEPTION WHEN NO_DATA_FOUND THEN ... END; 中，否則 NO_DATA_FOUND 會直接中斷整個函數。

移植檢查清單

1. 所有 DML 包裹獨立 BEGIN-END + EXCEPTION WHEN OTHERS。
2. @@ERROR / GOTO 全部改為異常區塊。
3. EXEC 移除，直接呼叫程序/函數。
4. 字串函數、日期函數、轉換函數逐一對照 (如 GETDATE() → SYSDATE、ISNULL → NVL、CONVERT → TO_CHAR/TO_DATE)。
5. 臨時表 #temp → GLOBAL TEMPORARY TABLE 或 PL/SQL Collection。
6. 動態 SQL EXEC(@sql) → EXECUTE IMMEDIATE sql (注意綁定變數)。
7. 測試邊界：空結果集、主鍵衝突、長字串截斷、隱式轉換。

結語

SQL Server → Oracle 移植核心在於**交易控制顯式化**與**異常處理結構化**。養成「每個 DML 一個 PL/SQL 區塊」習慣，配合對照表機械替換語法，即可高質量完成遷移。