oracle存储过程批量插入和批量删除实例

create or replace type SMSPHONE is table of varchar2(20);

 

create or replace procedure PRO_SMS_MASS_SEND(v_targetNumber in smsphone,
                                              v_beginTime    in date,
                                              v_endTime      in date,
                                              v_hour         in varchar2,
                                              o_msg          out integer)
 is
  type v_smsId_type is table of number index by binary_integer;
  v_smsid    v_smsId_type;
  v_smsidtmp v_smsId_type;
  v_sql      varchar2(1000);
  v_cnt      number;
begin
  v_sql := 'insert into sms_mass_send (sms_id, target_number,begin_time, end_time, hour)
            values(:smsId, :targetNumber,:beginTime, :endTime, :hour)';

  v_cnt := 0;
  for i in 1 .. v_targetNumber.count LOOP
    select seq_smsid.nextval into v_smsId(i) from dual;
    if v_targetNumber(i) is null then
      v_cnt := v_cnt + 1;
      v_smsidtmp(v_cnt) := v_smsId(i);
    end if;
  end loop;

  --使用批量插入
  forall i in 1 .. v_targetNumber.count
  execute immediate v_sql
  using v_smsId(i), v_targetNumber(i), v_beginTime, v_endTime, v_hour;

  if v_smsidtmp.count > 0 then
    v_sql := 'delete sms_mass_send where sms_id = :1';
   
    --使用批量删除
    forall i in 1 .. v_smsidtmp.count
    execute immediate v_sql using v_smsidtmp(i);
  end if;

  commit;
  o_msg := 1;
exception
  when others then
    rollback;
    o_msg := 0;
    dbms_output.put_line(sqlerrm);
end;

 

 

测试过程------------------------------------------------------------------------------------

declare
  v_targetnumber smsphone := smsphone();
  v_date1        number;
  v_date2        number;
  v_time         number;
  o_msg          number;
begin

  for i in 1 .. 50000 loop
    v_targetnumber.extend;
    v_targetnumber(v_targetnumber.count) := '1891999' || i;
  end loop;
 
  execute immediate 'truncate table sms_mass_send';

  select to_number(to_char(systimestamp, 'yyyymmddHH24missff'))
    into v_date1
    from dual;
  pro_sms_mass_send(v_targetnumber => v_targetnumber,
                    v_begintime    => sysdate,
                    v_endtime      => sysdate,
                    v_hour         => 1,
                    o_msg          => o_msg);
  select to_number(to_char(systimestamp, 'yyyymmddHH24missff'))
    into v_date2
    from dual;
  v_time := v_date2 - v_date1;
  dbms_output.put_line('使用批量绑定插入时间：' || v_time/1000/1000 || '秒');
end;