Oracle PLSQL数据导出csv的案例

　　之前项目运维人员碰到一个问题，需要写一个存储过程，把数据导出为csv文件，查了一些资料，帮他写成了一个PLSQL，今天拿出来分享一下，不足之处，欢迎指教。

数据背景：  用到两张表，一张存放单位组织名称org_name，它只有一个字段org_name；一张存放要导出的具体数据ryxx，其中ryxx这张表有一个字段是org_name中的org_name字段（需要like '%org_name%'）。（表因为一些原因不能贴出来，见谅）

目标需求：  要求根据单位组织名称即org_name，分批导出ryxx数据为csv文件，并且导出的文件最大不能超过30000行，文件名为单位组织名称后跟序号

所用知识：  PLSQL语法，包括游标、循环、条件等语句，CSV文件导出语法

具体实现：  

　　--1、创建要导出文件的存放地址的变量

　　CREATE OR REPLACE DIRECTORY mydir AS 'D:\sjdc';

　　--2、PLSQL开始

　　DECLARE

　　　　-- 定义游标 org 用于获取组织名称

　　　　CURSOR org IS SELECT org_name FROM org_name;

　　　　-- 定义组织名 org_name， 接收组织名

　　　　org_name VARCHAR2(40);

　　　　-- 定义组织名相似匹配（用于 LIKE 查询）

　　　　org_name_like VARCHAR(40);

　　　　-- 定义总量，接收每次根据组织名称查询时 ryxx 表中匹配的数据总量

　　　　count_number NUMBER;

　　　　-- 定义每个组织名称关联的数据需要循环次数（因为每次导出只能导出30000条数据，需要多次导出）

　　　　loop_times NUMBER;

　　　　-- 定义当前循环到第几次（同上）

　　　　loop_i NUMBER;

　　　　-- 定义根据 org_name 匹配查询出的具体数据，为SYS_REFCURSOR类型，即动态游标

　　　　data_cur SYS_REFCURSOR;

　　　　-- 定义匹配查询出的具体数据的总量（同上）

　　　　count_cur SYS_REFCURSOR;

　　　　-- 定义文件输出

　　　　csv_output UTL_FILE.FILE_TYPE;

　　　　-- 定义 ryxx 的行类型

　　　　data_row ryxx%ROWTYPE;

　　　　-- 每个文件导出的最大行数

　　　　MAX_LINE NUMBER := 30000;

　　　　-- 输出位置

　　　　dir VARCHAR(20) := 'MYDIR';

　　-- 函数体开始

　　BEGIN

　　　　-- 1、打开 org 游标，获取组织名称，挨个取出名称进行操作

　　　　OPEN org;

　　　　　　LOOP

　　　　　　　　--  2、循环取出组织名称，当无数据时推出循环

　　　　　　　　FETCH org INTO org_name;

　　　　　　　　EXIT WHEN org%NOTFOUND;

　　　　　　　　-- 3、拼接相似查询的 org_name_like，两边都有%

　　　　　　　　org_name_like := '%' || org_name || '%';

　　　　　　　　-- 4、打开游标 count_cur， 查询对应的单位名称下的 ryxx 总量

　　　　　　　　OPEN count_cur FOR 'SELECT COUNT(*) FROM ryxx WHERE st_code_name like :org_name_like' USING org_name_like;

　　　　　　　　　　FETCH count_cur INTO count_number;

　　　　　　　　CLOSE count_cur;

　　　　　　　　-- 5、 计算此单位的数据总共需要导出几次

　　　　　　　　loop_times := count_number/MAX_LINE;

　　　　　　　　-- 6、 开始循环导出数据

　　　　　　　　loop_i := 0;

　　　　　　　　LOOP

　　　　　　　　　　-- 退出循环条件：当前循环次数大于总共要循环的次数

　　　　　　　　　　EXIT WHEN loop_i > loop_times;

　　　　　　　　　　

　　　　　　　　　　IF loop_i <= loop_times THEN

　　　　　　　　　　　　-- 7、打开查询数据的data_cur游标，导出数据（需要分页查询，所以外层不能直接用*，否则不能把数据放入data_row）

　　　　　　　　　　　　OPEN data_cur FOR 'SELECT id, name, age FROM

　　　　　　　　　　　　　　　　(SELECT t.*, rownum rn FROM ryxx WHERE ST_CODE_NAME LIKE :org_name_like)

　　　　　　　　　　　　　　　　WHERE rn <= ' || TO_CHAR((loop_i) * MAX_LINE) || ' AND rn > ' || TO_CHAR(loop_i * MAX_LINE) USING org_name_like;

　　　　　　　　　　　　　　csv_output := UTL_FILE.FOPEN('MYDIR', org_name || loop_i || '.csv', 'W', MAX_LINE);

　　　　　　　　　　　　　　LOOP

　　　　　　　　　　　　　　　　FETCH data_cur INTO data_row;

　　　　　　　　　　　　　　　　EXIT WHEN data_cur%NOTFOUND;

　　　　　　　　　　　　　　　　UTL_FILE.PUT_LINE(CSV_OUTPUT, data_row.id || ',' || data_row.name || ',' || data_row.age);

　　　　　　　　　　　　　　END LOOP;

　　　　　　　　　　　　　　loop_i := loop_i + i;

　　　　　　　　　　　　　　UTL_FILE.FCLOSE(csv_output);

　　　　　　　　　　　　CLOSE data_cur;

　　　　　　　　　　END IF;　　　　　　　　　　

　　　　　　　　END LOOP;

　　　　　　END LOOP;

　　　　CLOSE org;

　　END;

　　/