"地瓜哥"博客网

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

1. 多表关联下的测试数据制造方法（一）：思路详解

　　上一节中，我们详细介绍了单表、多表关联等情况下，制造测试数据的思路，并用SQL、存储过程等测试了这些思路。这一节，D瓜哥讲继续上一节的思路，使用SQL Loader来提升导入数据的效率。

　　使用SQL Loader来导入数据时，必须提供有规范格式的数据文件。假设，我们的基础表已经有数据了。目前需要做的事，1、根据基础表生成数据文件；2、使用SQL Loader讲数据文件中的数据导入到数据汇总表。那么，问题就来了：我们如何生存数据文件呢？这一篇文章，我们就重点关注一下，如何根据查询结果生存数据文件的问题。

　　了解SQL的朋友应该都知道，使用SQL增删改查还行。如果让SQL读写文件，那真是鞭长莫及啊！好在Oracle的存储过程提供了读写文件的功能。所以，只能通过Oracle的存储过程来完成数据文件的生成。下面，给大家讲解一下如果通过存储过程来生成数据文件。

准备工作

　　提供文件读写功能的utl_file包。使用这包之前，必须先创建DIRECTORY类型的变量，用于指点生成数据文件的路径。所以，需要DBA或者或者是其他具有CREATE DIRECTORY权限的用户创建DIRECTORY，SQL如下：

-- 创建DIRECTORY
CREATE OR REPLACE DIRECTORY output_data_dir AS 'd:\oracle';  
-- 注意，这个路径必须数据库服务器上的路径。

　　创建完成后，将其授权给需要使用的用户，这里假如是scott。SQL如下：

-- 授权DIRECTORY
GRANT read,write ON DIRECTORY output_data_dir TO scott;

　　然后，就可以使用存储过程生成文件了。根据使用游标类型的不同，还分使用显式游标和使用隐式游标两种方式。下面我们分别给出相关例子。

使用显式游标生成数据文件

　　先来了解一下游标的概念。在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

　　游标有两种类型：显式游标和隐式游标。显式游标即用户显式声明的游标，即指定结果集。我们直接来看看如何使用显式游标读取数据，导出成数据文件的程序。代码如下：

-- 使用显式游标生成数据
CREATE OR REPLACE PROCEDURE export_to_csv(data_dir IN VARCHAR2) is
  -- 显示游标，一次性将数据全部查询完
  CURSOR mycur IS
    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;
  -- 行记录
  myrecord d%ROWTYPE; -- 不知道这样写是否可以

  csv_output utl_file.file_type;

  out_file_name VARCHAR2(20);

  fee_month_str VARCHAR2(20);

  begin_time NUMBER;
  end_time   NUMBER;

  count_num NUMBER;
BEGIN
  begin_time := dbms_utility.get_time;
  -- 显式打开游标
  OPEN mycur;
  -- 拼接文件名
  out_file_name := 'total_fee_data.csv';
  --打开文件
  csv_output := UTL_FILE.FOPEN(data_dir, out_file_name, 'W');
  -- 假设有100000000条数据，为了简化起见，这个就不在使用SQL查询了。
  while count_num < 100000000 loop
    -- 逐条取游标记录放入记录中
    fetch mycur into myrecord;
  
    -- 将日期转换为字符串
    fee_month_str := to_char(myrecord.fee_month, 'yyyy-MM-dd HH24:mi');
  
    --写文件
    utl_file.put_line(csv_output,
                      myrecord.tfee_id || ',' || myrecord.user_id || ','
					  || myrecord.user_name || ',' 
					  || myrecord.mobile_phone || ',' 
					  || myrecord.month_total_fee || ',' 
					  || fee_month_str || ',' || myrecord.data_state);
  
    -- 取游标中下一条记录
    --fetch mycur into myrecord;
  END LOOP;
  
  UTL_FILE.FCLOSE(csv_output);
  -- 关闭游标
  close mycur;
  end_time := dbms_utility.get_time;
  dbms_output.put_line('Total time=' || 
	          (end_time - begin_time) * 10 || 'ms.');
END;

-- 执行导出
BEGIN
  export_to_csv('output_data_dir');
END;

使用隐式游标生成数据文件

　　显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

　　刚刚演示了显式游标的例子。现在再看看隐式游标的例子。在D瓜哥看来，显式游标和隐式游标只是使用方法上的不同。基本原理是一样的。例子代码如下：

-- 使用隐式游标导出数据文件
CREATE OR REPLACE PROCEDURE export_to_csv(data_dir IN VARCHAR2) IS
  csv_output    utl_file.file_type;
  out_file_name VARCHAR2(20);
  fee_month_str VARCHAR2(20);
  begin_time    NUMBER;
  end_time      NUMBER;
BEGIN
  --记录开始时间，注意dbms_utility.get_time获取的时间戳单位是1/100s
  begin_time := dbms_utility.get_time; 

  out_file_name := 'total_fee_data.csv'; --存储内容的文件名

  --以写方式打开指定目录中指定文件名文件
  csv_output := utl_file.fopen(data_dir, out_file_name, 'W');

  --隐式游标
  FOR cur IN (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
              
              ) LOOP

    --将日期转换为字符串
    fee_month_str := to_char(cur.fee_month, 'yyyy-MM-dd HH24:mi');
  
    --写文件
    utl_file.put_line(csv_output,
                      cur.tfee_id || ',' || cur.user_id || ',' 
					  || cur.user_name || ',' || cur.mobile_phone || ',' 
					  || cur.month_total_fee || ',' || fee_month_str 
					  || ',' || cur.data_state);
  END LOOP;

  utl_file.fclose(csv_output);

  end_time := dbms_utility.get_time;

  dbms_output.put_line('Total time=' || 
                    (end_time - begin_time) * 10 || 'ms.');
END;

--执行导出
BEGIN
  export_to_csv('output_data_dir');
END;

　　注意：由于D瓜哥没有CREATE DIRECTORY的权限。所以，这里的例子，没有进行测试。如果有朋友测试后发现问题，请及时通知D瓜哥，D瓜哥马上改正。谢谢！

　　一般数据量比较大的情况下，还要考虑分批导出到不同文件中。这个留给大家慢慢摸索吧！下一节，讲给大家介绍使用SQL Loader将数据文件导入到数据库中。敬请关注！

参考资料

1. 使用存储过程将Oracle数据批量导出为多个csv文件
2. Oracle的UTL_FILE.FOPEN的用法
3. 利用存储过程将表中数据导出到.txt文件
4. Oracle存储过程读写文件
5. Oracle存储过程读写文件
6. oracle 隐式游标,显示游标
7. ORACLE PL/SQL编程之四：把游标说透
8. ORACLE游标概念讲解