Тема 9. Хранимые процедуры как базовый компонент серверной части информационных систем

117. Каков синтаксис описания хранмой процедуры с параметрами? Что такое параметр хранимой процедуры и какие типы параметров Вы знаете? Однако кроме имени хранимой процедуры все остальные параметры являются необязательными. Процедуры могут быть процедурами или процедурами-функциями. И эти понятия здесь трактуются традиционно, как в языках программирования высокого уровня. Хранимая процедура-функция возвращает значение, которое присваивается переменной, определяющей имя процедуры. Процедура в явном виде не возвращает значение, но в ней может быть использовано ключевое слово OUTPUT, которое определяет, что данный параметр является выходным.

Рассмотрим несколько примеров простейших хранимых процедур.

/* процедура проверки наличия экземпляров данной книги

параметры:

@ISBN шифр книги

Процедура возвращает значение, равное количеству экземпляров

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

*/

CREATE PROCEDURE COUNT_EX (@ISBN varchar(12))

AS

/* определим внутреннюю переменную, которая будет хранить количество экземпляров искомой книги. Количество — это целое число, поэтому наша переменная @ TEK _ COUNT имеет целочисленный тип */

DECLARE @ TEK _ COUNT int

/* выполним соответствующий оператор SELECT

Будем считать только экземпляры, которые в настоящий момент находятся не на руках у читателей, а в библиотеке */

select @TEK_COUNT = count(*) FROM EXEMPLAR WHERE ISBN = @ISBN AND READER_ID Is NULL AND EXIST = True

/* 0 — ноль означает, что нет ни одного свободного экземпляра данной книги в библиотеке */

RETURN @TEK_COUNT

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

118. Есть хранимая процедура, которая считает количество экземпляров книги в библиотеке count_books(@isbn)

Eсть хранимая процедура, которая считает количество экземпляров данной книги на руках у читателей count_read(@isbn)

Как сосчитать количество свободных экземпляров данной книги, напишите оператор. См. вопрос 117.

119. Каков формат оператора вызова хранимой процедуры с параметрами? Что возвращает оператор RETURN? Каковы ограничения на его применение в хранимой процедуре? EXEC <имя процедуры> <значение_входного_параметра1>...

<имя_переменной_для_выходного параметра1>... Оператор RETURN в СУБД MS SQL возвращает значение целочисленной переменной и прекращает выполнение. Возвращаемое значение должно быть целочисленным.

120. Что такое план выполнения процедуры? При выполнении процедуры план выполнения строится заново каждый раз или нет, поясните. План, в соответствии с которым процедура выполняется. Построение заново происходит при заданном RECOMPILE. При новых данных старый план может быть просто неэффективным, потому и необходимо перестраивать план выполнения.

121. Чем отличается использование входных и выходных параметров при вызове процедуры, покажите на примере.

122. Чем отличаются хранимые процедуры от пользовательских функций? В MS SQL SERVER 2000 существует множество заранее определенных функций, позволяющих выполнять самые разнообразные действия. Однако может возникнуть необходимость использовать некоторые специфичные функции. Для этого начиная с версии 8.0 (2000) появилась возможность описывать функции самим и хранить такие функции в виде полноценного объекта БД, наравне с хранимыми процедурами, представлениями и т. д. Удобство применения функций, определяемых пользователем очевидно: их, в отличие от хранимых процедур можно встраивать непосредственно в оператор SELECT, причем можно их использовать как для получения конкретных значений (в разделе SELECT), так и в качестве источника данных (в разделе FROM). При использовании UDF в качестве источников данных преимущество их использования перед представлениями заключается в том, что UDF в отличие от представлений могут иметь входные параметры, с помощью которых можно влиять на результат работы функции. Функции, определяемые пользователем, могут быть трех видов: скалярные функции, Inline-функции и многооператорные функции, возвращающие табличный результат.