创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。 用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。
语法 标量函数
CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,..本文来源gao($daima.com搞@代@#码$网.n ] ] ) RETURNS scalar_return_data_type [ WITH [ [,] …n] ] [ AS ] BEGIN function_body RETURN scalar_expression END
示例 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 —————- 52
B. 内嵌表值函数 下例返回内嵌表值函数。 代码如下: 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 marked 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
