oracle存储过程定义及调试，并最终被C#调用代码

oracle存储过程定义及调试，并最终被C#调用代码

C# 调用存储过程

参考了很多文章，写了如下文字，算是分享吧

目的：更改积分，并作一定校验

一般的调试方法：

DECLARE
myresult INT;
BEGIN
P_CHANGEVIPBALANCE('018604712233',-1,'TEST',myresult);
END;

更详细的调试方法还是PLSQL，选择要调试的过程名，找test，如果不能进入到里面去，好像要要编译，生成debug信息之类的。

存储过程定义：2015-7-18 20:00:21更新备注计算方法

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 -- AND
BEGIN
myresult:=0;
RAISE yhjerror;
end;
END IF ;
EXCEPTION
WHEN yhjerror THEN
BEGIN
ROLLBACK;
END;
COMMIT;
END p_changevipbalance;

C#调用代码：

public static String CAL_changevipbalancebyStoredProcedure(string vipno, int point, string str_reason )
{//需要增加日志
string strCmd = OracleAccess.str_vip_update1 + point + OracleAccess.str_vip_update2 + vipno.Trim() + "\'";
int old_points = 0; int after_point = 0; int kkk = 0; int myresult = 0;
//i = Convert.ToInt32(DBUtil.SqlExecuteScalar(strCmd.Replace("TESTA", OracleAccess.str_USER)));
if (exist_vip(vipno))
{
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 = myresult; //parameters[13].Value = str_sku;// parameters[10].Value = str_no; parameters[11].Value = str_sku;
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);
kkk = Convert.ToInt32(parameters[3].Value);
if (kkk>=1)
return OracleAccess.str_db_operate_sucess;
else
return "ERROR内部校验出错";


}
catch (Exception e)
{
throw e;
}

//DBUtil.SqlExecuteNonQuery(strCmd.Replace("TESTA", OracleAccess.str_USER)); //ok 防注入，不用
//after_point = int.Parse(CAL_VipBalance(vipno));
//if ((after_point - old_points) == point)
// return OracleAccess.str_db_operate_sucess;
//else
// return "ERROR内部校验出错";
}
else
return "ERROR会员不存在";
//return Convert.ToInt32(DBUtil.SqlExecuteScalar(strCmd.Replace("TESTA", OracleAccess.str_USER)));
}