№03 函数

自定义函数

         MSSQL2008带有很多系统函数，比如：SUBSTRING,CONVERT等等，自定义函数和这些函数的使用方式基本相同。从下面的例子中可以看出。DB函数有一些容易出错的地方要注意，它必须有返回值，其次它无法更改DB，即在其中不能使用DML和DDL，为了防止这种操作，所以函数中也不能执行存储过程（防止在存储过程中使用DML和DDL）。

         SQLSERVER MANAGEMENT STUDIO中新建函数时(自定义函数)有三个分类：标量值函数、内联表函数、多表值函数，下面分别说明。

测试用数据

（来自AdventureWorks2008R2中的HumanResources.Department）

A.标量函数

返回值是一个确定类型的标量值。

CREATE FUNCTION [dbo].[getDepartmentName] 
(
    @departmentId Varchar(20) --参数
)
RETURNS VARCHAR(20) --返回值
AS
BEGIN
    DECLARE @name VARCHAR(20)='';
    DECLARE dep CURSOR FOR SELECT Name FROM HumanResources.Department WHERE --定义游标
    DepartmentId = @departmentId;
    OPEN dep;
    FETCH dep into  @name;
    RETURN @name;
END

以上是一个标量值函数，从Department中找出DepartmentId=传入参数的部门名。

执行：

SELECT getDepartmentName('1');

结果：

B.内联表值函数

内联表值函数 返回一个查询结果，函数定义的主体只能是仅有的一个查询语句：RETURNS TABLE AS RETURN(...)，注意这是一对括号，里面只能有一个查询作为返回值。

CREATE FUNCTION LJS_GetDepartment 
(	
	@DepartmentId Varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
	 SELECT * FROM HumanResources.Department WHERE DepartmentID=@DepartmentId	
)

执行：

SELECT * FROM LJS_GetDepartment('1')

结果：

C.多语句表值函数

多语句表值函数 是内联表值函数的增强版，函数定义主体可以存在程序逻辑：AS BEGIN ... END

CREATE FUNCTION LJS_GetDepartmentByCondition 
(
	@IsOdd VARCHAR(1)
	 
)
RETURNS 
@Table_Var TABLE 
(
	ID smallint, 
	NAME VARCHAR(30)
)
AS
BEGIN
	IF @IsOdd='Y'
	BEGIN
		INSERT @Table_Var SELECT DepartmentID,Name FROM HumanResources.Department T1
		WHERE T1.DepartmentID%2<>0 ORDER BY DepartmentID;
	END	
	ELSE
	BEGIN
		INSERT @Table_Var SELECT DepartmentID,Name FROM HumanResources.Department T1
		WHERE T1.DepartmentID%2=0 ORDER BY DepartmentID;
	END
	RETURN
END

当@IsOdd为‘Y’时， LJS_GetDepartmentByCondition返回DepartmentID为奇数的行；为其它值时，返回DepartmentID为偶数的行。

执行：

SELECT * FROM LJS_GetDepartmentByCondition('N');

结果：