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

Обратите внимание: до MySQL 5.0.10 сохраненные функции, созданные с CREATE FUNCTION, не должны содержать ссылки к таблицам, с ограниченными исключительными ситуациями. Они могут включать некоторые инструкции SET, которые содержат ссылки на таблицы, например, SET a:= (SELECT MAX(id) FROM t), и инструкции SELECT, которые выбирают значения непосредственно в переменные, например, SELECT i INTO var1 FROM t.

Следующее показывает пример простой сохраненной процедуры, которая использует параметр OUT. Пример использует команду delimiter клиента mysql, чтобы изменить операторный разделитель с ; на // в то время как процедура определяется. Это позволяет использовать разделитель ; в теле процедуры, которое будет передано на сервер.mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

– > BEGIN

– > SELECT COUNT(*) INTO param1 FROM t;

– > END;

– > //

Query OK, 0 rows affected (0.00 sec)

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)

При использовании команды delimiter, Вы должны избежать использования наклонной черты влево (\), потому что это escape-символ для MySQL.

Следующее представляет собой пример функции, которая берет параметр, выполняет операцию, использующую функцию SQL, и возвращает результат. В этом случае, ненужно использовать разделитель, потому что функциональное определение не содержит никакой внутренней ;, значит точку с запятой можно использовать как операторный разделитель:mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

– > RETURN CONCAT('Hello, ',s,'!');

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');

+----------------+

| hello('world') |

+----------------+

| Hello, world! |

+----------------+

1 row in set (0.00 sec)

Сохраненная функция возвращает значение типа данных, определенного в предложении RETURNS. Если инструкция RETURN возвращает значение иного типа, значение приведено к соответствующему типу. Например, если функция возвращает значение ENUM или SET, но инструкция RETURN возвращает целое число, значение, возвращенное из функции: строка для соответствующего ENUM-члена набора элементов SET.

5.2.2. Синтаксис ALTER PROCEDURE и ALTER FUNCTION

ALTER {PROCEDURE | FUNCTION} sp_name

[characteristic …]

characteristic:

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

Эта инструкция может использоваться, чтобы изменить характеристики сохраненной процедуры или функции. Начиная с MySQL 5.0.3, Вы должны иметь привилегию ALTER ROUTINE для подпрограммы. Эта привилегия предоставляется автоматически стандартному создателю. Если двоичная регистрация допускается, эта инструкция могла бы также требовать привилегии SUPER. Больше чем одно изменение может быть определено в команде ALTER PROCEDURE или ALTER FUNCTION.

5.2.3. Синтаксис DROP PROCEDURE и DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Эта инструкция используется, чтобы удалить сохраненную процедуру или функцию. То есть определенная подпрограмма будет удалена с сервера. Начиная с MySQL 5.0.3, Вы должны иметь привилегию ALTER ROUTINE для подпрограммы. Такая привилегия предоставляется автоматически создателю.

Предложение IF EXISTS является расширением MySQL. Это предотвращает ошибку, если процедура или функция не существует. Будет произведено предупреждение, которое может просматриваться с помощью команды SHOW WARNINGS.

5.2.4. Синтаксис команды CALLCALL sp_name([parameter[,…]])

Инструкция CALL вызывает процедуру, которая была определена предварительно, с помощью команды CREATE PROCEDURE.

CALL может передавать обратно значения к параметрам использования вызывающего оператора, которые объявлены как OUT или INOUT. Это также вернет число строк, на которые воздействовал запрос, это же значение программа пользователя может получать в уровне SQL, вызывая функцию ROW_COUNT() или из C API функцией mysql_affected_rows().

Чтобы вернуть значение из процедуры, использующей параметр OUT или INOUT, передайте параметр посредством переменной пользователя, и затем проверьте значение переменной после возврата из процедуры. Если Вы вызываете процедуру изнутри другой сохраненной процедуры или функции, Вы можете также передавать стандартный параметр или локальную стандартную переменную как параметр IN или INOUT. Для параметра INOUT инициализируйте значение перед его передачей процедуре. Следующая процедура имеет параметр OUT, который процедура устанавливает в текущую (актуальную) версию сервера, и значение INOUT, которое процедура увеличивает:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)

BEGIN

# Set value of OUT parameter

SELECT VERSION() INTO ver_param;

# Increment value of INOUT parameter

SET incr_param = incr_param + 1;

END;

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

mysql> SET @increment = 10;

mysql> CALL p(@version, @increment);

mysql> SELECT @version, @increment;

+------------+------------+

| @version | @increment |

+------------+------------+

| 5.0.25-log | 11 |

+------------+------------+

Если Вы пишете программы на C, которые выполняют сохраненные процедуры с инструкцией SQL CALL, Вы должны установить флажок CLIENT_MULTI_RESULTS, когда Вы вызываете mysql_real_connect(), либо установить CLIENT_MULTI_STATEMENTS. Это потому, что каждый CALL возвращает результат, чтобы указать состояние обращения, в дополнение к любым наборам результатов, которые могли бы быть возвращены инструкциями, выполненными внутри процедуры. Чтобы обрабатывать результат инструкции CALL, используйте цикл, который вызывает mysql_next_result() чтобы определить, имеется ли большее количество результатов.

5.2.5. Синтаксис составного оператора BEGIN … END

[begin_label:]

BEGIN

[statement_list]

END [end_label]

Синтаксис BEGIN … END используется для записи составных инструкций, которые могут появляться внутри сохраненных подпрограмм и триггеров. Составная инструкция может содержать много инструкций, взятых в ключевые слова BEGIN и END. Здесь statement_list представляет список инструкций. Каждая инструкция внутри statement_list должна быть завершена операторным разделителем точкой с запятой (;). Обратите внимание, что список statement_list факультативный, что означает, что пустая составная инструкция (BEGIN END) допустима.

Использование многих инструкций требует, чтобы пользователь был способен послать операторные строки, содержащие операторный разделитель ;. Это обработано в клиенте командной строки mysql командой delimiter. Изменение разделителя конца инструкции ; (например, на //) позволяет ; использоваться в стандартном теле.

Составная инструкция может быть помечена. end_label не может быть дан, если begin_label также не присутствует. Если оба присутствуют, они должны быть те же самые.

Факультативное предложение [NOT] ATOMIC еще не обеспечивается. Это означает, что никакие точки сохранения транзакций не установлены в начале блока команды, и предложение BEGIN, используемое в этом контексте не имеет никакого эффекта на текущей (актуальной) транзакции.

5.2.6. Синтаксис DECLARE

Инструкция DECLARE используется, чтобы определить различные элементы, локальные для подпрограммы:

Локальные переменные. Подробности в разделе "5.2.7. Переменные в сохраненных подпрограммах".

Условия и драйверы. Подробности в разделе "5.2.8. Условия и драйверы ".

Курсоры. Подробности в разделе "5.2.9. Курсоры".

Инструкции SIGNAL и RESIGNAL в настоящее время не обеспечиваются.

DECLARE позволяется только внутри BEGIN … END и должен быть в начале, перед любыми другими инструкциями.

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

5.2.7. Переменные в сохраненных подпрограммах

Вы можете объявлять и использовать переменные внутри подпрограммы.

5.2.7.1. Локальные переменные DECLARE

DECLARE var_name[,…] type [DEFAULT value]

Эта инструкция используется, чтобы объявить локальные переменные. Чтобы обеспечивать значение по умолчанию для переменной, включите предложение DEFAULT. Значение может быть определено как выражение, оно не обязательно должно быть константой. Если предложение DEFAULT отсутствует, начальное значение NULL.

Локальные переменные обрабатываются подобно стандартным параметрам относительно типа данных и проверки переполнения.

Область (контекст) локальной переменной: внутри блока BEGIN … END, где это объявлено. Переменная может упоминаться в блоках, вложенных внутри блока объявления, за исключением тех блоков, которые объявляют переменную с тем же самым именем.

5.2.7.2. Команда SET

SET var_name = expr [, var_name = expr] …

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