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


  • 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


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


Параметры выполнения
Параметры передаем так:
  <PARAMS>
    <PARAM>
      <NAME>      </NAME>
      <DATATYPE> [ALPHANUMERIC|CHAR|DATE|NUMBER|AS_IS] </DATATYPE>
      <FORMAT_MASK>[Date format]</FORMAT_MASK>
      <PROMPT>    </PROMPT>
      <LABEL>     </LABEL>
      <VALUE>     </VALUE>
    </PARAM>
              ..
  </PARAMS>




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

Нужен некий язык, на котором можно написать инструкции, что делать в зависимости от значений параметров выполнения.
  • Получаем параметры выполнения.
  • Компилируем текст селекта.
  • Подаем его для выполнения следующему шагу.

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

В тексте селекта это выглядит как комментарий(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 и заменяю всю команду на результат выполнения.
Например
/*! VAR :Max_salary_dep number {select department_id
                                  from (select ee.department_id,
                                               sum(ee.salary)
                                          from employee ee
                   /*! IS_NOT_NULL :emp_id {where ee.employee_id = :emp_id}; !*/
                                         group  by ee.department_id
                                         order by sum(ee.salary) desc)
                                 where rownum = 1} !*/
   /*! VAR :Debug_print char    'Y' ; !*/
  select e.first_name "First Name",
         e.last_name,
         d.name "Department  name",
         j.function,e.hire_date,e.salary,e.commission
 /*! IS_NOT_NULL :loc_id {,l.regional_group}; !*/
   from department d,employee e,job j
 /*! IS_NOT_NULL :loc_id {,loc l}; !*/
  where e.department_id=d.department_id
    and e.job_id=j.job_id
 /*! IS_NOT_NULL :loc_id { and l.loc_id=d.loc_id}; !*/
 /*! IS_NOT_NULL :hire_date { and hire_date >= :hire_date}; !*/
 /*! IS_NOT_NULL :function { and j.function=upper(:function)}; !*/
 /*! IIF_EXPR {:dep_id is not null} {and d.department_id = :dep_id}
                                 {and d.department_id = :Max_salary_dep}; !*/
 /*! IS_NOT_NULL :emp_id {and employee_id = :emp_id}; !*/
 /*! ROW_SUM {Total row} F G; !*/
 /*! BOTTOM_SUM Total F G /*! IS_NOT_NULL :loc_id I ; !*/
 /*! IS_NULL :loc_id H; !*/ ; !*/ 
 



В зависимости от передаваемых значений можно получить всякие селекты
 select e.first_name "First Name",
         e.last_name,
         d.name "Department name",
         j.function,e.hire_date,e.salary,e.commission
   from department d,employee e,job j
  where e.department_id=d.department_id
    and e.job_id=j.job_id
and d.department_id = 20 


select e.first_name "First Name",
         e.last_name,
         d.name "Department  name",
         j.function,e.hire_date,e.salary,e.commission
  ,l.regional_group
   from department d,employee e,job j
  ,loc l
  where e.department_id=d.department_id
    and e.job_id=j.job_id
  and l.loc_id=d.loc_id
  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')
  and j.function=upper('SALESPERSON')
 and d.department_id = 30 

или
 select e.first_name "First Name",
         e.last_name,
         d.name "Department name",
         j.function,e.hire_date,e.salary,e.commission
   from department d,employee e,job j
  where e.department_id=d.department_id
    and e.job_id=j.job_id
  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')
  and j.function=upper('SALESPERSON')
 and d.department_id = 30 

ну и так далее…

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

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


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

Получили селект после предкомпиляции с параметрами выполнения. Он у нас в переменной l_Stmt.
К сожалению, в PL/SQL нет легкой возможности организовать цикл по полям селекта, как это можно было бы сделать в Java. Будем пользоваться процедурой dbms_sql.parse, которая возвращает поля селекта как таблицу, по которой сделаем цикл в дальнейшем.
Что мы хотим сделать?
Выполнить парсинг и получить список полей с datatype.
Для этого применяем
dbms_sql.parse
    l_CursorId := dbms_sql.open_cursor;
    begin
      dbms_sql.parse(l_CursorId, substr('select * from (' || l_Stmt || ')', 1, 32765),1);
    exception
      when others then
        v_Msg := '--After parse: ' || sqlerrm;
        put_str_to_output(substr('select * from (' || l_Stmt || ')',1,32765));
        raise ParsingException;
    end;


dbms_sql.describe_columns
    begin
      dbms_sql.describe_columns(l_CursorId, l_ColumnCnt, l_LogColumnTblInit);
    exception
      when others then
        v_Msg := '--After describe_columns: ' || sqlerrm;
        put_str_to_output(substr('select * from (' || l_Stmt || ')', 1, 32765));
        raise ParsingException;
    end;

Мы получили самое главное — список полей селекта в 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, выводим параметры, с которыми выполнен отчет.

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

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

Комментарии (11)


  1. Rupper
    24.07.2015 10:04
    +2

    Месье знает толк в извращениях…


    1. alexcrown
      24.07.2015 16:57

      У вас есть крупная ERP система в которой долго и сложно делать отчёты, но есть доступ к базе на чтение. Менеджеры хотят отчёты, которые неизвестно будут ли нужны кому-то ещё. Но при этом менеджеры не хотят учить SQL (хотя, говорят, он для них и создавался)

      Почему бы не дать им возможность получать отчёты таким образом? Если отчёт будет популярным, то его уже можно будет реализовать в системе


      1. Rupper
        24.07.2015 17:04
        +1

        У нас есть крупная ERP система, но в ней легко и быстро делать отчеты. И в ней НЕВОЗМОЖНО получить доступ на чтение из базы с клиентского места.
        Так что мы немного иным образом решили задачу. И да, выгрузить в Excel можно.


        1. jvs Автор
          24.07.2015 20:39

          доступ на чтение из базы с клиентского места. Фраза не очень понятна(как впрочем и Месье знает толк в извращениях…)
          Вам не нужен доступ на чтение, вам нужен грант на выполнение. Процедура уже в базе.


          1. Rupper
            25.07.2015 11:06
            +2

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


            1. jvs Автор
              25.07.2015 13:30
              -3

              Но я же вам не анекдот рассказал. Если бы я хотел пошутить, я бы сказал:«Однажды лошадь зашла в бар...».
              Я хотел получить комментарий по существу вопроса.
              Про доступ, я вас не понимаю. Понятно, что обращение к процедуре не может быть у всякого. Но это вы в своей системе определяете, кому ее можно вызывать, собственно, как и права на все остальные действия. Процедура только автоматизирует создание файла. Вы не должны тратить усилия на написание Excel, и поверьте написать это не так просто. А вызывайте ее хоть из Python…
              По поводу, как это может быть имплементировано с систему, можете посмотреть несколько картинок
              www.samtrest.com/rm/demo.htm
              Request manager for any forms application


  1. GlukKazan
    24.07.2015 12:36
    +1

    Эпично конечно, но где традиционный пятничный хаб «Ненормальное программирование»?


  1. Kodim
    24.07.2015 14:57
    +1

    Очень неплохо, замечательная работа. Заворачивание разовых отчетов в ексел-файлы — очень часто требуется — городить постоянный отчет а паблишере — относительно долго, а иметь инструмент для легкого и быстрого ответа аналитику на запросы, нестандартного типа — весьма полезно. А поделиться исходным кодом как-то предполагается?
    равно и дальнейшее развитие и работа с архивами представляет интерес.


  1. alexcrown
    24.07.2015 16:54

    Неплохо. Я делаю наоборот — создаю файл Excel и в него встраиваю DataSource с запросом к БД. Выборка обновляется кнопкой Обновить на закладке Данные. Из минусов — невозможно параметризовать запрос и нужен oledb провайдер от Oracle (тот что от Microsoft плохо поддерживает utf-8)


    1. jvs Автор
      24.07.2015 20:33

      Я намеренно все сделал в пакете, который находится в базе. Это как движок. Обращение к процедуре из любой аппликации, только передай параметры.
      Понятно, что пользователь не обращается к ней напрямую. В нашей системе есть форма, где администратор заносит текст селекта и определяет, какие параметры будут ему передаваться. Кстати, там можно определить и тип запроса(отчет, Excel, процедура, скрипт shell). И есть форма, где пользователь задает параметры. Похоже на concurrent manager в Oracle ERP. Для того, чтобы ввести отчет в систему после отладки селекта нужно потратить минут 10 на определение параметров.


  1. KrD
    05.08.2015 09:20

    Дело камрада Шеффера живёт.