我在做一个项目的时候遇到了一个有关于存储过程的很奇怪的问题:
我的这个存储过程的where有点复杂:
SELECT
'CN101'||SUBSTR(A.LNDT,4,1)||'1',
F.CUSTCOD
FROM LC A,
AM B ,
HHB C ,
TABCHMAS D ,
DEPBIMAP E ,
HKBBIMAS F
WHERE a.CRNO = b.CRNO AND a.STCD NOT IN ('0','3')
AND (((SUBSTR(b.ACIT,1,3) IN ('123','124','125','126','127','128','130') OR ( b.ACIT='40602' AND B.ACBL>0) )
AND SUBSTR(b.ACIT,4,2) NOT IN ('18','19','20','21','22'))
OR (SUBSTR(b.ACIT,1,5) IN ('13003','13004') AND A.MOTP<>'1' AND A.LNOT NOT IN ('900','906','907')))
AND b.acsf = (SELECT max(acsf) FROM YZSRC.am WHERE crno =a.crno)
AND TRIM(D.REFNON) = TRIM(C.NEW_BRCH)
AND TRIM(C.OLD_BRCH) = TRIM(A.LNBR)
AND TRIM(E.CUSTSEQ) = TRIM(A.CUNO)
AND E.CUSTCOD = F.CUSTCD
and not exists(select 1 from cn_to_in h where h.crno = a.crno );
通过查询该sql的数据总数是5665条,这样的数据可以确定没错;存储过程为:
CREATE OR REPLACE PROCEDURE COV_LOAN_HKBCNMAS(vCVDT IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE YZSRC.HKBCNMAS_BAK';
commit;
INSERT INTO YZSRC.HKBCNMAS_BAK SELECT
'CN101'||SUBSTR(A.LNDT,4,1)||'1',
F.CUSTCOD
FROM LC A,
AM B ,
HHB C ,
TABCHMAS D ,
DEPBIMAP E ,
HKBBIMAS F
WHERE a.CRNO = b.CRNO AND a.STCD NOT IN ('0','3')
AND (((SUBSTR(b.ACIT,1,3) IN ('123','124','125','126','127','128','130') OR ( b.ACIT='40602' AND B.ACBL>0) )
AND SUBSTR(b.ACIT,4,2) NOT IN ('18','19','20','21','22'))
OR (SUBSTR(b.ACIT,1,5) IN ('13003','13004') AND A.MOTP<>'1' AND A.LNOT NOT IN ('900','906','907')))
AND b.acsf = (SELECT max(acsf) FROM YZSRC.am WHERE crno =a.crno)
AND TRIM(D.REFNON) = TRIM(C.NEW_BRCH)
AND TRIM(C.OLD_BRCH) = TRIM(A.LNBR)
AND TRIM(E.CUSTSEQ) = TRIM(A.CUNO)
AND E.CUSTCOD = F.CUSTCD
and not exists(select 1 from cn_to_in h where h.crno = a.crno );
end COV_LOAN_HKBCNMAS;
这个存储过程执行结果是788040条数据,凡是与LC有关的数据都只取到了1条,其它几个表的数据却是正常的,同时了存储过程也没有报错,我使用很多种方法都没有解决问题;如果在pl/sql中直接insert数据条数也是5665条,并且数据和select的结果一致;后来就把存储过程修改了一下:
CREATE OR REPLACE PROCEDURE COV_LOAN_HKBCNMAS(vCVDT IN VARCHAR2)
AS
Type HKBCNMAS_BAKxx is table of hkbcnmas_bak%rowtype;
hkbcnmas_bak11 HKBCNMAS_BAKxx;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE YZSRC.HKBCNMAS_BAK';
commit;
SELECT
'CN101'||SUBSTR(A.LNDT,4,1)||'1',
F.CUSTCOD
FROM YZSRC.LC A,
YZSRC.AM B ,
YZSRC.HHB C ,
YZSRC.TABCHMAS D ,
BSBUAT1.DEPBIMAP E ,
BSBUAT1.HKBBIMAS F
WHERE a.CRNO = b.CRNO AND a.STCD NOT IN ('0','3')
AND (((SUBSTR(b.ACIT,1,3) IN ('123','124','125','126','127','128','130') OR ( b.ACIT='40602' AND B.ACBL>0) )
AND SUBSTR(b.ACIT,4,2) NOT IN ('18','19','20','21','22'))
OR (SUBSTR(b.ACIT,1,5) IN ('13003','13004') AND A.MOTP<>'1' AND A.LNOT NOT IN ('900','906','907')))
AND b.acsf = (SELECT max(acsf) FROM YZSRC.am WHERE crno =a.crno)
AND TRIM(D.REFNON) = TRIM(C.NEW_BRCH)
AND TRIM(C.OLD_BRCH) = TRIM(A.LNBR)
AND TRIM(E.CUSTSEQ) = TRIM(A.CUNO)
AND E.CUSTCOD = F.CUSTCD
and not exists(select 1 from cn_to_in h where h.crno = a.crno );
FORALL X IN hkbcnmas_bak11.FIRST..hkbcnmas_bak11.LAST
INSERT INTO HKBCNMAS_BAK VALUES hkbcnmas_bak11(X);
COMMIT;
end COV_LOAN_HKBCNMAS;
这样修改后执行结果就对了,跟select的结果完全一致。但是我一至没有弄懂第一个存储过程发生错误的原因是为什么?请名位给予指点!谢谢..........
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wangsu854/archive/2010/06/05/5649477.aspx