存储过程的一个奇怪问题

来源：互联网发布：淘宝李宁三号店是高仿编辑：程序博客网时间：2024/06/02 11:56

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

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

这个存储过程执行结果是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