Oracle用随机数据填充表

同样来自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更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址