开发函数

函数的优点和缺点

优点 类似于视图，可以将它们用在表达式或者

select 语句的 from 子句中。 比视图更灵活，可以接收参数 具有存储过程的优点，函数也是预先经过

编译和优化的。 缺点

不易移植

函数的种类 返回单值的标量函数 类似于视图的可更新内嵌表函数 使用代码创建结果集的多语句表函数

标量函数

标量函数是返回单个值的函数。这类函数可以接收多个参数，进行计算，然后返回单个值。可以在 SQL Server 的表达式（包括 check 约束中的表达式）中使用这些用户定义函数，而这些函数将会使用 return 命令来返回一个值。

标量函数

与定义存储过程的参数类似，定义用户函数的输入参数时需要给出数据类型定义，如果有必要，还可以为其提供默认值。但与存储过程不同的是，即使为函数的参数指定了默认值，在调用函数时仍然需要为它提供相应的参数值。也就是说，具有默认值的参数也不是可选参数。如果要使用参数的默认值调用函数，可以在调用时将关键字 default 传递给函数。

标量函数

CREATE FUNCTION FunctionName (InputParameters)

RETURNS DataType

AS

BEGIN

Code

RETURN Expression

END

标量函数例子

CREATE FUNCTION dbo.fMultiply (@A INT, @B INT = 3)

RETURNS INT

AS

BEGIN

RETURN @A * @B

End

调用标量函数

在表达式中任何可以使用单个值的地方都可以使用标量函数。

调用标量函数时，必须始终使用由两个部分构成的名字（ owner.name ）。

SELECT dbo.fMultiply (3,4)

SELECT dbo.fMultiply (7, DEFAULT)

标量函数例子CREATE FUNCTION dbo.fGradeLevel (@Grade INT = 0)RETURNS NCHAR(3)ASBEGIN

IF @Grade < 60RETURN ' 不及格 '

IF @Grade >= 60 AND @Grade < 70RETURN ' 及格 '

IF @Grade >= 70 AND @Grade < 80RETURN ' 中 '

IF @Grade >= 80 AND @Grade < 90RETURN ' 良好 '

IF @Grade >= 90RETURN ' 优秀 '

RETURN ' 错误 'End

调用标量函数 dbo.GradeLevel

SELECT student.sno, sname, cname,

dbo.fGradeLevel (grade)

FROM STUDENT

JOIN SC

ON student.sno=sc.sno

JOIN COURSE

ON sc.cno=course.cno

内嵌表函数

CREATE FUNCTION FunctionName (InputParameters)

RETURNS Table

AS

RETURN (Select Statement)

内嵌表函数优点

内嵌表函数非常类似于视图，它们都包含有一条存储的 select 语句。

内嵌表函数不仅具有视图的全部优点，而且还多了视图所没有的两个新优点：预先编译和可以使用参数。如同视图一样，如果所包含的 select 语句是可以更新的，内嵌表值函数就是可以更新的。

内嵌表函数例子CREATE FUNCTION fStudent_GradeLevel (@cname

nchar(10))RETURNS TableASRETURN(SELECT student.sno, sname, cname, dbo.G

radeLevel (grade) AS GradeLevelFROM STUDENT

JOIN SCON student.sno=sc.sno

JOIN COURSEON sc.cno=course.cno

WHERE cname=@cname OR @cname IS NULL)

使用内嵌表函数

SELECT GradeLevel, Count(*)

FROM dbo.fStudent_GradeLevel(' 数学 ')

GROUP BY GradeLevel

SELECT GradeLevel, Count(*)

FROM dbo.fStudent_GradeLevel(NULL)

GROUP BY GradeLevel

多语句表函数 多语句表值用户定义函数既可以像标量函数

那样包含复杂的代码，也可以像内嵌表值函数那样返回一个结果集。这类函数会创建一个表变量，并使用代码对它进行填充。然后，它会将这个表变量返回，以便在 select 语句中使用它。

多语句表值用户定义函数的主要优点是：可以用代码产生复杂的结果集，然后方便地在 select 语句中使用它。因此，可以使用这些函数来替代返回结果集的存储过程。 。

多语句表函数

CREATE FUNCTION FunctionName (InputParamenters)

RETURNS @TableName TABLE (Columns)

AS

BEGIN

Code to populate table variable

RETURN

END

多语句表函数CREATE FUNCTION fElectiveList()RETURNS @ElectiveList TABLE

(cname NCHAR(10),list NCHAR(128))

ASBEGIN

DECLARE @old_cname NCHAR(10),@new_cname NCHAR(10),@sname NCHAR(3),@list NVARCHAR(128)

DECLARE cList CURSORFOR SELECT cname, sname

FROM studentJOIN sc ON student.sno = sc.snoJOIN course ON sc.cno = course.cno

ORDER BY cnameOPEN cList

多语句表函数SET @list = ''FETCH cList INTO @old_cname, @snameWHILE @@Fetch_Status = 0

BEGINSET @list = @list+@snameFETCH cList INTO @new_cname, @snameIF @new_cname <> @old_cname OR @@Fetch_Status <> 0BEGIN

INSERT INTO @ElectiveList (cname, list)VALUES (@old_cname, @list)

SET @old_cname = @new_cnameSET @list = ''

ENDEND

CLOSE cListDEALLOCATE cList

RETURNEND

调用多语句表函数

SELECT * FROM dbo.fElectiveList()

删除和修改函数

DROP FUNCTION 函数名 ALTER FUNCTION 函数名

函数定义