《数据库原理与应用》09.存储过程的创建和使用.ppt

孙 发 勤 扬州大学新闻与传媒学院Y,数据库原理与应用第九讲 存储过程的创建和使用,1、创建存储过程2、执行存储过程3、查看和修改存储过程4、重命名和删除存储过程,本讲主要内容,存储过程的概念,SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。类似于DOS一下的批处理。存储过程是SQL语句和可选控制流程语句的预编译集合。是一种封装重复任务操作的方法，以一个名称存储，作为一个单元处理。存储过程属于服务器方软件，可立即访问数据库在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,存储过程存储在数据库内，可由应用程序通过一个调用来执行，而且充许用户声明变量。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。注意:存储过程与函数不同，因为存储过程并不返回取代其名称的值，也不能直接在表达式中使用。,在SQL Server中存储过程分为两类:系统提供的存储过程和用户存储过程。系统过程主要存储在Master数据库中，并以SP_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动:如了解数据库对象、数据库信息都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名，而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能(如查询用户所需数据信息)的存储过程。,存储过程时存放在SQL Server中的特别快的数据库对象，当首次运行存储过程时，它按以下方式进行:1、该过程被划分成部件片断。2、检查引用数据库中其它对蒙(表、视图等)的部件，确保引用的对象是存在的，这也被称为分解。3、一旦分解完成，该过程的名字将存放倒sysobjects表中，而创建存储过程的代码存放在syscomments表中4、然后编译，并且，编译过程中将创建如何运行查询的蓝本。该蓝本通称称为常规计划或查询树，查询树存放在sysProcetlures表中。5、存储过程首次运行时，读出查询计划并完全编译成过程计划，然后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询树的时间。,当利用SQL Server创建一个应用程序时，T-SQL是一种主要的编程语言。若运用T一SQL来进行编程有两种方法:其一是在本地存储T-SQL程序并创建应用程序，向SQL-Server发送命令米对结果进行T-SQL编写的程序作为存储过程其二是可以把部分用在SQL Server中，并创建应用程序来调用存储过程，对数据结果进行处理。存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定;返回状态值给调用者，指明调用是成功或是失败:包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。我们通常更偏爱于使用第二种方法，即在SQL Server中使用存储过程，而不是在客户计算机上调用T一QL编写的一段程序原因在于存储过程具有以下优点:,存储过程的优点,可用存储过程封装事务规则。一旦封装完成，这些规则就可用于多个应用，从而有一个一致的数据接口，因此，只需改变过程的功能，只需在一个地方对其进行修改，而不必对每个应用都进行修改。,存储过程允许标准组件式编程:存储过程在被创建以后，可以在程序中被多次调用而不必重新编写该存储过程的SQL语句;而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响，因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植性。,存储过程能够实现较快的执行速度:如果某一操作包含大量的TSQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析优化，并给出最终被存在系统表中的执行计划;而批处理的T-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。,存储过程能够减少网络流量:对于同一个针对数据数据库对象的操作(如查询修改)，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句;否则将是多条SQL语句，从而大大增加了网络流量，降低网络负载。,存储过程可被作为一种安全机制来充分利用:系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。,创建存储过程的规则,几乎任何可以写成批处理的T一SQL代码都可用于创建存储过程，但是在设计存储过程时，需要遵循下列规则:名字必须符合SQL Server命名规则。引用的对象必须在创建存储过程前就存在不能在单个存储过程中创建后去掉或再创建同名的对象。存储过程最后能有255各参数。,创建存储过程的规则,再自己的存储过程中可以引用临时表，局部临时表再过程结束时将会消失。再存储过程中不能有如下的SQL创建语句:Create Default、Create Procedure、Create Rule、Create Trigger、Create View.可在过程中嵌套过程。创建存储过程的文本不能超过64K字节，以为SQL存放再syscomments表中。若在存储过程中使用了Select*，而底层表中加入了新的列，新的列再过程运行时无法显示。,1 创建存储过程,在SQL Server中，可以使用三种方法创建存储过程：使用创建存储过程向导创建存储过程。利用SQL Server 企业管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。,1.使用创建存储过程向导创建存储过程,在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”根据提示可完成创建存储过程。,2.使用SQL Server 企业管理器创建存储过程,（）在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项（）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮。,3.使用Transact-SQL语句创建存储过程,创建存储过程前，应该考虑下列几个事项：不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为128M。,CREATE PROCEDURE的语法形式：,CREATEPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement.n,CREATE PROCEDURE的语法参数的意义：,procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。,RECOMPILE：表明 SQL Server 不会保存该存储过程的执行计划。ENCRYPTION：表示 SQL Server 加密了 syscomments 表，该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。FOR REPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。,2 执行存储过程,直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：EXECUTE return_status=procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n WITH RECOMPILE,如果执行存储过程的语句是批中的第一个语句，可以省略execute关键字。return status为整型局部变量，用于保存存储过程的返回值;procedure_name指定执行的存储过程的名称;number用来指定该存储过程与其它同名存储过程同组时的标识号。parameter:在创建过程时定义的过程参数。调用者向存储过程所传递的参数值由value参数或variable变量提供，或者使用default关键字指定使用该参数的默认值。output参数说明指定参数为返回参数。,注意:如果按paramete=value|variable的形式为存储过程提供参数，可以不考虑创建存储过程时的参数顺序，但是必须以这种方式提供该存储过程的全部参数。如果不以这种方式提供参数，则必须按照创建存储过程时参数的顺序提供参数。With Recompile指定在实行存储过程时重新编译执行计划。,例：,Create procedure GetAvgname varchar(10),avg int outputAs DeclareErrorSave intSetErrorSave=0Select avgp=AVG(项目)From project as p INNER JOIN pmanager as pmon p.负责人ID=Pm.负责人IDWhere pm.姓名=nameif(Error 0)SetErrorSave=ErrorReturnErrorSavego,例：,使用 EXECUTE 命令传递单个参数，它执行 showind 存储过程，以 titles 为参数值。showind 存储过程需要参数(tabname)，它是一个表的名称。其程序清单如下：EXEC showind titles当然，在执行过程中变量可以显式命名：EXEC showind tabname=titles如果这是 isql 脚本或批处理中第一个语句，则 EXEC 语句可以省略：showind titles或者showind tabname=titles,3 查看和修改存储过程,查看存储过程 存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,（1）使用企业管理器查看用户创建的存储过程,在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。,（2）使用系统存储过程来查看用户创建的存储过程,可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型sp_help objname=name参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码 sp_helptext objname=name参数name为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表,2.修改存储过程,存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTERPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION ASsql_statement.n,4 重命名和删除存储过程,1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_rename 原存储过程名称，新存储过程名称另外，通过企业管理器也可以修改存储过程的名称。,2.删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：drop procedure procedure,n当然，利用企业管理器也可以很方便地删除存储过程。,在存储过程中使用参数,例:下面的存储过程有5个传入参数，求其平均值，而后将平均值赋给output。Create Procedure scoresscore1 smallint,score2 smallint,score3 smallint,score4 smallint,score5 smallint,myAvg smallint OutputAs selectmyAvg=(scarel+scare2+scare3+scare4+scare5)/5,要调用myAvg的值，一首先要定义一个变量，然后才能运行该程序。Declare AvgScore smallintExec scores 113,9,8,8,113,AvgScnre OutputSelect The Average Score is:，AvgScnrego,在将值传递给存储过程时，可以按位置顺序传入(称为按位置传入)，也可按引用转参数，即用参数名称值的方式传入参数。当按引用传入参数时，可按任何次序传入参数。例:declare Avgscore smallintExec scoresscore1=10,score3=9,score2=8,score4=8,score5=10,myAvg=AvgScore OutputSelect The Average score is,AvgScoreGo注意:如果开始时己经按引用传入，则整个过程调用必须按引用传入，不能在存储过程调用中在按位置传入和按参数传入二者之间掉换。,也可用return关键字来将信息返回给调用过程。这将会直接将一个变量返回给调用过程，而无需要求有存储过程定义及调用过程都要有的output声明。,例:create proc MyReturnt1 smallint,t2 smallint,retval smallintAs selectretval=t1+t2Return retval创建该过程后，可输入以下内容对其调用:DeclaremyReturnValue smallintExec myReturnValue=myReturn 9,9,0SelectThe return value is，myReturnValue,With Recompile,可以在Create Procedure语句或Exec procedure语句后增加With Recompile语句，其位置将影响存储过程的处理和运行方式。用With Recompile创建过程:在Create Procedure中使用With Recompile后，执行划将不被存入Catch.每次运行时都要重新编译整个过程，这气标准查询的处理方式很相似，这种方式在存储过程带有使常规执行效率较低的参数时是很有帮助的，通过每次重新编译，过程可针对新参数进行优化执行。,