Что такое функции sql

Функции Transact-SQL

Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.

Агрегатные функции

Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:

AVG

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

MIN и MAX

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

SUM

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

COUNT

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

COUNT_BIG

Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.

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

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

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

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

Числовые функции

Числовые функции языка Transact-SQL — это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:

Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n.

Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT.

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

Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот.

Округляет число до большего целого значения.

Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное — целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону).

Округляет до меньшего целого значения.

Вычисляет значение e n .

LOG(n) — вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) — вычисляет десятичный (с основанием 10) логарифм числа n.

Возвращает значение π (3,1415).

Вычисляет значение x y .

Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1.

Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT.

Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное.

SQRT(n) — вычисляет квадратный корень числа n, SQUARE(n) — возвращает квадрат аргумента n.

Функции даты

Функции даты вычисляют соответствующие части даты или времени выражения или возвращают значение временного интервала. Поддерживаемые в Transact-SQL функции даты и их краткое описание приводятся в таблице ниже:

Возвращает текущую системную дату и время.

Возвращает указанную в параметре item часть даты date в виде целого числа.

Возвращает указанную в параметре item часть даты date в виде строки символов.

Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item.

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

Строковые функции

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

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

Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ.

Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0

Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII.

Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT.

Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы.

Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные.

Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки.

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

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

Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3.

Повторяет i раз строку str.

Выводит строку str в обратном порядке.

Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII.

Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(‘ ‘, length).

Преобразовывает заданное выражение с плавающей точкой f в строку, где len — длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d — число разрядов дробной части, которые нужно возвратить.

Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2.

Извлекает из строки str, начиная с позиции a, подстроку длиной length.

Системные функции

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

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

Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением.

Возвращает первое значение выражения из списка выражений a1, a2, . которое не является значением null.

Возвращает длину столбца col объекта базы данных (таблицы или представления) obj.

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

Возвращает текущие дату и время.

Возвращает имя текущего пользователя.

Возвращает число байтов, которые занимает выражение z.

Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL.

Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value.

Определяет, имеет ли выражение expr действительный числовой тип.

Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER.

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

Возвращает значение null, если значения выражений expr1 и expr2 одинаковые.

Возвращает информацию о свойствах сервера базы данных.

Возвращает ID текущего пользователя.

Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя.

Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя.

Функции метаданных

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

Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id.

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

Возвращает значение свойства property базы данных database.

Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных.

Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных.

Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе.

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

Особенности формирования и использования MS SQL функций

В SQL (Structured Query Language) используется огромное множество функций. Статья посвящена особенностям их создания и использования. Изучайте материал или освежайте свои знания. Правильное применение алгоритмов их создания и применения поможет стать первоклассным высокооплачиваемым специалистом, востребованным на рынке труда.

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

Функции встроенные

В SQL встроено огромное множество функций. К ним относятся:

• строковые – для работы со строками;
• числовые – для работы с числами;
• даты – для работы с датами и временем;
• расширенные.

Ранжирующие

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

Такие функции относятся к недетерминированным.

Агрегатные

Функции, которые производят вычислительные действия на наборах значений и возвращающие одиночные показатели, называются агрегатными. В них не учитываются показатели NULL, за исключением лишь COUNT(*). Довольно часто их применяют в выражении GROUP BY внутри инструкции SELECT.

Они могут использоваться в:

• команде HAVING в SELECT;
• списках выбора.

Также они могут сочетаться с предложениями:

• GROUP BY – для осуществления статистических расчетов, производимых на основании категорий строк;
• OVER, чтобы на основании заданной амплитуды значений найти статистическое. Оно не может использоваться вслед за функциями GROUPING, GROUPING_ID, STRING_AGG.

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

Аналитические

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

Они используются для определения:

• скользящих средних;
• доли в процентах;
• промежуточных результатов;
• первых N результатов в группах.

NEXT VALUE FOR (T-SQL)

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

NEXT VALUE FOR относится к недетерминированным функциям и допускается лишь при правильном определении номера из последовательности, которая формируется.

Наборы строк

Они отвечают за возврат объектов, которые могут быть применены таким же образом, как в SQL используются ссылки табличные.

Встроенные скалярные

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

Они делятся на следующие категории функций:

• конфигурации – отвечают за возврат данных о действующих конфигурациях;
• преобразования – поддерживают приведение и преобразовывают данные разных типов;
• работы с курсорами – отвечают за возврат информации о курсорах;
• ф-ций и типов данных даты и времени – производят операции над исходящими значениями дата/ время, отвечают за возврат значений строк и чисел, вместе с датой и временем;
• JSON – работают с данными JSON (выполняют запросы, проверку, вносят изменения);
• логические – осуществляют логические операции;
• математические – производят вычисления, которые основаны на числах, что были переданы в качестве аргументов и возвращают значения в числах;
• метаданных – отвечают за возврат данных как о самих базах данных (БД), так и о принадлежащих им объектах;
• безопасности – возвращают информацию о пользователях и какими ролями они наделены;
• строковые – выполняют операции со строковыми входными значениями (char либо varchar) и возвращают значения (в виде строк или чисел);
• системные – выполняют операции над разными объектами, значениями, параметрами экземпляров SQL Server и возвращают информацию о них;
• системные статистические – возвращают статистические данные о системе;
• обработки текстов и изображений – выполняют различные операции над текстовыми, графическими значениями, столбцами и возвращают данные о них.

Оконные

В начале данной функции всегда находится оператор OVER. Настройка осуществляется при участии инструкций PARTITION BY, ORDER BY и ROWS. Их реализация стала возможна начиная со SQL Server 2005.

Преимущества их использования:

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

Инструкция SELECT – предложение OVER (T-SQL)

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

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

Детерминированные и недетерминированные

Все встроенные функции бывают:

• детерминированные – для входящих значений они постоянно возвращают один и тот же результат;
• недетерминированными – для одних и тех же исходящих значений возвращают разные результаты.

Параметры сортировки

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

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

В каждом случае функции возвращают одно значение, чем и отличаются от хранимых процедур. Особый интерес вызывают UDF. Это функции, которые пользователь определяет самостоятельно (UDF – User Defined Functions). В Microsoft SQL предоставленные системой встроенные функции можно расширять, создавая самостоятельно пользовательские – UDF.

Они получают параметры (от 0 и более) и возвращают скалярное значение либо таблицу. Для входящих параметров может использоваться любой тип данных. Исключение составляют timestamp, cursor, table.

Различают несколько типов UDF:

• скалярные – аналогичны со скалярными встроенными;
• возвращающие табличное значение – возвращают итог инструкции SELECT;
• мультиоператорные – возвращают T-SQL таблицу, которая создана оператором (одним либо несколькими). Это схоже с хранимыми процедурами, однако отличаются от них тем, что в WHERE можно ссылаться на такие UDF, как на просматриваемый объект.

Создание и выполнение

Процесс формирования UDF очень схож с тем, как создаются объекты просмотра и процедур. Они формируются при помощи инструкции CREATE FUNCTION.

UDF может быть использована только пользователями, наделенными ролями sysadmin, db_owner, db_ddladmin. Однако пользователи с перечисленными ролями могут присваивать аналогичные права иным пользователям, воспользовавшись инструкцией GRANT CREATE FUNCTION.

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

Инструкция CREATE FUNCTION

Как сказано выше, с помощью CREATE FUNCTION создаются UDF, которые представляют собой подпрограммы среды CLR или T-SQL. CREATE FUNCTION позволяет создавать именно такие подпрограммы.

В дальнейшем подпрограммы, созданные данной инструкцией, могут применяться:

• инструкциями Transact-SQL (к примеру, SELECT);
• приложениями, участвующими в вызове функций;
• при определении иной UDF;
• чтобы определить параметры представления либо улучшить функциональные возможности индексированных представлений;
• при определении столбцов таблиц;
• чтобы определить ограничения на столбец CHECK;
• чтобы заменить хранимые процедуры;
• для политики безопасности.

Скалярные в T-SQL

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

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

Код выполняемой функции должен быть вписан внутри блока ключевых слов BEGIN/ END. В середине кода могут указываться любые операторы T-SQL. В этот блок могут быть включены также инструкции: присвоения (к примеру, SET), управления процессом выполнения (WHILE и IF), DECLARE, SELECT.

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

UDF могут быть вызваны инструкциями T-SQL: SELECT, UPDATE, INSERT, DELETE.

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

Если при вызове функции для параметров не указаны значения по умолчанию, то для них должны быть предоставлены аргументы, определенные в том же порядке, как и в CREATE FUNCTION. Наименования в инструкциях T-SQL должны задаваться с помощью имен, состоящих из пары слов: function name, schema name и т.д.

Возвращающие таблицу

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

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

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

• inline – встраиваемые, где рядом с TABLE внутри предложения RETURNS отсутствует сопровождающий список столбцов. Оператором SELECT будет возвращен результирующий набор в виде значения с типом данных TABLE;
• multistatement – многоинструкционные, они включают наименование переменной и ключевое слово TABLE, определяющее тип данных. Указанные строки будут вставлены в переменную, которая выступает возвращаемым значением самой функции.

При написании запросов, во всех его полях необходимо указывать имя. Отсутствие имени хотя бы в одном поле спровоцирует возврат ошибки, когда будет выполняться инструкция CREATE FUNCTION.

Инструкция APPLY

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

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

Инструкция APPLY имеет две формы:

• CROSS APPLY – возвращает строки из внутреннего выражения таблицы (левого), совпадающие с внешним выражением таблицы (правым). Данная форма логически работает аналогично инструкции INNER JOIN;
• OUTER APPLY – возвращает все строки из внутреннего выражения таблицы (левого). В том случае, когда строчки не имеют совпадений во внешнем выражении таблицы, тогда в столбцах внешнего выражения таблицы они содержат NULL. Данная форма логически работает аналогично инструкции LEFT OUTER JOIN.

Опции

В процессе создания UDF можно использовать одну или обе опции:

• SCHEMABINDING – привязки к схеме. Объекты БД, на которые ссылается функция, созданная с данной опцией, не могут изменяться (с помощью ALTER) или удаляться (оператором DROP);
• ENCRYPTION – шифрования текста функции внутри системных таблиц.

Допускается, что UDF может быть связана со схемой, только при выполнении условий:

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

Параметры, возвращающие таблицу

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

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

Применение таких параметров имеет ряд преимуществ:

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

Изменение и удаление UDF

Инструкция ALTER FUNCTION, поддерживаемая языком T-SQL, изменяет структуру UDF. Ее обычно применяют, чтобы удалить привязку к схеме. Параметры данной инструкции имеют значения, аналогичные одноименным параметрам инструкции CREATE FUNCTION. Для каждого вида UDF используется индивидуальный вид команд изменения.

Чтобы удалить UDF, необходимо использовать инструкцию DROP FUNCTION. Сделать это могут владельцы либо пользователи, наделенные ролями sysadmin или db_owner.

Как становится понятно, для грамотного оперирования функциями в MS SQL Server, необходимо хорошо изучить не только встроенные, но и понять алгоритмы создания пользовательских. Потому, на изучение данного материала нужно потратить достаточно времени, чтобы разобраться во всех деталях. Будем рады, если статья поможет в этом. При возникновении вопросов – обязательно задавайте их. Удачи в изучении основ MS SQL функций и алгоритмов работы с ними!

Что такое функции sql

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

Кроме того, можно объявить SQL-функцию как возвращающую множество (то есть, несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип , либо объявив её с указанием RETURNS TABLE( столбцы ) . В этом случае будут возвращены все строки результата последнего запроса. Подробнее это описывается ниже.

Тело SQL-функции должно представлять собой список SQL-операторов, разделённых точкой с запятой. Точка с запятой после последнего оператора может отсутствовать. Если только функция не объявлена как возвращающая void , последним оператором должен быть SELECT , либо INSERT , UPDATE или DELETE с предложением RETURNING .

Примечание

Прежде чем начинается выполнение команд, разбирается всё тело SQL-функции. Когда SQL-функция содержит команды, модифицирующие системные каталоги (например, CREATE TABLE ), действие таких команд не будет проявляться на стадии анализа последующих команд этой функции. Так, например, команды CREATE TABLE foo (. ); INSERT INTO foo VALUES(. ); не будут работать, как ожидается, если их упаковать в одну SQL-функцию, так как foo не будет существовать к моменту разбору команды INSERT . В подобных ситуациях вместо SQL-функции рекомендуется использовать PL/pgSQL .

Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно для этого удобнее всего заключать строковую константу в доллары (см. Подраздел 4.1.2.4). Если вы решите использовать обычный синтаксис с заключением строки в апострофы, вам придётся дублировать апострофы ( ‘ ) и обратную косую черту ( \ ) (предполагается синтаксис спецпоследовательностей) в теле функции (см. Подраздел 4.1.2.1).

35.5.1. Аргументы SQL -функций

К аргументам SQL-функции можно обращаться в теле функции по именам или номерам. Ниже приведены примеры обоих вариантов.

Чтобы использовать имя, объявите аргумент функции как именованный, а затем просто пишите это имя в теле функции. Если имя аргумента совпадает с именем какого-либо столбца в текущей SQL-команде внутри функции, имя столбца будет иметь приоритет. Чтобы всё же перекрыть имя столбца, дополните имя аргумента именем самой функции, то есть запишите его в виде имя_функции . имя_аргумента . (Если и это имя будет конфликтовать с полным именем столбца, снова выиграет имя столбца. Неоднозначности в этом случае вы можете избежать, выбрав другой псевдоним для таблицы в SQL-команде.)

Старый подход с нумерацией позволяет обращаться к аргументам, применяя запись $ n : $1 обозначает первый аргумент, $2 — второй и т. д. Это будет работать и в том случае, если данному аргументу назначено имя.

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

Аргументы SQL-функции могут использоваться только как значения данных, но не как идентификаторы. Например, это приемлемо:

а это не будет работать:

Примечание

Возможность обращаться к аргументам SQL-функций по именам появилась в PostgreSQL 9.2. В функциях, которые должны работать со старыми серверами, необходимо применять запись $ n .

35.5.2. Функции SQL с базовыми типами

Заметьте, что мы определили псевдоним столбца в теле функции для её результата (дали ему имя result ), но этот псевдоним не виден снаружи функции. Вследствие этого, столбец результата получил имя one , а не result .

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

Мы также можем отказаться от имён аргументов и обращаться к ним по номерам:

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

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

В этом примере мы выбрали имя accountno для первого аргумента, но это же имя имеет столбец в таблице bank . В команде UPDATE имя accountno относится к столбцу bank.accountno , так для обращения к аргументу нужно записать tf1.accountno . Конечно, мы могли бы избежать этого, выбрав другое имя для аргумента.

На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:

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

несмотря на то, что в других контекстах Postgres Pro выполнил бы неявное приведение для преобразования integer в float8 . Правильный вариант выглядит так:

35.5.3. Функции SQL со сложными типами

В функциях с аргументами составных типов мы должны указывать не только, какой аргумент, но и какой атрибут (поле) этого аргумента нам нужен. Например, предположим, что emp — таблица, содержащая данные работников, и это же имя составного типа, представляющего каждую строку таблицы. Следующая функция double_salary вычисляет, каким было бы чьё-либо жалование в случае увеличения вдвое:

Обратите внимание на запись $1.salary позволяющую выбрать одно поле из значения строки аргумента. Также заметьте, что в вызывающей команде SELECT указание имя_таблицы .* выбирает всю текущую строку таблицы как составное значение. На строку таблицы можно сослаться и просто по имени таблицы, например так:

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

Иногда бывает удобно образовать составное значение аргумента на лету. Это позволяет сделать конструкция ROW . Например, так можно изменить данные, передаваемые функции:

Также возможно создать функцию, возвращающую составной тип. Например, эта функция возвращает одну строку emp :

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

Учтите два важных требования относительно определения функции:

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

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

Ту же функцию можно определить другим способом:

Здесь мы записали SELECT , который возвращает один столбец нужного составного типа. В данной ситуации этот вариант на самом деле не лучше, но в некоторых случаях он может быть удобной альтернативой — например, если нам нужно вычислить результат, вызывая другую функцию, которая возвращает нужное составное значение. Этот вариант полезен и в случае, когда мы хотим написать функцию, которая возвращает не обычный составной тип, а домен, определённый поверх составного типа; тогда она в любом случае должна определяться как возвращающая единственный столбец, так как никаким другим образом получить значение именно доменного типа нельзя.

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

либо обратившись к ней, как к табличной функции:

Второй способ более подробно описан в Подразделе 35.5.7.

Когда используется функция, возвращающая составной тип, может возникнуть желание получить из её результата только одно поле (атрибут). Это можно сделать, применяя такую запись:

Дополнительные скобки необходимы во избежание неоднозначности при разборе запроса. Если вы попытаетесь выполнить запрос без них, вы получите ошибку:

(ОШИБКА: синтаксическая ошибка (примерное положение: «.»))

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

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

Ещё один вариант использования функции, возвращающей составной тип, заключается в передаче её результата другой функции, которая принимает этот тип строки на вход:

35.5.4. Функции SQL с выходными параметрами

Альтернативный способ описать результаты функции — определить её с выходными параметрами, как в этом примере:

Это по сути не отличается от версии add_em , показанной в Подразделе 35.5.2. Действительная ценность выходных параметров в том, что они позволяют удобным способом определить функции, возвращающие несколько столбцов. Например:

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

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

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

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

35.5.5. Функции SQL с переменным числом аргументов

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

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

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

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

Также указание VARIADIC даёт единственную возможность передать пустой массив функции с переменными параметрами, например, так:

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

Элементы массива, создаваемые из переменных параметров, считаются не имеющими собственных имён. Это означает, что передать функции с переменными параметрами именованные аргументы нельзя (см. Раздел 4.3), если только при вызове не добавлено VARIADIC . Например, этот вариант будет работать:

А эти варианты нет:

35.5.6. Функции SQL со значениями аргументов по умолчанию

Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются, когда функция вызывается с недостаточным количеством фактических аргументов. Так как аргументы можно опускать только с конца списка фактических аргументов, все параметры после параметра со значением по умолчанию также получат значения по умолчанию. (Хотя запись с именованными аргументами могла бы ослабить это ограничение, оно всё же остаётся в силе, чтобы позиционные ссылки на аргументы оставались действительными.) Независимо от того, используете вы эту возможность или нет, она требует осторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3.

(ОШИБКА: функция foo() не существует) Вместо ключевого слова DEFAULT можно использовать знак = .

35.5.7. Функции SQL , порождающие таблицы

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

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

Заметьте, что мы получаем из данной функции только одну строку. Это объясняется тем, что мы не использовали указание SETOF . Оно описывается в следующем разделе.

35.5.8. Функции SQL , возвращающие множества

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

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

Тогда в ответ мы получим:

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

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

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

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

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

Заметьте, что в последней команде SELECT для Child2 , Child3 и т. д. строки не выдаются. Это происходит потому, что listchildren возвращает пустое множество для этих аргументов, так что строки результата не генерируются. Это же поведение мы получаем при внутреннем соединении с результатом функции с применением LATERAL .

Поведение Postgres Pro с функциями, возвращающими множества, в списке выборки запроса практически не отличается от поведения с такими функциями, помещёнными в предложение LATERAL FROM . Например, запрос:

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

Если в списке выборки запроса используются несколько функций, возвращающих запросы, они вычисляются примерно так же, как если бы они были помещены в один элемент LATERAL ROWS FROM( . ) предложения FROM . Для каждой строки из нижележащего запроса выдаётся строка с первым результатом каждой функции, а затем строка со вторым результатом и так далее. Если какие-либо из функций, возвращающих множества, выдают меньше результатов, чем другие, то вместо недостающих данных подставляются значения NULL, так что общее число строк, выдаваемых для одной нижележащей строки, равно числу строк, которое выдаёт функция с наибольшим количеством строк в возвращаемом множестве. Таким образом, функции, возвращающие множества, выполняются совместно, пока все их множества не будут исчерпаны, а затем выполнение продолжается со следующей нижележащей строкой.

Функции, возвращающие множества, могут быть вложенными в списке выборки, но это не допускается в элементах предложения FROM . В таких случаях каждый уровень вложенности обрабатывается отдельно, как если бы это был отдельный элемент LATERAL ROWS FROM( . ) . Например, в

возвращающие множества функции srf2 , srf3 и srf5 будут выполняться совместно для каждой строки tab , а затем srf1 и srf4 будут совместно применяться к каждой строке, произведённой нижними функциями.

Функции, возвращающие множества, нельзя использовать в конструкциях, вычисляемых по условию, например, CASE или COALESCE . Например, рассмотрите запрос

Может показаться, что он должен выдать пять экземпляров входных строк, в которых x > 0 , и по одному экземпляру остальных строк; но на деле, так как generate_series(1, 5) будет выполняться в неявном элементе LATERAL FROM до того, как выражение CASE вообще будет рассматриваться, должно было бы выдаваться пять экземпляров абсолютно всех выходных строк. Во избежание путаницы в таких случаях выдаётся ошибка при разборе запроса.

Примечание

Если последняя команда функции — INSERT , UPDATE или DELETE с RETURNING , эта команда будет всегда выполняться до завершения, даже если функция не объявлена с указанием SETOF или вызывающий запрос не выбирает все строки результата. Все дополнительные строки, выданные предложением RETURNING , просто игнорируются, но соответствующие изменения в таблице всё равно произойдут (и будут завершены до выхода из функции).

Примечание

В Postgres Pro до версии 10 при помещении нескольких функций, возвращающих множества, в один список выборки поведение было не очень разумным, если они возвращали не одинаковое число строк. В таких случаях число выходных строк равнялось наименьшему общему множителю количеств строк, возвращаемых этими функциями. Также и вложенные функции, возвращающие множества, работали не так, как описано выше; у такой функции мог быть максимум один аргумент, возвращающий множество, и каждая вложенность вычислялась независимо. Кроме того, ранее допускалось и условное выполнение (вычисление таких функций внутри CASE и т. п.), что ещё больше всё усложняло. При написании запросов, которые должны работать и со старыми версиями Postgres Pro , рекомендуется использовать синтаксис LATERAL , так как это гарантирует одинаковый результат с разными версиями. Если в вашем запросе используется условное вычисление функции, возвращающей множество, его можно исправить, переместив проверку условия в специально созданную функцию, возвращающую множество. Например:

можно заменить на

Это будет работать одинаково во всех версиях Postgres Pro .

35.5.9. Функции SQL , возвращающие таблицы ( TABLE )

Есть ещё один способ объявить функцию, возвращающую множества, — использовать синтаксис RETURNS TABLE( столбцы ) . Это равнозначно использованию одного или нескольких параметров OUT с объявлением функции как возвращающей SETOF record (или SETOF тип единственного параметра, если это применимо). Этот синтаксис описан в последних версиях стандарта SQL, так что этот вариант может быть более портируемым, чем SETOF .

Например, предыдущий пример с суммой и произведением можно также переписать так:

Запись RETURNS TABLE не позволяет явно указывать OUT и INOUT для параметров — все выходные столбцы необходимо записать в списке TABLE .

35.5.10. Полиморфные функции SQL

Обратите внимание на приведение типа ‘a’::text , определяющее, что аргумент имеет тип text . Оно необходимо, если аргумент задаётся просто строковой константой, так как иначе он будет воспринят как имеющий тип unknown , а массив типов unknown является недопустимым. Без этого приведения вы получите такую ошибку:

(ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип «unknown»)

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

(ОШИБКА: не удалось определить тип результата; ПОДРОБНОСТИ: Функция, возвращающая полиморфный тип, должна иметь минимум один полиморфный аргумент.)

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

Полиморфизм также можно применять с функциями с переменными параметрами. Например:

35.5.11. Функции SQL с правилами сортировки

Когда функция SQL принимает один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции, в зависимости от правил сортировки, связанных с фактическими аргументами, как описано в Разделе 22.2. Если правило сортировки определено успешно (то есть не возникло конфликтов между неявно установленными правилами сортировки аргументов), оно неявно назначается для всех сортируемых параметров. Выбранное правило будет определять поведение операций, связанных с сортировкой, в данной функции. Например, для показанной выше функции anyleast , результат

будет зависеть от правила сортировки по умолчанию, заданного в базе данных. С локалью C результатом будет строка ABC , но со многими другими локалями это будет abc . Нужное правило сортировки можно установить принудительно, добавив предложение COLLATE к одному из аргументов функции, например:

С другой стороны, если вы хотите, чтобы функция работала с определённым правилом сортировки, вне зависимости от того, с каким она была вызвана, вставьте предложения COLLATE где требуется в определении функции. Эта версия anyleast всегда будет сравнивать строки по правилам локали en_US :

Но заметьте, что при попытке применить правило к несортируемому типу данных, возникнет ошибка.

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

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

SQL Server Functions: The Basics

SQL Server’s functions are a valuable addition to TSQL when used wisely. Jeremiah provides a complete and comprehensive guide to scalar functions and table-valued functions, and shows how and where they are best used.

i.e. built of pieces that can be reused and put together in a number of different ways to meet the needs of the users. Functions hide the steps and the complexity from other code.

However, in certain respects, SQL Server’s functions are fundamentally different from functions in other programming environments. In procedural programming, the piece of functionality that most programmers call a function should really be called a subroutine, which is more like a miniature program. These subroutines can go about changing data, introducing side effects, and generally misbehaving as much as they like.

In SQL Server, functions adhere much more closely to their mathematic definition of mapping a set of inputs to a set of outputs. SQL Server’s functions accept parameters, perform some sort of action, and return a result. They do all of this with no side effects. Nevertheless, in the same way as subroutines, SQL Server functions can hide complexity from users and turn a complex piece of code into a re-usable commodity. Functions make it possible, for example, to create very complex search conditions that would be difficult and tedious to express in inline T-SQL.

This article describes:

• The types of user-defined functions (UDFs) that SQL Server supports, both scalar (which return a single value) and table-valued (which return a table), and how to use them.
• Some of the more interesting built-in functions
• How and why functions can get you into trouble, and cause terrible performance, if you’re not careful about how you use them.

Fun Facts about Functions

This section describes, briefly, some of the basic characteristics of the various types of SQL Server function, which we’ll explore in more detail as we progress through the later examples.

As noted in the introduction, all SQL Server functions adhere closely to the mathematic definition of a function i.e. a mapping of inputs to outputs, without have side effects. A function with inputs x and y cannot both return x + y and modify the original value of y. As a matter of fact, that function couldn’t even modify y: it is only able to return a new value.

Where Can I Use A Function?

Well, we can use a function almost anywhere that we would use a table or column. We can use a function anywhere that we can use a scalar value or a table. Functions can be used in constraints, computed columns, joins, WHERE clauses, or even in other functions. Functions are an incredibly powerful part of SQL Server.

Functions can be Scalar or Table-valued

Scalar functions return a single value. It doesn’t matter what type it is, as long as it’s only a single, value rather than a table value. You can use a scalar function “ anywhere that a scalar expression of the same data type is allowed in T-SQL statements ” (quote from Books Online). All data types in SQL Server are scalar data types, with the exception of TEXT , NTEXT , ROWVERSION , and IMAGE . Unless you are working with SQL Server 2000, you should be avoiding the TEXT , NTEXT , and IMAGE data types; they are deprecated and will be removed in a future version of SQL Server.

In addition to user-defined scalar functions, SQL Server provides numerous types of built-in scalar functions, some of which we’ll cover in more detail later. For example, there are several built-in date functions, such as GETDATE , string functions, such as SUBSTRING , and so on, all of which act on a single value and return a single value. There are also aggregate functions that perform a calculation on a set of values and return a single value, as well as a few ranking functions that produce one row for each input row.

Table-valued functions (TVFs) return a table instead of a single value. A table valued function can be used anywhere a table can be used – typically in the FROM clause of a query. TVFs make it possible to encapsulate complex logic in a query. For example, security permissions, calculations, and business logic can be embedded in a TVF. Careful use of TVFs makes it easy to create re-usable code frameworks in the database.

One of the important differences between scalar functions and TVFs is the way in which they can be handled internally, by the SQL Server query optimizer.

Most developers will be used to working with compilers that will “inline” trivial function calls. In other words, in any place where the function is called, the compiler will automatically incorporate the whole body of the function into the surrounding code. The alternative is that a function is treated as interpreted code, and invoking it from the main body of code requires a jump to a different code block to execute the function.

The biggest drawback of SQL Server functions is that they may not be automatically inlined. For a scalar function that operates on multiple rows, SQL Server will execute the function once for every row in the result set. This can have a huge performance impact, as will be demonstrated later in the article. Fortunately, with TVFs, SQL Server will call them only once, regardless of the number of rows in the result set and it’s often possible, with a bit of ingenuity, to rewrite scalar functions into TVFs, and so avoid the row-by-row processing that is inherent with scalar functions.

In some cases, it might be necessary to dispense with the TVF altogether, and simply “manually inline” the function logic into the main code. Of course this defeats the purpose of creating a function to encapsulate re-usable logic.

Functions can be Deterministic or Nondeterministic

A deterministic function will return the same result when it is called with the same set of input parameters. Adding two numbers together is an example of a deterministic function.

A nondeterministic function, on the other hand, may return different results every time they are called with the same set of input values. Even if the state of the data in the database is the same, the results of the function might be different. The GETDATE function, for example, is nondeterministic. One caveat of almost all nondeterministic functions is that they are executed once per statement, not once per row. If you query 90,000 rows of data and use the RAND function to attempt to produce a random value for each row you will be disappointed; SQL Server will only generate a single random number for the entire statement. The only exception to this rule is NEWID , which will generate a new GUID for every row in the statement.

When we create a function, SQL Server will analyze the code we’ve created and evaluate whether the function is deterministic. If our function makes calls to any nondeterministic functions, it will, itself, be marked as nondeterministic. SQL Server relies on the author of a SQL CLR function to declare the function as deterministic using an attribute.

Deterministic functions can be used in indexed views and computed columns whereas nondeterministic functions cannot.

Keeping things safe: functions can be schema-bound

Functions, just like views, can be schema bound. Attempts to alter objects that are referenced by a schema bound function will fail. What does this buy us, though? Well, just as when schema binding a view, schema binding a function makes it more difficult to make changes to the underlying data structures that would break our functions. To create a schema-bound function we simply specify schema binding as an option during function creation, as shown in Listing 1.