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
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29254281/viewspace-1609379/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL用隨機資料填充表MySql隨機
- MySQL 用隨機資料填充外來鍵表MySql隨機
- oracle插入隨機資料Oracle隨機
- 表中隨機取資料隨機
- 從oracle表中隨機取記錄,產生隨機數和隨機字串Oracle隨機字串
- 如何隨機生成一個大表的資料隨機
- (轉)Oracle的隨機數、隨機日期和時間、隨機字串及造資料匿名過程Oracle隨機字串
- Faker資料填充
- Laravel - 資料填充Laravel
- oracle表機構和資料複製Oracle
- 應用:資料預處理-缺失值填充
- MySQL 隨機查詢資料與隨機更新資料實現程式碼MySql隨機
- 如何實現MySQL隨機查詢資料與MySQL隨機更新資料?MySql隨機
- 使用SQL語句從資料庫一個表中隨機獲取資料SQL資料庫隨機
- ORACLE 模擬資料之 從指定表中的指定欄位時隨機出一個值Oracle隨機
- 配置化資料填充框架框架
- excel模板資料填充 :tablefillExcel
- [Z]Oracle 的隨機數、隨機日期和時間、隨機字串Oracle隨機字串
- python 隨機生成字串資料Python隨機字串
- Oracle資料表物件Oracle物件
- 隨機取表記錄隨機
- Oracle 隨機函式Oracle隨機函式
- oracle隨機取樣Oracle隨機
- 用Python寫一個向資料庫填充資料的小工具Python資料庫
- faker 資料填充常用指令收集
- Laravel 優雅填充 SQL 資料LaravelSQL
- Laravel模型工廠填充資料Laravel模型
- 隨機獲取oracle資料庫中的任意一行資料(rownum)示例介紹隨機Oracle資料庫
- SAP CRM訂單資料庫表CRMD_SHIPPING的填充原理資料庫
- Laravel 中資料遷移和資料填充Laravel
- 集合中隨機取出幾個資料隨機
- Oracle資料表碎片整理Oracle
- Oracle常用資料字典表Oracle
- (轉)Oracle隨機函式Oracle隨機函式
- ORACLE裡的隨機數Oracle隨機
- 資料模型設計(表結構)之隨記模型
- 假資料填充錯誤記錄
- oracle資料庫兩表資料比較Oracle資料庫