存储过程实例1.doc

CREATE OR REPLACE PROCEDURE TOOLD_T_JZZYW_YX_ALL IS /***************************************** 功能：把新市库中的t_jzzyw_yx\t_jzzxx_yx\t_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 * INTO V_N FROM DUAL@KYXX; 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, 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.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,