不确定的函数会产生副作用。副作用是更改数据库的某些全局状态，比如更新数据库表或某些外部资源，如文件或网络等
（例如，修改文件或发送电子邮件消息）。

不允许在用户定义函数主体中内置不确定函数；这些不确定函数如下：

@@CONNECTIONS @@TOTAL_ERRORS
@@CPU_BUSY @@TOTAL_READ
@@IDLE @@TOTAL_WRITE
@@IO_BUSY GETDATE
@@MAX_CONNECTIONS GETUTCDATE
@@PACK_RECEIVED NEWID
@@PACK_SENT RAND
@@PACKET_ERRORS TEXTPTR
@@TIMETICKS


尽管在用户定义函数主体中不允许有不确定函数，这些用户定义函数在调用扩展存储过程时仍会产生副作用。

由于扩展存储过程会对数据库产生副作用，因此调用扩展存储过程的函数是不确定的。当用户定义函数调用会对数据库产
生副作用的扩展存储过程时，不要指望结果集保持一致或执行函数。

从函数中调用扩展存储过程
从函数内部调用时扩展存储过程无法向客户端返回结果集。任何向客户端返回结果集的 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 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

 2/2   首页 上一页 1 2