Створення збереженої процедури
CREATE PROCEDURE sp_name ([parameter [,...]]) [characteristic ...] routine_body
CREATE FUNCTION sp_name ([parameter[,...]]) RETURNS type
[characteristic ...] routine_body
Тут sp_name ім'я збереженої процедури, в дужках, наступних за ім'ям, передається необов'язковий список параметрів, перерахованих через кому. Кожен параметр Parameter дозволяє передати в процедуру або з неї вхідні дані або результат роботи функції і має наступний синтаксис:
[ IN | OUT | INOUT ] param_name type
Параметру param_name передує одне з ключових слів in, out, inout, які дозволяють задати напрямок передачі даних:
- in - дані передаються строго всередину збереженої процедури, але якщо параметру з даним модифікатором всередині функції присвоюється нове значення, після виходу з неї він не зберігається і параметр приймає значення, яке він мав до виклику процедури;
- out-дані передаються строго зі збереженої процедури, навіть якщо параметр має якесь початкове значення, всередині збереженої процедури це значення не береться до уваги. З іншого боку, якщо параметр змінюється всередині процедури, після виклику процедури параметр має значення, присвоєне йому всередині процедури;
- inout - значення цього параметра приймається до уваги всередині процедури, так і зберігає своє значення після виходу з неї.
Зауваження:
- Список аргументів, вкладених у круглі дужки, повинен бути присутнім завжди. Якщо аргументи відсутні, слід використовувати порожній список аргументів ().
Після імені параметра param_name вказується його тип. За замовчуванням, якщо жоден з модифікаторів не вказаний, вважається, що параметр оголошений з ключовим словом in.
Оператор CREATE FUNCTION дозволяє задати функцію користувача, тобто такий вид процедури, який повертає єдине значення. Тип цього значення дозволяє задати оператор RETURN.
Характеристика characteristic може приймати одне з наступних значень або їх комбінацію:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'
Тіло процедури
Тіло процедури routine_body складається з складеного оператора begin ... end, всередині якого можуть розташовуватися інші оператори, у тому числі й інші складові оператори begin ... end. Оператор має наступний синтаксис:
[label:] BEGIN
statements
END [label]
Якщо оператор починається з необов'язкової мітки label, в якості якої може виступати будь унікальне ім'я, то він може закінчуватися виразом end label
Оператор begin ... end може виглядати так, як це представлено у прикладі.
Приклад:
begin
update tbll set coll = '1234.56';
update tbl2 set col2 = '1234.56';
END
В якості одного з операторів всередині складеного оператора BEGIN ... END може виступати інший складений оператор (приклад).
Приклад:
BEGIN
UPDATE tbll SET coll = '1234.56';
inner: BEGIN
UPDATE tbl2 SET col2 = '1234.56';
UPDATE tbl3 SET col3 = '1234.56';
END inner;
END
Як видно з прикладу, у внутрішньому складеному операторі BEGIN ... END перед оператором BEGIN використовується мітка inner, яка обов'язково згадується і у операторі END. Слід зазначити, що якщо процедура, що зберігається містить тільки один запит, то можна не використовувати складений оператор BEGIN ... END.
Зауваження:
- Після оператора END можна як поміщати, так і не поміщати крапку з комою тут і далі крапка з комою після END поміщатися не буде.
Основні труднощі, що виникають при роботі з збереженими процедурами, полягають в тому, що обов'язковий символ крапки з комою ";" наприкінці кожного запиту сприймається консольним клієнтом як сигнал до відправлення запиту на сервер. Для того щоб уникнути цього, при роботі з збереженими процедурами слід перевизначити роздільник запитів за допомогою параметра - delimiter = // консольного клієнта mysql (приклад). У цьому випадку для позначення закінчення введення замість крапки з комою необхідно буде використовувати послідовність "//".
Приклад:
mysql -u root —delimiter=//
Крім того, можна змінювати роздільник у будь-який момент в консольному клієнті mysql. Для цього необхідно скористатися командою delimiter, після якої вказати роздільник (приклад нижче).
Приклад:
DELIMITER //
SELECT VERSION ()//
VERSION ()
5.0.6-beta-nt
DELIMITER ;
SELECT VERSION();
VERSION ()
5.0-6-beta-nt
Ім'я процедури не може перевищувати 64 символи та не залежить від регістра, т.e. імена numcatalogs (), Numcatalogs () і NUMCATALOGS () є еквівалентними. Приклад створення найпростішої процедури демонструється в наступному прикладі.
Приклад:
CREATE PROCEDURE ray_version ()
BEGIN
SELECT VERSION();
END //
Якщо тіло процедури містить єдиний запит, можна не використовувати складений оператор BEGIN ... END.
Приклад:
CREATE PROCEDURE my_version ()
SELECT VERSION(); //
Функція my_version () нічого не робить, крім того, що виводить версію сервера MySQL. Яким чином можна скористатися результатами запиту? Для того щоб викликати збережену процедуру, необхідно застосувати оператор CALL, після якого міститься ім'я процедури і її параметри в круглих дужках.
Приклад:
CALL my_version ();
Зауваження:
- При виклику процедури, на відміну від вбудованих функцій, між ім'ям функції і круглими дужками допускається пропуск.
При іменуванні функцій слід уникати назв, що збігаються з іменами внутрішніх функцій MySQL (приклад).
Приклад:
CREATE PROCEDURE pi ()
BEGIN
SELECT VERSION();
END //
У прикладі оголошується процедура, pi (), яка виводить поточну версію сервера MySQL. Слід звернути увагу, що між ім'ям процедури і круглими дужками поміщений пробіл - в іншому випадку СКБД MySQL інтерпретує послідовність pi () як виклик вбудованої функції, що повертає число пі. При виклику процедури використання пропусків в цьому випадку є обов'язковим. Перевантаження функцій в MySQL не допустима.
Щоб уникнути подібних накладок рекомендується уникати використання назв збережених процедур, що збігаються з іменами вбудованих функцій MySQL. Якщо все ж таки це необхідно, то і у визначенні функції, і при її виклику слід використовувати пробіл між ім'ям процедури і круглими дужками.
Які оператори припустимі в тілі процедур? Будь-які, включаючи INSERT, UPDATE, DELETE, SELECT, DROP, REPLACE та ін.. (приклад).
Приклад:
CREATE PROCEDURE р () DELETE FROM t; //
CREATE PROCEDURE p () SET @x = 5; //
CREATE PROCEDURE p () DROP TABLE t; //
CREATE PROCEDURE p () SELECT 'A' ; //
У тілі процедури можна використовувати багаторядковий коментар у стилі мови С, який починається з послідовності "/ *" і закінчується послідовністю "* /" (приклад).
Приклад:
CREATE PROCEDURE my_version ()
BEGIN
/*
Багаторядковий коментар всередині функції my_version()
*/
SELECT VERSION(); /* Виклик єдиного оперетора */
END
//