存储过程的一个奇怪问题

我在做一个项目的时候遇到了一个有关于存储过程的很奇怪的问题:

我的这个存储过程的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

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭