Особенности формирования и использования 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 функций и алгоритмов работы с ними!

Также приглашаем на специальный курс по MS SQL.