Oracle 存儲過程寫法規範與實戰範例：增、刪、改完整示範

核心規範

1. 使用 CREATE OR REPLACE PROCEDURE 確保冪等部署。
2. 參數模式明確：IN（輸入）、OUT（輸出）、IN OUT（雙向）。
3. 變量聲明區（IS/AS 至 BEGIN）嚴格區分類型，推薦使用 %TYPE 錨定欄位類型。
4. 業務邏輯區（BEGIN ... END）包含異常處理區（EXCEPTION ... END）。
5. DML 操作後顯式 COMMIT（除非由外層事務控制）。

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 p_insert_t_stu;
/

2. 刪除存儲過程（含異常處理與狀態返回）

CREATE OR REPLACE PROCEDURE p_delete_t_stu (
  p_stuid IN NUMBER,
  p_msg   OUT NVARCHAR2
) AS
  v_flag INTEGER := 1;
  v_stuid NUMBER;
BEGIN
  -- 檢查記錄是否存在
  SELECT 1 INTO v_stuid FROM t_stu WHERE stuid = p_stuid;

  DELETE FROM t_stu WHERE stuid = p_stuid;
  COMMIT;

  IF v_flag = 1 THEN
    p_msg := '刪除成功';
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_msg := '記錄不存在，刪除失敗';
  WHEN OTHERS THEN
    p_msg := SQLERRM || '，刪除失敗';
END p_delete_t_stu;
/

優化點：原文邏輯有誤（SELECT flag INTO v_stuid 但表無 flag 欄位），此處修正為檢查主鍵存在性；異常處理區分 NO_DATA_FOUND 與其他錯誤。

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, '指定學生ID不存在');
  END IF;

  COMMIT;
END p_update_t_stu;
/

進階建議

• 生產環境建議將 COMMIT 移至調用層，實現事務邊界統一控制。
• 大批量操作改用 FORALL + BULK COLLECT 提升性能。
• 敏感操作（刪除/修改）務必記錄審計日志（AUTONOMOUS_TRANSACTION 寫入日志表）。