Отображения

Отображения представляют собой “виртуальные таблицы”, и позволяют сохранять запросы на выборку непосредственно в СУБД.

Общий синтаксис:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Отображение фиксируется при создании. Скажем, если select_statement содержит *, колонки, добавленные после создания отображения, не попадают в выборку по отображению.

В некоторых случаях, возможна вставка и обновление отображения, как если бы это была таблица. Не особо вдаваясь в подробности, обычно это возможно при наличии соответствия 1:1 между отображением и нижележащей таблицей. Другими словами, если запрос оперирует одной таблицей и не содержит агрегатных функций, вставка возможна.

Можно ограничить данные, которые можно вставить или обновить, указав WITH CHECK OPTION (по умолчанию CASCADED). В таком случае, невозможно изменить/добавить данные так, что они будут недоступны из отображения.

Более точно, запрос не может содержать:

Агрегатные функции
DISTINCT
GROUP BY
HAVING
UNION [ALL]
Подзапросы в списке сразу после SELECT (fails for INSERT, okay for UPDATE, DELETE)
Некоторые соединения (see additional join discussion later in this section)
Другие необновляемые отображения
Подзапросы в WHERE, ссылающиеся на ту же таблицу, что и FROM
Не использует таблиц
ALGORITHM = TEMPTABLE
Несколько использований колонки базовой таблицы (fails for INSERT, okay for UPDATE, DELETE)

Отображение с соединениями (т.е. многотабличное) является обновляемым при условии, что обновляется только одна таблица, и используются внутренние соединения (INNER JOIN).

Многотабличные отображения, как правило, не поддерживают INSERT.

Хранимые процедуры

Хранимые процедуры могут использоваться для сохранения некоторых SQL-выражений непосредственно в СУБД.

Существует два типа хранимых процедур:

Собственно, процедуры
Функции

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

Общий синтаксис:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([[ IN | OUT | INOUT ] param_name type, ...])
    [ COMMENT 'string'
    | LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }]
    ...

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([param_name type, ...])
    RETURNS type
    [ COMMENT 'string'
    | LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }]
    ...

DETERMINISTIC говорит оптимизатору, что вызов функции/процедуры с одинаковыми аргументами всегда приводит к одинаковому результату.

NOT DETERMINISTIC установлен по умолчанию, и говорит об обратном.

CONTAINS SQL устанавливается по умолчанию и означает, что процедура/функция использует выражения SQL, но не работает с данными.

NO SQL означает, что тело процедуры не содержит SQL-выражений.

READS SQL DATA означает, что процедура читает данные (обычно, если содержит выражение SELECT или аналогичное)

MODIFIES SQL DATA – что процедура изменяет данные (напр, через INSERT INTO или UPDATE)

Параметры процедур по умолчанию IN. Параметры функций всегда IN. OUT и INOUT параметры доступны в вызывающем контексте.

Вызов функций осуществляется так же, как вызов встроенных функций, т.е. как часть выражения.

Вызов хранимых процедур осуществляется с помощью выражения CALL sp_name[(...)].

Триггеры

Триггеры – это процедуры, которые срабатывают при определенных событиях.

Общий синтаксис:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name]
    ...

Триггеры создаются на таблицах. События – это вставка, обновление или удаление.

В теле триггера можно использовать выражения NEW.col_name и OLD.col_name для обращения к новым и старым значениям соответственно.

Можно прервать выполнение текущей операции, например, так:

SIGNAL SQLSTATE '45000' SET message_text = msg;

45000 соответствует пользовательскому исключению.

Тело хранимой процедуры

Тело хранимой процедуры может состоять из нескольких выражений SQL, и процедурных расширений.

Для этого, конструкция заключается в блок BEGIN ... END

Синтаксис:

[begin_label:] BEGIN
    [statement_list]
END [end_label]

Внутри выражения BEGIN ... END используется разделитель ;. Дабы избежать конфликтов при определении таких выражений, рекомендуется переопределить разделитель при помощи команды DELIMITER $$, использовать ; внутри тела процедуры, и завершить определение процедуры символами $$.

Определения локальных переменных

DECLARE var_name [, var_name] ... type [DEFAULT value]

Управление потоком исполнения

`CASE`

Немного отличается от ранее рассмотренного выражения CASE

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

В случае отсутствия совпадений, генерируется ошибка. Чтобы этого избежать, рекомендуется использовать конструкцию ELSE BEGIN END;

`IF`

Отличается от функции IF.

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

`ITERATE`

Аналог continue

ITERATE label

`LEAVE`

Аналог break

LEAVE label

`LOOP`

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

`REPEAT`

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

statement_list выполняется, пока search_condition ложно. Условие проверяется после первого выполнения цикла, т.е. statement_list выполняется хотя бы один раз.

`WHILE`

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

statement_list выполняется, пока search_condition истинно

`RETURN`

Работает только в функциях

RETURN expr

Курсоры

Курсоры позволяют производить итерации по результатам запросов на выборку.

Объявление

DECLARE cursor_name CURSOR FOR select_statement

Курсоры должны быть объявлены после переменных, но до обработчиков исключений (см. ниже).

Использование

Перед первым использованием, курсор должен быть открыт с помощью выражения

OPEN cursor_name;

после последнего использования, он должен быть закрыт

CLOSE cursor_name;

Так же можно получить одну запись и сохранить ее в локальных переменных:

FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...

Количество получаемых колонок должно совпадать с количеством переменных, перечисленных в выражении FETCH.

Если записей больше нет, будет брошено исключение No Data (SQLSTATE ‘02000’), так же известное как NOT FOUND.

Пример

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

Обработчики исключений

Возможно добавить обработчики исключений. Для этого существует два механизма: состояния и обработчики

Состояния

Состояния объявляются после курсоров, но до обработчиков, и по сути позволяют задать человекочитаемые названия для кодов ошибок MySQL.

DECLARE condition_name CONDITION FOR condition_value

condition_value может представлять собой внутренний код ошибки MySQL (число) или SQLSTATE value, где value – строка из 5 символов, код состояния SQL.

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

Использования имен для состояний значительно повышает читабельность кода.

Состояния, которые используются с выражениями SIGNAL или RESIGNAL должны быть объявлены с кодами SQLSTATE, а не с внутренними кодами ошибок MySQL.

Обработчики

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

Выражение DECLARE ... HANDLER устанавливает обработчик одного или нескольких состояний. Если состояние достигается, выполняется указанное выражение statement, которое может быть простым выражением, скажем, SET var_name = value, или блоком BEGIN ... END;

Объявления обработчиков должны быть в конце списка объявлений.

После выполнения statement возможно одно из трех действий, определяемое через handler_action:

CONTINUE – выполнение текущей программы продолжается с выражения, следующего за создавшим исключение
EXIT – завершается выполнение блока BEGIN ... END, в котором объявлен данный обработчик.
UNDO – отменяет результаты выполнения блока BEGIN ... END, в котором объявлен данный обработчик. Не поддерживается

Конкретное состояние condition_value может быть указано как код ошибки MySQL, код SQLSTATE, название ранее именованного состояния, или одно из трех выражений:

SQLWARNING – соответствует значениям SQLSTATE, начинающимся на ‘01’, т.е. предупреждениям SQL
NOT FOUND – соответствует значениям SQLSTATE, начинающимся на ‘02’, то есть в случаях, когда курсор не может получить очередное значение.
SQLEXCEPTION – соответствует SQLSTATE, не начинающимся на ‘00’, ‘01’ и ‘02’ – то есть, всем прочим ошибкам.

Если возникает состояние, для которого не объявлен обработчик, результат зависит от типа состояния.

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

В случае SQLWARNING подпрограмма продолжает работать, как если бы использовался обработчик CONTINUE.

В случае NOT FOUND, если состояние возникло в результате работы с курсором, действие – CONTINUE, если же оно создано с помощью SIGNAL или RESIGNAL, то действие – EXIT

Для игнорирования состояния, можно использовать такую конструкцию:

DECLARE CONTINUE HANDLER FOR condition BEGIN END;

Внутри обработчиков не могут использоваться конструкции ITERATE или LEAVE.

SIGNAL, RESIGNAL

Аналог throw.

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name

signal_information_item:
    condition_information_item_name = simple_value_specification

condition_information_item_name:
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME

condition_name – это название состояния, объявленого ранее с помощью DECLARE ... CONDITION.

SET ... позволяет установить дополнительные диагностические сообщения. Типы соответствующих переменных указаны в таблице:

Item Name	Definition
CLASS_ORIGIN	VARCHAR(64)
SUBCLASS_ORIGIN	VARCHAR(64)
CONSTRAINT_CATALOG	VARCHAR(64)
CONSTRAINT_SCHEMA	VARCHAR(64)
CONSTRAINT_NAME	VARCHAR(64)
CATALOG_NAME	VARCHAR(64)
SCHEMA_NAME	VARCHAR(64)
TABLE_NAME	VARCHAR(64)
COLUMN_NAME	VARCHAR(64)
CURSOR_NAME	VARCHAR(64)
MESSAGE_TEXT	VARCHAR(128)
MYSQL_ERRNO	SMALLINT UNSIGNED

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

Триггеры, отображения и хранимые процедуры.

Отображения

Хранимые процедуры

Триггеры

Тело хранимой процедуры

Определения локальных переменных

Управление потоком исполнения

CASE

IF

ITERATE

LEAVE

LOOP

REPEAT

WHILE

RETURN

Курсоры

Объявление

Использование

Пример

Обработчики исключений

Состояния

Обработчики

SIGNAL, RESIGNAL

`CASE`

`IF`

`ITERATE`

`LEAVE`

`LOOP`

`REPEAT`

`WHILE`

`RETURN`