Тема 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-функции и многооператорные функции, возвращающие табличный результат.