Данная статья предназначается всем тем, кто плотно работает с Oracle Application Express (в просторечии — APEX, а то и просто апекс). А так же тем, кто что-то слышал и подумывает начать использовать его в работе. После прочтения статьи, я надеюсь, у вас прибавится желания сделать это.

Вводная информация

Предполагается, что читатель знаком (или познакомится вскоре после прочтения) хотя бы в общих чертах со следующими вещами:

Что такое SQL и PL/SQL, и чем они друг от друга отличаются
Какие объекты бывают в СУБД Oracle и зачем они нужны (таблицы, пакеты, вью)
Основные компоненты приложения апекса: страница, регион, итем, процесс и т. д., как их создавать и удалять
Основы администрирования апекса: как создать в оракле таблицу, пакет, вью и как потом сделать их доступными для своего приложения
Как использовать значение итема в качестве параметра SQL-запроса

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

Что такое плагин региона в апексе и что для него нужно

В апексе можно создавать плагины регионов, итемов, процессов, динамических действий (Dynamic action) и схем аутентификации и авторизации. Что такое плагин региона, думаю, интуитивно понятно. Вы создаете новый тип региона, который умеет делать что-то такое, что не умеют делать стандартные апексовые регионы. Часто самое сложное тут — придумать какую-нибудь действительно стоящую идею для реализации. Я в качестве примера для статьи придумал плагин для создания рубрикации, источником вдохновения послужили алфавитные и предметные указатели в книгах и эта страница на сайте htmlbook.ru. Итак, приступим.

Исходные данные

Для начала, создадим таблицы с данными. У нас будет рубрикатор стран мира, выбранных моей левой пяткой прямо перед написанием статьи. Допустим, мы храним список стран и континентов, а также ссылки на статьи в Википедии об этих странах. Нам надо аккуратно и красиво выводить этот список на экран, и чтобы они были разбиты по рубрикам (рубриками будут соответствующие континенты). Плагин будет также иметь одно кастомное свойство — количество колонок, в которых будут выводиться данные.

Код для создания таблиц и заполнения данными

create table continent (
       continent_id number primary key, 
       continent_name varchar2(100));
   
create table country (
         country_id number primary key, 
         country_name varchar2(100), 
         continent_id number references continent (continent_id),
         url varchar2(4000));

insert into continent(continent_id, continent_name) values (1, 'Europe');
insert into continent(continent_id, continent_name) values (2, 'Asia');
insert into continent(continent_id, continent_name) values (3, 'North America');
insert into continent(continent_id, continent_name) values (4, 'South America');
insert into continent(continent_id, continent_name) values (5, 'Australia');
insert into country (country_id, country_name, continent_id, url) values (1, 'France', 1, 'https://ru.wikipedia.org/wiki/%D0%A4%D1%80%D0%B0%D0%BD%D1%86%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (2, 'Greece', 1, 'https://ru.wikipedia.org/wiki/%D0%93%D1%80%D0%B5%D1%86%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (3, 'Norway', 1, 'https://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%B2%D0%B5%D0%B3%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (4, 'Spain', 1, 'https://ru.wikipedia.org/wiki/%D0%98%D1%81%D0%BF%D0%B0%D0%BD%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (5, 'China', 2, 'https://ru.wikipedia.org/wiki/%D0%9A%D0%B8%D1%82%D0%B0%D0%B9%D1%81%D0%BA%D0%B0%D1%8F_%D0%9D%D0%B0%D1%80%D0%BE%D0%B4%D0%BD%D0%B0%D1%8F_%D0%A0%D0%B5%D1%81%D0%BF%D1%83%D0%B1%D0%BB%D0%B8%D0%BA%D0%B0');
insert into country (country_id, country_name, continent_id, url) values (6, 'India', 2, 'https://ru.wikipedia.org/wiki/%D0%98%D0%BD%D0%B4%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (7, 'Japan', 2, 'https://ru.wikipedia.org/wiki/%D0%AF%D0%BF%D0%BE%D0%BD%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (8, 'USA', 3, 'https://ru.wikipedia.org/wiki/%D0%A1%D0%BE%D0%B5%D0%B4%D0%B8%D0%BD%D1%91%D0%BD%D0%BD%D1%8B%D0%B5_%D0%A8%D1%82%D0%B0%D1%82%D1%8B_%D0%90%D0%BC%D0%B5%D1%80%D0%B8%D0%BA%D0%B8');
insert into country (country_id, country_name, continent_id, url) values (9, 'Canada', 3, 'https://ru.wikipedia.org/wiki/%D0%9A%D0%B0%D0%BD%D0%B0%D0%B4%D0%B0');
insert into country (country_id, country_name, continent_id, url) values (10, 'Mexico', 3, 'https://ru.wikipedia.org/wiki/%D0%9C%D0%B5%D0%BA%D1%81%D0%B8%D0%BA%D0%B0');
insert into country (country_id, country_name, continent_id, url) values (11, 'Brasil', 4, 'https://ru.wikipedia.org/wiki/%D0%91%D1%80%D0%B0%D0%B7%D0%B8%D0%BB%D0%B8%D1%8F');
insert into country (country_id, country_name, continent_id, url) values (12, 'Uruguay', 4, 'https://ru.wikipedia.org/wiki/%D0%A3%D1%80%D1%83%D0%B3%D0%B2%D0%B0%D0%B9');
insert into country (country_id, country_name, continent_id, url) values (13, 'Chile', 4, 'https://ru.wikipedia.org/wiki/%D0%A7%D0%B8%D0%BB%D0%B8');
insert into country (country_id, country_name, continent_id, url) values (14, 'Australia', 5, 'https://ru.wikipedia.org/wiki/%D0%90%D0%B2%D1%81%D1%82%D1%80%D0%B0%D0%BB%D0%B8%D1%8F');

create view by_continent as
      select continent_name rubric, country_name value, url link
        from continent ct,
             country cr
       where ct.continent_id = cr.continent_id
       order by rubric, upper(value);

create view by_alphabet as
      select upper(substr(country_name, 1, 1)) rubric, country_name value, url link
        from country cr
       order by rubric, upper(value);

Плагин

Плагин создается в приложении апекса. Открываем IDE, выбираем необходимое приложение, идем в раздел «Shared Components», там находим раздел «Other Components», а в нем — «Plug-ins». Заходим на страницу плагинов, нажимаем «Create», запускается мастер создания плагинов. Далее по шагам (их всего два): указываем способ создания — «From Scratch», затем указываем свойства. Необходимо указать название(«Name»), внутреннее название («Internal name»), тип плагина («Type» — «Region») и тип поддерживаемых приложений («Supported for») — Desktop и Mobile (поставить галочки напротив необходимых пунктов). Далее нажимаем «Create Plug-in». Плагин создан, теперь его надо немного настроить. Заходим в свойства плагина (для этого надо кликнуть по его названию в списке плагинов), в разделе «Standard Attributes» ставим галочки напротив «Region Source is SQL Statement» и «Region Source Required». Идем в раздел «Custom Attributes», нажимаем на «Add Attribute» (чтобы добавить свойство для задания количества колонок при выводе), заполняем следующие поля:

«Scope» — «Component»
«Attribute» — «1» (порядковый номер кастомных атрибутов, ниже будет немного о том, что с этим номером потом делать)
«Display Sequence» — оставляем умолчательное значение «10»
«Label» — «Columns count» (это название будет отображаться в настройках, когда мы будем создавать регион на основе этого плагина)
«Type» — «Integer»
«Supported for» — «Desktop»

Ложка дёгтя в кастомных атрибутах. Есть там один крайне неприятный косяк: если вы захотите сделать кастомный атрибут типа «Select List», делайте его аккуратно. В интерфейсе отсутствуют кнопки для удаления отдельных записей, а также невозможно отредактировать return value. Вот скриншот этого непотребства:

Как видите, кнопки «Удалить» там нет. И поле «return value» нередактируемое. То есть если вы ошибетесь где-нибудь и не заметите сразу, или решите намного видоизменить список, то вам придется удалить атрибут и пересоздавать его с нуля. Да-да, и все ваши 20 записей для выпадающего списка тоже.

Теперь о самом главном свойстве плагина: на закладке «Callbacks» надо указать «Render Function Name» — название PL/SQL функции, которая будет генерировать HTML-код региона для показа браузером. Согласно документации, функция должна иметь следующую сигнатуру:

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

Кстати, для того, чтобы получить описание этой функции, даже не надо лезть в документацию, достаточно нажать на вопросик напротив поля «Render Function Name» и скопировать текст из встроенной подсказки. И раз уж заговорили о документации — для разработки плагинов вам очень пригодится документация на пакеты APEX_PLUGIN и APEX_PLUGIN_UTIL.
Самое загадочное в этой функции то, что мне не удалось пока найти описание возвращаемого функцией результата и где он используется. Вы можете просто возвращать NULL, и все будет работать.

Render Function

Плагин почти готов, теперь нужно сделать функцию рендеринга. Создадим функцию:

function render (
    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 is
begin

  return null;
end;

Код, генерирующий HTML, как вы уже наверно догадались, надо поместить между строками «begin» и «return null;».
Чтобы сгенерировать регион на основе результата SQL запроса пользователя, надо этот запрос получить и выполнить. Получить запрос просто: читаем описание типа apex_plugin.t_region и обнаруживаем, что текст запроса хранится в поле p_region.source. Осталось только запустить этот запрос, но не спешите хвататься за EXECUTE IMMEDIATE, потому что тут он вам не поможет! Дело в том, что пользователь в запросе может указать параметры, которые движком апекса ассоциируются со страничными полями. Движок апекса умеет их определять и заполнять данными. Сделать такое самому практически невозможно, придется писать свой парсер запросов. Как же тогда выполнить запрос? Можно запретить пользователю использовать запросы с параметрами, а можно покопаться в недрах документации и найти семейство функций APEX_PLUGIN_UTIL.GET_DATA (две функции GET_DATA и две — GET_DATA2, за подробностями — см. документацию пакета). Эти функции принимают на вход SQL-код запроса, парсят его, определяют параметры, находят соответствующие итемы на странице и т. д. Результат возвращается в виде коллекции, ее описание есть на той же странице документации. В той же переменной p_region (которая является записью — RECORD) содержатся атрибуты с названиями attribute_01… attribute_25. Эти числа соответствуют номерам кастомных атрибутов, указанных в поле «Attribute» в процессе создания.

И последнее. HTML-код вставляется на страницу с помощью процедуры htp.p (не путать с http!). То есть код

htp.p('<b>Всем привет!</b>');

Выведет на страницу надпись «Всем привет!» жирным шрифтом. Полный код функции для нашего плагина будет ниже.

Я тоже хочу попробовать!

Плагин вместе со всем необходимым можно взять тут. В комплекте идут следующие файлы:

region_type_plugin_rubrikator.sql — файл экспорта плагина
render_plugin_rubrikator.pls — заголовок пакета с функцией рендеринга
render_plugin_rubrikator body.pls — тело пакета с функцией рендеринга

Установка плагина (выполнять не нужно, если вы выполнили все шаги по созданию плагина из данной статьи): заходим в IDE, открываем нужное приложение, импортируем файл с плагином (region_type_plugin_rubrikator.sql). Указываем File Type — Plug-in, нажимаем Next, Next, Install Plug-in.

Установка пакета: компилируем пакет из файлов render_plugin_rubrikator.pls и render_plugin_rubrikator body.pls.

Пакет содержит функцию рендеринга (render) и две процедуры (prepare_demo и drop_demo), которые, соответственно, создают и удаляют таблицы и вью для демонстрации (код, выполняемый процедурой prepare_demo, приведен в начале статьи; если вы уже выполнили его, вызывать процедуру prepare_demo не нужно). Выполните процедуру prepare_demo после установки:

begin
  render_plugin_rubrikator.prepare_demo;
end;
/

Теперь создайте новую страницу, на ней создайте два региона. Тип региона укажите — Plug-ins, в списке плагинов выберите «Rubrikator». В качестве источника данных укажите:

Для первого региона:

select * from by_continent

Для второго:

select * from by_alphabet

Здесь by_continent и by_alphabet — два вью, созданных процедурой prepare_demo. В текущей версии плагина требования к запросу-источнику такие: первый столбец должен содержать заголовки рубрик, второй — пункты внутри рубрик, третий — ссылки. Если ссылка равна NULL, запись будет показана на странице без тега <а>.

Значение поля Columns count в плагине выберите на свой вкус. В моей демо-версии будет 2 для первого и 3 для второго.
Результат первого запроса, для примера:

А если запустить страницу, то наш плагин преобразует это к такому виду:

Первый регион:

Второй регион:

Ссылка на страницу.

Создаем плагин региона для Oracle Application Express