1、*/ FOR I IN (SELECT A.ID AS ID, A.DJBID AS DJBID, A.RYID AS RYID, C.DQYWHJ AS DQYWHJ, C.DQYWZT AS DQYWZT, A.SLLB AS SLLB, A.SLYY AS SLYY, A.SLRQ AS SLRQ, A.SLDWDM AS SLDWDM, A.SLR AS SLR, A.SFKS AS SFKS, A.SFDYLQPZ AS SFDYLQPZ, A.DYLQPZRQ AS DYLQPZRQ, A.SFYJF AS SFYJF, A.HZBH AS HZBH, A.HZFFRQ AS HZ
2、FFRQ, A.HZFFR AS HZFFR, A.SFSH AS SFSH, A.SHSFTG AS SHSFTG, A.SHBTGYY AS SHBTGYY, C.SHR AS SHR, A.SHRQ AS SHRQ, A.SHDW AS SHDW, A.SFZZ AS SFZZ, C.ZZDWDM AS ZZDWDM, A.ZZRQ AS ZZRQ, A.SFCXZZ AS SFCXZZ, A.BLSFFF AS BLSFFF, A.BLFFPH AS BLFFPH, A.BLFFDWDM AS BLFFDWDM, A.BLFFR AS BLFFR, A.BLFFRQ AS BLFFRQ
3、, A.JSDWDM AS JSDWDM, A.JSR AS JSR, A.JSRQ AS JSRQ, A.SFFF AS SFFF, C.FFR AS FFR, A.FFDWDM AS FFDWDM, A.FFRQ AS FFRQ, B.XM AS LQR, A.LQRQ AS LQRQ, A.CXLX AS CXLX, A.CXNR AS CXNR, A.CXDW AS CXDW, A.CXR AS CXR, A.CXRQ AS CXRQ, A.BZ AS BZ, A.SJCSPH AS SJCSPH, A.CJR AS CJR, A.CJSJ AS CJSJ, A.GXR AS GXR,
4、 A.GXSJ AS GXSJ, A.SFTH AS SFTH, A.SFZZYW AS SFZZYW, A.SFDCL AS SFDCL, A.SFCX AS SFCX, A.ZZSFCG AS ZZSFCG, A.ZZSBYY AS ZZSBYY, A.SFDZZ AS SFDZZ, A.DZZRQ AS DZZRQ, A.ZZPH AS ZZPH, A.CXZZCS AS CXZZCS, A.SLBH AS SLBH, B.GMSFHM AS LQRSFZH, A.KZZD1 AS KZZD1, A.KZZD2 AS KZZD2, A.KZZD3 AS KZZD3, A.KZZD4 AS
5、 KZZD4, A.KZZD5 AS KZZD5, A.KZZD6 AS KZZD6, C.FLAG AS FLAG, C.OPERATION AS OPERATION, C.XZQ AS XZQ FROM KY_T_JZZYW_YX C, T_JZZXX_YX B, T_JZZYW_YX A WHERE A.ID = B.JZZYWID AND A.ID = C.ID /*AND XZQ IS NOT NULL*/ AND FLAG IS NULL) LOOP 根据不同的操作类型, 应用到新市库上 IF I.OPERATION = DE THEN /* DELETE FROM T_JZZ_Y
6、WKYXX WHERE ID = I.ID; DELETE FROM T_JZZ_XXKYXX WHERE ID = I.ID; NULL; ELSIF I.OPERATION = IN /*当t_jzzyw_yx的shsftg不为空且dqywhj为03、04、05、07、08、09、10时, 如果id不存在于t_jzz_yw_sh ,就新增一条记录到t_jzz_yw_sh, 如果存在,则修改*/ IF I.SHR IS NOT NULL AND I.DQYWHJ IN (03, 04 /*, 0507080910*/) THEN INSERT INTO T_JZZ_YW_SHKYXX (ID
7、, SHR, SHDW, SHRQ, SHJG, SHBTGYY, CJR, CJSJ, ZHXGR, ZHXGSJ) SELECT ID, SHR, SHDW, SHRQ, SHJG, SHBTGYY, CJR, CJSJ, GXR, GXSJ FROM T_JZZYW_SH WHERE ID = I.KZZD2; END IF; /*当t_jzzyw_yx的zzdwdm不为空且dqywhj为07、08、09、10时, 如果id不存在于t_jzz_yw_zzxx,就新增一条记录到t_jzz_yw_zzxx, 如果存在,则修改该记录*/ IF I.ZZDWDM IS NOT NULL AND
8、-V_ZZXXID :ZZXX | ID_JZZ.NEXTVAL; INSERT INTO T_JZZ_YW_ZZXXKYXX (ID, ZZDW, ZZRQ, ZZSFCG, ZZCWLXMC, CJR, CJSJ, ZHXGR, ZHXGSJ) SELECT ID, ZZDW, ZZRQ, ZZSFCG, ZZCWLXMC, CJR, CJSJ, ZHXGR, ZHXGSJ FROM T_JZZ_YW_ZZXX WHERE ID = I.KZZD4; /* 当t_jzzyw_yx的FFR不为空且dqywhj为10时, 新增一条记录到t_jzz_yw_ff*/ IF I.FFR IS NOT
9、 NULL AND I.DQYWHJ = -V_FFID :FFID INSERT INTO T_JZZ_YW_FFKYXX (ID, FFR, FFDW, FFSJ, LQRXM, LQRSFZH, CJR, CJSJ, ZHXGR, ZHXGSJ) (I.KZZD3, I.FFR, I.FFDWDM, I.FFRQ, I.LQR, I.LQRSFZH, I.CJR, I.CJSJ, I.GXR, I.GXSJ); INSERT INTO T_JZZ_YWKYXX (ID, RYYWLSH, RYID, YWHJ, DQYWZT, SLLB, SLYY, SLRQ, SLDW, SLR, S
10、FKS, SFDYLQPZ, DYLQPZSJ, SFYJF, SFZXZZ, CXRQ, CZZZCS, SLBH, ZHXGSJ, JZZBH, XM, ZJHM, XB, CSRQ, MZ, DZ, ZZDZXZ, HZBH, JZZYXQX, JZZYXQSRQ, JZZYXJZRQ, BZRQ, JZZXPH, SFSCSB, SHENG, SHI, FWID, XIAN, ZYSCBZRQ, ZALFFJE, GBFJE, SHIJB, XZQ, JZ, FWZ, JWH, PQ, GAFJ, PCS, JDDM, MPDM, CZWBH, FWCSDZ, DWDZ, YDDH,
11、ZZSY, DYHDZ, ZZMM, ZJXY, WHCD, QYBH, XZ, JTGJ, CPHM, LSRQ, HYZK, JHRQ, POXM, POZJLX, POZJHM, SFTZ, JSZLX, JSZHM, YZRQ, JSZYXRQ, BZ, JYZK, HDQK, CZHKSZD, QH, NANGS, NUGS, SFCB, ZY, PODZ, SG, RZRQ, FJH, ZZCS, JZFS, TBR, TBRQ, DWDH, ZZID, SHID, FFID, ZZXXID, JCID, DBID, HJDH, DQYWHJ) SELECT A.ID, A.DJB
12、ID, A.RYID, A.DQYWHJ, NVL(A.DQYWZT, 2) AS DQYWZT, A.SLLB, A.SLYY, A.SLRQ, t6.xzqmc|t6.jzmc|t6.fwzmc|t6.jwhmc|t6.cmxzmc SLDW, A.SLR, A.SFKS, A.SFDYLQPZ, A.DYLQPZRQ, A.SFYJF, A.SFCXZZ, A.CXRQ, A.CXZZCS, A.SLBH, A.CJR, A.CJSJ, A.GXR, A.GXSJ, B.XM, B.GMSFHM, B.XBDM, B.CSRQ, B.MZDM, B.HJDZ, B.JZDZ, NVL(T
13、.HZBH, T.BZ), B.YXQX, B.YXQSRQ, B.YXJZRQ, B.SLRQ, B.XPH, B.SFSCSB, SUBSTR(B.GMSFHM, 0, 2), SUBSTR(B.GMSFHM, 0, 4), B.FWID, B.HJQXDM, B.ZYSCBZRQ, B.ZALFFJE, B.GBFJE, 4406, T4.XZQDM XZQ, T4.JZDM JZ, T4.FWZDM FWZ, T4.JWHDM JWH, T4.CMXZDM PQ, T5.GAFJ, T2.PCS, T2.JLXDM JDDM, T1.MPDM, T1.DABH CZWBH, C.FWC
14、S, C.FWCSDZ, C.YDDH, NVL(C.ZZSY, 99), NVL(D.DYHDZ, E.DYHDZ), COALESCE(D.ZZMM, E.ZZMM,13 NVL(D.ZJXY, E.ZJXY), COALESCE(D.WHCD, E.WHCD,80 NVL(D.DWID, E.DWID), NVL(D.HJXZ, E.HJXZ), NVL(D.JTGJ, ) AS JTGJ, NVL(D.CPHM, E.CPHM), COALESCE(D.LSRQ, E.LSRQ,D.TBSJ,SYSDATE), COALESCE(D.HYZK, E.HYZK,1 NVL(D.JHRQ,
15、 E.JHRQ), NVL(D.POXM, E.POXM), NVL(D.POZJLX, E.POZJLX), NVL(D.POZJHM, E.POZJHM), NVL(D.SFTZ, E.SFTZ), NVL(D.JSZLX, E.JSZLX), NVL(D.JSZHM, E.JSZHM), NVL(D.YZRQ, E.YZRQ), NVL(D.JSZYXRQ, E.JSZYXRQ), NVL(D.BZ, E.BZ), COALESCE(D.JYZK, E.JYZK, NVL(D.HDQK, E.HDQK), NVL(D.CZHKSZD, E.CZHKSZD), NVL(D.HJQH, E.
16、HJQH), NVL(D.NANGS, E.NANGS), NVL(D.NUGS, E.NUGS), NVL(D.SFCB, E.SFCB), NVL(D.ZY, E.ZY), NVL(D.POHJDZ, E.POHJDZ), NVL(D.SG, E.SG), NVL(D.RZRQ, E.RZRQ), NVL(D.FJH, E.FJH), NVL(D.ZZCS, 无 COALESCE(D.JZFS, E.JZFS, NULL, I.KZZD2, I.KZZD3, I.KZZD4, (CASE WHEN A.DQYWHJ IN (0102) THEN WHEN A.DQYWHJ = 0687 E
17、ND) DQYWHJ FROM T_JZZYW_YX A, T_JZZXX_YX B, T_JZZYW_QTXX_YX C, T_LDRK_DJB D, T_LDRK_DJB_ZXQK E, T_LDRK_XP T, T_FWXX T1, T_FWXX_MPXX T2, T_FWXX_JLX T3, V_XZQH_ZZJG T4, T_XZQH_QX T5, V_XZQH_ZZJG t6 AND A.ID = C.JZZYWID AND A.DJBID = D.ID(+) AND A.DJBID = E.ID(+) AND T.ID(+) = B.XPID AND B.FWID = T1.ID
18、 AND T2.JLXDM = T3.DM AND T2.XZQHDM = T4.DM AND T4.XZQDM = T5.DM AND a.sldwdm= t6.dm AND A.ID = I.ID; IF I.DQYWHJ = INSERT INTO T_JZZ_XXKYXX FFR, FFDW, FFSJ, JZZZT, ZXBZ) A.ZZDWDM, A.ZZRQ, A.FFR, A.FFDWDM, A.FFRQ, B.CJR, B.CJSJ, B.GXR, B.GXSJ, B.JZZZT, T4.XZQDM, T4.JZDM, T4.FWZDM, T4.JWHDM, T2.JLXDM, T1.DABH, NULL V_XZQH_ZZJG T6 WHERE A.ID = B.JZZYW
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1