第13讲存储过程

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

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

3、。(1)模块化编程。模块化编程。(2)快速执行。快速执行。 (3)减少网络通信量。减少网络通信量。(4)提供安全机制。提供安全机制。(5)保证操作一致性。保证操作一致性。2022-5-116(3) SQL Server存储过程的类型：存储过程的类型：p系统存储过程系统存储过程由系统提供的存储过程，主要存储在由系统提供的存储过程，主要存储在master数据库中并以数据库中并以sp_为前缀为前缀如：如：sp_renamedb NewSales,sales sp_helpdb 数据库名数据库名-查看数据库列表查看数据库列表 p用户定义存储过程用户定义存储过程由用户创建并能完成某一特定功能由用户创建并

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

5、库中，其前缀是中，其前缀是xp_。2022-5-117(4) 存储过程的创建、修改、删除与执行存储过程的创建、修改、删除与执行n创建创建 CREATE PROC 存储过程名存储过程名 参数表参数表With RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS SQL STATEMENT其中：其中：RECOMPILE-执行时重新编译，执行时重新编译，ENCRPTION-对存储对存储过程脚本加密。过程脚本加密。p执行执行 EXECUTE 存储过程名存储过程名 参数表参数表p修改存储过程修改存储过程 ALTER PROC 存储过程名存储过程名p删除存储过程删除存储

6、过程DROP PROC 存储过程名存储过程名2. 存储过程创建与执行示例存储过程创建与执行示例关键词：关键词：PROC，OUTPUT2022-5-119学号学号课程号课程号成绩成绩学号学号姓名姓名性别性别身份证号身份证号生日生日课程号课程号课程名课程名学分学分学生表学生表课程表课程表成绩表成绩表(1)示例数据库示例数据库Teach数据库包含三个表，表结构如下：数据库包含三个表，表结构如下：例例2022-5-111010.2 10.2 存储过程的创建与使用存储过程的创建与使用(1) 无参数的存储过程。无参数的存储过程。(2)带参数的存储过程带参数的存储过程p存储过程和调用者之间通过参数交换数据，

7、参数可分存储过程和调用者之间通过参数交换数据，参数可分为：输入参数为：输入参数(input)和输出参数和输出参数(output)。一个存储过。一个存储过程可以有多个参数，用程可以有多个参数，用“,”分隔。分隔。p输入参数的定义格式：输入参数的定义格式：参数名参数名 类型类型 p输出参数的定义格式：输出参数的定义格式：参数名参数名 类型类型 outputp执行执行对输入参数，在对输入参数，在EXEC语句中用常量传值语句中用常量传值对输出参数，先要定义一个存储结果的参数，在对输出参数，先要定义一个存储结果的参数，在EXEC语句中用语句中用OUTPUT显式声明它为输出项。显式声明它为输出项。2022

8、-5-1111课堂练习：课堂练习：写存储过程写存储过程proc_AddCourse，用于向课程表增加新行，用于向课程表增加新行（用参数表示数据值（用参数表示数据值)写存储过程写存储过程Proc_ModifyCourse，用于修改一个给定了，用于修改一个给定了课程号的课程的课程名和学分。课程号的课程的课程名和学分。课程号课程号课程名课程名学分学分课程表课程表3. 存储过程的执行状态存储过程的执行状态关键词：状态值是一个整数，关键词：状态值是一个整数，0表示成功，其他的都表示成功，其他的都用来表示执行错误。用来表示执行错误。2022-5-111310.2.5 10.2.5 存储过程参数与状态值存储

9、过程参数与状态值（1）存储过程的状态值）存储过程的状态值p状态值表示过程是否成功执行，或者过程失败的原因。状态值表示过程是否成功执行，或者过程失败的原因。p状态值是一个整数。状态值是一个整数。p当存储过程成功执行时，状态值当存储过程成功执行时，状态值 0；当执行中发生错误，；当执行中发生错误，则会返回一个则会返回一个“-99 -1”之间的整数。这一功能是由系之间的整数。这一功能是由系统提供的，通常用来捕捉错误原因。统提供的，通常用来捕捉错误原因。p用户也可以在存储过程中自定义返回状态值，自定义用户也可以在存储过程中自定义返回状态值，自定义状态值是状态值是大于大于0或小于或小于-99的整数。用的

10、整数。用RETURN返回状返回状态值。这种做法通常用来处理可预见的不正确执行方态值。这种做法通常用来处理可预见的不正确执行方式。式。p在执行存储过程时，可以用变量来存储状态值。格式：在执行存储过程时，可以用变量来存储状态值。格式：EXECUTE status_var=procedure_name2022-5-111410.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值p自定义状态值举例：创建存储过程，输入学号和课程自定义状态值举例：创建存储过程，输入学号和课程号，返回成绩。号，返回成绩。CREATE PROC proc_GetScoreBySidAndCidsid char(1

11、0)=NULL,cid char(6)=NULLASIF sid=NULL or cid=NULL RETURN 15 -用值用值15表示用户没有提供参数表示用户没有提供参数IF NOT EXISTS (SELECT * FROM 学生表学生表 WHERE 学号学号=sid) RETURN -101 -值值-l01表示没有该学生表示没有该学生IF NOT EXISTS (SELECT * FROM 课程表课程表 WHERE 课程号课程号=cid) RETURN -102 -值值-102表示没有该课程表示没有该课程SELECT * FROM 成绩表成绩表WHERE 学号学号=sid AND 课程

12、号课程号=cidRETURN 0-值值0表示过程运行没有出错。表示过程运行没有出错。 2022-5-1115p执行存储过程，获取状态值举例，接上片。执行存储过程，获取状态值举例，接上片。DECLARE return_status intEXEC return_status=proc_GetScoreBySidAndCid 001, NN0001IF return_status=15 SELECT 没有提供参数没有提供参数ELSE IF return_status=-101 SELECT 没有该学生没有该学生ELSE IF return_status=-102 SELECT 没有该课程没有该课程

13、4. 综合实例综合实例同学们自己体会同学们自己体会2022-5-11172022-5-1118(1)创建数据库创建数据库-创建数据库创建数据库Create Database DBTEACHON PRIMARY(NAME=Teach_dat, FILENAME=D:YZDBTEACH.mdf)LOG ON(NAME=Teach_log,FILENAME=D:YZDBTEACH.ldf)go-打开数据库打开数据库use DBteachgo2022-5-1119（2） 创建数据表创建数据表if Exists (Select * from sysobjects where name=系表系表)DROP

14、 TABLE 系表系表goCREATE TABLE 系表系表(系名系名 nchar(10) PRIMARY KEY,办公地址办公地址 nchar(20) NOT NULL)goif EXISTS (SELECT * FROM SYSOBJECTS WHERE name=学期表学期表)DROP TABLE 学期表学期表goCREATE TABLE 学期表学期表(学期编号学期编号 int identity(1,1) PRIMARY KEY,学年学年 nchar(12) NOT NULL,学期学期 nchar(4) NOT NULL,CHECK (学期学期=第一学期第一学期 or 学期学期=第二学期

15、第二学期 ),CHECK (学年学年 Like 0-90-90-90-9-0-90-90-90-9%)go2022-5-1120(3) 创建存储过程创建存储过程-1-proc_AddDepart添加系表数据添加系表数据CREATE PROCEDURE proc_AddDepartdeptname nchar(10),Address nchar(10)ASINSERT INTO 系表系表(系名系名,办公地址办公地址)VALUES(deptname,Address)go-初始化系表数据初始化系表数据CREATE PROCEDURE proc_initDeptDataASDELETE 系表系表EXE

16、C proc_AddDepart 网络技术系网络技术系,综合楼四楼综合楼四楼EXEC Proc_AddDepart 计算机系计算机系,综合楼二楼综合楼二楼EXEC Proc_AddDepart 管理系管理系,综合楼三楼综合楼三楼goEXEC proc_initDeptDatago2022-5-11213) 创建存储过程创建存储过程-2-Proc_AddTerm:添加学期添加学期CREATE PROCEDURE proc_AddTerm -添加学期添加学期year nchar(12),term nchar(4)asINSERT INTO 学期表学期表(学年学年,学期学期) VALUES (yea

17、r,term)goCreate PROCEDURE proc_InitTermData-学期表初始数据学期表初始数据ASDELETE 学期表学期表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 2010