《数据库存储过程》PPT课件VIP

AnIntroductiontoDatabaseSystem,8.2.1存储过程概述,在SQLServer2000中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名来调用它们，这些过程就叫做存储过程。,AnIntroductiontoDatabaseSystem,8.2.1存储过程概述,应该提倡多使用存储过程，原因有以下几点。（1）实现了模块化编程。（2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。（3）存储过程可以接受输入参数并可以返回输出值。（4）存储过程具有对数据库立即访问的功能。（5）使用存储过程可以加快程序的运行速度。（6）使用存储过程可以减少网络流量。（7）使用存储过程可以提高数据库的安全性。,AnIntroductiontoDatabaseSystem,8.2.1存储过程概述,在SQLServer2000中，存储过程分为系统测试、用户存储过程、临时存储过程、扩展存储过程及远程存储过程。1.系统存储过程是由系统自动创建的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。,AnIntroductiontoDatabaseSystem,8.2.1存储过程概述,2.用户存储过程是由用户根据各自的应用需求进行创建，完成某一个特定功能的存储过程。名称前一般不加sp_。3.临时存储过程属于用户存储过程。如果用户存储过程名前有#，则为临时存储过程，只能在一个用户会话中使用。如在名字前有#，则表示为全局存储过程，可以再所有的用户会话中使用。,AnIntroductiontoDatabaseSystem,8.2.1存储过程概述,4.扩展存储过程是在SQLServer环境之外执行的动态链接库(DLL)，前缀为xp_。它们可以被加载到SQLServer系统中，并且按照存储过程的方式执行。5.远程存储过程是从远程服务器上调用的存储过程，或是从连接到另一个服务器上的客户机上调用的存储过程，是非本地服务器上的存储过程。,AnIntroductiontoDatabaseSystem,注意事项,不能将创建存储过程的语句与其他SQL语句组合到单个批处理中；默认权限属于数据库所有者，并可以将权限授予其他用户；存储过程是数据库对象，名称必须遵守标示符规则；只能在当前数据库中创建存储过程；一个存储过程的最大尺寸为128M。,AnIntroductiontoDatabaseSystem,8.2.2创建存储过程,在SQLServer2000中，可以使用三种方法创建存储过程。使用创建存储过程向导创建存储过程。使用SQLServer2000企业管理器创建存储过程。使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。,AnIntroductiontoDatabaseSystem,1使用创建存储过程向导创建存储过程,（1）在企业管理器中，选中某个SQLServer服务器，选择要创建存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项。（2）单击“确定”按钮。,AnIntroductiontoDatabaseSystem,2使用SQLServer2000企业管理器创建存储过程,使用SQLServer2000企业管理器创建存储过程的步骤如下：在SQLServer2000企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建”“存储过程”命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程”命令。,AnIntroductiontoDatabaseSystem,3使用Transact-SQL语句创建存储过程,可以使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。,AnIntroductiontoDatabaseSystem,3使用Transact-SQL语句创建存储过程,CREATEPROCEDURE的语法形式如下：CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,AnIntroductiontoDatabaseSystem,其中各参数的含义如下：procedure_name：用于指定所要创建存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。,3使用Transact-SQL语句创建存储过程,AnIntroductiontoDatabaseSystem,parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。仅适用于游标参数。,3使用Transact-SQL语句创建存储过程,AnIntroductiontoDatabaseSystem,default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。RECOMPILE：表明SQLServer2000不会保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。,3使用Transact-SQL语句创建存储过程,AnIntroductiontoDatabaseSystem,ENCRYPTION：表示对存储过程文本进行加密。FORREPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITHRECOMPILE选项一起使用。,3使用Transact-SQL语句创建存储过程,AnIntroductiontoDatabaseSystem,AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。,3使用Transact-SQL语句创建存储过程,AnIntroductiontoDatabaseSystem,例9-1创建一个GetInfo，用于获取所有学生信息。Ifexists(SELECTnameFROMsysobjectsWHEREname=GetInfoANDtype=p)DROPPROCEDUREGetInfoGOCREATEPROCEDUREGetInfoASSELECT*FROM学生表GO,3使用Transact-SQL语句创建存储过程,AnIntroductiontoDatabaseSystem,3使用Transact-SQL语句创建存储过程,例9-2创建一个StuInfo，用于获取指定学生的信息。IFEXISTSGOCREATERPOCEDUREStuInfoStuIdvarchar(8)ASSELECT*FROM学生表WHERE学号=StuIdGO,AnIntroductiontoDatabaseSystem,3使用Transact-SQL语句创建存储过程,例9-4创建GetScore，获取所有课程的平均成绩、最高成绩、最低成绩。并返回结果。IFEXISTSGOCREATEPROCEDUREGetScorekcIDvarchar(6),AVGScoreintOUTPUT,MAXScoreintOUTPUT,MINScoreintOUTPUTASSELECTAVGScore=AVG(Grade),MAXScore=MAX(Grade)，MINScore=MIN(Grade)FROMSCWHERECno=kcID,AnIntroductiontoDatabaseSystem,8.2.3管理存储过程,1查看存储过程存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。,AnIntroductiontoDatabaseSystem,1查看存储过程,在企业管理器中查看用户创建的存储过程的方法如下。（1）在企业管理器中，打开指定的服务器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的所有存储过程。,AnIntroductiontoDatabaseSystem,1查看存储过程,（2）右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。,AnIntroductiontoDatabaseSystem,1查看存储过程,也可以使用系统存储过程来查看用户创建的存储过程。可供使用的系统存储过程及其语法形式如下：（1）sp_help：用于显示存储过程的参数及其数据类型sp_helpobjname=name参数name为要查看的存储过程的名称。,AnIntroductiontoDatabaseSystem,1查看存储过程,（2）sp_helptext：用于显示存储过程的源代码sp_helptextobjname=name参数name为要查看的存储过程的名称。,AnIntroductiontoDatabaseSystem,1查看存储过程,（3）sp_depends：用于显示和存储过程相关的数据库对象sp_dependsobjname=object参数object为要查看依赖关系的存储过程的名称。,AnIntroductiontoDatabaseSystem,1查看存储过程,（4）sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_proceduressp_name=name,sp_owner=owner,sp_qualifier=qualifier,AnIntroductiontoDatabaseSystem,1查看存储过程,其中，sp_name=name用于指定返回目录信息的过程名；sp_owner=owner用于指定过程所有者的名称；sp_qualifier=qualifier用于指定过程限定符的名称。,AnIntroductiontoDatabaseSystem,2修改存储过程定义,在企业管理器中，单击目录树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会出现存储过程属性对话框。,AnIntroductiontoDatabaseSystem,2修改存储过程定义,其语法形式如下：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,AnIntroductiontoDatabaseSystem,3重命名存储过程,在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。,AnIntroductiontoDatabaseSystem,3重命名存储过程,也可以使用系统存储过程sp_rename修改存储过程的名称，其语法形式如下：sp_rename原存储过程名称，新存储过程名称,AnIntroductiontoDatabaseSystem,8.2.4执行存储过程,在SQLServer2000中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE,AnIntroductiontoDatabaseSystem,8.2.4执行存储过程,其中各选项的含义如下：EXECUTE：执行存储过程的命令关键字，如果此语句是批处理中的第一条语句，可以省略此关键字。return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必须在批处理、存储过程或函数中声明过。,AnIntroductiontoDatabaseSystem,8.2.4执行存储过程,procedure_name：指定执行的存储过程的名称。;number：用来指定该存储过程与其他同名存储过程同组时的标识号。procedure_name_var：是局部定义变量名，代表存储过程名称。,AnIntroductiontoDatabaseSystem,8.2.4执行存储过程,parameter：是在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或variable变量提供，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。WITHRECOMPILE：指定在执行存储过程时重新编译执行计划。,AnIntroductiontoDatabaseSystem,一个实例,创建一个存储过程sortScore，用于获取给定学号的某门课程考试成绩是否及格的信息。创建存储过程：CreateproceduresortScorekechengchar(2),xuehaochar(9)Asdeclarefenshuintselectfenshu=gradefromsc,AnIntroductiontoDatabaseSystem,wherecno=kechengandsno=xuehaoiffenshu60print不及格elseprint及格执行过程：ExecsortScore4,200515125,AnIntroductio