Алексей Паутов - MySQL: руководство профессионала

• Название: MySQL: руководство профессионала
• Автор: Алексей Паутов
• Жанр: Базы данных

Это не совсем книга. Просто по ходу работы и изучения пакета у меня накопилось немало заметок, которые я в конце концов собрал воедино и опубликовал с оглавлением и под единым названием. Данные заметки относятся к версиям 4 и 5 пакета MySQL. По ходу текста особо отмечены места, относящиеся к специфической версии пакета.

---

Да. http://forums.mysql.com/list.php?98.

5.6.2: Где я могу найти спецификацию ANSI SQL 2003 для сохраненных процедур?

К сожалению, официальные спецификации не свободно доступны (ANSI делает их доступными для приобретения). Однако, имеются книги, типа SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer, которые дают всесторонний краткий обзор стандарта, включая покрытие сохраненных процедур.

5.6.3: Как управлять сохраненными подпрограммами?

Лучше использовать чистую схему наименования сохраненных подпрограмм. Вы можете управлять сохраненными подпрограммами с помощью CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE], DROP [FUNCTION|PROCEDURE] и SHOW CREATE [FUNCTION|PROCEDURE]. Вы можете получать информацию относительно существующих сохраненных процедур, используя таблицу ROUTINES в базе данных INFORMATION_SCHEMA.

5.6.4: Есть ли способ просматривать все сохраненные процедуры и функции в базе данных?

Да. Для базы данных dbname используйте этот запрос к таблице INFORMATION_SCHEMA.

ROUTINES:

SELECT ROUTINE_TYPE, ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_SCHEMA='dbname';

Тело сохраненной подпрограммы может просматриваться, используя SHOW CREATE FUNCTION (для сохраненной функции) или SHOW CREATE PROCEDURE (для сохраненной процедуры).

5.6.5: Где сохраненные процедуры сохранены?

В таблице proc базы данных mysql. Однако, Вы не должны обращаться к таблицам в базе данных системы непосредственно. Вместо этого, используйте SHOW CREATE FUNCTION, чтобы получить информацию относительно сохраненных функций и SHOW CREATE PROCEDURE, чтобы получить информацию относительно сохраненных процедур.

Вы можете также сделать запрос к таблице ROUTINES в базе данных INFORMATION_SCHEMA для информации относительно этой таблицы.

5.6.6: Возможно ли группировать сохраненные процедуры или функции в пакеты?

Нет. Это не обеспечивается в MySQL 5.1.

5.6.7: Может сохраненная процедура вызывать другую сохраненную процедуру?

Да.

5.6.8: Может сохраненная процедура вызывать триггер?

Сохраненная процедура может выполнять инструкцию SQL, типа UPDATE, которая вызывает триггер.

5.6.9: Может сохраненная процедура обращаться к таблицам?

Да. Сохраненная процедура может обращаться к таблицам.

5.6.10: Может сохраненная процедура выдать ошибку прикладной программы?

В MySQL 5.1 нет. Предполагается выполнять стандартные SQL-инструкции SIGNAL и RESIGNAL в будущем.

5.6.11: Может сохраненная процедура обеспечивать обработку особых ситуаций?

MySQL осуществляет определения HANDLER согласно стандарту SQL.

5.6.12: Может сохраненная процедура в MySQL 5.1 вернуть набор результатов?

Сохраненная процедура может, а вот сохраненная функция нет. Если Вы выполняете обычный SELECT внутри сохраненной процедуры, набор результатов возвращен непосредственно пользователю. Вы должны использовать клиент-серверный протокол MySQL 4.1 (или выше), чтобы это сработало. Это означает, что например, в PHP Вы должны использовать расширение mysqli вместо mysql.

5.6.13: WITH RECOMPILE обеспечивается для сохраненных процедур?

В MySQL 5.1 нет.

5.6.14: Есть ли в MySQL аналог mod_plsql как шлюза к Apache, чтобы общаться непосредственно с сохраннеными процедурами в базе данных?

Не имеется никакого эквивалента в MySQL 5.1.

5.6.15: Я могу передавать массив как ввод сохраненной процедуре?

В MySQL 5.1 нет.

5.6.16: Я могу передавать курсор как параметр IN для сохраненной процедуры?

В MySQL 5.1 курсоры доступны только внутри сохраненных процедур.

5.6.17: Я могу возвращать курсор как параметр OUT из сохраненной процедуры?

В MySQL 5.1 курсоры доступны только внутри сохраненных процедур. Однако, если Вы не открываете курсор на SELECT, результат будет послан непосредственно пользователю. Вы можете также применить SELECT INTO в переменные.

5.6.18: Я могу распечатывать значение переменной внутри сохраненной подпрограммы для целей отладки?

Да, Вы можете делать это в сохраненной процедуре, но не в сохраненной функции. Если Вы выполняете обычный SELECT внутри сохраненной процедуры, набор результатов возвращен непосредственно пользователю. Вы должны будете использовать протокол MySQL 4.1 (или выше). В PHP Вы должны использовать расширение mysqli вместо mysql.

5.6.19: Я могу передавать или отменять транзакции внутри сохраненной процедуры?

Да. Однако, Вы не можете выполнять транзакционные операции внутри сохраненной функции.

Глава 6. Триггеры

Поддержка для триггеров включена, начиная с MySQL 5.0.2. Триггер представляет собой именованный объект базы данных, который связан с таблицей, и он будет активизирован, когда специфическое событие происходит для таблицы. Например, следующие инструкции создают таблицу и вызывают триггер INSERT. Он суммирует значения, вставленные в один из столбцов таблицы:mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

– > FOR EACH ROW SET @sum = @sum + NEW.amount;

Query OK, 0 rows affected (0.06 sec)

Эта глава описывает синтаксис для создания и удаления триггеров, показывает некоторые примеры того, как использовать их. Обсуждение ограничений на использование дано в разделе " 11.1. Ограничения на сохраненные подпрограммы и триггеры".

6.1. Синтаксис CREATE TRIGGER

CREATE [DEFINER = {user | CURRENT_USER}]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

Эта инструкция создает новый триггер. CREATE TRIGGER была добавлена в MySQL 5.0.2. В настоящее время использование требует привилегии SUPER.

Триггер становится связанным с таблицей с именем tbl_name, которое должно обратиться к постоянной таблице. Вы не можете связывать триггер с view или таблицей TEMPORARY.

Когда триггер активизирован, предложение DEFINER определяет привилегии, которые применяются, как описано ниже в этом разделе.

trigger_time задает время действия. Это может быть BEFORE или AFTER, чтобы задать, что триггер активизируется прежде или после инструкции, которая активизировала это.

trigger_event указывает вид инструкции, которая активизирует триггер. Здесь trigger_event может быть одним из следующего:

INSERT: всякий раз, когда новая строка вставлена в таблицу. Например, через команды INSERT, LOAD DATA или REPLACE.

UPDATE: всякий раз, когда строка изменяется. Например, через инструкцию UPDATE.

DELETE: всякий раз, когда строка удалена из таблицы. Например, через инструкции DELETE и REPLACE. Однако, инструкции DROP TABLE и TRUNCATE относительно таблицы НЕ активизируют триггер, потому что они не используют DELETE!

Важно понять, что trigger_event не представляет литеральный тип инструкции SQL, которая активизирует триггер, поскольку это представляет тип операции таблицы. Например, триггер INSERT активизирован не только инструкцией INSERT, но и LOAD DATA, потому что обе инструкции вставляют строки в таблицу.

Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, Вы не можете иметь два триггера BEFORE UPDATE для таблицы. Но Вы можете иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE.

trigger_stmt задает инструкцию, которая будет выполнена, когда триггер активизируется. Если Вы хотите выполнять много инструкций, используйте операторную конструкцию BEGIN … END. Это также дает возможность Вам использовать те же самые инструкции, которые являются допустимыми внутри сохраненных подпрограмм.

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

Обратите внимание: до MySQL 5.0.10 триггер не может содержать прямые ссылки к именам таблиц. С MySQL 5.0.10, Вы можете записывать имена, как показано в этом примере:

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

b4 INT DEFAULT 0);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;

DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END;

|

DELIMITER ;

INSERT INTO test3 (a3) VALUES

(NULL), (NULL), (NULL), (NULL), (NULL),

(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES

(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

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

mysql> INSERT INTO test1 VALUES