存储过程

存储过程
问题 要把完成某功能的SQL语句系列做成 类似C语言的函数，供需要时调用， 如何做？ 什么是存储过程？

概 述


Baidu Nhomakorabea
是一组被编辑在一起的T-SQL语句的集合， 它们被集合在一起以完成一个特定的任 务。
存储过程的分类


系统存储过程（以sp_作前缀） 扩展存储过程（提供从SQL Server到外部 程序的接口，以便进行各种维护活动， 以xp_作前缀） 用户自定义的存储过程
执 行 带 输 入 参 数 的 存 储 过 程

执行格式：
EXEC proc_name [@para_name = ] value, …


指定参数名的格式（位置顺序 可以任意，要么全指定，要么 全不指定） 不指定参数名的格式（位置必 须对应）
1.
创建一个名为ScBySnoCno的存储 过程，根据给定的学生学号和课 程号，显示相应学生的选课信息

调用：
DECLARE @NUM SMALLINT EXEC P_DeptNum @DEPTID='MA' , @DeptNum = @NUM OUTPUT PRINT @NUM
练 习
【练习】创建存储过程Cou_credit， 要求能根据用户给定的学分值，统 计满足该学分值的课程数目，并把 它返回给调用程序。 如要统计2个学分的课程门数，该如 何调用上述存储过程。 【练习】创建名为mod_credit的存储 过程，能修改用户指定课程号的课 程学分值，修改值也由用户指定。
带 输 出 参 数 的 存 储 过 程

定义格式同上，同时指明OUTPUT关 键字
CREATE PROCEDURE proc_name @para_name datatype [=default] [output], … AS Sql_statements

执行方法：用output指明输出参数 注：OUTPUT变量在定义存储过程 和使用该变量时都必须进行定义。 【例】创建存储过程P_DeptNum，要求能

删除格式：
DROP PROCEDURE proc_name

在创建时指明重编译
添加WITH
（ RECOMPILE 重 编 译 处 理

RECOMPILE选项
在执行时指明重编译
使用
EXECUTE 时指定 WITH RECOMPILE

通过系统存储过程设定重编译 选项
使用系统存储过程
）
SP_RECOMPILE

从以下几个方面考虑：
1.
存 储 过 程 的 作 用
模块化编程：
创建一个存储过程存放在数据库中后，就可 以被其他程序反复使用。
2.
快速执行：
存储过程在服务器端运行，第一次被执行后， 就驻留在内存中。以后执行就省去了重 新分析、优化、编译的过程。
3.
减少网络通信量
有了存储过程后，在网络上只要一条语句就 能执行一个存储过程。
系 统 存 储 储 过 过 程 程 、 扩 展 存

sp_addlogin——创建登录ID
xp_cmdshell——执行操作系统 命令 xp_enumgroups——列出域中的 组的信息 xp_loginconfig——报告SQL Server的登录安全信息

本 章 总 结
存储过程的作用 创建、执行简单的存储过程 创建、执行带参数（输入、输 出）的存储过程（重点） 重点：存储过程的创建和执行， 特别是带有参数的存储过程
练 习
use 学生选课库 go create procedure ScBySnoCno @StudentSno char(10), @CourseCno as char(10) as select * from SC where SNO = @StudentSno and Cno =@CourseCno
创 建 带 输 入 参 数 的 存 储 过 程

创建格式：
CREATE PROCEDURE proc_name @para_name datatype [=default], … AS Sql_statements
创 建 带 输 入 参 数 的 存 储 过 程
【例】创建一个名为StuBySno的存储过 程，该存储过程根据给定的学号显示 相应学生的信息
use 学生选课库 go create procedure StuBySno @StudentSno char(10) as select * from Student where SNO = @StudentSno
创 建 带 输 入 参 数 的 存 储 过 程
【例】创建一个名为StuBySno的存储过程，该存 储过程根据给定的学号显示相应学生的信息 CREATE procedure StuBySno @StudentSno char(10)=NULL as IF @StudentSno IS NULL BEGIN PRINT '必须提供一个数值作参数！' RETURN 13 END IF NOT EXISTS (select * from Student where SNO = @StudentSno) BEGIN PRINT '没有满足条件的记录！' RETURN -103 END select * from Student where SNO = @StudentSno RETURN 0
创 建 、 执 行 简 单 的 存 储 过 程

创建格式：
CREATE PROCEDURE proc_name AS Sql_statements

执行格式：
EXEC|EXECUTE proc_name
创 建 、 执 行 简 单 的 存 储 过 程
【问题】创建一个名为math_Student 的存储过程，返回student表中数学 系的学生信息 use 学生选课库 go create procedure math_student as select * from Student where Sdept =‘MA'

4.
安全机制
通过隔离和加密的方法提高了数据库的安全 性，通过授权可以让用户只能执行存储 过程而不能直接访问数据库对象。
存 储 过 程 和 视 图 的 比 较
视图
语句 只能是SELECT 语句
存储过程
可以包含程序流 程控制、逻辑判 断以及SELECT 语句
输入、 返回结果 典型应用
不能接受参数， 可以有输入输出 只能返回结果集 参数，也可以有 返回值 多个表格的连接 完成某个特定的 查询 较复杂的任务
根据用户给定的系代码，统计该系的人数， 并将人数通过输出变量返回给用户。
如要查询系代码为’MA’的学生人数，如何 调用存储过程
带 输 出 参 数 的 存 储 过 程

定义：
use 学生选课库 Go create procedure P_DeptNum @DeptId char(15), @DeptNum smallint OUTPUT as Select @DeptNum =COUNT (*) from Student where Sdept =@DeptId

修改格式：
ALTER PROCEDURE proc_name @para_name datatype [=default], … [WITH ENCRYPTION] AS Sql_statements
修 改 存 储 过 程
重 命 名 、 删 除 存 储 过 程

重命名：
sp_rename 原存储过程名,新存储过 程名