存储过程

create or replace procedure test_makedata
as
  str_sqlerrm varchar2(256); --error message
  in_msisdn  number;
  v_number number;
  type msisdnType is table of dm.msisdn%type index by binary_integer;
  --type terminalidType is table of dm.terminalid%type index by binary_integer;
  type terminalgroupidType is table of dm.terminalgroupid%type index by binary_integer;

  this_msisdn              msisdnType;
  --this_terminalid          terminalidType;
  this_terminalgroupid     terminalgroupidType;
  this_terminalgroupidTypeTmp   terminalgroupidType;
begin
  in_msisdn := 13300000000;
  this_terminalgroupid(0) := '11012710100700';
  this_terminalgroupid(1) := '11012710101800';
  this_terminalgroupid(2) := '11012710103800';
  this_terminalgroupid(3) := '00000000000000';

  for j in 1 .. 120000 loop

    for i in 0 .. 10000 loop
      in_msisdn := in_msisdn + 1;
      this_msisdn(i) := in_msisdn;
      SELECT to_number(TRUNC(DBMS_RANDOM.VALUE(0, 4))) into v_number FROM dual;
      this_terminalgroupidTypeTmp(i) := this_terminalgroupid(v_number);
    end loop;

    forall k in 0 .. 10000
      insert /*+ append */ into dm
        (
         msisdn,
         terminalid,
         terminalgroupid
        )
      values
        (
         this_msisdn(k),
         2,
         this_terminalgroupidTypeTmp(k)
         --00000000000000
         );
    commit;
  end loop;

exception
  when others then
    str_sqlerrm := substr(sqlerrm, 1, 200);
    dbms_output.put_line(str_sqlerrm);
    rollback;
end test_makedata;