数据库设计开发案例教程（七）.ppt

7.3自定义函数 SQL Server系统不仅提供了大量的内置函数，而且还允许用户根据需要创建用户自定义函数。用户自定义函数是白一个或多个T-SQL语句组成的子程序，它接收参数，返回操作结果，返回值可以是单个的标量值或结果集。因此，用户自定义函数可分为：标量函数和表值函数。其中，表值函数又可分为：内联表值函数和多语句表值函数。 可使用CREATE FUNCTION语句创建函数，使用ALTER FUNCTION语句修改函数，使用DROP FUNCTION语句删除函数。用户自定义函数名必唯一。 7.3自定义函数 7.3.1 创建函数 (1)创建标量函数 创建标量函数的语法形式如下： CREATE FUNCTION [schema_name. ] function_ name ( [ { @ parame,ter_name [AS ] parameter_data_type [ = default ]} [ , .. n] ] ) RETURNS return_data_type [AS] BEGIN function_body RETURN expression END 7.3自定义函数 (2)创建表值函数 表值函数返回一个表作为输出。在RETURNS子句中定义函数要返回的类型为table，在RETURN子句中给出函数要返回的一个直接SELECT语句结果集或一个临时表中的数据。 内联表值函数：函数直接返回一个SELECT语句的结果集。并且，在内联函数中不使用BEGIN和END语句包含函数体，直接使用RETURN子句返回一个SELECT语句的结果集或多个SELECT语句的集合运算，结构比较简单。 多语句表值函数：函数返回一个临时表作为输出。在函数的RETURNS子句中首先创建一个临时表，然后在BEGIN和END定义的函数体中可以通过多条话句向临时表中插入值，并将临时表中的数据作为返回结果。 内联表值函数的语法形式如下： CREATE FUNCTION [ schema_name. ] f unction _ name ( [ { @ parameter _name (AS ] parameter_data_type [ = default ] } [ , .. n J ] ) RETURNS TABLE [AS] RETURN (select-stmt) 其中，select-stmt是函数返回值的单个查询语句。 创建多语句表值函数的语法形式如下： CREATE FUNCTION [schema_name. ] function_ name ( [ { @ parameter_name [ AS ] parameter_data_type [ = de fault ] } [ , . . n ] ] ) RETURNS@ return_variable TABELE table_type_definition [AS ] BEGIN function_body RETURN END 其中， table_ type_ definition定义表数据类型，包括列的定义和约束。 7.3.2 函数的调用 在调用用户自定义函数时，如果调用的是标量函数，则必须提供架构名，其语法格式为： schema_name. function([ argument_expr)[,…]) 其中，argument_ expr表示实际参数值。 如果调用的是表值函数，则可以不提供架构名。 用户可以将调用的函数用在赋值语句中，或作为表达式的操作数，或用在SQL命令中。 7.3.2 函数的调用 7.3.3 修改函数 用户可以使用DROP FUNCTION命令删除函数，其语法形式如下： DROP FUNCTION { [ schema_name. ] function_name } [ , . . n] ? 1.用户自定义函数应用举例 以下示例： 创建自定义标量函数TOTAL()，用来计算任意两数之和。 CREATE FUNCTION TOTAL(@expr1 AS int,@expr2 AS int) RETURNS int BEGIN DECLARE @my_total int SELECT @my_total=@expr1+@expr2 RETURN @my_total END GO 用命令行调用TOTAL()函数： SELECT dbo.TOTAL (10,20) AS 两数之和 显示结果，如图7-27所示。 图7-27 7.3.2 函数的调用 以下示例： 创建标量函数sumclass()统计班级总数。 USE MySchool GO CREATE FUNCTION