浅谈构造数据

用数据库, 总是要关心数据, 数据的格式, 数据量的多少等等.
在这里谈谈构造测试数据, 构造部分符合逻辑要求的数据, 可以用于功能测试, 构造期望数量级的数据, 可以用于压力测试.


在这里先介绍数据构造中的几个简单的方法.
1. 随机数的生成. dbms_random.value & dbms_random.value(low, high)
   dbms_random.value 生成的数字是在0-1之间的数字,
   dbms_random.value(low, high) 生成的数字是在low-high之间的数字,
   通过 round/ceil/trunc 等函数去确定所得数据的精度, 也可以直接在插入数据时由字段定义的精度来确定数据的精度.
   示例:


--------------------------------------------------------------------------------
   SQL> select dbms_random.value as val1,
     2         dbms_random.value(1, 10) as val2,
     3         round(dbms_random.value(1, 10), 2) as val3
     4    from dual
     5  /

         VAL1       VAL2       VAL3
   ---------- ---------- ----------
   0.80612009 9.17445487       1.95

--------------------------------------------------------------------------------

2. 随机字符串的生成. dbms_random.string(opt, len)
   opt 的取值如下:
   'u' or 'U': Upper case alpha characters 大写字母
   'l' or 'L': Lower case alpha characters 小写字母
   'a' or 'A': Upper and lower case alpha characters 下小写字母
   'x' or 'X': Upper alpha and numeric characters 大写的字母和数字
   'p' or 'P': Any printable character 任意可见字符
   示例:

--------------------------------------------------------------------------------
   SQL> select dbms_random.string('u', as u,
     2         dbms_random.string('l', as l,
     3         dbms_random.string('a', as a,
     4         dbms_random.string('x', as x,
     5         dbms_random.string('p', as p
     6    from dual;

   U          L          A          X          P
   ---------- ---------- ---------- ---------- ----------
   KNTSNCAW   mymfnoyu   zXyGIFcw   DU7JG17K   -!1yYxke

--------------------------------------------------------------------------------

3. 指定数据记录数的生成.
   生成期望的记录数量, 写几个便捷常用的方法.
   a) 用 connect by 来构造数据记录数

--------------------------------------------------------------------------------
   select * from dual connect by rownum<=n;

--------------------------------------------------------------------------------
   用这样的语句就可以生成n条记录, 适用于 9.2 或更高版本.

   b) 用字典表生来生成n条记录, n小于等于字典表的记录数, 如使用dba_objects(有访问字典表权限先)
   该方法适用于更多版本, 缺点是注意字典表的记录数是否足够.

--------------------------------------------------------------------------------
   select rownum from dba_objects where rownum<=n;

--------------------------------------------------------------------------------

   c) 运用表关联的集合运算生成n条记录, n小于等于笛卡儿乘积数. 这里的表可以使用字典表, 也可以使用用户下自建的表.
   该方法适用于更多版本, 不过使用前要保证表关联后的记录数要够用.

--------------------------------------------------------------------------------
   自建表tb_times,
   create table tb_times (c char);
   -- 放10条记录在里面, 为了方便计算记录数
   begin
     for i in 0..9 loop
       insert into tb_times values(i);
     end loop;
     commit;
   end;

   如要通过 tb_times 来构造700条数据, 可以这样做
   SQL> select count(*)
     2    from tb_times t1, -- 10
     3         tb_times t2, -- 10*10=100
     4         tb_times t3  -- 10*10*10=1000>700
     5   where rownum<=700
     6  /

     COUNT(*)
   ----------
          700
   或
   SQL> select count(*)
     2    from tb_times t1, -- 10
     3         tb_times t2, -- 10*10=100
     4         tb_times t3  -- [10]*10*10=1000>700
     5   where t3.c<7 -- t3表使用部分记录
     6  /

     COUNT(*)
   ----------
          700

--------------------------------------------------------------------------------

好了, 有了以上三个部分, 灵活的使用它们, 我们就可以构造出很多数据库所需的数据.
数据类型可以满足 char/varchar2/number/date/timestamp等常用的类型.

下面用一个示例来构造数据
  col1 : 定值字段, 取值只有 'a' 一种
  col2 : 枚举值字段, 取值范围为 b/c/d, 用decode转义实现
  col3 : 随机的数字, 数值的取值范围在 10 ~ 100 之间, 精度为小数1位
         示例中用round实现精度要求, 其他方法也可以,
         如用 cast(dbms_random.value(10, 100) as number(4,1)),
         或直接插入表中, 用对应字段的精度定义来确定.
  col4 : 定长字符串
  col5 : 不定长字符串, 允许为空, 最大长度10
  col6 : 随机日期, 日期的取值范围为当前日期向前2年内
  col7 : 连续时间, 起始时间后的连续时间, 时间间隔为1小时

--------------------------------------------------------------------------------
  SQL> select 'a' as col1,
    2         decode(trunc(dbms_random.value(0,3)),
    3                 0, 'b',
    4                 1, 'c',
    5                 'd'
    6               ) as col2,
    7         round(dbms_random.value(10, 100),1) as col3,
    8         dbms_random.string('u', as col4,
    9         dbms_random.string('a', round(dbms_random.value(0,10))) as col5,
   10         trunc(sysdate) - dbms_random.value(1, 365*2) as col6,
   11         sysdate + interval '1' hour * (rownum-1) as col7
   12    from dual
   13   connect by rownum<=10;

  COL1 COL2  COL3 COL4       COL5       COL6                 COL7
  ---- ---- ----- ---------- ---------- -------------------- --------------------
  a    b     27.7 TTLMKTAO   RVUh       2004-12-15 2:10:12   2006-7-1 20:37:56
  a    b     36.4 XBJTLBZT   f          2006-5-9 7:20:55     2006-7-1 21:37:56
  a    d     30.2 RUIWQNNF   RC         2005-7-3 2:30:52     2006-7-1 22:37:56
  a    c       64 BMWHIUYC   lIwtSbUhVp 2004-9-6 21:46:19    2006-7-1 23:37:56
  a    b     99.5 YXWZQIKP   ruuMEwcHu  2004-8-13 15:53:38   2006-7-2 0:37:56
  a    d     76.2 FGEYEQAG   ZZa        2004-8-26 20:11:24   2006-7-2 1:37:56
  a    c     54.4 PSKXXWPK              2004-11-16 6:28:57   2006-7-2 2:37:56
  a    d     70.4 HOGNDMEW   Z          2006-6-15 11:41:13   2006-7-2 3:37:56
  a    d     65.9 WCLTJJVU   WCo        2005-5-28 3:20:14    2006-7-2 4:37:56
  a    b     77.6 ADWFTXOJ   iSeVYTLf   2005-7-1 22:42:29    2006-7-2 5:37:56

--------------------------------------------------------------------------------

以上的数据都是构造虚拟数据, 有时我们还需要构造出仿真的数据, 比如通过基础表的数据构造流水表的数据, 方法是类似的.浅谈构造数据