Хранимая процедура – это самостоятельная часть программного код, которую создают и хранят в БД MySQL.
В состав хранимой процедуры могут входить SQL-операторы и специальные управляющие структуры. Удобна в использовании, если одну и ту же функцию нужно выполнить из разных приложений или с разных платформ, или в качестве средства сокрытия функциональных возможностей. В базе данных хранимые процедуры считаются аналогом объектно-ориентированного подхода в программировании. С помощью хранимых процедур можно управлять способом доступа к данным.
Создание хранимой процедуры
Создадим процедуру с именем GetAllProducts() для получения списка всех продуктов из таблицы.
Для этого нужно загрузить mysql-клиент и выполнить следующие команды:
Команда DELIMITER // не входит в хранимые процедуры. DELIMITER – специальная команда, изменяющая стандартный разделитель запросов (по умолчанию «;») на указанный после нее. Этой командой изменим его на 2 слеша (//).
Если не изменить разделитель, то mysql ошибочно интерпретирует процедуру и выдаст ошибку. После END используется разделитель // и с помощью команды DELIMITER возвращается значение разделителя «;».
Зарезервированные слова CREATE PROCEDURE указывают mysql, что нужно СОЗДАТЬ ПРОЦЕДУРУ. После этих слов нужно указать название хранимой процедуры (в примере GetAllProducts). Пустые скобки «()» после названия процедуры означает, что процедура не принимает никаких переменных.
Команды BEGIN и END соответственно открывают и закрывают блок кода SQL.
В консоли mysql хранимые процедуры писать не очень удобно. В таком случае можно воспользоваться GUI tools, с помощью которых можно создавать хранимые процедуры в интуитивно понятном интерфейсе.
В MySQL Workbench процедура создается нажатием правой кнопкой мыши на Routines и выбором в выпадающем меню пункта Create Procedure…
Далее вводим название хранимой процедуры и нажимаем кнопку Apply.
Можно просмотреть полный код, который отправится в MySQL, перед тем, как он запишется в базу данных. Если ошибок нет, нажимаем Apply.
После компиляции MySQL записывает процедуру в каталог. После завершения записи нажимаем кнопку Finish.
Можно увидеть созданную хранимую процедуру в списке Routines.
Вызов хранимой процедуры
Для вызова хранимой процедуры используется встроенная SQL команда CALL (ВЫЗВАТЬ):
Вместо STORED_PROCEDURE_NAME указывается имя хранимой процедуры, а в скобках указывается список переменных. Для вызова созданной процедуры без переменных будет использоваться код:
Т.к. процедура должна выполнять команду *SELECT FROM products;**, т.е. ВЫБРАТЬ все ИЗ таблицы ПРОДУКТЫ, то примером ее работы может быть следующий результат:
Переменные в хранимых процедурах
Переменная является именованным объектом данных, содержащим некоторое значение при выполнении хранимой процедуры.
Их используют для сохранения какого-либо результата запроса или выражения. Переменные в хранимых процедурах являются локальными, т.е. их нельзя вызвать извне.
Декларирование переменных
Процесс сообщения mysql о том, что нужно использовать переменную, называют декларированием.
Для декларирования переменных используется оператор DECLARE:
DECLARE – зарезервированный оператор.
variable_name – название переменной, к которому применяют такие же правила, что и к названию столбца данных в MySQL.
datatype(size) – тип переменной, которая используется, и ее размер. Переменные в MySQL могут принимать значения любого типа данных, например DATETIME, VARCHAR, INT и др.
DEFAULT default_value – позволяет задавать начальное значение переменной. Если оно не задано, то будет установлено значение по умолчанию NULL.
Для создания переменной total_sale, в которой будет храниться список покупок типа INT, и которая по умолчанию будет равна 0, запишем код:
Для декларирования нескольких переменных одного типа можно записать код:
или:
Присвоение значений переменной
Для присвоения значения переменной используют оператор SET.
К примеру, декларируем переменную total_count, а затем присвоим ей значение 10:
Помимо оператора SET может использоваться оператор SELECT INTO для передачи результата запроса в переменную. Обратим внимание, что запрос должен вернуть скалярное значение (т.е. одно).
В первой строке кода объявляется переменная total_products и устанавливается ее значение в 0.
Во второй и третьей строке используется оператор SELECT INTO для записи результата выполнения запроса SELECT COUNT(*) FROM products в созданную переменную.
Область видимости переменных
Каждая переменная имеет свою область видимости, определяющую время ее жизни.
Если создать переменную внутри хранимой процедуры, то она будет существовать, пока используется.
Если декларировать переменную внутри блока BEGIN END, то она будет существовать лишь в пределах этого блока.
Переменные со знаком @ вначале названия являются глобальными, т.е. они являются доступными на протяжении всей сессии.
