Хранимые процедуры в microsoft sql server

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

По отношению к БД — это объекты, которые создаются и хранятся в БД. Они могут быть вызваны из клиентских приложений. При этом одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Так же как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их вовсе.

Каждая хранимая процедура компилируется при первом выполнении, в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выполнения хранится в системных таблицах БД.

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

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

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

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

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

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

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

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

Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные.

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

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

Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##.

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

Создание хранимой процедуры предполагает решение следующих задач:

• планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;

• определение параметров хранимой процедуры, хранимые процедуры могут обладать входными и выходными параметрами;

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

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

<определение_процедуры>::=

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

[;номер]

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

[=default][OUTPUT] ][,...n]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,

ENCRYPTION }]

[FOR REPLICATION]

AS

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

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

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

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

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

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

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

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

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

При указании типа данных cursor нужно также указать ключевые слова VARYING и OUTPUT. Выходных параметров типа cursor может быть несколько.

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру.

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

Не разрешается использование любых выражений или констант, допустимое для обычных параметров. Аргументы типов text, ntext и image не могут быть выходными, если процедура не является процедурой CLR.

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

Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.

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

Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.

Параметр FOR REPLICATION востребован при репликации данных.

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

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

Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете.

Использование RETURN в хранимой процедуре. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

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

Синтаксис:

RETURN [ integer_expression ]

Аргументы:

integer_expression - возвращаемое целочисленное значение. Хранимые процедуры могут возвращать целочисленное значение вызывающей их процедуре или приложению. Тип int. Пример приведен ниже.

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

DROP PROCEDURE {имя_процедуры} [,...n]

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

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

[ { EXEC | EXECUTE } ]

    {

      [ @return_status = ]

       имя_процедуры [;номер]

        [ [@имя_параметра=] {значение | @имя_переменной [ OUTPUT ]

                           | [ DEFAULT ]

                           }

        ]

      [ ,...n ]

      [ WITH RECOMPILE ]

    }

[;]

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

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

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

Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры.

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

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

Пример:

CREATE TABLE dbo.Table11

(c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);

INSERT INTO dbo.Table11 VALUES (1, 10);

INSERT INTO dbo.Table11 VALUES (2, 20);

INSERT INTO dbo.Table11 VALUES (3, 30);

Процедура:

CREATE PROC my_proc11 @par1 int output, @par2 int output

AS

SELECT @par1= c1, @par2 = c2 FROM dbo.Table11;

Обращение:

Declare @p1 int, @p2 int

EXEC my_proc11 @p1 output, @p2 output

select @p1, @p2

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

Результат:

Выводится только одна строка (последняя).

Пример задания значения входного параметра по умолчанию:

CREATE PROC my_proc55 @par1 int output, @par2 int output, @par3 As int =10

AS

SELECT @par1= c1, @par2 = c2 FROM dbo.Table5 where c2=@par3;

Обращение к процедуре:

Declare @p1 int, @p2 int

EXEC my_proc55 @p1 output, @p2 output --!!!!!!!!!!

select @p1, @p2

или

Declare @p1 int, @p2 int

EXEC my_proc55 @p1 output, @p2 output, DEFAULT

select @p1, @p2

Результат:

Примеры создания и использования хранимых процедур для базы данных DB_Books:

1. Процедура без параметров:

CREATE PROCEDURE Count_Books AS

Select count(Code_book) from Books

Go

Обращение к процедуре:

EXEC Count_Books

Результат:

2. Процедура c входным параметром @Count_pages:

CREATE PROCEDURE Count_Books_Pages @Count_pages as Int

AS Select count(Code_book) from Books

WHERE Pages>=@Count_pages

Go

Обращение к процедуре:

EXEC Count_Books_Pages 100

3. Процедура c входными параметрами @Count_pages и @Title:

CREATE PROCEDURE Count_Books_Title @Count_pages as Int, @Title AS Char(10) AS

Select count(Code_book) from Books

WHERE Pages>=@Count_pages AND Title_book LIKE @Title

Go

Обращение к процедуре:

EXEC Count_Books_Title 100, 'П%'

4. Процедура c входными параметрами @Count_pages и @Title и выходным параметром @Itogo:

CREATE PROCEDURE Count_Books_Itogo @Count_pages Int, @Title Char(10), @Itogo Int OUTPUT

AS

Select @Itogo = count(Code_book) from Books

WHERE Pages>=@Count_pages AND Title_book LIKE @Title

Go

Обращение к процедуре:

Declare @q As int

EXEC Count_Books_Itogo 100, 'П%', @q output

select @q

5. Процедура c входным параметром @param и RETURN:

CREATE PROCEDURE checkname @param int AS

IF (SELECT Name_author FROM authors WHERE Code_author = @param) = 'Пушкин А.С.'

RETURN 1

ELSE

RETURN 2

Обращение к процедуре:

DECLARE @return_status As int

EXEC @return_status = checkname 3

SELECT 'Return Status' = @return_status

6. Процедура c входным параметром @k для получения всей информации о конкретном авторе:

CREATE PROC select_author @k CHAR(30)

AS SELECT * FROM Authors WHERE name_author=@k

Обращение к процедуре:

EXEC select_author 'Пушкин А.С.'

или

Declare @p1 As CHAR(30)

set @p1='Пушкин А.С.'

EXEC select_author @p1

Использование типа данных cursor в параметре OUTPUT

Хранимые процедуры языка Transact-SQL могут использовать тип данных cursor только для параметров OUTPUT. Если тип данных cursor указан для параметра, должны быть также указаны оба параметра VARYING и OUTPUT. Если для параметра указано ключевое слово VARYING, тип данных должен быть cursor и должно быть указано ключевое слово OUTPUT.

Примечание: Тип данных cursor не может быть связан с переменными приложения через интерфейсы API баз данных, таких как OLE DB, ODBC, ADO и DB-Library. Поскольку параметры OUTPUT должны быть связаны прежде, чем приложение может выполнить хранимую процедуру, хранимые процедуры с параметрами OUTPUT типа cursor не могут быть вызваны из функций API базы данных. Эти процедуры могут быть вызваны из пакетов языка Transact-SQL, хранимых процедур или триггеров, только когда переменная OUTPUT типа cursor присвоена локальной переменной языка Transact-SQL типа cursor.

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

1. Для однонаправленного курсора в результирующий набор курсора будут возвращены только строки с текущей позиции курсора до конца курсора. Текущая позиция курсора определяется при окончании выполнения хранимой процедуры.

Например:

Непрокручиваемый курсор открыт в процедуре на результирующем наборе по имени RS из 100 строк.

Процедура выбирает первые 5 строк результирующего набора RS.

Процедура возвращает результат участнику.

Результирующий набор RS, возвращенный участнику, состоит из строк с 6 по 100 из набора RS, и курсор в участнике позиционирован перед первой строкой RS.

2. Для однонаправленного курсора, если курсор позиционирован перед первой строкой после завершения хранимой процедуры, весь результирующий набор будет возвращен к вызывающему пакету, хранимой процедуре или триггеру. После возврата позиция курсора будет установлена перед первой строкой.

3. Для однонаправленного курсора, если курсор позиционирован за концом последней строки после завершения хранимой процедуры, вызывающему пакету, хранимой процедуре или триггеру будет возвращен пустой результирующий набор.

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

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

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

Пример:

В следующем примере создается хранимая процедура, которая указывает выходной параметр @currency_cursor, используя тип данных cursor. Хранимая процедура затем будет вызвана из пакета.

Создание и заполнение таблицы для примера:

IF OBJECT_ID ('Table1', N'U') IS NOT NULL

DROP TABLE Table1;

GO

CREATE TABLE Table1

(c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);

GO

INSERT INTO Table1 VALUES (1, 10);

INSERT INTO Table1 VALUES (2, 20);

INSERT INTO Table1 VALUES (3, 30);

GO

select * from Table1

Создание процедуры:

IF OBJECT_ID ( 'PrimerCursor', 'P' ) IS NOT NULL

DROP PROCEDURE PrimerCursor;

GO

CREATE PROCEDURE PrimerCursor

@CurrencyCursor CURSOR VARYING OUTPUT

AS

SET NOCOUNT ON;

SET @CurrencyCursor = CURSOR

STATIC FORWARD_ONLY FOR

select c1, c2 from Table1

OPEN @CurrencyCursor;

GO

Обращение к процедуре:

DECLARE @MyCursor CURSOR;

EXEC PrimerCursor @CurrencyCursor = @MyCursor OUTPUT;

--обратите внимание на синтаксис, @CurrencyCursor = @MyCursor

WHILE (@@FETCH_STATUS = 0)

BEGIN

FETCH NEXT FROM @MyCursor

END;

CLOSE @MyCursor;

DEALLOCATE @MyCursor;

GO

Результат:

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

Измененный пример:

IF OBJECT_ID ( 'PrimerCursor', 'P' ) IS NOT NULL

DROP PROCEDURE PrimerCursor;

GO

CREATE PROCEDURE PrimerCursor

@CurrencyCursor CURSOR VARYING OUTPUT

AS

SET NOCOUNT ON;

SET @CurrencyCursor = CURSOR

STATIC SCROLL FOR

select c1, c2 from Table1

OPEN @CurrencyCursor;

FETCH ABSOLUTE 2 FROM @CurrencyCursor

FETCH prior FROM @CurrencyCursor

GO

--Обращение к процедуре:

DECLARE @MyCursor CURSOR;

EXEC PrimerCursor @CurrencyCursor = @MyCursor OUTPUT;

FETCH NEXT FROM @MyCursor

CLOSE @MyCursor;

DEALLOCATE @MyCursor;

GO

Результат:

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

Пример:

--Создание процедуры с выходным параметром курсором

USE AdventureWorks;

GO

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL

DROP PROCEDURE dbo.uspCurrencyCursor;

GO

CREATE PROCEDURE dbo.uspCurrencyCursor

@CurrencyCursor CURSOR VARYING OUTPUT

AS

SET NOCOUNT ON;

SET @CurrencyCursor = CURSOR

FORWARD_ONLY DYNAMIC FOR

SELECT CurrencyCode, Name

FROM Sales.Currency where Name Like 'S%';

OPEN @CurrencyCursor;

GO

Вызов процедуры:

USE AdventureWorks;

GO

DECLARE @MyCursor CURSOR;

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;

WHILE (@@FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @MyCursor;

END;

CLOSE @MyCursor;

DEALLOCATE @MyCursor;

GO

Результат:

Функции, определенные пользователем

Функции всегда имеют один возвращаемый параметр.

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

Скалярная функция возвращает атомарное (скалярное) значение.

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

Функции создаются при помощи оператора create function, который имеет cледующий синтаксис:

create function [schema_name.]function_name

[ { @parameter_name [ AS ] parameter_data_type     [ = default ] }     [ ,...n ]  ]

RETURNS {scalar_type | [@variable] TABLE}

[WITH (ENCRYPTION | SCHEMABINDING)]

[AS] {block RETURN scalar_expression

| RETURN (select_statement) }

schema_name - имя схемы, которой назначено владение созданной функцией;

function_name - имя новой функции;

@ parameter_name - имя входного параметра;

parameter_data_type      - задает тип данных параметра;

default - задает необязательное значение по умолчанию для соответствующего параметра. Значением по умолчанию может быть также пустое значение null.

RETURNS - задает тип данных значения, возвращаемого функцией. Это может быть любой стандартный тип данных, поддерживаемый системой базы данных, включая тип данных table (SQL 2008). (Только один стандартный тип данных, который нельзя использовать, является тип данных timestamp);

TABLE -указывает, что возвращаемым значением функции является таблица. Функциям, возвращающим табличное значение, могут передаваться только константы и @local_variables.

block - является блоком begin/end, который содержит реализацию функции. Последним оператором блока должен быть оператор return с аргументом. Значение аргумента - это значение, возвращаемое данной функцией.

В теле блока begin/end допустимы только следующие операторы:

• операторы присваивания, такие как set;

• операторы управления потоком выполнения, такие как while и if;

• операторы declare, определяющие локальные переменные данных;

• операторы select, содержащие списки выбора с выражениями, которые присваиваются в качестве значений переменным, являющимся локальными в этой функции;

• операторы insert, update и delete, изменяющие переменные типа table, которые являются локальными в этой функции.

По умолчанию только участники фиксированной серверной роли sysadmin, а также участники фиксированных ролей базы данных db_owner и db_ddladmin могут использовать оператор create function.

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

RETURN scalar_expression -возвращаемое атомарное (скалярное) значение.

Синтаксис:

……………………………………

RETURNS return_data_type

    [ WITH <function_option> [ ,...n ] ]

    [ AS ]

    BEGIN

                function_body

        RETURN scalar_expression

    END

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

RETURN (select_statement) – определяет выходные табличные данные функции. Инструкция RETURN не может иметь аргумента.

with encrYption - кодирует информацию в системном каталоге, который содержит текст оператора create function.

with schemabinding связывает функцию с объектами базы данных.

Пример: функция вычисляет среднее 3 чисел.

CREATE FUNCTION SRED

(@X1 Int, @X2 Int, @X3 Int)

RETURNS real

AS

BEGIN

declare @RES As real

set @RES =(@X1+@X2+@X3)/3

RETURN @RES

END

Вызов:

select dbo.SRED (3, 4, 5)

Пример: функция вычисляет дополнительные затраты, если увеличиваются бюджеты проектов

US sample;

GO

CREATE FUNCTION compute_costs (@percent INT =10) -- значение по умолчанию

     RETURNS DECIMAL(16,2)

BEGIN

   DECLARE @additional_costs DEC (14,2), @sum_budget dec(16,2)

  SELECT @sum_budget = SUM (budget) FROM project

  SET @additional_costs = @sum_budget * @percent/100

  RETURN @additional_costs  

END

Входная переменная @percent задает процент увеличения бюджетов. Блок begin/end объявляет две локальные переменные: @additionai_cost и @sum_budget. Затем функция присваивает переменной @sum_budget сумму всех бюджетов, используя специальную форму оператора select. После этого функция вычисляет общие дополнительные затраты и возвращает это значение с помощью оператора return.

Пример: функция вычисляет возраст сотрудника на сегодняшний день.

IF OBJECT_ID('dbo.fn_age') IS NOT NULL DROP FUNCTION dbo.fn_age;

GO

CREATE FUNCTION dbo.fn_age

(

@birthdate AS DATETIME,

@eventdate AS DATETIME

)

RETURNS INT

AS

BEGIN

RETURN

DATEDIFF(year, @birthdate, @eventdate)

- CASE WHEN MONTH(@eventdate) + DAY(@eventdate)

< MONTH(@birthdate) + DAY(@birthdate)

THEN 1 ELSE 0

END

END

GO

Функции может быть вызвана в операторах Transact-SQL, таких как select, insert, update или delete.

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

При вызове функции, когда все входные параметры не имеют значения по умолчанию, нужно задать значения для каждого параметра в том же порядке, в котором эти параметры определены в операторе create function.

Пример: использование функции compute_cost

USE sample;

SELECT project_no, project_name

    FROM project

 WHERE budget < dbo.compute_costs (25)

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

Пример: использование функции compute_cost

USE sample;

SELECT dbo.compute_costs (25)

Пример: использование функции dbo.fn_age

SELECT

empid, firstname, lastname, birthdate,

dbo.fn_age(birthdate, GETDATE ()) AS age

FROM HR.Employees;

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

Ни одна из инструкций Transact-SQL в возвращающей табличное значение функции не может возвращать результирующий набор непосредственно пользователю. Единственные данные, которые функция может вернуть пользователю, это таблица table, возвращаемая этой функцией.

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

Если предложение returns задает table без указания списка столбцов, то эта функция является линейной. Линейные функции возвращают результат выполнения оператора select в виде переменной типа данных table

Синтаксис:

……………………………………

RETURNS TABLE

    [ WITH <function_option> [ ,...n ] ]

    [ AS ]

    RETURN [ ( ] select_stmt [ ) ]

или

Пример:

USE sample;

GO

CREATE FUNCTION employees_in_project (@pr_number CHAR(4))

RETURNS TABLE

AS RETURN (SELECT emp_fname, emp_lname

FROM works_on, employee

WHERE employee.emp_no = works_on.emp_no

AND project_no = @pr_number)

Функция employees_in_project отображает имена всех служащих, которые связаны с конкретным проектом. Входной параметр @pr_number задает номер проекта. Поскольку функция в общем случае вращает набор строк, предложение returns содержит тип данных TABLE. Блока begin/end в примере нет, потому что предложение return содержит оператор select.

Обращение к функции имеет вид:

USE sample;

SELECT *     FROM employees_in_project('p3')

Результат:

Пример использования функции, возвращающей таблицу:

CREATE FUNCTION fn_getcustomerorders1(@CustomerID int, @TopRecords bigint)

RETURNS TABLE

As Return

SELECT TOP (@TopRecords) *

FROM Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

ORDER BY OrderDate DESC

Обращение к функции:

SELECT * FROM fn_getcustomerorders1(5,3 );

Результат:

Многооператорная табличная функция включает имя, определяющее table. Имя задает внутреннюю переменную типа table. Можно использовать эту переменную для добавления в нее строк, а затем вернуть эту переменную в качестве возвращаемого значения функции.

Синтаксис:

………………………………………………………

RETURNS @return_variable TABLE < table_type_definition >

    [ WITH <function_option> [ ,...n ] ]

    [ AS ]

    BEGIN

                function_body

        RETURN

    END

RETURNS определяет имя локальной возвращаемой переменной для таблицы, которую возвращает эта функция. Предложение RETURNS также определяет формат таблицы. Область видимости имени локальной возвращаемой переменной является локальной в этой функции.

Пример: В следующем примере создается функция dbo.ufnGetContactInformation и демонстрируются компоненты возвращающей табличное значение функции. В этой функции именем локальной возвращаемой переменной является @retContactInformation. Инструкции в теле функции вставляют строки в эту переменную для создания табличных результатов, возвращаемых этой функцией.

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

--@ContactID входной параметр – индекс контакта

RETURNS @retContactInformation TABLE

(

-- Поля, возвращаемые функцией

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Присвоение общей информации

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Проверка служащих

WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Проверить продавцов

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc

INNER JOIN Person.ContactType AS ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact AS vc

INNER JOIN Person.ContactType AS ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Проверить магазины

WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc

INNER JOIN Person.ContactType AS ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact AS sc

INNER JOIN Person.ContactType AS ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Проверить служащих

WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Проверить продавцов

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc

INNER JOIN Person.ContactType AS ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Проверить магазины

WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc

INNER JOIN Person.ContactType AS ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Проверить покупателей

WHEN EXISTS(SELECT * FROM Sales.Individual AS i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- формирование вызываемой информации

IF @ContactID IS NOT NULL

BEGIN

INSERT INTO @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

Обращение к функции:

USE AdventureWorks;

GO

SELECT ContactID, FirstName, LastName, JobTitle, ContactType

FROM dbo.ufnGetContactInformation(2200);

Результат:

Пользовательские функции, возвращающие тип данных table, могут быть полноценной альтернативой представлениям. Ссылка на эти функции происходит как на возвращающие табличное значение функции. Возвращающая табличное значение пользовательская функция может быть использована там, где в запросах Transact-SQL разрешены табличные выражения или выражения представлений. В то время как представления ограничены одной инструкцией SELECT, пользовательские функции могут содержать дополнительные инструкции, обеспечивающие более эффективную логику, чем та, которая возможна в представлениях.

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

Вложенные хранимые процедуры

Вложенностью называют ситуацию, когда хранимая процедура вызывает другую процедуру или выполняет управляемый код, ссылаясь на подпрограмму, тип или статистическую функцию среды Common Language Runtime (CLR) Integration. Любая ссылка на управляемый код внутри хранимой процедуры Transact-SQL считается одним уровнем вложенности.

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

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

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

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

CREATE PROC my_proc7 @n VARCHAR(20), @f VARCHAR(20) OUTPUT

AS

SELECT @f=Фирма

FROM Клиент

WHERE Фамилия=@n

2. Затем создается процедура (вызывающая процедура), подсчитывающая общее количество товара, который закуплен интересующей нас фирмой.

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

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

DECLARE @k INT

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

SELECT @k