c#调用oralce函数错误(孟子等高手来,救救我)
不知道该如何调用这个函数,大哥大姐们,都来帮帮忙啊!!
函数如下,该函数有返回的是1,
具体的函数体不需要看,前两天有人告诉我使用 select cs_csjs_zz(4102006102900000037,2007,1,1,99) as return_value from dual这种方法来调用函数,但是出现错误
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "FINAPP.CS_CSJS_ZZ ", line 74
ORA-14551: cannot perform a DML operation inside a query
select cs_csjs_zz(4102006102900000037,2007,1,1,99) as return_value from dual
函数:
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 ZZ_ZT.KJZD_HH%TYPE;
ln_bwb_hh ZZ_ZT.BWB_HH%TYPE;
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 lc_kjkm%ROWTYPE;
ln_lsjl_hh ZZ_KJQJ.JL_HH%TYPE;
ln_qyqj ZZ_KJQJ.QJXH%TYPE;
ln_sykjqj ZZ_KJQJ.QJXH%TYPE;
ln_qynd ZZ_KJQJ.KJND%TYPE;
ln_nmqj ZZ_KJQJ.QJXH%TYPE;
ln_ncqj ZZ_KJQJ.QJXH%TYPE;
lc_yjz_bz ZZ_KJQJ.SFYJZ_BZ%TYPE;
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;
/
[解决办法]
自己可以先在PL/SQL裏面測試一下啊
[解决办法]
错误提示很明显：查询中不能执行数据定义或者commit，rollback等操作
[解决办法]
CALL cs_csjs_zz(4102006102900000037,2007,1,1,99)就可以了
使用C#中的方法直接执行这句话,也可以带参数
参考MSDN
[解决办法]
楼主,没有看清楚,你写的是function
你应该这样写
BEGINE
numReturn := CS_CSJS_ZZ(4102006111100000014,2007,1,1,99);
END;
大概是这样的，具体你再去看看。
我的理解是call 只能调procedure ，不能直接调function 。所以你只能把它放在一个模块中。
大概是这样的，搂住可以再试试。你也可以把你的function 定义为procedure ，使用原来的方法调用。
