Как вызвать функцию sql

Практическое руководство. Встроенный вызов пользовательских функций

Пользовательские функции можно вызывать из строки кода, однако функции, включенные в запрос, выполнение которого отложено, выполняются только одновременно с запросом. Дополнительные сведения см. в разделе Введение в запросы LINQ (C#).

При вызове той же функции за пределами запроса LINQ to SQL создает простой запрос из выражения вызова метода. Ниже показан синтаксис SQL (параметр @p0 привязан к передаваемой константе).

SELECT dbo.ReverseCustName(@p0) 

LINQ to SQL создает следующее:

string str = db.ReverseCustName("LINQ to SQL"); 

Dim str As String = db.ReverseCustName("LINQ to SQL") 

Пример

В следующем запросе LINQ to SQL можно увидеть встроенный вызов созданной определяемой пользователем функции ReverseCustName . Функция не выполняется немедленно, поскольку выполнение запроса отложено. Код SQL, созданный для этого запроса, преобразуется в вызов пользовательской функции в базе данных (см. код SQL, расположенный после запроса).

var custQuery = from cust in db.Customers select new ; 

Dim custQuery = _ From cust In db.Customers _ Select cust.ContactName, Title = _ db.ReverseCustName(cust.ContactTitle) 

SELECT [t0].[ContactName], dbo.ReverseCustName([t0].[ContactTitle]) AS [Title] FROM [Customers] AS [t0] 

См. также

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

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

• хранимые процедуры;
• определяемые пользователем функции (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:

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:

USE SampleDb; -- Вернет проект "p2 - Gemini" SELECT Number, ProjectName FROM Project WHERE Budget < dbo.ComputeCosts(25);

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

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

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

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

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

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

USE SampleDb; GO CREATE FUNCTION EmployeesInProject (@projectNumber CHAR(4)) RETURNS TABLE AS RETURN (SELECT FirstName, LastName FROM Works_on, Employee WHERE Employee.Id = Works_on.EmpId AND ProjectNumber = @projectNumber)

Функция 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
• OUTER APPLY

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

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

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

USE SampleDb; GO -- Создать функцию CREATE FUNCTION GetJob(@empid AS INT) RETURNS TABLE AS RETURN SELECT Job FROM Works_on WHERE EmpId = @empid AND Job IS NOT NULL AND ProjectNumber = 'p1';

Функция 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#, в котором я добавлял определение хранимых процедур и функций, у вас сборка может называться иначе):

USE SampleDb; GO ALTER ASSEMBLY CLRStoredProcedures FROM 'D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll' WITH PERMISSION_SET = SAFE

Инструкция CREATE FUNCTION в примере ниже сохраняет метод ComputeBudget в виде объекта базы данных, который в дальнейшем можно использовать в инструкциях для манипулирования данными.

USE SampleDb; GO CREATE FUNCTION RecomputeBudget (@budget Real) RETURNS FLOAT AS EXTERNAL NAME CLRStoredProcedures.BudgetPercent.ComputeBudget

Использование одной из таких инструкций, инструкции SELECT, показано в примере ниже:

USE SampleDb; -- Вернет 4098 SELECT dbo.RecomputeBudget (341.5);

Определяемую пользователем функцию можно поместить в разных местах инструкции SELECT. В примерах выше она вызывалась в предложениях WHERE, FROM и в списке выбора оператора SELECT.

Вызов функции

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

SELECT now() 

Иногда требуется приведение строки в верхний регистр, для этого используют функцию upper :

SELECT upper('elephant') 

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

имя_функции(выражение1, выражение2, . ) 

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

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

Как запустить функцию в sql

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

1. Позиционная передача
2. Именная передача
3. Смешанная передача

Рассмотрим эти 3 варианта на примере функции concat_lower_or_upper() сигнатура у неё определена следующим образом:

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text . 

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

-- Позиционная передача SELECT concat_lower_or_upper('Hello', 'Hexlet', true); -- Именная передача SELECT concat_lower_or_upper(a => 'Hello', b => 'Hexlet'); -- Смешанная передача SELECT concat_lower_or_upper('Hello', 'Hexlet', uppercase => true);