為了更好的測試我們的系統在一定數據量下的運行情況，時常需要準備大量的測試數據。如果有靈活的方法可以使用，那就可以事半功倍了。
以下介紹如何在ORACLE數據庫中進行大數據量的構造方法，數據量大小均為500萬。
'''基本主鍵列'''
SELECT LEVEL AS ID
FROM DUAL
CONNECT BY LEVEL <= 5000000
'''基本的單據列'''
SELECT 'SO20121123' || LPAD(LEVEL, 5, 0) AS ORDER_NO
FROM DUAL
CONNECT BY LEVEL <= 5000000;
'''基本的日期列'''
SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(SYSDATE, 'J')),
TO_NUMBER(TO_CHAR(SYSDATE, 'J')))),
'J') + DBMS_RANDOM.VALUE(1, 360000) / 3600 AS ENTRYDATE
FROM DUAL
CONNECT BY LEVEL < 5000000;
'''特定時間范圍內'''
SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(SYSDATE - 10, 'J')),
TO_NUMBER(TO_CHAR(SYSDATE, 'J')))),
'J') + DBMS_RANDOM.VALUE(1, 360000) / 3600 / 24 AS ENTRYDATE
FROM DUAL
CONNECT BY LEVEL < 5000000;
'''隨機數據'''
SELECT DBMS_RANDOM.VALUE FROM DUAL CONNECT BY LEVEL < 5000000;
'''隨機數據范圍'''
SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100))
FROM DUAL
CONNECT BY LEVEL < 5000000;
'''隨機字符串'''
SELECT DBMS_RANDOM.STRING('A', 8) FROM DUAL CONNECT BY LEVEL < 5000000;
'''復雜組合（訂單）'''
SELECT 'P-' ||
TO_CHAR((TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(SYSDATE - 30,
'J')),
TO_NUMBER(TO_CHAR(SYSDATE,
'J')))),
'J') + DBMS_RANDOM.VALUE(1, 360000) / 3600 / 24),
'YYMMDD') || '-' ||
LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 100)), 5, '0') AS 訂單號,
'一般采購' AS 訂單類型,
'SO20121123' || LPAD(LEVEL, 4, 0) AS 外部訂單號,
'admin' AS 變更人,
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(SYSDATE, 'J')),
TO_NUMBER(TO_CHAR(SYSDATE, 'J')))),
'J') + DBMS_RANDOM.VALUE(1, 360000) / 3600 AS 更新時間
FROM DUAL
CONNECT BY LEVEL < 5000000;
'''加入隨機用戶或者字符'''
SELECT 'P-' ||
TO_CHAR((TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(SYSDATE - 30,
'J')),
TO_NUMBER(TO_CHAR(SYSDATE,
'J')))),
'J') + DBMS_RANDOM.VALUE(1, 360000) / 3600 / 24),
'YYMMDD') || '-' ||
LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 100)), 5, '0') AS 訂單號,
'一般采購' AS 訂單類型,
CASE TRUNC(DBMS_RANDOM.VALUE(0, 2))
WHEN 0 THEN
('SO20121123' || LPAD(LEVEL, 4, 0))
ELSE
('')
END AS 外部訂單號,
CASE TRUNC(DBMS_RANDOM.VALUE(0, 3))
WHEN 0 THEN
'admin'
WHEN 1 THEN
'steven'
ELSE
'joseph'
END AS 變更人,
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER(TO_CHAR(SYSDATE, 'J')),
TO_NUMBER(TO_CHAR(SYSDATE, 'J')))),
'J') + DBMS_RANDOM.VALUE(1, 360000) / 3600 AS 更新時間
FROM DUAL
CONNECT BY LEVEL < 5000000;