您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 預存程序 CRUD 範本：新增、刪除、修改的標準化寫法與異常處理

編輯：Oracle 資料庫基礎

設計原則

參數錨定：使用 %TYPE 綁定表欄位型別，結構變更自動適配。
明確異常處理：區分業務異常（如查無資料）與系統異常，回傳狀態碼/訊息。
交易控制權上移：程序內 不 COMMIT/ROLLBACK，由呼叫端統一控制，保持原子性彈性。
名稱標記法呼叫：提升可讀性、免受參數順序影響。

測試表結構

CREATE TABLE t_stu (
    stuid   NUMBER PRIMARY KEY,
    stuname NVARCHAR2(50),
    stusex  NVARCHAR2(10),
    stuadd  NVARCHAR2(200)
);

1. 新增程序（含重複鍵處理）

CREATE OR REPLACE PROCEDURE p_insert_t_stu (
    p_stuid   IN t_stu.stuid%TYPE,
    p_stuname IN t_stu.stuname%TYPE,
    p_stusex  IN t_stu.stusex%TYPE,
    p_stuadd  IN t_stu.stuadd%TYPE,
    p_msg     OUT VARCHAR2
) AS
BEGIN
    INSERT INTO t_stu (stuid, stuname, stusex, stuadd)
    VALUES (p_stuid, p_stuname, p_stusex, p_stuadd);
    
    p_msg := 'SUCCESS: Inserted ' || p_stuid;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        p_msg := 'ERROR: Student ID ' || p_stuid || ' already exists';
    WHEN OTHERS THEN
        p_msg := 'ERROR: ' || SQLERRM;
END p_insert_t_stu;
/
-- 呼叫範例
DECLARE v_msg VARCHAR2(200); BEGIN
    p_insert_t_stu(1001, '王小明', '男', '台北市信義區', v_msg);
    DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/
-- 輸出：SUCCESS: Inserted 1001

2. 刪除程序（存在性檢查 + 狀態回傳）

CREATE OR REPLACE PROCEDURE p_delete_t_stu (
    p_stuid IN t_stu.stuid%TYPE,
    p_msg   OUT VARCHAR2
) AS
    v_exists NUMBER;
BEGIN
    -- 檢查是否存在
    SELECT 1 INTO v_exists FROM t_stu WHERE stuid = p_stuid;
    
    DELETE FROM t_stu WHERE stuid = p_stuid;
    
    IF SQL%ROWCOUNT = 1 THEN
        p_msg := 'SUCCESS: Deleted ' || p_stuid;
    ELSE
        p_msg := 'WARNING: No row deleted for ' || p_stuid;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_msg := 'ERROR: Student ID ' || p_stuid || ' not found';
    WHEN OTHERS THEN
        p_msg := 'ERROR: ' || SQLERRM;
END p_delete_t_stu;
/
-- 呼叫
DECLARE v_msg VARCHAR2(200); BEGIN
    p_delete_t_stu(1001, v_msg);
    DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

3. 修改程序（部分欄位更新 + 樂觀鎖可選）

CREATE OR REPLACE PROCEDURE p_update_t_stu (
    p_stuid   IN t_stu.stuid%TYPE,
    p_stuname IN t_stu.stuname%TYPE,
    p_stusex  IN t_stu.stusex%TYPE,
    p_stuadd  IN t_stu.stuadd%TYPE,
    p_msg     OUT VARCHAR2
) AS
    v_rowcnt NUMBER;
BEGIN
    UPDATE t_stu
    SET stuname = p_stuname,
        stusex  = p_stusex,
        stuadd  = p_stuadd
    WHERE stuid = p_stuid;
    
    v_rowcnt := SQL%ROWCOUNT;
    
    IF v_rowcnt = 1 THEN
        p_msg := 'SUCCESS: Updated ' || p_stuid;
    ELSIF v_rowcnt = 0 THEN
        p_msg := 'WARNING: Student ID ' || p_stuid || ' not found';
    ELSE
        p_msg := 'ERROR: Multiple rows updated (' || v_rowcnt || ') - check PK integrity';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        p_msg := 'ERROR: ' || SQLERRM;
END p_update_t_stu;
/
-- 呼叫
DECLARE v_msg VARCHAR2(200); BEGIN
    p_update_t_stu(1001, '王大明', '男', '新北市板橋區', v_msg);
    DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

4. 進階：合併 UPSERT 程序（單一入口處理新增/修改）

CREATE OR REPLACE PROCEDURE p_upsert_t_stu (
    p_stuid   IN t_stu.stuid%TYPE,
    p_stuname IN t_stu.stuname%TYPE,
    p_stusex  IN t_stu.stusex%TYPE,
    p_stuadd  IN t_stu.stuadd%TYPE,
    p_msg     OUT VARCHAR2
) AS
BEGIN
    MERGE INTO t_stu target
    USING (SELECT p_stuid AS stuid FROM dual) src
    ON (target.stuid = src.stuid)
    WHEN MATCHED THEN
        UPDATE SET stuname = p_stuname, stusex = p_stusex, stuadd = p_stuadd
    WHEN NOT MATCHED THEN
        INSERT (stuid, stuname, stusex, stuadd)
        VALUES (p_stuid, p_stuname, p_stusex, p_stuadd);
    
    p_msg := 'SUCCESS: Upserted ' || p_stuid;
EXCEPTION
    WHEN OTHERS THEN
        p_msg := 'ERROR: ' || SQLERRM;
END p_upsert_t_stu;
/
-- MERGE 原子性處理 INSERT/UPDATE，避免競態條件，效能優於分開判斷

5. 批次處理範本（FORALL + BULK COLLECT）

CREATE OR REPLACE PROCEDURE p_batch_upsert_stu (
    p_ids     IN SYS.ODCINUMBERLIST,
    p_names   IN SYS.ODCIVARCHAR2LIST,
    p_sexes   IN SYS.ODCIVARCHAR2LIST,
    p_addrs   IN SYS.ODCIVARCHAR2LIST,
    p_success OUT NUMBER,
    p_failed  OUT NUMBER
) AS
    v_cnt NUMBER;
BEGIN
    FORALL i IN p_ids.FIRST .. p_ids.LAST
        MERGE INTO t_stu target
        USING (SELECT p_ids(i) AS stuid FROM dual) src
        ON (target.stuid = src.stuid)
        WHEN MATCHED THEN
            UPDATE SET stuname = p_names(i), stusex = p_sexes(i), stuadd = p_addrs(i)
        WHEN NOT MATCHED THEN
            INSERT (stuid, stuname, stusex, stuadd)
            VALUES (p_ids(i), p_names(i), p_sexes(i), p_addrs(i));
    
    p_success := SQL%ROWCOUNT;
    p_failed  := 0;
EXCEPTION
    WHEN OTHERS THEN
        p_success := 0;
        p_failed  := p_ids.COUNT;
        -- 建議寫入錯誤日誌表
END p_batch_upsert_stu;
/
-- 單次呼叫處理陣列資料，大幅減少 Context Switch