💇🏻 🥞 🧤 使用DBMS_SQL的SQL至CSV 🧚 ⛪️ 🖖

通常，在解决系统集成问题时，有必要以一种或另一种格式显示一定数量的数据。同时，任何人都可以成为数据使用者，但源几乎总是公司数据库。例如，制造商可能要求供应商以XLSX或XML格式等定期报告其货物的移动。

有许多用于将数据转换为各种格式的工具，其使用的可能性取决于企业采用的技术堆栈和软件体系结构。同时，您始终希望由用于转换源数据的各种库，框架和系统层组成的链尽可能短。这将减少开发解决方案所花费的时间并提高其生产效率。

如果实际上我们假设SQL查询位于数据选择过程的根源，那么理想的情况是希望使用转换链来查看此情况：

d^{'} = f （ S Q L （ d ） ）

$d'= f（SQL（d））$

在哪里

d

$d$ -源数据，

S Q L （ d ）

$SQL（d）$ -SQL查询以检索数据，

f

$f$ -将选择内容转换为所需格式的功能，

d^{'}

$d'$ -所需格式的数据。

对于Oracle PL / SQL，有许多内置的和第三方的软件包可以实现此功能。这些是DBMS_XMLGEN，DBMS_XMLQUERY，AS_XLSX，PL / JSON等。

但是，由于某种原因出现了将数据转换为CSV格式的问题时，没有现成的解决方案。我必须自己做，然后将演示如何操作。

问题陈述

创建一个工具（PL / SQL程序包），该工具在输入端接收任意SELECT查询作为字符串或光标变量，然后在输出处返回CLOB类型的对象，该对象将数据封装为CSV格式。如有任何错误，应返回NULL。 CSV格式本身不需要表示-这些是字符串，其元素由某些字符（通常为“;”）分隔，但是在一般情况下，任意字符都可以充当分隔符。假定使用字符0x0D + 0x0A分隔字符串。 CSV文件中的第一行通常是标题，并定义列名称。

定义包接口

CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END;

有两个重载过程，它们之间的区别在于，其中一个重载过程以字符串形式接收请求，而另一个重载为指向游标的链接。第二个参数是输出；这是CLOB对象中所需的结果。最后，第三个参数是CSV分隔符。

在执行这些过程时，内置的DBMS_SQL包将为我们提供帮助，这使得我们可以在事先不知道（在编译阶段）确切涉及选择多少列的情况下使用动态游标。

DBMS_SQL功能允许您动态解析和执行任意查询。对于将请求接受为字符串的过程，将发生以下情况：

 AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; --     DBMS_SQL ignore INTEGER; BEGIN DBMS_SQL.PARSE(cur, stmt, DBMS_SQL.NATIVE); ignore := DBMS_SQL.EXECUTE(cur);

对于使用游标变量的过程，一切都变得更加简单-从Oracle的第11版开始，“游标变量→SQL游标编号”转换已可用。

DBMS_SQL.TO_CURSOR_NUMBER函数将REFCURSOR变量（强类型或弱类型）转换为SQL游标号，然后可以将其传递给DBMS_SQL例程。在这种情况下，游标变量必须先打开，然后再传递给DBMS_SQL.TO_CURSOR_NUMBER函数。

 cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);

因此，这两个版本的调用都归结为获得动态光标和相关数据集的编号。下一步是获取有关此集合列的信息，并提取数据本身。

DMBS_SQL包允许您描述动态游标的列，返回有关记录关联数组中每一列的信息。

为此，必须基于集合类型DBMS_SQL.DESC_TAB（如果查询可以返回长度超过30个字符的列，则为DESC_TAB2）声明PL / SQL集合。之后，您可以使用收集方法遍历表并检索游标信息。

 AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER; --     col_val_chr VARCHAR2(32767); BEGIN DBMS_SQL.DESCRIBE_COLUMNS2(cur, ncols, cols);

接下来，必须使用动态查询将所选的每一列的类型通知DBMS_SQL包。这是通过调用DEFINE_COLUMN来完成的。

 FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP;

在第二个参数DEFINE_COLUMN中，传递了一个数字-列在列表中的顺序位置。第三个参数设置游标列的数据类型。它传递适当类型的表达式。换句话说，不传递DBMS_SQL.DEFINE_COLUMN类型名称（例如“ VARCHAR2”）的字符串，而是传递类型VARCHAR2定义的变量。

在第四个参数中定义字符类型列时，必须指定可以加载到游标中的值的最大长度。

准备工作已完成，现在您可以创建标题行并开始提取数据。

 AS cap CLOB; --    CSV- BEGIN DBMS_LOB.CREATETEMPORARY(cap, TRUE, DBMS_LOB.SESSION); FOR i IN 1 .. ncols LOOP DBMS_LOB.APPEND(cap, cols(i).col_name || delimeter); END LOOP;

使用DBMS_SQL.FETCH_ROWS以及随后对DBMS_SQL.COLUMN_VALUE的调用逐行检索数据，以获取各个列的值。

 AS bod CLOB; --   CSV- c_line_break CONSTANT VARCHAR2(2) := chr(13) || chr(10); BEGIN DBMS_LOB.CREATETEMPORARY(bod, TRUE, DBMS_LOB.SESSION); WHILE DBMS_SQL.FETCH_ROWS(ur) > 0 LOOP FOR i IN 1 .. ncols LOOP DBMS_SQL.COLUMN_VALUE(cur, i, col_val_chr); DBMS_LOB.APPEND(bod, col_val_chr || delimeter); END LOOP; DBMS_LOB.APPEND(bod, c_line_break); END LOOP;

然后它仅用于收集生成的CSV

 DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod);

处理错误

 EXCEPTION WHEN OTHERS THEN sheet := NULL;

并关闭游标

 DBMS_SQL.CLOSE_CURSOR(cur);

套餐使用选项

 DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;

 DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;

实际上，就是所有附带的源代码。

这本书对发展有帮助
Feuerstein S.，到达B。-Oracle PL / SQL。对于专业人士。

使用DBMS_SQL的SQL至CSV

More articles: