oracle 存储过程 传参数,如果是希望传参值也被利用起来,存储过程里面虽有IN,但OUT必不可少。
oracle 存储过程 传参数,如果是希望传参值也利用起来,如果在 存储过程里面该参数只写 IN,而不写OUT,那只会返回部分数据。而加上OUT,最终写成IN OUT,则完全无问题,请大家看注释部分。
int point = 0; string MYSTATUS = ""; int kkk = 0;
//存储过程的参数声明
OracleParameter[] parameters ={
new OracleParameter("MYMEMBER_CODE",OracleType.VarChar,50),
new OracleParameter("point",OracleType.VarChar,30),
new OracleParameter("MYSTATUS",OracleType.VarChar,20)
};
parameters[0].Value = strvip; parameters[1].Value = point; parameters[2].Value = MYSTATUS;
parameters[0].Direction = ParameterDirection.InputOutput; parameters[1].Direction = ParameterDirection.Output; parameters[2].Direction = ParameterDirection.Output;
try
{
OracleAccess.logger.Debug(" p_GetVipBalanceStoredProcedure 执行前 " + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString() + strvip );
if (exist_vipProcedure(strvip))
{
YHJ_StoredProcedure.RunProcedure("p_GetVipBalanceStoredProcedure", parameters);
OracleAccess.logger.Debug(" p_GetVipBalanceStoredProcedure 执行后 " + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString() + strvip);
kkk = Convert.ToInt32(parameters[2].Value);
if (kkk >= 1)
{
vipinf myvipinf = new vipinf();
myvipinf.str_point = parameters[1].Value.ToString();//myvipinf.str_grade=parameters[2].Value.ToString();myvipinf.str_coupon_int=parameters[3].Value.ToString();
myvipinf.str_membercode = parameters[0].Value.ToString();//第一个参数不加OUT 只返回前5个字符,如果加了OUT则返回完整字符。
myvipinf.str_discount = "0.9";
return myvipinf;
}
}
}
catch (Exception e)
{
OracleAccess.logger.Debug(" p_GetVipBalanceStoredProcedure 执行前 " + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString() + strvip+e.Message.ToString());
throw e;
}
存储过程:
CREATE OR REPLACE PROCEDURE p_GetVipBalanceStoredProcedure ( MYMEMBER_CODE IN OUT VARCHAR2 , point OUT VARCHAR2,MYSTATUS OUT VARCHAR2 )
IS
point_old INT;
point_updated INT;
yhjerror EXCEPTION;
tempint INT;
tempa INT;
myresult INT;
BEGIN
myresult:=0;MYSTATUS:='';
SELECT M.POINTS INTO point_old FROM MEMBERSHIP M WHERE TRIM(M.MEMBER_CODE) = MYMEMBER_CODE;
myresult:=SQL%ROWCOUNT;
point:=point_old;
MYSTATUS:=myresult;
IF ((myresult<>1) OR (myresult=0)) THEN
BEGIN
MYSTATUS:=0;
end;
END IF ;
END p_GetVipBalanceStoredProcedure;