標籤：
做資料庫開發或管理的人經常要建立大量的測試資料，動不動就需要上萬條，如果一條一條的錄入，那會浪費大量的時間，本文介紹了Oracle中如何通過一條SQL快速產生大量的測試資料的方法。
首先類比一下100條的隨機資料
[sql] view plaincopyprint?
- select rownum as id,
- to_char(sysdate + rownum / 24 / 3600, ‘yyyy-mm-dd hh24:mi:ss‘) as inc_datetime,
- trunc(dbms_random.value(0, 100)) as random_id,
- dbms_random.string(‘$‘, 20) random_string
- from dual
- connect by level <= 100;
以上代碼中並沒有插入資料庫中，若要插入只需要對sql上增加create table 表 as 或 insert into select方式
上面SQL是利用了Oracle資料庫文法的幾個實用小技巧實現的：
1、利用Oracle特有的“connect by”樹形串連文法產生測試記錄，“level <= 10”表示要產生10記錄；
2、利用rownum虛擬列產生遞增的整數資料；
3、利用sysdate函數加一些簡單運算來產生日期資料，本例中是每條記錄的時間加1秒；
4、利用dbms_random.value函數產生隨機的數值型資料，本例中是產生0到100之間的隨機整數；
5、利用dbms_random.string函數產生隨機的字元型資料，本例中是產生長度為20的隨機字串，字串中可以包括字元或數字。
dbms_random.函數中的參數介紹
[sql] view plaincopyprint?
- -- **************************************** 隨機數字 ***************************************************
- select decode(trunc(dbms_random.value(0, 2)),‘0‘,‘女‘,‘1‘,‘男‘) from dual
- select length(dbms_random.random) from dual; -- 產生一個任意長度為9，10，11位的數字
- select abs(mod(dbms_random.random,100)) from dual; -- 產生一個100以內的隨機數
- select trunc(10+90*dbms_random.value) from dual; -- 產生10~100之間的任意2位隨機整數（包含10,不包含100）
- select dbms_random.value from dual; -- 產生一個大於0小於1的隨機數小數(小數點後37或38位長度)
- select dbms_random.value(10,20) from dual; -- 產生一個大於10小於20的隨機數小數(小數點後38位)
- select dbms_random.normal from dual; -- normal函數返回服從常態分佈的一組數。此常態分佈標準差為1，期望值為0。這個函數返回的數值中有68%是介於-1與+1之間，95%介於-2與+2之間，99%介於-3與+3之間。
-
-
- -- **************************************** 隨機日期 ***************************************************
- select to_date(trunc(dbms_random.value(to_number(to_char(sysdate-10, ‘J‘)), to_number(to_char(sysdate, ‘J‘)))), ‘J‘) from dual; -- 在過去10天內取隨機日期
- select (sysdate - 1 / 24 / 60 * 30) + dbms_random.value (1, 1800) / 3600 / 24 from dual; -- 在過去30分鐘內取隨機時間
- select to_date(to_char(to_date(‘2015-01-01‘, ‘yyyy-MM-dd‘), ‘J‘) + trunc(DBMS_RANDOM.VALUE(0, 365)), ‘J‘) from dual; -- 查詢2015年中的任意日期
- -- 在指定日期範圍內取隨機時間
- select to_date(trunc(dbms_random.value(to_number(to_char(to_date(‘20150501‘, ‘yyyymmdd‘),‘J‘)),
- to_number(to_char(to_date(‘20150531‘, ‘yyyymmdd‘) + 1, ‘J‘)))
- ), ‘J‘) + dbms_random.value(1, 3600) / 3600 prize_time
- from dual;
-
- -- 從現在開始到過去的100年之間的任意時刻
- select to_date(trunc(dbms_random.value(
- to_number(to_char(add_months(sysdate,-100 * 12),‘J‘)),
- to_number(to_char(sysdate + 1, ‘J‘)))
- ), ‘J‘) + dbms_random.value(1, 3600) / 3600 from dual;
-
-
- -- **************************************** 隨機字元(產生任意長度為20的字串) ***************************************************
- select dbms_random.string(‘u‘, 20) from dual -- ‘u‘, ‘U‘ 返回全是大寫的字串（uppercase） 測算髮現,只要參數不是一個中文或全形字元，返回資料均是大寫
- select dbms_random.string(‘l‘, 20) from dual -- ‘l‘, ‘L‘ - 返回全是小寫字串（lowercase）
- select dbms_random.string(‘a‘, 20) from dual -- ‘a‘, ‘A‘ - 返回大小寫結合的字串（mixed case）
- select dbms_random.string(‘x‘, 20) from dual -- ‘x‘, ‘X‘ - 返回全是大寫和數位字串（uppercase,alpha&numeric）
- select dbms_random.string(‘p‘, 20) from dual -- ‘p‘, ‘P‘ - 返回鍵盤上出現字元的隨機組合（any printable char）
- 那要產生10萬條測試記錄表可以用如下SQL：create table myTestTable as
select rownum as id,
to_char(sysdate + rownum/24/3600, ‘yyyy-mm-dd hh24:mi:ss‘) as inc_datetime,
trunc(dbms_random.value(0, 100)) as random_id,
dbms_random.string(‘x‘, 20) random_string
from dual
connect by level <= 100000;
DBMS_RANDOM.STRING用法：
1）選項‘u‘, ‘U‘ - returning string in uppercase alpha characters
2）選項‘l‘, ‘L‘ - returning string in lowercase alpha characters
3）選項‘a‘, ‘A‘ - returning string in mixed case alpha characters
4）選項‘x‘, ‘X‘ - returning string in uppercase alpha-numeric characters
5）選項‘p‘, ‘P‘ - returning string in any printable characters.
6）選項 Otherwise the returning string is in uppercase alpha characters.
例子：
產生由大寫字母和數字組成的8位密碼
[email protected]>select dbms_random.string(‘x‘,8) "x_8_password" from dual;
x_8_password
----------------------------------------------
TT3ISGUV
[轉]Oracle dbms_random函數用法快速產生多條測試資料
