№03 函数

来源：互联网发布：科学计算 python 编辑：程序博客网时间：2024/06/05 21:50

自定义函数

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) --返回值ASBEGIN    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 ASRETURN ( 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))ASBEGINIF @IsOdd='Y'BEGININSERT @Table_Var SELECT DepartmentID,Name FROM HumanResources.Department T1WHERE T1.DepartmentID%2<>0 ORDER BY DepartmentID;ENDELSEBEGININSERT @Table_Var SELECT DepartmentID,Name FROM HumanResources.Department T1WHERE T1.DepartmentID%2=0 ORDER BY DepartmentID;ENDRETURNEND

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

执行：

SELECT * FROM LJS_GetDepartmentByCondition('N');

结果：

0 0