Збережені процедури

 

Збережені процедури

 

Часто при виконанні рутинних операцій потрібно здійснювати послідовність однакових запитів. Збережені процедури дозволяють об'єднати послідовність таких запитів і зберегти їх на сервері. Після цього клієнтам не доведеться посилати серверу послідовність запитів, достатньо надіслати один запит на виконання збереженої процедури.

Зауваження:

Збережені процедури з'явилися в СКБД MySQL, починаючи з версії 5.0.0.

Збережені процедури мають наведені далі переваги.

  • Повторне використання коду - після того, як процедура, створена, її можна викликати з будь-яких додатків і SQL-запитів, більше не потрібно щоразу програмувати одні й ті самі дії, завдяки чому зменшується ризик проникнення у додатки помилок і зменшується час розробки програми.
  • Скорочення мережевого трафіку-при використанні збережених процедур замість того, щоб відсилати по мережі кожен запит і отримати на кожен із запитів відповідь, набагато економніше послати серверу запит на виконання збереженої процедури і відразу отримати відповідь.
  • Безпека-збережені процедури використовуються для всіх стандартних банківських операцій. Процедура гарантує, що послідовність дій буде узгодженою і не призведе до порушення цілісності даних через те, що один оператор не буде виконаний. Крім того, для виконання збереженої процедури користувач повинен мати відповідний привілей. При цьому прав доступу до таблиць мати зовсім не обов'язково. Таким чином, адміністратор бази даних отримує більш широкі можливості у плані захисту даних і управління доступом користувачів до об'єктів бази даних.
  • Простота доступу-основним імперативом розробки програмного забезпечення проголошено зменшення складності коду. Збережені процедури дозволяють інкапсулювати складний код і оформити його у вигляді простого виклику з осмисленим ім'ям. При створенні нового каталогу набагато простіше оперувати процедурою create_new_catalog (), ніж кількома операторами незрозумілого призначення. При реєстрації угоди, що вимагає пошуку в таблицях catalogs products, users, і редагуванні таблиць orders і users набагато простіше оформити послідовність SQL-операторів для оформлення угоди у збережену процедуру ordering (). Навіть якщо послідовність операторів добре знайома програмісту, застосування процедур дозволяє перейти на більш високий рівень абстракції і оперувати не таблицями, а сутностями реально го світу, такими як угода, каталог продукція та ін..
  • Виконання ділової логіки - збережені процедури можуть виконувати перевірку умов виконання замовлення, наприклад, підраховуючи число товарних позицій на складі, і відхиляти замовлення у випадку, якщо його недостатньо. Це дозволяє перенести код збереження цілісності бази даних з прикладної програми на сервер бази даних. Так як прикладна програма може виконуватися на сотні машин мережі, а сервер, як правило, один, стійкість системи при переміщенні логіки на сервер різко зростає. Крім того, в цьому випадку легше змінити мову розробки зовнішнього застосування, тому що велика частина логіки оформлена у вигляді збережених процедур і не залежить від мови розробки програмного додатку.

Збережені процедури і привілеї

Починаючи з версії 5.0.3, СУБД MySQL вимагає від користувачів наявності наступних привілеїв при роботі з збереженими процедурами:

  • для створення збережених процедур необхідна наявність привілеї create routine;
  • Для редагування та видалення збереженої процедури необхідно наявність привілеї alter routine, користувач, який створює збережену процедуру, автоматично наділяється цим привілеєм;
  • для виклику процедури, що зберігається необхідна наявність привілеї execute, цей привілей також автоматично надається користувачеві, що створив збережену процедуру.

Створення збереженої процедури

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

//

Параметри процедури

У попередніх прикладах збережені процедури не містили параметрів. Як згадувалося раніше, кожен параметр може бути проголошений одним з модифікатором IN, OUT або INOUT. У наступному прикладі наводиться приклад функції, яка присвоює змінній користувача @ х нове значення.

Приклад:

CREATE PROCEDURE set_K (IN value INT)

BEGIN

SET @x = value;

END //

CALL set_x(123456)//

SELECT @x//

@x

123456

Як видно з прикладу, через параметр value функції передається числове значення 123456, яке вона присвоює змінній користувача @ х. Модифікатор in повідомляє СКБД MySQL, що за допомогою параметра value користувачі передають дані всередину функції.

Зауваження:

  • На відміну від змінної @ х, яка є глобальною і доступна як всередині збереженої процедури set_x (), так і поза нею, параметри функції є локальними і доступні для використання тільки усередині функції.

Використання ключового слова in не є обов'язковим - якщо жоден з модифікаторів не вказано, СУБД MySQL вважає, що параметр оголошений з модифікатором IN.

CREATE PROCEDURE set_y (value INT)

BEGIN

SET value = 7;

SET @x = value;

END //

SET @val = 123456//

CALL set_y(@val)

@val

123456

Зауваження:

  • Слід зазначити, що імена параметрів при оголошенні збереженої процедури і при виклику не обов'язково повинні збігатися. Усередині процедури всі локальні змінні використовуються без символу @ в той час як для глобальних змінних символ перед ім'ям обов'язковий4

Процедура set_y () приймає єдиний iN-параметр value, за допомогою оператора set значення параметра змінюється всередині функції. Однак, як видно з прикладу вище, після виконання процедури, що значення користувача змінної @ val, переданої функції як параметр, не змінюється. Якщо потрібно, щоб значення змінної піддавалося зміні, необхідно оголосити параметр процедури з модифікатором out (приклад).

Приклад:

CREATE PROCEDURE set_y (OUT value INT)

BEGIN

SET @x = value;

SET value = 7;

END//

SET @val = 123456//

CALL set_y(@val)//

SELECT @x,@val//

@x  @val

Null 7

Як видно з прикладу, при використанні модифікатора OUT будь-які зміни параметра всередині процедури відображаються на змінній. Передача у якості значення змінної дозволяє використовувати результат процедури для подальших обчислень. Однак передати значення всередину функції за допомогою OUT-параметра вже не вийде.

Зауваження:

  • Якщо локальна або користувацька змінні не ініціюються за допомогою оператора SET чи ключового слова DEFAULT, вони отримують значення NULL.

Для того щоб через параметр можна було і передати значення всередину процедури, і отримати значення, яке потрапляє в параметр в результаті обчислень всередині процедури, його слід оголосити з модифікатором INOUT (приклад).

CREATE PROCEDURE set_y (INOUT value INT)

BEGIN

SET @x = value;

SET value = 7;

END //

SET @val = 123456//

CALL set_y(@val)//

SELECT @x, @val//

@x           @val

123456     7

Тепер через параметр value можна як передавати значення всередину процедури, так і витягувати значення, які отримує параметр всередині процедури. Проте рекомендується використовувати тільки IN-і ОІТ-параметри, не вдаючись до комбінованих INOUT-параметрів, оскільки це призводить до нечитабельного і непослідовного коду.

Робота з таблицями бази даних

Всі процедури, розглянуті вище, не використовували у своїй роботі таблиці, далі розглянемо декілька процедур, які здійснюють запити до таблиць бази даних. Створимо функцію numcatalogs (), яка підраховує кількість записів у таблиці catalogs навчальної бази даних shop (приклад).

Приклад:

CREATE PROCEDURE numcatalogs (OUT total INT)

BEGIN

SELECT COUNT(*) INTO total FROM catalogs;

END //

Процедура numcatalogs () має один цілочисельний (int) параметр total, в який зберігається число записів в таблиці catalogs. Здійснюється це за допомогою оператора SELECT ... INTO ... FROM, який дозволяє зберігати результати безпосередньо у вихідному параметрі total функції numcatalogs. Даний оператор дозволяє оперувати відразу декількома стовпцями (наступний приклад).

Приклад:

SELECT id, data INTO x, у FROM test LIMIT 1;

Таким чином, параметр total, після виконання процедури, містить число записів у таблиці catalogs.

Приклад (виклик новоствореної процедури):

CALL numcatalogs(@a);

select @a;

@a

5

Як видно з прикладу, як параметр функції numcatalogs () передається змінна @а.

 СКБД підтримує контекст виклику процедури для бази даних за замовчуванням. Це означає, що якщо Процедура, створена в базі даних. shop, буде викликана в той момент, коли поточною базою даних є база test - СУБД MySQL поверне помилку (1305: PROCEDURE test.numcatalogs does not exist).

Процедура успадковує базу даних за замовчуванням від викликаючого оператора тому при зверненні до таблиць інших баз даних необхідно використовувати розширені імена.

Зауваження:

  • Использование оператора USE в хранимых процедурах запрещено.

Створимо процедуру catalogname (), яка буде повертати по первинному ключу id_catalog назву каталогу nаmе (приклад нижче). Для цього буде потрібно визначити параметр id_catalog з атрибутом IN, a name з атрибутом OUT.

Приклад:

CREATE PROCEDURE catalogname (IN id INT, OUT catalog TINYTEXT)

BEGIN

SELECT name INTO catalog FROM catalogs

WHERE id_catalog = id;

END //

SET @id := 5//

CALL catalogname(@id, @name)//

SELECT @id, @name//

@id @name

5     Оперативная память

CALL catalogname(1, @name)//

SELECT @name//

@name

Процессоры

Прикладом функції, що використовує параметр типу INOUT, ??може стати функція count_by_id (), яка, приймаючи в якості параметра первинний ключ каталогу з таблиці catalogs, повертає число товарних позицій у даному каталозі.

Приклад:

CREATE PROCEDURE count_by_id (INOUT id INT)

BEGIN

SELECT COUNT(*) INTO id FROM products

WHERE id_catalog = id;

­END //

SET @id = 3//

CALL count_by_id(@id)//

SELECT @td//

@id

4

Однак застосування inout-параметрів слід всіляко уникати, оскільки при їх використанні виникає спокуса порушити правила хорошого стилю програмування, як у прикладі, де змінна @ id спочатку містила первинний ключ каталогу, а після виклику містить вже число товарних позицій у даному каталозі . Використання змінних у цьому дусі неодмінно призводить до виникнення помилок. Краще взагалі відмовитися від використання inout-змінних навіть у тому випадку, коли функція приймає і повертає однотипні дані (наприклад, первинний ключ), краще створити два параметри: один для вхідного значення, інший - для вихідного.

Приклад:

CREATE PROCEDURE numcatalogsview ()

BEGIN

SELECT COUNT(*) FROM catalogs;

END //

CALL numcatalogsview ()//

COUNT(*)

5

Як видно з прикладу, функція numcatalogsview () дозволяє домогтися тих же pезультатов, що і numcatalogs () (дивіться приклад вище) без використання параметрів і користувацьких змінних. Проте використання параметрів виправдано, оскільки дозволяє створювати більш читабельний код і використовувати результат функції при виклику її з іншої функції. Створимо функцію catalog_by_product (), яка по імені товарної позиції повертає ім'я каталогу, до якого стосується ця товарна позиція. У ході створення нової функції будемо спиратися на раніше створену функцію catalogname (), яка повертає ім'я каталогу по його первинному ключу id_catalog (приклад вище). Код нової функції представлений в прикладі.

CREATE PROCEDURE catalog_by_product (IN product TINYTEXT,

OUT catalog TINYTEXT)

BEGIN

DECLARE id INT;

SELECT id_catalog INTO id FROM products

WHERE name = product LIMIT 1;

CALL catalogname(id, catalog);

END //

CALL catalog_by_product (' Celeron 1.8', @catalogname) //

SELECT @catalogname//

@catalogname

Процессоры

Як видно з прикладу, функція catalog_by_product () має два параметри:

  • product-вхідний параметр, через який передається назва товарної позиції;
  •  catalog-вихідний параметр, через який можна отримати результат роботи функції - назву каталогу, в який входить товарна позиція.

 

За назвою товарної позиції product при допомозі SELECT-запиту визначається первинний ключ каталогу. Отримане значення поміщається в тимчасову змінну id, яка передається через перший аргумент функції catalogname (), яка повертає назву каталогу і розміщує результат роботи в змінну catalog, що є вихідним параметром функції catalog_by_product ().

 У функції catalog_by_product () потрібна тимчасова змінна id. Для використання будь-якої змінної у функції потрібно її оголошення при допомозі оператора declare, який має наступний синтаксис:

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

Один оператор declare дозволяє оголосити відразу кілька змінних одного типу причому необов'язкове слово default дозволяє призначити значення.

Приклад:

CREATE PROCEDURE declare_var ()

BEGIN

DECLARE id, num INT(11) DEFAULT 0;

DECLARE name, hello, temp TINYTEXT;

END //

У прикладі оголошуються дві змінні типу int (11) - id і num, ініційовані значенням 0, і три текстові змінні name, hello і temp, оголошені без додаткової ініціалізації. Ініціювати локальні змінні можна і пізніше при допомозі оператора set.

Оператор DECLARE може з'являтися тільки всередині блоку BEGIN ... END, область видимості оголошеної змінної також обмежена цим блоком. Це означає, що в різних блоках BEGIN ... END можуть бути оголошені змінні з однаковими іменами, і діяти вони будуть тільки в рамках даного блоку, не перетинаючись з змінними інших блоків.

Приклад:

CREATE PROCEDURE declare_var ()

outer: BEGIN

DECLARE var TINYTEXT DEFAULT 'зовнішня змінна';

inner: BEGIN

DECLARE var TINYTEXT DEFAULT 'внутрішня змінна';

SELECT var;

END inner;

SELECT var;

END outer //

CALL declare_var()//

var

зовнішня змінна

var

внутрішня змінна

У прикладі змінна var оголошується спочатку зі значенням 'зовнішня змінна' в зовнішньому блоці BEGIN ... END, після чого у вкладеному блоці оголошується друга змінна var зі значенням ’внутрішня змінна’, яка екранує першу змінну.

Однак змінна, оголошена в зовнішньому блоці BEGIN ... END, буде доступна у вкладеному блоці, якщо не буде оголошено екрануючої її змінної (приклад нижче).

Приклад:

CREATE PROCEDURE one_declare_var ()

BEGIN

DECLARE var TINYTEXT DEFAULT 'зовнішня змінна';

BEGIN

SELECT var;

END;

SELECT var;

END //

CALL one_declare_var()//

var

зовнішня змінна

var

зовнішня змінна

Зворотнє не вірно, змінна, оголошена у вкладеному блоці, недоступна в зовнішньому (приклад).

Приклад:

CREATE PROCEDURE inner_declare_var ()

BEGIN

BEGIN

DECLARE var TINYTEXT DEFAULT ' внутрішня змінна ';

SELECT var;

END;

SELECT var;

END //

CALL inner_declare_var()//

var

внутрішня змінна

ERROR 1054 (42S22): Unknown column 'var' in field list'

Як видно з прикладу, виклик оператора select var у зовнішньому блоці begin ... end призводить до помилки, тому що час життя змінної var обмежено її блоком і після виходу з цього блоку її існування припиняється.

На закінчення розділу слід зазначити, що не допускається і повторне оголошення змінної в рамках одного блоку BEGIN ... END (приклад). Це призводить до виникнення помилки 1331: "Повторне оголошення змінної".

Приклад:

CREATE PROCEDURE dbl_declare_var ()

BEGIN

DECLARE var TINYTEXT DEFAULT 'зовнішня змінна';

DECLARE var TINYTEXT DEFAULT ’внутрішня змінна’

SELECT var;

END //

ERROR 1331 (42000): Duplicate variable: var

Збережені функції

Крім форми CREATE PROCEDURE, що створює процедуру, допускається використання create function, яка створює функцію. Функція на відміну від процедури може викликатися безпосередньо, без використання оператора CALL і повертати одне значення, яке підставляється на місце виклику функції, як у випадку вбудованих функцій MySQL.

Створимо найпростішу функцію say_hello (), яка буде приймати єдиний вхідний параметр з ім'ям name і повертати фразу "Hello, name!", Де замість підрядка name буде підставлено значення параметра name (приклад).

Приклад:

CREATE FUNCTION say_hello (name CHAR(20)) RETURNS CHAR(50)

BEGIN

RETURN CONCAT(' Hello, ',name,'!');

END //

SELECT say_hello('world'), say_hello('softtime')//

say_hello('world')                 say_hello('softtime')

Hello, world!                        Hello, softtime!

Після оголошення параметрів функції слідує оператор returns, який задає тип повертається функцією значення. Повернути значення з функції можна за допомогою оператора return (наступний приклад), який може бути викликаний у будь-якій точці функції. Виклик оператора return означає, що функція повинна негайно завершити виконання і повернути значення, передане в якості аргументу оператора return.

Зауваження:

  • При оголошенні параметрів функції використання ключових слів IN, INOUT і OUT неприпустимо Всі параметри, передані функції, є вхідними.
  • Функція обов'язково повинна містити оператор returns, що встановлює тип значення функції, і хоча б один оператор return в тілі функції, який повертає це значення.

Приклад:

CREATE FUNCTION func_catalog (id INT)

RETURNS TINYTEXT

BEGIN

DECLARE catalog TINYTEXT;

SELECT name INTO catalog FROM catalogs

WHERE id_catalog = id LIMIT 1;

RETURN catalog;"

SELECT name INTO catalog FROM catalogs

WHERE id_catalog = id + 1 LIMIT 1;

RETURN catalog;

END //

SELECT func_catalog (1)//

func_catalog(1)

Процессори

Збережена функція func_catalog () приймає єдиний параметр id-первинний ключ таблиці catalogs. Прийнявши як параметр id значення 1, функція повертає результат ("процесори"), досягнувши першого оператора return. При цьому другий оператор select і return не досягаються ніколи (інакше поверталося б значення "Оперативна пам'ять"). Це не означає, що двох операторів return у тілі функції не повинно зустрічатися. Нижче будуть розглянуті умовні конструкції дозволяють в залежності від умов вибирати варіант, який повинен повертатися - в такій ситуації використання множинного виходу з функції за допомогою декількох операторів return не уникнути.

Якщо послідовність операторів, які вирішено оформити у вигляді збереженої процедури, повертає одне-єдине значення, набагато зручніше оформити їх у вигляді збереженої функції, т. к. працювати з ними у виразах набагато зручніше. Створимо дві функції: coun_jproduct_in_catalog () і count_product (), які будуть повертати загальне число товарних позицій в каталозі і загальне число товарних позицій в навчальному електронному магазині shop.

Приклад:

CREATE FUNCTION count_product_in_catalog (id INT)

RETURNS INT

BEGIN

DECLARE total INT;

SELECT SUM(count) INTO total FROM products

WHERE id_catalog = id LIMIT 1;

RETURN total;

END //

CREATE FUNCTION count_product ()

RETURNS INT

BEGIN

DECLARE total INT;

SELECT SUM(count) INTO total FROM products;

RETURN total;

END//

SELECT count_product_in_catalog(1) AS total,

count_product_in_catalog(1)/countjproduct()*100 AS persent//

total                   persent

56             26.5403

Як видно з прикладу, функції зручно використовувати у виразах, наприклад, для підрахунку процентного вкладу товарних позицій каталогу в загальне число товарів в електронному магазині shop.

Група характеристик збережених процедур

Синтаксис збережених процедур допускає використання наступних характеристик characteristic У визначенні CREATE PROCEDURE та CREATE FUNCTION:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| SQL SECURITY {DEFINER | INVOKER}

|COMMENT 'string'

Дані ключові слова описують характеристики збережених процедур і функцій. Вони розміщуються після списку параметрів, але до початку тіла збереженої процедури. Характеристика LANGUAGE SQL поки не має особливого сенсу і повідомляє, що процедура написана на мові SQL (приклад).

Приклад:

CREATE PROCEDURE characteristics ()

LANGUAGE SQL

BEGIN

SELECT RAND();

END //

У наступних версіях MySQL планується надати можливість створення збережених процедур з використанням мови, відмінної від SQL. Швидше за все, одним з перших підтримуваних мов стане РНР, т. к. базовий механізм РНР невеликий за розмірами, безпечний у відношенні потоків і легко вбудовується. Рекомендується використовувати ключове слово LANGUAGE SQL вже зараз для забезпечення сумісності з майбутніми версіями MySQL та іншими СУБД.

Ключове слово DETERMINISTIC дозволяє повідомити оптимізатору, що процедура завжди повертає один і той же результат для одних і тих самих вхідних параметрів, в іншому випадку слід використовувати ключове слово NOT DETERMINISTIC (приклад).

Приклад:

CREATE PROCEDURE characteristics ()

LANGUAGE SQL

NOT DETERMINISTIC

BEGIN

SELECT RAND();

END //

Зауваження:

  • На даний момент ключове слово DETERMINISTIC розпізнається, але не використовується оптимізатором MySQL.

Ключове слово SQL SECURITY може бути записано в двох формах: SQL SECURITY DEFINER і SQL SECURITY INVOKER. Якщо використовується форма SQL SECURITY DEFINER, то процедура викликається з привілеями користувача, що створив її, при використанні SQL SECURITY INVOKER процедура викликається з привілеями користувача, що викликає процедуру оператором CALL (приклад).

Приклад:

CREATE PROCEDURE characteristics ()

LANGUAGE SQL

NOT DETERMINISTIC

SQL SECURITY INVOKER

BEGIN

SELECT RAND();

­END //

Зауваження:

  • Якщо ключове слово SQL SECURITY не вказано, за замовчуванням встановлюється режим SQL SECURITY DEFINER-Процедура виконується з привілеями створившого користувача.

Для виконання процедури, і її власник, і її користувач повинні мати доступ до бази даних, в якій збережена процедура, крім того, вони обоє повинні мати привілей EXECUTE, незалежно від того, в якій формі використано ключове слово SQL SECURITY.

Ключове слово comment (наступний приклад) дозволяє позначити процедуру коротким описом, який відобразить оператор SHOW CREATE PROCEDURE і SHOW CREATE FUNCTION.

Зауваження:

  • Ключове слово COMMENT є розширенням MySQL і може не підтримуватися іншими СУБД.

Приклад:

CREATE PROCEDURE characteristics ()

LANGUAGE SQL

NOT DETERMINISTIC

SQL SECURITY INVOKER

COMMENT 'Функція повертає випадкове значення'

BEGIN

SELECT RAND();

END //

SHOW CREATE PROCEDURE characteristics//

Procedure: characteristics sql_mode:

Create Procedure: CREATE PROCEDURE 'shop*.'characteristics'()

SQL SECURITY INVOKER

COMMENT * Функция возвращает случайное значение'

BEGIN

SELECT RAND();

END

Оператори керування потоком даних

Збережені процедури це не просто зручні контейнери для групи запитів, вони дозволяють реалізувати, досить складну логіку, використовуючи оператори розгалуження та цикли.

Зауваження:

  • Поза збережених процедур описані в даному розділі оператори застосовувати не можна.

Оператор IF...THEN...ELSE

Оператор if дозволяє реалізувати розгалуження програми за умовою і має наступний синтаксис:

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list] END IF

Логічний вираз search_condition може приймати два значення: 0 (брехня) і значення, відмінне від нуля (істина). Якщо логічний вираз істинний, то оператор statement_list після ключового слова виконується, інакше виконується список операторів у блоці else (якщо блок else є). Як statement_list може виступати складений оператор begin ... end.

Зауваження:

  • Слід зазначити, що в СКБД MySQL, крім оператора if, описаного тут, існує функція if ().

Розглянемо найпростіший приклад використання оператора if. Процедура pricelist (), представлена ??у прикладі нижче, виводить список товарних позицій в каталозі, первинний ключ яких дорівнює id. Другим параметром функції є число cur, що може приймати значення 0, якщо потрібно вивести ціни в рублях, і 1, якщо ціни повинні бути перераховані в долари з курсом 8.8 гривень.

Зауваження:

  • Для створення логічних виразів можна використовувати всі оператори порівняння ("=", ">", ">=" "<>", "<", "<="). Крім того, логічні вирази можна комбінувати між собою за допомогою операторів "&&" (І), а також "||" (АБО).

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL

BEGIN

IF(cur = 0) THEN

SELECT name, price FROM products WHERE id_cataiog = id;

end if;

IF(cur = 1) THEN

select name, price/8.8 FROM products WHERE id_catalog = id;

END IF

END //

CALL pricelist (1,1)//

name                           price/8.8

Celeron 1.8                 55.381944

Celeron 2.0GHz 68.368056

Оператор if може бути оснащений додатковим блоком else, після якого виконуються оператори, якщо умова виявилася хибною. Функцію pricelist () можна переписати так, як це показано у прикладі.

Зауваження:

  • Після ключового слова ELSE ставити крапку з комою не потрібно, оскільки цим самим єдиний оператор IF розбивається на частини - крапку з комою ставлять після ключового слова END IF.

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL

BEGIN

IF(cur) then

SELECT name, price/8.8 FROM products WHERE id_catalog = id;

ELSE

SELECT name, price FROM products WHERE id_catalog = id;

END IF;

END //

З точки зору функціональності, збережені процедури з прикладів абсолютно однакові, але в останньому випадку знадобився лише один оператор IF, який приймає в якості логічного виразу параметр cur. Якщо cur дорівнює 1, що є істиною, виконується перший оператор, який виводить ціни в доларах, якщо параметр cur дорівнює 0, що є брехнею, виконується запит в блоці ELSE.

Оператор if дозволяє вибрати і більше альтернатив. Нехай необхідно крім гривневого і доларового прайс-листа, виводити ціни в євро за курсом 14.5 гривень за євро. Таким чином, параметр cur може приймати такі значення:

  • 0 - гривні;
  •  1-долари;
  •  2 і вище - євро.

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL -> BEGIN

IF(cur = 0) THEN

SELECT name, price FROM products WHERE id_catalog = id;

ELSEIF (cur = 1) THEN

SELECT name, price/8.8 FROM products WHERE id_catalog = id;

ELSE

SELECT name, price/14.5 FROM products WHERE id_catalog = id;

END IF;

END //

Оператор if в попередньому прикладі перевіряє, чи не рівний параметр cur нулю, якщо це так спрацьовує перший запит і процедура виходить з if. Якщо параметр cur не поранений нулю, перевірка переміщається до блоку elseif, де відбувається порівняння параметра cur з одиницею. Рівність cur одиниці призводить до виконання другого оператора select. Якщо оператор cur приймає будь-яке інше значення, відмінне від 0 і 1, виконується третій запит. Третій запит буде виконаний, навіть якщо cur є негативним значенням.

Кількість блоків else if не обмежена - можна використовувати будь-яке їх число. Додамо в процедуру pricelist () англійський фунт стерлінгів за курсом 20.4 гривні за фунт будемо використовувати значення параметра cur, що дорівнює 3 і вище, для позначення цієї валюти (приклад).

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL

BEGIN

IF(cur = 0) then

SELECT name, price FROM products WHERE id_catalog = id;

ELSEIF (cur = 1) THEN

SELECT name, price/8.8 FROM products WHERE id_catalcg = id;

ELSEIF (cur - 2) THEN

SЕLEСТ name, price/14.5 FROM products WHERE Id_catalog = id;

ELSE

SELECT name, price/20.4 FROM products WHERE id_catalog = id;

END IF;

END //

Проте зловживати блоками elseif не рекомендується, тому що велика кількість блоків else if знижує читабельність коду.

Якщо в блоках if, elseif і else використовується два або більше операторів, для наочності можна вдатися до складеного оператору begin ... end. У прикладі наводиться варіант процедури pricelist () з виводом прайс-листів, що містять ціни в рублях, доларах і євро. Однак для кожного списку буде виводитися загальне число товарних позицій та їх загальна вартість у вибраній валюті для каталогу з первинним ключем id.

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL

BEGIN

IF(cur = 0) THEN

BEGIN

SELECT name, price FROM products WHERE id_catalog = id;

SELECT SUM(count), SUM(price*count) FROM products

WHERE id_catalog = id;

END;

ELSEIF (cur = 1) THEN

BEGIN

SELECT name, price/8.8 FROM products WHERE id_catalog = id;

SELECT SUM(count), SUM(price*count)/8.8 FROM products

WHERE id_catalog = id;

END;

ELSE

BEGIN

SELECT name, price/14.5 FROM products WHERE id_catalog = id;

SELECT SUM(count), SUM(price*count)/14.5 FROM products

WHERE id_catalog = id;

END;

END IF;

END //

Оператор 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

Зауваження:

  • Синтаксис оператора case всередині збереженої процедури трохи відрізняється від синтаксису SQL-виразу CASE. Оператор CASE не може містити конструкціію ELSE NULL, і його виконання завершується за допомогою виразу END CASE, а не END.

У першій формі оператор case порівнює вираз case_value з when value. Як тільки відповідність знайдено, виконується відповідний оператор statement_list. Якщо ні однієї відповідності не знайдено, виконується оператор statement_list, розміщений після ключового слова else (якщо воно, звичайно, присутнє ). Перепишемо процедуру pricelist () з прикладу вище за допомогою оператора case (приклад нижче).

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL

BEGIN

CASE cur

WHEN 0 THEN

SELECT name, price FROM products WHERE id_catalog = id;

WHEN 1 THEN

       SELECT name, price/8.8 FROM products WHERE id_catalog = id;

 WHEN 2 THEN

       SELECT name, price/14.5 FROM products WHERE id_catalog id;

WHEN 3 THEN

SELECT name, price/50.4 FROM products WHERE id_cat.alog = id;

ELSE

       SELECT 'Ошибка в параметре cur';

END CASE;

END //

Ситуація, коли в якості параметра cur передано помилкове значення, оброблена спеціально в блоці ELSE. Підхід із застосуванням оператора if це також передбачає, але в операторі CASE ключове слово ELSE краще виділяється на фоні послідовності ключових слів when порівняно з ключовими словами ELSE IF в операторі IF.

Друга форма оператора CASE дозволяє здійснювати порівняння безпосередньо в конструкції WHEN - як тільки буде знайдено перше істинне значення, виконується оператор statement_list і процедура виходить з оператора CASE. У прикладі представлена ??процедура pricelist (), реалізована з використанням другої форми оператора CASE.

Приклад:

CREATE PROCEDURE pricelist (id INT, cur INT)

LANGUAGE SQL

BEGIN

CASE

WHEN cur = 0 THEN

 

       SELECT name, price FROM products WHERE id_catalog = id;

WHEN cur = 1 THEN

       SELECT name, price/8.8 from products where id_catalog = id;

WHEN cur = 2 THEN

       SELECT name, price/14.5 FROM products WHERE id_catalog = id;

WHEN cur = 3 THEN

       SELECT name, price/20.4 FROM products WHERE id_catalog = id;

ELSE

       SELECT 'Ошибка в параметре cur';

END CASE;

END //

Якщо в одному блоці when необхідно виконати декілька запитів, слід використовувати блок begin ... end.

Оператор WHILE

Оператор WHILE виконує цикл і має наступний синтаксис:

[label:] while search_condition DO

statement_list

END while [label]

Цикл WHILE виконує оператори statement_list до тих пір, поки умова search_condition істинна. При кожній ітерації умова search_condition перевіряється, і якщо при черговій перевірці воно буде хибним (0), цикл завершить своє виконання. Це означає, що якщо умова search_condition хибна з самого початку, цикл не виконає жодної ітерації.

 Якщо в циклі потрібно виконати більше одного оператора, не обов'язково укладати, їх у блок BEGIN ... END, т. к. цю функцію виконує сам оператор WHILE. Виведемо 3 рази поточну дату за допомогою циклу WHILE (приклад).

Приклад:

CREATE PROCEDURE NOW5()

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 3;

WHILE i > 0 DO

SELECT NOW();

SET i = i - 1;

END WHILE;

END //

 

CALL NOW5()//

NOW()

2005-07-18 12:36:59

NOW()

2005-07-18 12:36:59

NOW()

2005-07-18 12:36:59

Перший оператор в циклі WHILE виводить поточну дату, а другий віднімає з локальної змінної i одиницю. Якщо одиницю не віднімати, то утворюється нескінченний цикл, з якого процедура ніколи не вийде, а буде марно навантажувати сервер, поки сеанс з ним не припиниться. Варто дуже уважно проектувати цикли, щоб запобігти нескінченні цикли.

 У прикладі нижче представлений код збереженої процедури, яка виводить поточну дату num число раз, де num - параметр, що задається користувачем.

Приклад:

CREATE PROCEDURE NOWN (IN num INT)

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 0;

IF (num > 0) THEN  wet : WHILE i < num DO SELECT NOW();

SET i = i + 1;

END WHILE wet;

ELSE

SELECT 'Помилкове значення параметра';

END IF;

END //

CALL NOWN(2)//

NOW ()

2005-07-18 12:51:27

NOW ()

2005-07-18 12:51:27

Як видно з попереднього прикладу, цикл WHILE іменований міткою wet. Мітка в циклі призначена не тільки для того, щоб полегшити читання коду при дуже довгих циклах, вона дозволяє здійснювати достроковий вихід з циклу.

Для дострокового виходу з циклу призначений оператор LEAVE, який має наступний синтаксис:

LEAVE label

Оператор LEAVE припиняє виконання блоку, позначеного міткою label.

Зауваження:

  • Оператор LEAVE еквівалентний оператору break в С-подібних мовах програмування

Процедура NOWN () має недолік - якщо задати дуже велике значення аргументу num, можна створити псевдобескінечний цикл, який дозволить зловмиснику завантажити сервер марною роботою. Для запобігання такої ситуації можна скористатися оператором LEAVE, який припинить виконання никла після досягнення критичної числа ітерацій. У лістингу нижче наводиться приклад збереженої процедури, де число ітерації обмежена двома.

Приклад:

CREATE PROCEDURE NOWN (IN num INT)

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 0;

IF (num > 0) THEN

wet : WHILE i < num DO

IF i > 2 THEN LEAVE wet;

END IF;

SELECT N0W();

SET i = i + 1;

END WHILE wet;

ELSE

SELECT 'Помилкове значення параметра';

END IF;

END //

CALL NOWN(10)//

NOW()

2005-07-18 12:51:27

NOW()

2005-07-18 12:51:27

Умова if i> 2 then leave wet; перевіряє, чи не перевищило значення лічильника i число 2, і якщо це так, відбувається припинення циклу while. Використання міток дозволяє точно вказати, який цикл необхідно перервати. Якщо є вкладений цикл, можна явно вказати, який з двох циклів потрібно перервати (приклад).

Приклад:

first : WHILE i < num DO

second : WHILE j < num DO

IF i > 2 && j > 2 THEN LEAVE first;

END IF;

SELECT NOW();

SET j = j + 1;

END WHILE second;

 SET i = i + 1;

END WHILE first;

При досягненні умови i> 2 & & j> 2 оператор LEAVE перерве не вкладений цикл second, а зовнішній цикл first, т. к. мітка зовнішнього циклу явно вказана після оператора.

При використанні дострокового виходу LEAVE можна навіть використовувати нескінченні цикли, тому що рано чи пізно зовнішній цикл буде завершений (приклад).

Приклад:

first : WHILE 1 DO

second : WHILE 1 DO

IF i > 2 && j > 2 THEN LEAVE first;

END IF; SELECT NOW();

SET j = j + 1;

END WHILE second;

SET i - i + 1;

END WHILE first;

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

Ще одним оператором, виконуючим дострокове припинення циклу, є оператор ITERATE, який має наступний синтаксис:

ITERATE label

На відміну від оператора LEAVE, оператор ITERATE не припиняє виконання циклу, він лише виконує дострокове припинення поточної ітерації.

Зауваження:

  • Оператор iterate еквівалентний оператору continue в С-подібних мовах програмування.

Розглянемо програму, яка в циклі формує бінарну послідовність, додаючи до рядка дві одиниці на парних ітераціях та дві одиниці і два нулі на непарних (приклад).

Приклад:

CREATE PROCEDURE binstring (IN num INT)

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE bin TINYTEXT DEFAULT ’’;

IF (num > 0) THEN

wet : WHILE 1 < num DO

SET i = i + 1;

SET bin = CONCAT(bin, 'll');

IF !(i/2 - CEILING(i/2)) THEN ITERATE wet;

END IF;

SET bin = CONCAT(bin, '00');

END WHILE wet;

SELECT bin;

ELSE

SELECT 'Хибне значення параметру';

END IF;

END //

CALL binstring(10)//

bin

110011110011110011110011110011 1

Бінарна послідовність зберігається в тимчасовому рядку bin, який обов'язково має бути ініційований порожній рядком:

DECLARE bin TINYTEXT DEFAULT'';

Якщо ініціалізація не проведена, змінна отримає значення NULL, і всі операції з цією змінною також повертатимуть null. На кожній ітерації змінної bin за допомогою функції CONCAT () додається послідовність '11 '. Якщо індекс i є непарним (1,3,5,7,9), поточний цикл припиняється за допомогою ключового слова iterate, якщо індекс є парним (0,2, 4,6, 8), то ітерація виконується до кінця, т. е. до тимчасового рядку bin додається ще й послідовність '00 '. На парність індекс i перевіряється за допомогою рядка

i / 2 - CEILING (i / 2)

Якщо індекс i ділиться на 2 без залишку, цей вираз поверне 0 (брехня), якщо число є непарним, то вираз поверне 0.5 (істина).

Зауваження:

  • Необхідно стежити, щоб оператор SET, що збільшує значення лічильника i на одиницю, на кожній ітерації був розташований до оператора ITERATE, інакше це призведе до створення нескінченного циклу - значення лічильника буде залишатися не парним і збільшуватися не буде, тому що оператор ITERATE припинятиме виконання ітерації циклу достроково.

Оператор REPEAT

Оператор REPEAT, так само як і оператор WHILE, реалізує цикл:

[label:] REPEAT

statement_list UNTIL search_condition

END REPEAT [label]

Відмінною особливістю даного циклу є той факт, що умова циклу search_condition перевіряється не на початку, як у циклі WHILE, а в кінці оператора (ключове слово UNTIL). Таким чином, цикл виконує, принаймні, одну ітерацію незалежно від умови. Слід зазначити, що цикл REPEAT виконується, поки умова search_concition помилково.

Оператор REPEAT може бути використаний з необов'язковою міткою label, по якій можна здійснювати достроковий вихід з циклу за допомогою операторів LEAVE і ITERATE, розглянутих в попередньому розділі.

У прикладі представлена процедура binrand (), яка генерує і виводить випадкову бінарну послідовність з 20 символів. Для формування бінарної послідовності використовується цикл REPEAT.

Приклад:

CREATE PROCEDURE binrand ()

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE bin TINYTEXT DEFAULT '';

REPEAT

SET i = i + 1;

IF RAND() >0.5 THEN SET bin = CONCAT(bin, ' 1');

ELSE SET bin = CONCAT(bin, '0') ;

END IF;

UNTIL i >= 20

END REPEAT;

SELECT bin;

END //

CALL binrand()//

bin

1010100011110001111110

Оператор LOOP

Оператор LOOP призначений для реалізації циклів і має наступний синтаксис:

 [label:] LOOP

 statement_list END LOOP [label]

Цикл LOOP, на відміну від операторів WHILE і REPEAT, не має умов виходу. Тому даний вид циклу повинен обов'язково мати у своєму складі оператор LEAVE.

Збережену процедуру binrand (), наведену у прикладі, можна переписати з використанням циклу LOOP, як це зроблено в наступному лістингу.

Приклад:

CREATE PROCEDURE binrand ()

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE bin TINYTEXT DEFAULT '';

wet : LOOP

SET i = i + 1;

IF RANDO >0.5 THEN SET bin = CONCAT(bin, ' 1') ;

ELSE SET bin = CONCAT(bin, '0');

END IF;

IF i >= 20 THEN LEAVE wet;

END IF;

END LOOP wet;

SELECT bin;

END //

CALL binrand()//

bin

01000111011110001110

Оператор GOTO

Оператор goto дозволяє здійснювати безумовний перехід і має наступний синтаксис:

 GOTO label;

Оператор goto здійснює перехід до оператора, позначеного міткою label. Це може бути як оператор begin, так і будь-який з циклів: while, repeat та loop. Крім того, мітка може бути не прив'язана ні до одного з операторів процедури, а оголошена за допомогою оператора label, який має наступний синтаксис:

 LABEL label;

У загальному випадку використання операторів goto і label може виглядати так, як це представлено у прикладі.

Приклад:

CREATE PROCEDURE binrand ()

BEGIN

LABEL labelname;

...

GOTO labelname;

END

Оператор LABEL не виконує ніяких дій - завдання цього оператора просто оголосити мітку labelname. На місці цього оператора можна помістити будь-який оператор, що допускає використання в своєму складі мітки (BEGIN, WHILE, REPEAT та LOOP). Досягаючи оператора GOTO, процедура слідує до посилання і переходить назад до мітки labelname. Таким чином, у прикладі реалізований безкінечний цикл. Зазвичай перехід по мітці GOTO використовують спільно з оператором IF, так як це продемонстровано в наступному прикладі, де представлена ??реалізація процедури binrand () з використанням оператора безумовного переходу GOTO.

Приклад:

CREATE PROCEDURE binrand ()

LANGUAGE SQL

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE bin TINYTEXT DEFAULT ’’;

LABEL wet;

SET i = i + 1;

IF RAND() >0.5 THEN SET bin = CONCAT(bin, '1');

ELSE SET bin = CONCAT(bin, '0');

END IF;

IF(i < 20) THEN GOTO wet;

END IF;

SELECT bin;

END //

CALL binrand () //

bin

00001010001110111100

Однак використовувати оператор GOTO для реалізації циклів не рекомендується, тому що звичайні цикли набагато наочніше і простіше піддаються модифікації, до того ж у них складніше допустити логічну помилку.

Часто взагалі рекомендується не використовувати оператор GOTO, оскільки він підштовхує до створення погано структурованого коду. Це не виключає можливості створення читабельного та структурованого коду з використанням оператора GOTO, але спокуса неправильної реалізації цього оператора занадто велика. Крім того, будь-який код можна переписати без використання GOTO, деякі сучасні мови програмування (наприклад, РНР), взагалі не включають його у свій склад.

Метадані.

Існує три способи переглянути дані, пов'язані з збереженим процедурам або функціями:

  1. Оператори show procedure status / show function status.
  2. Оператори show create procedure / show create function.
  3. Запит SELECT from mysq.proc.

Оператор SHOW PROCEDURE STATUS

Переглянути список вже створених збережених процедур можна за допомогою оператора show procedure status, який має наступний синтаксис:

SHOW PROCEDURE STATUS [LIKE 'pattern'];

Оператор повертає список збережених процедур, який не включає збережених функцій. При використанні ключового слова LIKE можна вивести інформацію тільки про ті процедури, імена яких задовольняють шаблону pattern.

Приклад:

SHOW PROCEDURE STATUS LIKE 'bin%';

Db: shop

Name: binrand

Type: PROCEDURE

Definer: root@localhost

Modified: 2005-07-18 23:43:23

Created: 2005-07-18 23:43:23

Security_type: DEFINER

Comment:

Db: shop

Name: binstring

Type: PROCEDURE

Definer: root@localhost

Modified: 2005-07-18 14:09:55

Created: 2005-07-18 14:09:55

Security _type: DEFINER

Comment:

Як видно з прикладу, оператор повертає результуючу таблицю, в якій кожен рядок відповідає одній збереженій процедурі. При цьому таблиця містить 8 таких стовпців:

  1. Db - ім'я бази даних, в яку збережена процедура;
  2. Name - ім'я процедури;
  3. Tуре - тип збереженої процедури, приймає значення PROCEDURE для оператора процедури та FUNCTION для збереженої функції (список збережених функцій повертається оператором SHOW FUNCTION STATUS, який розглядається нижче);
  4. Definer - обліковий запис, від імені якої була створена процедура;
  5. Modified-дата останньої модифікації процедури;
  6. Created - дата створення збереженої процедури;
  7. Security type-режим виконання збереженої процедури. Якщо це поле приймає значення DEFINER, то процедура виконується з правами доступу користувача, який створив цю процедуру. Якщо поле Security_type приймає значення INVOKER, то процедура виконується з правами доступу користувача, що викликає процедуру за допомогою оператора CALL.
  8. Comment - коментар до збереженої процедури.

Для перегляду списку збережених функцій призначений оператор SHOW FUNCTION STATUS. Оператор має наступний синтаксис:

SHOW FUNCTION STATUS [LIKE 'pattern'];

Оператор виводить список збережених функцій, що не включає до свого складу збережених процедур (приклад). При використанні ключового слова LIKE можна вивести інформацію тільки про ті функції, імена яких задовольняють шаблону pattern.

Приклад:

SHOW FUNCTION STATUS LIKE 'count%';

Db: shop

Name: count_product

Type: FUNCTION

Definer: root@localhost

Modified: 2005-07-17 01:26:41

Created: 2005-07-17 01:26:4

Security_type: DEFINER

Comment:

Db: shop

Name: count_product_in_catalog

Type: FUNCTION

Definer: root@localhost

Modified: 2005-07-17 01:26:40

Created: 2005-07-17 01:26:40

Securitytype: DEFINER

Comment:

Формат виведення оператора SHOW FUNCTION STATUS збігається з форматом SHOW PROCEDURE STATUS, розглянутому раніше.

Оператор SHOW CREATE

Ще одним оператором, який дозволяє отримати інформацію о збережених процедурах, є оператор SHOW CREATE PROCEDURE, який має наступний синтаксис:

SHOW CREATE PROCEDURE procname;

Оператор виводить синтаксис оператора CREATE PROCEDURE, за допомогою якого була створена процедура procname (приклад).

Приклад:

mysql> SHOW CREATE PROCEDURE binstring\G;

Procedure: binstring

sql_mode:

Create Procedure: CREATE PROCEDURE 'shop'.'binstring'(IN num INT)

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE bin TINYTEXT DEFAULT '';

IF (num > 0) THEN

wet : WHILE i < num DO SET i = + 1;

SET bin = CONCAT(bin, '11');

IF !(i/2 - CEILING(i/2)) THEN ITERATE wet;

END IF;

SET bin = CONCAT(bin, '00');

END WHILE wet;

SELECT bin;

ELSE

SELECT 'Помилкове значення параметра';

END IF;

END

 

Оператор SHOW CREATE PROCEDURE виводить інформацію тільки для збережених процедур, для збережених функцій необхідно скористатися оператором SHOW CREATE FUNCTION, який має наступний синтаксис:

 SHOW CREATE FUNCTION funcname;

Оператор виводить синтаксис оператора CREATE FUNCTION, за допомогою якого була створена процедура funcname.

Видалення збережених процедур

Для видалення збережених процедур використовується синтаксис оператора DROP PROCEDURE, який має наступний синтаксис:

DROP PROCEDURE [IF EXISTS] nameproc

Оператор DROP ROCEDURE дозволяє видалити збережену процедуру nameproc. Якщо процедури з таким ім'ям не існує, синтаксис оператора повертає помилку, яку можна поглинути, якщо використовувати необов'язкове ключове слово IF EXISTS.

Створимо в базі даних test збережену процедуру test () і застосуємо до неї синтаксис оператора DROP PROCEDURE (приклад).

Приклад:

CREATE PROCEDURE test() SELECT VERSION();

DROP PROCEDURE test;

Проте використання оператора DROP PROCEDURE стосовно збереженої функції закінчується помилкою

Для удаления хранимых функций необходимо использовать специальный оператор DROP FUNCTION

Приклад:

DROP FUNCTION test;

Редагування збережених процедур

Для зміни характеристик процедури призначений синтаксис ОПЕРАТОРА ALTER PROCEDURe. Редагування збереженої функції виконується за допомогою оператора ALTER FUNCTION. Оператори мають наступний синтаксис:

 ALTER PROCEDURE sp_name [characteristic ...]

 ALTER FUNCTION sp_name [characteristic ...]

Характеристика characteristic може приймати такі значення:

  1. SQL SECURITY (DEFINER | INVOKER) - даний парамеир визначає режим виконання: процедура виконується або з правами створившого користувача, (DEFINER), або з правами користувача, що викликав її (INVOKER).
  2. COMMENT 'string' - даний параметр дозволяє призначити коментар для процедури.

Зауваження:

  • Для виконання операторів ALTER PROCEDURE та ALTER FUNCTION необхідно володіти привілеєм ALTER ROUTINE. Даний привілей автоматично передається користувачеві, що створив збережену процедуру.

Приклад:

ALTER PROCEDURE test

SQL SECURITY INVOKER

COMMENT 'Функція повертає версію сервера';

Tags: 
mySQL (4 курс)