Хранимая процедура – это объект БД, представляющий собой поименованный код T-SQL. Как и любой объект БД, хранимая процедура может быть создана с помощью CREATE, изменена с помощью ALTER и удалена с помощью оператора DROP. К именованию процедуры предъявляются такие же требования, как и к именам объектов БД. Не рекомендуется использовать имена процедур, начинающиеся с символов SP – обычно так именуются системные хранимые процедуры. Хранимая процедура может принимать входные и формировать выходные параметры, а результатом ее выполнения может быть целочисленное значение, возвращаемое к точке вызова с помощью оператора RETURN один или более результирующих наборов, сформированных операторами SELECT, а также содержимое стандартного выходного потока, полученного при выполнении операторов PRINT. Вызов процедуры осуществляется с помощью оператора EXECUTE. Кроме того, результирующий набор хранимой процедуры может быть использован в качестве исходного источника строк для оператора INSERT.

На рис. 13.62 приведен пример создания простейшей хранимой процедуры с именем GET_ PULPITS. Процедура не имеет параметров. В результате выполнения процедуры будет сформирован результирующий набор.

Рис. 13.62. Создание процедуры с именем GET_PULPITS

В сценарии на рис. 13.63 осуществляется вызов хранимой процедуры GET_ PULPITS. Кроме того, на рисунке приведен фрагмент результирующего набора, сформированного процедурой. Обратите внимание, что ключевое слово EXECUTE может быть сокращено EXEC.

Рис. 13.63. Вызов процедуры GET_ PULPITS

В сценарии на рис. 13.64 сначала удаляется процедура (оператор DROP PROCEDURE), а затем создается снова (оператор CREATE PROCEDURE). Новая процедура имеет то же имя GET_ PULPITS.

Новая версия процедуры с помощью оператора RETURN возвращает к точке вызова количество строк в таблице PULPIT.

Рис. 13.64. Удаление и создание новой процедуры

На рис. 13.65 приведен пример сценария, в котором вызывается вновь созданная хранимая процедура GET_ PULPITS, возвращающая к точке вызова количество строк в таблице PULPIT и формирующая результирующий набор.

Рис. 13.65. Вызов процедуры, возвращающей целочисленное значение
 к точке вызова

На рис. 13.66 приведен пример сценария, модифицирующего процедуру GET_ PULPITS. Модифицированная процедура принимает два параметра: входной параметр @ f и выходной (указано ключевое слово OUTPUT) параметр @ c. В результате своей работы процедура формирует значение выходного параметра (равно значению встроенной функции @@rowcount), выводит в стандартный поток сообщение (оператор PRINT), а также, как прежде, формирует результирующий набор и код возврата к точке вызова.

Рис. 13.66. Модификация процедуры GET_ PULPITS

Пример вызова последней версии процедуры GET_ PULPITS приведен в сценарии на рис. 13.67. При вызове хранимой процедуры применяется параметрическая форма передачи параметров (задаются имена параметров и их значения в произвольном порядке).

Для входных параметров можно задать значения по умолчанию. В этом случае при вызове процедуры можно не указывать значение этого параметра (рис. 13.68).

Рис. 13.67. Вызов процедуры GET_ PULPITS после модификации
(рис. 13.66) и результаты ее выполнения

Рис. 13.68. Установка значения входного параметра по умолчанию

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

Рис. 13.69. Применение в операторе INSERT хранимой процедуры,
 формирующей результирующий набор

В хранимых процедурах допускается применение основных DDL и всех DML и TCL-операторов, конструкций TRY/CATCH, курсоров, использование временных таблиц.

На рис. 13.70 приведен пример хранимой процедуры, создающей и заполняющей временную таблицу данными, формирующимися на основе значений входных параметров.

Рис. 13.70. Пример хранимой процедуры, использующий механизм транзакций,
конструкцию TRAY/CATCH, временную таблицу

В процедуре используются механизм транзакций и конструкция для структурной обработки ошибок.

Пользовательские функции

Функция – это объект БД, представляющий собой поименованный код T-SQL. Для создания, удаления и изменения функций необходимо применять CREATE, DROP и ALTER соответственно. Отличие функций от хранимых процедур в ограничениях, накладываемых на код функции, в форме представления результата работы, а также в способе вызова.

В функции не допускается применение DDL-операторов, DML-операторов, изменяющих БД (INSERT, DELETE, UPDATE), конструкций TRY/CATCH, а также использование транзакций.

Результатом выполнения функции является возвращаемое к точке вызова значение. Если функция возвращает единственное значение (число, строка, дата, время и пр.), то она называется скалярной. Функция, возвращающая таблицу, называется табличной. В зависимости от структуры кода различают встроенные (inline) функции и многооператорные табличные функции.

Скалярные функции

Скалярная функция возвращает единственное значение и принимает любое количество параметров. Допускается возвращать и принимать все типы данных, за исключением TIMESTAMP и устаревших IMAGE, TEXT и NTEXT.

На рис. 13.71 представлен пример создания функции с именем COUNT_ STUDENTS.

Рис. 13.71. Создание скалярной функции с именем COUNT_ STUDENTS,
 принимающей один параметр и возвращающей значение типа INT

Функция COUNT_ STUDENTS принимает один параметр типа VARCHAR с именем @ faculty и возвращает значение типа INT. В теле функции объявляется переменная @ rc. Затем ей присваивается значение, вычисленное в результате выполнения оператора SELECT. С помощью оператора RETURN значение переменной возвращается к точке вызова.

На рис. 13.71–13.75 приведены сценарии, демонстрирующие способы вызова скалярной функции COUNT_ STUDENTS.

Рис. 13.72. Применение скалярной функции для инициализации
или присвоения значения переменных

Рис. 13.73. Встроенный вызов скалярной функции

Рис. 13.74. Применение скалярной функции в списке SELECT

Рис. 13.75. Применение скалярной функции в секции WHERE

оператора SELECT

Обратите внимание: при вызове функции необходимо указывать ее имя с точностью до схемы БД. При создании (рис. 13.71) функции имя схемы не указывалось, что привело ее размещение в схеме по умолчанию – DBO.

При создании или изменении функции можно указать для параметров значения по умолчанию (рис. 13.76). 

Рис. 13.76. Изменение функции COUNT_ STUDENTS: функция принимает
 два параметра с установленными значениями по умолчанию

На рис. 13.77–13.79 приведены примеры вызова скалярной функции COUNT_ STUDENTS без применения (13.77) и с применением значений по умолчанию для ее параметров (13.78, 13.79).

Рис. 13.77. Вызов в SELECT-списке функции COUNT_ STUDENTS с заданными
 значениями для двух параметров

Рис. 13.78. Вызов функции COUNT_ STUDENTS с заданным значением
 для первого и значением по умолчанию для второго параметров

Рис. 13.79. Вызов функции COUNT_ STUDENTS со значениями
 по умолчанию для двух параметров

Обратите внимание: при использовании значения по умолчанию параметра необходимо указывать ключевое слово default в соответствующей этому параметру позиции в списке параметров функции.