当前位置:首页 >> 高中教育 >> 第11章存储过程、触发器和游标(1)

第11章存储过程、触发器和游标(1)


数据库原理与应用教程 ―SQL Server

第11章存储过程、触发器和游标 11章存储过程、 章存储过程

引言
应用操作中, 在SQL Server 2005应用操作中,存储过程、触发器和游 应用操作中 存储过程、 标都扮演着相当重要的角色。 标都扮演着相当重要的角色。 存储过程是SQL语句和可选流程控制语句的预编译集合,

语句和可选流程控制语句的预编译集合, 存储过程是 语句和可选流程控制语句的预编译集合 它以一个名称存储并作为一个单元处理, 它以一个名称存储并作为一个单元处理,能够提高系统的 应用效率和执行速度。 应用效率和执行速度。 触发器是一种特殊类型的存储过程。 触发器是一种特殊类型的存储过程。当有操作影响到触发 器保护的数据时,触发器就会自动触发执行。 器保护的数据时,触发器就会自动触发执行。它可以扩展 SQL Server完整性约束默认值对象和规则的完整性检查逻 完整性约束默认值对象和规则的完整性检查逻 实施更为复杂的数据完整性约束。 辑,实施更为复杂的数据完整性约束。 游标提供了一种对从表中检索出的数据进行操作的灵活手 就本质而言, 段,就本质而言,游标实际上是一种能从包括多条数据记 录的结果集中每次提取一条记录的机制。 录的结果集中每次提取一条记录的机制。

内容
11.1 存储过程 11.2 触发器 11.3 游标

11.1 存储过程
1. 概述 2. 存储过程的类别 3. 创建和执行存储过程 4. 查看、修改和删除存储过程 查看、

1. 概述
?客户 服务器模型 客户/服务器模型 客户
客户B 客户

11.1存储过程 存储过程

客户A需要查询部门 的销售额 客户 需要查询部门1的销售额 需要查询部门 SELECT SUM(总金额 总金额) 总金额 FROM 订单信息 A INNER JOIN 销售人员 B ON A.销售工号 B. 工号 销售工号= 销售工号 WHERE 部门号 部门号=1

客户A 客户

服务器

有大量数据在服务器和客户端之间流动!!! 有大量数据在服务器和客户端之间流动!!!

1.概述 什么是存储过程 概述----什么是存储过程 概述
① 存储过程的概念 一组编译在单个执行计划中的T-SQL语句,它将一 语句, 一组编译在单个执行计划中的 语句 些固定的操作集中起来交给SQL Server数据库服 些固定的操作集中起来交给 数据库服 务器完成,以完成某个任务。( 务器完成,以完成某个任务。(管理任务或应用复 杂的业务规则)。 杂的业务规则) 是存储在服务器上的 T-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、 支持用户声明变量、条件执行以及其他强有力的 编程特性

1.概述 什么是存储过程 概述----什么是存储过程 概述
存储过程( 存储过程(procedure)类似于 语言中的函数 )类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数, 存储过程可以带参数,也可以返回结果
存储过程相 当于C语言 当于 语言 中的函数

int sum(int a,int b) { int s; s =a+b; return s ; }

1.概述 什么是存储过程 概述----什么是存储过程 概述 SQL Server 中的存储过程可以
包含执行数据库操作(包括调用其他过程) 包含执行数据库操作(包括调用其他过程)的 编程语句 接受输入参数 向调用过程或批处理返回状态值, 向调用过程或批处理返回状态值,以表明成功 或失败(以及失败原因) 或失败(以及失败原因) 以输出参数的形式将多个值返回至调用过程或 批处理

1.概述 什么是存储过程 概述----什么是存储过程 概述
存储过程可以包含数据操纵语句、变量、 存储过程可以包含数据操纵语句、变量、逻辑 控制语句 等
单个 SELECT 语句

存储过程
----------------------

SELECT 语句块

SELECT语句与逻辑 语句与逻辑 控制语句

可以包含

1. 概述 存储过程优点 概述----存储过程优点
安全机制:只给用户访问存储过程的权限, 安全机制:只给用户访问存储过程的权限,而不授予用户 访问表和视图的权限。 访问表和视图的权限。 改良了执行性能:只在第一次执行时进行编译, 改良了执行性能:只在第一次执行时进行编译,以后执行 无需重新编译,而一般SQL语句每执行一次就编译一次。 SQL语句每执行一次就编译一次 无需重新编译,而一般SQL语句每执行一次就编译一次。 减少网络流量:存储过程存在于服务器上,调用时,只需 减少网络流量:存储过程存在于服务器上,调用时, 传递执行存储过程的执行命令和返回结果。 传递执行存储过程的执行命令和返回结果。 模块化的程序设计:增强了代码的可重用性, 模块化的程序设计:增强了代码的可重用性,提高了开发 效率。 效率。

2.存储过程的类型 存储过程的类型
用户定义的存储过程: 用户定义的存储过程:用户定义的存储过程是 用户根据需要,为完成某一特定功能, 用户根据需要,为完成某一特定功能,在自己 的普通数据库中创建的存储过程。 的普通数据库中创建的存储过程。 系统存储过程:系统存储过程以sp_为前缀, 为前缀, 系统存储过程:系统存储过程以 为前缀 主要用来从系统表中获取信息, 主要用来从系统表中获取信息,为系统管理员 管理SQL Server提供帮助,为用户查看数据库 提供帮助, 管理 提供帮助 对象提供方便。 对象提供方便。比如用来查看数据库对象信息 的系统存储过程sp_help。从物理意义上讲, 的系统存储过程 。从物理意义上讲, 系统存储过程存储在资源数据库中。 系统存储过程存储在资源数据库中。从逻辑意 义上讲, 义上讲,系统存储过程出现在每个系统定义数 据库和用户定义数据库的sys构架中。 构架中。 据库和用户定义数据库的 构架中

扩展存储过程: 扩展存储过程:指SQL Server的实例动态加载和运 的实例动态加载和运 行的DLL,这些 通常是用编程语言( 行的 ,这些DLL通常是用编程语言(例如:C) 通常是用编程语言 例如: ) 创建的。扩展存储过程以xp_为前缀。 创建的。扩展存储过程以 为前缀。 为前缀 临时存储过程: 临时存储过程:以“#”和“##”为前缀的过程, ” ”为前缀的过程, “#”表示本地临时存储过程,“##”表示全局临 ”表示本地临时存储过程, ” 时存储过程,它们存储在tempdb数据库中。 数据库中。 时存储过程,它们存储在 数据库中 远程存储过程: 远程存储过程:是在远程服务器的数据库中创建和 存储的过程。这些存储过程可被各种服务器访问, 存储的过程。这些存储过程可被各种服务器访问, 向具有相应许可权限的用户提供服务。 向具有相应许可权限的用户提供服务。

2.存储过程的类型 常用的系统存储过程 存储过程的类型--存储过程的类型
系统存储过程
sp_databases sp_helpdb sp_renamedb sp_tables sp_columns sp_help sp_helpconstraint sp_helpindex sp_stored_procedure s sp_password sp_helptext

说明
列出服务器上的所有数据库。 列出服务器上的所有数据库。 报告有关指定数据库或所有数据库的信息 更改数据库的名称 返回当前环境下可查询的对象的列表 回某个表列的信息 查看某个表的所有信息 查看某个表的约束 查看某个表的索引 列出当前环境中的所有存储过程。 列出当前环境中的所有存储过程。 添加或修改登录帐户的密码。 添加或修改登录帐户的密码。 显示默认值、未加密的存储过程、 显示默认值、未加密的存储过程、用户定义的存 储过程、触发器或视图的实际文本。 储过程、触发器或视图的实际文本。

常用的系统存储过程
列出当前系统中的数据库 EXEC sp_databases EXEC sp_renamedb 'Northwind','Northwind1' 修改数据库的名称 单用户访问 修改数据库的名称(单用户访问 单用户访问) USE stuDB 当前数据库中查询的对象的列表 GO EXEC sp_tables 返回某个表列的信息 EXEC sp_columns stuInfo 查看表stuInfo的信息 查看表 的信息 EXEC sp_help stuInfo EXEC sp_helpconstraint stuInfo 查看表stuInfo的约束 查看表 的约束 EXEC sp_helpindex stuMarks EXEC sp_helptext 'view_stuInfo_stuMarks' 查看表stuMarks的索引 查看表 的索引 EXEC sp_stored_procedures 查看视图的语句文本 查看当前数据库中的存储过程

3.创建、执行存储过程 创建、 创建
使用SSMS管理存储过程 ①使用 管理存储过程 创建 执行 查看 修改 删除

3.创建、执行存储过程 创建、 创建
利用T SQL语句创建存储过程 ② 利用T-SQL语句创建存储过程 CREATE PROC[EDURE] 存储过程名 [;num] @参数 数据类型 [=默认值 [OUT[PUT]] 参数1 默认值] 参数 默认值 [, …其他参数 其他参数…] 其他参数 [WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }] AS BEGIN SQL语句系列 语句系列 END

5.2.2 存储过程创建和执行 说明: 说明
[;num]:可选的整数。用来对同名若干过程编号, [;num]:可选的整数。用来对同名若干过程编号,如 proc_s;1, proc_s;3等 proc_s;2, proc_s;3等。 采用相同名字加编号区别若干过程的优点是删除时方便, 采用相同名字加编号区别若干过程的优点是删除时方便,如, 优点是删除时方便 proc_s一条命令把 个过程都删了。 一条命令把3 DROP PROCEDURE proc_s一条命令把3个过程都删了。 [default]:参数的默认值。如果定义了默认值,不必指定该参 [default]:参数的默认值。如果定义了默认值, 数的值就可调用。默认值必须是常量或NULL NULL。 数的值就可调用。默认值必须是常量或NULL。如果过程将对该参 数使用LIKE关键字,那么默认值中可以包含通配符(% LIKE关键字 (%、 []、 数使用LIKE关键字,那么默认值中可以包含通配符(%、_、[]、 [^])。 [^])。 [OUTPUT]:表示该参数是返回参数。 [OUTPUT]:表示该参数是返回参数。参数可将信息返回给调用过 程。Text、ntext、Image参数可用作OUTPUT参数。 Text、ntext、Image参数可用作OUTPUT参数。 参数可用作OUTPUT参数 RECOMPILE:表明该过程在运行时重新编译。 RECOMPILE:表明该过程在运行时重新编译。 表示系统加密syscomments表中包含CREATE syscomments表中包含 ENCRYPTION :表示系统加密syscomments表中包含CREATE PROCEDURE语句文本的条目 语句文本的条目。 PROCEDURE语句文本的条目。 本人也无法查看过程定义文本。 加密后即使是过程创建者 本人也无法查看过程定义文本。

3.创建、执行存储过程 创建、 创建
定义存储过程的语法 CREATE PROC[EDURE] 存储过程名 @参数 数据类型 = 默认值 OUTPUT, 参数1 参数 …… , @参数 数据类型 = 默认值 OUTPUT 参数n 参数 AS SQL语句 语句 GO 语言的函数一样, 和C语言的函数一样,参数可选 语言的函数一样 参数分为输入参数、输出参数 参数分为输入参数、 输入参数允许有默认值

① 创建不带参数的存储过程
问题: 问题: 请创建存储过程, 请创建存储过程,查看本次考试平均分以及未通过考试的学员 名单

创建不带参数的存储过程
CREATE PROCEDURE proc_stu proc_stu为存储过程的名称 为存储过程的名称 AS DECLARE @writtenAvg float,@labAvg float 笔试平均分和机 试平均分变量 SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks print '笔试平均分:'+convert(varchar(5),@writtenAvg) 笔试平均分: 笔试平均分 print '机试平均分:'+convert(varchar(5),@labAvg) 机试平均分: 机试平均分 IF (@writtenAvg>70 AND @labAvg>70) print '本班考试成绩:优秀 本班考试成绩: 本班考试成绩 优秀' 显示考试成绩的等级 ELSE print '本班考试成绩:较差 本班考试成绩: 本班考试成绩 较差' print '--------------------------------------------------' 显示未通过的学员 print ' 参加本次考试没有通过的学员: 参加本次考试没有通过的学员:' SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<60 OR labExam<60 GO

② 调用存储过程 EXECUTE(执行)语句用来调用存储过程 (执行) 调用的语法 EXEC 过程名 [参数 参数] 参数
EXEC proc_stu

② 调用存储过程 存储过程的参数分两种: 存储过程的参数分两种:
输入参数 输出参数
c=sum(5, 8) ( ) 传入参 数值

输入参数: 输入参数: 输出参数: 输出参数:
用于在调用存储过程后, 用于在调用存储过程后, 返回结果 类似C语言的 结果, 返回结果,类似 语言的 按引用传递; 按引用传递 返回结果
{ int s; s=a+b; return s; }

用于向存储过程传入值 类似 sum (int a, int b) 语言的按值传递; 用于向存储过程传入值,类似C语言的按值传递 传入 语言的按值传递 int

传递参数的方式
使用参数名
形式: 参数名 参数名= 形式:@参数名=参数值 顺序要求: 顺序要求:可以不按顺序指定参数值

使用参数位置
形式: 形式:不显式指出参数名 顺序要求: 顺序要求:严格按照定义的顺序

③ 带输入参数的存储过程
问题: 问题: 修改上例:由于每次考试的难易程度不一样,每次 笔试和 修改上例: 由于每次考试的难易程度不一样, 可能随时变化( 60分 机试的及格线可能随时变化 不再是60 机试的及格线可能随时变化(不再是60分),这导致考试的 评判结果也相应变化。 评判结果也相应变化。 分析: 分析: 在述存储过程添加2个输入参数 个输入参数: 在述存储过程添加 个输入参数: @writtenPass 笔试及格线 @labPass 机试及格线

③ 带输入参数的存储过程
CREATE PROCEDURE proc_stu 输入参数: 输入参数:笔试及格线 @writtenPass int, @labPass int 输入参数:机试及格线 输入参数: AS print '--------------------------------------------------' print ' 参加本次考试没有通过的学员: 参加本次考试没有通过的学员:' SELECT stuName,stuInfo.stuNo,writtenExam, 查询没有通过 考试的学员 labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<@writtenPass OR labExam<@labPass GO

③ 带输入参数的存储过程
调用带参数的存储过程
假定本次考试机试偏难,机试的及格线定为55分 假定本次考试机试偏难,机试的及格线定为 分,笔试及格线定为 60分 分
EXEC proc_stu 60,55 --或这样调用: 或这样调用: 或这样调用 EXEC proc_stu @labPass=55,@writtenPass=60

机试及格线降分后, 机试及格线降分后,李斯文 (59分)成为“漏网之鱼”了 分 成为“漏网之鱼”

④ 输入参数的默认值 带参数的存储过程确实比较方便, 带参数的存储过程确实比较方便,调用者可根据 试卷的难易度, 试卷的难易度,随时修改每次考试的及格线

问题: 问题: 如果试卷的难易程度合适, 如果试卷的难易程度合适,则调用者还是必须 如此调用: 如此调用: EXEC proc_stu 60,60,比较麻烦 , 这样调用就比较合理: 这样调用就比较合理: 笔试及格线55分 EXEC proc_stu 55 笔试及格线 分,机试及格线默认 为60分 分 EXEC proc_stu 笔试和机试及格线都默认为标准的 60分 分

④ 输入参数的默认值
CREATE PROCEDURE proc_stu 笔试及格线:默认为60分 笔试及格线:默认为 分 @writtenPass int=60, @labPass int=60 机试及格线:默认为60分 机试及格线:默认为 分 AS print '--------------------------------------------------' print ' 参加本次考试没有通过的学员: 参加本次考试没有通过的学员:' SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo 查询没有通过考试的学员 INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<@writtenPass OR labExam<@labPass GO

输入参数的默认值
调用带参数默认值的存储过程 EXEC proc_stu --都采用默认值 都采用默认值 EXEC proc_stu 64 --机试采用默认值 机试采用默认值 EXEC proc_stu 60,55 --都不采用默认值 都不采用默认值 --错误的调用方式:希望笔试采用默认值,机试及格线55分 错误的调用方式:希望笔试采用默认值,机试及格线 分 错误的调用方式 EXEC proc_stu ,55 --正确的调用方式: 正确的调用方式: 正确的调用方式 EXEC proc_stu @labPass=55

⑤ 带输出参数的存储过程
如果希望调用存储过程后,返回一个或多个值, 如果希望调用存储过程后,返回一个或多个值, 这时就需 要使用输出(OUTPUT)参数了 要使用输出( ) 问题: 问题: 修改上例,返回未通过考试的学员人数。 修改上例,返回未通过考试的学员人数。

带输出参数的存储过程
CREATE PROCEDURE proc_stu 输出(返回)参数: 输出(返回)参数: @notpassSum int OUTPUT, 表示没有通过的人数 @writtenPass int=60, @labPass int=60 推荐将默认参数放后 AS …… SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam<@writtenPass OR labExam<@labPass SELECT @notpassSum=COUNT(stuNo) 统计并返回没有 FROM stuMarks WHERE 通过考试的学员 writtenExam<@writtenPass 人数 OR labExam<@labPass GO

带输出参数的存储过程
调用带输出参数的存储过程
调用时必须带OUTPUT关键字 , 调用时必须带 关键字 /*---调用存储过程 调用存储过程----*/ 调用存储过程 返回结果将存放在变量@sum中 返回结果将存放在变量 中 DECLARE @sum int EXEC proc_stu @sum OUTPUT ,64 print '--------------------------------------------------' 后续语句引用返回结果 IF @sum>=3 print '未通过人数:'+convert(varchar(5),@sum)+ '人, 未通过人数: 未通过人数 人 超过60%,及格分数线还应下调 及格分数线还应下调' 超过 及格分数线还应下调 ELSE print '未通过人数:'+convert(varchar(5),@sum)+ '人, 未通过人数: 未通过人数 人 已控制在60%以下,及格分数线适中 以下, 已控制在 以下 及格分数线适中' GO

5 查看、修改和删除存储过程 查看、 5.2.4 存储过程的查看、修改和删除 存储过程的查看、
①查看存储过程 方法1: 方法 :可以通过系统存储过程来查看用户定义的存 储过程。 储过程。

Sp_helptext 存储过程名
方法2 对象资源管理器中 找到所要查看的存储过程, 对象资源管理器中, 方法 :对象资源管理器中,找到所要查看的存储过程, 单击鼠标右键,打开快捷菜单,选择“修改” 单击鼠标右键,打开快捷菜单,选择“修改”菜单命 令。 方法3:如果想查看存储过程的一般信息,可使用: 方法 :如果想查看存储过程的一般信息,可使用: Sp_help 存储过程名

5. 查看、修改和删除存储过程 查看、
②修改存储过程(建议可视化操作完成) 修改存储过程(建议可视化操作完成) 在查询分析器中使用下列命令: 在查询分析器中使用下列命令: ALTER PROC [SCHEMA].存储过程名 存储过程名 <存储过程源代码 存储过程源代码> 存储过程源代码

5.2.4 存储过程的查看、修改和删除 存储过程的查看、

或 可视化操作:选定存储过程,在其上打开快捷菜单, 可视化操作:选定存储过程,在其上打开快捷菜单, 选择“修改” 菜单命令,打开编辑窗口。 选择“修改” 菜单命令,打开编辑窗口。修改完成 再重新执行一次即可。 后,再重新执行一次即可。

5. 查看、修改和删除存储过程 查看、 ③ 删除存储过程
DROP PROCEDURE 存储过程名


更多相关文档:

实验6 游标、存储过程和触发器报告

实验六 游标存储过程与触发器实验报告 一、实验目的掌握使用 T-SQL 实现游标...代码: /*1*/ declare @Sno varchar(10),@Cno varchar(10),@Grade int ...

实验六 游标、存储过程与触发器

信息工程学院1 数据库原理应用(SQL Server 2008) 实验/实习报告存储过程与触发器 实验六一、实验目的 游标存储过程与触发器 掌握使用 T-SQL 实现游标存储过...

第11章 存储过程

第11章存储过程触发器游... 77页 免费第​1​1​章​ ​存​储...仅适用于 游标参数。 11.3.1. 指定存储过程的名称 ? ? 存储过程名称最长不...

第10章 存储过程和触发器

第11章 游标、事务和锁 第12章 数据库安全管理 第13章 数据库备份恢复 第14章 SQL Server数据库的...1/2 相关文档推荐 第10章 存储过程和触发器 39页 ...

游标、存储过程、触发器

第7章事务、存储过程、触发... 157页 5财富值 事务 游标 存储过程 触发器... 9页 免费 第11章存储过程触发器和... 50页 1财富值 数据库编程技术——...

第11章 触发器

13页 免费 第11章存储过程触发器游标 77页 免费 第11章 触发器的创建与应用... 暂无评价 43页 10财富值 第11章存储过程和触发器 68页 1财富值喜欢...

实验六 游标、存储过程和触发器

28页 1财富值 实验六 存储过程和触发器 2页 免费 第6章游标存储过程函数触发...实验六一、实验目的 游标存储过程与触发器 掌握使用 T-SQL 实现游标存储过程...

实验八 数据库编程技术——游标、存储过程与触发器

数据库原理实验学号:123012008xxx 姓名:xxx 班级(x)小班 日期:2010 年月日 实验八一、实验目的 数据库编程技术—游标存储过程与触发器 1.掌握游标的定义和使用...

第11章 触发器

13页 免费 第11章存储过程触发器游标 77页 免费 第11章 触发器的创建与应用... 暂无评价 43页 10财富值 第11章存储过程和触发器 68页 1财富值喜欢...

数据库编程技术——游标、存储过程与触发器

数据库编程技术—游标存储过程与触发器一、实验目的 实验目的 1.掌握游标的定义和使用方法 2.掌握存储过程的定义、执行和调用方法 3.掌握游标和存储过程的综合...
更多相关标签:
存储过程 游标 触发器 | 触发器调用存储过程 | 存储过程和触发器 | 存储过程与触发器 | 存储过程 触发器 | mysql存储过程触发器 | 触发器中调用存储过程 | 触发器执行存储过程 |
网站地图

文档资料共享网 nexoncn.com copyright ©right 2010-2020。
文档资料共享网内容来自网络,如有侵犯请联系客服。email:zhit325@126.com