Изучаем хранимые процедуры MySQL

Хранимая процедура MySQL представляет собой подпрограмму, хранящуюся в базе данных. Она содержит имя, список параметров и операторы SQL. Все популярные системы управления базами данных поддерживают хранимые процедуры. Они были введены в MySQL 5.

Существует два вида подпрограмм: хранимые процедуры и функции, возвращающие значения, которые используются в других операторах SQL (например, pi()).

Основное отличие заключается в том, что функции могут использоваться, как любое другое выражение в операторах SQL, а хранимые процедуры должны вызываться с помощью оператора CALL.

Хранимые процедуры MySQL-основные преимущества

Хранимые процедуры MySQL работают быстро. Преимущество сервера MySQL заключается в том, что он использует кэширование, а также заранее заданные операторы. Основной прирост скорости дает сокращение сетевого трафика. Если есть повторяющиеся задачи, которые требуют проверки, обработки циклов, нескольких операторов, и при этом не требуют взаимодействия с пользователем, это можно реализовать с помощью одного вызова процедуры, которая хранится на сервере;
MySQL хранимые процедуры являются универсальными. При написании хранимой процедуры на SQL она будет работать на любой платформе, которая использует MySQL. В этом преимущество SQL над другими языками, такими как Java, C или PHP;
Исходный код хранимых процедур всегда доступен в базе данных. Это эффективная практика связать данные с процессами, которые их обрабатывают.

Создание процедуры в MySQL

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

CREATE [DEFINER = { user | CURRENT_USER }]          
PROCEDURE имя_процедуры ([параметры_процедуры[,...]])          
[характеристики ...] тело_подпрограммы

параметры_процедуры: [ IN | OUT | INOUT ] имя_параметра type    
type: Любой валидный тип данных MySQL    
характеристики: COMMENT 'string'     
| LANGUAGE SQL      
| [NOT] DETERMINISTIC      
| { CONTAINS SQL | NO SQL | READS SQL DATA 
| MODIFIES SQL DATA }      
| SQL SECURITY { DEFINER | INVOKER }    
тело_подпрограммы:  Валидный оператор программы SQL

Перед тем, как осуществить MySQL вызов хранимой процедуры, необходимо получить определенную информацию.

Проверка версии MySQL

Следующая команда выводит версию MySQL:

mysql>SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)

Проверка привилегий текущего пользователя

Для команд CREATE PROCEDURE и CREATE FUNCTION требуются привилегия пользователя CREATE ROUTINE. Также может потребоваться привилегия SUPER, это зависит от значения DEFINER, которое будет описано далее. Если включен бинарный лог для CREATE FUNCTION, то может потребоваться привилегия SUPER. По умолчанию MySQL автоматически предоставляет для создателя подпрограммы привилегии ALTER ROUTINE и EXECUTE. Такое поведение можно изменить, отключив системную переменную automatic_sp_privileges:

mysql> SHOW PRIVILEGES;
+-----------------+----------------------+----------------------------------------------+
| Привилегия      | Контекст             | Комментарий                    
+-----------------+----------------------+----------------------------------------------+
| Alter           | Таблицы              | Изменить таблицу                                    |
| Alter routine   | Функции, процедуры   | Изменить или удалить хранимую процедуру или |                 |                 | функцию              |
| Create          | Базы данных, таблицы,| Создать новую базу данных и таблицу 
|                 | индексы              | 
|
|Create temporary | Базы данных          | Использовать CREATE TEMPORARY TABLE                         |
| tables          |                      |                                                       |
| Create view     | Таблицы              | Создать новые представления                                   |
| Create user     | Администратор сервера| Создать новых пользователей                                   |
| Delete          | Таблицы              | Удалить существующие строки                               |
| Drop            | Базы данных, таблицы | Удалить базы данных, таблицы и представления                  |
| Event           | Администратор сервера| Создать, изменить, удалить и выполнить события             |
| Execute         | Функции, процедуры   | Выполнить хранимую подпрограмму                            |
| File            | Доступ к файлам      | Для чтения и записи файлов на сервере
|
| Grant option    | Базы данных, таблицы,| Дать другим пользователям те же
|                 | Функции, процедуры   | привилегии, которые есть у вас  
|
| Index           | Таблицы              | Создать или удалить индексы                             |
| Insert          | Таблицы              | Вставить данные в таблицу                            |
| Lock tables     | Базы данных          | Использовать LOCK TABLES (вместе с            |                 |                      | привилегией SELECT)   
|
| Process         | Администратор сервера| Посмотреть текст текущих выполняемых запросов |
| Proxy           | Администратор сервера| Подключить пользователей прокси               |
| References      | Базы данных, таблицы | Получить ссылки на таблицы                          |
| Reload          | Администратор сервера| Перезагрузить или обновить таблицы, логи и                  |                 |                      | привилегии      
|
| Replication     | Администратор сервера| Запросить, какой сервер является слейв, а
| client          |                      |  какой клиент                                                               |
| Replication     | Администратор сервера| Считать события бинарных логов                
|                 |                      |с сервера
|
| Select          | Таблицы              | Извлечь строки из таблицы                           |
| Show databases  | Администратор сервера| Посмотреть все базы данных с помощью SHOW     |                 |                      |DATABASES              
|
| Show view       | Таблицы              | Посмотреть представления с помощью SHOW CREATE |                 |                      | VIEW                    
|
| Shutdown        | Администратор сервера| Выключить сервер                               |
| Super           | Администратор сервера| Использовать KILL, SET GLOBAL, CHANGE MASTER и |                 |                      | т.д.   
|
| Trigger         | Таблицы              | Использовать триггеры                                       |
| Create          | Администратор сервера| Создать/изменить/удалить пространство таблиц                    |
|      |                      |                                                       |
| Update          | Таблицы              | Обновить существующие строки                               |
| Usage           | Администратор сервера| Без привилегий - разрешить только соединение                    |
+-----------------+----------------------+----------------------------------------------+
31 rows in set (0.00 sec)

Выбор базы данных

Перед тем создать процедуру MySQL, нужно выбрать базу данных. Давайте просмотрим список баз данных и выберем одну из них:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hr                 |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
7 rows in set (0.06 sec))

Теперь выберите базу данных "hr" и выведите список таблиц:

mysql> USE hr;
Database changed
mysql> SHOW TABLES;
+--------------+
| Tables_in_hr |
+--------------+
| alluser      |
| departments  |
| emp_details  |
| job_history  |
| jobs         |
| locations    |
| regions      |
| user         |
| user_details |
+--------------+
9 rows in set (0.00 sec)

Выбор разделителя

Разделитель - символ или строка символов, которая используется для закрытия оператора SQL. По умолчанию в качестве разделителя используется точка с запятой (;). Но это вызывает проблемы в хранимых процедурах и триггерах MySQL, поскольку она может иметь много операторов, и каждый должен заканчиваться точкой с запятой. Поэтому в качестве разделителя будем использовать двойной знак доллара - $$. Чтобы позже снова использовать в качестве разделителя ";" выполните команду "DELIMITER ; $$". Ниже приведен код для смены разделителя:

mysql> DELIMITER $$ ;

Теперь DELIMITER по умолчанию - "$$". Выполним простую команду SQL:

mysql> SELECT * FROM user $$
+------------+-----------------+--------------+
| userid     | password    | name       |
+------------+-----------------+--------------+
| scott123 | 123@sco     | Scott        |
| ferp6734 | dloeiu@&3   | Palash      |
| diana094 | ku$j@23     | Diana       |
+------------+-----------------+--------------+
3 rows in set (0.00 sec)

Теперь выполните следующую команду, чтобы снова установить ";" в качестве разделителя:

mysql> DELIMITER ; $$

Пример процедуры в MySQL

Мы создадим простую MySQL процедуру под названием job_data, при выполнении она будет выводить все данные из таблицы "jobs":

mysql> DELIMITER $$ ;mysql> CREATE PROCEDURE job_data()
    -> SELECT * FROM JOBS; $$
Query OK, 0 rows affected (0.00 sec)

Пояснение:

Команда CREATE PROCEDURE создает хранимую процедуру;
Следующая часть - это имя процедуры "job_data";
Имена процедур не чувствительны к регистру, поэтому job_data равносильно JOB_DATA;
Нельзя использовать две процедуры с одним именем в одной и той же базе данных;
Можно использовать имена в формате "имя-процедуры.имя-базы-данных", например, "hr.job_data";
Имена процедур могут быть разделены. Если имя разделено, оно может содержать пробелы;
Максимальная длина имени процедуры составляет 64 символа;
Избегайте использования имен встроенных функций MySQL;
Последняя часть "CREATE PROCEDURE" - это пара скобок содержит список параметров. Поскольку эта процедура не имеет никаких параметров, список пуст;
Следующая часть SELECT * FROM JOBS; $$ - это последний оператор в синтаксисе хранимых процедур MySQL. Точка с запятой (;) здесь не является обязательной, так как реальным окончанием оператора является $$.

Инструменты для создания процедур в MySQL - см. https://www.internet-technologies.ru/articles/obzor-konstruktora-jimdo.html (Можно написать процедуру с помощью инструмента командной строки MySQL или с помощью MySQL Workbench.)

Вызов процедуры в MySQL

Оператор CALL используется для вызова процедуры, которая хранится в базе данных. Синтаксис следующий:

CALL имя_процедуры([параметр[,...]]) 
CALL имя_процедуры[()]

Хранимые процедуры MySQL, которые не принимают аргументов, могут вызываться без скобок. Поэтому CALL job_data() равносильно CALL job_data.

Давайте выполним процедуру:

mysql> CALL job_data$$
+------------+---------------------------------+------------+------------+
| JOB_ID     | JOB_TITLE                       | MIN_SALARY | MAX_SALARY |
+------------+---------------------------------+------------+------------+
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)Query OK, 0 rows affected (0.15 sec)

SHOW CREATE PROCEDURE

Этот оператор является расширением MySQL. Он возвращает точную строку, которая может быть использована, чтобы воссоздать указанную хранимую процедуру. Синтаксис следующий:

SHOW CREATE PROCEDURE имя_процедуры

Давайте осуществим MySQL вызов хранимой процедуры:

mysql> SHOW CREATE PROCEDURE job_data$$

MySQL: блоки характеристик

В синтаксисе оператора CREATE PROCEDURE допустимо использование блоков, которые описывают характеристики процедуры. Блоки указываются после скобок, но перед телом процедуры. Эти блоки являются необязательными.

Например:

characteristic:          
COMMENT 'string'      
| LANGUAGE SQL      
| [NOT] DETERMINISTIC      
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }      
| SQL SECURITY { DEFINER | INVOKER }

COMMENT

Характеристика COMMENT - это расширение MySQL. Она используется для описания хранимой подпрограммы, и данная информация отображается с помощью оператора SHOW CREATE PROCEDURE.

LANGUAGE

Характеристика 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).

SQL SECURITY {DEFINER | INVOKER}

Значение SQL SECURITY может быть определено либо как SQL SECURITY DEFINER, либо как SQL SECURITY INVOKER. Оно указывает, выполняется ли подпрограмма с использованием привилегий аккаунта, указанного в условии DEFINER, или аккаунта пользователя, который осуществляют MySQL вызов хранимой процедуры. Этот аккаунт должен иметь разрешение на доступ к базе данных, с которой связана подпрограмма. Значение по умолчанию DEFINER. Пользователь, который запускает процедуру, должен иметь привилегию EXECUTE, если процедура выполняется в контексте безопасности DEFINER.

Все перечисленные блоки характеристик имеют значения по умолчанию. Следующие два оператора дают одинаковый результат:

mysql> CREATE PROCEDURE job_data()
    -> SELECT * FROM JOBS; $$
Query OK, 0 rows affected (0.00 sec)

то же самое, что:

mysql> CREATE PROCEDURE new_job_data()
    -> COMMENT ''
    -> LANGUAGE SQL
    -> NOT DETERMINISTIC
    -> CONTAINS SQL
    -> SQL SECURITY DEFINER
    -> SELECT * FROM JOBS;
    -> $$
Query OK, 0 rows affected (0.26 sec)

Прежде, чем перейти к параметрам MySQL, рассмотрим несколько составных операторов MySQL.

MySQL: составные операторы

Составной оператор представляет собой блок, который может содержать другие блоки: объявления переменных, обработчиков состояний и курсоров, конструкции управления потоками данных, циклы и условные тесты. В версии MySQL 5.6 существуют следующие составные операторы:

Составной оператор BEGIN ... END;
Метки операторов;
DECLARE;
Переменные в хранимых программах;
Операторы контроля потока данных;
Курсоры;
Обработчики условий.

В этом разделе мы рассмотрим первые четыре оператора, связанные с параметрами оператора CREATE PROCEDURE.

Синтаксис составного оператора BEGIN ... END

Он используется, когда нужно разместить в пределах подпрограммы (например, хранимой процедуры MySQL, функции, триггера или события) более одного оператора. Синтаксис следующий:

[метка_начала:] 
BEGIN     
[список_операторов] 
END 
[метка_конца])

список_операторов: один или несколько операторов, завершающихся точкой с запятой (;). Сам по себе список операторов не является обязательным, поэтому пустой оператор BEGIN END является действительным.

Метки операторов

Метки - это разрешения на выполнение для блоков BEGIN ... END и операторов цикла REPEAT и WHILE. Синтаксис следующий:

[метка_начала:] 
BEGIN    
[список_операторов]  
END [метка_конца]    
[метка_начала:] 
LOOP      
список_операторов 
END LOOP 
[метка_конца]    
[метка_начала:] 
REPEAT            
список_операторов  
UNTIL search_condition  
END 
REPEAT [метка_конца]    
[метка_начала:] 
WHILE условие_поиска 
DO           
список_операторов 
END WHILE 
[метка_конца]

При применении меток применяются следующие правила:

метка_начала должна закрываться двоеточием;
метка_начала может использоваться без метки_конца. Если метка_конца присутствует, она должна принадлежать тому же блоку, что и метка_начала;
метка_конца не может использоваться без метки_начала;
метки, принадлежащие к одному вложенному уровню, должны быть разделены;
метки могут иметь длину не более 16 символов.

Оператор DECLARE

Используется для определения различных локальных элементов при MySQL создании хранимой процедуры. Например, локальных переменных, условий, обработчиков, курсоров. DECLARE используется только внутри составного оператора BEGIN ... END и должен находиться в его начале перед всеми остальными операторами.

Для объявлений существуют следующие правила:

Объявления курсоров должны размещаться перед объявлениями обработчиков;
Объявления переменных и условий должны размещаться перед объявлениями курсоров или обработчиков.

Переменные в хранимых программах

Хранимые программы используют оператор DECLARE для определения локальных переменных. Процедуры и функции могут при объявлении принимать параметры, которые обмениваются значениями между подпрограммой и вызывающим ее агентом.

Объявление переменной:

DECLARE имя_переменной [, имя_переменной] ... type [DEFAULT значение]

Чтобы предоставить значение для переменной по умолчанию, используется блок DEFAULT. Значение может быть задано как выражение; это не обязательно должна быть константа. Если блок DEFAULT отсутствует, начальное значение равно NULL.

Пример: Локальные переменные

Локальные переменные объявляются внутри хранимых процедур MySQL. Они действительны только в пределах блока END... BEGIN, в котором они объявлены. Локальные переменные могут содержать любой тип данных SQL. В следующем примере показано использование локальных переменных в хранимой процедуре:

DELIMITER $$
CREATE PROCEDURE my_procedure_Local_Variables()
BEGIN   /* объявление локальной переменной */   
DECLARE a INT DEFAULT 10;   
DECLARE b, c INT;    /* использование локальной переменной */   
SET a = a + 100;   
SET b = 2;   
SET c = a + b;    
BEGIN      /* локальная переменная во вложенном блоке */      
DECLARE c INT;             
SET c = 5;       
/* локальная переменная имеет приоритет по сравнению с переменной с          
тем же именем, объявленной в закрытом блоке. */       
SELECT a, b, c;   
END;    
SELECT a, b, c;
END$$

Теперь выполните процедуру:

mysql> CALL my_procedure_Local_Variables();
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  110 |    2 |    5 |
+------+------+------+
1 row in set (0.00 sec)

+------+------+------+
| a    | b    | c    |
+------+------+------+
|  110 |    2 |  112 |
+------+------+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.03 sec)

Пример: пользовательские переменные

В хранимых процедурах MySQL обращение к пользовательским переменным происходит через символ амперсанда (@) перед именем пользовательской переменной (например, @x и @y). В следующем примере показано использование пользовательских переменных внутри хранимой процедуры:

DELIMITER $$
CREATE PROCEDURE my_procedure_User_Variables()
BEGIN   
SET @x = 15;       
SET @y = 10;       
SELECT @x, @y, @x-@y;   
END$$
Теперь выполните процедуру:
mysql> CALL my_procedure_User_Variables() ;
+------+------+-------+
| @x   | @y   | @x-@y |
+------+------+-------+
|   15 |   10 |     5 |
+------+------+-------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)

MySQL: параметры процедуры

Ниже приводится синтаксис CREATE PROCEDURE для параметров:

CREATE          
[DEFINER = { пользователь | CURRENT_USER }]          
PROCEDURE имя_процедуры ([параметр_процедуры[,...]])          
[характеристики ...] тело_подпрограммы    
параметр_процедуры: [ IN | OUT | INOUT ] имя_парамета type

Варианты синтаксиса:

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". Количество строк указывается пользователем. Ниже приводится процедура:

mysql> CREATE PROCEDURE my_proc_IN (IN var1 INT)
    -> BEGIN 
    -> SELECT * FROM jobs LIMIT var1;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

Чтобы выбрать первые две строки из таблицы "jobs" выполните следующую команду:

mysql> CALL my_proc_in(2)$$
+---------+-------------------------------+------------+------------+
| JOB_ID  | JOB_TITLE                     | MIN_SALARY | MAX_SALARY |
+---------+-------------------------------+------------+------------+
| AD_PRES | President                     |      20000 |      40000 |
| AD_VP   | Administration Vice President |      15000 |      30000 |
+---------+-------------------------------+------------+------------+
2 rows in set (0.00 sec)Query OK, 0 rows affected (0.03 sec)

Теперь выберите первые пять строк из таблицы "jobs":

mysql> 
CALL my_proc_in(5)$$
+------------+-------------------------------+------------+------------+
| JOB_ID     | JOB_TITLE                     | MIN_SALARY | MAX_SALARY |
+------------+-------------------------------+------------+------------+
| AD_PRES    | President                     |      20000 |      40000 |
| AD_VP      | Administration Vice President |      15000 |      30000 |
| AD_ASST    | Administration Assistant      |       3000 |       6000 |
| FI_MGR     | Finance Manager               |       8200 |      16000 |
| FI_ACCOUNT | Accountant                    |       4200 |       9000 |
+------------+-------------------------------+------------+------------+
5 rows in set (0.00 sec)Query OK, 0 rows affected (0.05 sec)

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

Дальше представлен MySQL хранимой процедуры пример, в котором используется параметр OUT. В рамках процедуры MySQL функция MAX() извлекает максимальную зарплату из столбца MAX_SALARY таблицы "jobs":

mysql> CREATE PROCEDURE my_proc_OUT (OUT highest_salary INT)
    -> BEGIN
    -> SELECT MAX(MAX_SALARY) INTO highest_salary FROM JOBS;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

В теле процедуры параметр получает самую высокую зарплату из столбца MAX_SALARY. После вызова процедуры слово OUT сообщает СУБД, что значение исходит от процедуры. highest_salary - это имя выходного параметра и в операторе CALL мы передали его значение переменной сеанса с именем @M:

mysql> CALL my_proc_OUT(@M)$$
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @M$$+-------+
| @M    |
+-------+
| 40000 |
+-------+
1 row in set (0.00 sec)

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

В следующем примере показана простая хранимая процедура MySQL, которая использует параметр INOUT и параметр IN. Пользователь предоставляет 'M' или 'F' через параметр IN (emp_gender) для подсчета количества сотрудников мужского или женского пола из таблицы user_details. Параметр INOUT (mfgender) возвращает результат пользователю. Вот код и результат выполнения процедуры:

mysql> CALL my_proc_OUT(@M)$$Query OK, 1 row affected (0.03 sec)mysql> CREATE PROCEDURE my_proc_INOUT (INOUT mfgender INT, IN emp_gender CHAR(1))
    -> BEGIN
    -> SELECT COUNT(gender) INTO mfgender FROM user_details WHERE gender = emp_gender;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

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

mysql> CALL my_proc_INOUT(@C,'M')$$
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @C$$
+------+
| @C   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> CALL my_proc_INOUT(@C,'F')$$
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @C$$
+------+
| @C   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MySQL: Операторы управления потоком

MySQL поддерживает конструкции для управления потоком данных в хранимых программах IF, CASE, ITERATE, LEAVE, LOOP, WHILE и REPEAT. Также поддерживается RETURN внутри хранимых процедур MySQL.

MySQL: Оператор IF

Оператор IF реализует базовую конструкцию условия, он должен заканчиваться точкой с запятой. Существует также функция IF(), которая отличается от оператора IF. Вот синтаксис оператора IF:

IF условие THEN оператор(ы)   
[ELSEIF условие THEN оператор(ы)] ...         
[ELSE оператор(ы)]  
END IF

Если условие выполняется, выполняются операторы соответствующих блоков THEN или ELSE IF.

Если условие не удовлетворяется, выполняются операторы блока ELSE. Каждый оператор состоит из одного или нескольких операторов SQL; пустые операторы не допускается.

Пример:

В следующем примере мы передаем через параметр IN user_id, чтобы получить имя пользователя. В рамках процедуры мы использовали операторы IF ELSE IF и ELSE, чтобы получить имя пользователя из множества идентификаторов пользователей. Имя пользователя будет храниться в параметре user_name INOUT:

CREATE DEFINER=`root`@`127.0.0.1` 
PROCEDURE `GetUserName`(INOUT user_name varchar(16),
IN user_id varchar(16))
BEGIN
DECLARE uname varchar(16);
SELECT name INTO uname
FROM user
WHERE userid = user_id;
IF user_id = "scott123" 
THEN
SET user_name = "Scott";
ELSEIF user_id = "ferp6734" 
THEN
SET user_name = "Palash";
ELSEIF user_id = "diana094" 
THEN
SET user_name = "Diana";
END IF;
END

Осуществите MySQL вызов хранимой процедуры:

mysql> CALL GetUserName(@A,'scott123')$$
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @A;
    -> $$
+-------+
| @A    |
+-------+
| Scott |
+-------+
1 row in set (0.00 sec)

MySQL: Оператор CASE

Оператор CASE используется для создания внутри хранимой процедуры MySQL сложной условной конструкции. Оператор CASE не может содержать блок ELSE NULL и должен закрываться END CASE, а не END. Синтаксис:

CASE значение    
WHEN значение THEN список_операторов         
[WHEN значение THEN список_операторов] ...         
[ELSE список_операторов] END CASE

или:

CASE        
WHEN условие_поиска THEN список_операторов   
[WHEN условие_поиска THEN список_операторов] ...        
[ELSE список_операторов] END CASE

Пояснение: первый синтаксис

Значение - это выражение, которое сравнивается со значением в каждом блоке WHEN, пока они не будут равны. При найденном соответствии значений выполняется список_операторов соответствующего блока THEN.

Если значения не равны, тогда выполняется список_операторов блока ELSE, (если таковой имеется).

Пояснение: второй синтаксис

Каждое выражение блока условие_поиска оценивается, пока одно из них не будет истинно. В этот момент выполняется список_операторов соответствующего блока THEN.

Если ни одно из выражений условие_поиска не истинно, тогда выполняется список_операторов блока ELSE, если таковой имеется. Каждый список_операторов состоит из одного или нескольких операторов SQL; пустой список_операторов не допускается.

Пример:

У нас есть таблица под названием 'jobs' со следующими записями:

+------------+---------------------------------+------------+------------+

| JOB_ID     | JOB_TITLE                       | MIN_SALARY | MAX_SALARY |

+------------+---------------------------------+------------+------------+

| AD_PRES    | President                       |      20000 |      40000 |

| AD_VP      | Administration Vice President   |      15000 |      30000 |

| AD_ASST    | Administration Assistant        |       3000 |       6000 |

| FI_MGR     | Finance Manager                 |       8200 |      16000 |

| FI_ACCOUNT | Accountant                      |       4200 |       9000 |

| AC_MGR     | Accounting Manager              |       8200 |      16000 |

| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |

| SA_MAN     | Sales Manager                   |      10000 |      20000 |

| SA_REP     | Sales Representative            |       6000 |      12000 |

| PU_MAN     | Purchasing Manager              |       8000 |      15000 |

| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |

| ST_MAN     | Stock Manager                   |       5500 |       8500 |

| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |

| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |

| IT_PROG    | Programmer                      |       4000 |      10000 |

| MK_MAN     | Marketing Manager               |       9000 |      15000 |

| MK_REP     | Marketing Representative        |       4000 |       9000 |

| HR_REP     | Human Resources Representative  |       4000 |       9000 |

| PR_REP     | Public Relations Representative |       4500 |      10500 |

+------------+---------------------------------+------------+------------+

19 rows in set (0.03 sec)

Подсчитаем количество сотрудников, удовлетворяющих следующим условиям:

MIN_SALARY > 10000
MIN_SALARY < 10000
MIN_SALARY = 10000

Для этого мы используем следующую процедуру (MySQL хранимой процедуры пример создан в MySQL Workbench 5.2 CE):

DELIMITER $$
CREATE PROCEDURE `hr`.`my_proc_CASE` 
(INOUT no_employees INT, IN salary INT)
BEGIN
CASE
WHEN (salary>10000) 
THEN (SELECT COUNT(job_id) INTO no_employees 
FROM jobs 
WHERE min_salary>10000);
WHEN (salary<10000) 
THEN (SELECT COUNT(job_id) INTO no_employees 
FROM jobs 
WHERE min_salary<10000);
ELSE (SELECT COUNT(job_id) INTO no_employees 
FROM jobs WHERE min_salary=10000);
END CASE;
END$$

В приведенной выше процедуре мы передаем переменную salary через параметр IN. Есть оператор CASE с двумя блоками WHEN и ELSE, который проверяет условия и возвращает значение счетчика в no_employees. Выполним процедуру через командную строку MySQL.

Количество сотрудников, чья зарплата превышает 10000:

mysql> CALL my_proc_CASE(@C,10001);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @C;
+------+
| @C   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Количество сотрудников, чья зарплата меньше, чем 10000:

mysql> CALL my_proc_CASE(@C,9999);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @C;
+------+
| @C   |
+------+
|   16 |
+------+
1 row in set (0.00 sec)

Количество сотрудников, чья зарплата равна 10000:

mysql> CALL my_proc_CASE(@C,10000);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @C;
+------+
| @C   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MySQL: оператор ITERATE

ITERATE означает "запустить цикл снова". ITERATE может использоваться только в операторах LOOP, REPEAT и WHILE. Синтаксис следующий:

ITERATE метка

MySQL: оператор LEAVE

Используется для выхода из конструкции управления потоком, который имеет заданную метку. Если метка задана для самого последнего блока хранимой процедуры MySQL, LEAVE выходит из программы.

LEAVE может использоваться в BEGIN ... END или конструкциях цикла (LOOP, REPEAT, WHILE). Синтаксис следующий:

LEAVE метка

MySQL: оператор LOOP

Используется, чтобы задать повторное выполнение списка операторов. Синтаксис следующий:

[метка_начала:]
LOOP
список_операторов
END LOOP
[метка_конца]

список_операторов состоит из одного или нескольких операторов, каждый из которых заканчивается точкой с запятой (;). Операторы внутри цикла повторяются до тех пор, пока цикл не будет завершен. Обычно для выхода из конструкции цикла используется LEAVE.

Также может использоваться оператор RETURN. Оператор LOOP может иметь метки.

Пример:

В приведенной ниже процедуре строки вставляются в таблицу 'number' до тех пор, пока х меньше, чем num (число заданное пользователем через параметр IN). Каждый раз сохраняется случайное число:

DELIMITER $$
CREATE PROCEDURE `my_proc_LOOP` (IN num INT)
BEGIN
DECLARE x INT;
SET x = 0;
loop_label: LOOP
INSERT INTO number VALUES (rand());
SET x = x + 1;
IF x >= num 
THEN
LEAVE loop_label;
END IF;
END LOOP;
END$$

Теперь выполните MySQL хранимую процедуру:

mysql> CALL my_proc_LOOP(3);
Query OK, 1 row affected, 1 warning (0.19 sec)

mysql> select * from number;
+--------------+
| rnumber      |
+--------------+
| 0.1228974146 |
| 0.2705919913 |
| 0.9842677433 |
+--------------+
3 rows in set (0.00 sec)

MySQL: оператор REPEAT

REPEAT исполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец оператора:

[метка_начала:] 
REPEAT     
список_операторов
UNTIL условие_поиска 
END 
REPEAT 
[метка_конца]

список_операторов - список из одного или нескольких операторов, каждый из которых разделяется точкой с запятой (;). условие_поиска - выражение.

Оператор REPEAT может иметь метки.

Пример:

Четные числа - это числа, которые могут быть разделены на 2 без остатка. В следующей процедуре пользователь задает число через параметр IN и получает сумму четных чисел от 1 до установленного числа:

DELIMITER $$
CREATE PROCEDURE my_proc_REPEAT (IN n INT)
BEGI
NSET @sum = 0;
SET @x = 1;  
REPEAT   
IF mod(@x, 2) = 0 
THEN   
SET @sum = @sum + @x;   
END IF;   
SET @x = @x + 1;   
UNTIL @x > n 
END REPEAT;
END $$

Теперь выполните хранимую процедуру MySQL:

mysql> call my_proc_REPEAT(5);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sum;
+------+
| @sum |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

mysql> call my_proc_REPEAT(10);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sum;
+------+
| @sum |
+------+
|   30 |
+------+
1 row in set (0.00 sec)

MySQL: оператор RETURN

Оператор RETURN завершает выполнение хранимой процедуры MySQL и возвращает агенту, вызвавшему функцию, значение expr. В хранимой функции должен содержаться, по крайней мере, один оператор RETURN. Если функция имеет несколько точек выхода, она может содержать больше одного RETURN. Синтаксис следующий:

RETURN expr

Этот оператор не используется в хранимых процедурах или триггерах. Вместо него применяется оператор LEAVE.

MySQL: оператор WHILE

Оператор WHILE выполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец цикла. Каждый оператор заканчивается точкой с запятой (;). Синтаксис следующий:

[Метка_начала:] WHILE условие_поиска DO
    список_операторов
END WHILE [метка_конца]

Оператор WHILE может иметь метки.

Пример:

Нечетные числа - это числа, которые не делятся на 2 без остатка. В следующей процедуре пользователь передает через параметр IN число и получает сумму нечетных чисел от 1 до заданного числа:

DELIMITER $$
CREATE PROCEDURE my_proc_WHILE(IN n INT)
BEGIN
SET @sum = 0;
SET @x = 1;
WHILE @x<n 
DO
   IF mod(@x, 2) <> 0 THEN   
SET @sum = @sum + @x;   
END IF;   
SET @x = @x + 1;   
END WHILE;
END$$

Теперь выполните MySQL хранимую процедуру:

mysql> CALL my_proc_WHILE(5);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sum;
+------+
| @sum |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> CALL my_proc_WHILE(10);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sum;
+------+
| @sum |
+------+
|   25 |
+------+
1 row in set (0.00 sec)

mysql> CALL my_proc_WHILE(3);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sum;
+------+
| @sum |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

MySQL: ALTER PROCEDURE

Используется для изменения характеристик хранимой процедуры MySQL. В операторе ALTER PROCEDURE может быть указано более одного значения для изменения. Но с его помощью нельзя изменить параметры или тело хранимой процедуры. Чтобы внести такие изменения, необходимо удалить и заново создать процедуру с помощью операторов DROP PROCEDURE и CREATE PROCEDURE. Синтаксис следующий:

ALTER PROCEDURE имя_процедуры [характеристики ...] характеристики:    
COMMENT 'string'  
| LANGUAGE SQL  
| { CONTAINS SQL 
| NO SQL | READS SQL DATA 
| MODIFIES SQL DATA }  
| SQL SECURITY { DEFINER 
| INVOKER }

Для выполнения этого оператора нужно иметь привилегию ALTER ROUTINE. По умолчанию эта привилегия автоматически предоставляется создателю процедуры. В предыдущей процедуре "my_proc_WHILE" раздел комментариев был пуст. Для ввода нового комментария или изменения предыдущего используйте следующую команду:

mysql> ALTER PROCEDURE my_proc_WHILE 
COMMENT 'Modify Comment';
Query OK, 0 rows affected (0.20 sec)

Можно проверить результат с помощью команды SHOW CREATE PROCEDURE, которую мы рассматривали ранее.

MySQL: DROP PROCEDURE

Используется для сброса в MySQL вызванной хранимой процедуры или функции. После чего указанная подпрограмма удаляется с сервера. Для этого нужно иметь привилегию ALTER ROUTINE. Если системная переменная automatic_sp_privileges включена, эта привилегия и привилегия EXECUTE автоматически предоставляются во время создания подпрограммы и сбрасываются во время удаления подпрограммы:

DROP {PROCEDURE | FUNCTION} [IF EXISTS] имя_процедуры

Блок IF EXISTS - это расширение MySQL. Он предотвращает возникновение ошибки, если процедура или функция не существует. Создается предупреждение, которое можно просмотреть с помощью SHOW WARNINGS. Например:

mysql> DROP PROCEDURE new_procedure;
Query OK, 0 rows affected (0.05 sec)

Можно проверить результат с помощью команды SHOW CREATE PROCEDURE, которую мы рассматривали ранее.

MySQL: курсоры

Курсор представляет собой структуру управления, которая позволяет обрабатывать записи в базе данных. Курсоры используются для обработки отдельных строк, возвращаемых в ответ на запросы системой базы данных. Курсор включает строки в набор результатов, чтобы последовательно их обработать.

В процедурах SQL курсор позволяет определить результирующий набор (набор строк данных) и выполнить сложную логику построчно. Используя те же механизмы, процедура SQL также может определить набор результатов и вернуть его непосредственно вызывающему агенту или в клиентское приложение.

MySQL хранимые процедуры поддерживают курсоры. Синтаксис тот же, что и для встроенного SQL. Курсоры имеют следующие свойства:

Asensitive: сервер может или не может создавать копию таблицы результатов;
Read only: не обновляемые;
Nonscrollable: обработка может производиться только в одном направлении, при этом пропуск строк не допускается.

Чтобы использовать курсор в процедурах MySQL, нужно сделать следующее:

Объявить курсор;
Открыть курсор;
Извлечь данные в переменные;
Закрыть курсор.

Объявление курсора

Следующий оператор объявляет курсор и связывает его с оператором SELECT. Он извлекает строки, которые будут перемещаться с помощью курсора:

DECLARE имя_курсора 
CURSOR FOR оператор_select

Открытие курсора

После объявления мы открываем объявленный курсор:

OPEN имя_курсора

Выборка данных в переменные

FETCH выбирает строки для оператора SELECT, связанного с указанным курсором (который должен быть открыт), и перемещает указатель курсора. Если строка существует, то выбранные столбцы сохраняются в указанных переменных. Число столбцов извлекаемых SELECT должно соответствовать количеству выходных переменных, указанных в FETCH:

FETCH [[NEXT] FROM] имя_курсора
INTO имя_переменной [,имя_переменной] ...

Закрытие курсора

Этот оператор закрывает ранее открытый курсор. Если курсор не открыт, возникает ошибка:

CLOSE имя_курсора

Пример:

Хранимая процедура MySQL начинается с объявления трех переменных. При этом порядок имеет значение. Первыми объявляются переменные. После этого объявляются условия, затем – курсоры и обработчики. Если вы поместите их в неправильном порядке, то получите сообщение об ошибке:

DELIMITER $$
CREATE PROCEDURE my_procedure_cursors(INOUT return_val INT)
BEGIN
DECLARE a,b INT; 
DECLARE cur_1 CURSOR FOR 
SELECT max_salary FROM jobs;
DECLARE CONTINUE HANDLER FOR NOT FOUNDSET b = 1;
OPEN cur_1;REPEATFETCH cur_1 INTO a;
UNTIL b = 1END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;
$$

Теперь выполните процедуру:

mysql> 
CALL my_procedure_cursors(@R);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @R;
+-------+
| @R    |
+-------+
| 10500 |
+-------+
1 row in set (0.00 sec)

Управление доступом для встроенных программ

Для хранимых процедур MySQL и представлений с помощью привилегий задаются правила использования и выполнения. Эти привилегии управляются атрибутом DEFINER, и, если таковая имеется, характеристикой SQL SECURITY.

Все процедуры, функции, триггеры и представления могут иметь атрибут DEFINER, который указывает на аккаунт MySQL. Если атрибут DEFINER в определении отсутствует, учетной записью по умолчанию является пользователь, который создает объект.

MySQL использует следующие правила для управления атрибутом объекта DEFINER:

Можно указать в качестве значения DEFINER другого пользователя, только если у вас есть привилегия SUPER;
Если вы не имеете привилегии SUPER, единственное допустимое значение - это ваша учетная запись, указанная явно с помощью CURRENT_USER. Нельзя указать для DEFINER другого пользователя;
Для подпрограммы или представления используйте в определении объекта SQL SECURITY INVOKER, чтобы они могли использоваться только пользователями с соответствующими правами;
Если вы создаете хранимую процедуру MySQL с включением DEFINER через пользовательскую запись с привилегией SUPER, то задавайте атрибут DEFINER, указывающий на пользователя с привилегиями, необходимыми для операций, выполняемых с объектом. Указывайте в DEFINER аккаунт с более широкими привилегиями только, когда это абсолютно необходимо;
Администраторы могут лишить пользователей права указывать более привилегированные пользовательские записи в DEFINER, не предоставляя им привилегию SUPER;
Объект с DEFINER-контекстом должен создаваться с учетом того, что он может получить доступ к данным, на которые вызывающий пользователь не имеет никаких привилегий. В некоторых случаях можно не допустить ссылки на эти объекты, не предоставляя неавторизованным пользователям определенные привилегии;
MySQL хранимая процедура или функция не может быть связана с пользователем, который не имеет привилегию EXECUTE;
Представление не может быть связано с пользователем, который не имеет соответствующих привилегией (SELECT на выборку данных, INSERT для вставки данных и так далее).

PreviousЭмуляция функции row_number() в MySQL NextSELECT DISTINCT

Last updated 3 months ago