Сегодня мы поговорим об очень полезном функционале языка Transact-SQL — это табличные функции. Так как у нас сайт для начинающих программистов, а такие программисты по началу боятся использовать табличные функции или просто не знают, зачем они нужны. Поэтому я решил рассказать про основы создания таких функций. А также рассказать про использование временных таблиц в процедурах или SQL инструкциях.
Табличные функции в Transact-SQL – описание и примеры создания
Раньше мы уже знакомились с функциями, которые возвращают таблицу, правда, на языке PL/pgSQL для сервера PostgreSQL (Написание табличной функции на PL/pgSQL). Теперь пришло время поговорить о такой реализации на Transact-SQL.
Вспомним, а для чего нам вообще нужны такие функции. Самый простой ответ на этот вопрос это то, что в таких функциях можно программировать (объявлять переменные, выполнять какие-то расчеты) и передавать параметры внутрь этой функции, как в обычных скалярных функциях, а результат получать в виде таблицы. И это хорошо, ведь в представлениях (вьюхах) этого делать нельзя, а процедура ничего не возвращает (можно сделать, чтобы возвращала, но это в большинстве случае не очень удобно).
Пример создания простой табличной функции
Итак, приступим, для начала приведем самый простой вариант реализации такой функции. Допустим, нам нужно выбрать несколько полей из таблицы по определенному критерию.
Примечание! Данный пример можно реализовать и с помощью представления. Но мы пока только учимся писать такие функции.
В итоге мы создали функцию, в которую будем передавать один параметр id, его мы используем в условии исходного SQL запроса.
Получить данные из этой функции можно следующим образом:
Как видите все проще простого. Теперь давайте создадим функцию уже с использованием программирования в этой функции.
Пример создания табличной функции, в которой можно программировать
Здесь мы уже программируем и можем выполнять любые действия как в обычных функциях и процедурах, при этом получая результат в виде таблицы. В этом примере мы передаем один параметр внутрь нашей функции (их может быть несколько!), внутри функции мы уже смотрим, что за параметр к нам пришел, и на основе этого уже формируем условие для запроса. Как Вы понимаете это тоже простой пример, но можно писать очень и очень сложные алгоритмы как в процедурах, именно поэтому, и созданы эти табличные функции.
Теперь давайте обратимся к нашей функции, например, вот так
Временные таблицы в Transact-SQL — описание и пример создания
В дополнение к табличным функциям я хочу рассказать еще о возможности использования временных таблиц, которые также иногда очень сильно помогают выполнять поставленные задачи.
Например, в процедуре Вам необходимо выполнять много расчетов и запоминать их результат, а создавать дополнительные представления, табличные функции или вообще таблицы, которые потом нужно удалять, так как они Вам после выполнения процедуры будут не нужны, Вам просто не охота, да и это не очень удобно. Поэтому в Microsoft SQL Server существует такая возможность как «Временные таблицы». Давайте научимся их создавать. А создавать и использовать их очень просто, например, в коде своей процедуры, Вы хотите сохранить результат в таблицу, для этого просто создаете эту таблицу вот так (перед названием таблицы ставится знак решетки #).
Таким образом, у Вас в базе фактически таблица не будет создана (и Вы ее нигде не увидите и, соответственно, не сможете обратиться к ней), она будет создана только для этой сессии, и обратиться к ней можно только из этой сессии, в которой Вы ее создали. То есть, даже если Вы создаете такую таблицу в одном окне запроса Management Studio, то из другого окна, Вы к ней не сможете обратиться. Это упрощает разработку процедуры и в некоторых случаях сильно ее ускоряет (в качестве альтернативы можно использовать табличные переменные).
Надеюсь, эта статья поможет Вам разобраться, для чего нужны табличные функции и временные таблицы, и как Вы их можете использовать на практике.
Для более детального изучения языка Transact-SQL рекомендую книгу «Путь программиста T-SQL», в которой я очень подробно рассказываю про все основные конструкции и возможности данного языка.
В языках программирования обычно имеется два типа подпрограмм:
определяемые пользователем функции (UDF).
Как уже было рассмотрено в предыдущей статье, хранимые процедуры состоят из нескольких инструкций и имеют от нуля до нескольких входных параметров, но обычно не возвращают никаких параметров. В отличие от хранимых процедур, функции всегда возвращают одно значение. В этом разделе мы рассмотрим создание и использование .
Создание и выполнение определяемых пользователем функций
Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION, которая имеет следующий синтаксис:
Параметр 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:
Функция 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 приведено в примере ниже:
Возвращающие табличное значение функции и инструкция 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:
Результатом выполнения этих двух функций будут следующие две таблицы (отображаются после выполнения второй функции):
В первом запросе примера результирующий набор табличной функции GetJob() "соединяется" с содержимым таблицы Employee посредством инструкции CROSS APPLY. Функция GetJob() играет роль правого ввода, а таблица Employee — левого. Выражение правого ввода вычисляется для каждой строки левого ввода, а полученные строки комбинируются, создавая конечный результат.
Второй запрос похожий на первый (но в нем используется инструкция OUTER APPLY), который логически соответствует операции внешнего соединения двух таблиц.
Возвращающие табличное значение параметры
Во всех версиях сервера, предшествующих SQL Server 2008, задача передачи подпрограмме множественных параметров была сопряжена со значительными сложностями. Для этого сначала нужно было создать временную таблицу, вставить в нее передаваемые значения, и только затем можно было вызывать подпрограмму. Начиная с версии SQL Server 2008, эта задача упрощена, благодаря возможности использования возвращающих табличное значение параметров, посредством которых результирующий набор может быть передан соответствующей подпрограмме.
Использование возвращающего табличное значение параметра показано в примере ниже:
В этом примере сначала определяется табличный тип 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#:
В исходном коде определяемых пользователем функций в примере вычисляется новый бюджет проекта, увеличивая старый бюджет на определенное количество процентов. Вы можете использовать инструкцию CREATE ASSEMBLY для создания сборки CLR в базе данных, как это было показано ранее. Если вы прорабатывали примеры из предыдущей статьи и уже добавили сборку CLRStoredProcedures в базу данных, то вы можете обновить эту сборку, после ее перекомпиляции с новым классом (CLRStoredProcedures это имя моего проекта классов C#, в котором я добавлял определение хранимых процедур и функций, у вас сборка может называться иначе):
Инструкция CREATE FUNCTION в примере ниже сохраняет метод ComputeBudget в виде объекта базы данных, который в дальнейшем можно использовать в инструкциях для манипулирования данными.
Использование одной из таких инструкций, инструкции SELECT, показано в примере ниже:
Определяемую пользователем функцию можно поместить в разных местах инструкции SELECT. В примерах выше она вызывалась в предложениях WHERE, FROM и в списке выбора оператора SELECT.
Начиная с версии 2008 MS SQL Server позволяет принимать табличные наборы данных в качестве параметров хранимых процедур и функций.
Рассмотрим подробнее использование этого приёма.
Чтобы MS SQL Server смог принять в качестве параметра таблицу требуется описать её структуру. Для этого создадим пользовательский тип данных, который будет содержать данное описание.
В качестве примера создадим тип данных, который описывает список моделей автомобилей.
После того как соответствующий тип данных создан, его можно использовать для обозначения табличных параметров в хранимых процедурах и функциях.
Напишем функцию, которая будет возвращать количество записей в табличном наборе созданного ранее типа.
Обратите внимание, что табличный параметр обозначен как readonly. Это обозначение является обязательным, так как над такими параметрами в MS SQL Server запрещено выполнять операции связанные с изменением данных ( INSERT, UPDATE, DELETE) в процедурах и функциях.
Если мы создадим табличную переменную типа CarList и вызовем функцию CarListCount, мы получим количество записей в этой переменной.
Работа с параметрами возвращающими табличное значение в хранимых процедурах осуществляется аналогичным образом.
Параметры возвращающие табличное значение очень удобны при работе с большими объёмами структурированных данных, так как позволяют обращаться с ними как с таблицей. По этой же причине они дают возможность в ряде случаев оптимизировать выполнение сложных запросов.
Так, что применяя подобные параметры можно добиться весьма существенного положительного эффекта как в плане упрощения реализации алгоритмов, так и с точки зрения быстродействия.
