同样来自asktom的脚本,可以对一个表填充随机数据 create or replace procedure gen_data( p_tname in varchar2, p_records in n
同样来自asktom的脚本,可以对一个表填充随机数据
create or replace
 procedure gen_data( p_tname in varchar2, p_records in number )
 -- this routine is designed to be installed once pre database, hence
 -- the current_user authorization.
 authid current_user
 as
    l_insert long;
    l_rows  number default 0;
 begin
    -- dbms_random can be very cpu intensive. i use dbms_application_info
    -- to instrument this routine, so i can monitor how far along it is
    -- from another session. every bulk insert will update v$session for us.
    dbms_application_info.set_client_info( 'gen_data ' || p_tname );
-- the beginning of our insert into statement. using a direct path
    -- insert, if you alter your table to be nologging in an archive
    -- log mode database, it'll generate no redo (assuming the table
    -- is not indexed).
    l_insert := 'insert /*+ append */ into ' || p_tname ||
                ' select ';
-- now, we build the rest of our insert. we select the datatype
    -- and size of each column. maxval is used for numbers only. using
    -- the precision defined for the column, we determine the maximum number
    -- that we can stuff in there.
    for x in
    ( select data_type, data_length,
    nvl(rpad( '9',data_precision,'9')/power(10,data_scale),9999999999) maxval
        from user_tab_columns
        where table_name = upper(p_tname)
        order by column_id )
    loop
        -- if number, generate a number in the range 1 .. maxval.
        if ( x.data_type in ('number', 'float' ))
        then
            l_insert := l_insert ||
                        'dbms_random.value(1,' || x.maxval || '),';
-- if a date/timestamp type, add some random number to sysdate.
        elsif ( x.data_type = 'date' or x.data_type like 'timestamp%' )
        then
            l_insert := l_insert ||
                  'sysdate+dbms_random.value(1,1000),';
-- if a string, generate a random string between 1 and data length.
        -- bytes in length
        else
            l_insert := l_insert || 'dbms_random.string(''a'',
                    trunc(dbms_random.value(1,' || x.data_length || '))),';
        end if;
    end loop;
    l_insert := rtrim(l_insert,',') ||
                  ' from all_objects where rownum
-- now, wo just execute the insert into as many times as needed
    -- in order to put l_rows rows in the table. since we are direct path
    -- loading, we must commit after each insert. in this case, since
    -- we are generating test data, it is ok from a transactional perspective.
    -- and since this operation should generate little redo in all cases,
    -- it will not affect our performance as well.
    loop
        execute immediate l_insert using p_records - l_rows;
        l_rows := l_rows + sql%rowcount;
        commit;
        dbms_application_info.set_module
        ( l_rows || ' rows of ' || p_records, '' );
        exit when ( l_rows >= p_records );
    end loop;
 end;
 /
以hr的depertment表为例,
sql> create table dept as select * from departments where 1=0;
 table created.
但是需要注意的是 字段的取值范围不能小于1
以hr的employees表的commission_pct字段为例,
执行到过程的第36行
最大值应该是0.99
但是实际执行的结果却是 超过了最大值,导致溢出。
解决这个问题,可以将下限设置为0
本文永久更新链接地址：
，