Лекция № 28
Тема: "Создание хранимых процедур в СУБД MySQL"
План
1. Особенности программирования в MySQL
2. Принципы создания хранимых процедур без параметров
3. Создание хранимых процедур с входными параметрами
4. Создание хранимых процедур для подсчета результата
5. Создание хранимых процедур на изменение данных
1. Особенности программирования в MySQL
1. В коде можно использовать переменные, которые предварительно описываются с помощью инструкции:
declare имя тип;
Каждая переменная описывается с новой строки командой declare.
2. Для присваивания значения переменной используют команду:
set переменная=значение;
Допускается присваивания нескольким переменным значений в одном строке:
set переменная1=значение, переменная2=значение, . . . , переменнаяN=значение;
3. Если запрос рассчитывает некоторое значение, то результат такого запроса можно записать в переменную с помощью команды:
select функция into переменная from таблица;
Можно одним запросом рассчитать несколько значений:
select функция1, функция2, . . . ,функцияN into переменная1, переменная2, ..., зміннаn from таблица;
Например:
declare kol int, sroklad decimal(7,2)
select count(*) into kol, avg(oklad) into sroklad from sotr
4. Каждая команда заканчивается точкой с запятой.
5. Для ввода комментариев используют символы /*...*/ или #
6. Оператор условия имеет вид:
if (условие) then
действие;
elseif
действие;
else
действие;
end if;
7. Оператор case имеет вид:
case (выражение)
when значение1 then действие1;
when значение2 then действие2;
. . .
else
действие;
end case;
8. Другая форма записи оператора case:
case
when условие1 then действие1;
when условие2 then действие2;
. . .
else
действие;
end case;
9. Оператор цикла имеет вид:
while условие do
действия;
end while;
10. Для группировки блоков операторов в единое целое используют операторные скобки begin…end.
2. Принципы создания хранимых процедур без параметров
Каждая хранимая процедура состоит из имени, списка параметров и самого кода процедуры. Имя процедуры задается произвольно, списка параметров может не быть, если логика работы процедуры их не предполагает. Код процедуры - это набор SQL команд, помещенный между словами begin...end.
Рассмотрим создание хранимой процедуры с помощью утилиты SQL Manager for MySQL. Запустите утилиту, двойным щелчком откройте нужную БД. В списке объектов БД выберите папку "Процедуры". В контекстном меню папки выберите команду "Новый объект: процедура". В результате в правой части откроется окно для ввода параметров процедуры.
В поле "Имя" ведите имя процедуры, в поле "Определение" введите код процедуры. После задания всех параметров щелкните на команде "Компилировать". Если в тексте процедуры нет ошибок, то компиляция пройдет успешно, а в папке "Процедуры" появится созданный элемент.
Для проверки работы процедуры щелкните на команде "Выполнить процедуру".
Наиболее простым вариантом процедуры является процедура без параметров. В этом случае нужно указать только имя и код процедуры.
Например, создать процедуру с именем "Show" для выдачи всех записей таблицы "sotr". Код процедуры имеет вид:
Для вызова сохраненной процедуры без параметров используют команду:
call Show();
Пример использования переменной. Создать процедуру "Spisok", которая выводит на экран список сотрудников с окладом больше среднего по предприятию.
#описываем переменную
declare sred float(9,2);
#находим средний оклад и записываем в переменную sred
select avg(oklad) into sred from sotr;
#выводим список сотрудников с окладом больше sred
select * from sotr where oklad>sred;
Для вызова хранимой процедуры без параметров используют команду:
call Spisok();
3. Создание хранимых процедур с входящими параметрами
Процедуры без параметров имеют небольшое применение, потому что они не могут использоваться для разных задач и всегда срабатывают одинаково. Входящие параметры позволяют задавать порядок выполнения хранимой процедуры, ее логику. Входные параметры позволяют передавать в процедуру исходные данные для ее работы. Число входящих параметров может быть разным. При этом каждый параметр имеет имя и тип (как при описании обычных переменных).
При создании такой процедуры кроме имени и кода нужно указать список параметров. Для списка параметров используют таблицу "Параметры". Таблица имеет колонки:
В коде процедуры имя параметра используют как обычную переменную.
Например, вывести на экран список мужчин, женщин или всех сотрудников. Процедура "Primer1" будет иметь входящий параметр "k" типа Integer. Если к=0, выдаем мужчин, если к=1, выдаем женщин, а если k=другому значению, то выдаем всех сотрудников.
Для вызова хранимой процедуры с параметрами используют команду:
call Primer1(0); #для вывода списка мужчин
call Primer1(1); #для вывода списка женщин
call Primer1(2); #для вывода всего списка
Пример, вывести на экран список сотрудников с фамилией на заданную букву. Буква - входящий параметр (bukva varchar(15)). Процедура будет носить имя "Primer2".
select * from sotr where fam=concat(bukva,'%');
Для вызова хранимой процедуры без параметров используют команду:
call Primer2('o'); #список сотрудников с фамилией на "о"
call primer2('иван'); #список сотрудников с фамилией на "иван"
4. Создание хранимых процедур для подсчета результата
Если процедура подсчитывает какое-то значение, то для его передачи в программу клиента нужно создать запрос на подсчет этого результата и присвоить результату произвольное имя.
Пример. Подсчитать максимальный и средний оклад сотрудников. Процедура будет носить имя "Calc1".
Код процедуры имеет вид:
select max(oklad) as maxoklad, avg(oklad) as sredoklad from sort;
Для вызова хранимой процедуры используют команду:
call Calc1 ();
В клиентской программе можно обратиться к полям "maxoklad" и "sredoklad" для считывания найденных значений.
Создание хранимых процедур для подсчета результата с входящим параметром
Например, создать хранимую процедуру "Calc2" для расчетов количества сотрудников, которые родились в заданном году. Год - это входящий параметр (god integer).
Код процедуры будет иметь вид:
select count(*) as kolvo from sotr where year(datar)=god;
Для вызова процедуры можно использовать команду:
call Calc2 (1978);
В клиентской программе можно обратиться к полю "Kolvo" для считывания найденного значения.
5. Создание хранимых процедур на изменение данных
Все рассмотренные процедуры выполняют отбор записей, или расчет нужных значений. Однако существуют процедуры, которые выполняют изменение данных (обновление или удаление). Такие процедуры также могут иметь параметры.
Например. Создать процедуру "UpdByProcent", которая увеличивает оклад сотрудников на произвольный процент. Размер процента - входящий параметр (procent float(9,2)).
Код процедуры имеет вид:
update sotr set oklad= oklad+.oklad*procent/100;
Для вызова такой процедуры используют команду:
call UpdByProcent(10); #увеличиваем оклады на 10%
Аналогично создается процедура на удаление данных. Создать процедуру "DelByYear", которая удаляет сотрудников с заданным годом рождения. Год рождения - входящий параметр (god integer). Код процедуры имеет вид:
delete from sotr where year(datar)=god;
Для вызова процедуры используйте команду:
call DelByYear(1950); #удаляем сотрудников 1950 года рождения
Замечание: Для вызова созданных процедур в проектах Delphi используется такой же подход, как и при работе с хранимыми процедурами SQL Server (см. предыдущую лекцию). Отличие в том, что имя параметра процедуры не нужно начинать с символа @.
Вопросы для самоконтроля
1. Какие операции над переменными можно выполнить в коде MySQL? Как в коде MySQL присвоить переменной значение?
2. Как записать комментарий в коде MySQL? Как записать оператор if, case, while в коде MySQL? Как записать операторные скобки в коде MySQL?
3. Что такое хранимая процедура? В чем ее преимущества и недостатки?
4. Как создать хранимую процедуру MySQL без параметров и вызвать ее на выполнение. Пример.
5. Как создать хранимую процедуру MySQL с входящими параметрами и вызвать ее на выполнение. Пример.
6. Опишите процесс создания хранимой процедуры MySQL для расчетов результатов. Как вызвать такую процедуру? Пример.
7. Опишите процесс создания хранимой процедуры MySQL на обновление или удаление данных Пример.