Oracle 預存程序實戰：INSERT、DELETE、UPDATE 完整範本與最佳實務

資料表結構

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 NUMBER,
  p_stuname IN NVARCHAR2,
  p_stusex  IN NVARCHAR2,
  p_stuadd  IN NVARCHAR2
) AS
BEGIN
  INSERT INTO t_stu (stuid, stuname, stusex, stuadd)
  VALUES (p_stuid, p_stuname, p_stusex, p_stuadd);
  COMMIT;
END;
/

2. 刪除資料：含例外處理與回傳訊息

CREATE OR REPLACE PROCEDURE p_delete_t_stu (
  p_stuid IN NUMBER,
  p_msg   OUT NVARCHAR2
) IS
  v_exists NUMBER;
BEGIN
  -- 檢查資料是否存在
  SELECT COUNT(*) INTO v_exists FROM t_stu WHERE stuid = p_stuid;
  
  IF v_exists = 0 THEN
    p_msg := '查無此學號，刪除失敗';
    RETURN;
  END IF;
  
  DELETE FROM t_stu WHERE stuid = p_stuid;
  COMMIT;
  p_msg := '刪除成功';
  
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    p_msg := '刪除失敗: ' || SQLERRM;
END;
/

3. 修改資料：基本範本

CREATE OR REPLACE PROCEDURE p_update_t_stu (
  p_stuid   IN NUMBER,
  p_stuname IN NVARCHAR2,
  p_stusex  IN NVARCHAR2,
  p_stuadd  IN NVARCHAR2
) AS
BEGIN
  UPDATE t_stu
  SET stuname = p_stuname,
      stusex  = p_stusex,
      stuadd  = p_stuadd
  WHERE stuid = p_stuid;
  
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '查無此學號，修改失敗');
  END IF;
  
  COMMIT;
END;
/

呼叫範例

-- 新增
EXEC p_insert_t_stu(1, '王小明', '男', '台北市信義區');

-- 刪除（需變數接收 OUT 參數）
VARIABLE v_msg NVARCHAR2(100);
EXEC p_delete_t_stu(1, :v_msg);
PRINT v_msg;

-- 修改
EXEC p_update_t_stu(1, '王大明', '男', '新北市板橋區');

最佳實務重點

• 明確欄位清單：INSERT INTO table (col1, col2)... 避免結構變更導致錯誤
• 例外處理：捕獲 WHEN OTHERS 並 ROLLBACK，回傳錯誤碼與訊息
• 檢查影響列數：SQL%ROWCOUNT 確認 DML 實際影響筆數
• 使用錯誤代碼：RAISE_APPLICATION_ERROR(-20xxx, 'msg') 讓呼叫端可程式化處理
• 避免在 Procedure 中 COMMIT：交易控制宜由呼叫端（應用層）決定，除非獨立業務單元

進階擴充

• 批次處理：使用 FORALL + Collection 提升大量 DML 效能
• 動態 SQL：EXECUTE IMMEDIATE 處理動態表名/欄位
• 記錄稽核：整合 DBMS_APPLICATION_INFO 設定模組/動作資訊

預存程序封裝業務邏輯、減少網路往返、強化安全性。善用參數模式（IN/OUT/IN OUT）、例外處理與交易控制，能建立健壯且可維護的資料存取層。