sql函数和存储.ppt

第五章 函数和存储过程 1.表值函数和标量值函数 2.系统存储过程 3.用户自定义存储过程及执行 标量函数 标量函数返回在RETURNS子句中定义的单个数据值。函数返回类型是除text、next、image、cursor和timestamp外的任何数据类型。 标量函数的创建 语法格式如下： CREATE FUNCTION[schema_name.]function_name ([{@parameter_name[AS][type_schema_name.]parameter_data_type [=default]} [,…n] ]) RETURNS return_data_type [WITH function_option [,…n]] [AS] BEGIN 标量函数 function_body RETURN scalar_expression END 参数描述： schema_name：用户定义函数所属的架构的名称。 function_name：用户定义函数的名称。函数名称必须符合有关标识符的规则，并且在数据库中以及对其架构来说是唯一的。即使未指定参数，函数名称后也需要加上括号。 @parameter_name：用户定义函数的参数，可声明一个或多个参数。 标量函数 例：定义标量函数STUDENT_PASS()，统计学生考试是否合格的信息。代码如下： USE student GO CREATE FUNCTION student_pass_info(@grade tinyint) RETURNS char(8) BEGIN DECLARE @info char(8) IF @grade=60 SET @info=通过 ELSE SET @info=不合格 RETURN @info END GO 标量函数 标量函数 在查询分析器中输入以下代码，执行后结果. USE student GO SELECT 课程注册.学号,学生.姓名,课程.课程名,dbo.student_pass_info(成绩)AS 是否通过 FROM 学生,课程注册,课程 WHERE 课程注册.课程号=课程.课程号AND 课程注册.学号=学生.学号 表值函数 用户定义表值函数可分为内联表值函数和多语句表值函数。函数返回table数据类型。 创建内联表值函数 对于内联表值函数，没有函数主体，表是单个SELECT语句的结果集。语法格式如下： CREATE FUNCTION[schema_name.]function_name ([{@parameter_name[AS][type_schema_name.]parameter_data_type [=default]} [,…n] ]) RETURNS TABLE [WITH function_option [,…n]] [AS] RETURN[()select_stmt[]] [;] 表值函数 参数描述： schema_name：用户定义函数所属的架构的名称。 function_name：用户定义函数的名称。函数名称必须符合有关标识符的规则，并且在数据库中以及对其架构来说是唯一的。即使未指定参数，函数名称后也需要加上括号。 @parameter_name：用户定义函数的参数。可声明一个或多个参数。 [type_schema_name.]parameter_data_type：参数的数据类型及其所属的架构，后者为可选项。 [=default]：参数的默认值。 select_stmt：定义内联表值函数的返回值的单个SELECT语句 表值函数 例：定义内联表值函数TEACHER_COURSE()，函数根据参数返回教师开课的信息。代码如下： USE student GO CREATE FUNCTION TEACHER_COURSE(@teacher_id char(12)) RETURNS TABLE AS 表值函数 RETURN (SELECT DISTINCT 教师.教师编号,教师.姓名,系部.系部名称,课程.课程名,专业.专业名称 FROM 教师,系部,课程,教师任课,专业 WHERE 教师.教师编号=@teacher_id AND 教师.系部代码=系部.系部代码 AND 教师任课.教师编号=教师.教师编号 and 课程.课程号=教师任课.课程号 AND 教师任课.专业代码=专业.专业代码) GO 表值函数 表值函数 在查询分析器中输入并执行以下代码 USE student GO SELECT * FROM TEACHER_COURSE(100000000