第9章存储过程与触发器 2021/2/20 Information College. ChangJun
1 2021/2/20 Information College · ChangJun 第9章 存储过程与触发器
[本章概要] 线出 9.1存储过程 9.2触发器 2021/2/20 Information College. ChangJun
2 2021/2/20 Information College · ChangJun [本章概要] 9.1 存储过程 9.2 触发器
大型数据库系统中,存储过程和触发器具有 很重要的作用。无论是存储过程还是触发器,都 是 SQL Serverl的数据库对象,都是SQL语句和流 程控制语句的集合。 2021/2/20 Information College. ChangJun
3 2021/2/20 Information College · ChangJun 大型数据库系统中，存储过程和触发器具有 很重要的作用。无论是存储过程还是触发器，都 是SQL Server的数据库对象，都是SQL 语句和流 程控制语句的集合
9.1存储过程 线出 9.1.1基本概念 什么是存储过程 存储过程( Stored procedure)是一组为了完 成特定功能的SQL语句和可选控制流语句的预编译 集合,存储在数据库内,以一个名称存储并作为 个单元处理。 特点: a)存储过程在创建时即在服务器上进行编译, 所以执行起来比单个SQL语句快。 b)存储过程的名字存放在当前数据库的 sysobjects系统表中,文本存放在 syscomments系 统表中。 2021/2/20 Information College. ChangJun
4 2021/2/20 Information College · ChangJun 9.1 存储过程 9.1.1 基本概念 1、什么是存储过程 存储过程（Stored Procedure）是一组为了完 成特定功能的SQL 语句和可选控制流语句的预编译 集合，存储在数据库内，以一个名称存储并作为一 个单元处理。 特点： a) 存储过程在创建时即在服务器上进行编译， 所以执行起来比单个 SQL 语句快。 b) 存储过程的名字存放在当前数据库的 sysobjects系统表中，文本存放在syscomments系 统表中
c)用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数)来执行它 d)存储过程可包含程序流、逻辑以及对数据 库的查询。它们可以接受参数、输出参数、返回 单个或多个结果集以及返回值。 e)可以从自己的存储过程内引用其它存储过 程,这可以简化一系列复杂语句。 存储过程的优点: a)执行速度快 b)模块化程序设计 c)减少网络通信量 d)保证系统的安全性 2021/2/20 Information College. ChangJun
5 2021/2/20 Information College · ChangJun c) 用户通过指定存储过程的名字并给出参数 （如果该存储过程带有参数）来执行它。 d) 存储过程可包含程序流、逻辑以及对数据 库的查询。它们可以接受参数、输出参数、返回 单个或多个结果集以及返回值。 e) 可以从自己的存储过程内引用其它存储过 程，这可以简化一系列复杂语句。 存储过程的优点： a) 执行速度快 b) 模块化程序设计 c) 减少网络通信量 d) 保证系统的安全性
2、分类 在 SQL Server2000中存储过程分为五类?m )系统存储过程 主要存储在 master数据库中并以sp为前缀, 系统存储过程主要是从系统表中获取信息,从而为 系统管理员管理 SQL Server提供支持。可以在其它 数据库中对其进行调用,在调用时不必在存储过程 名前加上数据库名。而且当创建一个新数据库时, 些系统存储过程会在新数据库中被自动创建 2)本地存储过程 在独立的用户数据库中被创建。 3)局部临时存储过程 存储在 tempdb内,名字以单个数字符号(#) 开始,在单个用户任务中有效。 2021/2/20 Information College. ChangJun
6 2021/2/20 Information College · ChangJun 2、分类 在SQL Server 2000中存储过程分为五类： 1) 系统存储过程 主要存储在master 数据库中并以sp_为前缀， 系统存储过程主要是从系统表中获取信息，从而为 系统管理员管理SQL Server 提供支持。可以在其它 数据库中对其进行调用，在调用时不必在存储过程 名前加上数据库名。而且当创建一个新数据库时， 一些系统存储过程会在新数据库中被自动创建。 2) 本地存储过程 在独立的用户数据库中被创建。 3) 局部临时存储过程 存储在 tempdb 内，名字以单个数字符号（#） 开始，在单个用户任务中有效
4)全局临时存储过程 存储在 tempdb内,名字以两个数字符号 (#)开始,在所有用户任务中有效 5)扩展存储过程 作为动态链接库(d11)在 SQL Server环境中 执行的方法,以xp为前缀。 2021/2/20 Information College. ChangJun
7 2021/2/20 Information College · ChangJun 4) 全局临时存储过程 存储在 tempdb 内，名字以两个数字符号 （##）开始，在所有用户任务中有效。 5) 扩展存储过程 作为动态链接库（dll）在SQL Server环境中 执行的方法，以xp_为前缀
9.1.2创建存储过程 线出 当创建存储过程时,需要确定存储过程的→ 个组成部分: a)所有的输入参数以及传给调用者的输出参 数 b)被执行的针对数据库的操作语句包括调用 其它存储过程的语句 c)返回给调用者的状态值以指明调用是成功 还是失败 1、利用“向导 2、使用企业管理器 [服务器][数据库][存储过程]—右 键—[新建]—[编辑文本] 2021/2/20 Information College. ChangJun
8 2021/2/20 Information College · ChangJun 9.1.2 创建存储过程 当创建存储过程时，需要确定存储过程的三 个组成部分： a) 所有的输入参数以及传给调用者的输出参 数 b) 被执行的针对数据库的操作语句包括调用 其它存储过程的语句 c)返回给调用者的状态值以指明调用是成功 还是失败 1、利用“向导” 2、使用企业管理器 [服务器]——[数据库]——[存储过程]—右 键—[新建]——[编辑文本]
3、使用 Transact-SQL语句 线出 可以创建一个过程供永久使用,或在一爷金 话中临时使用(局部临时过程),或在所有会话 中临时使用(全局临时过程) 语法: CREATE PROC EDURE procedure name L number r @parameter data type j I VARYING][= default][ OUTPUT n I WITH RECOMPILE ENCRYPTION RECOMPILE, ENCRYPTION I I FOR REPLICATION AS sql statement L n 2021/2/20 Information College. ChangJun
9 2021/2/20 Information College · ChangJun 3、使用Transact-SQL语句 可以创建一个过程供永久使用，或在一个会 话中临时使用（局部临时过程），或在所有会话 中临时使用（全局临时过程）。 语法： CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
例1:要求存储过程从三个表的联接中返回所有学 生(提供了姓名)、选修的课程名称和成绩。 USE school GO IF EXISTS(SELECT name FROM sysobjects WHERE name ='student info all ANd type DROP PROCEDURE student info all GO CREATE PROCEDURE student info all AS SELECT Sname, cname, grade FROM S INNER JOIN SC ON S sno=sc sno INNER JOIN C ON SC cno=c cno GO 2021/2/20 Information College. ChangJun
10 2021/2/20 Information College · ChangJun 例1：要求存储过程从三个表的联接中返回所有学 生（提供了姓名）、选修的课程名称和成绩。 USE school GO IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘student_info_all' AND type = 'P') DROP PROCEDURE student_info_all GO CREATE PROCEDURE student_info_all AS SELECT sname, cname, grade FROM s INNER JOIN sc ON s.sno=sc.sno INNER JOIN c ON sc.cno=c.cno GO