SQL数据库应用(第八章)

GO
8.4 .2使用Transact-SQL修改和删除存储过程
3．删除存储过程
使用DROP PROCEDURE语句从当前数据库中删除一个或多个用户定义的 存储过程或存储过程组，语法格式如下： DROP PROC[EDURE] {[所有者.]存储过程(组)名}[,...n]
本章小结
存储过程是一种数据库对象，是存储在服务器上的一组预定义的SQL 语句集合。 创建存储过程并将编译好的版本存储在高速缓存中，可以加快程序的 执行效率。 存储过程可以有输入、输出参数，可以返回结果集以及返回值。 通过本章的学习，应掌握各种存储过程的创建、执行、修改和删除方
（1）重命名存储过程 EXEC sp_renຫໍສະໝຸດ Baidume 存储过程原名,存储过程新名
【例8.9】 将存储过程ProcSumByGoods重命名为proc_单月总销量。 EXEC sp_rename ProcSumByGoods, proc_单月总销量
8.4 .2使用Transact-SQL修改和删除存储过程
8.3 执行存储过程
【例8.2】 创建一个带有输入参数的存储过 程proc_goods，查询指定员工所进商品信息。 创建和执行存储过程的脚本内容如下： USE Sales GO CREATE PROC proc_goods @员工编号 char(6)='1001' AS SELECT 商品编号,商品名称,生产厂商,进货 价,零售价,数量,进货时间 FROM Goods WHERE 进货员工编号=@员工编号 GO --执行存储过程,查询1001号员工所进的商品 的信息 EXEC proc_goods @员工编号=default --或 EXEC proc_goods @员工编号='1001' 【例8.3】 创建一个带有输入和输出参数的存储 过程proc_GNO，查询指定厂商指定名称的商品所 对应的商品编号。 USE Sales GO CREATE PROC proc_GNO @商品名称 varchar(20),@生产厂商 varchar(30), @商品编号 int OUTPUT AS SELECT @商品编号=商品编号 FROM Goods WHERE 商品名称=@商品名称 AND 生产厂商 =@生产厂商 GO --执行存储过程，查询惠普公司打印机商品编号 DECLARE @商品编号 int EXEC proc_GNO '打印机', '惠普公司',@商品编 号 OUTPUT PRINT '该商品编号为：'+CAST(@商品编号 AS char(6))

8.1 存储过程简介
存储过程存放在服务器端数据库中，是经编译过的 能完成特定功能的T-SQL语句的集合，是作为一个单元来 处理的。 在存储过程中可以对任何数据及对象进行修改，包 括新建或删除表、修改数据库设置等。

8.1.1 存储过程的优点
（1）执行速度快，改善系统性能。 （2）减少网络流量。 （3）增强代码的重用性和共享性。 （4）提供了安全机制。
SQL Server使用CREATE PROCEDURE语句用于创建存储 过程，语法格式如下：
CREATE PROC[EDURE] [所有者.]存储过程名[;整数] [{@参数 数据类型}[VARYING][= 默认值][OUTPUT][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION|EXECUTE_AS_Clause}] [FOR REPLICATION] AS
8.4 修改和删除存储过程
◦ 使用图形化工具查看和修改存储过程 ◦ 使用Transact-SQL查看和修改存储过程
8.4 .1使用图形化工具修改和删除存储过程
1．查看存储过程定义文本和修改存储过程 2．查看存储过程的依赖关系 3．重命名存储过程 4．删除存储过程
8.4 .2使用Transact-SQL修改和删除存储过程
（2）使用ALTER PROCEDURE语句修改存储过程，语法格 式如下：
ALTER PROC[EDURE] [所有者.]存储过程名[;整数] [{@参数 数据类型}[VARYING][= 默认值][OUTPUT][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS SQL语句[,...n]
法。
{SQL语句[,...n]|EXTERNAL NAME assembly_name.class_name.method_name}
8.3 执行存储过程
使用EXECUTE语句执行存储过程
1．通过存储过程自身执行存储过程
语法格式如下： [EXEC[UTE]] {[@整型变量=]存储过程名[;分组标识号]|@存储过程变量} [[@参数=]{参量值|@变量 [OUTPUT]|[DEFAULT]}][,...n] [WITH RECOMPLILE]
1．查看存储过程
（1）查看存储过程的定义文本。 EXEC sp_helptext 存储过程名 （2）查看存储过程的依赖关系。 EXEC sp_depends 存储过程名 （3）查看存储过程的参数。 EXEC sp_help 存储过程名
8.4 .2使用Transact-SQL修改和删除存储过程
2．修改存储过程
8.3 执行存储过程
【例8.4】 创建带有参数和返回值的存储过程。在 Sales数据库中创建存储过程ProcSum ByGoods， 查询指定厂商指定名称的商品在某年某月的总销售 量。 USE Sales GO CREATE PROC ProcSumByGoods @goodname varchar(20),@corp varchar(30),@year int, @month int, @sum int OUTPUT AS --声明和初始化一个局部变量，用于保存系统函数 @@ERROR的返回值 DECLARE @ErrorSave int SET @ErrorSave=0 --统计指定厂商指定名称的商品在指定年份月份总 的销售量 SELECT @sum=SUM(Sell.数量) FROM Sell JOIN Goods ON Sell.商品编号 =Goods.商品编号 WHERE 商品名称=@goodname AND 生产厂商 =@corp AND YEAR(售出时间)=@year AND MONTH(售出时间)=@month IF (@@ERROR<>0) SET @ErrorSave=@@ERROR RETURN @ErrorSave GO GO --执行存储过程，查询HP公司2004年10月的打 印机销售总量 DECLARE @ret int,@sum int EXEC @ret=ProcSumByGoods ‘打印机’,‘惠普 公司',2004,10,@sum OUTPUT PRINT '该存储过程执行结果如下：' PRINT '返回值='+CAST(@ret AS char(1)) PRINT '总销售量='+CAST(@sum AS char(4))
SQL数据库应用
第八章 存储过程
学时：2 目标

掌握创建和使用存储过程

重点
创建存储过程
难点
命令的使用
第八章 存储过程
8.1 8.2 8.3 8.4 存储过程简介 创建存储过程 执行存储过程 修改和删除存储过程
存储过程
存储过程是 T-SQL 语句的集合，它提供了一种高效和安全的访 问数据库的方法，经常被用来访问数据和管理被修改的数据。 SQL Server 2005不仅提供了用户自定义存储过程的功能，而且 也提供了许多可作为工具使用的系统存储过程。 本章重点介绍存储过程的概念、存储过程的创建和管理，并介 绍存储过程的一些应用技巧。
【例8.10】 修改例8.2中的存储过程，要求加密存储过程的定义文本，不指定参数 的默认值，只查询指定员工所进商品的名称、生产厂商、进货价和数量信息。
USE Sales GO alter PROC proc_goods @员工编号char(6) WITH ENCRYPTION AS SELECT 商品名称,生产厂商,进货价,数量 FROM Goods WHERE 进货员工编号=@员工编号
8.2 创建存储过程
◦ 使用图形化工具创建存储过程
（参数的代码“@p1 int = 0，@p2 int = 0”删除，将代码“SELECT @p1，@p2”更改为“SELECT 商品编号，商品名称，进货价 ，零售价 FROM Goods” ）
◦ 使用Transact-SQL创建存储过程
8.2.2 使用Transact-SQL创建存储过程
【例8.7】 本例是用EXECUTE语句执行字符串的示例。
USE Sales GO DECLARE @sqlstr VARCHAR(40) SET @sqlstr='SELECT * FROM Employees ORDER BY 姓名' EXEC(@sqlstr)
8.3 执行存储过程
使用图形化工具执行存储过程
8.3 执行存储过程
使用EXECUTE语句执行存储过程
2．执行字符串
EXECUTE语句的主要用途是执行存储过程。此外，我们还可以将T-SQL语句放在字符 串变量中，然后使用EXECUTE语句来执行该字符串，语法格式如下：
EXEC[UTE] ({@字符串变量}|[N] 'tsql字符串') [+...n])
【例8.1】 本例创建一个简单的无参数的存储过程：在Sales数据库中，创建存储过程 proc_Employees，查询采购部的员工信息。 创建和执行存储过程的脚本内容如下： USE Sales GO CREATE PROC proc_Employees AS SELECT * FROM Employees WHERE 部门='采购部' GO --执行存储过程 EXEC proc_Employees

8.1.2 存储过程的类别
1、用户自定义存储过程
（1）Transact-SQL存储过程。 （2）CLR存储过程。 2、系统存储过程 存储在master数据库中，可以在其他数据库中任意 进行调用，由前缀“sp_”标识。
3、扩展存储过程 扩展存储过程以在SQL Server环境外执行的动态链接 库（DLL）来实现。由前缀“xp_”标识。