Хранимые процедуры и триггеры. Функции MySQL

Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
``Сначала прочти все, а потом пробуй примеры"

Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Концептуальный уровень можно также повысить за счет создания на сервере библиотеки функций.

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

Хранимые программы (процедуры и функции) поддерживаются в MySQL 5.0. Хранимые процедуры - набор SQL -выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.

Это может быть полезным тогда, когда:

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

Хранимые процедуры и функции (подпрограммы) могут обеспечить лучшую производительность потому, что меньше информации требуется для пересылки между клиентом и сервером. Выбор увеличивает нагрузку на сервер БД, но снижает затраты на стороне клиента. Используйте это, если много клиентских машин (таких как Веб-серверы) обслуживаются одной или несколькими БД.

Хранимые подпрограммы также позволяют вам использовать библиотеки функций, хранимые в БД сервера. Эта возможность представлена для многих современных языков программирования, которые позволяют вызывать их непосредственно (например, используя классы).

MySQL следует в синтаксисе за SQL:2003 для хранимых процедур, который уже используется в IBM"s DB2.

От слов к делу…

При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc

Начиная с MySQL 5.0.3 требуются следующие привилегии:

CREATE ROUTINE для создания хранимых процедур

ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции)

EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.

Синтаксис хранимых процедур и функций

Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION . Хранимая подпрограмма вызывается, используя выражение CALL , причем только возвращающие значение переменные используются в качестве выходных. Функция может быть вызвана подобно любой другой функции и может возвращать скалярную величину. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.

Начиная с MySQL 5.0.1, загруженная процедура или функция связана с конкретной базой данных. Это имеет несколько смыслов:

  • Когда подпрограмма вызывается, то подразумевается, что надо произвести вызов USE db_name (и отменить использование базы, когда подпрограмма завершилась, и база больше не потребуется)
  • Вы можете квалифицировать обычные имена с именем базы данных. Это может быть использовано, чтобы ссылаться на подпрограмму, которая - не в текущей базе данных. Например, для выполнения хранимой процедуры p или функции f которые связаны с БД test , вы можете сказать интерпретатору команд так: CALL test.p() или test.f() .
  • Когда база данных удалена, все загруженные подпрограммы связанные с ней тоже удаляются. В MySQL 5.0.0, загруженные подпрограммы - глобальные и не связанны с базой данных. Они наследуют по умолчанию базу данных из вызывающего оператора. Если USE db_name выполнено в пределах подпрограммы, оригинальная текущая БД будет восстановлена после выхода из подпрограммы (Например текущая БД db_11 , делаем вызов подпрограммы, использующей db_22 , после выхода из подпрограммы остается текущей db_11)

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

CREATE PROCEDURE - создать хранимую процедуру.

CREATE FUNCTION - создать хранимую функцию.

Синтаксис:

CREATE PROCEDURE имя_процедуры ([параметр_процедуры[,...]])
[характеристёика...] тело_подпрограммы

CREATE FUNCTION имя_функции ([параметр_функции[,...]])
RETURNS тип
[характеристика...] тело_подпрограммы

параметр_процедуры:
[ IN | OUT | INOUT ] имя_параметра тип
параметр_функции:
имя_параметра тип

тип:
Любой тип данных MySQL

характеристика:
LANGUAGE SQL
| DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"

тело_подпрограммы:
Правильное SQL выражение.

Рассмотрим все на практике.

Сначала создадим хранимую процедуру следующим запросом:

CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
BEGIN
select val1+val2 into "t" from `my` LIMIT 0,1;
END;

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

После этого вызовем ее:

CALL my_proc(@a);
SELECT @a;

Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)

Вот еще один пример с учетом всех требований.

Mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //

mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Весь процесс можно пронаблюдать на рисунке ниже:

Триггеры

Поддержка триггеров появилась в 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)

Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.

Замечание . Если значение переменной не инициализировано, то триггер работать не будет!

Синтаксис создания триггера

CREATE

TRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера

Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.

время_триггера

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

событие_срабатывания_триггера

Здесь все проще. Тут четко обозначается, при каком событии выполняется триггер.

  • INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE)
  • UPDATE: когда сущность (строка) модифицирована
  • DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE)

6.3.3.2. Математические функции

Все математические функции в случае ошибки возвращают NULL .

    Унарный минус. Изменяет знак аргумента:

    Mysql> SELECT - 2; -> -2

    Необходимо учитывать, что если этот оператор используется с данными типа BIGINT , возвращаемое значение также будет иметь тип BIGINT ! Это означает, что следует избегать использования оператора для целых чисел, которые могут иметь величину -2^63 !

    Возвращает абсолютное значение величины X:

    Mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32

    Возвращает знак аргумента в виде -1 , 0 или 1 , в зависимости от того, является ли X отрицательным, нулем или положительным:

    Mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1

    Значение по модулю (подобно оператору % в C). Возвращает остаток от деления N на M:

    Mysql> SELECT MOD(234, 10); -> 4 mysql> SELECT 253 % 7; -> 1 mysql> SELECT MOD(29,9); -> 2

    Эту функцию можно уверенно применять для величин типа BIGINT .

    Возвращает наибольшее целое число, не превышающее X:

    Mysql> SELECT FLOOR(1.23); -> 1 mysql> SELECT FLOOR(-1.23); -> -2

    Возвращает наименьшее целое число, не меньшее, чем X:

    Mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1

    Следует учитывать, что возвращаемая величина преобразуется в BIGINT !

    Возвращает аргумент X , округленный до ближайшего целого числа:

    Mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2

    Следует учитывать, что поведение функции ROUND() при значении аргумента, равном середине между двумя целыми числами, зависит от конкретной реализации библиотеки C. Округление может выполняться: к ближайшему четному числу, всегда к ближайшему большему, всегда к ближайшему меньшему, всегда быть направленным к нулю. Чтобы округление всегда происходило только в одном направлении, необходимо использовать вместо данной хорошо определенные функции, такие как TRUNCATE() или FLOOR() .

    Возвращает аргумент X , округленный до числа с D десятичными знаками. Если D равно 0 , результат будет представлен без десятичного знака или дробной части:

    Mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1

    Возвращает значение e (основа натуральных логарифмов), возведенное в степень X:

    Mysql> SELECT EXP(2); -> 7.389056 mysql> SELECT EXP(-2); -> 0.135335

    Возвращает натуральный логарифм числа X:

    Mysql> SELECT LN(2); -> 0.693147 mysql> SELECT LN(-2); -> NULL

    Эта функция появилась в MySQL 4.0.3. Это синоним LOG(X) .

    Если вызывается с одним параметром, возвращает натуральный логарифм числа X:

    Mysql> SELECT LOG(2); -> 0.693147 mysql> SELECT LOG(-2); -> NULL

    Если вызывается с двумя параметрами, функция возвращает логарифм числа X по базе B:

    Mysql> SELECT LOG(2,65536); -> 16.000000 mysql> SELECT LOG(1,100); -> NULL

    Опция указания базы логарифма появилась в MySQL 4.0.3. LOG(B,X) эквалиентно LOG(X)/LOG(B) .

    Возвращает логарифм числа X по базе 2:

    Mysql> SELECT LOG2(65536); -> 16.000000 mysql> SELECT LOG2(-100); -> NULL

    Функция LOG2() полезна с тем, чтобы узнать, сколько бит число потребует для хранения. Эта функция добавлена в MySQL 4.0.3. В более старых версиях вызывайте вместо нее: LOG(X)/LOG(2)

    Возвращает десятичный логарифм числа X:

    Mysql> SELECT LOG10(2); -> 0.301030 mysql> SELECT LOG10(100); -> 2.000000 mysql> SELECT LOG10(-100); -> NULL

    POW(X,Y) , POWER(X,Y)

    Возвращает значение аргумента X , возведенное в степень Y:

    Mysql> SELECT POW(2,2); -> 4.000000 mysql> SELECT POW(2,-2); -> 0.250000

    Возвращает неотрицательный квадратный корень числа X:

    Mysql> SELECT SQRT(4); -> 2.000000 mysql> SELECT SQRT(20); -> 4.472136

    Возвращает значение числа "пи". По умолчанию представлено 5 десятичных знаков, но в MySQL для представления числа "пи" при внутренних вычислениях используется полная двойная точность.

    Mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116

    Возвращает косинус числа X , где X задается в радианах:

    Mysql> SELECT COS(PI()); -> -1.000000

    Возвращает синус числа X , где X задается в радианах:

    Mysql> SELECT SIN(PI()); -> 0.000000

    Возвращает тангенс числа X , где X задается в радианах:

    Mysql> SELECT TAN(PI()+1); -> 1.557408

    Возвращает арккосинус числа X , т.е. величину, косинус которой равен X . Если X не находится в диапазоне от -1 до 1 , возвращает NULL:

    Mysql> SELECT ACOS(1); -> 0.000000 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.570796

    Возвращает арксинус числа X , т.е. величину, синус которой равен X . Если X не находится в диапазоне от -1 до 1 , возвращает NULL:

    Mysql> SELECT ASIN(0.2); -> 0.201358 mysql> SELECT ASIN("foo"); -> 0.000000

    Возвращает арктангенс числа X , т.е. величину, тангенс которой равен X:

    Mysql> SELECT ATAN(2); -> 1.107149 mysql> SELECT ATAN(-2); -> -1.107149

    ATAN(Y,X) , ATAN2(Y,X)

    Возвращает арктангенс двух переменных X и Y . Вычисление производится так же, как и вычисление арктангенса Y / X , за исключением того, что знаки обоих аргументов используются для определения квадранта результата:

    Mysql> SELECT ATAN(-2,2); -> -0.785398 mysql> SELECT ATAN2(PI(),0); -> 1.570796

    Возвращает котангенс числа X:

    Mysql> SELECT COT(12); -> -1.57267341 mysql> SELECT COT(0); -> NULL

    RAND() , RAND(N)

    Возвращает случайную величину с плавающей точкой в диапазоне от 0 до 1,0 . Если целочисленный аргумент N указан, то он используется как начальное значение этой величины:

    Mysql> SELECT RAND(); -> 0.9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881

    В выражениях вида ORDER BY не следует использовать столбец с величинами RAND() , поскольку применение оператора ORDER BY приведет к многократным вычислениям в этом столбце. В версии MySQL 3.23 можно, однако, выполнить следующий оператор: SELECT * FROM table_name ORDER BY RAND() : он полезен для получения случайного экземпляра из множества SELECT * FROM table1,table2 WHERE a=b AND c

    Если задано два или более аргументов, возвращает наименьший (с минимальным значением) аргумент. Сравнение аргументов происходит по следующим правилам:

    • Если возвращаемая величина используется в целочисленном контексте (INTEGER), или все аргументы являются целочисленными, то они сравниваются как целые числа.

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

      Если один из аргументов является зависимой от регистра строкой, то данные аргументы сравниваются с учетом регистра.

      В остальных случаях аргументы сравниваются как строки, независимые от регистра.

    mysql> SELECT LEAST(2,0); -> 0 mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0 mysql> SELECT LEAST("B","A","C"); -> "A"

    В версиях MySQL до 3.22.5 можно использовать MIN() вместо LEAST.

    GREATEST(X,Y,...)

    Возвращает наибольший (с максимальным значением) аргумент. Сравнение аргументов происходит по тем же правилам, что и для LEAST:

    Mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0 mysql> SELECT GREATEST("B","A","C"); -> "C"

    В версиях MySQL до 3.22.5 можно использовать MAX() вместо GREATEST .

    Возвращает аргумент X , преобразованный из радианов в градусы:

    Mysql> SELECT DEGREES(PI()); -> 180.000000

    Возвращает аргумент X , преобразованный из градусов в радианы:

    Mysql> SELECT RADIANS(90); -> 1.570796

    Возвращает число X , усеченное до D десятичных знаков. Если D равно 0 , результат будет представлен без десятичного знака или дробной части:

    Mysql> SELECT TRUNCATE(1.223,1); -> 1.2 mysql> SELECT TRUNCATE(1.999,1); -> 1.9 mysql> SELECT TRUNCATE(1.999,0); -> 1 mysql> SELECT TRUNCATE(-1.999,1); -> -1.9

    Начиная с MySQL 3.23.51 все числа округляются к нулю.

    Если D - негативное, то тогда вся часть числа обнуляется:

    Mysql> SELECT TRUNCATE(122,-2); -> 100

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

    Mysql> SELECT TRUNCATE(10.28*100,0); -> 1027

    Это происходит потому, что в действительности 10,28 хранится как нечто вроде 10,2799999999999999 .

Чтобы иметь возможность работать с этими функциями, вы обязаны скомпилировать РНР с поддержкой MySQL.

Установка

С помощью опции конфигурации --with-mysql вы включаете доступ PHP к БД MySQL. Если вы используете эту опцию без специфицирования пути к MySQL, PHP будет использовать встроенные клиентские библиотеки MySQL. В PHP4 поддержка MySQL всегда включена; Если вы не специфицируете эту опцию конфигурации, используются связанные библиотеки. Пользователи, запускающие другие приложения, которые используют MySQL (например, при запуске PHP 3 и PHP 4 как конкурирующих модулей Аpache, или auth-mysql), всегда должны специфицировать путь к MySQL: --with-mysql=/path/to/mysql . Это заставит PHP использовать клиентские библиотеки, установленные MySQL, исключая любые конфликты.

Установка

Поведение функций MySQL определяется установками в глобальном файле конфигурации php.ini .

Таблица 1. Опции конфигурации MySQL
Имя По умолчанию Изменяемая
mysql.allow_persistent "On" PHP_INI_SYSTEM
mysql.max_persistent "-1" PHP_INI_SYSTEM
mysql.max_links "-1" PHP_INI_SYSTEM
mysql.default_port NULL PHP_INI_ALL
mysql.default_socket NULL PHP_INI_ALL
mysql.default_host NULL PHP_INI_ALL
mysql.default_user NULL PHP_INI_ALL
mysql.default_password NULL PHP_INI_ALL

Дополнительные детали и определение констант PHP_INI_* см. в ini_set() .

Вот краткое описание директив конфигурации.

mysql.allow_persistent boolean mysql.max_persistent integer

Максимальное количество постоянных MySQL-соединений на процесс.

mysql.max_links integer

Максимальное количество постоянных MySQL-соединений на процесс, включая постоянные соединения.

mysql.default_port string

Номер порта по умолчанию TCP для использования при соединении с сервером БД, если иной порт не специфицирован. Если порт по умолчанию не специфицирован, значение получается из переменной окружения MYSQL_TCP_PORT , вхождения mysql-tcp в /etc/services или константы времени компиляции MYSQL_PORT , в указанном порядке. Win32 используют только константу MYSQL_PORT .

mysql.default_socket string

Имя сокета по умолчанию для использования при соединении с локальным сервером БД, если иное имя сокета не специфицировано.

mysql.default_host string

Хост сервера по умолчанию для использования при соединении с локальным сервером БД, если другой хост не специфицирован. Не примеряется в режиме safe mode .

mysql.default_user string

Имя пользователя по умолчанию для использования при соединении с сервером БД, если иное имя не специфицировано. Не применяется в режиме safe mode .

mysql.default_password string

Пароль по умолчанию для использования при соединении с сервером БД, если иной пароль не специфицирован. Не применяется в режиме safe mode .

Есть два типа ресурсов, используемых в MySQL-модуле. Первый это идентификатор ссылки для соединения с БД, второй - ресурс, содержащий результат выполнения запроса.

Предопределённые константы

Таблица 2. Константы извлечения MySQL

Примеры

Данный пример показывает, как соединиться с БД, выполнить запрос/query, напечатать результирующие ряды и отсоединиться от БД MySQL.

Содержание mysql_affected_rows - получает количество рядов, задействованных в предыдущей операции MySQL mysql_change_user - изменяет вошедшего/logged пользователя в активном соединении mysql_character_set_name - возвращает имя набора символов/character set mysql_close - закрывает MySQL-соединение mysql_connect - открывает соединение с MySQL-сервером mysql_create_db - создаёт БД MySQL mysql_data_seek - перемещает внутренний результирующий указатель mysql_db_name - получает результирующие данные mysql_db_query - отправляет MySQL query mysql_drop_db - удаляет БД MySQL mysql_errno - возвращает числовое значение сообщения об ошибке из предыдущей MySQL-операции mysql_error - возвращает текст сообщения об ошибке из предыдущей MySQL-операции mysql_escape_string - мнемонизирует/Escapes строку для использования в mysql_query mysql_fetch_array - извлекает результирующий ряд как ассоциативный массив, числовой массив или оба mysql_fetch_assoc - извлекает результирующий ряд как ассоциативный массив mysql_fetch_field - получает информацию столбца из результата и возвращает как объект mysql_fetch_lengths - получает длину каждого вывода в результате mysql_fetch_object - извлекает результирующий ряд как объект mysql_fetch_row - получает результирующий ряд в перечислимом массиве mysql_field_flags - получает флаги, ассоциированные со специфицированным полем в результате mysql_field_len - возвращает длину специфицированного поля в результате