Oracle 預存程序寫法範本：INSERT、DELETE、UPDATE 三大核心操作標準模式

預存程序封裝業務邏輯、減少網路往返、防止 SQL 注入。本文提供增刪改三大核心操作的標準 PL/SQL 範本，含參數模式、異常處理、交易控制最佳實務。

範本一：INSERT（新增）

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;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20001, '學號重複：' || p_stuid);
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20002, '新增失敗：' || SQLERRM);
END p_insert_t_stu;

關鍵點：顯式列出欄位名、異常處理轉換為業務錯誤碼、失敗時 ROLLBACK。

範本二：DELETE（刪除）

CREATE OR REPLACE PROCEDURE p_delete_t_stu (
  p_stuid IN NUMBER,
  p_msg   OUT NVARCHAR2
) 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;
  COMMIT;
  p_msg := '刪除成功';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_msg := '查無此學號：' || p_stuid;
  WHEN OTHERS THEN
    ROLLBACK;
    p_msg := '刪除失敗：' || SQLERRM;
END p_delete_t_stu;

改進原文：移除多餘 flag 變數，改用 SELECT INTO 檢查存在性，語意更清晰。

範本三：UPDATE（修改）

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
  v_updated NUMBER;
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(-20003, '查無此學號：' || p_stuid);
  END IF;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20004, '修改失敗：' || SQLERRM);
END p_update_t_stu;

關鍵點：SQL%ROWCOUNT 判斷是否真正更新、失敗統一 ROLLBACK + RAISE_APPLICATION_ERROR 回傳統一錯誤格式。

進階最佳實務清單

呼叫範例

-- SQL*Plus
VAR msg NVARCHAR2(100);
EXEC p_delete_t_stu(1001, :msg);
PRINT msg;

-- Java (JDBC)
CallableStatement cs = conn.prepareCall("{call p_delete_t_stu(?, ?)}");
cs.setInt(1, 1001);
cs.registerOutParameter(2, Types.NVARCHAR);
cs.execute();
String msg = cs.getString(2);

遵循「參數錨定、顯式欄位、統一異常碼、交易邊界清晰、日誌可追溯」五大原則，撰寫出可維護、可擴展、安全可靠的企業級預存程序。