数据库存储过程ppt课件

1、8.2.1 存储过程概述存储过程概述v在SQL Server 2000中，可以将某些需求多次调用的实现某个特定义务的代码段编写成一个过程，将其保管在数据库中，并由SQL Server效力器经过过程名来调用它们，这些过程就叫做存储过程。8.2.1 存储过程概述存储过程概述v 应该提倡多运用存储过程，缘由有以下几点。v 1实现了模块化编程。v 2调用一次以后，相关信息就保管在内存中，下次调用时可以直接执行。v 3存储过程可以接受输入参数并可以前往输出值。v 4存储过程具有对数据库立刻访问的功能。v 5运用存储过程可以加快程序的运转速度。v 6运用存储过程可以减少网络流量。v 7运用存储过程可以提高

2、数据库的平安性。8.2.1 存储过程概述存储过程概述v在SQL Server 2000中，存储过程分为系统测试、用户存储过程、暂时存储过程、扩展存储过程及远程存储过程。v1.系统存储过程是由系统自动创建的，主要存储在master数据库中，普通以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。8.2.1 存储过程概述存储过程概述2. 用户存储过程是由用户根据各自的运用需求进展创建，完成某一个特定功能的存储过程。称号前普通不加sp_。3. 暂时存储过程属于用户存储过程。假设用户存储过程名前有#，那么为暂时存储过程，只能在一个用户会话中运用。如在名字前有#，那么表示为全局存储过程，可以

3、再一切的用户会话中运用。8.2.1 存储过程概述存储过程概述4. 扩展存储过程是在SQL Server环境之外执行的动态链接库(DLL)，前缀为xp_。它们可以被加载到SQL Server系统中，并且按照存储过程的方式执行。5. 远程存储过程是从远程效力器上调用的存储过程，或是从衔接到另一个效力器上的客户机上调用的存储过程，是非本地效力器上的存储过程。本卷须知本卷须知v 不能将创建存储过程的语句与其他SQL语句组合到单个批处置中；v 默许权限属于数据库一切者，并可以将权限授予其他用户；v 存储过程是数据库对象，称号必需遵守标示符规那么；v 只能在当前数据库中创建存储过程；v 一个存储过程的最大

4、尺寸为128M。8.2.2 创建存储过程创建存储过程v在SQL Server 2000中，可以运用三种方法创建存储过程。v运用创建存储过程导游创建存储过程。v运用SQL Server 2000企业管理器创建存储过程。v运用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。1运用创建存储过程导游创建存储运用创建存储过程导游创建存储过程过程v1在企业管理器中，选中某个SQL Server效力器，选择要创建存储过程的数据库，选择“工具菜单中的“导游菜单项，单击导游中“数据库选项左边的加号，选中“创建存储过程导游选项。v2单击“确定按钮。2运用运用SQL Server

5、2000企业管理企业管理器创建存储过程器创建存储过程v 运用SQL Server 2000企业管理器创建存储过程的步骤如下：v 在SQL Server 2000企业管理器中，选择指定的效力器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建“存储过程命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程命令。3运用运用Transact-SQL语句创建存语句创建存储过程储过程v可以运用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。3运用运用Transact-SQL语句创建存储过程语句创建存

6、储过程v CREATE PROCEDURE的语法方式如下：v CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n v其中各参数的含义如下：vprocedure_name：用于指定所要创建存储过程的称号。vnumber：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROP PROCEDURE语句即可将同

7、组的过程一同除去。3运用运用Transact-SQL语句创建存储过程语句创建存储过程vparameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。vdata_type：用于指定参数的数据类型。vVARYING：用于指定作为输出OUTPUT参数支持的结果集。仅适用于游标参数。3运用运用Transact-SQL语句创建存储过程语句创建存储过程vdefault：用于指定参数的默许值。vOUTPUT：阐明该参数是一个前往参数。vRECOMPILE：阐明SQL Server 2000不会保管该存储过程的执行方案，该存储过程每执行一次都要重新编译。3运用运用Trans

8、act-SQL语句创建存储过程语句创建存储过程vENCRYPTION：表示对存储过程文本进展加密。vFOR REPLICATION：用于指定该存储过程只能在数据复制时运用。本选项不能和WITH RECOMPILE选项一同运用。3运用运用Transact-SQL语句创建存储过程语句创建存储过程vAS：用于指定该存储过程要执行的操作。vsql_statement：是存储过程中包含的恣意数目和类型的Transact-SQL语句。3运用运用Transact-SQL语句创建存储过程语句创建存储过程v例9-1 创建一个GetInfo，用于获取一切学生信息。vIf existsv (SELECT name

9、FROM sysobjectsv WHERE name=GetInfo AND type=p )v DROP PROCEDURE GetInfovGOvCREATE PROCEDURE GetInfovAS v SELECT * FROM 学生表vGO3运用运用Transact-SQL语句创建存储过程语句创建存储过程3运用运用Transact-SQL语句创建存储过程语句创建存储过程v例9-2 创建一个StuInfo，用于获取指定学生的信息。vIF EXISTSvvGOvCREATE RPOCEDURE StuInfov StuId varchar(8)vASv SELECT * FROM 学生

10、表v WHERE 学号=StuIdvGO3运用运用Transact-SQL语句创建存储过程语句创建存储过程v 例9-4 创建GetScore，获取一切课程的平均成果、最高成果、最低成果。并前往结果。v IF EXISTS GOv CREATE PROCEDURE GetScorev kcID varchar(6), AVGScore int OUTPUT,v MAXScore int OUTPUT,MINScore int OUTPUTv ASv SELECT AVGScore=AVG(Grade),MAXScore=v MAX(Grade)，MINScore=MIN(Grade)v FROM

11、 SCv WHERE Cno=kcID8.2.3 管理存储过程管理存储过程v1查看存储过程v存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以运用企业管理器或系统存储过程来查看用户创建的存储过程。1查看存储过程查看存储过程v在企业管理器中查看用户创建的存储过程的方法如下。v1在企业管理器中，翻开指定的效力器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的一切存储过程。1查看存储过程查看存储过程v2右击要查看的存储过程，从弹出的快捷菜单中选择“属性命令，会弹出“存储过程属性对话框。1查看存储过程查看存储过

12、程v也可以运用系统存储过程来查看用户创建的存储过程。可供运用的系统存储过程及其语法方式如下：v1sp_help：用于显示存储过程的参数及其数据类型vsp_help objname= namev参数name为要查看的存储过程的称号。1查看存储过程查看存储过程v2sp_helptext：用于显示存储过程的源代码vsp_helptext objname= namev参数name为要查看的存储过程的称号。1查看存储过程查看存储过程v3sp_depends：用于显示和存储过程相关的数据库对象vsp_depends objname=objectv参数object为要查看依赖关系的存储过程的称号。1查看存储

13、过程查看存储过程v4sp_stored_procedures：用于前往当前数据库中的存储过程列表vsp_stored_proceduressp_name=namev,sp_owner=ownerv,sp_qualifier = qualifier1查看存储过程查看存储过程v其中，sp_name = name 用于指定前往目录信息的过程名；sp_owner = owner 用于指定过程一切者的称号；sp_qualifier = qualifier 用于指定过程限定符的称号。2修正存储过程定义修正存储过程定义v在企业管理器中，单击目录树中的存储过程图标，在窗口的右侧右击要修正的存储过程，从弹出的快

14、捷菜单中选择“属性命令，那么会出现存储过程属性对话框。2修正存储过程定义修正存储过程定义v 其语法方式如下：v ALTER PROCEDURE procedure_name;numberv parameterdata_typev VARYING=defaultOUTPUT,.nv WITHv RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONv FOR REPLICATIONv ASv sql_statement .n 3重命名存储过程重命名存储过程v在企业管理器中，右击要操作的存储过程称号，从弹出的快捷菜单中选择“重命名命令，或者双击存储过程称号，当存储过程称

15、号变成可输入形状时，就可以直接修正该存储过程的称号了。3重命名存储过程重命名存储过程v也可以运用系统存储过程sp_rename修正存储过程的称号，其语法方式如下：vsp_rename 原存储过程称号，新存储过程称号8.2.4 执行存储过程执行存储过程v 在SQL Server 2000中可以运用EXECUTE命令来直接执行存储过程，语法方式如下：v EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 8.

16、2.4 执行存储过程执行存储过程v其中各选项的含义如下：vEXECUTE：执行存储过程的命令关键字，假设此语句是批处置中的第一条语句，可以省略此关键字。vreturn_status：是一个可选的整型变量，保管存储过程的前往形状。这个变量在运用前，必需在批处置、存储过程或函数中声明过。8.2.4 执行存储过程执行存储过程vprocedure_name：指定执行的存储过程的称号。v;number：用来指定该存储过程与其他同名存储过程同组时的标识号。vprocedure_name_var：是部分定义变量名，代表存储过程称号。8.2.4 执行存储过程执行存储过程vparameter：是在创建存储过程时

17、定义的过程参数。调用时向存储过程所传送的参数值由value参数或variable变量提供，或者运用DEFAULT关键字指定运用该参数的默许值，OUTPUT参数阐明指定参数为前往参数。vWITH RECOMPILE：指定在执行存储过程时重新编译执行方案。一个实例一个实例v创建一个存储过程sortScore，用于获取给定学号的某门课程考试成果能否及格的信息。v创建存储过程：vCreate procedure sortScore kecheng char(2),vxuehao char(9)vAsvdeclare fenshu intvselect fenshu = gradevfrom scwhe

18、re cno = kecheng and sno = xuehaoif fenshu 60print 不及格else print 及格执行过程：Exec sortScore 4, 2019151258.2.4 执行存储过程执行存储过程v例9-8 执行9-4创建的GetScore,获取课号为1的相关内容。v程序清单如下：vdeclare AVGScore int,MAXScore int,MINScore intvexec GetScore 1,AVGScore out, MAXScore out, MINScore outvselect AVGScore , MAXScore , MINScorev!思索：执行该存储过程是，声明的变量名能否要和定义的存储过程的变量名坚持一致。8.2.6 删除存储过程删除存储过程v1运用企业管理器删除存储过程v在企业管理器中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除命令，