资源简介

(共71张PPT)
第9章 流程控制、存储过程与触发器
本章主要介绍流程控制以及如何创建存储过程与使用触发器，包括存储过程简介、创建存储过程、执行存储过程、修改和删除存储过程、触发器简介、创建触发器、修改触发器和删除触发器。
通过本章的学习，读者可以掌握使用企业管理器和Transact-SQL创建存储过程或触发器，并应用存储过程或触发器编写SQL语句从而优化查询和提高数据访问速度。
2　存储过程
3　触发器
1　流程控制
9.1 流 程 控 制
9.1.1 BEGIN...END
语法：
BEGIN
{sql_statement...}
END
9.1.2 IF
IF结构的语法如下：
IF<条件表达式>
{命令行|程序块}
9.1.3 IF…ELSE
IF结构的语法如下：
IF<条件表达式>
{命令行1|程序块1}
[ELSE
{命令行2|程序块2}
9.1.4 CASE
Transact-SQL支持CASE有两种语句格式。
简单CASE函数：
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
CASE搜索函数：
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END
9.1.5 WHILE
WHILE<条件表达式>
BEGIN
<命令行|程序块>
END
9.1.6 WHILE…CONTINUE…BREAK
语法：
WHILE<条件表达式>
BEGIN
<命令行|程序块>
[BREAK]
[CONTINUE]
[命令行|程序块]
END
9.1.7 RETURN
RETURN语句用于从查询过程中无条件退出。RETURN语句可在任何时候用于从过程、批处理或语句块中退出。位于RETURN之后的语句不会被执行。
语法：
RETURN[整数值]
9.1.8 GOTO
GOTO命令用来改变程序执行的流程，使程序跳到标识符指定的程序行再继续往下执行。
语法：
GOTO 标识符
标识符需要在其名称后加上一个冒号“：“。
9.1.9 WAITFOR
WAITFOR指定触发器、存储过程或事务执行的时间、时间间隔或事件；还可以用来暂时停止程序的执行，直到所设定的等待时间已过才继续往下执行。
语法：
WAITFOR{DELAY<’时间’>|TIME<’时间’>
9.2　存储过程简介
存储过程（Stored Procedure）是在数据库服务器端执行的T-SQL语句的集合，经编译后存放在数据库服务器中。
存储过程作为一个单元进行处理并由一个名称来标识。
它能够向用户返回数据、在数据库表中写入或修改数据，还可以执行系统函数和管理操作。
用户在编程过程中只需要给出存储过程的名称和必需的参数，就可以方便地调用它们。
存储过程可以提高应用程序的处理能力，降低编写数据库应用程序的难度，同时还可以提高应用程序的效率。
存储过程的处理非常灵活，允许用户使用声明的变量，还可以有输入输出参数，返回单个或多个结果集以及处理后的结果值。
9.2.1　存储过程的优点
（1）存储过程可以嵌套使用，支持代码重用。
（2）存储过程可以接受并使用参数动态执行其中的SQL语句。
（3）存储过程比一般的SQL语句执行速度快。
（4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。
（5）存储过程允许模块化程序设计。
（6）存储过程可以减少网络通信流量。
（7）存储过程可以强制应用程序的安全性。
9.2.2　存储过程的类别
（1）系统存储过程
（2）用户自定义存储过程
（3）扩展存储过程
9.3　创建存储过程
在SQL Server 2016中创建存储过程有两种方法：一种方法是使用企业管理器创建存储过程；另一种方法是使用Transact-SQL语言创建存储过程。
9.3.1　使用企业管理器创建存储过程
【例9-1】 创建存储过程。
图9.1 用对象资源管理器创建存储过程
图9.2 在Management Studio中编写存储过程
9.3.2　使用Transact-SQL语言创建存
储过程
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 ]
【例9-2】 使用Transact-SQL语言创建存储过程。
图9.3 创建loving存储过程
9.4　执行存储过程
1．通过Execute或Exec语句执行
EXECUTE语句用于执行存储在服务器上的存储过程，也可以简写成EXEC语句。
语法：
[ [ EXECUTE [ UTE ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]
[ ,...n ]
[ WITH RECOMPILE ]
【例9-3】 调用Exec语句执行存储过程。
图9.4 执行loving存储过程结果
2．通过设置使存储过程自动执行
在SQL Server 2016中，可以通过设置使指定的存储过程在服务器启动的时候自动执行。
这种设置对于一些应用很有帮助，例如用户希望某些操作周期性地执行，某些操作作为后台进程完成，某些操作一直保持运行。
另外的一些应用也可能需要一些存储过程自动执行。
用户必须是固定服务器角色sysadmin的成员才可以设置指定的存储过程为自动执行的存储过程。
下面将一个存储过程设置为自动执行的存储过程， sp_procoption的语法结构：
sp_procoption [ @ProcName = ] 'procedure'
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'
9.5　查看和修改存储过程
9.5.1　使用企业管理器查看和修改存储过程
1．使用Microsoft SQL Server Mangement查看存储过程
图9.5 查看loving存储过程
2．使用Microsoft SQL Server Mangement修改存储过程
图9.6 修改存储过程的查询编辑器和消息窗口
9.5.2　使用Transact-SQL语言查看
和修改存储过程
1．使用系统存储过程查看存储过程信息
（1）sp_helptext
查看存储过程的文本信息。
语法：
sp_helptext [ @objname = ] 'name'
（2）sp_depends
查看存储过程的相关性信息。
语法：
sp_depends [ @objname = ] 'object'
（3）sp_help
查看存储过程的一般信息。
语法：
sp_help [ [ @objname = ] name ]
【例9-5】 查看存储过程。
use db_student
EXEC sp_helptext loving
EXEC sp_depends loving
EXEC sp_help loving
图9.7 查看存储过程信息
2．使用Transact-SQL修改存储过程
使用ALTER PROCEDURE语句修改存储过程，它不会影响存储过程的权限设定，也不会更改存储过程的名称。
语法：
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION }
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
9.6　删除存储过程
9.6.1　使用企业管理器删除存储过程
图9.8 删除存储过程的对话框
9.6.2　使用Transact-SQL语言删除存储过程
DROP PROCEDURE语句从当前数据库中删除一个或多个存储过程或过程组。
语法：
DROP PROCEDURE { procedure } [ ,...n ]
9.7　触发器简介
9.7.1　触发器的概念
触发器是一种特殊类型的存储过程，它在插入、删除或修改特定表中的数据时触发执行。
触发器通常可以强制执行一定的业务规则，以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加的功能。
在SQL Server中一张表可以有多个触发器。
用户可以根据INSERT、UPDATE或DELETE语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。
触发器可以包含复杂的T-SQL语句。
触发器不能通过名称被直接调用，更不允许设置参数。
9.7.2　触发器的功能
触发器可以使用T-SQL语句进行复杂的逻辑处理，它基于一个表创建，可以对多个表进行操作，因此常常用于复杂的业务规则。
一般可以使用触发器完成如下操作。
（1）级联修改数据库中相关表。
（2）执行比检查约束更为复杂的约束操作。
（3）拒绝或回滚违反引用完整性的操作。
检查对数据表的操作是否违反引用完整性，并选择相应的操作。
（4）比较表修改前后数据之间的差别，并根据差别采取相应的操作。
9.7.3　触发器的类型和触发操作
在SQL Server 2016中，触发器分为DML触发器和DDL触发器两种。
（1）DML触发器是在执行数据操作语言事件时被调用的触发器，其中数据操作语言事件包括：INSERT、UPDATE和DELETE语句。
触发器中可以包含复杂的Transact-SQL语句，触发器整体被看作一个事务，可以回滚。
DML触发器可以分为如下5种类型。
① UPDATE触发器
② INSERT触发器
③ DELETE触发器
④ INSTEAD OF触发器
⑤ AFTER触发器
（2）DDL与DML触发器类似，与DML不同的是，相应的触发事件是由数据定义语言引起的事件，包括：CREATE、ALTER和DROP语句，DDL触发器用于执行数据库管理任务，如调节和审计数据库运转。
DDL触发器只能在触发事件发生后才会调用执行，即它只能是AFTER触发器。
SQL Server 2016中，新增加了许多新的特性，其中，DDL触发器是SQL Server 2016的一大亮点。
9.8　创建触发器
9.8.1　使用企业管理器创建触发器
1．创建DML触发器
图9.9 利用“对象资源管理器”创建触发器
图9.10 查询编辑器中创建触发器
图9.11 成功创建了触发器loving20
2．创建DDL触发器
使用Microsoft SQL Server Management Studio创建DDL触发器与使用Microsoft SQL Server Management Studio创建DML触发器的方法一样，只要最后输入创建DDL触发器的SQL语句即可。
9.8.2　使用Transact-SQL语言创建触发器
1．使用T-SQL语法创建DML触发器
创建DML触发器的语法结构如下：
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
::=
assembly_name.class_name.method_name
2．使用T-SQL语句创建DDL触发器
创建DDL触发器的语法结构如下：
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
::=
assembly_name.class_name.method_name
9.9　修改触发器
9.9.1　使用企业管理器修改触发器
图9.14 用“对象资源管理器”修改触发器
9.9.2　使用Transact-SQL语言管理触发器
修改DML和DDL触发器的语法结构与创建它们的语法结构类似，除了使用的开始关键词变为ALTER和在修改DML触发器时不能使用WITH APPEND参数选项外，其他语法结构都相同。
修改DML触发器的ALTER TRIGGER语法结构如下：
ALTER TRIGGER schema_name.trigger_name
ON ( table | view )
[ WITH [ ,...n ] ]
( FOR | AFTER | INSTEAD OF )
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME [ ; ] }
::=
[ ENCRYPTION ]
[ ]
::=
assembly_name.class_name.method_name
修改DDL触发器的ALTER TRIGGER语法结构如下：
ALTER TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS { sql_statement [ ; ] | EXTERNAL NAME
[ ; ] } }
::=
[ ENCRYPTION ]
[ ]
::=
assembly_name.class_name.method_name
9.10　删除触发器
9.10.1　在企业管理器中删除触发器
图9.15 用“对象资源管理器”删除触发器
9.10.2　使用Transact-SQL语言删除触发器
DROP TRIGGER语句从当前数据库中删除一个或多个触发器。
语法：
DROP TRIGGER { trigger } [ ,...n ]
小　　结
本章介绍了存储过程和触发器的概念、创建和管理存储过程与触发器的方法。
读者使用存储过程可以增强代码的重用性，使用触发器可以在操作数据的同时触发指定的事件从而维护数据完整性。
创建存储过程后可以调用Execute语句执行存储过程或者设置其自动执行，还可以查看、修改或者删除存储过程。
而触发器分为DML触发器和DDL触发器，可以使用企业管理器或者Transact-SQL语句对触发器进行管理。

展开更多......

收起↑