SQL Server CREATE FUNCTION sqlserver用户定义函数

这篇文章主要为大家详细介绍了SQL Server CREATE FUNCTION sqlserver用户定义函数，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE 语句执行。用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。语法标量函数

代码如下：

CREATE FUNCTION [ owner_name.] function_name 
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 
RETURNS scalar_return_data_type 
[ WITH < function_option> [ [,] ...n] ] 
[ AS ] 
BEGIN 
function_body 
RETURN scalar_expression 
END

内嵌表值函数

代码如下：

CREATE FUNCTION [ owner_name.] function_name 
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 
RETURNS TABLE 
[ WITH < function_option > [ [,] ...n ] ] 
[ AS ] 
RETURN [ ( ] select-stmt [ ) ] 

多语句表值函数

代码如下：

CREATE FUNCTION [ owner_name.] function_name 
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 
RETURNS @return_variable TABLE < table_type_definition > 
[ WITH < function_option > [ [,] ...n ] ] 
[ AS ] 
BEGIN 
function_body 
RETURN 
END 
< function_option > ::= 
{ ENCRYPTION | SCHEMABINDING } 
< table_type_definition > ::= 
( { column_definition | table_constraint } [ ,...n ] ) 

参数

尽管在用户定义函数主体中不允许有不确定函数，这些用户定义函数在调用扩展存储过程时仍会产生副作用。由于扩展存储过程会对数据库产生副作用，因此调用扩展存储过程的函数是不确定的。当用户定义函数调用会对数据库产生副作用的扩展存储过程时，不要指望结果集保持一致或执行函数。从函数中调用扩展存储过程从函数内部调用时扩展存储过程无法向客户端返回结果集。任何向客户端返回结果集的 ODS API 都将返回 FAIL。扩展存储过程可以连接回 Microsoft® SQL Server™；但是，它不应尝试联接与唤醒调用扩展存储过程的函数相同的事务。与从批处理或存储过程中唤醒调用相似，扩展存储过程在运行 SQL Server 的 Windows® 安全帐户的上下文中执行。存储过程的所有者在授予用户 EXECUTE 特权时应考虑这一点。函数调用在可使用标量表达式的位置可唤醒调用标量值函数，包括计算列和 CHECK 约束定义。当唤醒调用标量值函数时，至少应使用函数的两部分名称。[database_name.]owner_name.function_name ([argument_expr][,...])如果用户定义函数用于定义计算列，则该函数的确定性同样决定了是否可在该计算列上创建索引。只有当函数具有确定性时，才可以在使用该函数的计算列上创建索引。如果在输入相同的情况下函数始终返回相同的值，则该函数具有确定性。可以使用由一部分组成的名称唤醒调用表值函数。[database_name.][owner_name.]function_name ([argument_expr][,...])对于 Microsoft® SQL Server™ 2000 中包含的系统表函数，唤醒调用时需在函数名的前面加上前缀"::"。SELECT *FROM ::fn_helpcollations()对于导致语句停止执行然后从存储过程中的下一语句继续执行的 Transact-SQL 错误，在函数中的处理方式不同。在函数中，这类错误会导致函数停止执行。这反过来使唤醒调用该函数的语句停止执行。权限用户应具有执行 CREATE FUNCTION 语句的 CREATE FUNCTION 权限。CREATE FUNCTION 的权限默认地授予 sysadmin 固定服务器角色和 db_owner 和 db_ddladmin 固定数据库角色的成员。sysadmin 和 db_owner 的成员可用 GRANT 语句将 CREATE FUNCTION 权限授予其它登录。函数的所有者对其函数具有 EXECUTE 权限。其他用户不具有 EXECUTE 权限，除非给他们授予了特定函数上的 EXECUTE 权限。若要创建或更改在 CONSTRAINT、DEFAULT 子句或计算列定义中引用了用户定义函数的表，用户还必须对这些函数有 REFERENCES 权限。示例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 1SELECT 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 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

注：关于SQL Server CREATE FUNCTION sqlserver用户定义函数的内容就先介绍到这里，更多相关文章的可以留意