第13讲 存储过程要点

2020/9/28
17
(1)创建数据库 --创建数据库 Create Database DBTEACH ON PRIMARY (NAME=Teach_dat, FILENAME='D:\YZ\DBTEACH.mdf') LOG ON (NAME=Teach_log, FILENAME='D:\YZ\DBTEACH.ldf') go
AS
SQL STATEMENT
其中：RECOMPILE---执行时重新编译，ENCRPTION---对存储 过程脚本加密。
执行
EXECUTE 存储过程名 参数表
修改存储过程
ALTER PROC 存储过程名
删除存储过程
2020/9/28 DROP PROC 存储过程名
7
2. 存储过程创建与执行示例
(1)模块化编程。
(2)快速执行。
(3)减少网络通信量。
(4)提供安全机制。
(5)保证操作一致性。
2020/9/28
5
(3) SQL Server存储过程的类型：
系统存储过程
➢由系统提供的存储过程，主要存储在master数据库中并以sp_ 为前缀
➢如：
sp_renamedb 'NewSales','sales'
2020/9/28
10
课堂练习：
写存储过程proc_AddCourse，用于向课程表增加新行 （用参数表示数据值)
写存储过程Proc_ModifyCourse，用于修改一个给定了 课程号的课程的课程名和学分。
课程表 课程号
课程名
学分
2020/9/28
11
3. 存储过程的执行状态
wenku.baidu.com关键词：状态值是一个整数，0表示成功，其他的都 用来表示执行错误。
关键词：预定义的，被存储的，SQL批处理。
(1) 存储过程的概念
是一组预编译的Transact-SQL语句，用于完成某项任务， 它可以接受参数、返回状态值和参数值，并且可以嵌 套调用。
(2) 存储过程的优点
存储过程 ，经编译后存放在数据库服务器端，供客户端调用， 因此存储过程可以充分利用服务器的高性能运算能力，而 无须把大量的结果集传送到客户处理，减少网络传输开销。
(DLL)。当扩展存储过程加载到SQL Server中，它的使用方法
与系统存储过程一样。扩展存储过程只能添加到master数据库
2020/9/28 中，其前缀是xp_。
6
(4) 存储过程的创建、修改、删除与执行
创建
CREATE PROC 存储过程名
参数表
[With {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION }]
IF @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)
sp_helpdb '数据库名‘--查看数据库列表
用户定义存储过程
➢由用户创建并能完成某一特定功能(例如查询用户所需数据信 息)的存储过程。它处于用户创建的数据库中
临时存储过程
➢在一个SQL SERVER实例中有效，重启后会消失。
扩展存储过程。
➢扩展存储过程是SQL Server可以动态装载和执行的动态链接库
RETURN -102 --值-102表示没有该课程
SELECT * FROM 成绩表 WHERE 学号=@sid AND 课程号=@cid
RETURN 0--值0表示过程运行没有出错。
2020/9/28
14
执行存储过程，获取状态值举例，接上片。
DECLARE @return_status int EXEC @return_status=proc_GetScoreBySidAndCid '001',
10.2.5 存储过程参数与状态值
（1）存储过程的状态值
状态值表示过程是否成功执行，或者过程失败的原因。
状态值是一个整数。
当存储过程成功执行时，状态值 0；当执行中发生错误， 则会返回一个“-99~ -1”之间的整数。这一功能是由系 统提供的，通常用来捕捉错误原因。
用户也可以在存储过程中自定义返回状态值，自定义 状态值是大于0或小于-99的整数。用RETURN返回状 态值。这种做法通常用来处理可预见的不正确执行方 式。
关键词：PROC，@，OUTPUT
(1)示例数据库
Teach数据库包含三个表，表结构如下：
学生表 学号 姓名 性别 身份证号 生日
成绩表 课程表
学号 课程号 成绩
课程号
课程名
学分
例
2020/9/28
9
10.2 存储过程的创建与使用
(1) 无参数的存储过程。
(2)带参数的存储过程 存储过程和调用者之间通过参数交换数据，参数可分 为：输入参数(input)和输出参数(output)。一个存储过 程可以有多个参数，用“,”分隔。 输入参数的定义格式：@参数名 类型 输出参数的定义格式：@参数名 类型 output 执行 ➢对输入参数，在EXEC语句中用常量传值 ➢对输出参数，先要定义一个存储结果的参数，在 EXEC语句中用OUTPUT显式声明它为输出项。
在执行存储过程时，可以用变量来存储状态值。格式：
EXECUTE @status_var=procedure_name
2020/9/28
13
10.2.5 存储过程参数与状态值
自定义状态值举例：创建存储过程，输入学号和课程 号，返回成绩。
CREATE PROC proc_GetScoreBySidAndCid @sid char(10)=NULL, @cid char(6)=NULL AS
'NN0001' IF @return_status=15
SELECT '没有提供参数' ELSE
IF @return_status=-101 SELECT '没有该学生'
ELSE IF @return_status=-102 SELECT '没有该课程'
2020/9/28
15
4. 综合实例—同学们自己体会
回顾
用约束来强制数据完整性 主键约束 外键约束——关系图，级联更新，级联删除。 唯一约束 检查约束 默认约束 非空约束
2020/9/28
1
数据库原理与应用
第13讲 存储过程
提纲
重点 对存储过程优点的理解 创建和执行存储过程的方法
难点 存储过程的输入参数、输出参数和状态值
2020/9/28
3
1. 存储过程