# 存储过程的一个奇怪问题

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 );

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;

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;

• 1
点赞
• 0
收藏
• 打赏
• 0
评论
11-03 610
03-25 1209
02-20 412
08-05 3377
01-21 2万+
03-17 4217
10-30 1104
09-07 9063
09-07 6376
06-19 4万+
04-18 4945
09-07 886
03-22 371

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

wangsu854

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。