sql函数和存储.ppt

1、第五章 函数和存储过程,1.表值函数和标量值函数 2.系统存储过程 3.用户自定义存储过程及执行,标量函数,标量函数返回在RETURNS子句中定义的单个数据值。函数返回类型是除text、next、image、cursor和timestamp外的任何数据类型。 标量函数的创建 语法格式如下： CREATE FUNCTIONschema_name.function_name (parameter_nameAStype_schema_name.parameter_data_type =default ,n ) RETURNS return_data_type WITH ,n AS BEGIN,标量函

2、数,function_body RETURN scalar_expression END 参数描述： schema_name：用户定义函数所属的架构的名称。 function_name：用户定义函数的名称。函数名称必须符合有关标识符的规则，并且在数据库中以及对其架构来说是唯一的。即使未指定参数，函数名称后也需要加上括号。 parameter_name：用户定义函数的参数，可声明一个或多个参数。,标量函数,例：定义标量函数STUDENT_PASS()，统计学生考试是否合格的信息。代码如下： USE student GO CREATE FUNCTION student_pass_info(grad

3、e 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 课程注册.学号=学生.学号,表值函数,用户定义表值函数可分为内联表值函数和

4、多语句表值函数。函数返回table数据类型。 创建内联表值函数 对于内联表值函数，没有函数主体，表是单个SELECT语句的结果集。语法格式如下： CREATE FUNCTIONschema_name.function_name (parameter_nameAStype_schema_name.parameter_data_type =default ,n ) RETURNS TABLE WITH ,n AS RETURN()select_stmt ;,表值函数,参数描述： schema_name：用户定义函数所属的架构的名称。 function_name：用户定义函数的名称。函数名称必须符合

5、有关标识符的规则，并且在数据库中以及对其架构来说是唯一的。即使未指定参数，函数名称后也需要加上括号。 parameter_name：用户定义函数的参数。可声明一个或多个参数。 type_schema_name.parameter_data_type：参数的数据类型及其所属的架构，后者为可选项。 =default：参数的默认值。 select_stmt：定义内联表值函数的返回值的单个SELECT语句,表值函数,例：定义内联表值函数TEACHER_COURSE()，函数根据参数返回教师开课的信息。代码如下： USE student GO CREATE FUNCTION TEACHER_COURSE

6、(teacher_id char(12) RETURNS TABLE AS,表值函数,RETURN (SELECT DISTINCT 教师.教师编号,教师.姓名,系部.系部名称,课程.课程名,专业.专业名称 FROM 教师,系部,课程,教师任课,专业 WHERE 教师.教师编号=teacher_id AND 教师.系部代码=系部.系部代码 AND 教师任课.教师编号=教师.教师编号 and 课程.课程号=教师任课.课程号 AND 教师任课.专业代码=专业.专业代码) GO,表值函数,表值函数,在查询分析器中输入并执行以下代码 USE student GO SELECT * FROM TEACH

7、ER_COURSE(100000000002) GO,表值函数,存储过程概述,什么是存储过程 一组预先编释好的代码，可以做为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次无须编译可以直接调用执行。,允许模块化编程，增强代码的重用性和共享性 使用存储过程可以加快程序的运行速度. 使用存储过程可以减少网络流量。 存储过程可以提高数据库的安全性。,存储过程的优点,1、系统存储过程 2、用户存储过程 3、临时存储过程 4、扩展存储过程 5、远程存储过程,存储过程的分类,系统存储过程是由系统提供的过程，可以作为命

8、令直接执行。系统存储过程存储在master数据库中，其前缀为sp_。系统存储过程可以在任意一个数据库中执行。 用户存储过程是创建在用户数据库中的存储过程。其名称前面一般不加sp_前缀。主要在应用程序中使用，以完成特定的任务。 临时存储过程属于用户存储过程。如果用户存储过程前面加上符号“#”，则该存储过程称为局部临时存储过程，只能在一个用户会话中使用。,存储过程的分类,4、扩展存储过程是在SQL Server环境之外执行的动态链接库DLL，其前缀为xp_。尽管这些动态链接库在SQL Server环境之外，但它们可以被加载到SQL Server系统中，并且按照存储过程的方式执行。 5、远程存储过程

9、是指从远程服务器上调用的存储过程，或者是从连接到另外一个服务器上的客户机上调用的存储过程。,存储过程的分类,创建存储过程,在SQL Server中使用三种方法创建存储过程 ： 使用创建存储过程向导创建存储过程。 利用SQL Server 企业管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,创建存储过程,CREATE PROCEDURE 存储过程名;数字 参数 数据类型 =defaultOUTPUT With RECOMPILE -重新编译 | ENCRYPTION -加密 | RECOMPILE , ENCRYPTION AS SQ

10、L语句,不带参数的存储过程： CREATE PROC pr_例一 AS SELECT * FROM STUDENT GO EXEC pr_例一,查看存储过程,用于显示存储过程的参数及其数据类型 sp_help objname=存储过程名称 用于显示存储过程的源代码 sp_helptext objname= 存储过程名称 用于显示和存储过程相关的数据库对象 sp_depends objname= 存储过程名称 用于返回当前数据库中的存储过程列表 sp_stored_procedures objname= 存储过程名称,执行存储过程,如果对存储过程的调用是批处理的第一条语句，则可以省略exec关键字，直接使用存储过程的名字调 用这个存储过程写入存储过程名直接执行。 Execute 存储过程名,删除存储过程,Drop procedure 名字,n,修改存储过程,Alter procdure 存储过程名 with encryption As SQL语句,重命名存储过程,sp_rename 原存储过程名称,新存储过程名称,使用T-SQL语句创建存储过程