c#中调用oracle数据库的高手进(100分,咨询函数调用问题)
现定义函数CS_CSJS_ZZ如下，在函数体中有一返回值return 1，不用看具体函数的意义，我现在就是先确定一下，调用函数都会有一个返回值，但是在该函数中return模块中变量较多，而最终只返回一个1，我该如何定义一个变量来获取这个return值
CREATE OR REPLACE FUNCTION CS_CSJS_ZZ
(an_zt_hh IN NUMBER,
an_kjnd IN NUMBER,
an_qsqj IN NUMBER,
an_jsqj IN NUMBER,
an_czyh_hh IN NUMBER
)
RETURN NUMBER IS
lvc_cwhdm VARCHAR2(32);
ln_kjzd_hh number(20);
ln_bwb_hh number(20);
li_qjjsq INTEGER;
CURSOR lc_kjkm IS
SELECT KJKM_HH FROM V_ZZ_ZTKM WHERE JL_SC_BZ = '0 ' AND KJND = an_kjnd AND ZT_HH = an_zt_hh ORDER BY kjkmdm;
lt_kjkm number(20);
ln_lsjl_hh number(20);
ln_qyqj number(10);
ln_sykjqj number(10);
ln_qynd number(4);
ln_nmqj number(10);
ln_ncqj number(10);
lc_yjz_bz char;
ln_jg NUMBER;
BEGIN
P_ZZ_QZTQYQJ(an_zt_hh,ln_qynd,ln_qyqj,ln_sykjqj,lc_yjz_bz);
SELECT MIN(QJXH), MAX(QJXH) INTO ln_ncqj, ln_nmqj FROM ZZ_KJQJ WHERE JL_SC_BZ = '0 ' AND QSRQ > = (SELECT QYRQ FROM ZZ_ZT WHERE JL_HH= an_zt_hh) AND KJND = an_kjnd AND ZT_HH = an_zt_hh;
SELECT DECODE(an_qsqj - 1,0,1,an_qsqj - 1), KJZD_HH, BWB_HH INTO ln_sykjqj, ln_kjzd_hh, ln_bwb_hh FROM ZZ_ZT WHERE JL_HH = an_zt_hh;
li_qjjsq := an_qsqj;
LOOP
EXIT WHEN li_qjjsq > an_jsqj;
IF li_qjjsq > = ln_ncqj AND li_qjjsq <= ln_nmqj THEN
SELECT JL_HH INTO ln_lsjl_hh FROM ZZ_KJQJ WHERE JL_SC_BZ = '0 ' AND QJXH = li_qjjsq AND KJND = an_kjnd AND ZT_HH = an_zt_hh FOR UPDATE;
OPEN lc_kjkm;
LOOP
FETCH lc_kjkm INTO lt_kjkm;
EXIT WHEN lc_kjkm%NOTFOUND;
ln_jg := CS_CSJS_KMQJQC_ZZ(an_zt_hh, lt_kjkm.KJKM_HH, an_kjnd, li_qjjsq, ln_sykjqj, ln_qynd, ln_qyqj, an_czyh_hh); ln_jg := CS_CSJS_KMQJQC_FZZZ(an_zt_hh, lt_kjkm.KJKM_HH, an_kjnd, li_qjjsq, ln_sykjqj, ln_qynd, ln_qyqj, an_czyh_hh);
ln_jg := CS_CSJS_KMQJ_ZZ(ln_kjzd_hh, an_zt_hh, ln_bwb_hh, lt_kjkm.KJKM_HH, an_kjnd, li_qjjsq, an_czyh_hh);
ln_jg := CS_CSJS_KMQJ_FZZZ(ln_kjzd_hh, an_zt_hh, ln_bwb_hh, lt_kjkm.KJKM_HH, an_kjnd, li_qjjsq, an_czyh_hh);
END LOOP;
CLOSE lc_kjkm;
COMMIT;
END IF;
ln_sykjqj := li_qjjsq;
li_qjjsq := li_qjjsq + 1;
END LOOP;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
IF lc_kjkm%ISOPEN THEN
CLOSE lc_kjkm;
END IF;
ROLLBACK;
END CS_CSJS_ZZ;
/
我的代码如下：
OracleConnection oc = new OracleConnection(conn);
OracleCommand ocd = new OracleCommand( "CS_CSJS_ZZ ", oc);
ocd.CommandType = CommandType.StoredProcedure;
OracleParameterCollection opc = ocd.Parameters;
opc.Clear();
opc.Add(new OracleParameter( "an_zt_hh ", OracleType.Number));
opc[ "an_zt_hh "].Value = 2302007041700000261;
opc.Add(new OracleParameter( "an_kjnd ", OracleType.Number));
opc[ "an_kjnd "].Value = 2007;
opc.Add(new OracleParameter( "an_qsqj ", OracleType.Number));
opc[ "an_qsqj "].Value = 4;
opc.Add(new OracleParameter( "an_jsqj ", OracleType.Number));
opc[ "an_jsqj "].Value = 5;
opc.Add(new OracleParameter( "an_czyh_hh ", OracleType.Number));
opc[ "an_czyh_hh "].Value = 99;
//return模块中声明的变量，这些变量不应该是rerurnvalue吧，该如何声明这些变量？
opc.Add(new OracleParameter( "lvc_cwhdm ", OracleType.VarChar,32));
opc[ "lvc_cwhdm "].Direction = ParameterDirection.Output;
opc.Add(new OracleParameter( "ln_kjzd_hh ", OracleType.Number));
opc[ "ln_kjzd_hh "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_bwb_hh ", OracleType.Number));
opc[ "ln_bwb_hh "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "li_qjjsq ", OracleType.Int32));
opc[ "li_qjjsq "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "lc_kjkm ", OracleType.Cursor));
opc[ "lc_kjkm "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "lt_kjkm ", OracleType.Number));
opc[ "lt_kjkm "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_lsjl_hh ", OracleType.Number));
opc[ "ln_lsjl_hh "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_qyqj ", OracleType.Number));
opc[ "ln_qyqj "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_sykjqj ", OracleType.Number));
opc[ "ln_sykjqj "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_qynd ", OracleType.Number));
opc[ "ln_qynd "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_nmqj ", OracleType.Number));
opc[ "ln_nmqj "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_ncqj ", OracleType.Number));
opc[ "ln_ncqj "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "lc_yjz_bz ", OracleType.Char,1));
opc[ "lc_yjz_bz "].Direction = ParameterDirection.ReturnValue;
opc.Add(new OracleParameter( "ln_jg ", OracleType.Number));
opc[ "ln_jg "].Direction = ParameterDirection.ReturnValue;
try
{
oc.Open();
decimal a = convert.todecimal(ocd.ExecuteScalar());
}
catch (OracleException oe)
{
MessageBox.Show(oe.Message);
}
finally
{
oc.Close();
}
声明return模块中的变量后,执行时就会抱ora-06550错误,
如果不声明return中的变量,就会提示cs_csjs_zz不是存储过程,如果对我的描述不是太清楚的话，可以提出来,我会把需要的东西都贴出来,希望大家能够帮忙解决.谢谢!!!!
[解决办法]
selet CS_CSJS_ZZ(parm1,parm2) as return_value from dual;
读取结果集就行了,一行,一列
[解决办法]
你这存储过程返回值只有一个呀，RETURN NUMBER。
怎么写那么多ParameterDirection.ReturnValue。
[解决办法]
你这函数只有一个返回值,要得到返回值selet CS_CSJS_ZZ(parm1,parm2) INTO ... from dual;
把函数的返回值放到一个变量里就成
