SQLServer2019数据库及应用理存储过程

生学号，查询该学生的姓名，并
将该姓名以变量的形式返回给用
练
户。
一
练
大型数据库（SQL Server） 单元11 创建与管理存储过程 11
? 修改格式：
ALTER PROCEDURE procedure_name
[WITH ENCRYPTION] [WITH RECOMPILE]
AS
修
sql_statement
大型数据库（SQL Server） 单元11 创建与管理存储过程 3
? 什么是存储过程？
? 是一组被编译在一起的 T-SQL 语句
的集合，它们被集合在一起以完成
一个特定的任务。
存
? 【注意】存储过程保存在 C/S的服务
储
器端
过
? 存储过程分类：
程
? 系统存储过程
概
? 用户自定义的存储过程
述
? 扩展存储过程（提供从 SQL Server 到外部程序的接口，以便进行各种
7
带参数的存储过程：
创 建 和
Cp[[RaWWEArIITaTTE mHHPReOCtEREeNEDUrCCR_ROEnYMpraPPocmTIedueILreOE_ndN]ama]etatype=[de
AS
执
sql_statement
行
【问题11 ．4】使用Transcact-SQL 语句。创建
看
Student表中班级编号为“20000001”的数据行。
存
创建p_Student（略） 执行p_Student ：EXEC p_Student
储
过
【问题11．3】在SQL Server Management Studio 中查看存储过程p_Student的属性。
程
大型数据库（SQL Server） 单元11 创建与管理存储过程
过
程
大型数据库（SQL Server） 单元11 创建与管理存储过程
8
创 建
? 声明输出参数的命令语法如下：
和
parameter_name datatype=[default] OUTPUT
执
行
【问题 11 ．7】创建存储过程
带
p_ClassNum ，它能够根据用户给定的
输 出
班级编号统计该班学生人数，并将学 生人数返回给用户。
带
存储过程p_StudentPara。该存储过程能根据
参
给定的班级编号，返回该班级编号对应的所 有学生信息。
数
创建p_StudentPara（略）。
的
执行带输入参数的存储过程（两种方法）：
存
EXEC p_StudentPara ClassNo='20000001‘ GO
储
EXEC p_StudentPara '20000001 GO
大型数据库
单元 11 创建与管理存储过程
深圳职业技术学院 大型数据库课程组
【知源自文库目标】
? 理解存储过程的作用。
? 学会根据需要创建、修改、删除存储 过程。
学 习
? 在实际应用开发时能够灵活运用存储 过程，以提高开发效率。
目 标
【技能目标】 ? 能根据需要创建、修改和存储过程。
? 能根据实际需要在存储过程中定义并 使用输入参数、输出参数。
的 4、安全机制： 通过隔离和加密的方法提
作
高了数据库的安全性：通过授权可以 让用户只能执行存储过程而不能直接
用
访问数据库对象；另外，存储过程的
定义可以被加密。
大型数据库（SQL Server） 单元11 创建与管理存储过程 5
视图
存储过程
存
储
SQL 语句 只能是SELECT 可以包含流控语
过
语句
练
该存储过程的定义。
一 练
3、创建名为p_StuByPara的存储过 程，根据给定的班级编码和学
生学号，返回相应学生的信息。
【拓展】当缺省参数执行存储过程 时，如何处理？
大型数据库（SQL Server） 单元11 创建与管理存储过程 10
? 创建存储过程p_StuName，要求能
够根据用户给定的班级名称和学
? 重命名存储过程：
重
【问题11．10】将存储过程 p_ClassNum
建
[WITH ENCRYPTION]
[WITH RECOMPILE]
、
AS
执
sql_statement 其中：
行
WITH ENCRYPTION：加密存储过程的定义。 WITH RECOMPILE：重新编译存储过程。
、
查
? 【问题11．1】使用T-SQL语句，在Xk数据库 中创建存储过程p_Student。该存储过程返回
维护活动）
大型数据库（SQL Server） 单元11 创建与管理存储过程 4
? 从以下几个方面考虑 ：
1、模块化编程 ：创建的存储过程保存在 数据库中，可以被其他程序反复使用。
存
2、快速执行： 存储过程第一次被执行后 就驻留在内存中。以后执行就省去了
储
重新分析、优化、编译的过程。
过 程
3、减少网络通信量 ：有了存储过程后， 在网络上只需要传送一条语句就能执 行存储过程。
句、声明和使用
程
变量以及SELECT 语句
和
输入、 不能接受参数， 可以有输入、输
与
返回结果 只能返回结果 出参数，也可以
图
集
有返回值
的
典型应用 多个表格的连 完成某个特定的
接查询
较复杂的任务
比
较
大型数据库（SQL Server） 单元11 创建与管理存储过程 6
创
? 创建存储过程（无参数）：
CREATE PROCEDURE procedure_name
参
数
【问题 11 ．8】执行存储过程
的
p_ClassNum 。
存
储
过
程
大型数据库（SQL Server） 单元11 创建与管理存储过程 9
1、创建名为p_StuByClass的存储 过程，返回“00电子商务”班 的所有学生的信息。
2、执行p_StuByClass存储过程，
并使用Management Studio查看
大型数据库（SQL Server） 单元11 创建与管理存储过程 2
? 学生每个班的班主任都需要经常查看 他们班学生选修课程的信息；
? 教务处的老师经常需要查看某个系部
任
开设的选修课程情况、学生选修某一
务
门课程的情况等等。
陈
现在需要在学生选课数据库 Xk中创建
述
带有参数的存储，并在需要的时候修 改或删除存储过程。
改 存
WITH ENCRYPTION ：以对存储过 程是定义进行加密。
储 过
【问题11．9】使用Transcact-SQL 语 句。修改存储过程 p_StudentPara ，
程
使其能根据用户提供的班级名称，
而不是班级编号进行模糊查询，并
要求加密存储过程。
大型数据库（SQL Server） 单元11 创建与管理存储过程 12