同样来自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 <= :n';
? ? -- 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
