Заняття 8. Збережені процедури. Користувацькі функції

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

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

на клієнтських комп’ютерах
Збережені процедури реєструються на сервері.
Збережені процедури можуть

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

і за необхідністю викликати її будь-яку кількість раз. Це робить

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

CURRENT_USER }]
PROCEDURE ([ [,...]])
[ ...]

Параметр

процедури:
[ IN | OUT | INOUT ] <ім'я параметра> <тип даних>

інструкцій SQL будь-якого типу, окрім наступних:
CREATE або ALTER FUNCTION
CREATE TRIGGER
CREATE

або ALTER PROCEDURE
CREATE або ALTER VIEW
CREATE AGGREGATE; CREATE RULE; CREATE DEFAULT; CREATE SCHEMA;
SET PARSEONLY; SET SHOWPLAN_ALL; SET SHOWPLAN_TEXT; SET SHOWPLAN_XML;
USE
Усі решта об’єктів можна створювати всередині збереженої процедури.
До створеного в процедурі об’єкту можна звертатись, як і до будь-якого іншого об’єкту БД.

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

лише в ній і при виході з процедури видалиться.
При виконанні збереженої процедури, яка викликає іншу збережену процедуру, остання може звертатись до всіх об’єктів, які створені першою, включаючи тимчасові таблиці.
Максимальна кількість параметрів у збереженої процедури – 2100.
Максимальна кількість локальних змінних обмежується лише доступною пам’яттю.
В залежності від доступної пам’яті максимальний розмір збереженої процедури може досягати 128 МБ.

процедур необхідно мати дозвіл для CREATE PROCEDURE в базі даних

і дозвіл ALTER у відповідній схемі.
Для підвищення швидкодії всі об’єкти, які використовуються у межах однієї збереженої процедури, повинні належати її власнику, яким є власник БД. В іншому випадку витрачається багато часу на перевірку прав доступу. Найефективніше створити збережену процедуру від імені власника БД і призначати іншим користувачам права на її виконання всюди, де це можливо.
Збережені процедури є об’єктами схеми, і їх імена повинні відповідати вимогам до імен ідентифікаторів. При повному форматі імені процедури (вказання її схеми) ядру СУБД не потрібно шукати процедуру в декількох схемах.

proc (IN varname DATA-TYPE): один вхідний параметр. (Слово IN необов'язкове

тому, що параметри по замовчуванню – вхідні).
CREATE PROCEDURE proc (OUT varname DATA-TYPE): один вихідний параметр.
CREATE PROCEDURE proc (INOUT varname DATA-TYPE): один параметр, що одночасно є вхідним і вихідним.

збережену процедуру, яка виводить усі дані із таблиці Sellers:
CREATE PROCEDURE

proc()
BEGIN
SELECT * FROM Sellers;
END;

яка виводить усі дані із таблиці про покупця із номером

1002:
CREATE PROCEDURE proc2(IN selnum INT)
BEGIN
SELECT * FROM Sellers WHERE snum = selnum;
END;

яка виводить кількість продавців, які містяться у таблиці Sellers:
CREATE PROCEDURE

proc3(OUT selcount INT)
BEGIN
SELECT COUNT (snum) FROM Sellers INTO selcount;
END;

команди DECLARE.
Після того, як змінна була оголошена можна задати їй

значення за допомогою команд SET або SELECT.

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

PROCEDURE proc4()
BEGIN
DECLARE last_month DATE;
SET last_month = CURRENT_DATE();
SELECT * FROM orders
WHERE MONTH(odate) = MONTH(last_month) AND YEAR(odate) = YEAR(last_month);
END;

процедуру, необхідно після команди CALL вказати назву процедури, а в

дужках вказати параметри (змінні або значення).
Для вхідних параметрів в дужках вказуються значення, для вихідних – змінні.
Для виводу значення вихідного параметру, що міститься у змінній необхідно використати команду SELECT.
CALL proc (param1, param2, ....);
CALL proc1(10 , 'string parameter' , @parameter_var);

можна видалити її і створити заново. При цьому всі права

доступу будуть втрачені.
При безпосередньому редагуванні можна змінити інструкції і параметри, а права доступу залишаться, а також залишаться залежні від неї процедури або тригери. Для цього використовується команда ALTER PROCEDURE.
Зміна імені або визначення збереженої процедури може призвести до того, що усі залежні від неї об’єкти при виконанні будуть повертати помилку, якщо вони не були оновлені у відповідності зі змінами, внесеними в процедуру.

командою DROP PROCEDURE.
Якщо на видалену процедуру посилається інша збережена процедура,

то при її виклику СУБД відобразить повідомлення про помилку. Однак, якщо замість видаленої визначити іншу збережену процедуру з таким же ім’ям і параметрами, то процедури, які на неї посилаються будуть виконуватись успішно.

повертають значення на місце своїх імен, і їх не можна

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

функцію;
у визначенні іншої користувацької функції;
для параметризації віртуальної таблиці (view);
для визначення

стовпця таблиці;
для визначення обмеження CHECK на стовпець;
для заміни збереженої процедури;

CURRENT_USER }]
FUNCTION ([ [,...]])
RETURNS
[...]

Параметр функції:
<ім'я параметра> <тип даних>

продавців, які містяться у таблиці Sellers:
CREATE FUNCTION func()
RETURNS INT
BEGIN
DECLARE selcount

INT;
SELECT COUNT(snum) FROM Sellers INTO selcount;
RETURN selcount;
END;

LOOP, WHILE і REPEAT для управління потоками в межах збереженої

процедури.
Багато з цих операторів містять інші інструкції, як визначено специфікаціями MySQL.
Такі оператори можуть бути вкладені. Наприклад, IF міг би містити цикл часу, який безпосередньо містить WHILE, який у свою чергу включає в себе оператор CASE.
Цикли FOR в MySQL не забезпечені.

операторів виконання>
[ELSEIF THEN ] ...
[ELSE

операторів виконання>]
END IF

продавців відповідно до сум, які вони виручили за операції купівлі-продажу:
CREATE

PROCEDURE Sel_Status (IN selnum INT, OUT selstatus VARCHAR(10))
BEGIN
DECLARE sum_amt double;
SELECT SUM(amt) INTO sum_amt FROM orders WHERE snum = selnum;
IF(sum_amt) > 10000 THEN SET selstatus = 'Excellent';
ELSEIF ((sum_amt) <= 10000 AND (sum_amt) >= 4000) THEN SET selstatus = 'Good';
ELSE SET selstatus = 'Bad';
END IF;
END;

[WHEN THEN ] ...
[ELSE

<список операторів виконання>]
END CASE

статус продавців відповідно до сум, які вони виручили за операції

купівлі-продажу:
CREATE PROCEDURE Sel_Status (IN selnum INT, out selstatus varchar(10))
BEGIN
DECLARE sum_amt double;
SELECT SUM(amt) INTO sum_amt FROM orders WHERE snum = selnum;
CASE
WHEN (sum_amt > 10000) THEN SET selstatus = 'Excellent';
WHEN ((sum_amt) <= 10000 AND (sum_amt) >= 4000) THEN SET selstatus = 'Good';
ELSE SET selstatus = 'Bad';
END CASE;
END;

DO

END WHILE []

INT)
BEGIN
SET @x = 0;
WHILE (@x

p2;
SET @x = @x + 1;
END WHILE;
END

END REPEAT []

INT)
BEGIN
SET @x = 0;
REPEAT
SELECT @x INTO p2;
SET @x = @x

+ 1;
UNTIL @x > p1 END REPEAT;
END

LOOP []

Щоб запустити цикл необхідно використати оператор ITERATE

(може також використовуватися із іншими операторами циклу).

Щоб вийти із циклу необхідно використати оператор LEAVE <мітка> (може також використовуватися із іншими операторами циклу).

INT)
BEGIN
SET @x = 0;
label1: LOOP
SET @x = @x + 1;
IF

@x < 5 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SELECT @x into p2;
END;

збережену процедуру без параметрів;
Створити 2 збережені процедури із вхідним параметром;
Створити

1 збережену процедуру із вихідним параметром;
Створити 2 користувацькі функції.