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

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

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

  声明一下,这个方案不一定最好!另外,这个方案也许不是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瓜哥从性能方面尝试做一些优化。

性能优化

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

定时调度

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

  1. 编写Shell脚本,使用Crontab在后台定时执行。使用过Quartz的Java程序猿,应该对Cron表达式很熟悉。
  2. 使用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瓜哥对这块内容不是很了解,再抽时间专门再写一篇文章来讲解。敬请期待!

 

参考资料

  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.