浅谈构造数据

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


在这里先介绍数据构造中的几个简单的方法.
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', 8) as u,
2 dbms_random.string('l', 8) as l,
3 dbms_random.string('a', 8) as a,
4 dbms_random.string('x', 8) as x,
5 dbms_random.string('p', 8) 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', 8) 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

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

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