oracle怎么传参,c# 用OracleParameter[]方法传参数很郁闷

满意答案

yuliuxing

推荐于 2016.03.21

采纳率：50%    等级：10

已帮助：1072人

下面解法引用自：http://blog.sina.com.cn/s/blog_4c81e6230100iy1h.html

C# 获取 oracle 存储过程的 返回值(2010-06-09 00:30:37)

存储过程

CREATE OR REPLACE PROCEDURE ADMIN.INSERT_OBJ (

OBJEFIRT_parms IN NVARCHAR2,

OBJEDATT_parms IN NVARCHAR2,

OBJESECT_parms IN NVARCHAR2,

OBJECONTENT_parms IN NVARCHAR2,

ISRELEASE_parms IN INTEGER,

OBJECODE_parms IN NVARCHAR2,

TAGS_parms IN NVARCHAR2,

OBJ_ID_parms OUT INTEGER

)

IS

BEGIN

INSERT INTO ADMIN.CB_OBJECT (OBJEID,

OBJEFIRT,

OBJEDATT,

OBJESECT,

OBJECONTENT,

ISRELEASE,

OBJECODE,

TAGS)

VALUES (ADMIN.CYANBIRD.NEXTVAL,

OBJEFIRT_parms,

TO_DATE (OBJEDATT_parms, 'YYYY-MM-DD hh24:mi:ss'),

OBJESECT_parms,

OBJECONTENT_parms,

ISRELEASE_parms,

OBJECODE_parms,

TAGS_parms)

RETURN OBJEID INTO OBJ_ID_parms;

RETURN;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL;

WHEN OTHERS

THEN

-- Consider logging the error and then re-raise

RAISE;

END INSERT_OBJ;

//C#中获取值

DbParameter[] paras = new OracleParameter[8];

paras[0] = new OracleParameter("OBJEFIRT_parms", OracleType.NVarChar);

paras[0].Value = cbinf.ObjeFirT;

paras[1] = new OracleParameter("OBJEDATT_parms", OracleType.NVarChar);

paras[1].Value=cbinf.ObjeDatT.ToString();

paras[2]= new OracleParameter("OBJESECT_parms",OracleType.NVarChar);

paras[2].Value = cbinf.ObjeSecT;

paras[3] = new OracleParameter("OBJECONTENT_parms", OracleType.NVarChar);

paras[3].Value = cbinf.ObjeContent;

paras[4] = new OracleParameter("ISRELEASE_parms", OracleType.Int32);

paras[4].Value = cbinf.IsRelease;

paras[5] = new OracleParameter("OBJECODE_parms", OracleType.NVarChar);

paras[5].Value = cbinf.ObjeCode;

paras[6] = new OracleParameter("TAGS_parms", OracleType.NVarChar);

paras[6].Value = cbinf.Tags;

paras[7] = new OracleParameter("OBJ_ID_parms", OracleType.Int32);

paras[7].Direction = ParameterDirection.Output;

SQLHelper.ExecuteScaler(InsertInfo_Obj, CommandType.StoredProcedure, paras);

int i=Convert.ToInt32(paras[7].Value);

return i;

另外如果要获取执行的行数，则依据Oracle的隐式参数：sql%rowcount，此参数用于记录受影响的行数，则给存储过程设置一个output的参数，用来记录这个值也可以。

这个参数的用法 select sql%rowcount into 参数 from dual

CREATE OR REPLACE PROCEDURE sp_UXXXXXX

(

rowAffected out number

, ......

)

IS

BEGIN

1、业务处理语句

2 select sql%rowcount into rowAffected from dual;

END;

00分享举报