9. Процедуры

Группировка двух и более команд в единый блок осуществляется с использованием ключевых слов BEGIN и END. Блоки BEGIN...END могут быть вложенными.

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

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

CREATE | ALTER PROC[EDURE] имя_процедуры

[{@имя_параметра тип_данных } ]

AS

[BEGIN]

sql_оператор [...n]

[END]

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры, имена которых, как и имена локальных переменных, должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры. Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры, годятся любые типы данных SQL, включая определенные пользователем.

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

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

В процедурах могут использоваться управляющие конструкции (циклы, ветвления).

Синтаксис условного оператора показан ниже:

IF лог_выражение

{ sql_оператор | блок_ sql_операторов }

[ ELSE

{sql_оператор | блок_ sql _ операторов } ]

Циклы организуются с помощью следующей конструкции:

WHILE лог_выражение

{ sql_оператор | sql_блок_операторов }

[ BREAK ]

{ sql_оператор | sql_блок_операторов }

[ CONTINUE ]

Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

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

CASE входное_значение

WHEN {значение_для_сравнения |

лог_выражение } THEN

вых_выражение [,...n]

[ ELSE иначе_вых_выражение ]

END

Если входное значение и значение для сравнения совпадают, то конструкция возвращает выходное значение. Если же значение входного параметра не найдено ни в одной из строк WHEN...THEN, то тогда будет возвращено значение, указанное после ключевого слова ELSE

9.1. Процедуры без параметров

1. процедура для просмотра общей суммы договоров в разрезе дат

CREATE PROCEDURE primer1

AS

BEGIN

SELECT SUM(dog_sum) AS 'общая сумма по договорам, руб', dog_data AS 'дата договора' FROM dogovor GROUP BY dog_data

END

EXECUTE primer1

2. процедура для индексации окладов с возможностью просмотра окладов до и после индексации

CREATE PROCEDURE primer2

AS

BEGIN

SELECT pov_fam AS 'фамилия поверщика', pov_oklad AS 'оклад'FROM poversotr

UPDATE poversotr SET pov_oklad=pov_oklad*0.01+pov_oklad

SELECT pov_fam AS 'фамилия поверщика', pov_oklad AS 'оклад после индексации' FROM poversotr

END

EXECUTE primer2

9.2. Процедуры с входным параметром

1. процедура для просмотра даты и суииы договора, при вводе номера договора

CREATE PROCEDURE primer3 (@param varchar(10))

AS

BEGIN

SELECT dog_data as 'дата договора', dog_sum as сумма договора' FROM dogovor WHERE dog_nom=@param

END

EXECUTE primer3 @param='124/а'

2. процедура для изменения срочности заявки на поверку СИ

CREATE PROCEDURE primer4 (@param varchar(20))

AS

BEGIN

UPDATE zayvka SET zayvka_srok=не срочно' WHERE prib_nom=@param2

SELECT * FROM zayvka

END

EXECUTE primer4 @param='ВТ-164-2008'

9.3. Процедуры с использованием курсора и управляющих конструкций (цикла и ветвления)

Существует несколько способов передачи данных между командами. Один из них – передача данных через локальные переменные. Прежде чем использовать какую-либо переменную, ее следует объявить. Объявление переменной выполняется командой DECLARE, имеющей следующий формат:

DECLARE {@имя_переменной {тип_данных} } [,...n]

Значения переменной можно присвоить посредством команд SET и SELECT. С помощью команды SELECT переменной можно присвоить не только конкретное значение, но и результат вычисления выражения. Например,

DECLARE @a INT,

SET @a=10,

SELECT @k=MIN(Цена) FROM Товар

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

В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия:

• создание или объявление курсора;

• открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти;

• выборка из курсора и изменение с его помощью строк данных;

• закрытие курсора, после чего он становится недоступным для пользовательских программ;

• освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память.

В разных реализациях определение курсора может иметь некоторые отличия. Так, например, иногда разработчик должен явным образом освободить выделяемую для курсора память. После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование его имени. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом. Сразу после восстановления она становится доступной для других операций: открытие другого курсора и т.д.

Курсоры делятся на две категории: последовательные и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении – от начала к концу. Прокручиваемые же курсоры допускают перемещение в обоих направлениях и переход к произвольной строке результирующего набора курсора.

SQL Server поддерживает курсоры статические, динамические.

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

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

В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме «только для чтения».

Динамический курсор поддерживает данные в «живом» состоянии, но это требует затрат сетевых и программных ресурсов. При использовании динамических курсоров не создается полная копия исходных данных, а выполняется динамическая выборка из исходных таблиц только при обращении пользователя к тем или иным данным. На время выборки сервер блокирует строки, а все изменения, вносимые пользователем в полный результирующий набор курсора, будут видны в курсоре. Однако если другой пользователь внес изменения уже после выборки данных курсором, то они не отразятся в курсоре.

Управление курсором реализуется путем выполнения следующих команд:

DECLARE – создание или объявление курсора;

OPEN – открытие курсора, т.е. наполнение его данными;

FETCH – выборка из курсора и изменение строк данных с помощью курсора;

CLOSE – закрытие курсора;

DEALLOCATE – освобождение курсора, т.е. удаление курсора как объекта.

Создание курсора (упрощенный вариант) выполняется при помощи команды

DECLARE @имя_переменной курсора

[SCROLL] [STATIC | DYNAMIC] CURSOR

FOR SELECT_оператор

При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то курсор окажется последовательным, т.е. его просмотр будет возможен только в одном направлении – от начала к концу.

SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора.

Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

OPEN @имя_переменной_курсора

Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

FETCH [NEXT | PRIOR | FIRST | LAST

| ABSOLUTE {номер_строки

| @переменная_номера_строки}

| RELATIVE {номер_строки |

@переменная_номера_строки}]

FROM имя_курсора [INTO @имя_переменной [,...n]]

Для работы с курсором используются специальные функции.

Функция @@FETCH_STATUS возвращает состояние последней инструкции FETCH курсора, запущенной в отношении любого курсора, который в настоящий момент открыт соединением. Тип возвращаемого значения - INT. Значение возвращаемого значения:

Возвращаемое значение	Описание
0	Инструкция FETCH была выполнена успешно.
-1	Выполнение инструкции FETCH завершилось неудачно или строка оказалась вне пределов результирующего набора.
-2	Выбранная строка отсутствует.

Функция @@CURSOR_ROWS возвращает число выбранных строк, имеющихся в последнем открытом курсоре в данном соединении.

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

CREATE PROCEDURE spisok

AS

-- объявление курсора s

DECLARE s CURSOR FOR SELECT pov_tabnom, COUNT(pov_tabnom) FROM poverka GROUP BY pov_tabnom

-- открытие курсора s

OPEN s

-- объявление пременных @y и @x

DECLARE @y SMALLINT, @x INT

-- загрузка данных из первой строки курсора в пременные @y и @x

FETCH next FROM s INTO @y, @x

PRINT 'табельный номер '+'количество поверок'

-- цикл «ПОКА» не просмотрятся все строки курсора

WHILE @@FETCH_STATUS=0

BEGIN

IF @x>1

PRINT STR(@y)+' '+STR(@x)

-- загрузка данных из следующей строки курсора в пременные @y и @x

FETCH next FROM s INTO @y, @x

END

-- закрытие курсора

CLOSE s

-- освобождение (очистка от данных) курсора

DEALLOCATE s

EXECUTE spisok

2. Определение списка сотрудников, которые произвели более одной поверки (без использования курсора)

CREATE VIEW primer AS SELECT pov_tabnom , COUNT(pov_tabnom) AS 'kol_poverok' FROM poverka GROUP BY pov_tabnom

S ELECT pov_tabnom FROM primer WHERE kol_poverok>1

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

CREATE PROCEDURE maxstoim

AS

DECLARE p CURSOR FOR SELECT zak_kod, AVG(dog_sum) FROM dogovor GROUP BY zak_kod

OPEN p

DECLARE @y SMALLINT, @x REAL, @max REAL, @kod SMALLINT

FETCH next FROM p INTO @y, @x

SET @max=@x

SET @kod=@y

PRINT 'код заказчика '+'максимальная средняя стоимость договоров'

WHILE @@FETCH_STATUS=0

BEGIN

IF @x>=@max

BEGIN

SET @max=@x

SET @kod=@y

END

FETCH next FROM p INTO @y, @x

END

PRINT STR(@kod)+' '+STR(@max)

CLOSE p

DEALLOCATE p

EXECUTE maxstoim

С одержание таблицы dogovor

Результат выполнения процедуры maxstoim