请教BCB用存储过程调用Oracle函数的问题
遇到这样一个问题:用BCB怎样用存储过程调用Oracle存的函数?
怎样设置存储过程的参数?调用函数需要取得返回值,请问返回值参数怎么设置?
//我的代码,
adosp1->Close();
adosp1->ConnectionString = ConnStrSvr;
//adosp1->ProcedureName = "MES.iBizMDI.SetRecMark";
adosp1-> Parameters-> Clear();
adosp1-> ProcedureName = "MES.iBizMDI.SetRecMark";
adosp1-> Parameters-> CreateParameter( "p_IACNo", ftString, pdInput, 20, OleVariant(MesInfo.jiaohu));
adosp1-> Parameters-> CreateParameter( "p_VerifyCode", ftString, pdInput, 20, OleVariant(NetPara.vword));
adosp1-> Open();
上面是我测试用的代码,执行时提示“调用时参数个数或类型错误”,错误在哪儿,请指正!万分感谢!
//附 Oracle函数定义
--设置接收标记
FUNCTION SetRecMark(p_IACNo VARCHAR2,p_VerifyCode VARCHAR2) RETURN VARCHAR2
IS
CURSOR cursor_MDIACRecord IS
SELECT OppNo,RecMark
FROM SYSMD_IACRecord
WHERE IACNo = p_IACNo;
v_RI cursor_MDIACRecord%ROWTYPE;
BEGIN
OPEN cursor_MDIACRecord;
FETCH cursor_MDIACRecord INTO v_RI;
IF cursor_MDIACRecord%NOTFOUND THEN
CLOSE cursor_MDIACRecord;
RETURN '交互号不正确!';
END IF;
CLOSE cursor_MDIACRecord;
IF NOT iVerifyOpp(v_RI.OppNo,p_VerifyCode) THEN
RETURN '验证码无效!';
END IF;
IF v_RI.RecMark = '!' OR TRIM(v_RI.RecMark) IS NULL THEN
UPDATE SYSMD_IACRecord SET
RecMark = '#',
RecTime = TO_CHAR(SYSDATE,'yyyymmddhh24miss')
WHERE IACNo = p_IACNo;
COMMIT;
RETURN NULL;
ELSE
RETURN '已经设置过接收标记!';
END IF;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RETURN '设置接收标记时异常:'|| CHR(13)||CHR(10) || '错误号:' || SQLCODE ||CHR(13)||CHR(10)|| '错误信息:'||SQLERRM;
END;
[解决办法]
我这里有一个MS SQL调用存储过程并带返回的例子
只是Oracle跟MSSQL的语法有区别,对于ADOQuery1的用法都是一样的
你可以参考,
http://blog.csdn.net/zhouzhangkui/archive/2010/05/16/5597549.aspx
[解决办法]
用TADOQuery来搞就行了。
ADOQuery1->Close();String str = String().sprintf( TEXT("exec MES.iBizMDI.SetRecMark ") TEXT("@p_IACNo=:IACNo,") TEXT("@p_VerifyCode=:VerifyCode"));ADOQuery1->SQL->Text = str;ADOQuery1->Parameters->Items[0]->Value = "参数1的值";ADOQuery1->Parameters->Items[1]->Value = "参数2的值";ADOQuery1->ExecSQL();
我的异常网推荐解决方案:oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html