第13讲 存储过程

1、2021-7-121 回顾回顾 用约束来强制数据完整性用约束来强制数据完整性 主键约束主键约束 外键约束外键约束关系图，级联更新，级联删除。关系图，级联更新，级联删除。 唯一约束唯一约束 检查约束检查约束 默认约束默认约束 非空约束非空约束 数据库原理与应用数据库原理与应用 第第13讲讲 存储过程存储过程 2021-7-123 提纲提纲 重点重点 对存储过程优点的理解对存储过程优点的理解 创建和执行存储过程的方法创建和执行存储过程的方法 难点难点 存储过程的输入参数、输出参数和状态值存储过程的输入参数、输出参数和状态值 1. 存储过程存储过程 关键词：预定义的，被存储的，关键词：预定义的，被存

2、储的，SQL批处理。批处理。 2021-7-125 (1) 存储过程的概念存储过程的概念 是一组预编译的是一组预编译的Transact-SQL语句，用于完成某项任务，语句，用于完成某项任务， 它可以它可以接受参数、返回状态值和参数值接受参数、返回状态值和参数值，并且可以，并且可以嵌嵌 套套调用。调用。 (2) 存储过程的优点存储过程的优点 存储过程存储过程 ，经编译后存放在数据库服务器端，供客户端调用，经编译后存放在数据库服务器端，供客户端调用， 因此存储过程可以充分利用服务器的高性能运算能力，而因此存储过程可以充分利用服务器的高性能运算能力，而 无须把大量的结果集传送到客户处理，减少网络传输

3、开销。无须把大量的结果集传送到客户处理，减少网络传输开销。 (1)模块化编程。模块化编程。 (2)快速执行。快速执行。 (3)减少网络通信量。减少网络通信量。 (4)提供安全机制。提供安全机制。 (5)保证操作一致性。保证操作一致性。 2021-7-126 (3) SQL Server存储过程的类型：存储过程的类型： p系统存储过程系统存储过程 由系统提供的存储过程，主要存储在由系统提供的存储过程，主要存储在master数据库中并以数据库中并以sp_ 为前缀为前缀 如：如： sp_renamedb NewSales,sales sp_helpdb 数据库名数据库名-查看数据库列表查看数据库列表

4、 p用户定义存储过程用户定义存储过程 由用户创建并能完成某一特定功能由用户创建并能完成某一特定功能(例如查询用户所需数据信例如查询用户所需数据信 息息)的存储过程。它处于用户创建的数据库中的存储过程。它处于用户创建的数据库中 p临时存储过程临时存储过程 在一个在一个SQL SERVER实例中有效，重启后会消失。实例中有效，重启后会消失。 p扩展存储过程。扩展存储过程。 扩展存储过程是扩展存储过程是SQL Server可以动态装载和执行的动态链接库可以动态装载和执行的动态链接库 (DLL)。当扩展存储过程加载到当扩展存储过程加载到SQL Server中，它的使用方法中，它的使用方法 与系统存储过

5、程一样与系统存储过程一样。扩展存储过程只能添加到。扩展存储过程只能添加到master数据库数据库 中，其前缀是中，其前缀是xp_。 2021-7-127 (4) 存储过程的创建、修改、删除与执行存储过程的创建、修改、删除与执行 n创建创建 CREATE PROC 存储过程名存储过程名 参数表参数表 With RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIO N AS SQL STATEMENT 其中：其中：RECOMPILE-执行时重新编译，执行时重新编译，ENCRPTION-对存储对存储 过程脚本加密。过程脚本加密。 p执行执行 EXECUTE 存储过程名存储

6、过程名 参数表参数表 p修改存储过程修改存储过程 ALTER PROC 存储过程名存储过程名 p删除存储过程删除存储过程 DROP PROC 存储过程名存储过程名 2. 存储过程创建与执行示例存储过程创建与执行示例 关键词：关键词：PROC，OUTPUT 2021-7-129 学号学号课程号课程号成绩成绩 学号学号姓名姓名性别性别身份证号身份证号生日生日 课程号课程号课程名课程名学分学分 学生表学生表 课程表课程表 成绩表成绩表 (1)示例数据库示例数据库 Teach数据库包含三个表，表结构如下：数据库包含三个表，表结构如下： 例例 2021-7-1210 10.2 10.2 存储过程的创建与

7、使用存储过程的创建与使用 (1) 无参数的存储过程。无参数的存储过程。 (2)带参数的存储过程带参数的存储过程 p存储过程和调用者之间通过参数交换数据，参数可分存储过程和调用者之间通过参数交换数据，参数可分 为：输入参数为：输入参数(input)和输出参数和输出参数(output)。一个存储过。一个存储过 程可以有多个参数，用程可以有多个参数，用“,”分隔。分隔。 p输入参数的定义格式：输入参数的定义格式：参数名参数名 类型类型 p输出参数的定义格式：输出参数的定义格式：参数名参数名 类型类型 output p执行执行 对输入参数，在对输入参数，在EXEC语句中用常量传值语句中用常量传值 对输

8、出参数，先要定义一个存储结果的参数，在对输出参数，先要定义一个存储结果的参数，在 EXEC语句中用语句中用OUTPUT显式声明它为输出项。显式声明它为输出项。 2021-7-1211 课堂练习：课堂练习： 写存储过程写存储过程proc_AddCourse，用于向课程表增加新行，用于向课程表增加新行 （用参数表示数据值（用参数表示数据值) 写存储过程写存储过程Proc_ModifyCourse，用于修改一个给定了，用于修改一个给定了 课程号的课程的课程名和学分。课程号的课程的课程名和学分。 课程号课程号课程名课程名学分学分 课程表课程表 3. 存储过程的执行状态存储过程的执行状态 关键词：状态值

9、是一个整数，关键词：状态值是一个整数，0表示成功，其他的都表示成功，其他的都 用来表示执行错误。用来表示执行错误。 2021-7-1213 10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值 （1）存储过程的状态值）存储过程的状态值 p状态值表示过程是否成功执行，或者过程失败的原因。状态值表示过程是否成功执行，或者过程失败的原因。 p状态值是一个整数。状态值是一个整数。 p当存储过程成功执行时，状态值当存储过程成功执行时，状态值 0；当执行中发生错误，；当执行中发生错误， 则会返回一个则会返回一个“-99 -1”之间的整数。这一功能是由系之间的整数。这一功能是由系 统提供的，

10、通常用来捕捉错误原因。统提供的，通常用来捕捉错误原因。 p用户也可以在存储过程中自定义返回状态值，自定义用户也可以在存储过程中自定义返回状态值，自定义 状态值是状态值是大于大于0或小于或小于-99的整数。用的整数。用RETURN返回状返回状 态值。这种做法通常用来处理可预见的不正确执行方态值。这种做法通常用来处理可预见的不正确执行方 式。式。 p在执行存储过程时，可以用变量来存储状态值。格式：在执行存储过程时，可以用变量来存储状态值。格式： EXECUTE status_var=procedure_name 2021-7-1214 10.2.5 10.2.5 存储过程参数与状态值存储过程参数与

11、状态值 p自定义状态值举例：创建存储过程，输入学号和课程自定义状态值举例：创建存储过程，输入学号和课程 号，返回成绩。号，返回成绩。 CREATE PROC proc_GetScoreBySidAndCid sid char(10)=NULL, cid char(6)=NULL AS IF sid=NULL or cid=NULL RETURN 15 -用值用值15表示用户没有提供参数表示用户没有提供参数 IF NOT EXISTS (SELECT * FROM 学生表学生表 WHERE 学号学号=sid) RETURN -101 -值值-l01表示没有该学生表示没有该学生 IF NOT EX

12、ISTS (SELECT * FROM 课程表课程表 WHERE 课程号课程号=cid) RETURN -102 -值值-102表示没有该课程表示没有该课程 SELECT * FROM 成绩表成绩表 WHERE 学号学号=sid AND 课程号课程号=cid RETURN 0-值值0表示过程运行没有出错。表示过程运行没有出错。 2021-7-1215 p执行存储过程，获取状态值举例，接上片。执行存储过程，获取状态值举例，接上片。 DECLARE return_status int EXEC return_status=proc_GetScoreBySidAndCid 001, NN0001 I

13、F return_status=15 SELECT 没有提供参数没有提供参数 ELSE IF return_status=-101 SELECT 没有该学生没有该学生 ELSE IF return_status=-102 SELECT 没有该课程没有该课程 4. 综合实例综合实例同学们自己体会同学们自己体会 2021-7-1217 2021-7-1218 (1)创建数据库创建数据库 -创建数据库创建数据库 Create Database DBTEACH ON PRIMARY (NAME=Teach_dat, FILENAME=D:YZDBTEACH.mdf) LOG ON (NAME=Teac

14、h_log, FILENAME=D:YZDBTEACH.ldf) go -打开数据库打开数据库 use DBteach go 2021-7-1219 （2） 创建数据表创建数据表 if Exists (Select * from sysobjects where name=系表系表) DROP TABLE 系表系表 go CREATE TABLE 系表系表 ( 系名系名 nchar(10) PRIMARY KEY, 办公地址办公地址 nchar(20) NOT NULL ) go if EXISTS (SELECT * FROM SYSOBJECTS WHERE name=学期表学期表) DR

15、OP TABLE 学期表学期表 go CREATE TABLE 学期表学期表 ( 学期编号学期编号 int identity(1,1) PRIMARY KEY, 学年学年 nchar(12) NOT NULL, 学期学期 nchar(4) NOT NULL, CHECK (学期学期=第一学期第一学期 or 学期学期=第二学期第二学期 ), CHECK (学年学年 Like 0-90-90-90-9-0-90-90-90-9%) ) go 2021-7-1220 (3) 创建存储过程创建存储过程-1 -proc_AddDepart添加系表数据添加系表数据 CREATE PROCEDURE pro

16、c_AddDepart deptname nchar(10), Address nchar(10) AS INSERT INTO 系表系表(系名系名,办公地址办公地址) VALUES(deptname,Address) go -初始化系表数据初始化系表数据 CREATE PROCEDURE proc_initDeptData AS DELETE 系表系表 EXEC proc_AddDepart 网络技术系网络技术系,综合楼四楼综合楼四楼 EXEC Proc_AddDepart 计算机系计算机系,综合楼二楼综合楼二楼 EXEC Proc_AddDepart 管理系管理系,综合楼三楼综合楼三楼 g

17、o EXEC proc_initDeptData go 2021-7-1221 3) 创建存储过程创建存储过程-2 -Proc_AddTerm:添加学期添加学期 CREATE PROCEDURE proc_AddTerm -添加学期添加学期 year nchar(12), term nchar(4) as INSERT INTO 学期表学期表(学年学年,学期学期) VALUES (year,term) go Create PROCEDURE proc_InitTermData-学期表初始数据学期表初始数据 AS DELETE 学期表学期表 EXEC proc_AddTerm 2007-2008,第一学期第一学期 EXEC proc_AddTerm 2007-2008,第二学期第二学期 EXEC proc_AddTerm 2008-2009,第一学期第一学期 EXEC proc_AddTerm 2008-2009,第二学期第二学期 EXEC proc_AddTerm 2009-2010,第一学期第一学期 EXEC proc_AddTerm 2009-2010,第二学期第二学期 EXEC proc_AddTerm 2010-2011,第一学期第一学期 EXEC proc_AddTerm 2