《数据库应用技术——SQL Server 2008篇 第3版 教学课件 ppt 作者 延霞 徐守祥 chap8》由会员分享，可在线阅读，更多相关《数据库应用技术——SQL Server 2008篇 第3版 教学课件 ppt 作者 延霞 徐守祥 chap8（30页珍藏版）》请在金锄头文库上搜索。
1、存储过程,数据库应用技术SQL Server2008篇,主讲人：,本章主要内容:,1. 存储过程的作用,2.创建、修改、删除存储过程,3.灵活运用存储过程,什么是存储过程,存储过程是一组编译在单个执行计划中的Transact-SQL语句，它将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。 利用服务器的计算能力，减少网络上的传输量，提高了客户端的工作效率,存储过程的优点,（1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。 （2）通过通用编程结构和过程重用实现编程框架 （3）通过隔离和加密的方法提高了数据库的安全性。,建立和执行存储过程,创建存储过程，
2、可以用SQL Server Management Studio，也可以用SQL语句。 示例：在marketing数据库中创建一个存储过程。该存储过程返回客户的订购信息 建立信息的存储过程 CREATE PROCEDURE simpleOrders AS SELECT A.姓名, A.电话, A.订货日期, B.货品名称, B.供应商 FROM 客户订单视图 A INNER JOIN 货品信息视图 B ON A.货品编码=B.编码 GO 调用存储过程 EXECUTE simpleOrders,执行字符串,EXECUTE语句可以执行存放SQL语句的字符串变量，或直接执行SQL语句字符串 示例：使用
3、SQL语句，在查询设计器中建立一个批处理，它能根据指定的表名关键字显示相应的表信息 DECLARE TableNam VARCHAR(20), SelectKey VARCHAR(6) SET SelectKey = 供应 指定查询表名的关键字 SELECT TableNam = CASE WHEN SelectKey LIKE %客户% THEN 客户信息 WHEN SelectKey LIKE %销售% THEN 销售人员 WHEN SelectKey LIKE %供应% THEN 供应商信息 ELSE NULL END,接上页,IF TableNam IS NULL 如果是空则显示消息
4、PRINT 没有找到对应的表！ ELSE EXECUTE (SELECT * FROM +TableNam) 执行字符串查询语 GO,带输入参数的存储过程,一个存储过程可以带一个或多个输入参数 输入参数是指由调用程序向存储过程传递的参数 示例：定义带输入参数的存储过程，能根据指定的表名关键字显示相应的表信息 如果存在则删除 IF EXISTS(SELECT name FROM sysobjects WHERE name=DispTab AND type=P) DROP PROCEDURE DispTab GO,接上页, 建立存储过程 CREATE PROCEDURE DispTab Selec
5、tKey VARCHAR(6) 定义输入参数 AS DECLARE TableNam VARCHAR(20) SELECT TableNam = CASE WHEN SelectKey LIKE %客户% THEN 客户信息 WHEN SelectKey LIKE %销售% THEN 销售人员 WHEN SelectKey LIKE %供应% THEN 供应商信息 ELSE NULL END,接上页,IF TableNam IS NULL 如果是空则显示消息 PRINT 没有找到对应的表！ ELSE EXECUTE (SELECT * FROM +TableNam) GO EXECUTE Di
6、spTab 供应 指定查询表名的关键字,4种执行存储过程的方法, 参数SelectKey采用默认值 EXECUTE DispTab2 ColumnKey=地址 参数SelectKey采用默认值 EXECUTE DispTab2 DEFAULT, 地址 EXECUTE DispTab2 SelectKey=客户, ColumnKey=地址 EXECUTE DispTab2 客户, 地址 GO,带输出参数的存储过程,需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现 示例：创建存储过程DispTab3，它是在例8-4的基础上，提供一个游标输出参数，将查询的结果以结果
7、集的方式返回 建立存储过程 CREATE PROCEDURE DispTab3 SelectKey VARCHAR(6)=客户, 定义输入参数,及默认值 ColumnKey VARCHAR(6)=*, 定义输入参数,及默认值 ListTab CURSOR VARYING OUTPUT 定义游标作为输出参数 AS DECLARE TableNam VARCHAR(20),接上页,SELECT TableNam = CASE WHEN SelectKey LIKE %客户% THEN 客户信息 WHEN SelectKey LIKE %销售% THEN 销售人员 WHEN SelectKey LI
8、KE %供应% THEN 供应商信息 ELSE NULL END IF TableNam IS NULL 如果是空则显示消息 RAISERROR(没有找到对应的表！,6,6) ELSE,接上页,BEGIN EXECUTE ( 执行字符串查询语, 建立临时游标 DECLARE TempCs CURSOR FOR SELECT +ColumnKey+ FROM +TableNam) SET ListTab=TempCs 设置临时游标给输出参数 OPEN ListTab 打开游标 DEALLOCATE TempCs 释放临时游标 END GO,接上页, 建立存储过程结束，执行存储过程 DECLARE
9、 DispData CURSOR 定义输出参数 执行存储过程。输入参数使用默认值，指定输出参数 EXECUTE DispTab3 ListTab=DispData OUTPUT FETCH NEXT FROM DispData 循环输出游标内容 WHILE (FETCH_STATUS=0) FETCH NEXT FROM DispData CLOSE DispData DEALLOCATE DispData GO,通过RETURN返回参数,用户可以通过RETUEN语句返回状态值 RETURN语句只能返回整数 在存储过程中RETURN不能返回空值，默认返回值是0 也可以利用它返回整数输出参数值,
10、上机实操,示例：写一个存储过程，用客户的地址信息进行检索，实现了统计“客户信息”表中指定城市客户的数量。存储过程带一个输入参数用来指定城市，带一个输出参数用来接收统计结果，另外该统计结果也可以通过RETURN语句返回。 CREATE PROCEDURE Cst_City City AS VARCHAR(20), CountOUT INT OUTPUT AS DECLARE customers CURSOR FOR SELECT 地址 FROM 客户信息,接上页,OPEN customers 打开游标 DECLARE Address VARCHAR(50) DECLARE Loop BIT DE
11、CLARE Count INT SET Count=0 IF ERROR = 0 BEGIN WHILE 1=1 控制循环 BEGIN FETCH NEXT FROM customers INTO Address IF FETCH_STATUS = 0 判断读入是否正确 BEGIN IF CHARINDEX(City, Address)0 SET Count=Count+1 END,接上页,ELSE BREAK END PRINT 表中+City+的客户数量为：+CONVERT(VARCHAR(3), Count) END CLOSE customers 关闭游标 DEALLOCATE cus
12、tomers 释放游标 SET CountOUT=Count 建立输出参数值 RETURN Count 返回参数 GO,接上页, 执行存储过程 DECLARE COUNT1 INT, COUNT2 INT EXECUTE COUNT2=Cst_City 深圳,COUNT1 OUTPUT PRINT COUNT2 PRINT COUNT1 GO,存储过程的管理与维护,在SQL Server Management Studio中实现 使用存储过程实现 EXECUTE sp_helptext DispTab3 EXECUTE sp_help DispTab3 EXECUTE sp_depends D
13、ispTab3 GO,存储过程的重编译,存储过程所采用的执行计划，只在编译时优化生成，以后便驻留在过程高速缓存中 当用户对数据库新增了索引或其他影响数据库逻辑结构的更改后，已编译的存储过程执行计划可能会失去效率。 通过对存储过程进行重新编译，可以重新优化存储过程的执行计划,SQL Server 2008提供3种重编译方法,在创建存储过程时设定 修改信息的存储过程simpleOrders ALTER PROCEDURE simpleOrders WITH RECOMPILE AS SELECT A.姓名, A.电话, A.订货日期, B.货品名称, B.供应商 FROM 客户订单视图 A INN
14、ER JOIN 货品视图 B ON A.货品编码=B.编码 GO,SQL Server 2008提供3种重编译方法,在执行存储过程时设定 修改信息的存储过程 ALTER PROCEDURE simpleOrders AS SELECT A.姓名, A.电话, A.订货日期, B.货品名称, B.供应商 FROM 客户订单视图 A INNER JOIN 货品视图 B ON A.货品编码=B.编码 GO 调用存储过程,并重新编译 EXECUTE simpleOrders WITH RECOMPILE 通过系统存储过程设定 EXEC sp_recompile 客户信息,重新命名存储过程,在对象浏览下
15、，可以通过在弹出的快捷菜单中选择“重命名”，来给存储过程重新命名。 使用系统存储过程sp_rename来更改存储过程的名称 sp_rename 存储过程原名 存储过程新名,修改和删除存储过程,示例：这里使用Transact-SQL语句修改存储过程simpleOrders，进行加密 ALTER PROCEDURE simpleOrders WITH ENCRYPTION 进行加密 AS SELECT A.姓名, A.电话, A.订货日期, B.货品名称, B.供应商 FROM 客户订单视图 A INNER JOIN 货品视图 B ON A.货品编码=B.编码 GO 尝试查看存储过程的定义信息 EXECUTE sp_helptext simpleOrders GO,系统存储过程,系统存储过程创建和保存在master数据库中，都是以sp_作为前缀的，可以在任何数据库中使用系统存储过程 （1）通过sp_OACreate创建一个COM实例。 （2）通过sp_OAMethod调用COM实例中的方法。 （3）通过sp_OADestory取消COM实例。 （4）如果当中发生错误，可以使用sp_OAGetErrorInfo获得失败原因。,扩
