insert /*+append */into mail_batchsendnotify_tmp
   (
     mobilenum,
     ispsid,
     readcount,
     seqno
    )
   select /*+PARALLEL(c,2) */
     mobilenum,
     c.ispsid,
     c.ReadCount,
     seqno
     from odsview.vw_mail_batch_notify c
    where c.modifytime >= trunc(Sysdate)
      and c.modifytime < trunc(Sysdate) + 1

Declare

  Cursor cur_batch_notify_data
  Is
  select /*+PARALLEL(c,2)*/
     c.mobilenum As usernumber,
     c.ispsid,
     c.seqno,
     c.ReadCount
     from odsview.vw_mail_batch_notify c
    where c.modifytime >= trunc(Sysdate)
      and c.modifytime < trunc(Sysdate) + 1
     ;

  TYPE type_table_SEQNO1 IS TABLE OF mail_batchsendnotify_tmp.seqno%type INDEX BY BINARY_INTEGER; --
  table_SEQNO1 type_table_SEQNO1;


  TYPE type_table_usernumber1 IS TABLE OF mail_batchsendnotify_tmp.MOBILENUM%type INDEX BY BINARY_INTEGER; --
  table_usernumber type_table_usernumber1;

  TYPE type_table_ispsid IS TABLE OF oss03.oss_mail_deliver_effective_tp.ispsid%type INDEX BY BINARY_INTEGER; --
  table_ispsid type_table_ispsid;


  TYPE type_table_readcount IS TABLE OF Number INDEX BY BINARY_INTEGER;
  table_readcount type_table_readcount;

  Type type_table_provcode Is Table Of Number INDEX BY BINARY_INTEGER;
  l_type_table_provcode type_table_provcode;

Begin
  OPEN cur_batch_notify_data;
  LOOP
    FETCH cur_batch_notify_data BULK COLLECT
      INTO table_usernumber,
           table_ispsid,
           table_seqno1,
           table_readcount LIMIT 3000;
    FORALL i IN 1 .. table_usernumber.count
      insert /*+append*/into mail_batchsendnotify_tmp
      (
       mobilenum,
       ispsid,
       seqno,
       readcount
       )
       Values
       (
        table_usernumber(i),
        table_ispsid(i),
        table_seqno1(i),
        table_readcount(i)
       );
       commit;
    EXIT WHEN cur_batch_notify_data%NOTFOUND OR cur_batch_notify_data%NOTFOUND IS NULL;
  END LOOP;
  CLOSE cur_batch_notify_data;
  End;

  select /*+PARALLEL(c,2)*/
     c.mobilenum As usernumber,
     c.ispsid,
     c.seqno,
     c.ReadCount
     from odsview.vw_mail_batch_notify c
    where c.modifytime >= trunc(Sysdate)
      and c.modifytime < trunc(Sysdate) + 1
     ;