Oracle 存儲過程開發全流程：定義、PL/SQL 除錯、C# 呼叫實戰

Oracle 存儲過程開發全流程：定義、PL/SQL 除錯、C# 呼叫實戰

以「會員積分異動與備註記錄」為例，示範存儲過程建立、PL/SQL Developer 除錯、C# 端 OracleParameter 呼叫完整代碼。

存儲過程定義

CREATE OR REPLACE PROCEDURE WX_120719_FLXT.p_changevipbalance (
  vipno      IN  VARCHAR2,
  point      IN  VARCHAR2,
  str_reason IN  VARCHAR2,
  myresult   OUT INT
) IS
  point_old      INT;
  point_updated  INT;
  yhjerror       EXCEPTION;
  tempint        INT;
  tempa          INT;
BEGIN
  myresult := 0;

  SELECT m.points INTO point_old
  FROM membership m
  WHERE TRIM(m.member_code) = vipno;

  UPDATE membership m
  SET m.points = m.points + point,
      m.remark = TRIM(m.remark) || TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') || ':' || str_reason
  WHERE TRIM(m.member_code) = vipno;

  myresult := SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE(point_old);

  SELECT m.points INTO point_updated
  FROM membership m
  WHERE TRIM(m.member_code) = vipno;

  tempa := ABS(point_old - point_updated);
  tempint := ABS(point);

  IF (tempa <> tempint OR myresult <> 1) THEN
    myresult := 0;
    RAISE yhjerror;
  END IF;

EXCEPTION
  WHEN yhjerror THEN
    ROLLBACK;
  COMMIT;
END p_changevipbalance;

PL/SQL Developer 除錯步驟

1. 編譯時勾選 Generate Debug Information。
2. 在 Object Browser 右鍵過程 → Test，輸入參數執行，觀察變數變化、中斷點。

C# 呼叫代碼

public static string CAL_changevipbalancebyStoredProcedure(string vipno, int point, string str_reason)
{
  if (!exist_vip(vipno)) return "ERROR會員不存在";

  int old_points = int.Parse(CAL_VipBalance(vipno));

  OracleParameter[] parameters = {
    new OracleParameter("vipno", OracleType.VarChar, 50),
    new OracleParameter("point", OracleType.VarChar, 30),
    new OracleParameter("str_reason", OracleType.VarChar, 20),
    new OracleParameter("myresult", OracleType.Int32)
  };
  parameters[0].Value = vipno;
  parameters[1].Value = point;
  parameters[2].Value = str_reason;
  parameters[3].Value = 0;
  parameters[0].Direction = ParameterDirection.Input;
  parameters[1].Direction = ParameterDirection.Input;
  parameters[2].Direction = ParameterDirection.Input;
  parameters[3].Direction = ParameterDirection.Output;

  try {
    YHJ_StoredProcedure.RunProcedure("WX_120719_FLXT.p_changevipbalance", parameters);
    int kkk = Convert.ToInt32(parameters[3].Value);
    return (kkk >= 1) ? "SUCCESS" : "ERROR內部校驗出錯";
  } catch (Exception e) { throw e; }
}

關鍵點

• OUT 參數在 C# 必須設 Direction = ParameterDirection.Output，並在執行後讀取 Value。
• 過程內用 SQL%ROWCOUNT 判斷影響行數，配合積分差值雙重校驗，確保資料一致性。
• 異常區塊 ROLLBACK 後再 COMMIT 結束交易，避免懸置交易。