Лабораторная работа № 5
Создание и вызов хранимых процедур
Цель работы: изучить операции по созданию хранимых процедур на сервере MySQL.
Методические указания
Хранимая процедура это программа, написанная на процедурном языке сервера, причем в этом языке можно использовать SQL-инструкции.
Хранимая процедура после создания хранится на сервере в скомпилированном виде, что позволяет ускорить выполнение запросов пользователя.
Для создания хранимой процедуры предназначен оператор create procedure, который имеет следующий синтаксис:
CREATE PROCEDURE имя_хранимой_процедуры (параметры) [характеристики] тело_процедуры
Параметры позволяют:
передать в процедуру входные данные (параметры IN);
зафиксировать результаты выполнения процедуры в выходных
данных (параметры OUT).
Описание параметра имеет следующий вид:
[ IN | OUT | INOUT ] имя_параметра тип
Имя параметра предваряет одно из ключевых слов in, out, inout,
которые позволяют задать направление передачи данных:
in – параметры передаются внутрь хранимой процедуры для использования в вычислениях и условных инструкциях;
out – параметры имеют значения, вычисленные в хранимой процедуре,
и используются после ее завершения;
inout – параметры являются одновременно in-параметрами и out-
параметрами. Их значения передаются в хранимую процедуру, могут быть изменены в ней и выдаются вовне в качестве результатов выполнения.
Типом параметра может быть любой из допустимых в MySQL типов.
Тело процедуры состоит из составного оператора begin …end, внутри которого могут располагаться другие операторы, в том числе и другие составные операторы begin…end.
В теле хранимой процедуры может встретиться символ ‘;’, который означает отправку запроса на сервер, при этом весь последующий текст процедуры не будет воспринят сервером и инструкция создания процедуры не будет выполнена. Чтобы этого избежать, перед созданием хранимой процедуры нужно задать командой delimiter другой разделитель. После создания хранимой процедуры можно командой delimiter вернуться к прежнему разделителю.
Хранимая процедура вызывается оператором call.
Существуют глобальные и локальные переменные. Глобальные переменные используются как внутри хранимой процедуры, так и вне ее и являются inout-параметрами. Локальные переменные используются только внутри хранимой процедуры. Для глобальных переменных перед именем обязателен символ @. Тип глобальной переменной сервер определяет по ее значению.
Существуют два способа назначить глобальной переменной значение:
1.оператор set;
2.оператор select…into…from.
Оператор set присваивает глобальной переменной значение.
set имя_переменной = значение;
Оператор select…into…from позволяет сохранять результат инструкции select без его немедленного вывода.
select список_полей into список_локальных_переменных from
имя_таблицы
Можно объявить независимую от входных параметров локальную переменную при помощи оператора declare, который имеет следующий синтаксис:
DECLARE имя тип [DEFAULT значение]
Один оператор declare позволяет объявить сразу несколько переменных одного типа, причем необязательное слово default позволяет назначить инициирующее значение.
Оператор declare может появляться только внутри блока begin…end,
область видимости объявленной переменной также ограничена этим блоком.
Это означает, что в разных блоках begin…end могут быть объявлены переменные с одинаковыми именами, и действовать они будут только внутри своих блоков, не пересекаясь с переменными других блоков.
Переменная, объявленная во внешнем блоке begin…end, будет доступна во вложенном блоке, если не будет объявлена экранирующая ее переменная. Обратное неверно: переменная, объявленная во вложенном блоке, недоступна во внешнем.
Помимо ключевого слова default, позволяющего присваивать значение переменной при ее объявлении, допускается присвоение значения переменной в теле процедуры или функции.
Оператор if позволяет реализовать ветвление программы по условию и имеет следующий синтаксис:
IF логическое_выражение THEN действие
[ELSEIF логическое_выражение THEN действие] …
[ELSE действие] END IF
Логическое выражение может принимать два значения. Для создания логических выражений можно использовать все операторы сравнения (=, >, >=, < >, <, <=). Кроме того, логические выражения можно комбинировать,
используя логические операции И, ИЛИ, НЕ.
Если логическое выражение истинно, то выполняются операторы в соответствующем блоке then, иначе проверяется условие в следующем блоке elseif, если оно истинно, то выполняются операторы в соответствующем блоке then, и т.д. Если после проверки всех логических выражений не найдено ни одно истинное, то выполняются операторы в блоке else (если блок else имеется).
Оператор case позволяет осуществить множественный выбор и имеет две формы. Синтаксис первой формы оператора выглядит следующим образом:
CASE case_значение
WHEN when_значение THEN действие
[WHEN when_значение THEN действие] … [ELSE действие]
END CASE
Синтаксис второй формы:
CASE
WHEN логическое_выражение THEN действие
[WHEN логическое_выражение THEN действие] … [ELSE действие]
END CASE
В первой форме оператор case сравнивает выражение case_значение с when_значение. Как только соответствие найдено, выполняется необходимое действие. Если ни одно соответствие не найдено, выполняется действие,
размещенный после ключевого слова else (если оно присутствует).
Вторая форма оператора case позволяет осуществлять сравнение непосредственно в конструкции when – как только будет найдено первое истинное значение, выполняется действие, и процедура выходит из оператора case.
Если в одном блоке when необходимо выполнить несколько запросов,
следует использовать блок begin…end.
Циклы являются важнейшей конструкцией, без которой хранимые процедуры и функции не имели бы достаточно функциональности. Таблицы,
как правило, имеют множество записей, поэтому циклическая обработка данных встречается в SQL-программировании достаточно часто.
Оператор while выполняет цикл и имеет следующий синтаксис:
[label:] WHILE условие DO
Тело_цикла
END WHILE [label]
Цикл while выполняет операторы тело_цикла до тех пор, пока условие истинно. При каждой итерации условие проверяется, и если при очередной проверке оно будет ложным, цикл завершит свое выполнение. Это означает,
что если условие ложно с самого начала, цикл не выполнит ни одной итерации.
Необязательной меткой label можно отметить начало цикла и выход
(или выходы) из него.
Для досрочного выхода из цикла предназначен оператор leave, который имеет следующий синтаксис:
LEAVE label
Оператор leave прекращает выполнение блока, помеченного меткой
label.
Еще одним оператором, выполняющим досрочное прекращение цикла,
является оператор iterate, который имеет следующий синтаксис:
ITERATE label
В отличие от оператора leave, оператор iterate не прекращает выполнение цикла, он лишь выполняет досрочное прекращение текущей итерации.
Оператор repeat, так же как и оператор while, реализует цикл:
[label:] REPEAT
Тело цикла
UNTIL условие
END REPEAT [label]
Отличительной особенностью данного цикла является тот факт, что условие цикла проверяется не в начале, как в цикле while, а в конце оператора (ключевое слово until). Таким образом, цикл выполняет, по крайней мере, одну операцию независимо от условия. Следует отметить, что цикл repeat выполняется, пока условие ложно.
Оператор loop предназначен для реализации циклов и имеет следующий синтаксис:
[label:] LOOP
Тело цикла
END LOOP [label]
Цикл loop, в отличие от операторов while и repeat, не имеет условий выхода. Поэтому данный вид цикла должен обязательно иметь в своем составе оператор leave.
Просмотреть список уже созданных хранимых процедур можно при помощи оператора show procedure status, который имеет следующий синтаксис:
SHOW PROCEDURE STATUS [LIKE ‘pattern’];
Оператор возвращает список хранимых процедур, который содержит перечень хранимых функций. При использовании ключевого слова like
можно вывести информацию только о тех процедурах, имена которых удовлетворяют шаблону pattern.
Пример:
Для начала установим разделитель, чтобы символ ‘;’ не воспринимался как сигнал к отправке запроса на сервер.
DELIMITER //
Создадим процедуру с параметром, который принимается во внимание как внутри процедуры, так и сохраняет свое значение после выхода из нее. Так, мы сможем вычислить переменную внутри процедуры и сохранить ее значение после выполнения.
CREATE PROCEDURE pr1 (INOUT par INT)
BEGIN
SET @x = par;
Set par = par – 5;
End //
Зададим значение параметра и вызовем процедуру.
SET @y = 13 //
CALL pr1 (@y) //
Для проверки правильности выполняем запрос.
SELECT @x, @y //
Результат выполнения этого запроса выглядит следующим образом:
@x |
@y |
|
|
13 |
8 |
|
|
Создадим хранимую процедуру, которая будет выводить текущую дату в формате “DD.MM.YY”, если аргумент flag принимает значение 0, и
текущее время в формате “hh:ii:ss”, если аргумент flag принимает значение
1.
CREATE PROCEDURE format_now (IN flag INT)
BEGIN
IF flag = 0 THEN
SELECT date_format(now(), “%d.%m.%Y”) as format_now;
ELSEIF flag = 1 THEN
SELECT date_format(now(), “%H:%i:%s”) as format_now;
ELSE
SELECT unix_timestamp(now()) as format_now;
END IF;
END
//
Создадим процедуру, которая будет выводить название элементов каталога из таблицы catalogs. Вывод названия должен осуществляться по первичному ключу id_catalog. При этом если в таблице отсутствует запись с переданным номером, необходимо вывести соответствующее сообщение.
CREATE PROCEDURE pr2 (IN id INT)
BEGIN
DECLARE name_catalog tinytext;
SELECT name into name_catalog FROM catalogs WHERE id_catalog = id; IF (name_catalog is not null) THEN
SELECT name_catalog FROM dual;
ELSE
SELECT “Запрашиваемый элемент не найден» FROM dual; END IF;
END
//
Создадим хранимую процедуру, которая выводит текущую дату num
раз, где num – параметр, задаваемый пользователем.
CREATE PROCEDURE nown (in num int)
BEGIN
DECLARE i int DEFAULT 0;
IF (num > 0) THEN
label: WHILE i < num DO SELECT now();
SET i = i + 1;
END WHILE label;
ELSE
SELECT “Ошибка»;
END IF;
END
//
Исходные данные: БД, созданная в лабораторной работе №2.
Порядок выполнения работы.
1.Создать хранимую процедуру, используя глобальные переменные и операторы ветвления.
2.Создать хранимую процедуру, используя различные типы параметров и циклы.
3.Вызвать процедуры.
4.Показать список хранимых процедур.
Содержание отчета
1.Цель работы.
2.Схема данных.
3.Скриншот списка хранимых процедур.
4.Листинг созданных процедур.
5.Скриншоты выполнения хранимых процедур.
Вопросы для самопроверки
1.Что такое хранимая процедура?
2.Какие бывают операторы ветвления?
3.Как можно реализовать цикл в хранимой процедуре?
4.Какими операторами можно обеспечить досрочный выход из цикла?