第10章-存储过程课件.ppt

第第10章存储过程章存储过程10.1.1 10.1.1 什么是存储过程什么是存储过程10.1 10.1 概概 述述 存储过程是在数据库服务器端执行的一组存储过程是在数据库服务器端执行的一组T-SQLT-SQL语句的语句的集合，经编译后存放在数据库服务器端。存储过程作为一集合，经编译后存放在数据库服务器端。存储过程作为一个单元进行处理并以一个名称来标识。个单元进行处理并以一个名称来标识。它能够向用户返回数据、向数据库表中写入或修改数它能够向用户返回数据、向数据库表中写入或修改数据，还可以执行系统函数和管理操作，用户在编程中只需据，还可以执行系统函数和管理操作，用户在编程中只需要给出存储过程的名称和必需的参数，就可以方便地调用要给出存储过程的名称和必需的参数，就可以方便地调用它们。它们。10.1.2 10.1.2 存储过程的类型存储过程的类型l系统存储过程：由系统存储过程：由SQL ServerSQL Server提供，通常使用提供，通常使用“sp_sp_”为前缀，主要用于管理为前缀，主要用于管理SQL ServerSQL Server和显示有关数据库及和显示有关数据库及用户的信息。用户的信息。l用户自定义存储过程：用户编定的可以重复用的用户自定义存储过程：用户编定的可以重复用的T-SQLT-SQL语句功能模块，并且在数据库中有唯一的名称，可以附语句功能模块，并且在数据库中有唯一的名称，可以附带参数，完全由用户自己定义、创建和维护。带参数，完全由用户自己定义、创建和维护。l临时存储过程：临时过程是用户自定义过程的一种形式。临时存储过程：临时过程是用户自定义过程的一种形式。临时存储过程与永久过程相似，只是临时存储过程存储临时存储过程与永久过程相似，只是临时存储过程存储于于tempdbtempdb中。中。临时存储过程有两种类型：本地过程和临时存储过程有两种类型：本地过程和全局过程。全局过程。l扩展存储过程：允许用户使用编程语言（例如扩展存储过程：允许用户使用编程语言（例如C C）创建）创建自己的外部例程。自己的外部例程。10.2创建存建存储过程程10.2.1.10.2.1.使用使用SQL ServerSQL Server管理控制器创建存储过程管理控制器创建存储过程【例例10.110.1】使用使用SQL ServerSQL Server管理控制器创建存储过程管理控制器创建存储过程maxscoremaxscore，用于输出所有学生的最高分。，用于输出所有学生的最高分。操作步骤操作步骤启动启动SQL ServerSQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中中展开展开“LCB-PCLCB-PC”服务器节点。服务器节点。展开展开“数据库数据库|school|school|可编程性可编程性|存储过程存储过程”节点，单击鼠节点，单击鼠标右键，在出现的快捷菜单中选择标右键，在出现的快捷菜单中选择“新建存储过程新建存储过程”命令，命令，如图如图10.110.1所示。所示。单击工具栏中的单击工具栏中的“！”按钮，将其保存在数据库中。此时按钮，将其保存在数据库中。此时选中选中“存储过程存储过程”节点，单击鼠标右键，在出现的快捷菜节点，单击鼠标右键，在出现的快捷菜单中选择单中选择“刷新刷新”命令，会看到命令，会看到“存储过程存储过程”的下方出现的下方出现了了maxscoremaxscore存储过程。存储过程。10.2.2 10.2.2 使用使用CREATE PROCEDURECREATE PROCEDURE语句创建存储过程语句创建存储过程使用使用CREATE PROCEDURECREATE PROCEDURE语句的基本语法格式如下：语句的基本语法格式如下：CREATE PROCEDURE 存储过程名存储过程名;number parameter 数据类型数据类型=默认值默认值 OUT|OUTPUT READONLY ,n WITH RECOMPILE|ENCRYPTION FOR REPLICATION AS SQL语句语句 n 创建存储过程时应该注意下面几点：创建存储过程时应该注意下面几点：l存储过程的最大大小为存储过程的最大大小为128MB128MB。l只能在当前数据库中创建用户定义的存储过程。只能在当前数据库中创建用户定义的存储过程。l在单个批处理中，在单个批处理中，CREATE PROCEDURECREATE PROCEDURE语句不能与其他语句不能与其他T-SQLT-SQL语句组合使用。语句组合使用。l存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。嵌套的最大深度不能超过存储过程。嵌套的最大深度不能超过3232层。层。l如果存储过程创建了临时表，则该临时表只能用于该存储过如果存储过程创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行完毕后，临时表自动被删除。程，而且当存储过程执行完毕后，临时表自动被删除。l创建存储过程时，在创建存储过程时，在“SQLSQL语句语句”中不能包含中不能包含SET SET SHOWPLAN_TEXTSHOWPLAN_TEXT、SET SHOWMAN_ALLSET SHOWMAN_ALL、CREATE VIEWCREATE VIEW、CREATE CREATE DEFAULTDEFAULT、CREATE RULECREATE RULE、CREATE PROCEDURECREATE PROCEDURE和和CREATE CREATE TRIGGERTRIGGER（用于创建触发器，在下一章介绍）语句。（用于创建触发器，在下一章介绍）语句。lSQL ServerSQL Server允许创建的存储过程中引用尚不存在的对象。允许创建的存储过程中引用尚不存在的对象。10.3 10.3 执行存储过程执行存储过程使用使用EXECUTEEXECUTE或或EXECEXEC关键字的语句执行存储过程。关键字的语句执行存储过程。EXECUTE return_status=存储过程名存储过程名 ;number|procedure_name_var parameter=值值|variable OUTPUT|DEFAULT ,n WITH RECOMPILE 【例例10.310.3】执行例执行例10.110.1中创建的存储过程中创建的存储过程maxscoremaxscore并查并查看输出的结果。看输出的结果。USE schoolGOEXEC maxscoreGO求求求求解解解解程程程程序序序序 在调用存储过程时，有两种传递参数的方式。在调用存储过程时，有两种传递参数的方式。（1 1）在传递参数时，使传递的参数和定义时的参数顺序）在传递参数时，使传递的参数和定义时的参数顺序一致。其一般格式如下：一致。其一般格式如下：EXEC EXEC 存储过程名存储过程名 实参列表实参列表 （2 2）采用）采用“参数参数=值值”的形式，此时，各个参数的顺序的形式，此时，各个参数的顺序可以任意排列。其一般格式如下：可以任意排列。其一般格式如下：EXEC EXEC 存储过程名存储过程名 参数参数1=1=值值1,1,参数参数2=2=值值2,2,采用第一种方式执行存储过程采用第一种方式执行存储过程maxnomaxno的程序如下：的程序如下：USE schoolUSE schoolGOGO EXEC maxno 103EXEC maxno 103GOGO采用第二种方式执行存储过程采用第二种方式执行存储过程maxnomaxno的程序如下：的程序如下：USE schoolUSE schoolGOGOEXEC maxno no=103EXEC maxno no=103GOGO10.4.2 10.4.2 在存储过程中使用默认参数在存储过程中使用默认参数 在设计存储过程时，可以为参数提供一个默认值，默认在设计存储过程时，可以为参数提供一个默认值，默认值必须为常量或者值必须为常量或者NULLNULL。其一般格式如下：。其一般格式如下：CREATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名(参数参数1=1=默认值默认值1,1,参数参数2=2=默认值默认值2,)2,)AS SQLAS SQL语句语句在调用存储过程时，如果不指定对应的实参值，则自动在调用存储过程时，如果不指定对应的实参值，则自动用对应的默认值代替。用对应的默认值代替。当不指定实参调用当不指定实参调用maxno1maxno1存储过程时，其结果如图存储过程时，其结果如图10.510.5所示。所示。当指定实参为当指定实参为105105调用调用maxno1maxno1存储过程时，其结果如图存储过程时，其结果如图10.610.6所示。所示。结论：结论：从执行结果可以看到，从执行结果可以看到，当调用存储过程时，没有指当调用存储过程时，没有指定参数值时就自动使用相应定参数值时就自动使用相应的默认值。的默认值。10.4.3 10.4.3 在存储过程中使用返回参数在存储过程中使用返回参数 在创建存储过程时，可以定义返回参数。在执行存储过在创建存储过程时，可以定义返回参数。在执行存储过程时，可以将结果返回给返回参数。返回参数应用程时，可以将结果返回给返回参数。返回参数应用OUTPUTOUTPUT进进行说明。行说明。【例例10.710.7】创建一个存储过程创建一个存储过程averageaverage，它返回两个参数，它返回两个参数st_namest_name和和st_avgst_avg，分别代表了姓名和平均分。并编写，分别代表了姓名和平均分。并编写T-T-SQLSQL语句执行该存储过程和查看输出的结果。语句执行该存储过程和查看输出的结果。USE schoolUSE schoolGOGOIF OBJECT_ID(average,P)IS NOT NULLIF OBJECT_ID(average,P)IS NOT NULL DROP PROCEDURE average DROP PROCEDURE average-如果存储过程如果存储过程averageaverage存在存在,删除之删除之GOGOCREATE PROCEDURE averageCREATE PROCEDURE average(st_no int,st_no int,st_name char(8)OUTPUT,st_name char(8)OUTPUT,-返回参数返回参数st_avg float OUTPUTst_avg float OUTPUT-返回参数返回参数)AS)AS SELECT st_name=student.SELECT st_name=student.姓名姓名,st_avg=AVG(score.,st_avg=AVG(score.分数分数)FROM student,score FROM student,score WHERE student.WHERE student.学号学号=score.=score.学号学号 GROUP BY student.GROUP BY student.学号学号,student.,student.姓名姓名 HAVING student.HAVING student.学号学号=st_no=st_noGOGO求求求求解解解解程程程程序序序序执行该存储过程，来查询学号为执行该存储过程，来查询学号为“105”105”的学生姓名和平均分：的学生姓名和平均分：DECLARE st_name char(10)DECLARE st_avg floatEXEC average 105,st_name OUTPUT,st_avg OUTPUTSELECT 姓名姓名=st_name,平均分平均分=st_avgGO10.4.4 10.4.4 存储过程的返回值存储过程的返回值 存储过程在执行后都会返回一个整型值（称为存储过程在执行后都会返回一个整型值（称为“返回代返回代码码”），指示存储过程的执行状态。），指示存储过程的执行状态。如果执行成功，返回如果执行成功，返回0 0；否则返回；否则返回-1-1-99-99之间的数值之间的数值（例如（例如-1-1表示找不到对象，表示找不到对象，-2-2表示数据类型错误，表示数据类型错误，-5-5表示语表示语法错误等）。也可以使用法错误等）。也可以使用RETURNRETURN语句来指定一个返回值。语句来指定一个返回值。执行该存储过程：执行该存储过程：USE Test GODECLARE ret_int intEXEC ret_int=test_ret 1PRINT 返回值返回值PRINT-PRINT ret_intEXEC ret_int=test_ret 0PRINT ret_intEXEC ret_int=test_ret-1PRINT ret_int10.4.5 10.4.5 使用使用SQL ServerSQL Server管理控制器执行存储过程管理控制器执行存储过程 【例例10.1010.10】以下程序建立有存储过程以下程序建立有存储过程studavgstudavg，使用，使用SQL ServerSQL Server管理控制器执行该存储过程。管理控制器执行该存储过程。USE schoolGOIF OBJECT_ID(studavg,P)IS NOT NULL DROP PROCEDURE studavg-如果存储过程如果存储过程studavg存在存在,删除之删除之GOCREATE PROC studavg(no int,avg float=0 OUTPUT)AS BEGIN IF NOT EXISTS(SELECT*FROM score WHERE 学号学号=no)RETURN-1 SELECT avg=AVG(score.分数分数)FROM score WHERE score.学号学号=no RETURN 1 ENDGO 解：解：上述存储过程上述存储过程studavgstudavg的功能是：求学号为的功能是：求学号为nono的学的学生的平均分生的平均分avgavg，如果指定的学号不正确，返回，如果指定的学号不正确，返回-1-1，否则，否则，求出相应的平均分并返回求出相应的平均分并返回1 1。启动启动SQL ServerSQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中展开中展开“LCB-PCLCB-PC”服务器节点。服务器节点。展开展开“数据库数据库|数据库名数据库名|可编程性可编程性|存储过程存储过程”节点，节点，在存储过程列表中，右击要执行的用户自定义存储过在存储过程列表中，右击要执行的用户自定义存储过程，然后在出现的快捷菜单中选择程，然后在出现的快捷菜单中选择“执行存储过程执行存储过程”命令。命令。操作步骤操作步骤如果如果“执行过程执行过程”对话框，设置输入参数的值为对话框，设置输入参数的值为888888，由，由于不存在该学生，存储过程于不存在该学生，存储过程studavgstudavg返回返回-1-1，如图，如图10.1110.11所示。所示。10.5 10.5 存储过程的管理存储过程的管理10.5.1 10.5.1 查看存储过程查看存储过程1.1.使用使用SQL ServerSQL Server管理控制器查看存储过程管理控制器查看存储过程 【例例10.1110.11】使用使用SQL ServerSQL Server管理控制器查看例管理控制器查看例10.810.8所创所创建的存储过程建的存储过程stud1_scorestud1_score。操作步骤操作步骤启动启动SQL ServerSQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中中展开展开“LCB-PCLCB-PC”服务器节点。服务器节点。在右边的编辑器窗口中出现存储过程在右边的编辑器窗口中出现存储过程stud_scorestud_score源代码，如源代码，如图图10.1210.12所示。此时用户只能查看其代码。所示。此时用户只能查看其代码。2.2.使用系统存储过程来查看存储过程使用系统存储过程来查看存储过程SQL ServerSQL Server提供了如下系统存储过程用于查看用户创建的提供了如下系统存储过程用于查看用户创建的存储过程。存储过程。（1 1）sp_helpsp_help用于显示存储过程的参数及其数据类型，其语法如下：用于显示存储过程的参数及其数据类型，其语法如下：sp_help objname=namesp_help objname=name其中，参数其中，参数namename为要查看的存储过程的名称。为要查看的存储过程的名称。（2 2）sp_helptextsp_helptext用于显示存储过程的源代码，其语法如下：用于显示存储过程的源代码，其语法如下：sp_helptext objname=namesp_helptext objname=name其中，参数其中，参数namename为要查看的存储过程的名称。为要查看的存储过程的名称。（3 3）sp_dependssp_depends用于显示和存储过程相关的数据库对象，其语法如下：用于显示和存储过程相关的数据库对象，其语法如下：sp_depends objname=objectsp_depends objname=object其中，参数其中，参数objectobject为要查看依赖关系的存储过程的名称。为要查看依赖关系的存储过程的名称。（4 4）sp_stored_proceduressp_stored_procedures用于返回当前数据库中的存储过程列表，其语法如下：用于返回当前数据库中的存储过程列表，其语法如下：sp_stored_procedure sp_name=namesp_stored_procedure sp_name=name ,sp_owner=owner,sp_owner=owner ,sp_qualifier=qualifier,sp_qualifier=qualifier 【例例10.1210.12】使用相关系统存储过程查看例使用相关系统存储过程查看例10.210.2所创建的存所创建的存储过程储过程stud_scorestud_score的相关内容。的相关内容。USE schoolUSE schoolGOGOEXEC sp_help stud_scoreEXEC sp_help stud_scoreEXEC sp_helptext stud_scoreEXEC sp_helptext stud_scoreEXEC sp_depends stud_scoreEXEC sp_depends stud_score求求求求解解解解程程程程序序序序10.5.2 10.5.2 修改存储过程修改存储过程1.1.使用使用SQL ServerSQL Server管理控制器修改存储过程管理控制器修改存储过程 【例例10.1310.13】使用使用SQL ServerSQL Server管理控制器修改例管理控制器修改例10.210.2所所创建的存储过程创建的存储过程stud_scorestud_score。操作步骤操作步骤启动启动SQL ServerSQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中中展开展开“LCB-PCLCB-PC”服务器节点。服务器节点。展开展开“数据库数据库”|“schoolschool”|“可编程性可编程性”|“存储过程存储过程”|“dbo.stud_scoredbo.stud_score”节点，单击鼠标右键，在出现的快节点，单击鼠标右键，在出现的快捷菜单中选择捷菜单中选择“修改修改”命令。命令。此时右边的编辑器窗口出现此时右边的编辑器窗口出现stud_scorestud_score存储过程的源代码存储过程的源代码（将（将“CREATE PROCEDURECREATE PROCEDURE”改为改为“ALTER PROCEDUREALTER PROCEDURE”），），如图如图10.1410.14所示，用户可以直接进行修改。修改完毕，单所示，用户可以直接进行修改。修改完毕，单击工具栏中的击工具栏中的“！执行！执行”按钮执行该存储过程，从而达到按钮执行该存储过程，从而达到修改的目的。修改的目的。使用使用ALTER PROCEDUREALTER PROCEDURE语句可以更改先前通过执行语句可以更改先前通过执行CREATE CREATE PROCEDUREPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器，其语法形式如下：存储过程或触发器，其语法形式如下：ALTER PROCEDURE ALTER PROCEDURE 存储过程名存储过程名参数列表参数列表 AS SQLAS SQL语句语句当使用当使用ALTER PROCEDUREALTER PROCEDURE语句时，如果在语句时，如果在CREATE PROCEDURECREATE PROCEDURE语句中使用过参数，那么在语句中使用过参数，那么在ALTER PROCEDUREALTER PROCEDURE语句中也应该使用语句中也应该使用这些参数。每次只能修改一个存储过程。这些参数。每次只能修改一个存储过程。2.2.使用使用ALTER PROCEDUREALTER PROCEDURE语句修改存储过程语句修改存储过程 【例例10.1410.14】编写一个程序，先创建一个存储过程编写一个程序，先创建一个存储过程studprocstudproc，输出，输出“10031003”班的所有学生，利用班的所有学生，利用sysobjectssysobjects和和syscommentssyscomments两个系统表输出该存储过程的两个系统表输出该存储过程的idid和和texttext列。然后利用列。然后利用ALTER ALTER PROCEDUREPROCEDURE语句修改该存储过程，将其改为加密方式，最后再输语句修改该存储过程，将其改为加密方式，最后再输出该存储过程的出该存储过程的idid和和texttext列。列。USE schoolGOIF OBJECT_ID(studproc,P)IS NOT NULL DROP PROCEDURE studproc-如果存储过程如果存储过程studproc存在存在,删除之删除之GOCREATE PROCEDURE studproc AS SELECT*FROM student WHERE 班号班号=1003GO求求求求解解解解程程程程序序序序SELECT sysobjects.id,syscomments.textFROM sysobjects,syscommentsWHERE sysobjects.name=studproc AND sysobjects.xtype=P AND sysobjects.id=syscomments.id通过以下语句输出通过以下语句输出studproc存储过程的存储过程的id和和text列：列：修改该存储过程的语句如下：修改该存储过程的语句如下：USE schoolGOALTER PROCEDURE studproc WITH ENCRYPTION AS SELECT*FROM student WHERE 班号班号=1003GO 再次执行前面的输出再次执行前面的输出studprocstudproc存储过程的存储过程的idid和和texttext列的列的语句，其执行结果如图语句，其执行结果如图10.1610.16所示。从中看到，加密过的存所示。从中看到，加密过的存储过程查询出的源代码是空值，从而起到保护源程序的作用。储过程查询出的源代码是空值，从而起到保护源程序的作用。10.5.3 10.5.3 重命名存储过程重命名存储过程1.1.使用使用SQL ServerSQL Server管理控制器重命名存储过程管理控制器重命名存储过程 【例例10.1510.15】使用使用SQL ServerSQL Server管理控制器将存储过程管理控制器将存储过程studprocstudproc重命令为重命令为studproc1studproc1。启动启动SQL ServerSQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中中展开展开“LCB-PCLCB-PC”服务器节点。服务器节点。操作步骤操作步骤展开展开“数据库数据库”|“schoolschool”|“可编程性可编程性”|“存储过程存储过程”|“dbo.studprocdbo.studproc”节点，单击鼠标右键，在出现的快捷节点，单击鼠标右键，在出现的快捷菜单中选择菜单中选择“重命名重命名”命令。命令。此时存储过程名此时存储过程名“studprocstudproc”变成可编辑的，可以直接修变成可编辑的，可以直接修改该存储过程的名称为改该存储过程的名称为studproc1studproc1。2.2.使用系统存储过程重命名用户存储过程使用系统存储过程重命名用户存储过程 重命名存储过程的系统存储过程为重命名存储过程的系统存储过程为sp_renamesp_rename，其语法格，其语法格式如下：式如下：sp_rename 原存储过程名原存储过程名,新存储过程名新存储过程名 【例例10.1610.16】使用系统存储过程使用系统存储过程sp_renamesp_rename将上例改名的用将上例改名的用户存储过程户存储过程studproc1studproc1再更名为再更名为studprocstudproc。USE schoolUSE schoolGOGOEXEC sp_rename studproc,studproc1EXEC sp_rename studproc,studproc1求求求求解解解解程程程程序序序序10.5.4 10.5.4 删除存储过程删除存储过程1.1.使用使用SQL ServerSQL Server管理控制器删除用户存储过程管理控制器删除用户存储过程启动启动SQL ServerSQL Server管理控制器。在管理控制器。在“对象资源管理器对象资源管理器”中中展开展开“LCB-PCLCB-PC”服务器节点。服务器节点。操作步骤操作步骤 【例例10.1710.17】使用使用SQL ServerSQL Server管理控制器删除存储过程管理控制器删除存储过程studprocstudproc。展开展开“数据库数据库”|“schoolschool”|“可编程性可编程性”|“存储过程存储过程”|“dbo.studprocdbo.studproc”节点，单击鼠标右键，在出现的快节点，单击鼠标右键，在出现的快捷菜单中选择捷菜单中选择“删除删除”命令。命令。在出现的在出现的“删除对象删除对象”对话框中选择对话框中选择“确定确定”按钮即可按钮即可删除存储过程名称删除存储过程名称studprocstudproc。2.2.使用使用DROP PROCEDUREDROP PROCEDURE语句删除用户存储过程语句删除用户存储过程 删除存储过程可以使用删除存储过程可以使用DROP PROCEDUREDROP PROCEDURE语句，它可以将语句，它可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法格式如下：其语法格式如下：DROP PROCEDURE DROP PROCEDURE 用户存储过程列表用户存储过程列表 【例例10.1810.18】使用使用DROP PROCEDUREDROP PROCEDURE语句删除用户存储过程语句删除用户存储过程stud_scorestud_score和和stud1_scorestud1_score。USE schoolUSE schoolGOGODROP PROCEDURE stud_score,stud1_scoreDROP PROCEDURE stud_score,stud1_scoreGOGO求求求求解解解解程程程程序序序序