Лекция № 10
Тема: «Работа с хранимыми процедурами»
План
1. Введение
2. Создание хранимых процедуры в SQL Server
3 Создание хранимых процедуры в MySQL
1. Введение
Написание и отладка кода команд запросов в «Среде SQL Server Management Studio», не вызывает особых трудностей. Однако, основными клиентами баз данных являются приложения, специально созданные для выполнения определенных задач. Эти приложения часто реализуют сложные операции, требующие использования многих команд.
Чтобы выполнить такие операции, приложение отсылает на сервер одну или более команд, которые там выполняются. В ответ сервер отправляет клиенту (приложению) результат обработки запроса. Этим результатом может быть как сообщение об успешном завершении выполнения команды, занимает всего несколько байт, так и огромный массив данных, включая тысячи строк, занимающих несколько мегабайт. Клиент может обработать полученный результат и на базе этого результата отправить серверу новый запрос.
В рассматриваемой ситуации логика обработки данных реализована на клиенте. Сервер просто принимает набор инструкций и выполняет их. При написании приложения разработчик должен позаботиться о разработке запросов, корректно работающих с данными и выполняющих все необходимые действия,
Предложенный подход к обработке данных имеет несколько недостатков. Например, если нужно изменить логику обработки данных, то следует менять исходный код программы, после чего заново компилировать ее и распространять среди пользователей. Кроме того, если одна и та же логика обработки данных используется в нескольких приложениях, то в худшем случае для каждого из этих приложений нужно будет повторять процесс разработки запросов, а в лучшем переносить код из уже работающего приложения. Также следует обратить внимание на сам процесс взаимодействия сервера и клиента. Как видно, алгоритмы обработки данных будут реализованы в виде набора блоков команд, которые отправляются на сервер по очереди. После выполнения блока приложение получает определенный результат, после обработки которого решается, какой следующий блок и с какими параметрами должен быть выполнен.
Также необходимо сказать о безопасности. Для выполнения обработки данных пользователь должен иметь соответствующие права доступа. Предполагается, что эти права будут использованы приложением для доступа к данным. Однако нельзя быть до конца уверенным, что пользователь не сможет обратиться к данным напрямую и выполнить запрещенные действия. Также нельзя быть уверенным, что команды, отправляемые приложением, осуществляют верные действия. Ошибка разработчика при создании запроса может иногда привести к повреждению данных. Кроме того, нельзя не учитывать, что злоумышленник или тот же разработчик способен изменить код запроса для получения несанкционированного доступа к данным или для их повреждения и даже уничтожения.
Все вышесказанное указывает на недостатки подхода к разработке систем, когда логика обработки данных реализуется на клиенте. Описанные проблемы могут быть решены за счет переноса алгоритмов обработки данных на сервер. В этом случае приложение просто сообщает серверу, какой именно набор команд необходимо выполнить. Дополнительно могут быть указаны параметры, которые в зависимости от реализации алгоритма будут влиять на ход выполнения процесса обработки данных. При этом приложение сможет получать только конечный результат выполнения. Все промежуточные результаты будут обработаны сервером. Это позволяет снизить сетевой трафик. Такой набор команд, который сохраняется на сервере и выполняется как единое целое, в терминологии SQL Server называется хранимой процедурой (stored procedure).
Использование хранимых процедур имеет ряд преимуществ:
- безопасность. Использование хранимых процедур позволяет не предоставлять доступ клиентам к таблицам БД. Достаточно дать доступ только к хранимым процедурам, которые в свою очередь сами обратятся к таблицам и обработают их. Для примера в банковских системах вся работа построена только на хранимых процедурах;
- не требовательность к мощности рабочих станций. Основной код выполняется на стороне сервера, поэтому не нужно иметь в качестве клиентов на рабочих местах высокопроизводительные машины. Достаточно иметь один мощный сервер;
- простота внесения изменений. Если нужно поменять логику работы процедуры, то достаточно внести изменения в код процедуры на сервере. При этом клиентские приложения переделывать не нужно;
- повторное использование кода. Хранимая процедура всегда срабатывает одинаково независимо от того, какая программа, на каком языке программирования ее вызывает. Так что такие процедуры могут вызвать различные программы, разработанные на разных языках программирования.
Однако, хранимые процедуры имеют и ряд недостатков:
- требования к знанию языка SQL для создания процедур;
- высокие требования к мощности сервера при большом количестве подключений.
Несмотря на недостатки, использование хранимых процедур считается одним из наиболее эффективных инструментов в технологии клиент / сервер.
2. Создание хранимых процедуры в SQL Server
Создание хранимых процедур
Все хранимые процедуры SQL Server находятся в папке "Программирование – Хранимые процедуры" внутри БД.
Для создания новой хранимой процедуры вызовите контекстное меню папки "Хранимые процедуры" и выберите команду "Создать хранимую процедуру".
В результате откроется окно, в котором отображается код шаблона команды создания процедуры, готовый для редактирования.
Чтобы правильно вносить в него изменения рассмотрим общий вид команды создания хранимой процедуры.
Создание хранимой процедуры осуществляется с помощью команды:
create procedure имя_ процедуры
[параметр1 тип [output],
параметр2 тип [output],
. . .
параметрN тип [output]]
as
begin
код процедуры
end
Рассмотрим назначение и использование параметров команды:
имя_ процедуры. С помощью этого параметра указывается имя, которое будет иметь хранимая процедура. Максимальная длина имени не должна превышать 128 символов.
параметр тип [output]. С помощью этого аргумента для хранимой процедуры указываются входные параметры. При вызове хранимой процедуры пользователь должен будет указать значения для этих параметров. Параметр, который помечен ключевым словом output, является выходным. С помощью параметров пользователи могут управлять логикой выполнения процедуры. В принципе, процедура может не иметь никаких параметров.
as. После этого ключевого слова начинается тело процедуры, состоящее из набора команд SQL.
begin … end. Задает начало и конец тела хранимой процедуры. Если процедура состоит из, однйть команды, то указывать эту конструкцию не обязательно.
Для вызова процедуры используют команду вида:
exec имя_ процедуры список_параметров
Список параметров представляет собой перечень переменных и констант, разделенных запятой, значения которых передаются в процедуру. Количество, тип и порядок значений в списке параметров должен совпадать с порядком и типом параметров в самой процедуре. Каждый выходной параметр помечается ключевым словом output.
Процедура без параметров
Пример. Создать процедуру, которая увеличивает оклады сотрудников на 15%.
create procedure oklad
as
update sotrudniki set oklad=oklad+oklad*0.15
Вызовем процедуру для увеличения оклада.
exec oklad
Процедура с входным параметром
Пример. Изменим предыдущую задачу и создадим процедуру для увеличения оклада на произвольный процент
create procedure oklad
--опишем параметр для задания нужного процента
@procent int
as
update sotrudniki
set oklad=oklad*oklad*@procent/100
Вызовем процедуру для увеличения оклада на 10%
exec oklad 10
Вызовем процедуру для увеличения оклада на 7%
exec oklad 7
Процедура на выборку данных
Пример. Написать процедуру для вывода списка сотрудников заданного пола.
create procedure spisok
--опишем параметр для задания пола
@pol varchar(1)
as
select * from sotrudniki where pol=@pol order by fam
Вызовем процедуру для вывода списка женщин.
exec spisok 'ж'
Пример. Написать процедуру, которая выдает на экран количество, средний и максимальный оклад сотрудников
create procedure schet_oklad
as
select count(*) as kol, avg(oklad) as sred, max(oklad) as maximum from sotrudniki
Вызовем процедуру для вывода расчетных значений.
exec schet_oklad
Пример. Написать процедуру, которая выдает на экран количество, средний и максимальный оклад сотрудников с указанным полом.
create procedure schet_oklad_pol
--опишем параметр для задания пола
@pol varchar(1)
as
select count(*) as kol, avg(oklad) as sred, max(oklad) as maximum from sotrudniki where pol=@pol
Вызовем процедуру для вывода расчетных значений для мужчин
exec schet_oklad_pol 'м'
Процедура с выходными параметрами
Пример. Написать процедуру, которая записывает количество, средний и максимальный оклад сотрудников в выходные параметры.
create procedure schet_oklad2
--опишем выходные параметры
@kol int output,
@sred decimal(7,2) output,
@maximum decimal(7,2) output
as
select @kol=count(*),
@sred=avg(oklad),
@maximum=max(oklad) from sotrudniki
Вызовем процедуру для вывода расчетных значений.
--опишем переменные для передачи как параметров в процедуру
@declare @k int, @s decimal(7,2), @m decimal(7,2)
--вызываем процедуру с передачей в нее переменных для записи ответов
exec schet_oklad2 @k output, @s output, @m output
--выводим переменные на экран
select @k, @s, @m
Пример. Написать процедуру, которая записывает количество, средний и максимальный оклад сотрудников с указанным полом в выходные параметры.
create procedure schet_oklad_pol2
--опишем параметр для задания пола
@pol varchar(1)
--опишем выходные параметры
@kol int output,
@sred decimal(7,2) output,
@maximum decimal(7,2) output
as
select @kol=count(*),
@sred=avg(oklad),
@maximum=max(oklad) from sotrudniki
where pol=@pol
Вызовем процедуру для вывода расчетных значений для мужчин
--опишем переменные для передачи как параметров в процедуру
@declare @k int, @s decimal(7,2), @m decimal(7,2)
--вызываем процедуру с предачей в нее переменных для записи ответов
exec schet_oklad_pol2 'м',@k output, @s output, @m output
--выводим переменные на экран
select @k, @s, @m
Рассмотрены команды создания процедур можно ввести в окне выполнения запросов и запустить их на выполнение. Здесь же можно и выполнить процедуру для проверки ее работы.
Работа с временными таблицами
Временные таблицы можно создавать в хранимых процедурах, выполнять над ними определенные действия, а затем удалять. Таким образом, такие таблицы могут играть роль переменных с набором данных.
Пример. Создать процедуру, которая выводит список студентов с суммой стипендии.
create procedure show_stip
begin
- создадим временную таблицу со средними баллами
select id_stud, avg (ocenka) as srbal
into #bufer
from ocenki
group by id_stud
- используя временную таблицу выведем список стипендий
select fam, imya, case when srbal = 5 then 600
when srbal> = 4 then 550
else 0 end as stip
from students, #bufer
where students.id_stud = # bufer.id_stud
and finance = 'Бюджет'
- удаляем временную таблицу
drop table #bufer
end
Изменение и удаления хранимых процедур
Как бы хорошо не разрабатывалась хранимая процедура, все равно возникает потребность в ее изменении.
Для внесения изменения в дереве объектов БД раскройте папку "Программирование – Хранимые процедуры". В контекстном меню нужной процедуры выберите команду "Изменить". Внесите изменения в код процедуры и щелкните на кнопке "Выполнить".
Для удаления процедуры в ее контекстном меню выберите команду "Удалить".
3 Создание хранимых процедуры в MySQL
Для создания хранимой процедуры используется команда:
create procedure имя_процедуры
([параметр1 тип,
параметр2 тип,
. . .
параметрn тип])
begin
код процедуры
end
Обратите внимание, что скобки в списке параметров обязательны. То есть, если процедура не имеет параметров, то указывают пустые скобки.
Для вызова хранимой процедуры без параметров используют команду:
call имя_процедуры(список параметров);
Если процедуры не имеет параметров, то ее вызывают с пустыми скобками:
call имя_процедуры();
Пример. Создать процедуру "spisok", которая выводит на экран список сотрудников с окладом больше среднего по предприятию.
create procedure spisok()
begin
-- описываем переменную
declare sred float (9,2)
-- находим средний оклад и записываем в переменную sred
select avg (oklad) into sred from sotr;
-- выведем список сотрудников с окладом больше sred
select * from sotr where oklad> sred;
end
Для вызова хранимой процедуры без параметров используют команду:
call spisok ();
Рассмотрим создание хранимой процедуры с помощью утилиты SQL Manager for MySQL. Запустите утилиту двойным щелчком откройте нужную БД. В списке объектов БД выберите папку "Процедуры". В контекстном меню папки выберите "Новый объект: Процедура". В результате в правой части откроется окно для ввода параметров процедуры.
В поле "Имя процедуры" ведите имя процедуры, в поле "Определение" введите код процедуры. После задания всех параметров щелкните на команде "Compile". Если в тексте процедуры нет ошибок, то компиляция пройдет успешно, а в папке "Процедуры" появится созданный элемент.
Для проверки работы процедуры щелкните на команде «Выполнить процедуру».
Наиболее простым вариантом процедуры является процедура без параметров. В этом случае нужно указать только имя и код процедуры.
Например, создать процедуру с именем "show" для выдачи всех записей таблицы "sotr". Код процедуры имеет вид:
Создание хранимых процедур с входными параметрами
Процедуры без параметров имеют небольшое применение, потому что они не могут использоваться для различных задач и всегда срабатывают одинаково. Входные параметры позволяют задавать порядок выполнения хранимой процедуры, ее логику. Входные параметры позволяют передавать в процедуру исходные данные для ее работы. Число входных параметров может быть различным. При этом каждый параметр имеет имя и тип (один из типов, используемых при описании полей таблиц БД).
Пример. Создать процедуру с именем primer1, которая выводит на экран список мужчин, женщин или всех сотрудников. Процедура "primer1" будет иметь входной параметр "k" типа Integer. Если k = 0, выдаем мужчин, если k = 1, выдаем женщин, а если другое значение, то выдаем всех сотрудников.
При создании такой процедуры кроме имени и кода нужно указать список параметров.
create procedure primer1 (k integer(11))
begin
if (k = 0) then
select * from sotrudniki where pol = 'м';
elseif (k = 1) then
select * from sotrudniki where pol = 'ж';
else
select * from sotrudniki;
end if;
end
Для вызова хранимой процедуры без параметров используют команду:
call primer1 (0); - для выдачи списка мужчин
call primer1 (1); - для выдачи списка женщин
call primer1 (2) - для выдачи всего списка
В окне Enterprise Manager for MySQL для задания списка параметров процедуры используют таблицу "Параметры". Таблица имеет колонки:
- Type - тип параметра (IN-входной, OUT-выходной)
- Name - имя параметра;
- Data type - тип параметра.
Создадим процедуру procedure1 как в предыдущем примере.
Пример. Вывести на экран список сотрудников с фамилией на заданные буквы. Буквы - входной параметр (bukva varchar (15)). Процедура будет носить имя "primer2".
create procedure primer2 (bukva varchar (15))
begin
select * from sotr where fam = concat (bukva, '%')
end
Для вызова хранимой процедуры используют команду:
call primer2 ('o'); - список сотрудников с фамилией на "О"
call primer2 ('Иван'); - список сотрудников с фамилией на "Иван"
Создание хранимых процедур на изменение данных
Все рассмотренные процедуры выполняют отбор записей. Однако существуют процедуры, которые выполняют обновление или удаление данных. Такие процедуры также могут иметь параметры.
Например. Создать процедуру "Upd", которая увеличивает оклад сотрудников на произвольный процент. Размер процента - входной параметр (procent float (9,2)).
Код процедуры имеет вид:
update sotr set oklad = oklad + oklad * procent / 100;
Для вызова такой процедуры используют команду:
call upd (10); - увеличиваем оклады на 10%
Аналогично создается процедура на удаление данных. Создать процедуру "Del", которая удаляет сотрудников с заданным годом рождения. Год рождения - входной параметр (god integer). Код процедуры имеет вид:
delete from sotr where year (datar) = god;
Для вызова процедуры используйте команду:
call sotr (1950) - удаляем сотрудников 1950 года
Работа с временными таблицами в MySQL
Пример. Создать процедуру, которая выводит список студентов с суммой стипендии.
create procedure show_stip
begin
-- создадим временную таблицу со средними баллами
create temporary table bufer
select id_stud, avg (ocenka) as srbal
from ocenki
group by id_stud
-- используя временную таблицу, выведем список стипендий
select fam, imya, case when srbal = 5 then 600
when srbal> = 4 then 550
else 0 end as stip
from students, bufer
where students.id_stud = bufer.id_stud
and finance = 'Бюджет'
-- удаляем временную таблицу
drop table bufer
end
Изменение и удаления хранимых процедур
Для внесения изменения в процедуру ее можно открыть двойным щелчком. Внесите изменения в код процедуры и щелкните на кнопке "Compile".
Для удаления процедуры в ее контекстном меню выберите команду "Удалить".
Вопросы для самоконтроля
1. Что такое хранимая процедура? В чем ее преимущества и недостатки?
2. Как создать хранимую процедуру SQL Server без параметров и вызвать ее на выполнение. Пример.
3. Как создать хранимую процедуру SQL Server с входными параметрами и вызвать ее на выполнение. Пример.
4. Опишите процесс создания хранимой процедуры SQL Server для расчетов результатов. Как вызвать такую ??процедуру? Пример.
5. Опишите процесс создания хранимой процедуры SQL Server на обновление или удаление данных Пример.
6. Как создать хранимую процедуру MySQL без параметров и вызвать ее на выполнение. Пример.
7. Как создать хранимую процедуру MySQL с входными параметрами и вызвать ее на выполнение. Пример.
8. Опишите процесс создания хранимой процедуры MySQL на обновление или удаление данных Пример.