第7章 存储过程、触发器与游标(课件）-《SQL Server数据库应用案例教程》同步教学（上海交通大学出版社）

7 存储过程、触发器 与游标 第 章 1 本章导读 存储过程是一条或多条T-SQL语句的集合，在实际应用中调用存储过程能够提高工作效率、增强数据安全性。触发器是一种特殊的存储过程，它的执行不需要调用，而是满足某条件时自动触发，使用触发器能够加强数据的完整性约束和业务规则等。游标通常存在于存储过程中，它能够对查询语句的结果集进行数据处理操作。 本章将介绍存储过程、触发器与游标的基本概念，以及使用T-SQL对它们进行创建、管理等操作的方法。 2 知识目标 理解存储过程、触发器与游标的基本概念。 能力目标 能够使用T-SQL创建、执行及管理存储过程。 能够使用SSMS创建、执行及管理存储过程。 能够使用T-SQL创建、管理触发器。 能够使用T-SQL对游标进行基本操作。 素质目标 拓展思维宽度，培养实践能力。 3 项目导航 存储过程 1 触发器 2 游 标 3 01 存储过程 5 7.1.1 初识存储过程 — 6 — 存储过程的概念 1 存储过程是一组用于完成特定功能的T-SQL语句集，这些语句经过编译后存储在数据库中。 存储过程可以被客户机或应用程序调用，使用它既可以保证数据的安全性又能够避免重复输入代码。 存储过程的分类 2 （1）系统存储过程。系统存储过程是系统提供的存储过程，主要存储在master数据库中并以“sp_”为前缀命名，它的主要功能是从系统表中获取信息。 （2）用户自定义存储过程。用户自定义存储过程是程序开发人员或数据库管理员为了完成某些特定功能而编写的存储过程，它可以接收用户提供的参数，也可以向客户端返回结果等。用户自定义存储过程的命名不要以“sp_”开头，避免执行发生错误。 （3）扩展存储过程。扩展存储过程是用于扩展SQL Server服务器功能的存储过程，通常以“xp_”为前缀命名。对于用户而言，拓展存储过程与其他存储过程一样，都可以通过调用的方式执行。 6 7.1.1 初识存储过程 — 7 — 存储过程的优点 3 （1）存储过程能够加快系统运行速度。 （2）存储过程能够简化操作流程。 （3）存储过程有助于实现模块化的程序设计。 （4）存储过程可以降低网络流量。 （5）存储过程能够提高数据的安全性。 提示 与查询操作类似，存储过程、触发器及游标的相关操作主要使用T-SQL实现，故本章主要介绍使用T-SQL对它们进行操作的方法。 7 7.1.2 创建与执行存储过程 — 8 — 1．创建存储过程 创建存储过程通常指创建用户自定义存储过程，其T-SQL命令为CREATE PROCEDURE，具体语法格式如下： （1）@parameter表示存储过程的参数，在创建存储过程的语句中可以声明一个或多个参数，当声明多个参数时用“,”隔开。 （2）<数据类型>指明参数的数据类型。 （3）DEFAULT表示参数的默认值。 （4）OUTPUT表示参数是输出参数，设置此项可以向调用者返回信息。 （5）[设定选项]中可设置其他设定选项，具体内容读者可查阅相关资料。 （6）AS关键字指明该存储过程要执行的操作，其后的<T-SQL语句>指明操作的具体内容。 CREATE PROCEDURE <存储过程名称> [{@parameter <数据类型>}[=DEFAULT] [OUTPUT][ , … n]] [设定选项] AS <T-SQL语句> 8 — 9 — 【例 7-1】 创建一个存储过程pr_s，用于查询数据表student中所有年龄大于18岁的学生信息。 CREATE PROCEDURE pr_s AS SELECT * FROM student WHERE age>18 【例 7-2】 创建一个存储过程pr_si，用于向数据表student中插入一条记录，记录的内容由参数提供。 CREATE PROCEDURE pr_si @sno varchar(6), @sn nvarchar(12), @sex nchar(1), @dept nvarchar(10), @age int AS INSERT INTO student VALUES(@sno,@sn,@sex,@dept,@age) 7.1.2 创建与执行存储过程 1．创建存储过程 9 — 10 — 【例 7-3】 创建一个存储过程pr_ss，用于返回学生的姓名和系别，输入的参数为学生的学号。 CREATE PROCEDURE pr_ss @sno varchar(6), @sn nvarchar(15) OUTPUT, @dept nvarchar(10) OUTPUT AS SELECT @sn=sn,@dept=dept FROM student WHERE sno=@sno 7.1.2 创建与执行存储过程 1．创建存储过程 10 — 1