На главную страницу
Microsoft SQL Server 2000. Определяемые пользователем функции (UDF).
Microsoft
Corp. (оригинал: Microsoft
SQL Server 2000 User Defined Functions Whitepaper)
Сокращенный
перевод Моисеенко С.И.
Часть 1
Часть 2
Часть 3 Часть
4
Предложения
-
Вы можете вызывать скалярнозначные функции в предложениях WHERE или HAVING.
Если вы имеете таблицу employee, которая содержит годовую зарплату для одних
сотрудников и почасовую оплату для других, и если вы хотите выполнить запрос,
который использует недельную зарплату в качестве фильтра, вы можете создать
UDF, которая вернет недельную зарплату как функцию классификации (годовая или
почасовая). Запрос может выглядеть следующим образом, где weekly
- имя UDF:
SELECT surname, first_name FROM employee
WHERE ((weekly(salary, classification)) > 400.);
-
Вы можете вызывать скалярнозначную функцию в предложении GROUP BY. Например:
SELECT DATEPART (yy, HireDate) AS Year, COUNT (*) AS
NumberOfHires
FROM Northwind.dbo.Employees
GROUP BY DATEPART (yy, HireDate)
-
Вы можете вызывать скалярнозначную функцию DATEPART (yy, HireDate) сортировке
по выражению в предложении ORDER BY:
SELECT EmployeeID, LastName, DATEPART (yy, HireDate) AS Year
FROM Northwind.dbo.Employees
ORDER BY DATEPART (yy, HireDate)
-
Вы можете вызывать скалярнозначные функции на стороне источника списка SET
оператора UPDATE.
Пусть требуется скалярнозначная функция, которая вычисляет премию, которую получает
каждый сотрудник. Все данные по сотрудникам хранятся в таблице Employee.
Входными полями для функции вычисления премии являются значения столбцов SALARY и
COMM. Возвращаемое значение функции заносится в столбец BONUS. Поскольку
функция получает входные данные из таблицы, а также заносит результат в
таблицу, удобно реализовать это при помощи UDF.
Создадим соответствующую UDF.
CREATE FUNCTION CALC_BONUS (DECIMAL (9,2), DECIMAL (9,2))
RETURNS DECIMAL (9,2)
<body>?
go
UPDATE EMP
SET BONUS = CALC_BONUS (SALARY, COMM)
-
Вы можете вызывать скалярнозначные функции в элементах списка предложения
VALUES оператора INSERT.
Ограничение Check
Вы можете использовать скалярнозначные UDF в качестве проверочного ограничения для
столбцов в определении таблицы, в результате чего можно реализовать сложную
логику ограничений целостности. Аргументы функции в ограничении могут включать
только столбцы таблицы или константы. Когда процессор запросов проверяет
ограничение, он ограничивается значениями столбцов-аргументов функции,
соответствующих проверяемой строке.
Ниже приведен классический пример UDF, которая используется в качестве проверочного
ограничения. Пусть имеются таблицы Orders и Customers,
при этом таблица Customers содержит столбец Status,
который указывает, является ли клиент активным или нет. Ваше бизнес-правило
разрешает появление новых строк в таблице Orders только для
активных клиентов из Customers. Поскольку нет такого
ограничения, которое проверяло бы состояние разных таблиц, вы можете создать
свою собственную UDF для решения этой задачи.
Ниже приведено определение UDF как проверочного ограничения на столбце CustomerId
таблицы Orders, которое проверяет статус клиента в таблице
Customers и вызывает появление сообщения об ошибке, если клиент не
является активным:
Create function ChckCustStatus(@CustomerId nchar(10))
Returns bit
BEGIN
DECLARE @ISActive BIT
Set @ISActive = 0
select @ISActive = Status from
Customer where CustomerID =@CustId
Return (@ISActive)
End
Go
Alter Table Orders WITH NOCHECK
Add CONSTRAINT ChkStatus CHECK (dbo.ChckCustStatus(CustomerID)=1)
Данный пример UDF демонстрирует использование проверочного ограничения для проверки
целостности на другой таблице.
Значение по умолчанию
<constant-expression> определяет значение по умолчанию для столбца в
операторах CREATE TABLE и ALTER TABLE. Аргументы функции могут содержать только
константы.
Например, требуется, чтобы принятая по умолчанию дата поставки представляла собой
допустимую дату (не выходной):
Create function DeliveryDt(@DlDt datetime)
Returns datetime
as
Begin
Declare @RetDt datetime
set @RetDt = DATEADD(dd, 15, @DlDt)
-- check If the delivery data falls on
sat/sun.
if DATEPART(dw, @RetDt) = 7
set
@RetDt = DATEADD(dd, 2, @RetDt)
if DATEPART(dw, @RetDt) = 1
set @RetDt = DATEADD(dd, 1, @RetDt) Return(@RetDt)
End
drop table OrderEntry
CREATE TABLE OrderEntry
(
OrderId int IDENTITY NOT NULL,
CustomerID nchar(10) NOT NULL,
OrderDate datetime NOT NULL DEFAULT
GETDATE(),
DeliveryDate datetime NOT NULL DEFAULT
dbo.DeliveryDt('09/28/2000')
)
insert into OrderEntry (CustomerID,OrderDate) values('Pooja','10/01/2000')
select * from OrderEntry
Операторы присвоения и управления ходом выполнения программы
Вызов скалярнозначной функции может присутствовать в правой части оператора
присваивания.
Выражения CASE
Вызов скалярнозначной функции может присутствовать в <when-expression>,
<input-expression> или <result-expression> выражения
CASE.
Оператор PRINT
Аргумент <srting-expression>оператора PRINT может
вызывать скалярнозначную функцию, которая возвращает символьный тип. Например:
PRINT dbo.Rectangle(5,4)
Аргументы функций
Вы можете вызывать UDF в качестве одного из аргументов UDF.
Оператор RETURN
Оператор RETURN может вызывать скалярнозначную UDF. Например:
Create function factRec(@n int)
Returns int
Begin
If @n=0
Return 1
Return (@n *
dbo.factRec(@n-1))
End
Замечание: UDF может вызываться рекурсивно, однако вложенность
ограничена 32-мя уровнями.
Как вызывать табличнозначные функции?
Табличнознчные функции могут вызываться в запросе там, где ожидается табличное
выражение. Это предложение FROM операторов SELECT, UPDATE или DELETE. Например:
SELECT *
FROM tb_Employees AS E, dbo.fn_EmployeesInDept('shipping') AS EID
WHERE E.EmployeeID = EID.EmployeeID
UPDATE titles SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, dbo.fn_sales s
WHERE t.title_id = s.title_id
Вызовы табличнозначных функций могут иметь последующий алиас. Вы можете использовать
эти алиасы для ссылок на столбцы, которые возвращаются функцией в предложениях
SELECT и WHERE.
Строчные табличнозначные функции могут вызываться на целевой стороне (модифицируемые
таблицы) оператора INSERT, UPDATE или DELETE, а также оператора SELECT, который
определяет соответствие функции критерию обновляемости. Условие обновляемости
для оператора SELECT аналогично условию обновляемости оператора CREATE VIEW.
Замечание: В коррелирующем подзапросе вызовы табличнозначной
функции не могут содержать аргумент, представляющий собой ссылку на столбец во
внешнем запросе.
Например, нижеследующая таблица представляет иерархическое отношение:
EMPLOYEES(empid nchar(5) PRIMARY KEY, empname nvarchar(50), Mgrid
nchar(5)
REFERENCES EMPLOYEES(empid), Title nvarchar(30))
При заданном идентификаторе сотрудника табличнозначная функция fn_FindReports(InEmpID)
возвращает таблицу, которая соответствует всем сотрудникам, подчиняющихся
данному, напрямую или опосредованно. Эту логику нельзя выразить одним запросом,
поэтому она является подходящим кандидатом для UDF. Например:
Create function fn_FindReports (@InEmpId nchar(5))
returns @retFindReports table (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5), title nvarchar(30))
-- Возвращает результирующий набор,
который содержит список всех сотрудников,
-- которые подчиняются заданному
напрямую или опосредованно
as
begin
declare @RowsAdded int
--
Table variable to hold accumulated results
declare @reports table(empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title
nvarchar(30),
processed tinyint default 0)
--
Инициализируем @Reports с прямыми подчиненными данного сотрудника
insert
@reports
select
empid, empname, mgrid, title, 0
from
employees
where
empid = @InEmpId
set
@RowsAdded = @@rowcount
--
Если новые сотрудники были добавлены на предыдущей итерации
while
@RowsAdded > 0
begin
-- Отмечаем все записи сотрудников с прямым подчинением,
-- которые были найдены на этой итерации с processed=1.
update
@reports
set processed = 1
where processed = 0
-- Вставляем сотрудников, которые подчиняются сотруднику
--
с отметкой 1.
insert @reports
select e.empid, e.empname, e.mgrid, e.title, 0
from employees e, @Reports r
where e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
set @RowsAdded = @@rowcount
-- Отмечаем всех сотрудников, которые непосредственно
--
подчиняются на этой итерации
update @reports
set processed = 2
where processed = 1
end
--
Копируем результат в соответствующие столбцы функции.
insert
@retFindReports
select
empid, empname, mgrid, title
from
@reports
return
end
go
-- Вывод
select * from fn_FindReports(‘11234’)
go
Часть 1
Часть 2
Часть 3 Часть
4