Oracle stored procedure definition and debugging, and ultimately by C # calling code

C # Calling stored procedures

Refer to a lot of articles, write the following text, is to share it

Purpose: To change the points and make certain verification

General Debugging methods:

DECLARE
Myresult INT;
BEGIN
P_changevipbalance (' 018604712233 ', -1, ' TEST ', myresult);

END;

More detailed debugging method or Plsql, select the process name to debug, find test, if not into the inside, as if to compile, generate debug information and so on.

Stored procedure definition: 2015-7-18 20:00:21 Update Comment calculation method
CREATE OR REPLACE PROCEDURE wx_120719_flxt.p_changevipbalance (vipno in VARCHAR2, point in VARCHAR2, Str_reason in Varch AR2, 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 # calling Code:

public static string Cal_changevipbalancebystoredprocedure (string vipno, int point, string Str_reason)
{//need to add log
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 internal check errors";


}
catch (Exception e)
{
Throw e;
}

Dbutil.sqlexecutenonquery (Strcmd.replace ("TESTA", Oracleaccess.str_user)); OK anti-injection, no
after_point = Int. Parse (Cal_vipbalance (vipno));
if ((after_point-old_points) = = point)
return oracleaccess.str_db_operate_sucess;
Else
Return "Error internal check errors";
}
Else
Return "error member does not exist";
Return Convert.ToInt32 (Dbutil.sqlexecutescalar (Strcmd.replace ("TESTA", Oracleaccess.str_user));
}

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle stored procedure definition and debugging, and ultimately by C # calling code