第09章 存储过程

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,点击此处结束放映,单击此处编辑母版标题样式,第 9 章,存 储 过 程,通过本章的学习，应该掌握以下内容：,存储过程的作用,;,熟练创建、修改、删除存储过程,;,在实际应用开发时能够灵活运用存储过程以提高开发效率。,第 9 章,存 储 过 程,9.1 存储过程的概念,9.2 建立和执行存储过程,9.3 存储过程中参数的使用,9.4 存储过程的管理与维护,9.5 系统存储过程和扩展存储过程,9.1 存储过程的概念,SQL Server,应用操作中,存储过程和触发器扮演相当重要的角色,不仅能提高应用效率,确保一直性,更能提高系统执行速度。同时，使用触发器来完成业务规则，达到简化程序设计的目的。,9.1 存储过程的概念,存储过程包括系统存储过程和用户存储过程。,系统存储过程我们以前接触过：,Execute,sp_help,表名 ：查看表的结构。,Execute,sp_helpindex,表名,：查看表上的索引信息。,Execute,sp_helptext,视图名：查看视图的定义信息 。,9.1 存储过程的概念,存储过程按返回的数据类型，可分为两类：,一类类似于,SELECT,语句,，用于查询数据，查询到的数据以结果集的形式给出；,另一类存储过程是通过输出参数返回信息,，或不返回信息只执行一个动作。存储过程可以嵌套，即一个存储过程的内部可以调用另一个存储过程。,9.1.1,基本概念,存储过程是一组编译在单个执行计划中的,Transact-SQL,语句，它将一些固定的操作集中起来交给,SQL Server,数据库服务器完成，以实现某个任务。,存储过程就是预先编译和优化并存储于数据库中的过程。,9.1.2,存储过程的优点,（1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。,（2）通过通用编程结构和过程重用实现编程框架。,（3）通过隔离和加密的方法提高了数据库的安全性。,使用,存储过程的好处,（1）允许模块化的程序设计。,（2）更快的执行速度。,（3）有效降低网络流量。,（,4,）交好地安全机制。,9.2 建立和执行存储过程,9.2.1,创建和执行简单存储过程,创建存储过程的基本语法如下：,CREATE PROCEDURE,存储过程名,WITH ENCRYPTION,对存储过程加密,WITH RECOMPILE,对存储过程重新编译,AS,SQL,语句,执行存储过程的基本语法如下：,EXECUTE,存储过程名,如果该存储过程是批处理中第一条语句，则,EXEC,可以省略。,例题：存储过程：在,Marketing,数据库中建立 一个存储过程，返回订单信息表中所有信息。,（无任何参数，没有输出值和返回值）,简单存储过程类似于将一组,SQL,语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。,use marketing,go,if exists (select name from,sysobjects,Where name=procedure1 and type=P),drop procedure procedure1,go,create procedure procedure1,as,select * from,订单信息,go,exec procedure1,go,例,9-1,：使用,TSQL,语句在,Marketing,数据库中创建一个存储过程，该存储过程返回客户的订购信息。,create procedure,simpleorders,as,select a.,姓名,a.,电话,a.,订货日期,b.,货品名称,b.,供应商,from,客户订单视图,a inner join,货品信息视图,b on a.,货品编码,=b.,编码,go,execute,simpleorders,9.2.2,执行字符串,EXECUTE,语句除了可以执行存储过程外，还可以执行存放,SQL,语句的字符串变量，或直接执行,SQL,语句字符串。此时,EXECUTE,语句的语法格式如下。,EXECUTE(,字符串变量|,NSQL,语句字符串+.,n),例,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 ,供应商信息,else null,end,if ,tablenam,is null,print ,没找到对应的表,else,execute(select,* from +,tablenam,),go,9.3 存储过程中参数的使用,9.3.1 带输入参数的存储过程,1建立存储过程,一个存储过程可以带一个或多个输入参数，,输入参数,是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。,声明带输入参数的存储过程的语法格式如下：,CREATE PROCEDURE,存储过程名,参数名 数据类型=默认值 ,.,n,WITH ENCRYPTION,WITH RECOMPILE,AS,SQL,语句,例,9-3,：在查询分析器中，将例,9-2,的批处理变为一个带输入参数的存储过程，它能根据指定的表名关键字显示相应的表信息。,if,exists(select,name from,sysobjects,where name=,disptab, and type=p),drop procedure,disptab,go,create procedure,disptab,selectkey,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,disptab,销售,go,2执行存储过程,在执行存储过程的语句中，有两种方式来传递参数值，分别是,使用参数名传递参数值,和,按参数位置传递参数值,。,使用参数名传递参数值，是通过语句“参数名=参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。,执行使用参数名传递参数值的存储过程的语法格式如下。,EXECUTE,存储过程名,参数名=参数值, ,.,n,例,9-4,：带多个输入参数及默认值的存储过程，在例,9-3,的基础上增加一个列选择参数。即存储数据可以根据指定的列名和表名给出数据。,create procedure disptab2,selectkey,varchar(6)=,客户,columnkey,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,raiserror,(,没有找到对应的表,6,6),else,execute(select,+,columnkey,+ from +,tablenam,),go,例,9-5,：四种执行存储过程的方法。,Execute disptab2 ,columnkey,=,地址,Execute disptab2,default,地址,Execute disptab2 ,selectkey,=,客户,columnkey,=,地址,Execute disptab2 ,客户,地址,Execute,disptab2 ,客户,姓名,地址,可以看到，,按参数位置传递参数值比按参数名传递参数值简洁,比较适合参数值较少的情况,;,而,按参数名传递参数使程序可读性增强,。特别是参数数量较多时，建议使用按参数名称传递参数的方法，这样的程序可读性、可维护性都要好一些。,9.3.2,带输出参数的存储过程,如果我们需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现，为了使用输出参数，需要在,CREATE PROCEDURE,语句中指定,OUTPUT,关键字。,声明带输出参数的存储过程的语法格式如下：,CREATE PROCEDURE,存储过程名,参数名 数据类型 ,VARYING=,默认值,OUTPUT ,.n,WITH ENCRYPTION,WITH RECOMPILE,AS,SQL,语句,例,9-6,创建存储过程,disptab3,，它是在例,9-4,的基础上，提供一个游标输出参数，将查询的结果以结果集的方式返回。,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,like %,供应,% 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 DispTab CURSOR,-,定义存储过程要用的输出参数,-,执行存储过程,EXECUTE DispTab3 ListTab=DispData OUTPUT,FETCH NEXT FROM DispData,WHILE(FETCH_STATUS=0),FETCH NEXT FROM DispData,CLOSE DispData,DEALLOCATE DispData,GO,9.3.3,通过,RETURN,返回参数,用户可以通过,RETUEN,语句返回状态值，,RETURN,语句只能返回整数,，在存储过程中,RETURN,不能返回空值，默认返回值是0。也可以利用它返回整数输出参数值。,例,9-7,在例,8-24,（,P158,）中，用客户的地址信息进行检索，实现了统计,”,客户信息,”,表中指定城市客户的数量。这里建立一个存储过程，它带一个输入参数用来指定城市，带一个输出参数用来接受统计结果，另外该统计结果也可以通过,RETURN,语句返回。,9.4 存储过程的管理与维护,9.4.1 查看存储过程的定义信息,在企业管理器下，可以在“存储过程属性”窗口中，对存储过程进行维护，参见9.2节中启动“存储过程属性”窗口的方法。在这个窗口中，可以查看存储过程的定义信息、修改存储过程的定义，给用户指定使用存储过程的权限。,在查询分析器下，可以通过系统存储过程,sp_helptext,查看存储过程的定义；通过,sp_help,查看存储过程的参数；通过,sp_depends,查看存储过程的相关性。,例,9-8,在查询分析器下，使用系统存储过程，查看例,9-6,创建的存储过程、参数和相关性。,EXECUTE sp_helptext DispTab3,EXECUTE sp_help DispTab3,EXECUTE sp_depends DispTab3,9.4.2 存储过程的重编译,通过存储过程重编译，可以重新优化存储过程的执行计划。,在,SQL Server,中，为用户提供了三种重新编译的方法：,1在创建存储过,程时设定,在创建存储过程中，使用,WITH RECOMPLILE,子句来指示,SQL Server,不该把存储过程保存在缓存中，而是在每次运行时重新编译和优化，并创建新的执行计划。,9.4.2 存储过程的重编译,1在创建存储过程时设定,例,9-9,使用,WITH RECOMPILE,子句，修改例,9-1,的存储过程，使其每次运行时重新编译和优化。,ALTER PROCEDURE simpleorders,WITH COMPILE,AS,select a.,姓名,a.,电话,a.,订货日期,b.,货品名称,b.,供应商,from,客户订单视图,a inner join,货品信息视图,b on a.,货品编码,=b.,编码,go,9.4.2 存储过程的重编译,2在执行存储过程时设定,通过在执行存储过程时设定重新编译，可以让,SQL Server,在执行存储过程时重新编译该存储过程，这一次执行完后，新的执行计划又被保存在缓存中。这样用户可以根据需要进行重新编译。,9.4.2 存储过程的重编译,2,在创建存储过程时设定,例,9-10,将例,9-9,修改后的存储过程，再修改回去，然后以重新编译的方式执行依次该存储过程，实现执行计划的更新。,ALTER PROCEDURE simpleorders,AS,select a.,姓名,a.,电话,a.,订货日期,b.,货品名称,b.,供应商,from,客户订单视图,a inner join,货品信息视图,b on a.,货品编码,=b.,编码,Go,EXECUTE,simpleorders,WITH RECOMPILE,9.4.2 存储过程的重编译,3通过系统存储过程设定,通过系统存储过程,sp_recompile,设定重新编译标记，是存储过程和触发器在下次运行时重新编译。,具体语法格式如下：,EXECUTE sp_recompile,数据库对象,其中数据库对象为当前数据库中的存储过程、表或视图的名称。,如果数据库对象是存储过程或触发器的名称，那么该存储过程或触发器在下次运行时重新编译。如果数据库对象时表或视图，那么所有引用该表或视图的存储过程都将在下次运行时重新编译。,9.4.3,重新命名存储过程,其语法格式如下。,sp_rename,存储过程原名 存储过程新名,9.4.4,修改和删除存储过程,1修改存储过程,存储过程的修改是由,ALTER,语句来完成的，基本语法如下。,ALTER PROCEDURE,存储过程名,WITH ENCRYPTION,WITH RECOMPILE,AS,SQL,语句,2删除存储过程,存储过程的删除是通过,DROP,语句来实现的，在企业管理器也同样可以进行删除。,9.5 系统存储过程和扩展存储过程,9.5.1 系统存储过程,在,SQL Server,中的许多管理工作是通过执行系统存储过程来完成的。,9.5.2,扩展存储过程,扩展存储过程提供从,SQL Server,到外部程序的接口。,扩展存储过程和普通存储过程一样，可以接收用户的输入参数，也可以返回执行结果和执行状态。扩展存储过程能够以类似存储过程的方式，动态装入和执行动态链接库（,DLL）,内的函数，无缝地扩展,SQL Server,的功能。,9.5.2,扩展存储过程,例,9-13,执行下列,xp_cmdshell,语句，返回指定目录的匹配文件列表。,EXECUTE master.xp_cmdshelldir c:*.txt,GO,