涉及兩張表T_QY_JBXX, T_QY_JBXX_IMPORT,一個(gè)儲(chǔ)存過程sp_update_qy_jbxx
t_qy_jbxx 是查詢用的那張表
t_qy_jbxx_import 是專門用于導(dǎo)入的
1.首先將數(shù)據(jù)全部導(dǎo)入到t_qy_jbxx_import(導(dǎo)入之前將該表中的數(shù)據(jù)全部刪除)
2.調(diào)用儲(chǔ)存過程比對室梅，對于t_qy_jbxx_import中的數(shù)據(jù)如果t_qy_jbxx中沒有則新增，如果有則更新

create or replace procedure sp_update_qy_jbxx IS
  COUNT_ONE NUMBER;
  CURSOR MINUS_QY_JBXX IS
    SELECT ZCH,
           QYMC,
           FDDBR,
           HZRQ,
           DJJG,
           JYCS,
           ZJHM,
           QYLX,
           SSHY,
           ZCZB,
           ZCBZ,
           JYFWJFS,
           XKJYXM,
           YBJYXM,
           SLRQ,
           YZBM,
           LXDH,
           YWBZ
    FROM T_QY_JBXX_IMPORT

    MINUS

    SELECT ZCH,
           QYMC,
           FDDBR,
           HZRQ,
           DJJG,
           JYCS,
           ZJHM,
           QYLX,
           SSHY,
           ZCZB,
           ZCBZ,
           JYFWJFS,
           XKJYXM,
           YBJYXM,
           SLRQ,
           YZBM,
           LXDH,
           YWBZ
    FROM T_QY_JBXX;
begin
  FOR CUR_JBXX IN MINUS_QY_JBXX LOOP
    BEGIN
      SELECT COUNT(1)
        INTO COUNT_ONE
        FROM T_QY_JBXX T
      WHERE T.zch = CUR_JBXX.zch;
      --無則新增
      IF COUNT_ONE=0 THEN
        INSERT INTO T_QY_JBXX
          (ZCH,
           QYMC,
           FDDBR,
           HZRQ,
           DJJG,
           JYCS,
           ZJHM,
           QYLX,
           SSHY,
           ZCZB,
           ZCBZ,
           JYFWJFS,
           XKJYXM,
           YBJYXM,
           SLRQ,
           YZBM,
           LXDH,
           YWBZ
           )
         VALUES
           (CUR_JBXX.ZCH,
            CUR_JBXX.QYMC,
            CUR_JBXX.FDDBR,
            CUR_JBXX.HZRQ,
            CUR_JBXX.DJJG,
            CUR_JBXX.JYCS,
            CUR_JBXX.ZJHM,
            CUR_JBXX.QYLX,
            CUR_JBXX.SSHY,
            CUR_JBXX.ZCZB,
            CUR_JBXX.ZCBZ,
            CUR_JBXX.JYFWJFS,
            CUR_JBXX.XKJYXM,
            CUR_JBXX.YBJYXM,
            CUR_JBXX.SLRQ,
            CUR_JBXX.YZBM,
            CUR_JBXX.LXDH,
            CUR_JBXX.YWBZ
            );
      END IF;
      --有則修改
      IF COUNT_ONE = 1 THEN
        UPDATE T_QY_JBXX
        SET ZCH       =   CUR_JBXX.ZCH,
            QYMC    =   CUR_JBXX.QYMC,
            FDDBR   =   CUR_JBXX.FDDBR,
            HZRQ    =   CUR_JBXX.HZRQ,
            DJJG    =   CUR_JBXX.DJJG,
            JYCS    =   CUR_JBXX.JYCS,
            ZJHM    =   CUR_JBXX.ZJHM,
            QYLX    =   CUR_JBXX.QYLX,
            SSHY    =   CUR_JBXX.SSHY,
            ZCZB    =   CUR_JBXX.ZCZB,
            ZCBZ    =   CUR_JBXX.ZCBZ,
            JYFWJFS =   CUR_JBXX.JYFWJFS,
            XKJYXM  =   CUR_JBXX.XKJYXM,
            YBJYXM  =   CUR_JBXX.YBJYXM,
            SLRQ    =   CUR_JBXX.SLRQ,
            YZBM    =   CUR_JBXX.YZBM,
            LXDH    =   CUR_JBXX.LXDH,
            YWBZ    =   CUR_JBXX.YWBZ
        WHERE zch = CUR_JBXX.zch;
      END IF;
    END;
  END LOOP;
  COMMIT;
end sp_update_qy_jbxx;