Главная | Обратная связь | Поможем написать вашу работу!

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

⇐ ПредыдущаяСтр 20 из 24Следующая ⇒

Функции всегда имеют один возвращаемый параметр.

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

Скалярная функция возвращает атомарное (скалярное) значение.

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

Функции создаются при помощи оператора create function, который имеет cледующий синтаксис:

create function[ schema_name. ] function_name

[ { @ parameter_name [ AS ] parameter_data_type [ = default ] } [,... n ] ]

RETURNS { scalar_type | [@ variable ]TABLE}

[WITH (ENCRYPTION | SCHEMABINDING)]

[AS] {block RETURN scalar_expression

| RETURN (select_statement) }

schema_name - имя схемы, которой назначено владение созданной функцией;

function_name - имя новой функции;

@ parameter_name - имя входного параметра;

parameter_data_type -задает тип данных параметра;

default - задает необязательное значение по умолчанию для соответствующего параметра. Значением по умолчанию может быть также пустое значение null.

RETURNS - задает тип данных значения, возвращаемого функцией. Это может быть любой стандартный тип данных, поддерживаемый системой базы данных, включая тип данных table (SQL 2008). (Только один стандартный тип данных, который нельзя использовать, является тип данных timestamp);

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

block - является блоком begin/end, который содержит реализацию функции. Последним оператором блока должен быть оператор return с аргументом. Значение аргумента - это значение, возвращаемое данной функцией.

В теле блока begin/end допустимы только следующие операторы:

· операторы присваивания, такие как set;

· операторы управления потоком выполнения, такие как while и if;

· операторы declare, определяющие локальные переменные данных;

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

· операторы insert, update и delete, изменяющие переменные типа table, которые являются локальными в этой функции.

По умолчанию только участники фиксированной серверной роли sysadmin, а также участники фиксированных ролей базы данных db_ownerи db_ddladminмогут использовать оператор create function.

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

RETURN scalar_expression -возвращаемое атомарное (скалярное) значение.

Синтаксис:

……………………………………

RETURNS return_data_type

[ WITH <function_option> [,...n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

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

RETURN (select_statement) – определяет выходные табличные данные функции. Инструкция RETURN не может иметь аргумента.

with encrYption - кодирует информацию в системном каталоге, который содержит текст оператора create function.

with schemabinding связывает функцию с объектами базы данных.

Пример: функция вычисляет среднее 3 чисел.

CREATE FUNCTION SRED

(@X1 Int, @X2 Int, @X3 Int)

RETURNS real

BEGIN

declare @RES As real

set @RES =(@X1+@X2+@X3)/3

RETURN @RES

END

Вызов:

select dbo.SRED (3, 4, 5)

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

US sample;

CREATE FUNCTION compute_costs (@percent INT =10) -- значение по умолчанию

RETURNS DECIMAL(16,2)

BEGIN

DECLARE @additional_costs DEC (14,2), @sum_budget dec(16,2)

SELECT @sum_budget = SUM (budget) FROM project

SET @additional_costs = @sum_budget * @percent/100

RETURN @additional_costs

END

Входная переменная @percent задает процент увеличения бюджетов. Блок begin/end объявляет две локальные переменные: @additionai_cost и @sum_budget. Затем функция присваивает переменной @sum_budget сумму всех бюджетов, используя специальную форму оператора select. После этого функция вычисляет общие дополнительные затраты и возвращает это значение с помощью оператора return.

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

IF OBJECT_ID('dbo.fn_age') IS NOT NULL DROP FUNCTION dbo.fn_age;

CREATE FUNCTION dbo.fn_age

(

@birthdate AS DATETIME,

@eventdate AS DATETIME

)

RETURNS INT

BEGIN

RETURN

DATEDIFF(year, @birthdate, @eventdate)

- CASE WHEN MONTH(@eventdate) + DAY(@eventdate)

< MONTH(@birthdate) + DAY(@birthdate)

THEN 1 ELSE 0

END

Функции может быть вызвана в операторах Transact-SQL, таких как select, insert, update или delete.

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

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

Пример: использование функции compute_cost

USE sample;

SELECT project_no, project_name

FROM project

WHERE budget < dbo.compute_costs (25)

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

Пример: использование функции compute_cost

USE sample;

SELECT dbo.compute_costs (25)

Пример: использование функции dbo.fn_age

SELECT

empid, firstname, lastname, birthdate,

dbo.fn_age (birthdate, GETDATE ()) AS age

FROM HR.Employees;

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

Ни одна из инструкций Transact-SQL в возвращающей табличное значение функции не может возвращать результирующий набор непосредственно пользователю. Единственные данные, которые функция может вернуть пользователю, это таблица table, возвращаемая этой функцией.

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

Если предложение returns задает table без указания списка столбцов, то эта функция является линейной. Линейные функции возвращают результат выполнения оператора select в виде переменной типа данных table

Синтаксис:

……………………………………

RETURNS TABLE

[ WITH <function_option> [,... n ] ]

[ AS ]

RETURN [ (] select_stmt [) ]

или

Пример:

USE sample;

CREATE FUNCTION employees_in_project (@pr_number CHAR(4))

RETURNS TABLE

AS RETURN (SELECT emp_fname, emp_lname

FROM works_on, employee

WHERE employee.emp_no = works_on.emp_no

AND project_no = @pr_number)

Функция employees_in_project отображает имена всех служащих, которые связаны с конкретным проектом. Входной параметр @pr_number задает номер проекта. Поскольку функция в общем случае вращает набор строк, предложение returns содержит тип данных TABLE. Блока begin/end в примере нет, потому что предложение return содержит оператор select.

Обращение к функции имеет вид:

USE sample;

SELECT * FROM employees_in_project('p3')

Результат:

Пример использования функции, возвращающей таблицу:

CREATE FUNCTION fn_getcustomerorders1(@CustomerID int, @TopRecords bigint)

RETURNS TABLE

As Return

SELECT TOP (@TopRecords) *

FROM Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

ORDER BY OrderDate DESC

Обращение к функции:

SELECT * FROM fn_getcustomerorders1(5,3);

Результат:

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

Синтаксис:

………………………………………………………

RETURNS @ return_variable TABLE < table_type_definition >

[ WITH <function_option> [,... n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

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

Пример: В следующем примере создается функция dbo.ufnGetContactInformation и демонстрируются компоненты возвращающей табличное значение функции. В этой функции именем локальной возвращаемой переменной является @retContactInformation. Инструкции в теле функции вставляют строки в эту переменную для создания табличных результатов, возвращаемых этой функцией.

USE AdventureWorks;

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

--@ContactID входной параметр – индекс контакта

RETURNS @retContactInformation TABLE

(

-- Поля, возвращаемые функцией

⇐ Предыдущая 15 16 17 18 192021 22 23 24 Следующая ⇒