Презентация "Хранимые процедуры в среде MS SQL Server"

Хранимые процедуры

План
Понятие хранимой процедуры
Хранимые процедуры в среде MS SQL Server
Изменение и удаление таблицы

Понятие хранимой процедуры

Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде. Выполнение в базе данных хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:
необходимые операторы уже содержатся в базе данных;
все они прошли этап синтаксического анализа и находятся в исполняемом формате;
хранимые процедуры поддерживают модульное программирование;
хранимые процедуры могут вызывать другие хранимые процедуры и функции;
хранимые процедуры могут быть вызваны из прикладных программ других типов;
как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных операторов;
хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры. Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.
Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных. Они вызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик может управлять правами доступа к хранимой процедуре, разрешая или запрещая ее выполнение.

2. Хранимые процедуры в среде MS SQL Server

Системные хранимые процедуры предназначены для выполнения различных административных действий. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##.

Создание хранимой процедуры предполагает решение следующих задач:
определение типа создаваемой хранимой процедуры: временная или пользовательская;
планирование прав доступа;
определение параметров хранимой процедуры;
разработка кода хранимой процедуры.
Создание новой и изменение имеющейся хранимой :
<определение_процедуры>::=
{CREATE | ALTER } [PROCEDURE] имя_процедуры
[;номер]
[{@имя_параметра тип_данных } [VARYING ]
[=default][OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [...n]

Номер в имени – это идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур.
Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры, имена которых, как и имена локальных переменных, должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.
Возможно использование любых типов данных. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.
Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.
Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию.

{CREATE | ALTER } [PROCEDURE] имя_процедуры
[;номер]
[{@имя_параметра тип_данных } [VARYING ]
[=default][OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [...n]

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена.

Репликация (англ. replication) — механизм синхронизации содержимого нескольких копий объекта (например, содержимого базы данных). Репликация — это процесс, под которым понимается копирование данных из одного источника на другой (или на множество других) и наоборот. При репликации изменения, сделанные в одной копии объекта, могут быть распространены в другие копии.
Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры.
Ключевое слово AS размещается в начале собственно тела хранимой процедуры, т.е. набора команд SQL, с помощью которых и будет реализовываться то или иное действие.

Удаление хранимой процедуры осуществляется командой:
DROP PROCEDURE {имя_процедуры} [,...n]

Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.
Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

{CREATE | ALTER } [PROCEDURE] имя_процедуры
[;номер]
[{@имя_параметра тип_данных } [VARYING ]
[=default][OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [...n]

Для выполнения хранимой процедуры используется команда:

[[ EXEC [UTE] имя_процедуры [;номер]
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.
Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.
Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры

Пример 12.1. Процедура без параметров. Разработать процедуру для получения названий и стоимости товаров, приобретенных Ивановым. Процедура возвращает набор данных.

CREATE PROC my_proc1
AS
SELECT Товар.Название,
Товар.Цена*Сделка.Количество
AS Стоимость, Клиент.Фамилия
FROM Клиент INNER JOIN
(Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара)
ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Клиент.Фамилия=’Иванов’

Пример 12.2. Процедура без параметров. Создать процедуру для уменьшения цены товара первого сорта на 10%. Процедура не возвращает никаких данных.

CREATE PROC my_proc2
AS
UPDATE Товар SET Цена=Цена*0.9
WHERE Сорт=’первый’

Для обращения к процедуре можно использовать команды:

EXEC my_proc1 или my_proc1

Для обращения к процедуре можно использовать команды:

EXEC my_proc2 или my_proc2

Пример 12.3. Процедура с входным параметром. Создать процедуру для получения названий и стоимости товаров, которые приобрел заданный клиент.

CREATE PROC my_proc3
@k VARCHAR(20)
AS
SELECT Товар.Название,
Товар.Цена*Сделка.Количество
AS Стоимость, Клиент.Фамилия
FROM Клиент INNER JOIN
(Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара)
ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Клиент.Фамилия=@k

Для обращения к процедуре можно использовать команды:

EXEC my_proc3 'Иванов'
или
my_proc3 @k='Иванов'

Пример 12.4. Процедура с входными параметрами. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

CREATE PROC my_proc4
@t VARCHAR(20), @p FLOAT
AS
UPDATE Товар SET Цена=Цена*(1-@p)
WHERE Тип=@t

Пример 12.5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.
CREATE PROC my_proc5
@t VARCHAR(20)=’Конфеты',
@p FLOAT=0.1
AS
UPDATE Товар SET Цена=Цена*(1-@p)
WHERE Тип=@t

Для обращения к процедуре можно использовать команды:

EXEC my_proc4 'Вафли',0.05

Или

EXEC my_proc4 @t='Вафли', @p=0.05

Для обращения к процедуре можно использовать команды:

EXEC my_proc5 'Вафли',0.05
или
EXEC my_proc5 @t='Вафли', @p=0.05
или

EXEC my_proc5 @p=0.05
В этом случае уменьшается цена конфет (значение типа не указано при вызове процедуры и берется по умолчанию).

EXEC my_proc5
В последнем случае оба параметра (и тип, и проценты) не указаны при вызове процедуры, их значения берутся по умолчанию.

Пример 12.6. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.

CREATE PROC my_proc6
@m INT,
@s FLOAT OUTPUT
AS
SELECT @s=Sum(Товар.Цена*Сделка.Количество)
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Month(Сделка.Дата)
HAVING Month(Сделка.Дата)=@m

Для обращения к процедуре можно использовать команды:

DECLARE @st FLOAT
EXEC my_proc6 1,@st OUTPUT
SELECT @st

Этот блок команд позволяет определить стоимость товаров, проданных в январе ( входной параметр месяц указан равным 1).

Создать процедуру для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Сначала разработаем процедуру для определения фирмы, где работает сотрудник.

CREATE PROC my_proc7
@n VARCHAR(20),
@f VARCHAR(20) OUTPUT
AS
SELECT @f=Фирма
FROM Клиент
WHERE Фамилия=@n

Пример 12.7. Использование вложенных процедур. Создать процедуру для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.
Затем создадим процедуру, подсчитывающую общее количество товара, который закуплен интересующей нас фирмой.

CREATE PROC my_proc8
@fam VARCHAR(20),
@kol INT OUTPUT
AS
DECLARE @firm VARCHAR(20)
EXEC my_proc7 @fam,@firm OUTPUT
SELECT @kol=Sum(Сделка.Количество)
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фирма
HAVING Клиент.Фирма=@firm

Вызов процедуры осуществляется с помощью команды:

DECLARE @k INT
EXEC my_proc8 ‘Иванов’,@k OUTPUT
SELECT @k