请教：现有两段程序，区别仅在于一段绑定变量，另外一段直接写入。绑定变量的提示ora-01008错误。直接写入的运行成功。变量在前面都已经声明。
1、提示ora-01008错误
v_sql:='MERGE INTO LDM.LSTDAY_CTR_MSG_TP_TAB A
USING (SELECT :f MSG_TP_DT,
B.REG_COD REG_COD,
B.LOAN_CTR_NO LOAN_CTR_NO,
B.TODAY_MSG_TP TODAY_MSG_TP,
DECODE(C.LSTDAY_MSG_TP,NULL,NULL,C.LSTDAY_MSG_TP) LSTDAY_MSG_TP,
DECODE(C.IS_NRML_CHNG,NULL,''Y'',DECODE(C.LSTDAY_MSG_TP,''17'',DECODE(B.TODAY_MSG_TP,''20'',''Y'',''N''),''N'')) IS_NRML_CHNG
FROM (SELECT MSG_TP_DT,REG_COD,LOAN_CTR_NO,TODAY_MSG_TP,LSTDAY_MSG_TP,IS_NRML_CHNG
FROM LDM.LSTDAY_CTR_MSG_TP_TAB
WHERE MSG_TP_DT=:g
AND REG_COD=:h) C,
ODS.MSG_CTR_CHNG_TAB B
WHERE B.REG_COD=C.REG_COD(+)
AND B.LOAN_CTR_NO=C.LOAN_CTR_NO(+)
AND B.REG_COD=:i
)D
ON (A.MSG_TP_DT=D.MSG_TP_DT AND A.REG_COD=D.REG_COD AND A.LOAN_CTR_NO=D.LOAN_CTR_NO)
WHEN MATCHED THEN UPDATE SET A.TODAY_MSG_TP=D.TODAY_MSG_TP,
A.IS_NRML_CHNG=D.IS_NRML_CHNG
WHEN NOT MATCHED THEN INSERT(MSG_TP_DT,
REG_COD,
LOAN_CTR_NO,
TODAY_MSG_TP,
LSTDAY_MSG_TP,
IS_NRML_CHNG)
VALUES(D.MSG_TP_DT,
D.REG_COD,
D.LOAN_CTR_NO,
D.TODAY_MSG_TP,
D.LSTDAY_MSG_TP,
D.IS_NRML_CHNG)';
EXECUTE IMMEDIATE v_sql USING v_HpndDate,v_HpndDate,v_AreaNo,v_AreaNo;
2、运行成功
MERGE INTO LDM.LSTDAY_CTR_MSG_TP_TAB A
USING (SELECT v_HpndDate MSG_TP_DT,
B.REG_COD REG_COD,
B.LOAN_CTR_NO LOAN_CTR_NO,
B.TODAY_MSG_TP TODAY_MSG_TP,
DECODE(C.LSTDAY_MSG_TP,NULL,NULL,C.LSTDAY_MSG_TP) LSTDAY_MSG_TP,
DECODE(C.IS_NRML_CHNG,NULL,'Y',DECODE(C.LSTDAY_MSG_TP,'17',DECODE(B.TODAY_MSG_TP,'20','Y','N'),'N')) IS_NRML_CHNG
FROM (SELECT MSG_TP_DT,REG_COD,LOAN_CTR_NO,TODAY_MSG_TP,LSTDAY_MSG_TP,IS_NRML_CHNG
FROM LDM.LSTDAY_CTR_MSG_TP_TAB
WHERE MSG_TP_DT=v_HpndDate
AND REG_COD=v_AreaNo) C,
ODS.MSG_CTR_CHNG_TAB B
WHERE B.REG_COD=C.REG_COD(+)
AND B.LOAN_CTR_NO=C.LOAN_CTR_NO(+)
AND B.REG_COD=v_AreaNo
)D
ON (A.MSG_TP_DT=D.MSG_TP_DT AND A.REG_COD=D.REG_COD AND A.LOAN_CTR_NO=D.LOAN_CTR_NO)
WHEN MATCHED THEN UPDATE SET A.TODAY_MSG_TP=D.TODAY_MSG_TP,
A.IS_NRML_CHNG=D.IS_NRML_CHNG
WHEN NOT MATCHED THEN INSERT(MSG_TP_DT,
REG_COD,
LOAN_CTR_NO,
TODAY_MSG_TP,
LSTDAY_MSG_TP,
IS_NRML_CHNG)
VALUES(D.MSG_TP_DT,
D.REG_COD,
D.LOAN_CTR_NO,
D.TODAY_MSG_TP,
D.LSTDAY_MSG_TP,
D.IS_NRML_CHNG);
|
