第09章 存储过程

1、 SQL Server应用操作中应用操作中,存储过程和触存储过程和触发器扮演相当重要的角色发器扮演相当重要的角色,不仅能提高应用不仅能提高应用效率效率,确保一直性确保一直性,更能提高系统执行速度。更能提高系统执行速度。同时，使用触发器来完成业务规则，达到同时，使用触发器来完成业务规则，达到简化程序设计的目的。简化程序设计的目的。 存储过程包括系统存储过程和用户存储过程。存储过程包括系统存储过程和用户存储过程。系统存储过程我们以前接触过：系统存储过程我们以前接触过：Execute sp_help 表名表名 ：查看表的结构。：查看表的结构。Execute sp_helpindex 表名表名 ：查看

2、表上的索引信：查看表上的索引信息。息。Execute sp_helptext 视图名：查看视图的定义信视图名：查看视图的定义信息息 。9.1.1 基本概念基本概念9.1.2 存储过程的优点存储过程的优点使用使用存储过程的好处存储过程的好处9.2.1 创建和执行简单存储过程创建和执行简单存储过程use marketing goif exists (select name from sysobjects Where name=procedure1 and type=P)drop procedure procedure1gocreate procedure procedure1as select *

3、 from 订单信息goexec procedure1go例例9-1：使用：使用TSQL语句在语句在Marketing数据库中创建一个存数据库中创建一个存储过程，该存储过程返回客户的订购信息。储过程，该存储过程返回客户的订购信息。create procedure simpleordersasselect a.姓名姓名,a.电话电话,a.订货日期订货日期,b.货品名称货品名称,b.供应商供应商from 客户订单视图客户订单视图 a inner join 货品信息视图货品信息视图 b on a.货品编货品编码码=b.编码编码goexecute simpleorders9.2.2 执行字符串执行字符

4、串例例9-2：使用：使用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 供应商信息供

5、应商信息 else null endif tablenam is null print 没找到对应的表没找到对应的表else execute(select * from +tablenam)go9.3.1 带输入参数的存储过程带输入参数的存储过程if exists(select name from sysobjects where name=disptab and type=p)drop procedure disptabgocreate procedure disptabselectkey varchar(6) -参数参数asdeclare tablenam varchar(20)selec

6、t tablenam= case when selectkey like%客户客户% then 客户信息客户信息 when selectkey like%销售销售% then 销售人员销售人员 when selectkey like%供应供应% then 供应商信息供应商信息 else null endif tablenam is nullprint 没有找到对应的表没有找到对应的表elseexecute(select * from +tablenam)goexecute disptab 销售销售go例例9-4：带多个输入参数及默认值的存储过程，在：带多个输入参数及默认值的存储过程，在例例9-

7、3的基础上增加一个列选择参数。即存储数据的基础上增加一个列选择参数。即存储数据可以根据指定的列名和表名给出数据。可以根据指定的列名和表名给出数据。create procedure disptab2selectkey varchar(6)=客户客户,columnkey varchar(6)=*asdeclare tablenam varchar(20)select tablenam= case when selectkey like %客户客户% then 客户信息客户信息 when selectkey like %销售销售% then 销售人员销售人员 when selectkey like

8、%供应供应% then 供应商信息供应商信息 else null endif tablenam is null raiserror(没有找到对应的表没有找到对应的表,6,6)else execute(select +columnkey+ from +tablenam)go例例9-5：四种执行存储过程的方法。：四种执行存储过程的方法。Execute disptab2 columnkey=地址地址Execute disptab2 default,地址地址Execute disptab2 selectkey=客户客户,columnkey=地地址址Execute disptab2 客户客户,地址地址E

9、xecute disptab2 客户客户,姓名姓名,地址地址9.3.2 带输出参数的存储过程带输出参数的存储过程create procedure disptab3selectkey varchar(6)=客户客户,columnkey varchar(6)=*listTab CURSOR VARYING OUTPUTasdeclare tablenam varchar(20)select tablenam= case when selectkey like %客户客户% then 客户信息客户信息 when selectkey like %销售销售% then 销售人员销售人员 when sel

10、ectkey like %供应供应% then 供应商信息供应商信息 else null endif tablenam is null raiserror(没有找到对应的表没有找到对应的表,6,6)else begin execute( -执行字符串查询语句，建立临时游标执行字符串查询语句，建立临时游标declare tempcs cursor for select +columnkey+ from +tablenam) set listTab=tempcs -设置临时游标给输出参数设置临时游标给输出参数 open listtab -打开游标打开游标 deallocate tempcs -释放

11、临时游标释放临时游标endgo执行存储过程：执行存储过程：DECLARE DispTab CURSOR -定义存储过程要用的输出参数-执行存储过程执行存储过程EXECUTE DispTab3 ListTab=DispData OUTPUTFETCH NEXT FROM DispDataWHILE(FETCH_STATUS=0)FETCH NEXT FROM DispDataCLOSE DispDataDEALLOCATE DispDataGO9.3.3 通过通过RETURN返回参数返回参数例例9-7 9-7 在例在例8-248-24（P158P158）中，用客户的地址信息进行检索，实现了）中，

12、用客户的地址信息进行检索，实现了统计统计”客户信息客户信息”表中指定城市客户的数量。这里建立一个存储过程，表中指定城市客户的数量。这里建立一个存储过程，它带一个输入参数用来指定城市，带一个输出参数用来接受统计结果，它带一个输入参数用来指定城市，带一个输出参数用来接受统计结果，另外该统计结果也可以通过另外该统计结果也可以通过RETURNRETURN语句返回。语句返回。 通过存储过程重编译，可以重新优化存储过程的执行计划。 在SQL Server中，为用户提供了三种重新编译的方法：1在创建存储过在创建存储过 在创建存储过程中，使用WITH RECOMPLILE子句来指示SQL Server不该把存

13、储过程保存在缓存中，而是在每次运行时重新编译和优化，并创建新的执行计划。例例9-9 使用使用WITH RECOMPILE子句，修改例子句，修改例9-1的存储过程，使其每次运行时重新编译和优化。的存储过程，使其每次运行时重新编译和优化。ALTER PROCEDURE simpleordersWITH COMPILE AS select a.姓名姓名,a.电话电话,a.订货日期订货日期,b.货品名称货品名称,b.供应商供应商from 客户订单视图客户订单视图 a inner join 货品信息视图货品信息视图 b on a.货货品编码品编码=b.编码编码go例例9-10 将例将例9-9修改后的存储过程，再修改回去，然后以重修改后的存储过程，再修改回去，然后以重新编译的方式执行依次该存储过程，实现执行计划的更新。新编译的方式执行依次该存储过程，实现执行计划的更新。ALTER PROCEDURE simpleordersAS select a.姓名姓名,a.电话电话,a.订货日期订货日期,b.货品名称货品名称