A. 计算 ISO 周的标量值用户定义函数
下例中,用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数,必须在调用该函数前唤醒调用 SET DATEFIRST 1。 CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END下面是函数调用。注意 DATEFIRST 设置为 1。SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是结果集。ISO Week
----------------
52B. 内嵌表值函数
下例返回内嵌表值函数。USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)C. 多语句表值函数
假设有一个表代表如下的层次关系: CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID,它返回与所有直接或间接向给定职员报告的职员相对应的表。该逻辑无法在单个查询中表现出来,不过可以实现为用户定义函数。 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))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
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)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 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
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
下例中,用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数,必须在调用该函数前唤醒调用 SET DATEFIRST 1。 CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END下面是函数调用。注意 DATEFIRST 设置为 1。SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是结果集。ISO Week
----------------
52B. 内嵌表值函数
下例返回内嵌表值函数。USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)C. 多语句表值函数
假设有一个表代表如下的层次关系: CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID,它返回与所有直接或间接向给定职员报告的职员相对应的表。该逻辑无法在单个查询中表现出来,不过可以实现为用户定义函数。 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))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
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)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 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
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
如有函数:
create function myfun()
as
...--------------
调用时:
select dbo.myfun()
create function myfun(参数 参数类型)
returns 类型
as
...--------------
调用时:
select dbo.myfun(参数)