Процедура MySQL являє собою підпрограму, що зберігається в базі даних. Вона містить ім'я, список параметрів і оператори SQL. Всі популярні системи управління базами даних підтримують збережені процедури. Вони були введені в MySQL 5.
Існує два види підпрограм: процедури, що і функції, які повертають значення, які використовуються в інших операторах SQL (наприклад, pi ()).
Основна відмінність полягає в тому, що функції можуть використовуватися, як будь-яке інше вираження в операторах SQL. а збережені процедури повинні викликатися за допомогою оператора CALL.
У чому перевага збережених процедур?
- Збережені процедури працюють швидко. Перевага сервера MySQL полягає в тому, що він використовує кешування, а також заздалегідь задані оператори. Основний приріст швидкості дає скорочення мережевого трафіку. Якщо є повторювані завдання, які вимагають перевірки, обробки циклів, декількох операторів, і при цьому не вимагають взаємодії з користувачем, це можна реалізувати за допомогою одного виклику процедури, яка зберігається на сервері;
- MySQL збережені процедури є універсальними. При написанні збереженої процедури на SQL вона буде працювати на будь-якій платформі, яка використовує MySQL. У цьому перевага SQL над іншими мовами, такими як Java. C або PHP;
- Вихідний код збережених процедур завжди доступний в базі даних. Це ефективна практика зв'язати дані з процесами, які їх обробляють.
створення процедури
За замовчуванням процедура пов'язана з базою даних, використовуваної в даний момент. Щоб зв'язати процедуру з конкретною базою даних, вкажіть її створенні збереженої процедури: імя_бази_данних.імя_хранімой_процедури. Повний синтаксис:
Перед тим, як здійснити MySQL виклик збереженої процедури. необхідно отримати певну інформацію.
Перевірка версії MySQL
Наступна команда виводить версію MySQL:
Перевірка привілеїв поточного користувача
Для команд CREATE PROCEDURE і CREATE FUNCTION потрібні привілей користувача CREATE ROUTINE. Також може знадобитися привілей SUPER. це залежить від значення DEFINER. яке буде описано далі. Якщо включений бінарний лог для CREATE FUNCTION. то може знадобитися привілей SUPER. За замовчуванням MySQL автоматично надає для творця підпрограми привілеї ALTER ROUTINE і EXECUTE. Таку поведінку можна змінити, відключивши системну змінну automatic_sp_privileges:
Вибір бази даних
Тепер виберіть базу даних "hr" і виведіть список таблиць:
вибір роздільник
Роздільник - символ або рядок символів, яка використовується для закриття оператора SQL. За замовчуванням як роздільник крапку з комою (;). Але це викликає проблеми в збережених процедурах і тригерах MySQL. оскільки вона може мати багато операторів, і кожен повинен закінчуватися крапкою з комою. Тому як роздільник будемо використовувати подвійний знак долара - $$. Щоб пізніше знову використовувати як роздільник ";" виконайте команду "DELIMITER; $$". Нижче наведено код для зміни роздільник:
Тепер DELIMITER за замовчуванням - "$$". Виконаємо просту команду SQL:
Тепер виконайте наступну команду, щоб знову встановити ";" як роздільник:
Приклад процедури MySQL
Ми створимо просту MySQL процедуру під назвою job_data. при виконанні вона буде виводити всі дані з таблиці "jobs":
пояснення:- Команда CREATE PROCEDURE створює збережену процедуру;
- Наступна частина - це ім'я процедури "job_data";
- Імена процедур не чутливі до регістру, тому job_data рівносильно JOB_DATA;
- Не можна використовувати дві процедури з одним ім'ям в одній і тій же базі даних;
- Можна використовувати імена в форматі "ім'я-процедури.імя-бази-даних", наприклад, "hr.job_data";
- Імена процедур можуть бути розділені. Якщо ім'я розділене, воно може містити пробіли;
- Максимальна довжина імені процедури становить 64 символу;
- Уникайте використання імен вбудованих функцій MySQL;
- Остання частина "CREATE PROCEDURE" - це пара дужок містить список параметрів. Оскільки ця процедура не має ніяких параметрів, список порожній;
- Наступна частина SELECT * FROM JOBS; $$ - це останній оператор в синтаксисі збережених процедур MySQL. Крапка з комою (;) тут не є обов'язковою, тому що реальним закінченням оператора є $$.
Інструменти для створення процедур MySQL
Можна написати процедуру за допомогою інструменту командного рядка MySQL або за допомогою MySQL Workbench.
Інструмент командного рядка MySQL:
Виберіть з меню «Пуск» «Клієнт командного рядка MySQL»:
Ви побачите на екрані наступне вікно:
Тепер можна писати і запускати власні процедури, дивіться наступний приклад:
MySQL Workbench (5.3 CE):
Виберіть в меню «Пуск» «MySQL Workbench»:
Після цього ви побачите на екрані наступне вікно:
Введіть свої облікові дані:
Після цього клікніть правою кнопкою миші по пункту «Routines» і на екрані з'явиться нове спливаюче вікно:
Після цього на екрані з'явиться наступне вікно, в якому можна створити власну процедуру MySQL:
Після того, як напишете процедуру, натисніть кнопку «Apply» і на екрані з'явиться наступне вікно:
Тепер натисніть на кнопку «Finish» і запустіть процедуру:
виклик процедури
Оператор CALL використовується для виклику процедури, яка зберігається в базі даних. Синтаксис наступний:
MySQL збережені процедури, які не приймають аргументів, можуть викликатися без дужок. Тому CALL job_data () рівносильно CALL job_data.
Давайте виконаємо процедуру:
SHOW CREATE PROCEDURE
Цей оператор є розширенням MySQL. Він повертає точну рядок, яка може бути використана, щоб відтворити зазначену процедуру, що зберігається. Синтаксис наступний:
Давайте здійснимо MySQL виклик збереженої процедури:
MySQL: блоки характеристик
У синтаксисі оператора CREATE PROCEDURE допустимо використання блоків, які описують характеристики процедури. Блоки вказуються після дужок, але перед тілом процедури. Ці блоки є необов'язковими.
Характеристика COMMENT - це розширення MySQL. Вона використовується для опису зберігається підпрограми, і дана інформація відображається за допомогою оператора SHOW CREATE PROCEDURE.
Характеристика LANGUAGE вказує на те, що тіло процедури написано на SQL.
NOT DETERMINISTIC
Це інформаційна характеристика. Процедура вважається "детермінованою", якщо вона завжди дає той же результат для одних і тих же вхідних параметрів, інакше вона є "не детермінованою".
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
CONTAINS SQL означає, що в збереженій процедурі MySQL немає ніяких заяв, які зчитують або записують дані. Наприклад, заяви SET @x = 1 або DO RELEASE_LOCK ( 'abc'). вони виконуються, але не зчитують і не записують дані. Це значення за замовчуванням, якщо не вказано інше значення характеристики.
NO SQL означає, що процедура не містить операторів SQL.
READS SQL DATA - процедура містить оператори, які зчитують дані (наприклад, SELECT), але не містить операторів, які записують дані.
MODIFIES SQL DATA-означає. що підпрограма містить оператори, які можуть записувати дані (наприклад, INSERT або DELETE).
Всі перераховані блоки характеристик мають значення за замовчуванням. Наступні два оператора дають однаковий результат:
те ж саме, що:
Перш, ніж перейти до параметрів MySQL. розглянемо кілька складових операторів MySQL.
MySQL: складові оператори
У цьому розділі ми розглянемо перші чотири оператори, пов'язані з параметрами оператора CREATE PROCEDURE.
Синтаксис складеного оператора BEGIN. END
Він використовується, коли потрібно розмістити в межах підпрограми (наприклад, процедури, що MySQL. Функції, тригера або події) більше одного оператора. Синтаксис наступний:
спісок_операторов: один або кілька операторів, що завершуються крапкою з комою (;). Сам по собі список операторів не є обов'язковим, тому порожній оператор BEGIN END є дійсним.
Мітки операторів
Мітки - це дозволу на виконання для блоків BEGIN. END і операторів циклу REPEAT і WHILE. Синтаксис наступний:
При застосуванні міток застосовуються такі правила:- метка_начала повинна закриватися двокрапкою;
- метка_начала може використовуватися без меткі_конца. Якщо метка_конца присутній, вона повинна належати тому самому блоку, що і метка_начала;
- метка_конца не може використовуватися без меткі_начала;
- мітки, що належать до одного вкладеного рівню, повинні бути розділені;
- мітки можуть мати довжину не більше 16 символів.
оператор DECLARE
Використовується для визначення різних локальних елементів при MySQL створенні збереженої процедури. Наприклад, локальних змінних, умов, обробників, курсорів. DECLARE використовується тільки всередині складеного оператора BEGIN. END і повинен перебувати в його початку перед усіма іншими операторами.
Змінні в збережених програмах
Щоб надати значення для змінної за замовчуванням, використовується блок DEFAULT. Значення може бути задано як вираз; це не обов'язково повинна бути константа. Якщо блок DEFAULT відсутня, початкове значення дорівнює NULL.
Приклад: Локальні змінні
Локальні змінні оголошуються всередині збережених процедур MySQL. Вони дійсні тільки в межах блоку END. BEGIN. в якому вони оголошені. Локальні змінні можуть містити будь-який тип даних SQL. У наступному прикладі показано використання локальних змінних в збереженій процедурі:
Тепер виконайте процедуру:
Приклад: змінні
У збережених процедурах MySQL звернення до призначених для користувача змінним відбувається через символ амперсанда (@) перед ім'ям користувача змінній (наприклад, @x і @y). У наступному прикладі показано використання призначених для користувача змінних всередині збереженої процедури:
MySQL: параметри процедури
Нижче наводиться синтаксис CREATE PROCEDURE для параметрів:
Варіанти синтаксису:- CREATE PROCEDURE імя_процедури ().
- CREATE PROCEDURE імя_процедури ([IN] імя_параметра type).
- CREATE PROCEDURE імя_процедури ([OUT] імя_параметра type).
- CREATE PROCEDURE імя_процедури ([INOUT] імя_параметра type).
- У першому прикладі список параметрів порожній.
- У другому прикладі параметр IN передає значення в процедуру. Ця процедура може змінити значення. Але, коли процедура повертає значення, воно не буде видно для викликає агента.
- У третьому прикладі параметр OUT передає значення з процедури назад викликає агенту. Його початкове значення в процедурі NULL. і, коли процедура повертає значення, воно видно викликає агенту.
- У четвертому прикладі параметр INOUT инициализируется викликає агентом, він може бути змінений процедурою, і коли процедура повертає значення, будь-які зміни, зроблені MySQL збереженої процедурою, буде видно викликає агенту.
У процедурі кожен параметр за замовчуванням є параметром IN. Щоб змінити, це використовуйте перед ім'ям параметра ключове слово OUT або INOUT.
Процедура MySQL: приклад параметра IN
У наступній процедурі використаний параметр IN "var1" (тип ціле число), який приймає число від користувача. У тілі процедури є оператор SELECT. який вибирає рядки з таблиці "jobs". Кількість рядків вказується користувачем. Нижче наводиться процедура:
Щоб вибрати перші два рядки з таблиці "jobs" виконайте наступну команду:
Тепер виберіть перші п'ять рядків з таблиці "jobs":
Процедура MySQL: приклад параметра OUT
Далі представлений MySQL збереженої процедури приклад, в якому використовується параметр OUT. В рамках процедури MySQL функція MAX () витягує максимальну зарплату з шпальти MAX_SALARY таблиці "jobs":
У тілі процедури параметр отримує найвищу зарплату з шпальти MAX_SALARY. Після виклику процедури слово OUT повідомляє СУБД. що значення виходить від процедури. highest_salary - це ім'я вихідного параметра і в операторі CALL ми передали його значення змінної сеансу з ім'ям @M:
Процедура MySQL: Приклад параметра INOUT
У наступному прикладі показана проста процедура, що зберігається MySQL. яка використовує параметр INOUT і параметр IN. Користувач надає 'M' або 'F' через параметр IN (emp_gender) для підрахунку кількості співробітників чоловічої або жіночої статі з таблиці user_details. Параметр INOUT (mfgender) повертає результат користувачеві. Ось код і результат виконання процедури:
Тепер перевіряємо кількість співробітників чоловічої і жіночої статі у зазначеній таблиці:
MySQL: Оператори керування потоком
MySQL підтримує конструкції для управління потоком даних в збережених програмах IF. CASE. ITERATE. LEAVE. LOOP. WHILE і REPEAT. Також підтримується RETURN всередині збережених процедур MySQL.
MySQL: Оператор IF
Оператор IF реалізує базову конструкцію умови, він повинен закінчуватися крапкою з комою. Існує також функція IF (), яка відрізняється від оператора IF. Ось синтаксис оператора IF:
Якщо умова виконується, виконуються оператори відповідних блоків THEN або ELSE IF.
Якщо умова не задовольняється, виконуються оператори блоку ELSE. Кожен оператор складається з одного або декількох операторів SQL; порожні оператори не допускається.
У наступному прикладі ми передаємо через параметр IN user_id. щоб отримати ім'я користувача. В рамках процедури ми використовували оператори IF ELSE IF і ELSE. щоб отримати ім'я користувача з безлічі ідентифікаторів користувачів. Ім'я користувача буде зберігатися в параметрі user_name INOUT:
Здійсніть MySQL виклик збереженої процедури:
MySQL: Оператор CASE
Оператор CASE використовується для створення всередині збереженої процедури MySQL складної умовної конструкції. Оператор CASE не може містити блок ELSE NULL і повинен закриватися END CASE. а не END. синтаксис:
Пояснення: перший синтаксис
Значення - це вираз, який порівнюється зі значенням в кожному блоці WHEN. поки вони не будуть рівні. При знайденому відповідно значень виконується спісок_операторов відповідного блоку THEN.
Якщо значення не рівні, тоді виконується спісок_операторов блоку ELSE. (Якщо такий є).
Пояснення: другий синтаксис
Кожен вираз блоку условіе_поіска оцінюється. поки одне з них не було це слово. У цей момент виконується спісок_операторов відповідного блоку THEN.
Якщо жодне з виразів условіе_поіска неправдиве, тоді виконується спісок_операторов блоку ELSE. якщо такий є. Кожен спісок_операторов складається з одного або декількох операторів SQL; порожній спісок_операторов не допускається.
У нас є таблиця під назвою 'jobs' з наступними записами:
Підрахуємо кількість співробітників, які відповідають таким умовам:
- MIN_SALARY> 10000
- MIN_SALARY