Статьи

Введение в хранимые процедуры в MySQL 5

Март 4, 2019

В MySQL 5 появилось множество новых функций — хранимые процедуры являются одними из самых значительных. В этом уроке мы сосредоточимся на том, кем они являются, и как они могут облегчить вашу жизнь.

Если вы много работаете с MySQL, вы можете проверить диапазон сценариев и плагинов кода MySQL на Envato Market.

Вступление

«Хранимая подпрограмма — это набор операторов SQL, которые могут храниться на сервере».

Хранимая процедура — это метод для инкапсуляции повторяющихся задач. Они допускают объявления переменных, управление потоком и другие полезные методы программирования.

«Академическая» позиция по этому вопросу достаточно ясна и поддерживает широкое использование хранимых процедур. С другой стороны, если вы посмотрите на мнение тех, кто работает с ними изо дня в день, вы заметите, что реакции варьируются от полной, непоколебимой поддержки до полной ненависти. Имейте это в виду.

Pros

  • Поделитесь логикой с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это гарантирует, что доступ к данным и манипулирование ими согласованы между различными приложениями.
  • Изолировать пользователей от таблиц данных. Это дает вам возможность предоставить доступ к хранимым процедурам, которые манипулируют данными, но не напрямую к таблицам.
  • Обеспечить механизм безопасности . С учетом предыдущего пункта, если вы можете получить доступ только к данным, используя определенные хранимые процедуры, никто другой не сможет выполнить инструкцию DELETE SQL и стереть ваши данные.
  • Для повышения производительности, потому что это уменьшает сетевой трафик. С помощью хранимой процедуры несколько вызовов можно объединить в один.

Cons

  • Увеличена нагрузка на сервер базы данных — большая часть работы выполняется на стороне сервера и меньше на стороне клиента.
  • Есть достойная кривая обучения . Вам нужно изучить синтаксис операторов MySQL, чтобы писать хранимые процедуры.
  • Вы повторяете логику вашего приложения в двух разных местах: код вашего сервера и код хранимых процедур, что усложняет обслуживание.
  • Перенос на другую систему управления базами данных (DB2, SQL Server и т. Д.) Потенциально может быть более сложным.

Инструмент, с которым я работаю в этом руководстве, MySQL Query Browser , является довольно стандартным для взаимодействия с базой данных. Инструмент командной строки MySQL — еще один отличный выбор. Я отмечаю это, потому что популярный phpMyAdmin не поддерживает выполнение хранимых процедур.

Кроме того, я буду использовать очень элементарные структуры таблиц, чтобы облегчить объяснение. Я хвастаюсь хранимыми процедурами, и они достаточно сложны, не беспокоясь о больших таблицах.

Шаг 1 — Выбор разделителя

Разделитель — это символ или строка символов, которые вы будете использовать, чтобы сообщить клиенту mySQL, что вы закончили вводить оператор SQL. Веками разделитель всегда был точкой с запятой. Это, однако, вызывает проблемы, потому что в хранимой процедуре может быть много операторов, и каждый из них должен заканчиваться точкой с запятой. В этом уроке я буду использовать «//»

Шаг 2 — Как работать с хранимой процедурой

Создание хранимой процедуры

01
02
03
04
05
06
07
08
09
10
DELIMITER //
 
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ‘A procedure’
BEGIN
    SELECT ‘Hello World !’;
END//

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

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

Четыре характеристики процедуры:

  • Язык : для целей мобильности; значение по умолчанию — SQL.
  • Детерминированный : если процедура всегда возвращает одинаковые результаты при одинаковых входных данных. Это для целей репликации и ведения журнала. Значение по умолчанию NOT DETERMINISTIC .
  • Безопасность SQL : во время вызова проверьте привилегии пользователя. INVOKER — это пользователь, который вызывает процедуру. DEFINER является создателем процедуры. Значением по умолчанию является DEFINER .
  • Комментарий : для целей документирования; значение по умолчанию ""

Вызов хранимой процедуры

Чтобы вызвать процедуру, вам нужно всего лишь ввести слово CALL , затем имя процедуры и затем круглые скобки, включая все параметры между ними (переменные или значения). Скобки обязательны. 

  CALL имя_процедура

 CALL method1 (10, «строковый параметр», @parameter_var);

Изменить хранимую процедуру

MySQL предоставляет инструкцию ALTER PROCEDURE для изменения подпрограммы, но допускает только возможность изменять определенные характеристики. Если вам нужно изменить тело или параметры, вы должны удалить и заново создать процедуру.

Удалить сохраненную процедуру

1
DROP PROCEDURE IF EXISTS p2;

Это простая команда. Предложение IF EXISTS предотвращает ошибку, если процедура не существует.

Шаг 3 — Параметры

Давайте рассмотрим, как вы можете определить параметры в хранимой процедуре.

  • CREATE PROCEDURE proc1 () : список параметров пуст
  • CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : один входной параметр. Слово IN является необязательным, поскольку параметры IN (входные) по умолчанию.
  • CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : один выходной параметр.
  • CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : один параметр, который является как входным, так и выходным.

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

В примере

1
2
3
4
5
6
DELIMITER //
 
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
    SELECT var1 + 2 AS result;
END//

Пример OUT

1
2
3
4
5
6
DELIMITER //
 
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
    SET var1 = ‘This is a test’;
END //

Пример INOUT

1
2
3
4
5
6
DELIMITER //
 
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
    SET var1 = var1 * 2;
END //

Шаг 4 — Переменные

Следующий шаг научит вас, как определять переменные и сохранять значения внутри процедуры. Вы должны объявить их явно в начале блока BEGIN/END вместе с их типами данных. После того как вы объявили переменную, вы можете использовать ее везде, где вы можете использовать переменную сеанса, или литерал, или имя столбца.

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

  ОБЪЯВИТЬ varname DATA-TYPE DEFAULT defaultvalue;

Давайте объявим несколько переменных:

1
2
3
4
5
6
7
DECLARE a, b INT DEFAULT 5;
 
DECLARE str VARCHAR(50);
 
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
 
DECLARE v1, v2, v3 TINYINT;

Работа с переменными

После того, как переменные были объявлены, вы можете присвоить им значения, используя команду SET или SELECT :

01
02
03
04
05
06
07
08
09
10
11
12
13
DELIMITER //
 
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
    DECLARE a, b INT DEFAULT 5;
    DECLARE str VARCHAR(50);
    DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
    DECLARE v1, v2, v3 TINYINT;
 
    INSERT INTO table1 VALUES (a);
    SET str = ‘I am a string’;
    SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;
END //

Шаг 5 — Структуры управления потоком

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоком в хранимых программах. Мы рассмотрим, как конкретно использовать IF , CASE и WHILE , поскольку они являются наиболее часто используемыми операторами в подпрограммах.

IF заявление

С помощью оператора IF мы можем обрабатывать задачи, которые включают условия:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
DELIMITER //
 
CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;
     
    IF variable1 = 0 THEN
        SELECT variable1;
    END IF;
 
    IF param1 = 0 THEN
        SELECT ‘Parameter value = 0’;
    ELSE
        SELECT ‘Parameter value <> 0’;
    END IF;
END //

CASE заявление

Оператор CASE — это еще один способ проверить условия и выбрать подходящий путь. Это отличный способ заменить несколько операторов IF . Заявление может быть написано двумя различными способами, обеспечивая большую гибкость для обработки нескольких условий.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
DELIMITER //
 
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;
     
    CASE variable1
        WHEN 0 THEN
            INSERT INTO table1 VALUES (param1);
        WHEN 1 THEN
            INSERT INTO table1 VALUES (variable1);
        ELSE
            INSERT INTO table1 VALUES (99);
    END CASE;
 
END //

или:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
DELIMITER //
 
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;
     
    CASE
        WHEN variable1 = 0 THEN
            INSERT INTO table1 VALUES (param1);
        WHEN variable1 = 1 THEN
            INSERT INTO table1 VALUES (variable1);
        ELSE
            INSERT INTO table1 VALUES (99);
    END CASE;
 
END //

WHILE заявление

Технически существует три стандартных цикла: циклы WHILE циклы LOOP циклы REPEAT . У вас также есть возможность создать цикл, используя «Дарта Вейдера» техник программирования: GOTO . Проверьте этот пример цикла в действии:

01
02
03
04
05
06
07
08
09
10
11
12
13
DELIMITER //
 
CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
    DECLARE variable1, variable2 INT;
    SET variable1 = 0;
     
    WHILE variable1 < param1 DO
        INSERT INTO table1 VALUES (param1);
        SELECT COUNT(*) INTO variable2 FROM table1;
        SET variable1 = variable1 + 1;
    END WHILE;
END //

Шаг 6 — Курсоры

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

MySQL поддерживает cursor в хранимых процедурах. Вот краткое изложение основного синтаксиса для создания и использования курсора. 

  ОБЪЯВИТЬ имя курсора CURSOR FOR SELECT ...;  / * Объявить и заполнить курсор с помощью инструкции SELECT * /
 ОБЪЯВИТЬ ПРОДОЛЖИТЬ ОБРАЩЕНИЕ ДЛЯ НЕ НАЙДЕННЫХ / * Укажите, что делать, если больше не найдено записей * /
 ОТКРЫТЬ курсор-имя;  / * Открыть курсор для использования * /
 FETCH имя курсора INTO variable [, variable];  / * Назначить переменные с текущими значениями столбца * /
 ЗАКРЫТЬ курсор-имя;  / * Закрыть курсор после использования * /

В этом примере мы выполним несколько простых операций с помощью курсора:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DELIMITER //
 
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur1;
 
    SET b = 0;
    SET c = 0;
    
    WHILE b = 0 DO
        FETCH cur1 INTO a;
        IF b = 0 THEN
            SET c = c + a;
    END IF;
    END WHILE;
 
    CLOSE cur1;
    SET param1 = c;
 
END //

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

  • Asensitive : после открытия курсор не будет отражать изменения в своих исходных таблицах. Фактически, MySQL не гарантирует, что курсор будет обновлен, поэтому вы не можете на него полагаться.
  • Только для чтения : курсоры не обновляются.
  • Not Scrollable : Курсоры можно перемещать только в одном направлении, вперед, и вы не можете пропустить выборку записей.

Вывод

В этом уроке мы рассмотрели основы хранимых процедур и некоторые их специфические свойства. Конечно, вы должны продолжить обучение в таких областях, как безопасность, операторы SQL и производительность, прежде чем вы сможете освоить процедуры MySQL.

Вы должны оценить преимущества, которые хранимые процедуры могут принести вашим приложениям, а затем сделать разумную реализацию, соответствующую вашим требованиям. Я обычно использую процедуры; их преимущества в плане безопасности, сопровождения кода и разработки программного обеспечения делают их достойными использования, на мой взгляд. Кроме того, помните, что процедуры в MySQL все еще находятся в стадии разработки. Вы должны полностью ожидать улучшения с точки зрения функциональности и производительности в будущем.

Пожалуйста, не стесняйтесь комментировать и делиться своими идеями и мнениями. И посмотрите на скрипты и плагины кода MySQL на Envato Market, чтобы узнать, найдете ли вы что-нибудь, что вам там поможет.

65
Share: