存储过程实例1.docx

1、存储过程实例1CREATE OR REPLACE PROCEDURE TOOLD_T_JZZYW_YX_ALL IS /* 功能：把新市库中的t_jzzyw_yxt_jzzxx_yxt_jzzyw_qtxx_yx 增量转换到旧市库的T_JZZ_YW,T_JZZ_XX数据； 时间：2013-02-25 */ V_ERRORS VARCHAR2(200); V_N VARCHAR2(2); C_TABLE_NAME CONSTANT VARCHAR2(32) := T_JZZYW_YX;BEGIN BEGIN /* 判断新市库到旧市库的链路是否联通 如果不通，则退出当次转换 */ SELECT *

2、 INTO V_N FROM DUALKYXX; EXCEPTION WHEN OTHERS THEN V_ERRORS := SQLERRM; INSERT INTO T_ERROR_LOG_TOOLD (TYPE, ERRORMSG, XRSJ, YWID) VALUES (C_TABLE_NAME, V_ERRORS, SYSDATE, 链路不通,退出转换); COMMIT; RETURN; END; /*更新XZQ字段* UPDATE KY_T_JZZYW_YX YX SET XZQ = (SELECT T3.XZQDM XZQ FROM T_JZZXX_YX T, T_FWXX T1

3、, T_FWXX_MPXX T2, V_XZQH_ZZJG T3 WHERE T.FWID = T1.ID AND T1.MPDM = T2.DM AND T2.XZQHDM = T3.DM AND JZZYWID = YX.ID); COMMIT;*/ 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

4、.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 HZFFRQ, 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 Z

5、ZRQ, A.SFCXZZ AS SFCXZZ, A.BLSFFF AS BLSFFF, A.BLFFPH AS BLFFPH, A.BLFFDWDM AS BLFFDWDM, A.BLFFR AS BLFFR, A.BLFFRQ AS BLFFRQ, 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 CX

6、NR, 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, 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 ZZ

7、PH, 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 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

8、A.ID = C.ID /*AND XZQ IS NOT NULL*/ AND FLAG IS NULL) LOOP /* 根据不同的操作类型， 应用到新市库上 */ BEGIN IF I.OPERATION = DE THEN /* DELETE FROM T_JZZ_YWKYXX WHERE ID = I.ID; DELETE FROM T_JZZ_XXKYXX WHERE ID = I.ID;*/ NULL; ELSIF I.OPERATION = IN THEN /*当t_jzzyw_yx的shsftg不为空且dqywhj为03、04、05、07、08、09、10时， 如果id不存在于

9、t_jzz_yw_sh ，就新增一条记录到t_jzz_yw_sh， 如果存在，则修改*/ IF I.SHR IS NOT NULL AND I.DQYWHJ IN (03, 04 /*, 05, 07, 08, 09, 10*/) THEN INSERT INTO T_JZZ_YW_SHKYXX (ID, 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

10、 = 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 I.DQYWHJ IN (07, 08, 09 /*, 10*/) THEN -V_ZZXXID := ZZXX | ID_JZZ.NEXTVAL; INSERT INTO T_JZZ_YW_ZZXXKYXX (ID, ZZDW, ZZRQ, ZZSFCG, ZZCWLXMC, CJR, CJS

11、J, ZHXGR, ZHXGSJ) SELECT ID, ZZDW, ZZRQ, ZZSFCG, ZZCWLXMC, CJR, CJSJ, ZHXGR, ZHXGSJ FROM T_JZZ_YW_ZZXX WHERE ID = I.KZZD4; END IF; /* 当t_jzzyw_yx的FFR不为空且dqywhj为10时， 新增一条记录到t_jzz_yw_ff*/ IF I.FFR IS NOT NULL AND I.DQYWHJ = 10 THEN -V_FFID := FFID | ID_JZZ.NEXTVAL; INSERT INTO T_JZZ_YW_FFKYXX (ID, FFR

12、, FFDW, FFSJ, LQRXM, LQRSFZH, CJR, CJSJ, ZHXGR, ZHXGSJ) VALUES (I.KZZD3, I.FFR, I.FFDWDM, I.FFRQ, I.LQR, I.LQRSFZH, I.CJR, I.CJSJ, I.GXR, I.GXSJ); END IF; INSERT INTO T_JZZ_YWKYXX (ID, RYYWLSH, RYID, YWHJ, DQYWZT, SLLB, SLYY, SLRQ, SLDW, SLR, SFKS, SFDYLQPZ, DYLQPZSJ, SFYJF, SFZXZZ, CXRQ, CZZZCS, SL

13、BH, CJR, CJSJ, ZHXGR, 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, ZZSY, DYHDZ, ZZMM, ZJXY, WHCD, QYBH, X

14、Z, 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.DJBID, A.RYID, A.DQYWHJ, NVL(A.DQYWZT, 2)

15、 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, A.SLBH, B.XM, B.GMSFHM, B.XBDM, B.CSRQ, B.MZDM, B.HJDZ, B.JZDZ, NVL(T.HZBH, T.BZ), B.YXQX, B.YXQSRQ

16、, 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.FWCS, C.FWCSDZ, C.YDDH, NVL(C.ZZS

17、Y, 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, 03) AS JTGJ, NVL(D.CPHM, E.CPHM), COALESCE(D.LSRQ, E.LSRQ,D.TBSJ,SYSDATE), COALESCE(D.HYZK, E.HYZK,1), NVL(D.JHRQ, E.JHRQ), NVL(D.POXM,

18、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,2), NVL(D.HDQK, E.HDQK), NVL(D.CZHKSZD, E.CZHKSZD), NVL(D.HJQH, E.HJQH), NVL(D.NANGS,

19、 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,05), NULL, NULL, NULL, NULL, I.KZZD2, I.KZZD3, I.KZZD4, NULL, NULL, NULL, (CASE WHEN A.DQYWHJ IN (01, 02) TH

20、EN 1 WHEN A.DQYWHJ IN (03, 04, 05, 07, 08, 09) THEN 2 WHEN A.DQYWHJ = 06 THEN 8 WHEN A.DQYWHJ = 10 THEN 7 END) 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

21、t6 WHERE A.ID = B.JZZYWID 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 AND T1.MPDM = T2.DM 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 = 10 THEN INSERT INTO T_JZZ_XXKYXX (

22、ID, RYID, SLRQ, SLDW, SLR, SLBH, JZZBH, ZZDW, ZZRQ, FFR, FFDW, FFSJ, XM, ZJHM, XB, CSRQ, MZ, DZ, ZZDZXZ, HZBH, CJR, CJSJ, ZHXGR, ZHXGSJ, JZZYXQX, JZZYXQSRQ, JZZYXJZRQ, BZRQ, JZZXPH, SFSCSB, SHENG, SHI, JZZZT, FWID, XIAN, ZYSCBZRQ, SHIJB, XZQ, JZ, FWZ, JWH, PQ, GAFJ, PCS, JDDM, MPDM, CZWBH, FWCSDZ, D

23、WDZ, YDDH, 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, HJDH, ZXBZ) SELECT A.ID, A.RYID, A.SLRQ, t6.xzqmc|t6.jzmc|t

24、6.fwzmc|t6.jwhmc|t6.cmxzmc SLDW, A.SLR, A.SLBH, A.SLBH, A.ZZDWDM, A.ZZRQ, A.FFR, A.FFDWDM, A.FFRQ, B.XM, B.GMSFHM, B.XBDM, B.CSRQ, B.MZDM, B.HJDZ, B.JZDZ, NVL(T.HZBH, T.BZ), B.CJR, B.CJSJ, B.GXR, B.GXSJ, B.YXQX, B.YXQSRQ, B.YXJZRQ, B.SLRQ, B.XPH, B.SFSCSB, SUBSTR(B.GMSFHM, 0, 2), SUBSTR(B.GMSFHM, 0,

25、 4), B.JZZZT, B.FWID, B.HJQXDM, B.ZYSCBZRQ, 4406, T4.XZQDM, T4.JZDM, T4.FWZDM, T4.JWHDM, T4.CMXZDM PQ, T5.GAFJ, T2.PCS, T2.JLXDM, T1.MPDM, T1.DABH, C.FWCS, 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.D

26、WID, E.DWID), NVL(D.HJXZ, E.HJXZ), NVL(D.JTGJ, 03) AS JTGJ, NVL(D.CPHM, E.CPHM), COALESCE(D.LSRQ, E.LSRQ,D.TBSJ,SYSDATE), COALESCE(D.HYZK, E.HYZK,1), NVL(D.JHRQ, 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

27、.JSZHM), NVL(D.YZRQ, E.YZRQ), NVL(D.JSZYXRQ, E.JSZYXRQ), NVL(D.BZ, E.BZ), COALESCE(D.JYZK, E.JYZK,2), NVL(D.HDQK, E.HDQK), NVL(D.CZHKSZD, E.CZHKSZD), NVL(D.HJQH, E.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

28、.RZRQ, E.RZRQ), NVL(D.FJH, E.FJH), NVL(D.ZZCS, 无), COALESCE(D.JZFS, E.JZFS,05), B.XM, B.SLRQ, NULL, NULL, NULL 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 WHERE A.ID = B.JZZYW