Ms sql создание функции. Примеры создания и использования функций sql. Создание EXEC-хранимой процедуры в SQL

1) Список стандартных функций

Функция	Возвращаемый результат
Bit_Length()	Количество битов в
Cast(As)	, преобразованное в указанный
Char_Length()	Длина символов
Convert(using)	, преобразованная в соответствии с указанной
Current_Date	Текущая дата
Current_Time()	Текущее время с указанной
Current_TimeStamp()	Текущая дата и время с указанной
Extract(from)	Указанная (Day, Hour и т.п.) из даты
Lower()	, преобразованная к нижнему регистру
Octet_Length()	Число байтов в
Position(in)	Позиция, с которой входит в
Substring(from for)	Часть, начинающаяся с позиции и имеющая указанную
Trim(Leading|Trailing|Both from)	, у которой удалены ведущие | концевые | с обоих сторон
Upper()	, преобразованная к верхнему регистру
User	Определяет идентификатор пользователя

2) Обзор функций MS SQL Server
Так в SQL Server предусмотрено много функций, разделенных на следующие группы:
→ Строковые;
→ Математические;
→ Преобразования;
→ Для работы с данными типа Text и Image;
→ Для работы с датами;
→ Системные;
→ Ниладические (нульместные – без параметров).
А так же целый ряд других функций.

3) Обзор функций Oracle
→ Для работы с ошибками;
→ Числовые;
→ Строковые;
→ Преобразования;
→ Трансляции, для работы с датами;
→ Различного назначения.

Объявление хранимой функции

CREATE FUNCTION ([ [()], …]) RETURNS [()] [[NOT ] DETERMINISTIC ] [CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA ]
BEGIN

RETURN
END

Ключевые слова
. DETERMINISTIC показывает, возвращает или нет функция одинаковые значения при одних и тех же входных значениях. Например, функция CURRENT_TIME является NOT DETERMINISTIC.
. CONTAINS SQL показывает, что в функции нет SQL-операторов, читающих и модифицирующих данные. Это значение установлено по умолчанию.
. READS SQL DATA показывает, что функция содержит инструкции SELECT или FETCH.
. MODIFIES SQL DATA показывает, что функция содержит инструкции INSERT, UPDATE или DELETE.

Ограничения на недетерминистские функции

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

В SQL Server пользовательская функция считается детерминистическое, если:
. Функция является привязанной к схеме, т.е. функция создана с использованием опции SCHEMABINDING, а это означает, что объекты, на которые ссылается данная функция, не могут изменяться или удаляться.
. Каждая функция (неважно, встроенная или определяемая пользователем), вызываемая из тела этой функции, является детерминистской.
. В теле функции отсутствуют ссылки на объекты БД (например, таблицы, представления и другие функции), выходившие за пределы области видимости.
. Функция не обращается к расширенным хранимым процедурам (которые могут изменять состояние БД).

Удаление и изменение хранимых функций

Для удаления функции используется оператор:

Для изменения функции используется оператор:

ALTER FUNCTION ([[{IN|OUT|INOUT }] [()],…])
BEGIN

RETURN
END

Хранимые функции в SQL Server

CREATE FUNCTION ([@ [AS ] [()] [=] [READONLY ],…])
RETURNS [()]
[AS ]
BEGIN

RETURN
END

Пример:

USE B1;
GO
CREATE FUNCTION Quarter(@Dat DateTime) RETURNS int
BEGIN
DECLARE @ISQuarter int;
IF ((Month(@Dat)>=1) And (Month(@Dat)=4) And (Month(@Dat)=8) And (Month(@Dat)=10) And (Month(@Dat)

Хранимые процедуры SQL представляют собой исполняемый программный модуль, который может храниться в в виде различных объектов. Другими словами, это объект, в котором содержатся SQL-инструкции. Эти хранимые процедуры могут быть выполнены в клиенте прикладных программ, чтобы получить хорошую производительность. Кроме того, такие объекты нередко вызываются из других сценариев или даже из какого-либо другого раздела.

Введение

Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).

В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».

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

Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.

Продуктивность

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

Безопасность

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

Передача данных

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

Передача данных с помощью параметра типа Output;

Передача данных с помощью оператора возврата;

Передача данных с помощью оператора выбора.

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

1. Создание EXEC-хранимой процедуры в SQL

Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.

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

Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.

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

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

Тело не должно создавать какой-либо другой хранимой процедуры;

Тело не должно создать ложное представление об объекте;

Тело не должно создавать никаких триггеров.

2. Установка переменной в тело процедуры

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

Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ​​ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.

Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.

В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.

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

Как в SQL выполнить хранимую процедуру

Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.

Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.

4. Хранимые процедуры SQL Server: способы возврата

Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:

Возврат значения хранимой процедуры;

Выход параметра хранимых процедур;

Выбор одной из хранимых процедур.

4.1 Возврат значений хранимых процедур SQL

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

Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.

4.2 Выход параметра хранимых процедур SQL

Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз-таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.

В нашем примере будет два выходных имени: @TotalAuthors и @TotalNoContract. Они указываются в списке параметров. Эти переменные присваивают значения внутри тела процедуры. Когда мы используем выходные параметры, вызывающий абонент может видеть значение, установленное внутри тела процедуры.

Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.

4.3 Выбор одной из хранимых процедур SQL

Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.

В заключение

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

В языках программирования обычно имеется два типа подпрограмм:

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

определяемые пользователем функции (UDF).

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

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

Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION , которая имеет следующий синтаксис:

CREATE FUNCTION function_name [({@param } type [= default]) {,...} RETURNS {scalar_type | [@variable] TABLE} {block | RETURN (select_statement)} Соглашения по синтаксису

Параметр schema_name определяет имя схемы, которая назначается владельцем создаваемой UDF, а параметр function_name определяет имя этой функции. Параметр @param является входным параметром функции (формальным аргументом), чей тип данных определяется параметром type. Параметры функции - это значения, которые передаются вызывающим объектом определяемой пользователем функции для использования в ней. Параметр default определяет значение по умолчанию для соответствующего параметра функции. (Значением по умолчанию также может быть NULL.)

Предложение RETURNS определяет тип данных значения, возвращаемого UDF. Это может быть почти любой стандартный тип данных, поддерживаемый системой баз данных, включая тип данных TABLE. Единственным типом данных, который нельзя указывать, является тип данных timestamp.

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

Параметр WITH ENCRYPTION в системном каталоге кодирует информацию, содержащую текст инструкции CREATE FUNCTION. Таким образом, предотвращается несанкционированный просмотр текста, который был использован для создания функции. Данная опция позволяет повысить безопасность системы баз данных.

Альтернативное предложение WITH SCHEMABINDING привязывает UDF к объектам базы данных, к которым эта функция обращается. После этого любая попытка модифицировать объект базы данных, к которому обращается функция, претерпевает неудачу. (Привязка функции к объектам базы данных, к которым она обращается, удаляется только при изменении функции, после чего параметр SCHEMABINDING больше не задан.)

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

все представления и другие UDF, к которым обращается определяемая функция, должны быть привязаны к схеме;

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

Параметр block определяет блок BEGIN/END, содержащий реализацию функции. Последней инструкцией блока должна быть инструкция RETURN с аргументом. (Значением аргумента является возвращаемое функцией значение.) Внутри блока BEGIN/END разрешаются только следующие инструкции:

инструкции присвоения, такие как SET;

инструкции для управления ходом выполнения, такие как WHILE и IF;

инструкции DECLARE, объявляющие локальные переменные;

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

инструкции INSERT, UPDATE и DELETE, которые изменяют переменные с типом данных TABLE, являющиеся локальными для данной функции.

По умолчанию инструкцию CREATE FUNCTION могут использовать только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присвоить это право другим пользователям с помощью инструкции GRANT CREATE FUNCTION.

В примере ниже показано создание функции ComputeCosts:

USE SampleDb; -- Эта функция вычисляет возникающие дополнительные общие затраты, -- при увеличении бюджетов проектов GO CREATE FUNCTION ComputeCosts (@percent INT = 10) RETURNS DECIMAL(16, 2) BEGIN DECLARE @addCosts DEC (14,2), @sumBudget DEC(16,2) SELECT @sumBudget = SUM (Budget) FROM Project SET @addCosts = @sumBudget * @percent/100 RETURN @addCosts END;

Функция ComputeCosts вычисляет дополнительные расходы, возникающие при увеличении бюджетов проектов. Единственный входной параметр, @percent, определяет процентное значение увеличения бюджетов. В блоке BEGIN/END сначала объявляются две локальные переменные: @addCosts и @sumBudget, а затем с помощью инструкции SELECT переменной @sumBudget присваивается общая сумма всех бюджетов. После этого функция вычисляет общие дополнительные расходы и посредством инструкции RETURN возвращает это значение.

Вызов определяемой пользователем функции

Определенную пользователем функцию можно вызывать с помощью инструкций Transact-SQL, таких как SELECT, INSERT, UPDATE или DELETE. Вызов функции осуществляется, указывая ее имя с парой круглых скобок в конце, в которых можно задать один или несколько аргументов. Аргументы - это значения или выражения, которые передаются входным параметрам, определяемым сразу же после имени функции. При вызове функции, когда для ее параметров не определены значения по умолчанию, для всех этих параметров необходимо предоставить аргументы в том же самом порядке, в каком эти параметры определены в инструкции CREATE FUNCTION.

В примере ниже показан вызов функции ComputeCosts в инструкции SELECT:

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

В инструкциях Transact-SQL имена функций необходимо задавать, используя имена, состоящие из двух частей: schema name и function name, поэтому в примере мы использовали префикс схемы dbo.

Возвращающие табличное значение функции

Как уже упоминалось ранее, функция является возвращающей табличное значение, если ее предложение RETURNS возвращает набор строк. В зависимости от того, каким образом определено тело функции, возвращающие табличное значение функции классифицируются как встраиваемые (inline) и многоинструкционные (multistatement) . Если в предложении RETURNS ключевое слово TABLE указывается без сопровождающего списка столбцов, такая функция является встроенной. Инструкция SELECT встраиваемой функции возвращает результирующий набор в виде переменной с типом данных TABLE.

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

Создание возвращающей табличное значение функции показано в примере ниже:

Функция EmployeesInProject отображает имена всех сотрудников, работающих над определенным проектом, номер которого задается входным параметром @projectNumber. Тогда как функция в общем случае возвращает набор строк, предложение RETURNS в определение данной функции содержит ключевое слово TABLE, указывающее, что функция возвращает табличное значение. (Обратите внимание на то, что в примере блок BEGIN/END необходимо опустить, а предложение RETURN содержит инструкцию SELECT.)

Использование функции Employees_in_Project приведено в примере ниже:

USE SampleDb; SELECT * FROM EmployeesInProject("p3")

Результат выполнения:

Возвращающие табличное значение функции и инструкция APPLY

Реляционная инструкция APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки табличного выражения. Эта инструкция задается в предложении FROM соответствующей инструкции SELECT таким же образом, как и инструкция JOIN. Инструкция APPLY может быть объединена с табличной функцией для получения результата, похожего на результирующий набор операции соединения двух таблиц. Существует две формы инструкции APPLY:

Инструкция CROSS APPLY возвращает те строки из внутреннего (левого) табличного выражения, которые совпадают с внешним (правым) табличным выражением. Таким образом, логически, инструкция CROSS APPLY функционирует так же, как и инструкция INNER JOIN.

Инструкция OUTER APPLY возвращает все строки из внутреннего (левого) табличного выражения. (Для тех строк, для которых нет совпадений во внешнем табличном выражении, он содержит значения NULL в столбцах внешнего табличного выражения.) Логически, инструкция OUTER APPLY эквивалентна инструкции LEFT OUTER JOIN.

Применение инструкции APPLY показано в примерах ниже:

Функция GetJob() возвращает набор строк с таблицы Works_on. В примере ниже этот результирующий набор "соединяется" предложением APPLY с содержимым таблицы Employee:

USE SampleDb; -- Используется CROSS APPLY SELECT E.Id, FirstName, LastName, Job FROM Employee as E CROSS APPLY GetJob(E.Id) AS A -- Используется OUTER APPLY SELECT E.Id, FirstName, LastName, Job FROM Employee as E OUTER APPLY GetJob(E.Id) AS A

Результатом выполнения этих двух функций будут следующие две таблицы (отображаются после выполнения второй функции):

В первом запросе примера результирующий набор табличной функции GetJob() "соединяется" с содержимым таблицы Employee посредством инструкции CROSS APPLY. Функция GetJob() играет роль правого ввода, а таблица Employee - левого. Выражение правого ввода вычисляется для каждой строки левого ввода, а полученные строки комбинируются, создавая конечный результат.

Второй запрос похожий на первый (но в нем используется инструкция OUTER APPLY), который логически соответствует операции внешнего соединения двух таблиц.

Возвращающие табличное значение параметры

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

Использование возвращающего табличное значение параметра показано в примере ниже:

USE SampleDb; CREATE TYPE departmentType AS TABLE (Number CHAR(4), DepartmentName CHAR(40), Location CHAR(40)); GO CREATE TABLE #moscowTable (Number CHAR(4), DepartmentName CHAR(40), Location CHAR(40)); GO CREATE PROCEDURE InsertProc @Moscow departmentType READONLY AS SET NOCOUNT ON INSERT INTO #moscowTable (Number, DepartmentName, Location) SELECT * FROM @Moscow GO DECLARE @Moscow AS departmentType; INSERT INTO @Moscow (Number, DepartmentName, Location) SELECT * FROM department WHERE location = "Москва"; EXEC InsertProc @Moscow;

В этом примере сначала определяется табличный тип departmentType. Это означает, что данный тип является типом данных TABLE, вследствие чего он разрешает вставку строк. В процедуре InsertProc объявляется переменная @Moscow с типом данных departmentType. (Предложение READONLY указывает, что содержимое этой таблицы нельзя изменять.) В последующем пакете в эту табличную переменную вставляются данные, после чего процедура запускается на выполнение. В процессе исполнения процедура вставляет строки из табличной переменной во временную таблицу #moscowTable. Вставленное содержимое временной таблицы выглядит следующим образом:

Использование возвращающих табличное значение параметров предоставляет следующие преимущества:

упрощается модель программирования подпрограмм;

уменьшается количество обращений к серверу и получений соответствующих ответов;

таблица результата может иметь произвольное количество строк.

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

Язык Transact-SQL также поддерживает инструкцию ALTER FUNCTION , которая модифицирует структуру определяемых пользователями инструкций (UDF). Эта инструкция обычно используется для удаления привязки функции к схеме. Все параметры инструкции ALTER FUNCTION имеют такое же значение, как и одноименные параметры инструкции CREATE FUNCTION.

Для удаления UDF применяется инструкция DROP FUNCTION . Удалить функцию может только ее владелец или член предопределенной роли db_owner или sysadmin.

Определяемые пользователем функции и среда CLR

В предыдущей статье мы рассмотрели способ создания хранимых процедур из управляемого кода среды CLR на языке C#. Этот подход можно использовать и для определяемых пользователем функций (UDF), с одним только различием, что для сохранения UDF в виде объекта базы данных используется инструкция CREATE FUNCTION, а не CREATE PROCEDURE. Кроме этого, определяемые пользователем функции также применяются в другом контексте, чем хранимые процедуры, поскольку UDF всегда возвращают значение.

В примере ниже показан исходный код определяемых пользователем функций (UDF), реализованный на языке C#:

Using System.Data.SqlTypes; public class BudgetPercent { private const float percent = 12; public static SqlDouble ComputeBudget(float budget) { return budget * percent; } }

В исходном коде определяемых пользователем функций в примере вычисляется новый бюджет проекта, увеличивая старый бюджет на определенное количество процентов. Вы можете использовать инструкцию CREATE ASSEMBLY для создания сборки CLR в базе данных, как это было показано ранее. Если вы прорабатывали примеры из предыдущей статьи и уже добавили сборку CLRStoredProcedures в базу данных, то вы можете обновить эту сборку, после ее перекомпиляции с новым классом (CLRStoredProcedures это имя моего проекта классов C#, в котором я добавлял определение хранимых процедур и функций, у вас сборка может называться иначе).

CREATE FUNCTION - создать функцию

Синтаксис

CREATE [ OR REPLACE ] FUNCTION имя ([ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [ { DEFAULT | = } выражение_по_умолчанию ] [, ...] ]) [ RETURNS тип_результата | RETURNS TABLE ( имя_столбца тип_столбца [, ...]) ] { LANGUAGE имя_языка | TRANSFORM { FOR TYPE имя_типа } [, ... ] | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | PARALLEL { UNSAFE | RESTRICTED | SAFE } | COST стоимость_выполнения | ROWS строк_в_результате | SET параметр_конфигурации { TO значение | = значение | FROM CURRENT } | AS " определение " | AS " объектный_файл ", " объектный_символ " } ... [ WITH ( атрибут [, ...]) ]

Описание

Команда CREATE FUNCTION определяет новую функцию. CREATE OR REPLACE FUNCTION создаёт новую функцию, либо заменяет определение уже существующей. Чтобы определить функцию, необходимо иметь право USAGE для соответствующего языка.

Если указано имя схемы, функция создаётся в заданной схеме, в противном случае - в текущей. Имя новой функции должно отличаться от имён существующих функций с такими же типами аргументов в этой схеме. Однако функции с аргументами разных типов могут иметь одно имя (это называется перегрузкой ).

Чтобы заменить текущее определение существующей функции, используйте команду CREATE OR REPLACE FUNCTION . Но учтите, что она не позволяет изменить имя или аргументы функции (если попытаться сделать это, на самом деле будет создана новая, независимая функция). Кроме того, CREATE OR REPLACE FUNCTION не позволит изменить тип результата существующей функции. Чтобы сделать это, придётся удалить функцию и создать её заново. (Это означает, что если функция имеет выходные параметры (OUT), то изменить типы параметров OUT можно, только удалив функцию.)

Когда команда CREATE OR REPLACE FUNCTION заменяет существующую функцию, владелец и права доступа к этой функции не меняются. Все другие свойства функции получают значения, задаваемые командой явно или по умолчанию. Чтобы заменить функцию, необходимо быть её владельцем (или быть членом роли-владельца).

Если вы удалите и затем вновь создадите функцию, новая функция станет другой сущностью, отличной от старой; вам потребуется так же удалить существующие правила, представления, триггеры и т. п., ссылающиеся на старую функцию. Поэтому, чтобы изменить определение функции, сохраняя ссылающиеся на неё объекты, следует использовать CREATE OR REPLACE FUNCTION . Кроме того, многие дополнительные свойства существующей функции можно изменить с помощью ALTER FUNCTION .

Владельцем функции становится создавший её пользователь.

Чтобы создать функцию, необходимо иметь право USAGE для типов её аргументов и возвращаемого типа.

Параметры

имя

Имя создаваемой функции (возможно, дополненное схемой). режим_аргумента

Режим аргумента: IN (входной), OUT (выходной), INOUT (входной и выходной) или VARIADIC (переменный). По умолчанию подразумевается IN . За единственным аргументом VARIADIC могут следовать только аргументы OUT . Кроме того, аргументы OUT и INOUT нельзя использовать с предложением RETURNS TABLE . имя_аргумента

Имя аргумента. Некоторые языки (включая SQL и PL/pgSQL) позволяют использовать это имя в теле функции. Для других языков это имя служит просто дополнительным описанием, если говорить о самой функции; однако вы можете указывать имена аргументов при вызове функции для улучшения читаемости (см. Раздел 4.3). Имя выходного аргумента в любом случае имеет значение, так как оно определяет имя столбца в типе результата. (Если вы опустите имя выходного аргумента, система выберет для него имя по умолчанию.) тип_аргумента

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

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

Ссылка на тип столбца записывается в виде имя_таблицы . имя_столбца %TYPE . Иногда такое указание бывает полезно, так как позволяет создать функцию, независящую от изменений в определении таблицы. выражение_по_умолчанию

Выражение, используемое для вычисления значения по умолчанию, если параметр не задан явно. Результат выражения должен сводиться к типу соответствующего параметра. Значения по умолчанию могут иметь только входные параметры (включая INOUT). Для всех входных параметров, следующих за параметром с определённым значением по умолчанию, также должны быть определены значения по умолчанию. тип_результата

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

В случае наличия параметров OUT или INOUT , предложение RETURNS можно опустить. Если оно присутствует, оно должно согласовываться с типом результата, выводимым из выходных параметров: в качестве возвращаемого типа указывается RECORD , если выходных параметров несколько, либо тип единственного выходного параметра.

Указание SETOF показывает, что функция возвращает множество, а не единственный элемент.

Имя выходного столбца в записи RETURNS TABLE . По сути это ещё один способ объявить именованный выходной параметр (OUT), но RETURNS TABLE также подразумевает и RETURNS SETOF . тип_столбца

Тип данных выходного столбца в записи RETURNS TABLE . имя_языка

Имя языка, на котором реализована функция. Это может быть sql , c , internal , либо имя процедурного языка, определённого пользователем, например, plpgsql . Стиль написания этого имени в апострофах считается устаревшим и требует точного совпадения регистра. TRANSFORM { FOR TYPE имя_типа } [, ... ] }

Устанавливает список трансформаций, которые должны применяться при вызове функции. Трансформации выполняют преобразования между типами SQL и типами данных, специфичными для языков; см. CREATE TRANSFORM . Преобразования встроенных типов обычно жёстко предопределены в реализациях процедурных языков, так что их здесь указывать не нужно. Если реализация процедурного языка не может обработать тип и трансформация для него отсутствует, будет выполнено преобразование типов по умолчанию, но это зависит от реализации. WINDOW

Указание WINDOW показывает, что создаётся не простая, а оконная функция . В настоящее время это имеет смысл только для функций, написанных на C. Атрибут WINDOW нельзя изменить, модифицируя впоследствии определение функции. IMMUTABLE
STABLE
VOLATILE

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

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

Характеристика STABLE (стабильная) показывает, что функция не может модифицировать базу данных и в рамках одного сканирования таблицы она всегда возвращает один и тот же результат для определённых значений аргументов, но этот результат может быть разным в разных операторах SQL. Это подходящий выбор для функций, результаты которых зависят от содержимого базы данных и настраиваемых параметров (например, текущего часового пояса). (Но этот вариант не подходит для триггеров AFTER , желающих прочитать строки, изменённые текущей командой.) Также заметьте, что функции семейства current_timestamp также считаются стабильными, так как их результаты не меняются внутри транзакции.

Характеристика VOLATILE (изменчивая) показывает, что результат функции может меняться даже в рамках одного сканирования таблицы, так что её вызовы нельзя оптимизировать. Изменчивы в этом смысле относительно немногие функции баз данных, например: random() , currval() и timeofday() . Но заметьте, что любая функция с побочными эффектами должна быть классифицирована как изменчивая, даже если её результат вполне предсказуем, чтобы её вызовы не были соптимизированы; пример такой функции: setval() .

За дополнительными подробностями обратитесь к Разделу 35.6 . LEAKPROOF

Характеристика LEAKPROOF (герметичная) показывает, что функция не имеет побочных эффектов. Она не раскрывает информацию о своих аргументах, кроме как возвращая результат. Например, функция, которая выдаёт сообщение об ошибке с некоторыми, но не всеми значениями аргументов, либо выводит значения аргументов в сообщении об ошибке, не является герметичной. Это влияет на то, как система выполняет запросы к представлениям, созданным с барьером безопасности (с указанием security_barrier), или к таблицам с включённой защитой строк. Во избежание неконтролируемой утечки данных система будет проверять условия из политик защиты и определений представлений с барьерами безопасности перед любыми условиями, которые задаёт пользователь в самом запросе и в которых задействуются негерметичные функции. Функции и операторы, помеченные как герметичные, считаются доверенными и могут выполняться перед условиями из политик защиты и представлений с барьерами безопасности. При этом функции, которые не имеют аргументов или которым не передаются никакие аргументы из представления с барьером безопасности или таблицы, не требуется помечать как герметичные, чтобы они выполнялись до условий, связанных с безопасностью. См. CREATE VIEW и Раздел 38.5 . Это свойство может установить только суперпользователь. CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT

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

Указание RETURNS NULL ON NULL INPUT или STRICT показывает, что функция всегда возвращает NULL, получив NULL в одном из аргументов. Такая функция не будет вызываться с аргументами NULL, вместо этого автоматически будет полагаться результат NULL. [EXTERNAL ] SECURITY INVOKER
[EXTERNAL ] SECURITY DEFINER

Характеристика SECURITY INVOKER (безопасность вызывающего) показывает, что функция будет выполняться с правами пользователя, вызвавшего её. Этот вариант подразумевается по умолчанию. Вариант SECURITY DEFINER (безопасность определившего) определяет, что функция выполняется с правами пользователя, создавшего её.

Ключевое слово EXTERNAL (внешняя) допускается для соответствия стандарту SQL, но является необязательным, так как, в отличие от SQL, эта характеристика распространяется на все функции, а не только внешние. PARALLEL

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

Функции должны помечаться как небезопасные для параллельного выполнения, если они изменяют состояние базы данных, вносят изменения в транзакции, например, используя подтранзакции, обращаются к последовательностям или пытаются сохранять параметры (например, используя setval). Ограниченно параллельными должны помечаться функции, которые обращаются к временным таблицам, состоянию клиентского подключения, курсорам, подготовленным операторам или разнообразному состоянию обслуживающего процесса, которое система не может синхронизировать в параллельном режиме (например, setseed может выполнять только ведущий процесс группы, так как изменения, внесённые другим процессом, не передаются ведущему). Вообще, если функция помечена как безопасная, тогда как она является ограниченной или небезопасной, либо если она помечена как ограниченно безопасная, не являясь безопасной, при попытке вызвать её в параллельном запросе она может выдавать ошибки или неверные результаты. Функции на языке C при неправильной пометке теоретически могут проявлять полностью неопределённое поведение, так как система никак не может защититься от произвольного кода на C, но чаще все они будут вести себя не хуже, чем любая другая функция. В случае сомнений функцию следует помечать как небезопасную (UNSAFE), что и имеет место по умолчанию. стоимость_выполнения

Положительное число, задающее примерную стоимость выполнения функции, в единицах cpu_operator_cost . Если функция возвращает множество, это число задаёт стоимость для одной строки. Если стоимость не указана, для функций на C и внутренних функций она считается равной 1 единице, а для функций на всех других языках - 100 единицам. При больших значениях планировщик будет стараться не вызывать эту функцию чаще, чем это необходимо. строк_в_результате

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

Предложение SET определяет, что при вызове функции указанный параметр конфигурации должен принять заданное значение, а затем восстановить своё предыдущее значение при завершении функции. Предложение SET FROM CURRENT сохраняет в качестве значения, которое будет применено при входе в функцию, значение, действующее в момент выполнения CREATE FUNCTION .

Если в определение функции добавлено SET , то действие команды SET LOCAL , выполняемой внутри функции для того же параметра, ограничивается телом функции: предыдущее значение параметра так же будет восстановлено при завершении функции. Однако обычная команда SET (без LOCAL) переопределяет предложение SET , как и предыдущую команду SET LOCAL: действие такой команды будет сохранено и после завершения функции, если только не произойдёт откат транзакции.

За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 18 . определение

Строковая константа, определяющая реализацию функции; её значение зависит от языка. Это может быть имя внутренней функции, путь к объектному файлу, команда SQL или код функции на процедурном языке.

Часто бывает полезно заключать определение функции в доллары (см. Подраздел 4.1.2.4), а не в традиционные апострофы. Если не использовать доллары, все апострофы и обратные косые черты в определении функции придётся экранировать, дублируя их. объектный_файл , объектный_символ

Эта форма предложения AS применяется для динамически загружаемых функций на языке C, когда имя функции в коде C не совпадает с именем функции в SQL. Строка объектный_файл задаёт имя файла, содержащего динамически загружаемый объект, а объектный_символ - символ скомпонованной функций, то есть имя функции в исходном коде на языке C. Если объектный символ опущен, предполагается, что он совпадает с именем определяемой SQL-функции. В C имена всех функций должны быть различными, поэтому перегружаемым функциям, реализованным на C, нужно давать разные имена (например, включать в имена C обозначения типов аргументов).

Если повторные вызовы CREATE FUNCTION ссылаются на один и тот же объектный файл, он загружается в рамках сеанса только один раз. Чтобы выгрузить и загрузить этот файл снова (например, в процессе разработки), начните новый сеанс. атрибут

Исторически сложившийся способ передавать дополнительную информацию о функции. В этой записи могут присутствовать следующие атрибуты:

IsStrict

Равнозначно указанию STRICT или RETURNS NULL ON NULL INPUT . isCachable

Свойство isCachable - устаревший эквивалент IMMUTABLE ; оно всё ещё поддерживается ради обратной совместимости.

Имена атрибутов являются регистронезависимыми.

За дополнительной информацией о разработке функций обратитесь к Разделу 35.3 .

Перегрузка

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

Две функции считаются совпадающими, если они имеют одинаковые имена и типы входных аргументов, параметры OUT игнорируются. Таким образом, например, эти объявления вызовут конфликт:

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...

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

CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ...

Вызов foo(10) завершится ошибкой из-за неоднозначности в выборе вызываемой функции.

Замечания

В объявлении аргументов функции и возвращаемого значения допускается полный синтаксис описания типа SQL . Однако модификаторы типа в скобках (например, поле точности для типа numeric) команда CREATE FUNCTION не учитывает. Так что, например, CREATE FUNCTION foo (varchar(10)) ... создаст такую же функцию, что и CREATE FUNCTION foo (varchar) ... .

При замене существующей функции с помощью CREATE OR REPLACE FUNCTION есть ограничения на изменения имён параметров. В частности, нельзя изменить имя, уже назначенное любому входному параметру (хотя можно добавить имена ранее безымянным параметрам). Также, если у функции более одного выходного параметра, нельзя изменять имена выходных параметров, так как это приведёт к изменению имён столбцов анонимного составного типа, описывающего результат функции. Эти ограничения позволяют гарантировать, что существующие вызовы функции не перестанут работать после её замены.

Если функция объявлена как STRICT с аргументом VARIADIC , при оценивании строгости проверяется, что весь переменный массив в целом не NULL. Если же в этом массиве содержатся элементы NULL, функция будет вызываться.

Примеры

Ниже приведено несколько простых вводных примеров. За дополнительными сведениями и примерами обратитесь к Разделу 35.3 .

CREATE FUNCTION add(integer, integer) RETURNS integer AS "select $1 + $2;" LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

Функция увеличения целого числа на 1, использующая именованный аргумент, на языке PL/pgSQL :

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END; $$ LANGUAGE plpgsql;

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

CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

То же самое можно сделать более развёрнуто, явно объявив составной тип:

CREATE TYPE dup_result AS (f1 int, f2 text); CREATE FUNCTION dup(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

Ещё один способ вернуть несколько столбцов - применить функцию TABLE:

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$ SELECT $1, CAST($1 AS text) || " is text" $$ LANGUAGE SQL; SELECT * FROM dup(42);

Однако пример с TABLE отличается от предыдущих, так как в нём функция на самом деле возвращает не одну, а набор записей.

Разработка защищённых функций SECURITY DEFINER

Так как функция SECURITY DEFINER выполняется с правами пользователя, создавшего её, необходимо позаботиться о том, чтобы её нельзя было использовать не по назначению. В целях безопасности, в пути search_path следует исключить любые схемы, доступные на запись недоверенным пользователям. Это не позволит злонамеренным пользователям создать свои объекты (например, таблицы, функции и операторы), которые замаскируют объекты, используемые функцией. Особенно важно в этом отношении исключить схему временных таблиц, которая по умолчанию просматривается первой, а право записи в неё по умолчанию имеют все. Соответствующую защиту можно организовать, поместив временную схему в конец списка поиска. Для этого следует сделать pg_temp последней записью в search_path . Безопасное использование демонстрирует следующая функция:

CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN; BEGIN SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; RETURN passed; END; $$ LANGUAGE plpgsql SECURITY DEFINER -- Установить безопасный путь поиска: сначала доверенная схема(ы), затем "pg_temp". SET search_path = admin, pg_temp;

Эта функция должна обращаться к таблице admin.pwds , но без предложения SET или с предложением SET , включающим только admin , её можно «обмануть», создав временную таблицу pwds .

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

Также следует помнить о том, что по умолчанию право выполнения для создаваемых функций имеет роль PUBLIC (за подробностями обратитесь к GRANT ). Однако часто требуется разрешить доступ к функциям, работающим в контексте определившего, только некоторым пользователям. Для этого необходимо отозвать стандартные права PUBLIC и затем дать права на выполнение индивидуально. Чтобы не образовалось окно, в котором новая функция будет недоступна совсем, создайте её и назначьте права в одной транзакции. Например, так:

BEGIN; CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER; REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC; GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins; COMMIT;

Совместимость

Команда CREATE FUNCTION определена в SQL:1999 и более поздних стандартах. Версия, реализованная в Postgres Pro , близка к стандартизированной, но соответствует ей не полностью. В частности, непереносимы атрибуты, а также различные языки реализаций.

Для совместимости с другими СУБД режим_аргумента можно записать после имя_аргумента или перед ним, но стандарту соответствует только первый вариант.

Для определения значений по умолчанию для параметров стандарт SQL поддерживает только синтаксис с ключевым словом DEFAULT . Синтаксис со знаком = используется в T-SQL и Firebird.

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

Функции Scalar

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

<определение_скаляр_функции>::={CREATE | ALTER } FUNCTION [владелец.] имя_функции([ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS скаляр_тип_данных ]BEGIN<тело_функции>RETURN скаляр_выражениеEND

Рассмотрим назначение параметров команды.

Функция может содержать один или несколько входных параметров либо не содержать ни одного. Каждый параметр должен иметь уникальное в пределах создаваемой функции имя и начинаться с символа " @ ". После имени указывается тип данных параметра. Дополнительно можно указать значение, которое будет автоматически присваиваться параметру (DEFAULT), если пользователь явно не указал значение соответствующего параметра при вызове функции .

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

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

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

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

Пример Создать и применить функцию скалярного типа для вычисления суммарного количества товара, поступившего за определенную дату. Владелец функции – пользователь с именем user1.

CREATE FUNCTION user1.sales(@data DATETIME)RETURNS INTASBEGINDECLARE @c INTSET @c=(SELECT SUM(количество) FROM Сделка WHERE дата=@data)RETURN (@c)END

Функции Inline

Создание и изменение функции этого типа выполняется с помощью команды:

<определение_табл_функции>::={CREATE | ALTER } FUNCTION [владелец.] имя_функции([ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS TABLE[ WITH {ENCRYPTION | SCHEMABINDING} [,...n] ]RETURN [(] SELECT_оператор [)]

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

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

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

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

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

CREATE FUNCTION user1.itog()RETURNS TABLEASRETURN (SELECT TOP 2 Товар.Название FROM Товар INNER JOIN Склад ON Товар.КодТовара=Склад.КодТовара ORDER BY Склад.Остаток DESC)

Использовать функцию для получения двух наименований товара с наибольшим остатком можно следующим образом:

SELECT НазваниеFROM user1.itog()

Функции Multi-statement

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

<определение_мульти_функции>::={CREATE | ALTER }FUNCTION [владелец.] имя_функции([ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS @имя_параметра TABLE <определение_таблицы> ]BEGIN<тело_функции>RETURN END

Использование большей части параметров рассматривалось при описании предыдущих функций .

Отметим, что функции данного типа, как и табличные , возвращают значение типа TABLE . Однако, в отличие от табличных функций , при созданиифункций Multi-statement необходимо явно задать структуру возвращаемого значения. Она указывается непосредственно после ключевого слова TABLEи, таким образом, является частью определения возвращаемого типа данных. Синтаксис конструкции <определение_таблицы> полностью соответствует одноименным структурам, используемым при создании обычных таблиц с помощью команды CREATE TABLE.

Набор возвращаемых данных должен формироваться с помощью команд INSERT, выполняемых в теле функции . Кроме того, в теле функции допускается использование различных конструкций языка SQL, которые могут контролировать значения, размещаемые в выходном наборе строк. При работе с командой INSERT требуется явно указать имя того объекта, куда необходимо вставить строки. Поэтому в функциях типа Multi-statement , в отличие оттабличных , необходимо присвоить какое-то имя объекту с типом данных TABLE – оно и указывается как возвращаемое значение.

Завершение работы функции происходит в двух случаях: если возникают ошибки выполнения и если появляется ключевое слово RETURN. В отличие отфункций скалярного типа , при использовании команды RETURN не нужно указывать возвращаемое значение. Сервер автоматически возвратит набор данных типа TABLE , имя и структура которого была указана после ключевого слова RETURNS. В теле функции может быть указано более одной командыRETURN.

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