Язык SQL

8.6 Иерархические структуры в таблицах

Уже упоминалось, что таблица может хранить дерево. В таблице emp хранится иерархия, изображённая на рисунке 8.5.

увеличить изображение
Рис. 8.5. Иерархия таблиц emp

Для работы с иерархиями в SQL введены две фразы:

• фраза START WITH для выбора начальной точки внутри иерархии;
• фраза CONNECT BY PRIOR, определяющая направления движения по дереву — вниз или вверх.

Синтаксис иерархического запроса:

SELECT [LEVEL], список_столбцов_или_выражений FROM имя_таблицы [WHERE условия] [START WITH условия] [CONNECT BY PRIOR условия]

где

условие ::= выражение оператор_сравнения выражение;

Псевдостолбец LEVEL возвращает значение 1 для корня дерева, полученного запросом, 2 для узлов уровня 1 в этом дереве и т.д.

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

Пример запроса, возвращающего поддерево в направлении сверху вниз, начиная с Jones, смотрите в листинге 8.15.

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

SELECT empno, ename, job, mgr
FROM emp
START WITH empno = 7566
CONNECT BY PRIOR mgr = empno
empno ename job mgr
7566 JONES MANAGER 7839
7788 SCOTT ANALYST 7566
7876 ADAMS CLERK 7788
7902 FORD ANALYST 7566
7369 SMITH CLERK 7902

8.7 Триггеры

Триггерами называют специальные процедуры, которые напрямую не вызываются, но срабатывают при наступлении некоторых событий, называемых триггерными. В SQL триггер прикреплён к таблице и может срабатывать до и после наступления триггерного события. Существуют два вида триггеров — уровня строки и уровня таблицы. Строчные триггеры срабатывают при обработке каждой строки, а табличные только один раз при входе в таблицу или выходе из неё. Заметим, что в Cache нет табличных триггеров.

Триггер в Cache создаётся инструкцией

CREATE TRIGGER имя_триггера {BEFORE | AFTER} событие
[ORDER целое]
ON имя_таблицы
[REFERENCING  {OLD   |   NEW}   [ROW AS] alias]
тело триггера

Триггер должен иметь имя. Событие в Cache — это исполнение инструкций INSERT, DELETE или UPDATE. Действие, которое выполняет срабатывающий триггер, может выполняться до вызвавшего события (триггер BEFORE) или после него (триггер AFTER).

Событие UPDATE имеет вариант UPDATE OF. После этих ключевых слов должен быть записан список столбцов, на обновление которых реагирует триггер.

Необязательное слово ORDER, после которого стоит натуральное число, позволяет задать порядок срабатывания нескольких триггеров, построенных для одной таблицы на одно и то же событие и на одно и то же время (BEFORE или AFTER). Триггеры с меньшим порядком (ORDER) срабатывают раньше.

Необязательная фраза REFERENCING позволяет задать алиасы для старых и новых значений. Их используют в разделе "действие" и в теле триггера. Для события INSERT можно работать только с новым значением, для DELETE только со старым, а для UPDATE и со старым и с новым.

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

LANGUAGE SQL или LANGUAGE OBJECTSCRIPT

По умолчанию выбирается SQL. В теле только такого триггера допустимы фразы REFERENCING, WHEN и UPDATE OF.

Удаляются триггеры командой

DROP TRIGGER имя_триггера FROM имя_таблицы

И в заключение пример триггера, который ничего не делает, а только сообщает, что он сработал.

В SQL в области USER создаём таблицу CREATE TABLE QQ (c1 CHAR(5)) В студии пишем программу, которая создаёт триггер и вставляет строчку в QQ (листинг 8.16).

DO $SYSTEM.Security.Login("_SYSTEM","SYS")
NEW SQLCODE
&sql (CREATE TRIGGER TrigTestQQ AFTER INSERT ON SQLuser.qq LANGUAGE OBJECTSCRIPT
{W "I just fired the trigger",!}
)
W "SQLCODE создания триггера: ", SQLCODE,!
$sql(INSERT INTO SQLUser.qq VALUES ('hello'))
W "SQLCODE срабатывания триггера: ", SQLCODE,!
QUIT

Первая строка программы даёт пользователю привилегии, необходимые для работы со встроенным SQL. Тело триггера заключено в фигурные скобки.

SQLCODE — это код ошибки. Значение 0 означает успешное завершение SQL-инструкции, значение 100 —то же успешное завершение, но данные не найдены. Отрицательные значения SQLCODE это коды ошибок, которые можно найти в документации.

При первом запуске программы всё заканчивается благополучно (листинг 8.17)

USER>D ^tr
SQLCODE создания триггера: 0
I just fired the trigger
SQLCODE срабатывания триггера: 0

Триггер срабатывает, и ошибок нет. При повторном запуске программы появится ошибка —365 за счёт повторного создания триггера.

8.8 Что такое представление (VIEW)

Представления создаются инструкцией, похожей на инструкцию создания таблиц. Формат инструкции создания представления:

CREATE  [OR REPLACE]   [FORCE] VIEW имя_представления [(столбец    [, столбец])    ... ]
AS
запрос
[WITH READ ONLY]
[WITH  [LOCAL   |  CASCADED]  CHECK OPTION]

Запрос может строиться над несколькими таблицами. Фраза ORDER BY в нём не используется.

Фраза WITH READ ONLY означает, что через представление нельзя выполнять инструкции манипулирования данными. Фраза WITH [LOCAL | CASCADED] CHECK OPTION означает возможность манипулирования данными через представление. При указании LOCAL проверка ограничений целостности ведётся только для таблицы, на которой построено представление, а в варианте CASCADED ещё для связанных таблиц.

Представление — хранимый объект. Поскольку данные могут храниться только в таблицах, в базе хранится имя представления, текст запроса, образующего view, и, может быть, описания свойств представления.

При выполнении инструкции SELECT от представления по текстам этого SELECTS и запроса, хранящегося в определении view, строится результирующий запрос. Манипулирование данными через view не всегда возможно.

Пример: Запрос данных через представление. Создадим преставление над таблицей emp:

CREATE OR REPLACE VIEW view_emp AS
SELECT ename, job, sal FROM emp WHERE deptno>10

Обратимся к нему с запросом:

SELECT ename,  job FROM emp WHERE job<>'CLERK'

Поскольку данные хранятся только в таблице emp, в действительности будет выполнен такой запрос:

SELECT ename, job FROM emp
WHERE   (deptno>10)  AND (job<>'CLERK')

Как он получен? Из двух наборов столбцов, определённых представлением (ename, job, sal) и запросом (ename, job), выбрано их пересечение (ename,job). Фильтр для выбора строк определяется двумя составляющими, взятыми из определения представления (deptno>10) и из фразы WHERE в запросе (job<>'CLERK'). Поскольку они должны работать оба, соединяем их связкой AND.

Пример: Вставка данных через представление. Попытаемся ввести строку в таблицу emp через представление view_emp. Однако попытка реализации вставки

INSERT INTO view_emp
VALUES  ('СИДОРОВ',   'ANALYST', 3000)

не удастся, так как она приводит к вставке в emp строки (NULL,'СИДО-РОВ', 'ANALYST', NULL, 3000, NULL, NULL, NULL). А первый NULL на месте ключевого столбца empno недопустим.

8.9 Встроенный SQL

Расширить возможности SQL можно встраивая его в процедурные языки общего назначения. Команды SQL помещают в тело программы вмещающего языка, выделяя специальными фразами, например exec sql в языках типа С и Java. В Cache ObjectScript фразы встроенного SQL имеют формат:

&sql(фраза_sql)

Одна из основных проблем встроенных языков заключается в том, что ошибки могут быть обнаружены и во вмещающем и во встроенном языке. В стандарте SQL2 для анализа ошибок встроенного SQL используются стандартные переменные: SQLCODE (код ошибки), SQLERROR (сообщение об ошибке). В новых разработках, рекомендуется заменить их переменной SQLSTATE, состоящей из двух частей — двухсимвольного класса ошибки и трехсимвольного подкласса ошибки. В Cache для анализа ошибок встроенного SQL используется только переменная SQLCODE со стандартными значениями (0 — успех, или запись найдена, 100 — больше нет записей, число < 0 — ошибка).

8.9.1 Примеры со встроенным SQL

• Для создания таблицы пишем программу

  &sql(CREATE TABLE qq (
  c1 SMALLINT PRIMARY KEY,
  c2 VARCHAR2(10), c3 VARCHAR2(30))) write  !,"Код ошибки:   ", SQLCODE
  

• Введем две записи

  &sql(INSERT INTO qq VALUES (1,

  'QWE', 'Z')) &sql(INSERT INTO qq VALUES  (2,   'АБВГД', 'ЕЖЗ'))
  

• Выполним запрос

  &sql(SELECT * FROM qq WHERE c1=1)
  

  Результат не появился, так как его выдача на экран не предусмотрена!

• Обмен данными с вмещающим языком возможен, если использовать формат запроса SELECT ... INTO

  &sql(SELECT * INTO  :V_C1,   :V_C2, :V_C3
  FROM qq
  WHERE c1 = 1)
  write !,"Код ошибки: ", SQLCODE
  write !,"  Результат  "
  write !,V_C1_" "_V_C2_" "_V_C3