oracle csv只能导出1200条,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;

/