1. Введение
В приложениях уровня предприятия всегда существует необходимость регулярно выполнять определенный конкретный набор задач в базе данных, таких как очистка базы данных, обработка платежной ведомости, создание нового объекта с несколькими записями по умолчанию и многое другое. Такая задача может включать выполнение нескольких запросов для каждой задачи. Этот процесс можно было бы облегчить, если бы был способ сгруппировать эти задачи в одну задачу и выполнить ее. Хранимая процедура MySQL именно для этой цели. Хранимая процедура MySQL представляет собой кусочек предварительно скомпилированного кода SQL, который может быть выполнен для выполнения нескольких задач наряду с выполнением определенных логических операций. Процедура MySQL аналогична функциям в общем языке программирования. В этой статье подробно описывается создание процедур MySQL с использованием сценария практического использования.
Содержание
- 1. Введение
- 2. Начало работы с процедурами MySQL
- 3. Создание процедуры для вставки записей
- 4. Отказ от процедуры
- 5. Переменные в процедуре
- 5.1 Добавление переменных внутри процедуры
- 5.2 Добавление параметров процедуры
- 6. Возврат определенного значения из процедуры
- 7. Условные потоки в процедуре
- 8. Перебирая записи
- 9. Вывод
2. Начало работы с процедурами MySQL
Для начала давайте создадим простую процедуру нулевого действия. Процедура с нулевым действием — это процедура, которая на самом деле не выполняет какую-либо задачу базы данных, но дает нам обратную связь, что процедура успешно выполнена. Прежде чем мы начнем с кода, вот предварительные условия для продолжения обучения.
- MySQL сервер запущен и работает
- MySQL shell работает
После запуска оболочки MySQL выберите базу данных для учебника. Если у вас нет базы данных, используйте приведенные ниже команды для создания и выбора базы данных.
|
1
2
|
mysql> create schema procedure_training;mysql> use procedure_training; |
Эти команды создадут схему и выберут ее. Теперь давайте разберемся в синтаксисе создания простой процедуры без переменных, прежде чем мы создадим нашу первую процедуру.
|
1
2
3
4
5
6
|
mysql> DELIMITER //mysql> CREATE PROCEDURE PROCEDURE_NAME() -> begin -> PROCEDURE_BODY -> end -> // |
Процедура содержит символы новой строки, а также точки с запятой в своем выражении. Следовательно, первым шагом перед созданием процедуры является изменение разделителя, который решает, когда оператор фактически заканчивается. Следовательно, код DELIMITER // был написан с целью указать, что инструкция заканчивается только тогда, когда приглашение MySQL встречает символ // .
Чтобы создать процедуру, начните с оператора CREATE PROCEDURE PROCEDURE_NAME() где PROCEDURE_NAME можно заменить именем по вашему выбору. Следующее утверждение содержит ключевое слово BEGIN . Это указывает на то, что основная часть процедуры начинается с этой точки. Следующая часть может быть одиночной или множественной, заменяя PROCEDURE_BODY в приведенном выше коде. Наконец, как только тело процедуры завершено, поместите ключевое слово END чтобы указать, что тело процедуры заканчивается там.
Теперь, когда у нас есть общее представление о синтаксисе, давайте создадим нашу первую процедуру, используя приведенный ниже код.
|
1
2
3
4
5
6
7
|
mysql> DELIMITER //mysql> CREATE PROCEDURE myfirstprocedure() -> begin -> select 'This is my first procedure'; -> end -> //Query OK, 0 rows affected (0.06 sec) |
В приведенном выше коде мы создаем процедуру с именем myfirstprocedure . Чтобы упростить процедуру, все, что мы помещаем в тело, это один оператор для отображения комментария. Пока весь код написан, как и ожидалось, вы увидите вывод, как показано выше.
После успешного выполнения запроса вы можете вызвать процедуру с помощью простой команды, показанной ниже.
|
01
02
03
04
05
06
07
08
09
10
|
mysql> delimiter ;mysql> call myfirstprocedure();+----------------------------+| This is my first procedure |+----------------------------+| This is my first procedure |+----------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec) |
Как видно, я сначала изменил разделитель на точку с запятой, чтобы избежать проблем с записью // для завершения оператора. После изменения выполните оператор вызова, чтобы выполнить процедуру. Поскольку тело просто содержит комментарий, оно отображается так, как показано выше. Давайте теперь создадим немного сложную процедуру.
3. Создание процедуры для вставки записей
Процедура вставки используется для вставки группы предопределенных записей при создании определенной записи. Идеальный вариант использования — назначить несколько прав доступа по умолчанию для пользователя при его создании. Для того, чтобы сделать такую деятельность, нам нужен стол для работы. Создайте таблицу, используя инструкцию create, как указано ниже:
|
1
2
3
4
5
|
CREATE TABLE `procedure_training`.`user_roles` ( `id` INT NOT NULL, `rolename` VARCHAR(45) NULL, `userid` INT NULL, PRIMARY KEY (`id`)); |
Следующим шагом является создание процедуры, которая вставляет группу ролей для значения userid 1. Это можно сделать, используя процедуру ниже.
|
1
2
3
4
5
6
|
DELIMITER // CREATE PROCEDURE insertdefaultroles() BEGIN insert into user_roles values (1, 'admin',1), (2, 'employee',1),(3,'learner',1); END // DELIMITER ; |
Вышеуказанная процедура вставляет 3 записи в таблицу при выполнении. Результат того же самого был показан ниже.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
mysql> call insertdefaultroles();Query OK, 3 rows affected (0.14 sec)mysql> select * from user_roles;+----+----------+--------+| id | rolename | userid |+----+----------+--------+| 1 | admin | 1 || 2 | employee | 1 || 3 | learner | 1 |+----+----------+--------+3 rows in set (0.01 sec) |
4. Отказ от процедуры
Когда вам нужно пересоздать процедуру, как и таблицы, вам нужно отбросить процедуру, прежде чем создавать ее заново. В альтернативной базе данных, такой как Oracle DB, есть поддержка для создания или замены процедуры с использованием CREATE OR REPLACE вместо простого CREATE . Однако в MySQL обязательно удалить процедуру, прежде чем вы сможете ее заново создать. Поскольку мы продолжаем в учебнике, мы должны будем часто отбрасывать процедуру. Запрос на удаление процедуры похож на запрос DROP для таблиц MySQL. Запрос на удаление процедуры insertdefaultroles показан ниже.
|
1
|
mysql> DROP PROCEDURE insertdefaultroles; |
5. Переменные в процедуре
Как вы, наверное, заметили в приведенном выше коде, мы указали статический идентификатор пользователя и значение первичного ключа. Это делает процедуру непригодной для использования после ее однократного использования. Следовательно, нам нужен выход для автоматизации этих значений. Давайте начнем со значения первичного ключа в первую очередь.
5.1 Добавление переменных внутри процедуры
Первым шагом к использованию переменной является ее объявление. После того, как оно было объявлено, нам нужно получить максимальное значение переменной первичного ключа, которая в данный момент находится в таблице. Код ниже делает то же самое.
|
1
2
3
4
5
6
7
8
9
|
DROP PROCEDURE insertdefaultroles; DELIMITER // CREATE PROCEDURE insertdefaultroles() BEGIN DECLARE pkvalue int; select max(id) into pkvalue from user_roles; insert into user_roles values (pkvalue+1, 'admin',1), (pkvalue+2, 'employee',1),(pkvalue+3,'learner',1); END // DELIMITER; |
Код содержит оператор удаления процедуры для удаления процедуры перед ее повторным созданием. Если вы этого не сделаете, вы столкнетесь с ошибкой, отметив, что процедура уже существует. Как только вы закончите выполнение кода, вызовите процедуру еще раз, чтобы просмотреть результаты. Результаты будут, если новые записи были добавлены в таблицу с увеличением значения первичных ключей.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
mysql> call insertdefaultroles();Query OK, 3 rows affected (0.07 sec)mysql> select * from user_roles;+----+----------+--------+| id | rolename | userid |+----+----------+--------+| 1 | admin | 1 || 2 | employee | 1 || 3 | learner | 1 || 4 | admin | 1 || 5 | employee | 1 || 6 | learner | 1 |+----+----------+--------+6 rows in set (0.00 sec) |
5.2 Добавление параметров процедуры
Хотя проблема с динамическим первичным ключом была решена, идентификатор пользователя все еще статичен. Идентификатор пользователя не может быть получен из базы данных, как первичный ключ. Следовательно, идентификатор пользователя должен быть каким-то образом передан процедуре. Процедура позволяет параметры функции для этого.
|
1
2
3
4
5
6
7
8
9
|
DROP PROCEDURE insertdefaultroles; DELIMITER // CREATE PROCEDURE insertdefaultroles(uid int) BEGIN DECLARE pkvalue int ; select max(id) into pkvalue from user_roles; insert into user_roles values (pkvalue+1, 'admin',uid), (pkvalue+2, 'employee',uid),(pkvalue+3,'learner',uid); END //DELIMITER ; |
Приведенный выше код объявляет одну такую процедуру. Эта процедура принимает идентификатор пользователя в переменной uid . Эта переменная может использоваться для замены значения идентификатора пользователя в запросе. Выполните процедуру и убедитесь, что выполнение прошло успешно.
Чтобы запустить процедуру, передайте идентификатор пользователя так же, как вы передаете его в обычной функции. Код ниже показывает выполнение и результаты выполнения тоже.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
|
mysql> call insertdefaultroles(2);Query OK, 3 rows affected (0.10 sec)mysql> select * from user_roles;+----+----------+--------+| id | rolename | userid |+----+----------+--------+| 1 | admin | 1 || 2 | employee | 1 || 3 | learner | 1 || 4 | admin | 1 || 5 | employee | 1 || 6 | learner | 1 || 7 | admin | 2 || 8 | employee | 2 || 9 | learner | 2 |+----+----------+--------+9 rows in set (0.01 sec) |
Как видно, новый идентификатор пользователя 2 был передан в параметре процедуры, а тот же был заменен в запросе. Вызов процедуры теперь вставляет роли по умолчанию для идентификатора пользователя 2.
6. Возврат определенного значения из процедуры
До сих пор мы либо возвращали результаты выполнения процедуры, либо возвращали комментарий, выполняя запрос. Теперь необходимо выполнить определенный расчет и вернуть оцененное значение. Например, давайте посчитаем количество пользователей с определенной ролью. Чтобы подсчитать количество пользователей, вам необходимо выполнить поиск пользователей по определенной роли и получить количество записей. Для этого выполните приведенный ниже запрос.
|
1
2
3
4
5
6
7
|
$ mysql> select count(*) from user_roles where rolename = 'admin';+----------+| count(*) |+----------+| 3 |+----------+1 row in set (0.00 sec) |
Запрос выводит количество пользователей, как это видно выше. Это количество пользователей может быть возвращено как значение из процедуры. Для этого вам нужно выбрать значение аналогично комментарию. Код ниже показывает такую реализацию для вас.
|
1
2
3
4
5
6
7
8
9
|
DROP PROCEDURE countusers; DELIMITER // CREATE PROCEDURE countusers(role varchar(45)) BEGIN DECLARE usercount int ; select count(*) into usercount from user_roles where rolename = role; select usercount; END // DELIMITER ; |
Приведенный выше код создает процедуру, которая принимает значение count в переменную usercount и выбирает его для возврата связанного значения. Как можно заметить, выходные данные первого запроса не отображаются, поскольку они затмеваются следующим запросом. В процедуре вывод всегда зависит от запроса, который выполняется в конце, если промежуточный выбор переменной не выполняется между ними.
7. Условные потоки в процедуре
До сих пор мы видели простые варианты использования процедуры. Эти варианты использования включают запросы, которые на самом деле не сложны. В этом разделе мы продолжим условное ветвление потока процедуры. Это где настоящее применение процедуры вступает в картину. Условные потоки позволяют вам принять решение о том, какую операцию необходимо выполнить, основываясь на запросе или переданной переменной. Прежде чем мы начнем писать процедуру, содержащую регистр, давайте разберемся в его синтаксисе. В следующем фрагменте кода показан синтаксис процедуры для процедуры, содержащей случаи.
|
1
2
3
4
5
6
7
8
9
|
CREATE PROCEDURE procedure_nameBEGINCASE case_value WHEN expression THEN queries_to_be_executed [WHEN expression THEN queries_to_be_executed] ... [WHEN expression THEN queries_to_be_executed] ... [ELSE queries_to_be_executed]END CASEEND |
В приведенном выше синтаксисе элементы, упомянутые в квадратных скобках, являются необязательными. В приведенной выше процедуре WHEN указывает начало условия if. Ключевое слово CASE означает начало условного ветвления. Оператор выражения может быть любой переменной или выражением, которое оценивается. THEN используется для указания начала тела, если выражение оценивается как true.
Остальные утверждения не являются обязательными. Они используются для добавления дополнительных условий if и else, если ни одно из предоставленных выражений не оценено как true. Теперь приступим к написанию актуальной процедуры. Мы бы использовали простые операторы тела, чтобы хорошо понять условное ветвление. В общем, вы можете использовать любые операторы SQL-запросов в теле.
|
1
2
3
4
5
6
7
8
9
|
CREATE PROCEDURE firstcaseprocedure(variable varchar(10))BEGINCASE variable WHEN 'first' THEN select 'First condition executed'; WHEN 'second' THEN select 'Second condition executed'; WHEN 'third' THEN select 'Third condition executed'; ELSE select 'Else condition executed';END CASE;END; |
В приведенном выше коде внимательно проверьте точки с запятой и одинарные кавычки. Они являются основной причиной ошибок, если они вообще встречаются. После создания процедуры проверьте выполнение процедуры для каждого тестового случая. Тестирование процедуры было показано ниже.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
mysql> call firstcaseprocedure('first');+--------------------------+| First condition executed |+--------------------------+| First condition executed |+--------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call firstcaseprocedure('second');+---------------------------+| Second condition executed |+---------------------------+| Second condition executed |+---------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call firstcaseprocedure('third');+--------------------------+| Third condition executed |+--------------------------+| Third condition executed |+--------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call firstcaseprocedure('xyz');+-------------------------+| Else condition executed |+-------------------------+| Else condition executed |+-------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec) |
Как видно из приведенных выше результатов выполнения, полученный вывод является условным на основе предоставленного аргумента. Хотя мы предоставили аргумент, также возможно определить аргумент в процедуре. Например, рассмотрим процедуру, которая обновляет, прошел ли студент или нет. В такой процедуре процедура динамически извлекает оценки и проверяет, прошел ли студент или нет, и соответствующим образом обновляет запись.
Пример процедуры с аналогичной реализацией показан ниже. Предполагается, что таблица существует, так как пример служит только для пояснения варианта использования.
|
01
02
03
04
05
06
07
08
09
10
11
|
CREATE PROCEDURE passorfail(sid int)BEGINDECLARE marks int;select student_marks into marks from student_result where student_id = sid;CASE WHEN marks<35 THEN select 'Fail'; WHEN marks<50 THEN select 'C Grade'; WHEN marks<70 THEN select 'B Grade'; ELSE select 'A Grade';END CASE;END; |
Здесь внимательно обратите внимание на разницу в деле. Переменная case больше не является общей для операторов. Всякий раз, когда нам нужно использовать сравнение или логические операторы, мы должны использовать его так, как показано выше. marks<35 условий marks<35 могут быть заменены любым соответствующим условием, включающим также другую переменную.
В приведенном выше коде управление потоком происходит с помощью оператора CASE . Однако часто нам удобнее пользоваться IF… ELSE за простоту использования выражений. В отличие от CASE , где вам разрешено оценивать только одну конкретную переменную в условии, IF… ELSE позволяет проверять наличие нескольких переменных с разными комбинациями. Давайте разберемся в синтаксисе оператора IF… ELSE в процедуре.
|
1
2
3
4
|
IF search_condition THEN query_statements [ELSEIF search_expression THEN query_statements] ... [ELSE statement_list]END IF |
Вышеуказанный синтаксис может быть обновлен и использован в любом теле процедуры. Утверждения в квадратных скобках являются необязательными, и их можно избежать, если не требуется. Часть query_statements может содержать одно или несколько условий SQL, которые должны быть выполнены, если search_condition оценивается как true. Вышеуказанный оператор CASE… WHEN преобразуется в IF… ELSE в приведенном ниже коде.
|
01
02
03
04
05
06
07
08
09
10
|
CREATE PROCEDURE passorfail(sid int)BEGINDECLARE marks int;select student_marks into marks from student_result where student_id = sid;IF marks<35 THEN select 'Fail';ELSEIF marks<50 THEN select 'C Grade';ELSEIF marks<70 THEN select 'B Grade';ELSE select 'A Grade';END IFEND; |
Соблюдайте количество простоты, которую он вводит. Просто становится проще понять условия даже для непрофессионала.
8. Перебирая записи
Обратите внимание, что в приведенном выше случае использования оценок учащихся все, что мы делали, это печатали проходной балл студента на основе предоставленного идентификатора. Процедура, как обсуждалось ранее, используется для выполнения больших заранее определенных задач. Следовательно, было бы более полезно, если бы можно было обновить оценку каждого ученика, просто вызвав процедуру. Чтобы сделать это, давайте создадим таблицу и добавим к ней несколько деталей. Выполните следующие запросы, чтобы сделать то же самое.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
|
mysql> create table student_marks (student_id int, marks int, grade varchar(5));Query OK, 0 rows affected (0.12 sec)mysql> insert into student_marks values (1, 50,null),(2,60,null),(3,34,null),(4,70,null),(5,43,null);Query OK, 5 rows affected (0.07 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from student_marks;+------------+-------+-------+| student_id | marks | grade |+------------+-------+-------+| 1 | 50 | NULL || 2 | 60 | NULL || 3 | 34 | NULL || 4 | 70 | NULL || 5 | 43 | NULL |+------------+-------+-------+5 rows in set (0.01 sec) |
Теперь мы бы создали процедуру, которая проверяет оценки каждого учащегося и назначает оценку каждому учащемуся. Прежде чем продолжить, давайте разберемся в синтаксисе и терминологии. Фрагмент кода показывает синтаксис процедуры, содержащей цикл.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
CREATE PROCEDURE curdemo()BEGIN //Declaration of variables //Open cursors read_loop: LOOP //FETCH Cursors //Loop through cursors IF done THEN LEAVE read_loop; END IF; //BODY END IF; END LOOP; //Close cursorsEND; |
В приведенном выше синтаксисе следует отметить термин Cursor . Курсор похож на указатель на строку в базе данных. Вы можете назначить результирующий набор курсору. После назначения курсор может помочь вам перебирать записи одну за другой. read_loop используется для продолжения итерации с определенной метки. Таким образом, когда вы пишете read_loop:LOOP , код продолжает вращаться, пока не встретит оператор LEAVE read_loop . Теперь, когда мы поняли синтаксис, давайте напишем нашу первую процедуру с итерацией в ней.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
|
CREATE PROCEDURE iterdemo()BEGIN DECLARE done INT DEFAULT FALSE; DECLARE sid int; DECLARE mks int; DECLARE cur1 CURSOR FOR select student_id,marks from student_mks; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; read_loop: LOOP FETCH cur1 INTO sid, mks; IF done THEN LEAVE read_loop; END IF; IF mks<35 THEN update student_marks set grade ='Fail' where student_id = sid; ELSE update student_marks set grade ='Pass' where student_id = sid; END IF; END LOOP; CLOSE cur1;END; |
Вышеуказанная процедура присваивает итоговый статус учащегося — независимо от того, сдал ли он экзамен или нет на основании набранных баллов. Процедура выполняет цикл вокруг записей таблицы student_marks. Курсор в основном указывает на выбранные записи. Как видно из приведенного выше кода, курсор cur1 указывает на все записи таблицы student_marks. При выполнении цикла read_loop курсор cur1 используется для выборки данных строка за строкой. Оператор FETCH cur1 into sid,mks помещает данные текущей строки в переменные и перемещает курсор, чтобы указать на следующую строку. Таким образом, каждая строка обрабатывается для условия if, указанного в теле. Условие if проверяет оценки и решает, сдал ли студент экзамен или нет. Соответственно строки таблицы будут обновлены. При вызове процедуры вы можете проверить, чтобы результаты были похожи на показанный ниже:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
mysql> delimiter ;mysql> call iterdemo();Query OK, 0 rows affected (0.01 sec)mysql> select * from student_marks;+------------+-------+-------+| student_id | marks | grade |+------------+-------+-------+| 1 | 50 | Pass || 2 | 60 | Pass || 3 | 34 | Fail || 4 | 70 | Pass || 5 | 43 | Pass |+------------+-------+-------+5 rows in set (0.00 sec) |
Как можно видеть, обновляется только оценка ученика с оценками ниже 35. Мы также можем использовать лестницу IF ELSE, чтобы обновить точную оценку ученика. Код ниже делает то же самое.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
mysql> drop procedure iterdemo;Query OK, 0 rows affected (0.12 sec)mysql> DELIMITER //mysql> mysql> CREATE PROCEDURE iterdemo() -> BEGIN -> DECLARE done INT DEFAULT FALSE; -> DECLARE sid int; -> DECLARE mks int; -> DECLARE cur1 CURSOR FOR select student_id,marks from student_marks; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -> OPEN cur1; -> read_loop: LOOP -> FETCH cur1 INTO sid, mks; -> IF done THEN LEAVE read_loop; END IF; -> IF mks ELSEIF mks ELSEIF mks ELSE update student_marks set grade ='A' where student_id = sid; -> END IF; -> END LOOP; -> CLOSE cur1; -> END;//Query OK, 0 rows affected (0.02 sec)mysql> delimiter ;mysql> call iterdemo;Query OK, 0 rows affected (0.05 sec)mysql> select * from student_marks;+------------+-------+-------+| student_id | marks | grade |+------------+-------+-------+| 1 | 50 | B || 2 | 60 | B || 3 | 34 | Fail || 4 | 70 | A || 5 | 43 | C |+------------+-------+-------+5 rows in set (0.00 sec) |
Приведенный выше код обрабатывает значение меток для нескольких условий IF и соответственно обновляет оценку. Именно так процедура может помочь в решении основных задач с простотой.
9. Вывод
В заключение, процедура — это набор правил и запросов, которые должны быть выполнены для простоты выполнения задач, интенсивно использующих базу данных. С помощью этих процедур вы можете выполнять обычные запросы SQL, условно разделять поток запросов, выполнять итерацию по списку записей и выполнять массовое обновление при необходимости.