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;