日期:2014-05-17  浏览次数:20537 次

【T-MAC学习笔记17之--浅谈UDF】
好久不写了,今天来说说UDF(用户自定义函数)


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 N