The oracle stored procedure is defined and debugged and finally called by C,

The oracle stored procedure is defined and debugged and finally called by C,

C # Call a stored procedure

I have read a lot of articles and wrote the following text to share it.

Purpose: To change the credits and perform a certain verification

I. general debugging methods:

Method 1: With the out parameter returned, the variable myresult must be defined.

DECLARE

Myresult INT;
BEGIN
P_CHANGEVIPBALANCE ('20140901',-1, 'test', myresult );

END;

/*

If no output parameter is provided, you can directly use:

Method 2: call P_CHANGEVIPBALANCE ('20140901',-1, 'test'); // assume that there is no parameter to be output, that is, the stored procedure is defined:

Create or replace procedure WX_120719_FLXT.p_changevipbalance (vipno IN VARCHAR2, point in VARCHAR2, str_reason IN VARCHAR2) // The OUT parameter ", myresult out int */

Method 3:

The more detailed debugging method is PLSQL. Select the process name to be debugged and find test. If you cannot enter it, it seems that you want to compile and generate debug information.

Ii. Stored Procedure Definition: 20:00:21 updated remarks Calculation Method
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;
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;

3. The stored procedure is called by C #. Code:

Public static String CAL_changevipbalancebyStoredProcedure (string vipno, int point, string str_reason)
{// Logs need to be added
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 verification ERROR ";


}
Catch (Exception e)
{
Throw e;
}

// DBUtil. SqlExecuteNonQuery (strCmd. Replace ("TESTA", OracleAccess. str_USER); // OK anti-injection, no need
// After_point = int. Parse (CAL_VipBalance (vipno ));
// If (after_point-old_points) = point)
// Return OracleAccess. str_db_operate_sucess;
// Else
// Return "ERROR internal verification ERROR ";
}
Else
Return "ERROR member does not exist ";
// Return Convert. ToInt32 (DBUtil. SqlExecuteScalar (strCmd. Replace ("TESTA", OracleAccess. str_USER )));
}

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.