日期:2014-05-18 浏览次数:20678 次
UDF分为标量UDF和表值UDF。
ps:每种都有CLR UDF的在05里面 本人不熟悉 所以只写T-SQL的
使用场合
——在 Transact-SQL 语句(如 SELECT)中
——在调用该函数的应用程序中
——在另一个用户定义函数的定义中
——用于参数化视图或改进索引视图的功能
——用于在表中定义列
——用于为列定义 CHECK 约束
——用于替换存储过程
三个优点:
a.允许模块化程序设计
b.执行速度更快 通过缓存计划并在重复执行时重用它来降低 Transact-SQL 代码的编译开销
c.减少网络流量
两个禁:
a.在UDF内部不可以创建或者访问临时表,也不会动态执行,可以使用表变量.
b.在UDF内部不可以修改表中的数据或者调用产生副作用的函数(比如rand,newid,getdate()),不过这个不是绝对的
create function k_s()
returns int
as
begin
create table #(a int)
insert a select floor(rand()*10)
declare @s int
set @s=(select max(a) from # )
return @s
end
/*
消息 2772,级别 16,状态 1,过程 k_s,第 5 行
无法从函数内访问临时表。*/
---修改成表变量
create function k_s()
returns int
as
begin
declare @# table (a int)
insert @# select floor(rand()*10)
declare @s int
set @s=(select max(a) from @# )
return @s
end
/*
消息 443,级别 16,状态 1,过程 k_s,第 7 行
在函数内对带副作用的运算符 'rand' 的使用无效。*/
--上面我说不是绝对的 因为在SQL2000中确实不允许调用这些不确定的函数,但是SQL2005会检查UDF的属性是否产生副作用,没有副作用 你还是调用的 比如
CREATE FUNCTION dbo.fn_getdate() RETURNS DATETIME
AS
BEGIN
RETURN GETDATE();
END
GO
两个函数选项:
a.在函数头上使用 SCHEMABINDING(绑定),这样防止删除引用的基础表或者修改引用的列导致函数失去效果
b.当你想在函数参数为NULL时候不调用函数,直接返回Null,可以使用returns null on null input ,这样可以提高代码性能
1.标量UDF
要求:
a.必须包含begin/end块定义函数主体
b.调用时候必须包含架构(dbo.fnname) ---> 可以用exec 直接+函数名 但是我试验出来没有结果 - -|| 应该是无意义操作
create function ksss (@n int )
returns int
as
begin
return @N
end
select dbo.ksss(1)
/*
-----------
1
*/
----用exec 脱离架构调用标量函数
create table #k(a int)
insert #k
exec ksss 1
/*
a
-----------
(0 行受影响
*/
c.调用的时候的参数要一个不少 ,有默认值的可以指定default关键字
(1)约束中的UDF
a.default-----(不能使用表中的列)
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY CHECK (keycol > 0),
datacol VARCHAR(10) NOT NULL
);
GO
IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL
DROP FUNCTION dbo.fn_T1_getkey;
GO
CREATE FUNCTION dbo.fn_T1_getkey() RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
ELSE (SELECT MIN(keycol + 1)
FROM dbo.T1 AS A
WHERE NOT EXISTS
(SELECT *
FROM dbo.T1 AS B
WHERE B.keycol = A.keycol + 1))
END;
END
GO
-- 为列加默认约束
ALTER TABLE dbo.T1 ADD DEFAULT(dbo.fn_T1_getkey()) FOR keycol;
GO
--测试
INSERT INTO dbo.T1(datacol) VALUES('a');
INSERT INTO dbo.T1(datacol) VALUES('b');
INSERT INTO dbo.T1(datacol) VALUES('c');
DELETE FROM dbo.T1 WHERE keycol = 2;
INSERT INTO dbo.T1(datacol) VALUES('d');
go
SELECT * FROM dbo.T1;
GO
/*
keycol