oracle 存储过程 批量,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;