13_存储过程

带多个输入参数的存储过程举例 (1)：用于向成绩表插入一行的存储过程。


创建
CREATE PROCEDURE Proc_AddScore
@sid char(10), @cid char(6), @score int AS INSERT INTO 成绩表(学号,课程号,成绩) VALUES( @sid, @cid, @score)


(3)减少网络通信量。


(4)提供安全机制。


(5)保证操作一致性。


(3) SQL Server存储过程的类型：

系统存储过程

由系统提供的存储过程，主要存储在master数据库中并 以sp_为前缀 由用户创建并能完成某一特定功能(例如查询用户所需 数据信息)的存储过程。它处于用户创建的数据库中 在一个SQL SERVER实例中有效，重启后会消失。 扩展存储过程是SQL Server可以动态装载和执行的动态 链接库(DLL)。当扩展存储过程加载到SQL Server中， 它的使用方法与系统存储过程一样。扩展存储过程只能 添加到master数据库中，其前缀是xp_。
性别
班号 成绩 学分
课程号
课程表
课程名

(1) 无参数的存储过程。

从学生表和成绩表的连接中返回‘0102200114’ 学生的学号，姓名，课程编号，成绩的存储过程 。


创建
CREATE PROC proc_GetAllScore AS SELECT a.学号,a.姓名, b.课程号,b. 成绩 FROM 学生表 a JOIN 成绩表 b ON a.学号=b.学号
第13讲 存储过程
提纲

重点

对存储过程优点的理解
创建和执行存储过程的方法

难点

存储过程的输入参数、输出参数和状态值
1. 存储过程
关键词：预定义的，被存储的
SQL批处理。

(1) 存储过程的概念

是一组预编译的Transact-SQL语句，用于完成某项任务，它可以接 受参数、返回状态值和参数值，并且可以嵌套调用。


AS
UPDATE 成绩表 SET 成绩=@score
WHERE 学号=@sid and 课程号=@cid


执行：
EXEC Proc_UpdateScore ‘0100200114’, ‘NN1018’, 83

带输入和输出参数的存储过程 举例：用于获取某学生修读的总学分的存储过程。
课程表
课程号
课程名
学分
3. 存储过程的执行状态
关键词：状态值是一个整数，0表示成功，其他
的都用来表示执行错误。
10.2.5 存储过程参数与状态值

（1）存储过程的状态值



状态值表示过程是否成功执行，或者过程失败的原 因。 状态值是一个整数。 当存储过程成功执行时，状态值 0；当执行中发生 错误，则会返回一个‚-99~ -1”之间的整数。这一 功能是由系统提供的，通常用来捕捉错误原因。 用户也可以在存储过程中自定义返回状态值，自定 义状态值是大于0或小于-99的整数。用RETURN返 回状态值。这种做法通常用来处理可预见的不正确 执行方式。 在执行存储过程时，可以用变量来存储状态值。格 式：EXECUTE @status_var=procedure_name
2. 3.


创建


CREATE PROCEDURE Proc_GETPointBySid @sid char(10)， @point int output AS SELECT @point=sum(b.学分) FROM 课程表 a join 成绩表 b on a.课程号=b.课程号 WHERE a.学号 =@sid Print @sid+’修读总学分是：’+ convert(char,@point)

Where 学号=‘0102200114’


执行
EXEC proc_GetAllScore
10.2 存储过程的创建与使用

(2)带参数的存储过程

存储过程和调用者之间通过参数交换数据，参数可 分为：输入参数(input)和输出参数(output)。一个存 储过程可以有多个参数，用‚,”分隔。 输入参数的定义格式：@参数名 类型 输出参数的定义格式：@参数名 类型 output 执行
练习：定义存储过 程，实现当调用存 储过程时向成绩表 中插入数据。



AS
SELECT a.学号,a.姓名, b.课程号,b. 成绩 FROM 学生表 a JOIN 成绩表 b ON a.学号=b.学号 Where 班号=@sid


执行
EXEC proc_GetScoreBySid ‘0102200114’





执行存储过程，获取状态值举例，接上片。
DECLARE @return_status int EXEC @return_status=proc_GetScoreBySidAndCid ‘0100200114’, ‘NN1018’
ห้องสมุดไป่ตู้

IF @return_status=15
回顾

约束

主键约束、外键约束、唯一约束、检查约束、默认 约束、非空约束 有INSTEAD OF触发器和AFTER触发器两种。
当进行增删改操作时，先执行INSTEAD OF触发 器，再进行约束验证，再执行AFTER触发器。 使用替代触发器实现通过视图更新多表数据

触发器



实验讲解

数据库原理与应用

(2) 存储过程的优点

(1)模块化编程。

一次创建，重复调用。
创建存储过程（大量T-SQL代码），再执行不用再分析，优化 和编译 几百条T-SQL语句组成的存储过程，执行仅用一条语句在网络 上传输。
对语句没有权限的用户，可以授权执行含该语句的存储过程。 封装查询，保持功能一致性。

(2)快速执行。
10.2.5 存储过程参数与状态值

自定义状态值举例：创建存储过程，输入学号和课 程号，返回成绩。

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 EXISTS (SELECT * FROM 课程表 WHERE 学号=@cid) RETURN -102 --值-102表示没有该课程 SELECT * FROM 成绩表 WHERE 学号=@sid AND 课程号=@cid RETURN 0--值0表示过程运行没有出错。


对输入参数，在EXEC语句中用常量传值 对输出参数，先要定义一个存储结果的参数，在EXEC 语句中用OUTPUT显式声明它为输出项。

带一个输入参数的存储过程举例:

用于查询某个班级的选课信息的存储过程
CREATE PROCEDURE proc_GetScoreBySid @sid char(10) AS CREATE PROC proc_GetAllScore
EXECUTE 存储过程名 参数表 ALTER PROC 存储过程名

执行


修改存储过程


删除存储过程

DROP PROC 存储过程名
2. 存储过程创建与执行 示例
关键词：PROC，@，OUTPUT
(1)示例数据库
Teach数据库包含三个表，表结构如下：
学生表 成绩表
学号 学号 课程号
姓名

执行：
EXEC Proc_GetPointBySid ‘0100200114’, @point OUTPUT 说明：在SQL语句体中生命变量，用DECLARE，SELECT用于 显示查询结果。


DECLARE @point int

课堂练习：

写存储过程Proc_ModifyCourse，用于修改一个给 定了课程号的课程的课程名和学分。

用户定义存储过程


临时存储过程

扩展存储过程。


(4) 存储过程的创建、修改、删除与执行

创建

CREATE PROC 存储过程名 参数表 [With {RECOMPILE|ENCRYPTION}] AS SQL STATEMENT

其中：RECOMPILE---执行时重新编译，ENCRPTION--对存储过程脚本加密。
SELECT ‘没有提供参数' ELSE
IF @return_status=-101
SELECT ‘没有该学生’ ELSE IF @return_status=-102 SELECT ‘没有该课程'
总结
1.
存储过程是一组SQL语句和流程控制语句的集合 以一个名字存储并作为一个单元处理。 存储过程它可以接受输入参数和输出参数 存储过程的执行返回状态值，状态值为0表示执 行成功。


执行：
EXEC Proc_AddScore ‘0100200114’, ‘NN1018’, 83