单元11 创建与管理存储过程

大型数据库（SQL Server） 单元11 创建与管理存储过程 19
预 习 单 元 12
思考问题： 1、什么时候使用触发器？有何作 用？ 2、触发器是在什么时候被触发执 行的？ 3、如何创建一个触发器？ 4、触发器中的INSERTEN表和 DELETED表和我们前面学过的 表格有什么不同之处？

大型数据库（SQL Server） 单元11 创建与管理存储过程 14

重 新 编 译 存 储 过 程
【问题11．13】使用WITH RECOMPILE子句。创建存储过程 p_StudentPara。该存储过程能根据给定 的班级编号返回该班级编号对应的所 有学生信息。要求每次执行时进行重 新编译和优化。 【问题11．14】以重新编译的方式执 行存储过程p_StudentPara。 【问题11．15】执行下面的语句将导 致使用Student表的触发器和存储过程 在下次执行时重新编译。
大型数据库（SQL Server） 单元11 创建与管理存储过程 15


系统存储过程： sp_who 显示当前用户和进程的信息 回顾学过的系统存储过程： 1、与“数据库”有关：
系 统 存 储 过 程
sp_helpdb、sp_helpfilegroup、sp_dboption、sp_renamedb
ALTER PROCEDURE procedure_name [WITH ENCRYPTION] [WITH RECOMPILE] AS sql_statement
修 改 存 储 过 程
WITH ENCRYPTION：以对存储过 程是定义进行加密。 【问题11．9】使用Transcact-SQL语 句。修改存储过程p_StudentPara， 使其能根据用户提供的班级名称， 而不是班级编号进行模糊查询，并 要求加密存储过程。
复 习 与 巩 固

大型数据库（SQL Server） 单元11 创建与管理存储过程 18
单 元 小 结
存储过程的作用 创建、执行简单的存储过程 创建、执行带参数（输入、输出） 的存储过程（重点） 重点：存储过程的创建和执行， 特别是带有参数的存储过程 修改、加密、重命名、删除存储 过程 重编译存储过程
大型数据库
单元 11 创建与管理存储过程
济宁职业技术学院
数据库课程组
学 习 目 标
【知识目标】 理解存储过程的作用。 学会根据需要创建、修改、删除存储 过程。 在实际应用开发时能够灵活运用存储 过程，以提高开发效率。
【技能目标】 能根据需要创建、修改和存储过程。 能根据实际需要在存储过程中定义并 使用输入参数、输出参数。
大型数据库（SQL Server） 单元11 创建与管理存储过程 8
创 建 和 执 行 带 输 出 参 数 的 存 储 过 程

声明输出参数的命令语法如下：
@parameter_name datatype=[default] OUTPUT
【问题11．7】创建存储过程 p_ClassNum，它能够根据用户给定的 班级编号统计该班学生人数，并将学 生人数返回给用户。
7、与“视图”有关：
sp_helptext、sp_depends
大型数据库（SQL Server） 单元11 创建与管理存储过程 16
扩 展 存 储 过 程
扩展存储过程： xp_cmdshell 执行操作系统命令 xp_enumgroups 列出域中的组的信息 xp_loginconfig 报告SQL Server的登录 安全信息
大型数据库（SQL Server） 单元11 创建与管理存储过程 6
创 建 、 执 行 、 查 看 存 储 过 程

创建存储过程（无参数）：
CREATE PROCEDURE procedure_name [WITH ENCRYPTION] [WITH RECOMPILE] AS sql_statement 其中： WITH ENCRYPTION：加密存储过程的定义。 WITH RECOMPILE：重新编译存储过程。

任 务 陈 述
大型数据库（SQL Server） 单元11 创建与管理存储过程 3

存 储 过 程 概 述

什么是存储过程？ 是一组被编译在一起的T-SQL语句 的集合，它们被集合在一起以完成 一个特定的任务。 【注意】存储过程保存在C/S的服务 器端 存储过程分类： 系统存储过程 用户自定义的存储过程 扩展存储过程（提供从SQL Server 到外部程序的接口，以便进行各种 维护活动）
大型数据库（SQL Server） 单元11 创建与管理存储过程 10

练 一 练
创建存储过程p_StuName，要求能 够根据用户给定的班级名称和学 生学号，查询该学生的姓名，并 将该姓名以变量的形式返回给用 户。
大型数据库（SQL Server） 单元11 创建与管理存储过程 11

修改格式：

大型数据库（SQL Server） 单元11 创建与管理存储过程 5
视图
存储过程
存 储 过 程 和 与 图 的 比 较
SQL 语句
只能是SELECT 语句
输入、 返回结果 典型应用
可以包含流控语 句、声明和使用 变量以及SELECT 语句 不能接受参数， 可以有输入、输 只能返回结果 出参数，也可以 集 有返回值 多个表格的连 接查询 完成某个特定的 较复杂的任务
大型数据库（SQL Server） 单元11 创建与管理存储过程 2

学生每个班的班主任都需要经常查看 他们班学生选修课程的信息； 教务处的老师经常需要查看某个系部 开设的选修课程情况、学生选修某一 门课程的情况等等。 现在需要在学生选课数据库Xk中创建 带有参数的存储，并在需要的时候修 改或删除存储过程。
【问题11．8】执行存储过程 p_ClassNum。
大型数据库（SQL Server） 单元11 创建与管理存储过程 9
练 一 练
1、创建名为p_StuByClass的存储 过程，返回“00电子商务”班 的所有学生的信息。 2、执行p_StuByClass存储过程， 并使用Management Studio查看 该存储过程的定义。 3、创建名为p_StuByPara的存储过 程，根据给定的班级编码和学 生学号，返回相应学生的信息。 【拓展】当缺省参数执行存储过程 时，如何处理？
大型数据库（SQL Server） 单元11 创建与管理存储过程 4
存 储 过 程 的 作 用
从以下几个方面考虑： 1、模块化编程：创建的存储过程保存在 数据库中，可以被其他程序反复使用。 2、快速执行：存储过程第一次被执行后 就驻留在内存中。以后执行就省去了 重新分析、优化、编译的过程。 3、减少网络通信量：有了存储过程后， 在网络上只需要传送一条语句就能执 行存储过程。 4、安全机制：通过隔离和加密的方法提 高了数据库的安全性：通过授权可以 让用户只能执行存储过程而不能直接 访问数据库对象；另外，存储过程的 定义可以被加密。
2、与“表”有关：
sp_help、sp_rename
3、与“索引”有关：
sp_helpindex、sp_rename
4、与“默认值”有关：
sp_helpindex、sp_rename
5、与“规则”有关：
sp_unbindrule、sp_unbindrule
6、与“用户自定义数据类型”有关：
sp_addtype、sp_droptype

【问题11．1】使用T-SQL语句，在Xk数据库 中创建存储过程p_Student。该存储过程返回 Student表中班级编号为“20000001”的数据行。 创建p_Student（略） 执行p_Student ：EXEC p_Student
【问题11．3】在SQL Server Management Studio 中查看存储过程p_Student的属性。

【问题11．17】执行下列 xp_cmdshell 语句将返回指定目录的匹配文件列表。
EXEC xp_cmdshell 'dir c:\*.exe'
大型数据库（SQL Server） 单元11 创建与管理存储过程 17
源自文库

独自完成课本page224实训
创建存储过程，返回系部编码为“01” 的所有班级的名称。 创建带输入参数的存储过程，根据给 定的学生学号，返回该学生选修的所 有课程的信息，包括：课程名、上课 时间、学分、授课教师。并执行该存 储过程，以得到学号为“00000001”的 学生选修的课程信息。 创建带输入、输出参数的存储过程， 以得到给定系部编码对应的系部所开 设课程的总报名人数。并编程执行该 存储过程。
大型数据库（SQL Server） 单元11 创建与管理存储过程 7
创 建 和 执 行 带 参 数 的 存 储 过 程
带参数的存储过程：
CREATE PROCEDURE procedure_name @parameter_name datatype=[default] [WITH ENCRYPTION] [WITH RECOMPILE] AS sql_statement

大型数据库（SQL Server） 单元11 创建与管理存储过程 20
大型数据库（SQL Server） 单元11 创建与管理存储过程 12
重 命 名 、 删 除 存 储 过 程

重命名存储过程：
【问题11．10】将存储过程p_ClassNum 重新命名为p_CalcClassNum。

删除存储过程：
DROP PROCEDURE proc_name

【问题11．11】使用Transcact-SQL 语句。删除存储过程p_Student。
大型数据库（SQL Server） 单元11 创建与管理存储过程 13

重 新 编 译 存 储 过 程
什么情况下重新编译？ 进行了索引或影响数据库统计 的更改后，需要重新编译，以 重新优化查询。
重新编译存储过程的3种方法： 1、在创建时指明重编译 2、在执行时指明重编译 3、使用系统存储过程重新编译
【问题11．4】使用Transcact-SQL语句。创建 存储过程p_StudentPara。该存储过程能根据 给定的班级编号，返回该班级编号对应的所 有学生信息。 创建p_StudentPara（略）。 执行带输入参数的存储过程（两种方法）：
EXEC p_StudentPara @ClassNo='20000001‘ GO EXEC p_StudentPara '20000001 GO