《数据库原理与SQL Server》课程教学课件（PPT讲稿）存储过程触发器

单元8存储过程、触发器

单元8 存储过程、触发器

8.1存储过程在创建SOLServer数据库应用程序时，Transact-SOL语言是应用程序和SOLServer数据库之间的主要编程接口。可用如下两种方法存储和执行Transact-SOL语句。(1）将Transact-SQL程序保存在本地，创建向SQLServer发送命令并处理结果的应用程序。(2）可以将Transact-SOL程序保存在SOLServer中，即存储过程，在本地创建执行存储过程及处理结果的应用程序。任何一组Transact-SOL语句构成的代码块，都可以作为存储过程保存起来。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作，减少了网络上的传输量，同时也提高了客户端的工作效率（因为这些操作都是在服务器端完成的）。2

2 8.1 存储过程 在创建SQL Server数据库应用程序时，Transact-SQL语 言是应用程序和SQL Server数据库之间的主要编程接口。 可用如下两种方法存储和执行Transact-SQL语句。 (1) 将Transact-SQL程序保存在本地，创建向SQL Server 发送命令并处理结果的应用程序。 (2) 可以将Transact-SQL程序保存在SQL Server中，即存 储过程，在本地创建执行存储过程及处理结果的应用程序。 任何一组Transact-SQL语句构成的代码块，都可以作为 存储过程保存起来。它在服务器端对数据库中的数据进行 处理，并将结果返回到客户端。这样就避免了从客户端多 次连接并访问数据库的操作，减少了网络上的传输量，同 时也提高了客户端的工作效率（因为这些操作都是在服务 器端完成的）

存储过程概述8.1.1存储过程是集中存储在SOLServer中的SOL语句和流程控制语句的预编译集合，用以实现某种任务（如查询）。这些语句在一个名称下存储并作为一个单元进行处理。3

3 8.1.1 存储过程概述 存储过程是集中存储在SQL Server中的SQL语 句和流程控制语句的预编译集合，用以实现某种 任务（如查询）。这些语句在一个名称下存储并 作为一个单元进行处理

使用存储过程的优势可以减少客户端代码的重复。只需创建存储过程一次并将其存储在数据库中，以后即可在客户端程序中多次调用该存储过程。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。允许更快地执行。如果某操作需要大量的Transact-SQL代码或需要重复执行，使用存储过程将比在客户端执行Transact-SOL批代码的执行速度要快。存储过程在服务器端经过预编译，生成查询计划，可以直接执行，而在客户端每次运行Transact-SOL语句时，都要从客户端重复发送，并且在SQLServer每次执行这些语句时，都要对其进行编译和优化。因此，存储过程执行速度更快

4 使用存储过程的优势 ◼ 可以减少客户端代码的重复。只需创建存储过程一次并 将其存储在数据库中，以后即可在客户端程序中多次调 用该存储过程。存储过程可由在数据库编程方面有专长 的人员创建，并可独立于程序源代码而单独修改。 ◼ 允许更快地执行。如果某操作需要大量的Transact-SQL 代码或需要重复执行，使用存储过程将比在客户端执行 Transact-SQL批代码的执行速度要快。存储过程在服务 器端经过预编译，生成查询计划，可以直接执行，而在 客户端每次运行Transact-SQL语句时，都要从客户端重 复发送，并且在SQL Server每次执行这些语句时，都要 对其进行编译和优化。因此，存储过程执行速度更快

使用存储过程的优势减少网络流量。例如，一个需要数百行Transact-SOL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。可以简化数据库管理。例如，要修改某种香询如果查询重复存放在客户机上，则要在所有客户机上修改查询，而使用存储过程可以集中修改。可作为安全机制使用。例如，即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限5

5 使用存储过程的优势 ◼ 减少网络流量。例如，一个需要数百行 Transact-SQL代码的操作由一条执行过程代码 的单独语句就可实现，而不需要在网络中发送 数百行代码。 ◼ 可以简化数据库管理。例如，要修改某种查询， 如果查询重复存放在客户机上，则要在所有客 户机上修改查询，而使用存储过程可以集中修 改。 ◼ 可作为安全机制使用。例如，即使对于没有直 接执行存储过程中语句的权限的用户，也可授 予他们执行该存储过程的权限

存储过程的分类■系统存储过程：SOLSeryer内置的存储过程，存储在master库中，主要用途是执行SOLServer的某些管理功能、显示有关数据库和用户的信息。系统存储过程名以SP开头，可以在任何数据库中执行系统存储过程。用户存储过程：用户自行创建并存储在用户数据库中的存储过程临时存储过程：分为局部临时存储过程和全局临时存储过程。6

6 存储过程的分类 ◼ 系统存储过程: SQL Server内置的存储过程，存 储在master库中，主要用途是执行SQL Server的 某些管理功能、显示有关数据库和用户的信息。 系统存储过程名以SP_开头，可以在任何数据库 中执行系统存储过程。 ◼ 用户存储过程：用户自行创建并存储在用户数据 库中的存储过程。 ◼ 临时存储过程：分为局部临时存储过程和全局临 时存储过程

存储过程的分类>局部临时存储过程名称以开头，存放在tempdb数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时存储过程，>全局临时存储过程名称以#开头，存放在tempdb数据库中，允许所有连接的用户使用在所有用户断开连接时自动被册删除。远程存储过程：位于远程服务器上的存储过程。■扩展存储过程：利用外部语言（如C）编写的存储过程，以弥补SOLServer的不足之处，扩展新的功能

7 存储过程的分类 ➢局部临时存储过程名称以#开头，存放在 tempdb数据库中，只由创建并连接的用户使 用，当该用户断开连接时将自动删除局部临时 存储过程。 ➢全局临时存储过程名称以##开头，存放在 tempdb数据库中，允许所有连接的用户使用， 在所有用户断开连接时自动被删除。 ◼ 远程存储过程：位于远程服务器上的存储过程。 ◼ 扩展存储过程：利用外部语言（如C）编写的存 储过程，以弥补SQL Server的不足之处，扩展新 的功能

创建存储过程8.1.2使用CREATEPROCEDURE语句一使用企业管理器中的菜单命令8

8 8.1.2 创建存储过程 ◼ 使用CREATE PROCEDURE语句 ◼ 使用企业管理器中的菜单命令

CREATEPROCEDURE语句CREATEPROCIEDURE丨存储过程名I：编号【@参数数据类型}【VARYINGI=默认值】[OUTPUT[,...n]WITHRECOMPILEENCRYPTIONRECOMPILE,ENCRYPTIONIIASSQL语句【...n]9

9 CREATE PROCEDURE语句 CREATE PROC[ EDURE ] 存储过程名[;编号] [ { @参数 数据类型 } [ VARYING ] [ = 默认值 ] [ OUTPUT ] ] [,.n] WITH { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}] AS SQL语句 [ .n ]

参数说明存储过程名必须符合标识符的命名规则，且对于数据库及其所有者必须是惟一的。要创建局部临时存储过程，可以在存储过程名前面加个#号，要创建全局临时过程，可以在存储过程名前面加两个#号。完整的名称（包括#或##）不能超过128个字符。编号：可选整数，用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的存储过程一起删除10

10 参数说明 ◼ 存储过程名必须符合标识符的命名规则，且对 于数据库及其所有者必须是惟一的。要创建局 部临时存储过程，可以在存储过程名前面加一 个#号，要创建全局临时过程，可以在存储过 程名前面加两个#号。完整的名称（包括# 或 ##）不能超过128个字符。 ◼ 编号：可选整数，用来对同名的存储过程分组， 以便用一条DROP PROCEDURE语句即可将同 组的存储过程一起删除