第十章存储过程触发系器及自定义函数2.pptVIP

用户自定义函数 10.3.2 创建自定义函数 1.创建标量值函数： 标量值函数的函数体由一条或多条T－SQL语句组成，写在BEGIN与END之间。 其语法格式如下： CREATE FUNCTION 函数名 ([@形参名 数据类型[,…,n]]) RETURNS 返回值数据类型 [WITH ENCRYPTION] [AS] BEGIN T-SQL语句或语句块 RETURN 返回表达式 END 参数说明如下： 返回值的数据类型不能是text、ntext、image和timestamp类型。 在BEGIN…END之间，必须有一条RETURN语句，用于指定返回表达式，即函数的值。 第十章 存储过程、触发器、自定义函数 SQL Server 2008 用户自定义函数 【例10-6】定义函数EProduct_Number，当给出一个客户ID，返回该客户所拥有的产品数量。 【分析】 该自定义函数接收的参数是客户ID，数据类型应与EProduct中的CID一致；返回的值是产品数，该数据量一般不大，可以设为smallint类型。 代码如下： CREATE FUNCTION EProduct_Number (@EP_CID int) RETURNS smallint AS BEGIN DECLARE @epnum smallint SELECT @epnum =count(*) FROM EProduct WHERE CID=@EP_CID RETURN @epnum END GO 第十章 存储过程、触发器、自定义函数 SQL Server 2008 标量值函数的调用 在select语句中调用： 调用形式： Select 所有者名称.函数名称(实参1，……实参n) 实参为已赋值的局部变量或表达式，实参的顺序要与函数创建时的顺序完全一致。 【例】 对创建的函数EProduct_Table_1进行调用。 SELECT dbo.EProduct_Table_1( 利用Exec语句执行 调用形式： EXEC 变量名= 所有者名称.函数名称(实参1，……实参n) 或 EXEC 变量名=Schema_name.function_name 形参名1=实参1，……形参名n=实参n 【例】利用Exec调用函数getAverAge。 在查询编辑器中，输入以下代码： USE jxgl DECLARE @age int EXEC @age=dbo.getaverage @tabname=students,@sex=男 PRINT 男学生的平均年龄：+CONVERT(varchar(10),@age) 用户自定义函数 2.创建内联表值函数： 语法格式如下： CREATE FUNCTION 函数名 ([@形参名 数据类型[,…,n]]) RETURNS Table [WITH ENCRYPTION] [AS] RETURN(SELECT查询语句) 参数说明如下： 内联表值函数没有函数体。 RETURNS Table子句说明返回值是一个表。 RETURN子句中的SELECT语句是返回表中的数据。 第十章 存储过程、触发器、自定义函数 SQL Server 2008 用户自定义函数 【例10-7】定义函数EProduct_Table，当给出一个客户ID，返回该客户所拥有的产品号码。 代码如下： CREATE FUNCTION EProduct_Table (@EP_CID int) RETURNS Table AS RETURN (SELECT ENO,EName FROM EProduct WHERE cid=@EP_CID 第十章 存储过程、触发器、自定义函数 SQL Server 2008 内联表值函数 内联表值函数的调用 内联表值函数只能通过Select语句进行调用，调用时，可以仅使用函数名。 【例】 调用函数getCourseScore，查询学生张小峰所选修的课程及其成绩。 USE jxgl SELECT * FROM getCourseScore(‘张小峰’) GO 3.多语句表值函数 多语句表值函数和内联表值函数都返回表，二者的不同之处在于：内联表值函数没有函数主体，返回的表是单个Select语句的结果集；而多语句表值函数在Begin……End块中定义的函数主体由T-SQL语句序列构成，这些语句可生成记录行并将行插入到表中，最后返回表。 用户自定义函数 创建多语句表值函数： 语法格式如下： CREATE FUNCTION 函数名 ([@形参名 数据类型[,…,n]]) RETURNS @返回变量 Table (表结构定义) [WITH ENCRYPT