该存储过程如何改成绑定变量的方式？

[好记忆不如烂笔头abc]

环境：linux6.6+oracle11.2.0.4

某存储过程代码如下：

create or replace procedure sp_test IS
TYPE myCur IS REF CURSOR;
TmpCur myCur;
tcur myCur;

vTasknodeID varchar2(64);

vTaskDefineID varchar2(64);
vReceiverDefineID varchar2(64);
vReceiverTime DATE;
vProccessID varchar2(64);
nLimitTime NUMBER;

vSql varchar2(1024);

--得到取得获取时间的ID
CURSOR getReceiverDefineID(vCurNodeID varchar2) IS
SELECT nd.fldreceivetimedefineid FROM test_define nd WHERE nd.fldid = vcurNodeID;

BEGIN
OPEN TmpCur FOR 'SELECT n.fldid FROM test n WHERE n.fldisdone <> ''' || 1 || ''' OR n.fldisdone IS NULL ';
FETCH Tmpcur INTO vTasknodeID;
WHILE TmpCur%FOUND LOOP

--根据当前的环节ID来处理
vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ''' || vTasknodeID || '''';
OPEN tcur FOR vsql;
FETCH tcur INTO vTaskDefineID,vReceiverTime,vProccessID;
CLOSE tcur;

--看这个环节是否需要从其他环节得到接收时间；
OPEN getReceiverDefineID(vTasknodeID);
FETCH getReceiverDefineID INTO vReceiverDefineID;
CLOSE getReceiverDefineID;

IF vReceiverDefineID IS NOT NULL THEN
--则从定义的环节得到时间；
SELECT n.fldreceivetime INTO vReceiverTime FROM test n
WHERE n.fldprocessid = vProccessID AND n.flddefineid = vReceiverDefineID;
END IF;

--计算历时
SELECT ROUND((SYSDATE - to_date(vReceiverTime,'yyyy-mm-dd hh24:mi:ss')) * 24,2) INTO nLimitTime FROM dual;

--将历时写入到数据库中
IF vReceiverDefineID IS NOT NULL THEN
--说明这些环节都计算为同一个环节；
UPDATE test n SET n.fldlimittime = nLimitTime WHERE n.fldprocessid = vProccessID AND n.flddefineid IN (
SELECT nd.fldid FROM test_define nd WHERE nd.fldreceivetimedefineid = vReceiverDefineID );
ELSE
--就更改当前的环节的历时信息即可
UPDATE test n SET n.fldlimittime = nLimitTime WHERE n.fldid = vTasknodeID;
END IF;

FETCH Tmpcur INTO vTasknodeID;
END LOOP;
CLOSE TmpCur;

COMMIT;

end sp_test;

存储过程运行频繁，红色代码导致大量的硬解析，如何改写成绑定变量的方式？谢谢！

[卖水果的net]

vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ''' || vTasknodeID || ''''; OPEN tcur FOR vsql; -- 这样试试 open tcur for select ... from n whre fid = v_taskid;

[好记忆不如烂笔头abc]

引用 1 楼 卖水果的net 的回复:

vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ''' || vTasknodeID || ''''; OPEN tcur FOR vsql; -- 这样试试 open tcur for select ... from n whre fid = v_taskid;

测试了不行，还是没有用上绑定变量。

[卖水果的net]

vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ''' || vTasknodeID || ''''; OPEN tcur FOR vsql; 那就这样改一下 vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ? ; OPEN tcur FOR vsql using vTasknodeID;

[好记忆不如烂笔头abc]

引用 3 楼 卖水果的net 的回复:

vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ''' || vTasknodeID || ''''; OPEN tcur FOR vsql; 那就这样改一下 vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ? ; OPEN tcur FOR vsql using vTasknodeID;

还是不行，奇怪了

[好记忆不如烂笔头abc]

引用 4 楼 自由与责任 的回复:

[quote=引用 3 楼 卖水果的net 的回复:] vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ''' || vTasknodeID || ''''; OPEN tcur FOR vsql; 那就这样改一下 vsql := 'SELECT n.flddefineid,n.fldreceivetime,n.fldprocessid FROM test n WHERE n.fldid = ? ; OPEN tcur FOR vsql using vTasknodeID;

还是不行，奇怪了[/quote] 搞错了，这种方式可以了。非常感谢版主！