И снова здравствуйте!

Всем, кто ждал и верил в очередной пост в блоге, посвящается.
Я решил немного отойти от концепции, изложенной в названии блога, потому что как я изучал Oracle Apex (как только не изучал) — это банально, а чем все это кончилось — неизвестно (еще не кончилось). Так или иначе, поработав с апексом на полную катушку на рабочем месте (а не только дома по вечерам в качестве хобби), я понял, что апекс — простой, как три копейки, а тупо пересказывать документацию с ораклового сайта, как я делал вначале, неинтересно. За прошедшее время у меня скопилось несколько более интересных идей, о реализации которых я буду рассказывать.

И первая идея — разработка какого-нибудь хорошего плагина. Например, плагина для вывода так называемых pivot отчетов.

Что такое pivot

Этот раздел для тех, кто не знает или забыл, что такое pivot. Те, кто знает, могут пропустить и читать следующий раздел.
Pivot отчет создается в оракле с помощью ключевого слова pivot. Например, пусть у нас есть таблицы fruit и sale, заполненные данными о фруктах и их продажах по датам:

create table fruit (id number, name varchar2(20));
create table sale (fruit_id number, cost number, sale_date date);
insert into fruit (id, name) values (1, 'apple');
insert into fruit (id, name) values (2, 'orange');
insert into fruit (id, name) values (3, 'mango');
insert into sale (fruit_id, cost, sale_date) values(1, 100, to_date('01.01.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(1, 200, to_date('05.01.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(1,  50, to_date('01.02.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(2,  30, to_date('01.01.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(2,  90, to_date('12.02.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(2, 135, to_date('01.03.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(2,  55, to_date('11.01.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(3,  95, to_date('01.02.2016', 'dd.mm.yyyy'));
insert into sale (fruit_id, cost, sale_date) values(3, 115, to_date('15.03.2016', 'dd.mm.yyyy')); 

Мы можем создать вот такой запрос:

select f.name, to_char(trunc(s.sale_date, 'mm'), 'dd month yyyy') sale_date, s.cost
  from dev.fruit f, dev.sale s
 where f.id = s.fruit_id

Этот запрос вернет нам вот такие данные:

Name	Sale Date	Cost
apple	01 январь 2016	100
apple	01 январь 2016	200
apple	01 февраль 2016	50
orange	01 январь 2016	30
orange	01 февраль 2016	90
orange	01 март 2016	135
orange	01 январь 2016	55
mango	01 февраль 2016	95
mango	01 март 2016	115

Тут все просто и понятно, но иногда пользователи просят нечто странное, например, такое:

Name	январь 2016	февраль 2016	март 2016
mango		95	115
apple	300	50
orange	85	90	135

Такой запрос и называется "pivot", и строится в оракле с помощью ключевого слова pivot:

with t as (select f.name, to_char(trunc(s.sale_date, 'mm'), 'month yyyy') sale_date, s.cost
             from dev.fruit f, dev.sale s
            where f.id = s.fruit_id)
select *
  from t
 pivot (sum(cost) for sale_date in ('январь   2016', 'февраль  2016', 'март     2016'))

Подробную документацию вы можете найти на сайте оракла.

Хорошо, а при чем тут APEX?

Несомненным недостатком pivot-запросов является необходимость указывать заранее список столцбов. А это не всегда просто, так как в случае с датами, например, пользователь часто хочет задавать диапазон дат вручную, а отчет при этом не должен содержать пустых столбцов. Как вы понимаете, в апексе это очень существенная проблема, потому что генерировать запросы на лету апекс не умеет. Но, на наше счастье, компания Oracle сделала возможность создавать плагины для апекса, а я сделал остальное. Итак, прошу любить и жаловать: плагин для построения pivot-отчетов. По ссылке вы найдете github-репозиторий с плагином.

Установка и использование плагина

Пункт 0: рекомендую начать использовать git, если вы еще не начали. Далее:

1. Выполняем в командной строке команду 

git clone https://github.com/apexuser/pivot_plugin.git

2. У вас появилась папка с файлами из репозитория. На момент написания этой статьи там находятся 4 файла: render_plugin_pivot.pls и render_plugin_pivot_body.pls — PL/SQL пакет (заголовок и тело соответственно), генерирующий HTML-код для построения региона с отчетом, region_type_plugin_pivot.sql — файл экспорта плагина и demo.sql — демонстрационные SQL-запросы, служащие источником данных для отчета.
3. Подключаемся к БД и создаем там пакет render_plugin_pivot (для этого выполняем скрипты из файлов render_plugin_pivot.pls и render_plugin_pivot_body.pls).
4. Выполняем скрипт:

   begin
     render_plugin_pivot.create_demo;
   end;
   /

   Этот скрипт создает демотаблицы с данными (те самые таблицы и данные, что и в начале этой статьи).
5. Заходим в IDE Oracle Application Express и создаем тестовое приложение (здесь и далее предполагается, что читатель знаком в общих чертах с IDE, способен самостоятельно найти на странице надписи, которые я буду упоминать, и более-менее в состоянии создать приложение, страницу, регион, кнопку, поле для ввода и т. д. — короче, предполагается, что читатель не тупой).
6. Кликаем на "Shared Components", затем — на "Plug-ins" (это в разделе "Other Components").
7. На странице плагинов нажимаем кнопку "Import >", указываем в качестве источника файл region_type_plugin_pivot.sql, нажимаем "Next", "Next", "Install Plug-in".
8. Возвращаемся к приложению ("Application Builder" в верхнем меню, потом выбираем нужное приложение). Создаем в новом приложении пустую страницу.
9. На странице нажимаем кнопку создания нового региона, после чего запускается мастер создания региона. На первом шаге выбираем тип — "Plug-ins", далее выбираем "pivot plug-in", затем задаем имя региона, затем — задаем источник: тип — "SQL query", текст запроса — выбираем любой из двух из файла demo.sql (например, первый).
10. Предыдущие шаги хорошо известны тем, кто достаточно много работает с апексом, там все стандартно. На следующем шаге мастера надо будет задать кастомные свойства плагина, на них я остановлюсь подробнее. "Aggregate function type" — тип агрегатной функции, которая будет использоваться для создания отчета. На момент написания этого текста там присутствует 4 опции: Sum, Count, Average и Сoncatenation (стандартные функции оракла sum, count, avg и listagg соответственно). Для начала, выберем Sum. Список легко расширить, и в будущем я обязательно это сделаю. "Max categories count" — максимальное количество значений ("категорий"), которые станут заголовками столбцов (чтобы не перегружать отчет, если их станет слишком много). Оставьте поле пустым, если нужно вывести все значения. "Sort categories by" — порядок сортировки столбцов категорий. В настоящее время можно сортировать по возрастанию или убыванию заголовков столбцов категорий или не сортировать вообще.
11. Теперь можно запустить страницу и увидеть результат. Вот он:

Так же мы без труда можем сделать наоборот, чтобы категориями стали названия фруктов. Берем второй запрос из файла demo.sql:

select f.name category, to_char(trunc(s.sale_date, 'mm'), 'month yyyy') sale_date, s.cost value
from fruit f, sale s
where f.id = s.fruit_id

И получаем:

Как это работает?

Два основных момента. Первый: запрос — источник данных для региона должен содержать 2 поля: category и value. Данные, которые результат запроса будет содержать в поле category, станут заголовками столбцов, а данные из поля value станут основой для расчета. Если посмотрите внимательнее на запросы в файле demo.sql, то все, чем они отличаются, — это алиасы столбцов.
 Второй: если вы зайдете в свойства плагина ("Shared Components" -> "Plug-ins" -> "Pivot plug-in", закладка "Callbacks"), то увидите там свойство "Render Function Name". Там должно быть "render_plugin_pivot.render" — это функция из пакета, который мы установили в п. 3. Убедитесь, что пакет установлен в нужную схему (основную схему вашего воркспейса).

И все-таки, как это работает?

Пара слов о том, как сделать плагин. Плагин, как и все в апексе, сделать просто. Допустим, мы делаем плагин для региона. На странице плагинов ("Shared Components" -> "Plug-ins") нажимаем кнопку "Create", выбираем вариант "From Scratch" или "As a Copy of an Existing Plug-in", а далее надо указать 4 главных свойства: название, внутреннее название, тип и указать "Render Function Name" — PL/SQL функцию, которая будет генерировать HTML код региона. У поля "Render Function Name" есть всплывающая подсказка, которая содержит описание сигнатур функций для плагинов всех типов. Для плагина типа "region" это будет:

function <name of function> (
    p_region              in apex_plugin.t_region,
    p_plugin              in apex_plugin.t_plugin,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_region_render_result

Входящий параметр p_region содержит свойства региона, указанные при разработке региона. Обратите внимание, что вывод HTML-кода осуществляется не через возвращаемое функцией значение (как может быть было бы логичнее), а с помощью процедуры htp.p.  Так же вам пригодится документация по пакетам APEX_PLUGIN и APEX_PLUGIN_UTIL.
Теперь и вы можете создать больше плагинов, хороших и разных!

Всех дочитавших досюда поздравляю с наступающим Новым годом, Рождеством, Старым Новым годом и прошедшим католическим Рождеством!