SQL в CSV с помощью DBMS_SQL

Часто при решении задач системной интеграции требуется представить некоторый объем данных в том или ином формате. При этом потребителем данных может быть кто угодно, а вот источником почти всегда является корпоративная база данных. К примеру, производитель может требовать у поставщика периодические отчеты о движении своих товаров в формате XLSX или XML, etc.

Существует множество инструментальных средств для преобразования данных в различные форматы, и возможность их использования зависит от принятого на предприятии технологического стека и программной архитектуры. При этом всегда хочется, чтобы цепочка, состоящая из различных библиотек, фреймворков, системных слоев, используемых для преобразования исходных данных, была как можно короче. Это бы сократило временные затраты на разработку решения и повысило его производительную эффективность.

Если принять, что по факту в корне процесса выборки данных лежит SQL-запрос, то в идеале цепочку преобразований хотелось бы видеть такой:

где
— исходные данные,
— SQL-запрос на выборку данных,
— функция, которая преобразует выборку в требуемый формат,
— данные в требуемом формате.

Для 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);

Для процедуры, принимающей курсорную переменную, все проще — начиная с 11-ой версии Oracle стало доступно преобразование «курсорная переменная → номер курсора 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 позволяет описывать столбцы динамического курсора, возвращая информацию о каждом столбце в ассоциативном массиве записей.

Для этого необходимо объявить коллекцию PL/SQL на базе типа коллекции DBMS_SQL.DESC_TAB (или DESC_TAB2, если запрос может возвращать имена столбцов, длина которых превышает 30 символов). После этого можно использовать методы коллекций для перебора таблицы и извлечения информации о курсоре.

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;

Вот, собственно, и все, исходники прилагаются.

В разработке помогла книга
Фейерштейн С., Прибыл Б. — Oracle PL/SQL. Для профессионалов.