第6章 存储过程与触发器.pptVIP

第6章 存储过程与触发器 存储过程概述、触发器概述 存储过程与触发器的创建及使用方法、触发器的概念与使用方法 学习目标 了解： SQL Serve存储过程、触发器的特点与作用。 理解： SQL Serve存储过程、触发器的基本概念；查看、修改、删除存储过程和触发器的基本概念。 掌握： 存储过程创建、执行以及参数应用的方法；触发器创建的方法。 6.1 存储过程概述 6.1.1 存储过程的特点和类型 6.1.2 存储过程的创建和执行 6.1.3 存储过程的参数和执行状态 6.1.4 存储过程的查看和修改 6.1.5 存储过程的删除 6.1 存储过程概述 存储过程(是一个数据库对象) 将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务。 存储过程类似于dos系统中的批处理文件。 存储过程是SQL语句和可选控制流语句的预编译集合; 是一种封装重复任务操作的方法; 它以一个名称存储并作为一个单元处理。 6.1.1 存储过程的特点和类型 存储过程的特点 （1）模块化编程； （2）代码执行效率高； （3）数据查询效率高； （4）减少网络流量； （6）调用方便； （7）延迟名称解析。 6.1.1 存储过程的特点和类型 存储过程的类型(3种) （1）系统存储过程 是由数据库系统自身所创建的存储过程。 在master数据库中，并以“sp_” 开头命名。 （2）用户定义存储过程 在用户数据库中由用户所创建的存储过程； 存储过程的名称前没有“sp_”前缀。 （3）扩展存储过程 通过使用程序语言编写的动态链接库（DLL）实现，扩展了T-SQL的功能； 扩展存储过程以“xp_”为前缀。 6.1.2 存储过程的创建和执行 存储过程的创建 即如何定义存储过程名称、参数、 SQL 语句主体。 有3种方法创建存储过程： 使用企业管理器 使用向导 使用Transact-SQL语言 6.1.2 存储过程的创建和执行 使用企业管理器创建存储过程 6.1.2 存储过程的创建和执行 使用向导创建存储过程 6.1.2 存储过程的创建和执行 使用CREATE PROCEDURE语句创建 CREATE PROC[EDURE] procedure_name [;number] [{ @parameter data_type} [VARYING][=default] [OUTPUT]][,...n] [WITH { RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION }] [FOR REPLICATION] AS sql_statement [...n] 注意： 只能在当前数据库中创建; 由数据库所有者创建，但也可授权其他用户创建； 存储过程命名必须遵守标识符命名规则； 不能将CREATE PROCEDURE与其他SQL语句组合在一起。 存储过程的最大大小为 128 MB。 6.1.2 存储过程的创建和执行——举例 【例6.1】创建一个带SELECT查询语句的名为“Average_Score”的存储过程。从学生表、课程表、选课表中返回每位修课学生的课程平均分。 USE student_db GO ‘ 存储过程的内容如下 CREATE PROCEDURE Average_Score AS SELECT St_Info.St_Name, AVG(S_C_Info.Score) AS AvgScore FROM St_Info, S_C_Info, C_Info WHERE St_Info.St_ID = S_C_Info.St_ID AND S_C_Info.C_No = C_Info.C_No GROUP BY St_Info.St_Name GO 6.1.3 存储过程的参数和执行状态 执行存储过程 使用EXECUTE命令或直接按其名称执行存储过程 执行存储过程必须具有执行该过程的权限 若存储过程是批处理中的第1条语句，EXECUTE命令可以省略。 语法格式： [[EXEC[UTE]] { [@return_status=] procedure_name [;number]|@procedure_name_var } [[@parameter=]{value|@variable [OUTPUT]|[DEFAULT]] [,...n] [WITH RECOMPILE] 6.1.2 存储过程的创建和执行 【例6.2】执行例【例6.1】所创建的存储过程Average_Score。 注意： （1）可以使用查询分析器来执行存储过程，从而检验存储过程编写是否正确。 （2）对于带参数的存储过程，也可以在查询分析器中的对象浏览器下以调试方式运