Блоки. Создание реляционных отношений. Использование свойств ORDER BY и WHERE Clause

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

увеличить изображение
Рис. 8.11. Мастер блока данных. Создание отношений

В появившемся окне вы можете создавать или удалять отношения между блоками данных. Это окно будет появляться всякий раз при создании нового блока, если в вашем модуле имеется хотя бы один блок. Окно имеет три управляющих элемента:

• кнопка "Создать отношение";
• флажок "Автоматическое сединение блоков";
• кнопка "Удалить отношение".

Для создания нового отношения нажмите кнопку "Создать отношение". На экране появится диалоговое окно с вариантами создания соединения ( рис. 8.12).

Рис. 8.12. Тип отношения

В нашем примере будет рассмотрен вариант создания отношения из условия соединения. Для этого установите значение переключателя на первый вариант – "создать новое отношение из условия соединения" – и подтвердите выбор. В появившемся окне ( рис. 8.13) выберите блок, который будет мастер для блока PPZAK, то есть блок PZAK, и нажмите кнопку "ОК".

Рис. 8.13. Перечень блоков модуля

После выбора главного блока в первоначальном окне появятся дополнительные элементы управления для создания отношения ( рис. 8.14):

• подчиненный элемент;
• главный элемент.

увеличить изображение
Рис. 8.14. Мастер блока данных. Создание условия соединения

В качестве подчиненного элемента таблицы PPZAK выберем элемент FK_PZAK, в качестве главного элемента выберем элемент PR_KEY "мастер" таблицы PZAK. Таким образом, одной записи таблицы PZAK будет соответствовать одна или более записей таблицы PPZAK. После того как будут выбраны главный и подчиненный элементы, в списке "Условие соединения" появится условие соединения:

PPZAK.FK_PZAK = PZAK.PR_KEY

Отношение создано, нажимаем кнопку "Далее" для вызова "мастера макетов". Используя "мастер макетов", расположите элементы блока PPZAK на канве КАРТИНКА 2, на которой уже расположены элементы первого блока:

1. Выберите канву, на которой расположены элементы первого блока – "КАРТИНКА 2", – и нажмите кнопку "Далее".
2. Из списка имеющихся элементов выберите все кроме PR_KEY и FK_PZAK. После того как вы перенесете все элементы в список "Выводимые элементы", выберите тип элемента ( рис. 8.14), соответствующий типу, который указан в таблице PPZAK, и нажмите кнопку "Далее".
3. Стиль размещения выберите "Форма".
4. Подпишите элементы, установите для них значение ширины и высоты и нажмите кнопку "Готово".

Использование свойств блока ORDER BY и WHERE Clause

Сортировка и извлечение записей, удовлетворяющих определенному критерию поиска, являются наиболее часто выполняемыми нами операциями. В этом разделе вы узнаете, как устанавливать значения свойств ORDER BY и WHERE Clause программно и на этапе проектирования.

Свойство ORDER BY

ORDER BY определяет значение одноименной конструкции SQL, добавляемой к команде SELECT по умолчанию и относящейся к блоку. То есть значение этого свойства определяет порядок сортировки записей в блоке. Рассмотрим случай, когда вам необходимо, чтобы выбранные вами записи были отсортированы по какому-либо признаку. Если вы пишете запрос в SQL*Plus или в любой другой среде, это будет выглядеть следующим образом:

Select * from table_name ORDER BY col_name asc (desc)

Одним словом, чтобы отсортировать записи в таблице, вы просто добавите конструкцию ORDER BY с условием сортировки. В Oracle Forms так нельзя, поскольку все базовые блоки строятся на основе запроса вида select * from table_name, исключением являются лишь представления, в которых вы можете заранее отсортировать записи. Поэтому чтобы добавить условие сортировки к вашему блоку, вам нужно установить значение свойства "Фраза ORDER BY". Значение этого свойства можно установить двумя способами:

• в режиме проектирования формы, определив значение этого свойства;
• в режиме исполнения, то есть меняя порядок сортировки по какому-либо событию или условию.

Рассмотрим первый способ, для этого выполним пример.

1. Откройте форму ZAK.fmb.
2. В объектном навигаторе выберите блок PZAK. Откройте палитру свойств и перейдите к свойству "Фраза ORDER BY" в узле "База данных".
3. Для того чтобы отсортировать записи в блоке по дате заявки в порядке убывания, установите значение свойства равным PDAT desc.
4. Запустите форму и выполните запрос.

Как видите, все записи отсортировались так, как мы задали в свойстве. В качестве критерия сортировки в блоке вы также можете задавать не только имена столбцов, но и параметры, глобальные переменные и подзапросы. Например, не будет ошибкой, если вы установите свойство ORDER BY в:

1. :global.p_dat asc;
2. :parameter.p_dat desc;
3. (select pdat from pzak) desc;
4. pdat desc, nfirm asc.

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

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

Теперь давайте рассмотрим второй способ, в котором условие сортировки можно изменять в ходе выполнения программы в зависимости от требований пользователя. Этот подход является более гибким в отличие от предыдущего, так как условие сортировки можно отключить или поменять в любой момент. В Oracle Forms можно программно влиять практически на все установки блока с помощью процедуры SET_BLOCK_PROPERTY. У этой процедуры достаточно много вариантов описаний, но нам пригодятся только два.

Синтаксис

SET_BLOCK_PROPERTY
    (block_id Block,
     property VARCHAR,
        value VARCHAR);

SET_BLOCK_PROPERTY
    (block_name VARCHAR2,
     property VARCHAR,
        value VARCHAR);

Параметры

block_id – уникальный идентификатор, который Forms Builder связывает с элементом при создании. Функция поиска идентификатора – FIND_Block.

block_name – имя блока, свойство которого вы хотите установить. Тип данных – Varchar2.

property – определяет свойство блока, которое вы устанавливаете; в данном случае это свойство "Фраза ORDER BY".

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

1. Откройте форму Zak.fmb.
2. В объектном навигаторе выберите блок PZAK. Откройте палитру свойств и перейдите к свойству "Фраза ORDER BY" в узле "База данных". Если свойство заполнено, то отчистите его.
3. Перейдите в объектный навигатор и создайте триггер PRE-QUERY на уровне блока PZAK. В триггере напишите и скомпилируйте следующий код:

   SET_BLOCK_PROPERTY ('PZAK', ORDER_BY, 'pdat desc');
4. Запустите форму и выполните запрос.

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

В форме PZAK.fmb удалите триггер PRE_QUERY, а вместо него создайте триггер на уровне блока WHEN_NEW_ITEM_INSTANCE. В теле триггера напишите следующий код:

declare
cr_item varchar2 (20):=:system.current_item;
begin
  : global.sort_param:= cr_item;
  set_block_property ('block_name', ORDER_BY, cr_item ||' asc');
  end;

В этом триггере мы объявили глобальную константу global.sort_param – она будет запоминать имя текущего элемента при перемещении, по которому мы будем производить сортировку. Разместите на форме новую кнопку, по нажатию которой будет происходить сортировка. Создайте событие WHEN_BUTTON_PRESSED кнопки и в теле триггера напишите следующий код:

go_block ('block_name');
clear_block;
execute_query;
  go_item (:global.sort_param);

Запустите форму и выполните запрос. Для того чтобы отсортировать записи в блоке по какому-нибудь столбцу, достаточно будет перенести фокус курсора в нужное поле и нажать кнопку. Можете переходить на любой элемент и сортировать по нему остальные, причем заметьте, что после извлечения записи курсор остается в том же поле, а не перемещается на исходный элемент благодаря тому, что мы после извлечения записей перемещаем курсор обратно в поле, имя которого запоминаем в глобальной переменной. Если же мы закомментируем последнюю строчку триггера, то после выполнения сортировки курсор всегда будет возвращаться на начальный элемент.

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

Свойство WHERE Clause

Выборка записей, удовлетворяющих определенному критерию, является наиболее часто выполняемой нами операцией. В этом разделе вы узнаете, как устанавливать значения свойства WHERE Clause программно и на этапе проектирования.

Конструкция WHERE Clause позволяет ограничить выборку записей или задать определенный критерий выборки.

Свойство WHERE Clause определяет значение одноименной конструкции SQL, добавляемой к команде SELECT по умолчанию и относящейся к блоку. Это свойство аналогично одноименной конструкции в SQL, поэтому все ограничения и правила, применимые к этой конструкции, имеет точно такое же отношение и к фразе WHERE Clause в Oracle Forms.

Рассмотрим случай, когда вам необходимо, к примеру, выбрать все заказы за 2007 год. Если вы пишете запрос в SQL*Plus или в любой другой среде, то вы просто допишете условие WHERE к вашему запросу:

Select * from pzak WHERE to_char (pdat, 'YY') ='2007'

В результате этого запроса вы получите все заявки за 2007 год. В Oracle Forms вы можете получить такой же результат. Для того чтобы добавить условие выборки к вашему блоку, вам нужно установить значение свойства "Фраза WHERE Clause". Значение этого свойства, как и в случае с ORDER BY, можно установить двумя способами:

• в режиме проектирования формы, определив значение этого свойства;
• в режиме исполнения, то есть меняя порядок сортировки по какому-либо событию или условию.

Рассмотрим первый способ, для этого выполним пример.

1. Откройте форму ZAK.fmb.
2. В объектном навигаторе выберите блок PZAK. Откройте палитру свойств и перейдите к свойству "Фраза WHERE Clause" в узле "База данных".
3. В значении свойства напишите условие отбора: to_char(pdat,'YY')='2007'.
4. Запустите форму и выполните запрос.

В результате выполнения запроса мы получаем требуемый набор данных, то есть все заявки за 2007 год. В свойстве "Фраза WHERE Clause" вы можете задавать различные условия выборки данных с помощью предикатов, булевых операторов и коррелируемых подзапросов. В качестве операндов можно указывать параметры и глобальные переменные. Например, не будет ошибкой, если вы установите свойство "Фраза WHERE Clause":

1. pdat=:global.p_dat;
2. pdat=:parameter.p_dat;
3. pdat BETWEEN '2006' AND to_char(sysdate, 'YY');
4. to_char(pdat, 'YY')='2007' OR to_char(pdat, 'YY')='2006'.

Примечание: когда вы пишете условие, в свойстве "Фраза WHERE Clause" ключевое слово WHERE опускается. Также не забудьте, что в конце вашего условия символ ";" опускается.

В Oracle Forms пользователи могут в режиме ввода запроса (Enter Query mode) указать в любом из полей значение, которое будет являться критерием отбора записей при выполнении запроса. После того как пользователь получил желаемую выборку данных, он продолжает выполнять какие-либо действия, например, операцию модификации данных. Пользователь зафиксировал данные и теперь ему необходимо запросить их еще раз, – чтобы вновь получить этот набор, придется проделать ту же операцию, что и в начале. Чтобы этого не делать, пользователь может в интерактивном режиме выполнить последовательно команды:

ENTER_QUERY (<F7>) ENTER_QUERY (<F7>) EXECUTE_QUERY (<F8>)

Если попытаться выполнить эти действия программно, к примеру, по нажатии кнопки, то желаемый результат не будет достигнут. Создайте кнопку и событие кнопки WHEN-BUTTON-PRESSED, в теле которого напишите последовательность команд:

ENTER_QUERY;
ENTER_QUERY;
EXECUTE_QUERY;

После нажатия кнопки форма всего лишь перейдет в режим ввода запроса. Ниже приведен пример, который позволяет выполнить последний запрос в блоке программно.

1. Откройте форму Zak.fmb. Проверьте, чтобы свойство "Фраза WHERE Clause" блока PZAK было пустым.
2. Создайте триггер KEY-ENTQRY на уровне блока. В теле триггера напишите следующий код:

   SET_BLOCK_PROPERTY ('items', DEFAULT_WHERE, '');
   Enter_Query;
3. Создайте триггер PRE-QUERY на уровне блока. В теле триггера напишите следующий код:

   Declare
     where_cl Varchar2 (1000);
   Begin
     where_cl:= UPPER (get_block_property ('pzak', last_query));
     where_cl:= SUBSTR (where_cl, INSTR(where_cl, 'WHERE') + 6);
     SET_BLOCK_PROPERTY ('pzak', DEFAULT_WHERE, where_cl);
   End;
4. Запустите форму на выполнение. Введите форму в режим запроса и введите в поле NFIRM любое существующее значение, после чего выполните запрос. Измените какую-либо запись и запросите данные еще раз.

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