Oracle stored procedure definition and debugging, and finally the C # calling code

C # Calling stored procedures

I have been involved in a lot of articles, such as the following text, is to share it

Purpose: To change the points and make certain verification

First, the General debugging method:

Method one: With the return out parameter, you must define the variable Myresult

DECLARE

Myresult INT;

Point VARCHAR2 (50); Suppose you want to use a character class variable, write the length.

BEGIN
P_changevipbalance (' 011111111 ', -1, ' TEST ', myresult);

END;

/*

Assume no output parameters. You can directly use:

Method Two: Call P_changevipbalance (' 018604712233 ', -1, ' TEST ');//If there is no number of parameters to output. That is, the stored procedure is defined as:

CREATE OR REPLACE PROCEDURE wx_120719_flxt.p_changevipbalance (vipno in VARCHAR2, point in VARCHAR2, Str_reason in Varch AR2)//compared to previous none:, myresult out int this out parameter */

Method Three:

The more specific debugging method is still plsql. Select the name of the procedure to debug and look for test. Suppose not to go inside. It seems like you want to compile, generate debug information, and so on.

Second, the 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;

Third, the stored procedure is called by C #. 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));
}

Oracle stored procedure definition and debugging, and finally the C # calling code