ORACLE大数据量生成语句

             为了更好的测试我们的系统在一定数据量下的运行情况，时常需要准备大量的测试数据。如果有灵活的方法可以使用，那就可以事半功倍了。

     以下介绍如何在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(TRU NC(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;