Изучаем хранимые процедуры MySQL
Хранимая процедура MySQL представляет собой подпрограмму, хранящуюся в базе данных. Она содержит имя, список параметров и операторы SQL. Все популярные системы управления базами данных поддерживают хранимые процедуры. Они были введены в MySQL 5.
Существует два вида подпрограмм: хранимые процедуры и функции, возвращающие значения, которые используются в других операторах SQL (например, pi()).
Основное отличие заключается в том, что функции могут использоваться, как любое другое выражение в операторах SQL, а хранимые процедуры должны вызываться с помощью оператора CALL.
Хранимые процедуры MySQL-основные преимущества
Хранимые процедуры MySQL работают быстро. Преимущество сервера MySQL заключается в том, что он использует кэширование, а также заранее заданные операторы. Основной прирост скорости дает сокращение сетевого трафика. Если есть повторяющиеся задачи, которые требуют проверки, обработки циклов, нескольких операторов, и при этом не требуют взаимодействия с пользователем, это можно реализовать с помощью одного вызова процедуры, которая хранится на сервере;
MySQL хранимые процедуры являются универсальными. При написании хранимой процедуры на SQL она будет работать на любой платформе, которая использует MySQL. В этом преимущество SQL над другими языками, такими как Java, C или PHP;
Исходный код хранимых процедур всегда доступен в базе данных. Это эффективная практика связать данные с процессами, которые их обрабатывают.
Создание процедуры в MySQL
По умолчанию процедура связана с базой данных, используемой в данный момент. Чтобы связать процедуру с конкретной базой данных, укажите ее создании хранимой процедуры: имя_базы_данных.имя_хранимой_процедуры. Полный синтаксис:
Перед тем, как осуществить MySQL вызов хранимой процедуры, необходимо получить определенную информацию.
Проверка версии MySQL
Следующая команда выводит версию MySQL:
Проверка привилегий текущего пользователя
Для команд CREATE PROCEDURE и CREATE FUNCTION требуются привилегия пользователя CREATE ROUTINE. Также может потребоваться привилегия SUPER, это зависит от значения DEFINER, которое будет описано далее. Если включен бинарный лог для CREATE FUNCTION, то может потребоваться привилегия SUPER. По умолчанию MySQL автоматически предоставляет для создателя подпрограммы привилегии ALTER ROUTINE и EXECUTE. Такое поведение можно изменить, отключив системную переменную automatic_sp_privileges:
Выбор базы данных
Перед тем создать процедуру MySQL, нужно выбрать базу данных. Давайте просмотрим список баз данных и выберем одну из них:
Теперь выберите базу данных "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 - см. https://www.internet-technologies.ru/articles/obzor-konstruktora-jimdo.html (Можно написать процедуру с помощью инструмента командной строки MySQL или с помощью MySQL Workbench.)
Вызов процедуры в MySQL
Оператор CALL используется для вызова процедуры, которая хранится в базе данных. Синтаксис следующий:
Хранимые процедуры MySQL, которые не принимают аргументов, могут вызываться без скобок. Поэтому CALL job_data() равносильно CALL job_data.
Давайте выполним процедуру:
SHOW CREATE PROCEDURE
Этот оператор является расширением MySQL. Он возвращает точную строку, которая может быть использована, чтобы воссоздать указанную хранимую процедуру. Синтаксис следующий:
Давайте осуществим MySQL вызов хранимой процедуры:
MySQL: блоки характеристик
В синтаксисе оператора CREATE PROCEDURE допустимо использование блоков, которые описывают характеристики процедуры. Блоки указываются после скобок, но перед телом процедуры. Эти блоки являются необязательными.
Например:
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, рассмотрим несколько составных операторов MySQL.
MySQL: составные операторы
Составной оператор представляет собой блок, который может содержать другие блоки: объявления переменных, обработчиков состояний и курсоров, конструкции управления потоками данных, циклы и условные тесты. В версии MySQL 5.6 существуют следующие составные операторы:
Составной оператор BEGIN ... END;
Метки операторов;
DECLARE;
Переменные в хранимых программах;
Операторы контроля потока данных;
Курсоры;
Обработчики условий.
В этом разделе мы рассмотрим первые четыре оператора, связанные с параметрами оператора CREATE PROCEDURE.
Синтаксис составного оператора BEGIN ... END
Он используется, когда нужно разместить в пределах подпрограммы (например, хранимой процедуры MySQL, функции, триггера или события) более одного оператора. Синтаксис следующий:
список_операторов: один или несколько операторов, завершающихся точкой с запятой (;). Сам по себе список операторов не является обязательным, поэтому пустой оператор BEGIN END является действительным.
Метки операторов
Метки - это разрешения на выполнение для блоков BEGIN ... END и операторов цикла REPEAT и WHILE. Синтаксис следующий:
При применении меток применяются следующие правила:
метка_начала должна закрываться двоеточием;
метка_начала может использоваться без метки_конца. Если метка_конца присутствует, она должна принадлежать тому же блоку, что и метка_начала;
метка_конца не может использоваться без метки_начала;
метки, принадлежащие к одному вложенному уровню, должны быть разделены;
метки могут иметь длину не более 16 символов.
Оператор DECLARE
Используется для определения различных локальных элементов при MySQL создании хранимой процедуры. Например, локальных переменных, условий, обработчиков, курсоров. DECLARE используется только внутри составного оператора BEGIN ... END и должен находиться в его начале перед всеми остальными операторами.
Для объявлений существуют следующие правила:
Объявления курсоров должны размещаться перед объявлениями обработчиков;
Объявления переменных и условий должны размещаться перед объявлениями курсоров или обработчиков.
Переменные в хранимых программах
Хранимые программы используют оператор DECLARE для определения локальных переменных. Процедуры и функции могут при объявлении принимать параметры, которые обмениваются значениями между подпрограммой и вызывающим ее агентом.
Объявление переменной:
Чтобы предоставить значение для переменной по умолчанию, используется блок 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 < 10000
MIN_SALARY = 10000
Для этого мы используем следующую процедуру (MySQL хранимой процедуры пример создан в MySQL Workbench 5.2 CE):
В приведенной выше процедуре мы передаем переменную salary через параметр IN. Есть оператор CASE с двумя блоками WHEN и ELSE, который проверяет условия и возвращает значение счетчика в no_employees. Выполним процедуру через командную строку MySQL.
Количество сотрудников, чья зарплата превышает 10000:
Количество сотрудников, чья зарплата меньше, чем 10000:
Количество сотрудников, чья зарплата равна 10000:
MySQL: оператор ITERATE
ITERATE означает "запустить цикл снова". ITERATE может использоваться только в операторах LOOP, REPEAT и WHILE. Синтаксис следующий:
MySQL: оператор LEAVE
Используется для выхода из конструкции управления потоком, который имеет заданную метку. Если метка задана для самого последнего блока хранимой процедуры MySQL, LEAVE выходит из программы.
LEAVE может использоваться в BEGIN ... END или конструкциях цикла (LOOP, REPEAT, WHILE). Синтаксис следующий:
MySQL: оператор LOOP
Используется, чтобы задать повторное выполнение списка операторов. Синтаксис следующий:
список_операторов состоит из одного или нескольких операторов, каждый из которых заканчивается точкой с запятой (;). Операторы внутри цикла повторяются до тех пор, пока цикл не будет завершен. Обычно для выхода из конструкции цикла используется LEAVE.
Также может использоваться оператор RETURN. Оператор LOOP может иметь метки.
Пример:
В приведенной ниже процедуре строки вставляются в таблицу 'number' до тех пор, пока х меньше, чем num (число заданное пользователем через параметр IN). Каждый раз сохраняется случайное число:
Теперь выполните MySQL хранимую процедуру:
MySQL: оператор REPEAT
REPEAT исполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец оператора:
список_операторов - список из одного или нескольких операторов, каждый из которых разделяется точкой с запятой (;). условие_поиска - выражение.
Оператор REPEAT может иметь метки.
Пример:
Четные числа - это числа, которые могут быть разделены на 2 без остатка. В следующей процедуре пользователь задает число через параметр IN и получает сумму четных чисел от 1 до установленного числа:
Теперь выполните хранимую процедуру MySQL:
MySQL: оператор RETURN
Оператор RETURN завершает выполнение хранимой процедуры MySQL и возвращает агенту, вызвавшему функцию, значение expr. В хранимой функции должен содержаться, по крайней мере, один оператор RETURN. Если функция имеет несколько точек выхода, она может содержать больше одного RETURN. Синтаксис следующий:
Этот оператор не используется в хранимых процедурах или триггерах. Вместо него применяется оператор LEAVE.
MySQL: оператор WHILE
Оператор WHILE выполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец цикла. Каждый оператор заканчивается точкой с запятой (;). Синтаксис следующий:
Оператор WHILE может иметь метки.
Пример:
Нечетные числа - это числа, которые не делятся на 2 без остатка. В следующей процедуре пользователь передает через параметр IN число и получает сумму нечетных чисел от 1 до заданного числа:
Теперь выполните MySQL хранимую процедуру:
MySQL: ALTER PROCEDURE
Используется для изменения характеристик хранимой процедуры MySQL. В операторе ALTER PROCEDURE может быть указано более одного значения для изменения. Но с его помощью нельзя изменить параметры или тело хранимой процедуры. Чтобы внести такие изменения, необходимо удалить и заново создать процедуру с помощью операторов DROP PROCEDURE и CREATE PROCEDURE. Синтаксис следующий:
Для выполнения этого оператора нужно иметь привилегию ALTER ROUTINE. По умолчанию эта привилегия автоматически предоставляется создателю процедуры. В предыдущей процедуре "my_proc_WHILE" раздел комментариев был пуст. Для ввода нового комментария или изменения предыдущего используйте следующую команду:
Можно проверить результат с помощью команды SHOW CREATE PROCEDURE, которую мы рассматривали ранее.
MySQL: DROP PROCEDURE
Используется для сброса в MySQL вызванной хранимой процедуры или функции. После чего указанная подпрограмма удаляется с сервера. Для этого нужно иметь привилегию ALTER ROUTINE. Если системная переменная automatic_sp_privileges включена, эта привилегия и привилегия EXECUTE автоматически предоставляются во время создания подпрограммы и сбрасываются во время удаления подпрограммы:
Блок IF EXISTS - это расширение MySQL. Он предотвращает возникновение ошибки, если процедура или функция не существует. Создается предупреждение, которое можно просмотреть с помощью SHOW WARNINGS. Например:
Можно проверить результат с помощью команды SHOW CREATE PROCEDURE, которую мы рассматривали ранее.
MySQL: курсоры
Курсор представляет собой структуру управления, которая позволяет обрабатывать записи в базе данных. Курсоры используются для обработки отдельных строк, возвращаемых в ответ на запросы системой базы данных. Курсор включает строки в набор результатов, чтобы последовательно их обработать.
В процедурах SQL курсор позволяет определить результирующий набор (набор строк данных) и выполнить сложную логику построчно. Используя те же механизмы, процедура SQL также может определить набор результатов и вернуть его непосредственно вызывающему агенту или в клиентское приложение.
MySQL хранимые процедуры поддерживают курсоры. Синтаксис тот же, что и для встроенного SQL. Курсоры имеют следующие свойства:
Asensitive: сервер может или не может создавать копию таблицы результатов;
Read only: не обновляемые;
Nonscrollable: обработка может производиться только в одном направлении, при этом пропуск строк не допускается.
Чтобы использовать курсор в процедурах MySQL, нужно сделать следующее:
Объявить курсор;
Открыть курсор;
Извлечь данные в переменные;
Закрыть курсор.
Объявление курсора
Следующий оператор объявляет курсор и связывает его с оператором SELECT. Он извлекает строки, которые будут перемещаться с помощью курсора:
Открытие курсора
После объявления мы открываем объявленный курсор:
Выборка данных в переменные
FETCH выбирает строки для оператора SELECT, связанного с указанным курсором (который должен быть открыт), и перемещает указатель курсора. Если строка существует, то выбранные столбцы сохраняются в указанных переменных. Число столбцов извлекаемых SELECT должно соответствовать количеству выходных переменных, указанных в FETCH:
Закрытие курсора
Этот оператор закрывает ранее открытый курсор. Если курсор не открыт, возникает ошибка:
Пример:
Хранимая процедура MySQL начинается с объявления трех переменных. При этом порядок имеет значение. Первыми объявляются переменные. После этого объявляются условия, затем – курсоры и обработчики. Если вы поместите их в неправильном порядке, то получите сообщение об ошибке:
Теперь выполните процедуру:
Управление доступом для встроенных программ
Для хранимых процедур 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 для вставки данных и так далее).
Last updated