首页 > 挨踢(IT), 数据库 > 多表关联下的测试数据制造方法

多表关联下的测试数据制造方法

2013年3月6日 发表评论 阅读评论 670 人阅读    

　　声明一下，这个方案不一定最好！另外，这个方案也许不是D瓜哥首先发明的（D瓜哥只是想解决问题），但是绝对是D瓜哥自己独立思考出来的！共享出来，希望能解决大家在制造测试数据时，遇到的烦恼。当然，如果您有更好的方案，欢迎共享出来，相互学习学习。废话少说，转入正文。

单表备份

　　在讲解复杂情况之前，我们先从最简单的情况说起。最简单的情况莫过于对一个单独的表进行备份。其实，这样的语句相信很多人都写过。D瓜哥就不过多去论述了，直接上一个SQL语句来说明吧：

查看源代码
打印帮助
1-- 单表备份
2CREATE TABLE users_bak 
3  AS
4    SELECT * FROM  users;

　　当然，如果users_bak表已经存在，则使用如下语句：

查看源代码
打印帮助
1-- 单表备份。这里假设，users_bak表和users表的结构完全相同。
2INSERT INTO users_bak 
3  SELECT * FROM  users;

　　这两条SQL语句相信大家都能看懂。备份的原理也很简单：把users表中的数据查询出来，然后插入到另外一个表中进行备份。

单表造数据

　　这个，有点小麻烦。先说一下用户信息表（users）的定义如下：

查看源代码
打印帮助
01--创建表
02CREATE TABLE users  (
03   user_id            NUMERIC(10)  not null,
04   user_name          VARCHAR2(50),
05   birthday           DATE,
06   mobile_phone       NUMERIC(11),
07   CONSTRAINT pk_user PRIMARY KEY (user_id)
08);
09--创建序列
10CREATE SEQUENCE users_sequence
11   MINVALUE 1
12   MAXVALUE 9999999
13   START WITH 1
14   INCREMENT BY 1 NOCACHE;

　　开始上演魔术，”大变活人”：

查看源代码
打印帮助
01-- 使用存储过程，给单表添加数据
02DECLARE
03  rows_count INT := 1000; --这里定义需要制造的数据量
04  i          INT := 0;
05BEGIN
06  FOR i IN 0 .. rows_count LOOP
07    INSERT INTO users
08      (user_id, user_name, birthday, mobile_phone)
09      SELECT users_sequence.nextval AS user_id,
10             dbms_random.string('P', 20) AS user_name,
11             --11263是从1970年1月1号到2013年3月5日的日期间隔
12             to_date('1970-01-01', 'yyyy-mm-dd') +
13             trunc(dbms_random.value(0, 11263)) AS birthday,
14             13000000000 + dbms_random.value(0, 999999999) AS mobile_phone
15        FROM dual;
16  END LOOP;
17  COMMIT;
18END;

　　其实，这个主要是构造各个字段：利用随机函数，制造恰当的值，然后插入到数据库表中。

　　昨天和我大学数据库老师聊天，老师说不用循环，也可以达到目的。并且给了一个例子，D瓜哥测试了一下果然好使！从上的存储过程中提取SQL，稍加修改之后，这个简单的SQL如下：

查看源代码
打印帮助
01-- 使用递归查询给单表添加数据
02INSERT INTO users
03  (user_id, user_name, birthday, mobile_phone)
04  SELECT users_sequence.nextval AS user_id,
05         dbms_random.string('P', 20) AS user_name,
06         --11263是从1970年1月1号到2013年3月5日的日期间隔
07         to_date('1970-01-01', 'yyyy-mm-dd') +
08         trunc(dbms_random.value(0, 11263)) AS birthday,
09         13000000000 + dbms_random.value(0, 999999999) AS mobile_phone
10    FROM dual
11  CONNECT BY LEVEL <= 1000;
12COMMIT;

多表造数据

　　这是今天讲解的主要内容。不过，经过上面两个小节的讲解，为我们这小节的内容做了很好的铺垫。相信，大家理解起来会很顺利。

　　前两个小节，内容比较简单，SQL也很简单。所以，没有使用场景的描述。这节相对来说，业务逻辑稍微复杂一点。所以，我们先说一下使用场景：根据用户(user)基本信息，以及用户每通电话的话费（phone_fee），计算每个用户的总话费（total_fee）。

　　用户信息表的定义如上一小节。电话费用表（phone_fee）定义如下：

查看源代码
打印帮助
01--创建表
02CREATE TABLE phone_fee  (
03   fee_id             NUMERIC(18)  not null,
04   user_id            NUMERIC(10),
05   fee                NUMERIC(10, 2),
06   fee_date           DATE,
07   CONSTRAINT pk_phone_fee PRIMARY KEY (fee_id)
08);
09--创建序列
10CREATE SEQUENCE phone_fee_sequence
11   MINVALUE 1
12   MAXVALUE 9999999
13   START WITH 1
14   INCREMENT BY 1 NOCACHE;

　　话费总计表（total_fee）定义如下：

查看源代码
打印帮助
01--创建表
02CREATE TABLE total_fee  (
03   tfee_id            NUMERIC(18)  not null,
04   user_id            NUMERIC(10),
05   user_name          VARCHAR2(50),
06   mobile_phone       NUMERIC(11),
07   month_total_fee    NUMERIC(10, 2),
08   fee_month          DATE,
09   data_state         NUMERIC(2),
10   CONSTRAINT pk_total_fee PRIMARY KEY (tfee_id)
11);
12--创建序列
13CREATE SEQUENCE total_fee_sequence
14   MINVALUE 1
15   MAXVALUE 9999999
16   START WITH 1
17   INCREMENT BY 1 NOCACHE;

　　了解了各个表的定义。下面，我们开始造数据，其实很简单，相信你已经猜出个八九不离十了。具体SQL如下：

查看源代码
打印帮助
01-- 多表关联下，添加测试数据
02INSERT INTO total_fee
03  (tfee_id,
04   user_id,
05   user_name,
06   mobile_phone,
07   month_total_fee,
08   fee_month,
09   data_state)
10  SELECT total_fee_sequence.nextval AS tfee_id,
11         d.user_id                  AS user_id,
12         d.user_name                AS user_name,
13         d.mobile_phone             AS mobile_phone,
14         d.month_total_fee          AS month_total_fee,
15         d.fee_month                AS fee_month,
16         d.data_state               AS data_state
17    FROM (SELECT u.user_id,
18                 u.user_name,
19                 u.mobile_phone,
20                 sum(f.fee) AS month_total_fee,
21                 trunc(f.fee_date, 'MM') AS fee_month,
22                 trunc(dbms_random.value(0, 5)) AS data_state
23            FROM users u, phone_fee f
24           WHERE u.user_id = f.user_id
25           GROUP BY u.user_id,
26                    u.user_name,
27                    u.mobile_phone,
28                    trunc(f.fee_date, 'MM')) d;

　　当然，这条SQL语句假设”用户信息表（user）”和”电话费用表（phone_fee）”中已经有数据了。如果没有数据，则参考上面的小节，给每个表分别造数据。然后再使用这条SQL语句造多表的数据。

　　为了方便大家测试，D瓜哥把给电话费用表（phone_fee）添加测试数据的SQL也贴出来：

查看源代码
打印帮助
01-- 单表添加测试数据
02INSERT INTO phone_fee
03  (fee_id, user_id, fee, fee_date)
04  SELECT phone_fee_sequence.nextval AS fee_id,
05         trunc(dbms_random.value(0, 1000)) AS user_id,
06         trunc(dbms_random.value(0, 300), 2) AS fee,
07         --11263是从1970年1月1号到2013年3月5日的日期间隔
08         to_date('2012-01-01', 'yyyy-mm-dd') +
09         trunc(dbms_random.value(0, 365)) AS fee_date
10    FROM dual
11  connect by level <= 1000;
12commit;

　　基本的造数据方法已经讲解完毕。这个方法有些粗糙，还存在很多需要改进的地方。下面，D瓜哥从性能方面尝试做一些优化。

性能优化

　　在电信行业中，千万级别的表随处可见。即使在单表情况下，仅仅是简单的单表查询也会非常耗时。在多表关联下，数据的查询更是很多时候的性能瓶颈所在。更何况再加上数据插入操作（我们就暂时不去计较背后的数据约束校验了），更是”瓶颈中的战斗机”啊！所以，如何进行性能优化是必须考虑的问题。

定时调度

　　实现定时调度一共有两种方式：

  1. 编写Shell脚本，使用Crontab在后台定时执行。使用过Quartz的Java程序猿，应该对Cron表达式很熟悉。
  2. 使用Oracle的Job，添加定时执行的存储过程。

　　今天，D瓜哥着重介绍一下这个第二种方式：使用Oracle存储过程的定时调度。使用定时调度之前，需要将上面的SQL语句封装到一个存储过程中去，命名为summarize_data。具体代码如下：

查看源代码
打印帮助
01-- 汇总数据
02CREATE OR REPLACE PROCEDURE summarize_data IS
03BEGIN
04  INSERT INTO total_fee
05    (tfee_id,
06     user_id,
07     user_name,
08     mobile_phone,
09     month_total_fee,
10     fee_month,
11     data_state)
12    SELECT total_fee_sequence.nextval AS tfee_id,
13           d.user_id                  AS user_id,
14           d.user_name                AS user_name,
15           d.mobile_phone             AS mobile_phone,
16           d.month_total_fee          AS month_total_fee,
17           d.fee_month                AS fee_month,
18           d.data_state               AS data_state
19      FROM (SELECT u.user_id,
20                   u.user_name,
21                   u.mobile_phone,
22                   sum(f.fee) AS month_total_fee,
23                   trunc(f.fee_date, 'MM') AS fee_month,
24                   trunc(dbms_random.value(0, 5)) AS data_state
25              FROM users u, phone_fee f
26             WHERE u.user_id = f.user_id
27             GROUP BY u.user_id,
28                      u.user_name,
29                      u.mobile_phone,
30                      trunc(f.fee_date, 'MM')) d;
31  COMMIT;
32EXCEPTION
33  WHEN OTHERS THEN
34    DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
35    ROLLBACK;
36END;

　　存储过程定义完毕，在这个基础上，我们来创建Job，代码如下：

查看源代码
打印帮助
01-- 使用汇总数据的存储过程，运行定时调度
02DECLARE
03  job_summarize_data number;
04BEGIN
05  dbms_job.submit(job_summarize_data,
06                  'summarize_data;',
07                  sysdate,  -- 由于只是测试，我们设置成立即执行
08                  'sysdate+1/(24*60)');  -- 每分钟执行一次
09  dbms_job.run(job_summarize_data);  -- 开始执行调度
10  dbms_output.put_line('Job ' || to_char(job_summarize_data) ||
11                       ' is running');
12END;

　　到这里，定时调度就可以开始工作了。不过，重要提醒，由于我对Oracle存储过程的不熟悉，有些东西解释的不是很明白。D瓜哥会尽快熟悉一下这些知识，把一些没有解释清楚的地方，尽快再补充一下。

SQL Loader

　　SQL Loader是Oracle数据库专门为需要向数据库导入大量数据而提供的功能。由于这块内容较多，以及D瓜哥对这块内容不是很了解，再抽时间专门再写一篇文章来讲解。敬请期待！

 

参考资料

  1. oracle定制定时执行任务
  2. Oracle存储过程定时执行2种方法
  3. Oracle to_char格式化函数
  4. oracle日期函数集锦
  5. 从oracle表中随机取记录，产生随机数和随机字符串
  6. Oracle Trunc函数
  7. Oracle TRUNC函数的正确用法
  8. Oracle中DBMS_JOB.SUBMIT的用法说明
  9. 如何设定oracle dbms_job.submit时间间隔

 

修改日志

　　2013年3月8日　　根据张老师的指导，简化SQL语句；实际运行、测试所有语句。



作 者： D瓜哥，https://www.diguage.com/
原文链接：https://wordpress.diguage.com/archives/103.html
版权声明：非特殊声明均为本站原创作品，转载时请注明作者和原文链接。

分类: 挨踢(IT), 数据库 标签: , ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.