插入测试数据SQL代码块

最新推荐文章于 2024-06-07 19:10:17 发布

原创最新推荐文章于 2024-06-07 19:10:17 发布 · 173 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

DATABASE 专栏收录该内容

8 篇文章

订阅专栏

/******************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;
/