mysql хранимые процедуры с параметрами
MySQL хранимые процедуры
Долго мучался с этим вопросом. Литературы в интернете мало, особенно на русском языке. Пришлось поспрашивать на зарубежных форумах, глубже покопаться в мануалах и разъяснить для себя некоторые непонятные моменты. Итак, коротко о хранимых процедурах в MySQL.
Stored procedures — что это?
Хранимые процедуры появились начиная с 5 версии MySQL. Они позволяют автоматизировать сложные процессы на уровне MySQL, нежели использовать для этого внешние скрипты. Это даёт нам наиболее высокую скорость выполнения, т.к. мы не гоняем большое количество запросов, а всего лишь один раз вызываем ту или иную процедуру (или функцию).
Первая хранимая процедура
Итак, открываем MySQL Administrator, подключаемся к серверу MySQL и создаем новую схему (базу данных): щелкните Catalogs, выберите Create New Schema в области Schemata (Ctrl+N). Назовите ее как-нибудь (например db). Откройте только что созданную схему, выберите вкладку Stored procedures и щелкните кнопку Create Stored Proc. Назовите свою процедуру procedure1. В тело процедуры (между BEGIN и END) впишите следующее:
И нажмите Execute SQL — процедура создана. Откройте MySQL Query Browser, выберите свою схему (db) и впишите следующий запрос:
Вуала! Поздравляю.
Переменные в MySQL
Для того, чтобы извлечь каку-то пользу от хранимых процедур в MySQL, вам придется поработать с переменными. Так как это не входи в рамки данной статьи, покажу лишь несколько примеров.
Простые переменные
Создание хранимых процедур в MySQL
Хранимые процедуры — это определяемый пользователем список предварительно скомпилированных инструкций SQL, которые сохраняются и используются по запросу в банке данных MySQL для выполнения определенного процесса базы данных. Это означает, что существует вероятность повторного использования сценария снова и снова. У процедуры действительно есть заголовок, набор параметров и инструкция из SQL, так что хранимая процедура может работать на основе значения (значений) переданного параметра. Хранимые процедуры должны выполняться с использованием фразы CALL. В этом руководстве давайте подробно рассмотрим процесс создания новых хранимых процедур в предложении MySQL CREATE PROCEDURE. Итак, приступим.
Создать хранимую процедуру с помощью Workbench
Откройте только что установленную MySQL Workbench 8.0 и подключите ее к корневой базе данных localhost.
Внутри Workbench есть панель навигатора. Под этой панелью навигатора находится набор различных функций MySQL. Он включает в себя список баз данных, таблиц, хранимых процедур и многое другое, как вы можете видеть на изображении.
Когда вы исследуете опцию «Таблицы», вы найдете список таблиц в виде сетки. Как показано ниже, у нас есть таблица «социальные».
Щелкните правой кнопкой мыши параметр «Сохраненная процедура» и выберите параметр «Создать хранимую процедуру», как показано на рисунке.
Откроется новое окно, как показано на фото ниже. Вы можете изменить имя хранимой процедуры в области запроса, удалив значение в кавычках.
Теперь вы можете редактировать этот запрос по своему желанию. Мы называем хранимую процедуру «detail» при получении данных из таблицы «social», где ее значение в столбце «Website» эквивалентно значению «Instagram». Это означает, что процедура хранения при выполнении будет отображать только те записи из этой таблицы, где «веб-сайт» — «Instagram». Щелкните по кнопке Применить.
Вы получите экран предварительного просмотра, где вы можете изменить или изменить свой запрос, если это необходимо. Вы можете увидеть полный синтаксис вновь созданной хранимой процедуры через Workbench. Нажмите кнопку «Применить», чтобы выполнить его.
Если в запросе нет ошибок, он будет работать правильно, как показано ниже. Нажмите на кнопку «Готово».
Когда вы посмотрите на параметр хранимых процедур и обновите его, он покажет вам только что созданную процедуру.
Когда вы выполните эту процедуру, она покажет вам, что единственными записями, имеющими значение столбца «Веб-сайт», является «Instagram», как показано ниже.
Создайте хранимую процедуру через оболочку командной строки
Откройте клиентскую оболочку командной строки MySQL 8.0 и введите пароль MySQL внизу.
Предположим, у нас есть таблица с именем «запись» в схеме базы данных «данные». Давайте проверим его записи с помощью команды SELECT следующим образом:
Используйте команду «использовать данные», чтобы использовать базу данных, в которую вы хотите добавить хранимую процедуру.
Пример 1: Хранимая процедура без параметров
Чтобы создать процедуру без параметра, вы должны создать ее с помощью команды CREATE PROCEDURE, перед которой стоит ключевое слово «DELIMITER». Затем мы создаем процедуру с именем «Фильтр» без параметров. Он выбирает все записи из таблицы «запись», где в столбце «Страна» в конце значений стоит «ia». Процесс должен быть завершен ключевым словом «END».
Мы будем использовать предложение CALL для выполнения хранимой процедуры в командной строке. После выполнения команды CALL мы получаем следующие результаты. Вы можете видеть, что запрос должен получить только те записи, в которых столбец «Страна» имеет «ia» в конце значений.
Пример 2: Хранимая процедура с одним параметром
Пришло время сгенерировать процедуру с одним параметром. Для этого используйте запрос CREATE PROCEDURE с ключевым словом DELIMITER. Итак, мы должны создать процедуру «Rec», которая принимает одно значение в качестве входного аргумента, в котором в этом примере в параметрах пользователя указана переменная «Var1». Начните процедуру с ключевого слова BEGIN. Оператор SELECT используется для выборки всех записей из таблицы ’record’, где столбец ’Name’ имеет то же значение, что и в ’Var1′. Это сопоставление записей. Завершите хранимую процедуру ключевым словом «END», за которым следует знак «&&».
Сначала запустите запрос DELIMITER, чтобы хранимая процедура подготовилась. После этого выполните запрос CALL, после которого укажите имя процедуры и значение ее входного аргумента в фигурных скобках. Вам просто нужно запустить команду, показанную ниже, и вы получите результат. Как мы и предусмотрели в параметрах «Зафар», поэтому после сравнения мы получили такой результат.
Пример 3: Хранимая процедура с несколькими параметрами
Давайте посмотрим, как работает процедура, когда ей предоставлено несколько параметров. Не забудьте использовать ключевое слово «DELIMITER» вместе со знаками «&&». Используйте команду CREATE PROCEDURE, чтобы создать процедуру «New». Эта процедура будет принимать в параметрах два аргумента, например, «var1» и «var2». Начните процедуру с предложения BEGIN. Теперь это что-то новенькое. Предложение SELECT снова выбирает все записи из таблицы ’record’. Первый аргумент, переданный пользователем, будет сопоставлен со значениями столбца «Имя». С другой стороны, второй аргумент, переданный пользователем, будет сопоставлен со значениями столбца «Страна». Если записи совпадают, он будет извлекать все данные из последовательных строк. Процедура будет завершена ключевым словом «END».
Используйте ключевое слово DELIMITER, чтобы активировать процедуру. После этого выполните предложение CALL, за которым следует имя хранимой процедуры. Которое является «New» вместе со значениями параметров. Из изображения ниже видно, что запрос будет извлекать только запись таблицы «запись», в которой совпадают оба значения, введенные пользователем.
Заключение
В этом руководстве вы узнали о различных способах создания хранимой процедуры в MySQL Workbench и клиентской оболочке командной строки MySQL, например, о хранимой процедуре с параметрами и без них.
Хранимые процедуры в MySQL
По долгу службы приходится глубоко разбираться с сабжем.
К сожалению, это не самое лучшее изобретение человечества, поэтому иногда приходится вбивать костыли, чтобы хоть как-то пользоваться этой штукой.
Итак, имеется хранимая процедура, созданная пользователем А.
Мы даем к ней доступ пользователю Б.
GRANT EXECUTE ON PROCEDURE
Пользователь Б может пользоваться этой процедурой. Все рады, музыка, шампанское.
После фуршета пользователь A вспоминает, что хорошо бы чуток подправить процедуру, чтобы она работала быстрее.
И подправляет. Наутро пользователь Б обнаруживает, что больше не может выполнять эту процедуру.
Все в трауре, миллионные потери, разработчики проекта уволены, занавес.
Что же произошло?
Дело в том, что люди из MySQL почему-то не подумали, что необходимость поменять процедуру возникает очень часто.
И не сделали возможность изменять код процедуры. ALTER PROCEDURE дает менять какие-то мало вразумительные настройки и не дает менять тела процедуры. Вместо этого ребята из Мускула предлагают делать ей DROP и потом CREATE с новым текстом: «you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure». А при удалении процедуры все GRANT’ы на нее, понятное дело, исчезают.
Можно ли было что-то сделать?
Как оказалось, можно.
Текст процедур хранится в таблице proc базы mysql.
И хотя товарищи из Мускула не рекомендуют не лезть в эту таблицу руками, но иного выбора не предоставляют.
В этой таблице нас интересуют два поля — body и body_utf8.
В них содержится текст нашей процедуры.
Берем за хобот нашего DBA и делаем UPDATE на этих полях, занося в них новый текст процедуры.
Казалось бы, время опять устраивать фуршет по поводу успешной борьбы с Мускулом.
Ан нет. Процедура начнет работать по-новому только для новых сессий пользователя Б.
А как быть, если коннекты перманентные и надо сделать изменения доступными и для них?
А вот тут поможет тот самый ALTER PROCREDURE.
Банальное изменения текста комментария к процедуре (например, изменение номера ее ревизии) делает доступными изменения для всех сессий:
Создание хранимых процедур
На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице «Туристы» по заданной фамилии создаем следующую процедуру:
После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля «Фамилия» установлен этот тип. Попытаемся запустить процедуру:
Появляется диагностическое сообщение (рис. 5.5):
Перевод этого сообщения: «Процедура ‘proc_p1’ ожидает параметр ‘@Фамилия’, который не указан».
Запустим процедуру так:
В результате выводится запись, соответствующая фамилии «Андреева» (рис. 5.6):
Если мы укажем фамилию, которая не содержится в таблице, появится пустая запись (рис. 5.7):
В таблице 5.2 приводятся примеры хранимых процедур с параметрами.
