SQL课件存储过程和触发器PPT课件
合集下载
数据库SQL ServerSQLite教程课件：存储过程、触发器和游标
存储过程、触发器和游标
⒉ 使用 SSMS 创建存储过程 操作步骤如下： (1) 在“对象资源管理器”中展开“数据库”节点，展 开要创建存储过程的数据库。 (2) 展开“可编程性”节点，选择“存储过程”选项， 右击鼠标弹出快捷菜单，选择“新建存储过程”命令，打开 创建存储过程对话框，如图 8-3 所示。
存储过程、触发器和游标
8.1.2 创建存储过程 1. 使用 create procedure 语句创建存储过程 1) 语法格式
存储过程、触发器和游标
参数说明： (1) procedure_name 是要创建的存储过程的名称，它后 面跟一个可选项 number，是一个整数，用来区别一组同名 的存储过程，如 proc1、proc2 等。 (2) @parameter 用来声明存储过程的形式参数。在 create procedure 语句中，可以声明一个或多个参数。 (3) data_type 是参数的数据类型。 (4) varying 指定由 output 参数支持的结果集，仅应用于 游标型参数。
exec xp_logininfo;
存储过程、触发器和游标 例 8-3 查看有关 D:\sq 文件夹的文件信息。显示结果如
图 8-2 所示。代码如下： exec xp_cmdshell 'dir D:\sq\';
图 8-2 扩展存储过程
存储过程、触发器和游标
3) 用户自定义存储过程 用户自定义存储过程 (User-Defined Stored Procedure) 是 由用户设计的存储过程。其名称可以是任意组合 SQL Server 命令规则的字符组合，通常以“usp_”开头，避免以“sp_” 或“xp_”开头，以免造成混淆。自定义的存储过程会被添 加到所属数据库的存储过程中，并以对象的形式保存。
《存储过程与触发器》课件
触发器
触发器适用于需要实时响应数据变化、自动执行数据关联操作的场景。
存储过程和触发器的区别和联系
1
区别
存储过程是主动调用，触发器是被动触发；
联系
2
存储过程可以接受参数，触发器不能。
存储过程和触发器都是数据库中的可调用代 码，都可以实现数据的处理和逻辑的实现。
示例和案例
示例一
使用存储过程实现用户权限管理， 提高系统的安全性。
示例二
使用触发器实现订单状态的自动更 新，提升客户服务效率。
案例
存储过程和触器在电子商务平台 的应用，优化数据处理流程。
触发器的定义和作用
1 定义
2 作用
触发器是与数据库表相关联的一段代码，当表上 的特定事件发生时自动执行。
触发器可以用于实现数据的自动更新、数据的完 整性约束、审计和日志记录等功能。
存储过程的优点和用途
1 优点
存储过程可以减少网络通信的开销，提高数据库性能；可以保护数据的安全性，防止数 据被非法访问。
《存储过程与触发器》 PPT课件
存储过程和触发器是数据库中重要的功能模块。本课件将介绍存储过程和触 发器的定义、作用、优点、用途以及它们的使用场景、区别和联系，并提供 实例和案例。
存储过程的定义和作用
1 定义
2 作用
存储过程是一段预先编写好并存储在数据库中的 可被多次调用的代码块。
存储过程可以实现数据的封装和重复使用，提高 数据库的性能和安全性。
2 用途
存储过程常用于数据的复杂计算、数据的批量处理、数据的备份和还原等场景。
触发器的优点和用途
1 优点
2 用途
触发器可以自动执行，无需手动触发；可以实现 数据的实时更新，保证数据的一致性。
触发器适用于需要实时响应数据变化、自动执行数据关联操作的场景。
存储过程和触发器的区别和联系
1
区别
存储过程是主动调用，触发器是被动触发；
联系
2
存储过程可以接受参数，触发器不能。
存储过程和触发器都是数据库中的可调用代 码，都可以实现数据的处理和逻辑的实现。
示例和案例
示例一
使用存储过程实现用户权限管理， 提高系统的安全性。
示例二
使用触发器实现订单状态的自动更 新，提升客户服务效率。
案例
存储过程和触器在电子商务平台 的应用，优化数据处理流程。
触发器的定义和作用
1 定义
2 作用
触发器是与数据库表相关联的一段代码，当表上 的特定事件发生时自动执行。
触发器可以用于实现数据的自动更新、数据的完 整性约束、审计和日志记录等功能。
存储过程的优点和用途
1 优点
存储过程可以减少网络通信的开销，提高数据库性能；可以保护数据的安全性，防止数 据被非法访问。
《存储过程与触发器》 PPT课件
存储过程和触发器是数据库中重要的功能模块。本课件将介绍存储过程和触 发器的定义、作用、优点、用途以及它们的使用场景、区别和联系，并提供 实例和案例。
存储过程的定义和作用
1 定义
2 作用
存储过程是一段预先编写好并存储在数据库中的 可被多次调用的代码块。
存储过程可以实现数据的封装和重复使用，提高 数据库的性能和安全性。
2 用途
存储过程常用于数据的复杂计算、数据的批量处理、数据的备份和还原等场景。
触发器的优点和用途
1 优点
2 用途
触发器可以自动执行，无需手动触发；可以实现 数据的实时更新，保证数据的一致性。
存储过程和触发器课件
作用
触发器的主要作用是用于在数据表上 自动执行一系列操作，以确保数据的 完整性和一致性。
触发器的分类与触发事件
分类
根据触发时机，触发器可分为INSERT触 发器、UPDATE触发器和DELETE触发器 。
VS
触发事件
在执行INSERT、UPDATE或DELETE操作 时，触发器会自动执行。
触发器的优缺点
作用
存储过程可以用于封装数据库操作，简化复杂的数据库操作 ，提高数据访问的效率，减少网络流量，提高数据安全性等 。
存储过程的分类
系统存储过程
系统存储过程以sp_作为前缀，主 要用于管理系统数据库的存储过 程。
自定义存储过程
用户自定义存储过程是由用户根 据自己的需求编写的存储过程。
存储过程的优缺点
触发器参数
用于传递给触发器的值或变量。
触发器返回值
触发器执行完毕后返回的值或结果。
触发器的使用示例
• 示例1：创建一个在插入操作时触发的触发器，将新插入的 记录的ID复制到另一个表中。
触发器的使用示例
AFTER INSERT ON table1
CREATE TRIGGER after_insert_example
可维护性差：随着业务逻辑的 变更，可能需要修改多个触发
器，维护成本较高。
05
触发器的创建与使用
创建触发器的基本语法
• CREATE TRIGGER 触发器名称
创建触发器的基本语法
ON 表名 FOR EACH ROW WHEN 条件
创建触发器的基本语法
BEGIN 触发器逻辑 END;
触发器的参数与返回值
存储过程和触发器课件
• 存储过程概述 • 存储过程的创建与使用 • 存储过程的调试与优化 • 触发器概述 • 触发器的创建与使用 • 触发器的调试与优化
触发器的主要作用是用于在数据表上 自动执行一系列操作，以确保数据的 完整性和一致性。
触发器的分类与触发事件
分类
根据触发时机，触发器可分为INSERT触 发器、UPDATE触发器和DELETE触发器 。
VS
触发事件
在执行INSERT、UPDATE或DELETE操作 时，触发器会自动执行。
触发器的优缺点
作用
存储过程可以用于封装数据库操作，简化复杂的数据库操作 ，提高数据访问的效率，减少网络流量，提高数据安全性等 。
存储过程的分类
系统存储过程
系统存储过程以sp_作为前缀，主 要用于管理系统数据库的存储过 程。
自定义存储过程
用户自定义存储过程是由用户根 据自己的需求编写的存储过程。
存储过程的优缺点
触发器参数
用于传递给触发器的值或变量。
触发器返回值
触发器执行完毕后返回的值或结果。
触发器的使用示例
• 示例1：创建一个在插入操作时触发的触发器，将新插入的 记录的ID复制到另一个表中。
触发器的使用示例
AFTER INSERT ON table1
CREATE TRIGGER after_insert_example
可维护性差：随着业务逻辑的 变更，可能需要修改多个触发
器，维护成本较高。
05
触发器的创建与使用
创建触发器的基本语法
• CREATE TRIGGER 触发器名称
创建触发器的基本语法
ON 表名 FOR EACH ROW WHEN 条件
创建触发器的基本语法
BEGIN 触发器逻辑 END;
触发器的参数与返回值
存储过程和触发器课件
• 存储过程概述 • 存储过程的创建与使用 • 存储过程的调试与优化 • 触发器概述 • 触发器的创建与使用 • 触发器的调试与优化
SQLServer2012数据库应用与开发教程(第三版)模块9存储过程和触发器简明教程PPT课件
9.1.3 存储过程创建与执行
1.存储过程不使用任何参数
示例：创建一个存储过程，返回学生学号，姓名，课程名， 成绩信息。 use test go create procedure student_info as select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and o=o go
2.存储过程使用带有输入参数的存储过程
示例：创建一个存储过程，返回学生学号，姓名，课程名， 成绩信息。接受与传递参数精确匹配的值。 use test go create procedure student_info2 @xm varchar(8),@kcm varchar(50) as select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and o=o and sname=@xm and cname=@kcm go
模块9 存储过程、触发器
• • • •
本章学习目标： 了解存储过程、触发器的基本概念与特点 掌握存储过程的基本类型和相关操作• 通过前面的学习，我们能够编写并运行T-SQL程序以完成各种不同的 应用。保存T-SQL程序的方法有两种：一种是在本地保存程序的源文 件，运行时先打开源文件再执行程序；另一种方法即将程序存储为存 储过程，运行时调用存储过程执行。 • 因为存储过程是由一组T-SQL语句构成的，要使用存储过程，我们必 需熟悉前面几章所讨论的基本的T-SQL语句，并且需要了解掌握一些 关于函数、过程的概念。
•
• • •
9.1.2 存储过程的类型
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
返回 上页
14
10.4.1 触发器的特点
触发器是一种特殊的存储过程，除了存储过程的特点 外，它还另外有以下特点：
触发器是自动执行的，可以在一定条件下触发。 触发器可以同步数据库的相关表，进行级联更改。 触发器可以实现更复杂的安全检查。它可以实现比CHECK
更复杂的业务规则，还可以引用其他表中的列。 触发器可以实现数据库的管理任务。如DDL触发器，在
返回 上页
1
教学目标：本章主要包含以下内容。 存储过程概述 设计存储过程 实现和管理存储过程 触发器概述 设计触发器 实现和管理触发器 通过本章的学习，使读者掌握存储过程和触发器的基础知识， 并基本学会编写简单的存储过程和触发器，为以后在实际 应用中不断提高自己编写存储过程和触发器的技能打下良 好的基础。
返回 上页
10
10.3.3 修改存储过程
1. 修改存储过程的语法 注意：修改存储过程的名称会影响已关联对象对此存储过程
的调用。 2. 使用Management Studio修改存储过程
返回 上页
11
10.3.4 重新编译存储过程
1.sp_recompile系统存储过程可以强制指定的存储过程在下 次调用时重新编译。其调用的语法结构为：sp_recompile [ @objname = ] 'object'，其中的object为存储过程的名称 。
由于必须使用Transact-SQL语句创建存储过程，所以用户 必须首先掌握创建存储过程的Transact-SQL语法结构，然后再 学习如何使用Management Studio创建存储过程。
返回 上页
9
10.3.2 执行存储过程
执行存储过程有多种方式，比较常用的有以下几种。 1. 通过Execute或Exec语句执行 2. 通过设置，使存储过程自动执行 3. 作为批处理的第一行，直接输入存储过程名
返回 上页
2
第10章 存储过程和触发器
10.1 10.2 10.3 10.4 10.5 10.6
存储过程概述 设计存储过程 实现和管理存储过程 触发器概述 触发器设计规则 实现和管理触发器
返回 上页
3
10.1 存储过程概述
10.1.1 存储过程的特点
能够在单个存储过程中执行一系列的Transact-SQL语句， 能够以在一个存储过程中调用其他的存储过程。 存储过程是保存在服务器端的已经编译的Transact-SQL语句。 存储过程可以使用控制流语句和变量，大大增强了SQL的功能。 存储过程在提交前会自动检查语法。 存储过程是管理员放在服务器端的Transact-SQL语句。
2.在创建存储过程时使用WITH
RECOMPILE参数项，SQL
Server将不会把此存储过程放在缓冲存储器中，并且每次调
用此存储过程时都会重新编译。
3.在调用存储过程时，可以使用参数选项WITH RECOMPILE强
制重新编译。使用这个参数项要求存储过程的参数必须是非
典型的或者数据发生了很大的变化，否则一般不用它。
返回 上页
12
10.3.5 Βιβλιοθήκη 除存储过程如果一个存储过程不再需要，用户就可以删除它，但是 如果此存储过程有关联存储过程调用，系统会返回错误信息 。但是删除存储过程后，用户重新定义的同名称同参数的存 储过程可以被原来关联的对象使用。
删除存储过程的Transact-SQL语法结构为：
DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ]
第10章 存储过程和触发器
教学提示：开发中编写的一些SQL语句会占用程序的很大 篇幅，而且不便于在其他地方重用，且由于这些SQL语句 通常还要跨越传输途径从外部不仅会导致程序的运行效率 低，还会产生安全隐患，而存储过程则能克服以上的缺陷。 触发器可以大大增强应用程序的健壮性、数据库可恢复性 和数据库的可管理性。 存储过程和触发器都是SQL Server的数据库对象。存储过 程的存在独立于表，它存放在服务器上，供客户端调用。
返回 上页
13
10.4 触发器概述
触发器是一种特殊类型的存储过程，这种存储过程能够 在某个规定的事件发生时触发执行。触发器通常可以强制执 行一定的业务规则，以保持数据完整性、检查数据有效性、 实现数据库管理任务和一些附加的功能。
触发器是数据库中比较高级的应用，灵活使用触发器可 以大大增强应用程序的健壮性、数据库的可恢复性和数据库 的可管理性。同时可以使开发人员和数据库管理员实现一些 复杂的功能，简化开发步骤，降低开发成本，增加开发效率， 提高数据库的可靠性。
返回 上页
4
10.1.2 存储过程的分类
1. 用户存储过程 2. 系统存储过程 3. 扩展存储过程
返回 上页
5
10.2 设计存储过程
10.2.1 一些设计规则
用户在设计数据库(的存储过程)时，应遵守以下规则： 在SQL Server 2005中，存储过程可以使用Transact-SQL 中的任何语句，但是表10.1中的语句除外。
DDL语句执行后触发，可以实现一些统一的数据库管理 策略。
返回 上页
15
10.4.2 触发器的种类
1. DML触发器
DML触发器是在执行数据操作语言事件时被调用的触发 器，其中数据操作语言事件包括：INSERT、UPDATE和DELETE 语句。触发器中可以包含复杂的Transact-SQL语句，触发器 整体被看作一个事务，可以回滚。
略
返回 上页
6
10.2.2 存储过程的内部命名规范化
存储过程中的Transact-SQL语句使用的对象名称必须符 合规范的模式，如果未指明对象所属的模式，存储过程将使 用当前默认的模式。
每个存储过程必须有惟一的名称，存储过程中使用的变 量类型必须符合Transact-SQL的要求，变量名必须以@开头 ，并且存储过程的名称和变量的命名要符合Transact-SQL标 识符规则。
返回 上页
7
10.2.3 存储过程的加密定义
如果用户想定义一个不让其他用户看到具体操作的存储 过程，可以使用WITH ENCRYPTION参数，那么存储过程将会 以不可读的状态存在，并且存储过程拥有者、创建者和数据 库管理员都不可见。
返回 上页
8
10.3 实现和管理存储过程
10.3.1 创建存储过程
14
10.4.1 触发器的特点
触发器是一种特殊的存储过程，除了存储过程的特点 外，它还另外有以下特点：
触发器是自动执行的，可以在一定条件下触发。 触发器可以同步数据库的相关表，进行级联更改。 触发器可以实现更复杂的安全检查。它可以实现比CHECK
更复杂的业务规则，还可以引用其他表中的列。 触发器可以实现数据库的管理任务。如DDL触发器，在
返回 上页
1
教学目标：本章主要包含以下内容。 存储过程概述 设计存储过程 实现和管理存储过程 触发器概述 设计触发器 实现和管理触发器 通过本章的学习，使读者掌握存储过程和触发器的基础知识， 并基本学会编写简单的存储过程和触发器，为以后在实际 应用中不断提高自己编写存储过程和触发器的技能打下良 好的基础。
返回 上页
10
10.3.3 修改存储过程
1. 修改存储过程的语法 注意：修改存储过程的名称会影响已关联对象对此存储过程
的调用。 2. 使用Management Studio修改存储过程
返回 上页
11
10.3.4 重新编译存储过程
1.sp_recompile系统存储过程可以强制指定的存储过程在下 次调用时重新编译。其调用的语法结构为：sp_recompile [ @objname = ] 'object'，其中的object为存储过程的名称 。
由于必须使用Transact-SQL语句创建存储过程，所以用户 必须首先掌握创建存储过程的Transact-SQL语法结构，然后再 学习如何使用Management Studio创建存储过程。
返回 上页
9
10.3.2 执行存储过程
执行存储过程有多种方式，比较常用的有以下几种。 1. 通过Execute或Exec语句执行 2. 通过设置，使存储过程自动执行 3. 作为批处理的第一行，直接输入存储过程名
返回 上页
2
第10章 存储过程和触发器
10.1 10.2 10.3 10.4 10.5 10.6
存储过程概述 设计存储过程 实现和管理存储过程 触发器概述 触发器设计规则 实现和管理触发器
返回 上页
3
10.1 存储过程概述
10.1.1 存储过程的特点
能够在单个存储过程中执行一系列的Transact-SQL语句， 能够以在一个存储过程中调用其他的存储过程。 存储过程是保存在服务器端的已经编译的Transact-SQL语句。 存储过程可以使用控制流语句和变量，大大增强了SQL的功能。 存储过程在提交前会自动检查语法。 存储过程是管理员放在服务器端的Transact-SQL语句。
2.在创建存储过程时使用WITH
RECOMPILE参数项，SQL
Server将不会把此存储过程放在缓冲存储器中，并且每次调
用此存储过程时都会重新编译。
3.在调用存储过程时，可以使用参数选项WITH RECOMPILE强
制重新编译。使用这个参数项要求存储过程的参数必须是非
典型的或者数据发生了很大的变化，否则一般不用它。
返回 上页
12
10.3.5 Βιβλιοθήκη 除存储过程如果一个存储过程不再需要，用户就可以删除它，但是 如果此存储过程有关联存储过程调用，系统会返回错误信息 。但是删除存储过程后，用户重新定义的同名称同参数的存 储过程可以被原来关联的对象使用。
删除存储过程的Transact-SQL语法结构为：
DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ]
第10章 存储过程和触发器
教学提示：开发中编写的一些SQL语句会占用程序的很大 篇幅，而且不便于在其他地方重用，且由于这些SQL语句 通常还要跨越传输途径从外部不仅会导致程序的运行效率 低，还会产生安全隐患，而存储过程则能克服以上的缺陷。 触发器可以大大增强应用程序的健壮性、数据库可恢复性 和数据库的可管理性。 存储过程和触发器都是SQL Server的数据库对象。存储过 程的存在独立于表，它存放在服务器上，供客户端调用。
返回 上页
13
10.4 触发器概述
触发器是一种特殊类型的存储过程，这种存储过程能够 在某个规定的事件发生时触发执行。触发器通常可以强制执 行一定的业务规则，以保持数据完整性、检查数据有效性、 实现数据库管理任务和一些附加的功能。
触发器是数据库中比较高级的应用，灵活使用触发器可 以大大增强应用程序的健壮性、数据库的可恢复性和数据库 的可管理性。同时可以使开发人员和数据库管理员实现一些 复杂的功能，简化开发步骤，降低开发成本，增加开发效率， 提高数据库的可靠性。
返回 上页
4
10.1.2 存储过程的分类
1. 用户存储过程 2. 系统存储过程 3. 扩展存储过程
返回 上页
5
10.2 设计存储过程
10.2.1 一些设计规则
用户在设计数据库(的存储过程)时，应遵守以下规则： 在SQL Server 2005中，存储过程可以使用Transact-SQL 中的任何语句，但是表10.1中的语句除外。
DDL语句执行后触发，可以实现一些统一的数据库管理 策略。
返回 上页
15
10.4.2 触发器的种类
1. DML触发器
DML触发器是在执行数据操作语言事件时被调用的触发 器，其中数据操作语言事件包括：INSERT、UPDATE和DELETE 语句。触发器中可以包含复杂的Transact-SQL语句，触发器 整体被看作一个事务，可以回滚。
略
返回 上页
6
10.2.2 存储过程的内部命名规范化
存储过程中的Transact-SQL语句使用的对象名称必须符 合规范的模式，如果未指明对象所属的模式，存储过程将使 用当前默认的模式。
每个存储过程必须有惟一的名称，存储过程中使用的变 量类型必须符合Transact-SQL的要求，变量名必须以@开头 ，并且存储过程的名称和变量的命名要符合Transact-SQL标 识符规则。
返回 上页
7
10.2.3 存储过程的加密定义
如果用户想定义一个不让其他用户看到具体操作的存储 过程，可以使用WITH ENCRYPTION参数，那么存储过程将会 以不可读的状态存在，并且存储过程拥有者、创建者和数据 库管理员都不可见。
返回 上页
8
10.3 实现和管理存储过程
10.3.1 创建存储过程