Sql - урок 15

Головна → Уроки SQL → Урок 15. Процедури, що зберігаються. Створення, використання і видалення.

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

CREATE PROCEDURE імя_процедури (параметри) begin оператори end

Параметри це ті дані, які ми будемо передавати процедурі при її виклику, а оператори - це власне запити. Давайте напишемо свою першу процедуру і переконаємося в її зручність. В уроці 10. коли ми додавали нові записи в БД shop, ми використовували стандартний запит на додавання виду:

INSERT INTO customers (name, email) VALUE ( 'Іванов Сергій', '[email protected]');

Оскільки подібний запит ми будемо використовувати кожен раз, коли нам необхідно буде додати нового покупця, то цілком доречно оформити його у вигляді процедури:

CREATE PROCEDURE ins_cust (n CHAR (50), e CHAR (50)) begin insert into customers (name, email) value (n, e); end

Зверніть увагу, як задаються параметри: необхідно дати ім'я параметру і вказати його тип, а в тілі процедури ми вже використовуємо імена параметрів. Один нюанс. Як ви пам'ятаєте, крапка з комою означає кінець запиту і відправляє його на виконання, що в даному випадку неприйнятно. Тому, перш, ніж написати процедуру необхідно перевизначити роздільник с; на "//", щоб запит не рушив завчасно. Робиться це за допомогою оператора DELIMITER //.

Таким чином, ми вказали СУБД, що виконувати команди тепер слід після //. Слід пам'ятати, що перевизначення роздільник здійснюється тільки на один сеанс роботи, тобто при наступному сеансі роботи з MySql роздільник знову стане крапкою з комою і при необхідності його доведеться знову перевизначати. Тепер можна розмістити процедуру:

CREATE PROCEDURE ins_cust (n CHAR (50), e CHAR (50)) begin insert into customers (name, email) value (n, e); end //

Отже, процедура створена. Тепер, коли нам знадобиться ввести нового покупця нам досить її викликати, вказавши необхідні параметри. Для виклику процедури, що використовується оператор CALL. після якого вказується ім'я процедури і її параметри. Давайте додамо нового покупця в нашу таблицю Покупці (customers):

call ins_cust ( 'Сичов Валерій', '[email protected]') //

Погодьтеся, що так набагато простіше, ніж писати кожен раз повний запит. Перевіримо, чи працює процедура, подивившись, чи з'явився новий покупець в таблиці Покупці (customers):

З'явився, процедура працює, і працюватиме завжди, поки ми її не видалимо за допомогою оператора DROP PROCEDURE названіе_процедури.

Як було сказано на початку уроку, процедури дозволяють об'єднати послідовність запитів. Давайте подивимося, як це робиться. Пам'ятайте в уроці 11 ми хотіли дізнатися, на яку суму нам привіз товар постачальник "Будинок друку"? Для цього нам довелося використовувати вкладені запити, об'єднання, обчислювані стовпці та подання. А якщо ми захочемо дізнатися, на яку суму нам привіз товар інший постачальник? Доведеться складати нові запити, об'єднання і т.д. Простіше один раз написати збережену процедуру для цього дії.

Здавалося б, найпростіше взяти вже написані в уроці 11 подання та запит до нього, об'єднати в збережену процедуру і зробити ідентифікатор постачальника (id_vendor) вхідним параметром, ось так:

CREATE PROCEDURE sum_vendor (i INT) begin CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND id_incoming = ( SELECT id_incoming FROM incoming WHERE id_vendor = i); SELECT SUM (summa) FROM report_vendor; end //

Але так процедура працювати не буде. Вся справа в тому, що в уявленнях не можуть використовуватися параметри. Тому нам доведеться дещо змінити послідовність запитів. Спочатку ми створимо подання, яке буде виводити ідентифікатор постачальника (id_vendor), ідентифікатор продукту (id_product), кількість (quantity), ціну (price) і суму (summa) з трьох таблиць Поставки (incoming), Журнал поставок (magazine_incoming), Ціни ( prices):

CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND magazine_incoming.id_incoming = incoming .id_incoming;

А потім створимо запит, який підсумує суми поставок цікавить нас постачальника, наприклад, з id_vendor = 2:

SELECT SUM (summa) FROM report_vendor WHERE id_vendor = 2;

Ось тепер ми можемо об'єднати два цих запиту в збережену процедуру, де вхідним параметром буде ідентифікатор постачальника (id_vendor), який буде підставлятися в другій запит, але не в уявлення:

CREATE PROCEDURE sum_vendor (i INT) begin CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = prices .id_product AND magazine_incoming.id_incoming = incoming.id_incoming; SELECT SUM (summa) FROM report_vendor WHERE id_vendor = i; end //

Перевіримо роботу процедури, з різними вхідними параметрами:

call sum_vendor (1) // call sum_vendor (2) // call sum_vendor (3) //

Як бачите, процедура спрацьовує один раз, а потім видає помилку, кажучи нам, що уявлення report_vendor вже є в БД. Так відбувається тому, що при зверненні до процедури в перший раз, вона створює уявлення. При зверненні вдруге, вона знову намагається створити уявлення, але воно вже є, тому і з'являється помилка. Щоб уникнути цього можливі два варіанти.

Перший - винести уявлення з процедури. Тобто ми один раз створимо уявлення, а процедура буде лише до нього звертатися, але не створювати його. Попередньо не забуде видалити вже створену процедуру і уявлення:

DROP PROCEDURE sum_vendor // DROP VIEW report_vendor // CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND magazine_incoming.id_incoming = incoming.id_incoming // CREATE PROCEDURE sum_vendor (i INT) begin SELECT SUM (summa) FROM report_vendor WHERE id_vendor = i; end //

call sum_vendor (1) // call sum_vendor (2) // call sum_vendor (3) //

Другий варіант - прямо в процедурі дописати команду, яка буде видаляти уявлення, якщо воно існує:

CREATE PROCEDURE sum_vendor (i INT) begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND magazine_incoming.id_incoming = incoming .id_incoming; SELECT SUM (summa) FROM report_vendor WHERE id_vendor = i; end //

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

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

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