他们彼此深信,是瞬间迸发的热情让他们相遇。这样的确定是美丽的,但变幻无常更为美丽

留言簿

公告

最新日志

最新评论

搜索

登陆

友情连接

统计

2008-5-26 14:39:04
用户定义函数
函数是由一个或多个 Transact-SQL 语句组成的子程序,可用于封装代码以便重新使用。Microsoft® SQL Server™ 2000 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上,而是允许用户创建自己的用户定义函数。

可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。每个完全合法的用户定义函数名 (database_name.owner_name.function_name) 必须唯一。

必须被授予 CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。不是所有者的用户在 Transact-SQL 语句中使用某个函数之前,必须先给此用户授予该函数的适当权限。若要创建或更改在 CHECK 约束、DEFAULT 子句或计算列定义中引用用户定义函数的表,还必须具有函数的 REFERENCES 权限。

在函数中,区别处理导致删除语句并且继续在诸如触发器或存储过程等模式中的下一语句的 Transact-SQL 错误。在函数中,上述错误会导致停止执行函数。接下来该操作导致停止唤醒调用该函数的语句。

用户定义函数的类型
SQL Server 2000 支持三种用户定义函数:

标量函数


内嵌表值函数


多语句表值函数
用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有 1024 个输入参数。当函数的参数有默认值时,调用该函数时必须指定 DEFAULT 关键字才能获取默认值。该行为不同于在存储过程中含有默认值的参数,而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。

标量函数返回在 RETURNS 子句中定义的类型的单个数据值。可以使用所有标量数据类型,包括 bigint 和 sql_variant。不支持 timestamp 数据类型、用户定义数据类型和非标量类型(如 table 或 cursor)。在 BEGIN...END 块中定义的函数主体包含返回该值的 Transact-SQL 语句系列。返回类型可以是除 text、ntext、image、cursor 和 timestamp 之外的任何数据类型。

表值函数返回 table。对于内嵌表值函数,没有函数主体;表是单个 SELECT 语句的结果集。对于多语句表值函数,在 BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句,这些语句可生成行并将行插入将返回的表中。有关内嵌表值函数的更多信息,请参见内嵌用户定义函数。有关表值函数的更多信息,请参见返回 table 数据类型的用户定义函数。

BEGIN...END 块中的语句不能有任何副作用。函数副作用是指对具有函数外作用域(例如数据库表的修改)的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象(如局部游标或局部变量)的更改。不能在函数中执行的操作包括:对数据库表的修改,对不在函数上的局部游标进行操作,发送电子邮件,尝试修改目录,以及生成返回至用户的结果集。

函数中的有效语句类型包括:

DECLARE 语句,该语句可用于定义函数局部的数据变量和游标。


为函数局部对象赋值,如使用 SET 给标量和表局部变量赋值。


游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用 FETCH 语句将数据返回到客户端。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值。


控制流语句。


SELECT 语句,该语句包含带有表达式的选择列表,其中的表达式将值赋予函数的局部变量。


INSERT、UPDATE 和 DELETE 语句,这些语句修改函数的局部 table 变量。


EXECUTE 语句,该语句调用扩展存储过程。
在查询中指定的函数的实际执行次数在优化器生成的执行计划间可能不同。示例为 WHERE 子句中的子查询唤醒调用的函数。子查询及其函数执行的次数会因优化器选择的访问路径而异。

用户定义函数中不允许使用会对每个调用返回不同数据的内置函数。用户定义函数中不允许使用以下内置函数:

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


架构绑定函数
CREATE FUNCTION 支持 SCHEMABINDING 子句,后者可将函数绑定到它引用的任何对象(如表、视图和其它用户定义函数)的架构。尝试对架构绑定函数所引用的任何对象执行 ALTER 或 DROP 都将失败。

必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING:

该函数所引用的所有视图和用户定义函数必须是绑定到架构的。


该函数所引用的所有对象必须与函数位于同一数据库中。必须使用由一部分或两部分构成的名称来引用对象。


必须具有对该函数中引用的所有对象(表、视图和用户定义函数)的 REFERENCES 权限。
可使用 ALTER FUNCTION 删除架构绑定。ALTER FUNCTION 语句将通过不带 WITH SCHEMABINDING 指定函数来重新定义函数。

调用用户定义函数
当调用标量用户定义函数时,必须提供至少由两部分组成的名称:

SELECT *, MyUser.MyScalarFunction()
FROM MyTable

可以使用一个部分构成的名称调用表值函数:

SELECT *
FROM MyTableFunction()

然而,当调用返回表的 SQL Server 内置函数时,必须将前缀 :: 添加至函数名:

SELECT * FROM ::fn_helpcollations()

可在 Transact-SQL 语句中所允许的函数返回的相同数据类型表达式所在的任何位置引用标量函数,包括计算列和 CHECK 约束定义。例如,下面的语句创建一个返回 decimal 的简单函数:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

然后可以在允许整型表达式的任何地方(如表的计算列中)使用该函数:

CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

dbo.CubicVolume 是返回标量值的用户定义函数的一个示例。RETURNS 子句定义由该函数返回的值的标量数据类型。BEGIN...END 块包含一个或多个执行该函数的 Transact-SQL 语句。该函数中的每个 RETURN 语句都必须具有一个参数,可返回具有在 RETURNS 子句中指定的数据类型(或可隐性转换为 RETURNS 中指定类型的数据类型)的数据值。RETURN 参数的值是该函数返回的值。

获得有关函数的信息
下面几个目录对象报告有关用户定义函数的信息:

sp_help 报告有关用户定义函数的信息。


sp_helptext 报告用户定义函数的来源。
三个信息架构视图报告有关用户定义函数的信息:ROUTINES、PARAMETERS 和 ROUTINE_COLUMNS。这些信息架构视图还报告存储过程的信息。

内嵌用户定义函数
新增信息 - SQL Server 2000 SP3。

内嵌用户定义函数是返回 table 的用户定义函数的子集。内嵌函数可用于实现参数化视图的功能。

请看下面的视图:

CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'

可创建更通用的版本 vw_CustomerNamesInRegion,方法是将 WHERE Region = 'WA' 替换为 WHERE Region = @RegionParameter 并让用户指定感兴趣的查看区域。然而,视图不支持在 WHERE 子句中指定的搜索条件的参数。



安全说明 验证所有用户输入。验证之前,不要串联用户输入。绝对不要执行根据尚未验证的用户输入构造的命令。有关更多信息,请参见验证用户输入。


内嵌用户定义函数可用于支持在 WHERE 子句中指定的搜索条件的参数。下面是使用户得以在其选择中指定区域的函数示例:

CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
GO
-- Example of calling the function for a specific region
SELECT *
FROM fn_CustomerNamesInRegion(N'WA')
GO

内嵌用户定义函数遵从以下规则:

RETURNS 子句仅包含关键字 table。不必定义返回变量的格式,因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置。


function_body 不由 BEGIN 和 END 分隔。


RETURN 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表。内嵌函数中使用的 SELECT 语句受到与视图中使用的 SELECT 语句相同的限制。


表值函数只接受常量或 @local_variable 参数
内嵌函数还可用于提高索引视图的能力。索引视图自身不能在其 WHERE 子句搜索条件中使用参数,针对特定用户的需要调整存储的结果集。然而,可定义存储与视图匹配的完整数据集的索引视图,然后在包含允许用户调整其结果的参数化搜索条件的索引视图上定义内嵌函数。如果视图定义较复杂,则生成结果集所要执行的大多数工作都涉及在视图上创建聚集索引时生成聚合或联接多个表。之后如果创建引用视图的内嵌函数,则该函数可应用用户的参数化筛选,从结果集中提取由 CREATE INDEX 语句生成的特定行。在执行 CREATE INDEX 时复杂的聚合和联接进行一次,随后引用内嵌函数的所有查询都从简化的存储结果集中筛选行。例如:

定义将所有销售数据聚合到结果集的视图 vw_QuarterlySales,该结果集按季度报告所有商店的汇总销售数据。


在 vw_QuarterlySales 上创建聚集索引以具体化包含汇总数据的结果集。


创建筛选汇总数据的内嵌函数:
CREATE FUNCTION fn_QuarterlySalesByStore
(
@StoreID int
)
RETURNS table
AS
RETURN (
SELECT *
FROM SalesDB.dbo.vw_QuarterlySales
WHERE StoreID = @StoreID
)

然后用户可从内嵌函数进行选择以获得其特定商店的数据:
SELECT *
FROM fn_QuarterlySalesByStore( 14432 )

满足在第 4 步发出的查询所需的大多数工作将按季度聚合销售数据。该工作在第 2 步进行一次。第 4 步中的每个 SELECT 语句都使用函数 fn_QuarterlySalesByStore 筛选出用户的某个商店特有的聚合数据。

返回 table 数据类型的用户定义函数
新增信息 - SQL Server 2000 SP3。

返回 table 的用户定义函数可以是替代视图的强大方式。返回 table 的用户定义函数可用在 Transact-SQL 查询中允许表或视图表达式的地方。视图限制于单个 SELECT 语句,而用户定义函数可包含允许比视图中具有的逻辑功能更强大的附加语句。

返回 table 的用户定义函数还可替换返回单个结果集的存储过程。可在 Transact-SQL 语句的 FROM 子句中引用由用户定义函数返回的 table,而返回结果集的存储过程却不能。例如,假设 fn_EmployeesInDept 是返回 table 的用户定义函数并可由 SELECT 语句唤醒调用,如下所示:

SELECT * FROM tb_Employees AS E INNER JOIN
dbo.fn_EmployeesInDept('shipping') AS EID
ON E.EmployeeID = EID.EmployeeID

在返回 table 的用户定义函数中:

RETURNS 子句为函数返回的表定义局部返回变量名。RETURNS 子句还定义表的格式。局部返回变量名的作用域位于函数内。


函数主体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句所定义的返回变量。


当执行 RETURN 语句时,插入变量的行以函数的表格格式输出形式返回。RETURN 语句不能有参数。
函数中返回 table 的 Transact-SQL 语句不能直接将结果集返回用户。函数返回用户的唯一信息是由该函数返回的 table。



安全说明 验证所有用户输入。验证之前,不要串联用户输入。绝对不要执行根据尚未验证的用户输入构造的命令。有关更多信息,请参见验证用户输入。


下例在 Northwind 数据库中创建一个返回 table 的函数:

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END

在这个函数中,返回的本地变量名是 @OrderShipperTab。函数主体中的语句将行插入变量 @OrderShipperTab,以生成该函数返回的表结果。

下面的查询在其 FROM 子句中引用函数返回的表:

SELECT *
FROM LargeOrderShippers( $500 )



说明 对于用户定义函数返回的表,text in row 表选项自动设置为 256。该值无法更改。不能使用 READTEXT、WRITETEXT 和 UPDATETEXT 语句来读取或写入该表中的任何 text、ntext 或 image 列的一部分。有关更多信息,请参见 Text in Row 数据。

posted @ 2008-5-26 14:39:04 aku1 阅读全文 | 回复(0) | 引用通告 | 编辑

发表评论:

    昵称:
    密码: (游客无须输入密码)
    主页:
    标题:
Powered by Oblog.