插入测试数据SQL代码块

 

 

/******************T_MESSAGE_INFO**********************/
declare
i NUMBER;
v_s varchar2(50);
v_phone_number VARCHAR2(21);
v_date DATE;
v_rows NUMBER;
START_DATE DATE;
Used_time VARCHAR2(50);
begin
    execute immediate 'truncate table T_MESSAGE_INFO';
   
    select sysdate INTO START_DATE from dual;
   
    v_rows := 500000;
   
    for i in 1..v_rows loop
         v_s := TRIM (TO_CHAR (MOD (i, 24), '00'))
         || ':'|| TRIM (TO_CHAR (MOD (i, 60), '00'))
         || TRIM (TO_CHAR (MOD (i, 60), '00'));
         v_date := to_date('2011-01-04'||' '||v_s,'yyyy-mm-dd hh24:mi:ss');
         v_phone_number := '8613258888' || lpad(round(DBMS_RANDOM.VALUE (1, 1000)),3,'0');
                
    INSERT INTO T_MESSAGE_INFO
    (FLOW_ID,ACCOUNT_ID,PHONE_NUMBER,CALLINGNUMBER,RECIPIENT,MSG_IDENTIFIER,SEND_DATE,MSG_CONTENT,MSG_PATH,MSG_STATE,CURRENT_DIRECTORY,DEFAULT_DIRECTORY,READ_FLAG,HANDLER_RESULT,ORAID,SUBJECTSIZE,MSG_SIZE,IMPORTANTFLAG,DESC2,DESC1)
    VALUES
    (seq_msg_wh_flow_id.nextval,lpad(round(DBMS_RANDOM.VALUE (1, 10000000000000000000)),20,'0'),v_phone_number,v_phone_number,v_phone_number,1,v_date,lpad(round(DBMS_RANDOM.VALUE (1, 10000000000000000000)),1400,'0'),lpad(round(DBMS_RANDOM.VALUE (1, 10000000000000000000)),512,'0'),1,lpad(round(DBMS_RANDOM.VALUE (1, 10000000000000000000)),20,'9'),lpad(round(DBMS_RANDOM.VALUE (1, 10000000000000000000)),20,'9'),1,1,lpad(round(DBMS_RANDOM.VALUE (1, 10000000000000000000)),128,'9'),lpad(round(DBMS_RANDOM.VALUE (1, 1000000000)),10,'9'),lpad(round(DBMS_RANDOM.VALUE (1, 1000000000)),10,'9'),lpad(round(DBMS_RANDOM.VALUE (1, 10000)),5,'9'),
    'SFB03XTPK6QJAJ GT7J4RF1 BCC352N77L8FP VJPS5NQYUNX6GBVR6UTNDBKONVACUJXH1SE2GA8A1YOE7D2MCXL5WR3NP03R3TGHECE3R7UKKJR0IJ DO2HFCE5VD9','SFB03XTPK6QJAJ GT7J4RF1 BCC352N77L8FP VJPS5NQYUNX6GBVR6UTNDBKONVACUJXH1SE2GA8A1YOE7D2MCXL5WR3NP03R3TGHECE3R7UKKJR0IJ DO2HFCE5VD9');
   
    if mod(i,50000) = 0 then
        commit;
    end if;
    end loop;
    COMMIT;
    -- calculate time
    select ceil((to_date(TO_CHAR(sysdate , 'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss') - to_date(TO_CHAR(START_DATE , 'yyyy-mm-dd hh24-mi-ss'),'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60* 1000) INTO Used_time FROM DUAL;
    dbms_output.put_line('USED_TIME: '||Used_time );
end;
/