Создание Excel файла из селекта с параметрами при помощи чистого PL/SQL, как альтернатива Oracle*Reports

Содержание

Цели, которых я хотел достичь
Как обеспечить динамичность селекта с параметрами, получаемыми в runtime
Парсинг и выполнение
Алгоритм работы
Нужно хранить текст селектов
Работа с параметрами
Собственно создание Excel
Единая форма, предлагающая пользователю ввести данные

Часть 1

Цели, которых я хотел достичь

Excel, как результат селекта, текст которого процедура узнает только в runtime
Селект перед выпонением видоизменяется в соответствии с параметрами, которые получает процедура
Процедуре передаются параметры файла, который будет создан
Возможность получения результирующего файла в форматах Excel Workbook,CSV,HTML,XML

Хранимая PL/SQL процедура получает в параметрах

текст селекта
параметры файла Excel
параметры выполнения

и создает полноценный Excel с несколькими таблицами(sheets).
Я знаю, что есть Crystal Reports и Oracle BI Publisher.
Но, во-первых, это крупные продукты(с большими ценами…), а Publisher, насколько я знаю не работает как отдельный модуль без Oracle Business Intelligence Enterprise Edition. И кроме того, речь шла о довольно узкой задаче создания файла без layout.

В конце, я написал один PL/SQL пакет, который находится в database и может быть вызван из любой аппликации. В ходе написания я столкнулся со многими ограничениями и хочу рассказать о том, как их поборол.

Для тех — кто сомневается, я этого, конечно не мог знать заранее, но за несколько лет, что пакет работает в большой компании, у меня не было проблем свести RDF любой сложности, с многими триггерами/формулами, в один селект, хвала Ораклу. Наоборот, так как селект — стринговый параметр и его можно построить динамически, это дает большую гибкость. В параметрах можно задать даже имя таблицы.

Прежде всего не судите строго за обилие англицизмов(так, по-моему, это называется), я просто давно вне русского программного сообщества и не знаю, чем заменяют эти слова.

Очень часто в аппликациях, написанных в Oracle Forms/Reports для создания файла Excel используют Oracle*Reports, потому что там есть возможность использовать параметры и видоизменять селект до его выполнения. Потом в триггер на уровне строки вывода пишут вывод в файл. Получается csv файл. Ну что же, можно и так, конечно.
Если вместе с Excel нужно создать pdf, то никуда не денешься, пользуйся Reports и не жалуйся как тебя достала эта программа. Но ведь часто нужен только Excel и городить для этого RDF как-то не хочется.

Итак, к делу.

Параметры файла

Тут все просто,

 <DIR_NAME> </DIR_NAME>  <FILE_NAME> </FILE_NAME>  <OUT_TYPE> </OUT_TYPE>  <LIMIT_ROWS> 
</LIMIT_ROWS>  <LIMIT_LEN> </LIMIT_LEN>  <EXCEL_TITLE> </EXCEL_TITLE>  <SUBTITLE></SUBTITLE> 
 <SUBTITLE2></SUBTITLE2>  <DIRECTION> ltr/rtl </DIRECTION>  <CHARSET> </CHARSET>  <LITERAL_PARAMS>
 Y/N</LITERAL_PARAMS>  <DIVIDE_BY>FILES|SHEETS</DIVIDE_BY>  <PARAM_TITLE> </PARAM_TITLE>  <PAR_NAME_HEAD>
 </PAR_NAME_HEAD>  <PAR_VALUE_HEAD> </PAR_VALUE_HEAD>  <NOT_FOUND_MSG> </NOT_FOUND_MSG>
  <LONG_OUT>Y/N</LONG_OUT>  <MULTI_VALUE_DELIMITER> </MULTI_VALUE_DELIMITER>  <CURR_DATE_PROMPT>
 </CURR_DATE_PROMPT>  <DEFAULT_DATE_MASK> </DEFAULT_DATE_MASK>  <OUTPUT_DATE_FORMAT></OUTPUT_DATE_FORMAT>
  <CURRENT_SHEET></CURRENT_SHEET>  <TOTAL_SHEETS></TOTAL_SHEETS>

Примерно такой набор. Думаю, тут все понятно. Несколько слов:

LIMIT_ROWS, LIMIT_LEN позволяют делить результирующий файл в процессе создания по мере достижения предельных значений на несколько Excel корректных.

LITERAL_PARAMS говорит о том, как использовать параметры выполнения — вставлять значения или выполнять селект в dbms_sql с dbms_sql.bind_variable.

OUT_TYPE задает формат: Excel Workbook,CSV,HTML,XML

Office Add-Ins для Excel — новые возможности для разработчиков на VBA и VSTO

Как обеспечить динамичность селекта с параметрами, получаемыми в runtime

Параметры выполнения

Язык предвыполнения

Нужен некий язык, на котором можно написать инструкции, что делать в зависимости от значений параметров выполнения.

Получаем параметры выполнения.
Компилируем текст селекта.
Подаем его для выполнения следующему шагу.

Вот язык, который в конце покрывал все мои потребности

В тексте селекта это выглядит как комментарий(hint)

/*!<HINT> [{]operand1[}] [ [{]operand2[}][{]operand3[}] ] ; !*/

Первое слово — это hint, определяющий команду
VAR CHAR | NUMBER | AS_IS | DATE [date format] {PL/SQL expression};
IF_CONTINUE {PL/SQL expression}
IF_EXECUTE {PL/SQL expression}
EXPR {PL/SQL expression};
IIF_EXPR {boolean expression} {String if true} {String if false};
IS_NOT_NULL {Bind variable} {String if Bind variable is not null};
IS_NULL {Bind variable} {String if Bind variable is null};
BOTTOM_SUM {Total bottom title} B C …Z;
ROW_SUM {Total column title} B C D… Z;
BEFORE {PL/SQL block};
TITLE {Title};
ALIAS {column_name} {alias};
AFTER {PL/SQL block};

Шаг компиляции заключается в том, что я нахожу в тексте команду, если один из операндов требует выполнения — выполняю это как select (expression) from dual или как PL/SQL блок в execute immediate и заменяю всю команду на результат выполнения.

Например

В зависимости от передаваемых значений можно получить всякие селекты

Я это описал для того, чтобы вы поверили, что эти приемы позволяют писать действительно эффективные селекты.
Никаких » and (:param1 is null or table_field=:param1)»

Парсинг и выполнение

Ради этого раздела я затеялся писать эту статью. Здесь я напишу об опыте, который приобрел, и который наверняка не нужен тому, кто не ходил на границах допустимого в Оракле. Например, все знают, что максимальная длина текстового поля в таблице — 4000, но многие ли знают, что предел для конкатенации строкового поля в селекте в оракле тоже 4000 байтов.

Все знают

Получили селект после предкомпиляции с параметрами выполнения. Он у нас в переменной l_Stmt.
К сожалению, в PL/SQL нет легкой возможности организовать цикл по полям селекта, как это можно было бы сделать в Java. Будем пользоваться процедурой dbms_sql.parse, которая возвращает поля селекта как таблицу, по которой сделаем цикл в дальнейшем.
Что мы хотим сделать?
Выполнить парсинг и получить список полей с datatype.
Для этого применяем

dbms_sql.parse

dbms_sql.describe_columns

Мы получили самое главное — список полей селекта в PL/SQL таблице l_LogColumnTblInit.
Это для нас выполнил великий пакет DBMS_SQL. Теперь мы можем организовать цикл по полям селекта.
Тот, кто пишет на Java(в том числе и я теперешний) посмеется над такой победой, там это всегда было — перебор полей в PreparedStatement.
Сейчас, зная Java, я бы написал бы, может, по другому, но принципиальные вещи не изменились бы.
Кстати, здесь я встретил ограничение на размер селекта 32К, не сразу, в ходе эксплуатации, когда начали писать серьезные селекты. И тут меня снова порадовал Оракл. Оказывается, длинный селект можно разбить на порции 256 байт, зарузить в PL/SQL таблицу l_LongSelectStmt dbms_sql.varchar2s и передать в overload версию dbms_sql.parse.

        begin
           dbms_sql.parse(l_CursorId
                         ,l_LongSelectStmt
                         ,1
                         ,l_LongSelectStmt.count
                         ,false
                         ,1);
         exception
           when others then
             v_Msg := '--After parse long 2: ' || sqlerrm;
             raise ParsingException;
         end;

Теперь пришло время подумать о форматах вывода.
Допустим, наш селект выглядит так:

  select a,b       from table1   where ...

Для вывода в формате CSV нужно написать

   select a||chr(9)||b
            from(  select a,b
                        from table1
                      where ...
                    )

Для вывода в формате HTML нужно написать

   select '<tr><td>'||a||'</td><td>'||b||'</td></tr>'
            from(   select a,b
                         from table1
                      where ...
                    )

Для создания самого красивого, но и самого сложного формата Excel Workbook, мне пришлось поэкспериментировать с Excel. Excel Workbook — это не бинарный, а текстовый файл, его можно посмотреть и понять, как там все устроено.
Там есть CSS, определения Workbook,Worksheet, заголовков таблиц. Не буду углубляться, это не очень сложно понять, если вы встречали раньше HTML.
В Excel Workbook строка вывода будет выглядеть примерно так

 select '<Row> <Cell ss:StyleID="s24"><Data ss:Type="String">'||a|| '</Data></Cell>'||
                          '<Cell ss:StyleID="s25"><Data ss:Type="DateTime">'||b||'</Data></Cell>'|| 
                         '<Cell ss:StyleID="s24"><Data ss:Type="String">'||c||'</Data></Cell></Row>'
   from ( select a,b,c
                 from table2
           )

Здесь, как вы видите, нам может пригодиться знание типов данных из виртуальной таблицы, полученной в dbms_sql.describe_columns.
Если сравнивать типы вывода, то можно сказать следующее:
CSV — маленький по размеру(это плюс), некрасивый, нет возможности нескольких таблиц(spreadshhets)
HTML — средний по размеру, достаточно сексуальный, нет возможности нескольких таблиц
Excel Workbook — большой файл, красивый, есть возможность создания нескольких таблиц

Алгоритм работы

Цикл по полям

Двигаясь по таблице выходных полей, заворачиваем очередное поле в соответствующие формату тэги или просто добавляем табуляцию(CSV). Теперь вы поняли, как я ударился об эти 4000 байтов. Мне пришлось проверять перед слиянием строк длину результата и, если она была больше 4000, то начинал новое поле вывода, примерно так:

 select '<Row> <Cell ss:StyleID="s24"><Data ss:Type="String">'||a|| '</Data></Cell>'||
                          '<Cell ss:StyleID="s25"><Data ss:Type="DateTime">'||b||'</Data></Cell>' а1,
              '<Cell ss:StyleID="s24"><Data ss:Type="String">'||c||'</Data></Cell></Row>' а2
   from ( select a,b,c
                 from table2
           )

Когда селект построен, выполняем его. Если селект большой, то его надо загрузить в dbms_sql.varchar2s таблицу и выполнить в dbms_sql. Если ваш DBA сказал, что он не потерпит литералы и требует, чтобы параметры были bind variable, то тоже нужно использовать dbms_sql с dbms_sql.bind_variable.
Иначе, если ваш селект поместился в 32К вашей varchar2 переменной l_Stmt можете открыть ref cursor:

      begin
         open l_RefCursor for l_Stmt;
       exception 
        when others then
           v_Msg := '--After open: ' || sqlerrm;
           raise ParsingException;
       end;

Цикл по курсору

Делаем fetch и пишем в utl_file. Следим за количеством строк и за величиной выводимого файла, если нужно, завершаем его(красиво, Excel корректно) и начинаем следующий.
В конце, или, если это Excel Workbook в отдельном sheet, выводим параметры, с которыми выполнен отчет.

Ну вот, наверно и все по большому счету.
Наверное теперь можно показать результат:

Если кому интересно, я могу рассказать, как я завернул этот пакет в другой, который зиповал файл, если он был большой, посылал его по мейлу как ссылку или как attachment, но главное, это определения параметров и типовой экран ввода.

Часть 2

Так получилось, что только после того, как я написал первую часть я понял, что создать Excel файл — не фокус, хотя чисто профессионально, это было достаточно трудно. Первая статья состояла из двух частей:

Примерное описание языка предварительной обработки селекта
Проблемы, которые были решены в процессе написания

Честно говоря, я думал, что вторая часть важнее, ради нее я и написал статью.
Сейчас я думаю, что тема языка обработки селекта более важна.

Я поискал в интернете и нашел, не сильно затрудняясь, 6 или 7 решений для создания Excel файла из селекта. Я не сомневаюсь, что там были решения получше моего. Но, оказывается, без языка предварительной обработки это ничего не стоит, на мой взгляд. Система, которую я выстроил во многом интуитивно, получается единственно возможной, если мы хотим заменить такую платформу как Oracle*Reports(понятно, мы говорим только о выходных файлах в различных форматах Excel).

Нужно хранить текст селектов

Необходимо заготовить текст селекта, отвечающего запросам пользователя.
Это аналогия report(rdf) файла с query, формулами и триггерами. Я писал, можете быть уверены, любой rdf можно свести к одному селекту(или в крайнем случае в «before» PL/SQL блоке реализовать логику и записать в некую таблицу и затем сделать select * from …).

Работа с параметрами

Прелесть rdf в том, что в нем есть параметры и средства видоизменения query в зависимости от значений, заданных пользователем в runtime.
Это означает, что в хранимом селекте уже прописаны параметры и есть правила(язык) что и как менять в селекте перед выполнением.

Собственно создание Excel

Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности

Единая форма, предлагающая пользователю ввести данные

Так как параметры теперь обрабатываются только перед выполнением, необходимо в некоей формализованной форме их определить. То есть задать

datatype
формат ввода
приглашение для ввода параметра пользователем
предварительные проверки
с каким result set проверять параметр
и так далее

Только все вместе это даст хороший эффект, который я наблюдал в нашей компании.Здесь примеры экранов, которые можно написать.
Это написано в Oracle Forms, но точно также можно написать и на любой другой платформе. Два экрана(Администрация и Выполнение) и два PL/SQL пакета в базе, несколько таблиц для хранения данных.
Мы практически отказались от Reports. Аналитики используют Excel, а мы по запросу строим селекты. На это может уходить много времени, и это отладка селекта, и логика и оптимизация. Но затем, в течение 20 минут определяем его в системе и пользователь начинает работать с ним. На мой взгляд, это проще, чем системы типа Business Objects или Oracle Discoverer. Да, это требует работы программиста и DBA, но зато и селекты получаются заточенные и эффективные. А по поводу Excel, как мощного аналитического средства я говорить не буду(не специалист).