多表关联下的测试数据制造方法
声明一下，这个方案不一定最好！另外，这个方案也许不是D瓜哥首先发明的（D瓜哥只是想解决问题），但是绝对是D瓜哥自己独立思考出来的！共享出来，希望能解决大家在制造测试数据时，遇到的烦恼。当然，如果您有更好的方案，欢迎共享出来，相互学习学习。废话少说，转入正文。
单表备份
在讲解复杂情况之前，我们先从最简单的情况说起。最简单的情况莫过于对一个单独的表进行备份。其实，这样的语句相信很多人都写过。D瓜哥就不过多去论述了，直接上一个SQL语句来说明吧：
-- 单表备份
CREATE TABLE users_bak
AS
SELECT * FROM users;
当然，如果users_bak表已经存在，则使用如下语句：
-- 单表备份。这里假设，users_bak表和users表的结构完全相同。 INSERT INTO users_bak SELECT * FROM users;
这两条SQL语句相信大家都能看懂。备份的原理也很简单：把users表中的数据查询出来，然后插入到另外一个表中进行备份。
单表造数据
这个，有点小麻烦。先说一下用户信息表（users）的定义如下：
--创建表 CREATE TABLE users ( user_id NUMERIC(10) not null, user_name VARCHAR2(50), birthday DATE, mobile_phone NUMERIC(11), CONSTRAINT pk_user PRIMARY KEY (user_id) ); --创建序列 CREATE SEQUENCE users_sequence MINVALUE 1 MAXVALUE 9999999 START WITH 1 INCREMENT BY 1 NOCACHE;
开始上演魔术，”大变活人”：
-- 使用存储过程，给单表添加数据
DECLARE
rows_count INT := 1000; --这里定义需要制造的数据量
i INT := 0;
BEGIN
FOR i IN 0 .. rows_count LOOP
INSERT INTO users
(user_id, user_name, birthday, mobile_phone)
SELECT users_sequence.nextval AS user_id,
dbms_random.string('P', 20) AS user_name,
--11263是从1970年1月1号到2013年3月5日的日期间隔
to_date('1970-01-01', 'yyyy-mm-dd') +
trunc(dbms_random.value(0, 11263)) AS birthday,
13000000000 + dbms_random.value(0, 999999999) AS mobile_phone
FROM dual;
END LOOP;
COMMIT;
END;
其实，这个主要是构造各个字段：利用随机函数，制造恰当的值，然后插入到数据库表中。
昨天和我大学数据库老师聊天，老师说不用循环，也可以达到目的。并且给了一个例子，D瓜哥测试了一下果然好使！从上的存储过程中提取SQL，稍加修改之后，这个简单的SQL如下：
-- 使用递归查询给单表添加数据
INSERT INTO users
(user_id, user_name, birthday, mobile_phone)
SELECT users_sequence.nextval AS user_id,
dbms_random.string('P', 20) AS user_name,
--11263是从1970年1月1号到2013年3月5日的日期间隔
to_date('1970-01-01', 'yyyy-mm-dd') +
trunc(dbms_random.value(0, 11263)) AS birthday,
13000000000 + dbms_random.value(0, 999999999) AS mobile_phone
FROM dual
CONNECT BY LEVEL <= 1000;
COMMIT;
多表造数据
这是今天讲解的主要内容。不过，经过上面两个小节的讲解，为我们这小节的内容做了很好的铺垫。相信，大家理解起来会很顺利。
前两个小节，内容比较简单，SQL也很简单。所以，没有使用场景的描述。这节相对来说，业务逻辑稍微复杂一点。所以，我们先说一下使用场景：根据用户(user)基本信息，以及用户每通电话的话费（phone_fee），计算每个用户的总话费（total_fee）。
用户信息表的定义如上一小节。电话费用表（phone_fee）定义如下：
--创建表 CREATE TABLE phone_fee ( fee_id NUMERIC(18) not null, user_id NUMERIC(10), fee NUMERIC(10, 2), fee_date DATE, CONSTRAINT pk_phone_fee PRIMARY KEY (fee_id) ); --创建序列 CREATE SEQUENCE phone_fee_sequence MINVALUE 1 MAXVALUE 9999999 START WITH 1 INCREMENT BY 1 NOCACHE;
话费总计表（total_fee）定义如下：
--创建表 CREATE TABLE total_fee ( tfee_id NUMERIC(18) not null, user_id NUMERIC(10), user_name VARCHAR2(50), mobile_phone NUMERIC(11), month_total_fee NUMERIC(10, 2), fee_month DATE, data_state NUMERIC(2), CONSTRAINT pk_total_fee PRIMARY KEY (tfee_id) ); --创建序列 CREATE SEQUENCE total_fee_sequence MINVALUE 1 MAXVALUE 9999999 START WITH 1 INCREMENT BY 1 NOCACHE;
了解了各个表的定义。下面，我们开始造数据，其实很简单，相信你已经猜出个八九不离十了。具体SQL如下：
-- 多表关联下，添加测试数据
INSERT INTO total_fee
(tfee_id,
user_id,
user_name,
mobile_phone,
month_total_fee,
fee_month,
data_state)
SELECT total_fee_sequence.nextval AS tfee_id,
d.user_id AS user_id,
d.user_name AS user_name,
d.mobile_phone AS mobile_phone,
d.month_total_fee AS month_total_fee,
d.fee_month AS fee_month,
d.data_state AS data_state
FROM (SELECT u.user_id,
u.user_name,
u.mobile_phone,
sum(f.fee) AS month_total_fee,
trunc(f.fee_date, 'MM') AS fee_month,
trunc(dbms_random.value(0, 5)) AS data_state
FROM users u, phone_fee f
WHERE u.user_id = f.user_id
GROUP BY u.user_id,
u.user_name,
u.mobile_phone,
trunc(f.fee_date, 'MM')) d;
当然，这条SQL语句假设”用户信息表（user）”和”电话费用表（phone_fee）”中已经有数据了。如果没有数据，则参考上面的小节，给每个表分别造数据。然后再使用这条SQL语句造多表的数据。
为了方便大家测试，D瓜哥把给电话费用表（phone_fee）添加测试数据的SQL也贴出来：
-- 单表添加测试数据
INSERT INTO phone_fee
(fee_id, user_id, fee, fee_date)
SELECT phone_fee_sequence.nextval AS fee_id,
trunc(dbms_random.value(0, 1000)) AS user_id,
trunc(dbms_random.value(0, 300), 2) AS fee,
--11263是从1970年1月1号到2013年3月5日的日期间隔
to_date('2012-01-01', 'yyyy-mm-dd') +
trunc(dbms_random.value(0, 365)) AS fee_date
FROM dual
connect by level <= 1000;
commit;
基本的造数据方法已经讲解完毕。这个方法有些粗糙，还存在很多需要改进的地方。下面，D瓜哥从性能方面尝试做一些优化。
性能优化
在电信行业中，千万级别的表随处可见。即使在单表情况下，仅仅是简单的单表查询也会非常耗时。在多表关联下，数据的查询更是很多时候的性能瓶颈所在。更何况再加上数据插入操作（我们就暂时不去计较背后的数据约束校验了），更是”瓶颈中的战斗机”啊！所以，如何进行性能优化是必须考虑的问题。
定时调度
实现定时调度一共有两种方式：
- 编写Shell脚本，使用Crontab在后台定时执行。使用过Quartz的Java程序猿，应该对Cron表达式很熟悉。
- 使用Oracle的Job，添加定时执行的存储过程。
今天，D瓜哥着重介绍一下这个第二种方式：使用Oracle存储过程的定时调度。使用定时调度之前，需要将上面的SQL语句封装到一个存储过程中去，命名为summarize_data。具体代码如下：
-- 汇总数据
CREATE OR REPLACE PROCEDURE summarize_data IS
BEGIN
INSERT INTO total_fee
(tfee_id,
user_id,
user_name,
mobile_phone,
month_total_fee,
fee_month,
data_state)
SELECT total_fee_sequence.nextval AS tfee_id,
d.user_id AS user_id,
d.user_name AS user_name,
d.mobile_phone AS mobile_phone,
d.month_total_fee AS month_total_fee,
d.fee_month AS fee_month,
d.data_state AS data_state
FROM (SELECT u.user_id,
u.user_name,
u.mobile_phone,
sum(f.fee) AS month_total_fee,
trunc(f.fee_date, 'MM') AS fee_month,
trunc(dbms_random.value(0, 5)) AS data_state
FROM users u, phone_fee f
WHERE u.user_id = f.user_id
GROUP BY u.user_id,
u.user_name,
u.mobile_phone,
trunc(f.fee_date, 'MM')) d;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
ROLLBACK;
END;
存储过程定义完毕，在这个基础上，我们来创建Job，代码如下：
-- 使用汇总数据的存储过程，运行定时调度
DECLARE
job_summarize_data number;
BEGIN
dbms_job.submit(job_summarize_data,
'summarize_data;',
sysdate, -- 由于只是测试，我们设置成立即执行
'sysdate+1/(24*60)'); -- 每分钟执行一次
dbms_job.run(job_summarize_data); -- 开始执行调度
dbms_output.put_line('Job ' || to_char(job_summarize_data) ||
' is running');
END;
到这里，定时调度就可以开始工作了。不过，重要提醒，由于我对Oracle存储过程的不熟悉，有些东西解释的不是很明白。D瓜哥会尽快熟悉一下这些知识，把一些没有解释清楚的地方，尽快再补充一下。
SQL Loader
SQL Loader是Oracle数据库专门为需要向数据库导入大量数据而提供的功能。由于这块内容较多，以及D瓜哥对这块内容不是很了解，再抽时间专门再写一篇文章来讲解。敬请期待！
参考资料
- oracle定制定时执行任务
- Oracle存储过程定时执行2种方法
- Oracle to_char格式化函数
- oracle日期函数集锦
- 从oracle表中随机取记录，产生随机数和随机字符串
- Oracle Trunc函数
- Oracle TRUNC函数的正确用法
- Oracle中DBMS_JOB.SUBMIT的用法说明
- 如何设定oracle dbms_job.submit时间间隔
修改日志
2013年3月8日 根据张老师的指导，简化SQL语句；实际运行、测试所有语句。
原文链接：https://wordpress.diguage.com/archives/103.html
版权声明：非特殊声明均为本站原创作品，转载时请注明作者和原文链接。
