oracle批量提交

CREATE OR REPLACE PROCEDURE P_CUST_BOSSORDER_SYNC IS
  --存放t_iiss_boss_order_back是否存在
  v_tableCount number(2) := 0;
  --定义变量
  TYPE v_bossOrder_Row IS TABLE OF t_cust_bossOrder@TOMBUDB%ROWTYPE;
  v_bossOrder_Row_cust v_bossOrder_Row;

  --查询新平台的数据
  CURSOR cust_order_res IS
    SELECT * FROM t_cust_bossOrder@TOMBUDB;

   --用户更新用户信息的查询
  CURSOR t_iiss_member_res IS
    SELECT bossorder.mobnum as mobnum,
           to_char(wm_concat(bossorder.memberdealid)) as memberdealid
      FROM t_iiss_boss_order bossorder, t_iiss_member members
     WHERE bossorder.mobnum = members.phoneno
     group by bossorder.mobnum;
  
  --定义用户信息表的menberID
  type MEMBERDEALID is table of t_iiss_member.memberdealid%type;
  v_memberdealid MEMBERDEALID;
  --定义用户信息表的电话号码
  type mobnum is table of t_iiss_member.phoneno%type;
  v_mobnum mobnum;

BEGIN
  --备份前查看是否已经存在表名 存在就备份数据 不存在创建并备份数据(全量删除老商盟数据)
  select count(*)
    into v_tableCount
    from user_tables
   where table_name = upper('t_iiss_boss_order_back');
  if (v_tableCount > 0) then
    --删除备份表中的数据信息
    EXECUTE IMMEDIATE 'TRUNCATE TABLE t_iiss_boss_order_back  ';
    EXECUTE IMMEDIATE 'insert into t_iiss_boss_order_back 
      select * from t_iiss_boss_order ';
    commit;
  else
    EXECUTE IMMEDIATE 'create table  t_iiss_boss_order_back   as select * from  t_iiss_boss_order';
  end if;
  --删除t_iiss_boss_order表数据
  EXECUTE IMMEDIATE 'TRUNCATE TABLE t_iiss_boss_order';
  --循环读取新商盟数据全量同步到老商盟表中
  OPEN cust_order_res;
  LOOP
    FETCH cust_order_res BULK COLLECT
      INTO v_bossOrder_Row_cust LIMIT 1000; --每1000次commit一次
    FORALL i IN 1 .. v_bossOrder_Row_cust.COUNT
    --插入数据
      INSERT INTO t_iiss_boss_order
        (Mobnum, Memberdealid, Countycode, Syncdate, Srcproductid)
      VALUES
        (v_bossOrder_Row_cust(i).Mobnum,
         v_bossOrder_Row_cust(i).Memberdealid,
         v_bossOrder_Row_cust(i).Countycode,
         v_bossOrder_Row_cust(i).Syncdate,
         v_bossOrder_Row_cust(i).Srcproductid);
    COMMIT;
    EXIT WHEN cust_order_res%NOTFOUND;
  end loop;
  CLOSE cust_order_res;

  --修改用户信息
  OPEN t_iiss_member_res;
  LOOP
    FETCH t_iiss_member_res BULK COLLECT
      INTO v_mobnum, v_memberdealid LIMIT 1000; --每1000次commit一次
    FORALL i IN 1 .. v_memberdealid.COUNT
   
    --修改用户协议
      UPDATE t_iiss_member
         SET MEMBERDEALID = v_memberdealid(i), chargeyesorno = '1'
       WHERE phoneno = v_mobnum(i);
 
    COMMIT;
    EXIT WHEN t_iiss_member_res%NOTFOUND;
  end loop;
  CLOSE t_iiss_member_res;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    p_IISS_LOG_WriteErrMsg(NULL, 'P_CUST_BOSSORDER_SYNC', NULL);
END P_CUST_BOSSORDER_SYNC;
/