【T-MAC学习笔记17之--浅谈UDF】

feixianxxx 2009-11-25 03:59:55

加精

好久不写了,今天来说说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 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      datacol

			----------- ----------

			1           a

			2           d

			3           c

			*/

		-------只能一列列插入---------------

	b.check约束 ----（可以一定程度上替代触发器的高成本）

	

	c.primary ,key unique 约束-----（在UDF的计算列上建立）

	前提:UDF必须是绑定到架构上的；计算列出来的结果要精确等

	利用上面的表

	 --首先创建一个函数 (不带架构的)

	 create FUNCTION dbo.fn_add1(@i AS INT) RETURNS INT

  --WITH SCHEMABINDING

		AS

		BEGIN

		  RETURN @i + 1;

		END

		GO

	--创建带有unique的col1列

		Alter Table Dbo.T1

	  	Add Col1 As Dbo.Fn_Add1(Keycol) Constraint Uq_T1_Col1 Unique;

			Go	

   /*

   不能在索引或统计信息中使用 表'dbo.T1' 的列 'col1'，也不能将该列用作分区键，因为它不具有确定性。*/

  --修改函数 绑定到架构

  	alter FUNCTION dbo.fn_add1(@i AS INT) RETURNS INT

		  WITH SCHEMABINDING  ----加上绑定

		AS

		BEGIN

		  RETURN @i + 1;

		END

		GO

  --再次添加带有unique的col1列

  Alter Table Dbo.T1

	  Add Col1 As Dbo.Fn_Add1(Keycol) Constraint Uq_T1_Col1 Unique;

		Go

 

  --如果想包含Primary key  约束的计算列 

  --先删除我们T1表上原来的primary key 约束

  	ALTER TABLE dbo.T1 DROP CONSTRAINT PK_T1;

  --然后再加你想要的

  	ALTER TABLE dbo.T1

		  ADD col2 AS dbo.fn_add1(keycol)

		    CONSTRAINT PK_T1 PRIMARY KEY;

  --不能对表 'T1' 中的列 'col2' 定义 PRIMARY KEY 约束。必须将计算列持久化，并且该列不可为 Null。

  --这里记住 对一个列加 primary key 首先就要保证它不能出现NULL 而SQL默认是为NULL的

  ALTER TABLE dbo.T1

	  ADD col2 AS dbo.fn_add1(keycol) PERSISTED NOT NULL --SQL05新出的关键词 persisted 

	    CONSTRAINT PK_T1 PRIMARY KEY;

	GO

	-- 2000

	ALTER TABLE dbo.T1

	  ADD col2 AS ISNULL(dbo.fn_add1(keycol), 0) --将NULL转换成0

	    CONSTRAINT PK_T1 PRIMARY KEY;

	GO

	--也可以修改下 列的熟悉后再加primary key 一样的

	

2.表值UDF

  它分为内联表值UDF和多语句表值UDF

  a.内联表值UDF

  它非常类似于视图,可以说它就是带有参数的视图.它甚至可以和视图一样去更新基表里的数据.

	  create table tb(id int, value int)

		insert tb values(1,2)

		insert tb values(2,3)

		insert tb values(3,2)

	 go

		create function sum_s() 

		returns table

		as

		return 

			select id,value

			from tb 

			where id=2

		--更新函数

		update sum_s() set value=9 

		--查询数据

		select * from tb 

		/*

		id          value

		----------- -----------

		1           2

		2           9

		3           2*/

  禁: 不可以在主体内使用Begin/end 块.

  

  b.多语句表值UDF

  当你无法用单个查询就返回一个表的时候,你可以用多语句表值UDF来实现.--它更像是proc(存储过程)

  关于这个多语句表值函数UDF我也不知道说什么 感觉按着基本语法来也就好了.基础的语法大家参考MSDN吧

  

  既然上面提到了PROC 那我就写点 将存储过程重写为函数 

  我们如何如何确定是否应该将现有存储过程逻辑重写为用户定义函数呢？

  例如，如果希望直接从查询中调用存储过程，可将代码重新打包为用户定义函数。

  一般来说，如果存储过程返回单个结果集，则定义表值函数。如果存储过程计算标量值，则定义标量函数。

  PROC转换成UDF的条件：

  1.逻辑可以用单个 SELECT 语句表达，但它是存储过程而不是视图，只是由于需要参数。可以使用内联表值函数处理这种情况。

	3.存储过程不执行更新操作（除了对表变量以外）。

	4.不需要动态 EXECUTE 语句。

	5.存储过程返回一个结果集。

	6.存储过程的主要用途是生成要加载到临时表中的中间结果，然后在 SELECT 语句中查询临时表。可以使用表值函数编写 INSERT...EXEC 语句。

  INSERT #temp EXEC sp_getresults

	SELECT ...

	FROM #temp, t1

	WHERE ...

 -------》》》 这里将存储过程 sp_getresults 写成 函数fn_results() 这样函数就可以当做#temp这个临时表来用了

	SELECT ...

	FROM fn_results(), t1

	WHERE ...