《数据库技术与应用》课件-项目8：存储过程

认知存储过程课程简介2通过本课程的学习，认知存储过程，掌握存储过程的相关知识。

主要学习内容：一．存储过程概述二．存储过程的优点三．存储过程的分类存储过程概述3存储过程（StoredProcedure）是一组编译好的、存储在服务器上的和完成特定功能的T-SQL语句集，是数据库的对象。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。存储过程概述4存储过程可以被想象成一个可以重复执行的应用程序，可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用执行存储过程，方便用户执行重复的工作。存储过程的优点5运用T-SQL语句进行编程有两种方法，一种是可以将程序存储在本地，并创建向SQLServer发送命令和处理结果的应用程序；另一种是可以把部分T-SQL语句编写的程序作为存储过程存储在SQLServer中，需要时可创建应用程序调用该存储过程，对数据结果进行处理。建议在SQLServer中使用存储过程，而不是在本地计算机上调用T-SQL语句编写的程序，原因在于存储过程具有以下优点：存储过程的优点6（1）允许标准组件式编程。存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程中的T-SQL语句，从而极大地提高了程序的重用性、共享性和可移植性。存储过程的优点7（2）能够提升执行速度。如果某一操作包含大量的T-SQL代码或被多次执行，那么使用存储过程要比T-SQL代码批处理的执行速度快很多。因为存储过程是预编译的，执行步骤的减少提高了其执行速度。存储过程的优点8（3）能够减少网络流量。一个需要数百行T-SQL代码的操作可以通过一条执行存储过程代码的语句来调用，而不需要在网络中发送数百行代码，从而大大减轻了网络的负荷。（4）可以有效提高安全性。可以只授予用户执行存储过程的权限，而不授予用户直接访问存储过程中涉及的表的权限，从而保证了表中数据的安全。存储过程的分类9在SQLServer中的存储过程有系统提供的、用户自定义的以及SQLServer环境之外的三种分类。存储过程的分类10（1）系统提供的存储过程：系统提供的存储过程主要存储在master数据库中并以sp_为前缀，形式为sp_*，如sp_rename、sp_help等，用于执行与系统相关的任务。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。如图所示，可查看数据库中系统存储过程。存储过程的分类11（2）扩展存储过程：扩展存储过程是以在SQLServer环境之外执行的动态链接库(Dymatic-Link)Libraries,DDL)来实现的，执行系统存储过程不能胜任的任务，如发邮件、文件处理等，通常以前缀xp_开头。存储过程的分类12（3）用户自定义的存储过程：用户自定义存储过程是指封装的由用户创建，能完成某一特定的功能的可重复使用代码的模块或例程。用户自定义的存储过程由用户创建的一组T-SQL语句集合组成，可以接收和返回用户提供的参数，完成某些特定功能。创建和执行用户存储过程课程简介14通过本课程的学习，掌握创建和执行用户存储过程。

主要学习内容：一．定义存储过程二．创建与执行存储过程的两种方法三．三种形式存储过程的创建与执行语句定义存储过程15定义存储过程，要由两个主要部分组成：（1）过程名称及其参数（包括输入参数以及传给调用者的输出参数）。（2）过程体：实现某特定功能的T-SQL语句。创建与执行存储过程的两种方法161.创建存储过程的方法1）使用SSMS可视化界面创建如图所示,在"对象资源管理器"窗口中，展开指定数据库节点→“可编程性”节点，用右键单击下方的“存储过程”节点，从弹出的快捷菜单中选择“新建存储过程”命令:创建与执行存储过程的两种方法17在"查询编辑器"中会出现存储过程编程模板，在此模板对应位置编写创建代码，如图所示。创建与执行存储过程的两种方法18最后，单击"SQL编辑器"工具栏上的“执行”按钮，运行成功后，在"对象资源管理器"窗口中，右键单击刷新“存储过程”节点，就可以看到新建的存储过程。创建与执行存储过程的两种方法192）使用T-SQL语句创建使用T-SQL创建存储过程与使用SSMS存储过程编程模板创建存储过程的方法类似：如图所示，在"查询编辑器"中，输入创建代码并执行后，在"对象资源管理器"窗口中，刷新“存储过程”节点，就可以看到新建的存储过程。创建与执行存储过程的两种方法202.执行调用存储过程的方法1）使用SSMS可视化界面执行如图所示,在"对象资源管理器"窗口中，展开指定数据库节点→“可编程性”节点→“存储过程”节点，右键单击需要执行的存储过程，从弹出的快捷菜单中选择“执行存储过程”命令:创建与执行存储过程的两种方法21在打开的“执行过程”窗口中，为参数提供值，按“确定”按钮。创建与执行存储过程的两种方法22如图所示，执行结果，就会展示在结果面板上：创建与执行存储过程的两种方法232）使用T-SQL语句执行使用EXECUTE（简写为EXEC）语句可调用执行存储过程。语法结构如下所示：

EXEC[CUTE]存储过程名称[参数表]如图所示，在"查询编辑器"中输入EXEC语句执行调用存储过程，可以完成存储过程中所定义的功能。三种形式存储过程的创建与执行语句24根据存储过程定义的参数形式，可以把存储过程分为不带任何参数、带输入参数和带输出参数这三种形式的存储过程。三种形式存储过程的创建与执行语句251.不带参数的存储过程1）创建语句语法结构如下所示：CREATE[PROC|PROCEDURE]存储过程名ASsql_statementPROCEDURE可以简写成PROC。sql_statement是存储过程要实现某特定功能的T-SQL语句。三种形式存储过程的创建与执行语句262）执行语句语法结构如下所示: EXEC[UTE]存储过程名不带参数的存储过程只要通过调用过程名即可执行存储过程。三种形式存储过程的创建与执行语句272.带输入参数的存储过程1）创建语句语法结构如下所示：CREATE[PROC|PROCEDURE]存储过程名[{@输入参数名称参数数据类型}[=参数的默认值][,...n]ASsql_statement三种形式存储过程的创建与执行语句28@输入参数名称：是定义局部变量作为存储过程的输入参数。输入参数是由调用程序向存储过程传递的参数，创建存储过程时被定义的输入参数，在执行调用该存储过程中要给出相应的变量值。参数的默认值：是在执行调用存储过程时，如未提供输入参数具体值，则直接使用默认值作为替代值进行调用。三种形式存储过程的创建与执行语句292）执行语句语法结构如下所示：执行调用带输入参数的存储过程，有两种方法：一种是使用参数名来传递参数值，另一种是按位置传递参数值，它们的语法结构分别如下:①EXEC[UTE]存储过程名[@参数名=参数值][DEFAULT][，…n]②EXEC存储过程名[参数值1，参数值2，…]三种形式存储过程的创建与执行语句30以上两种执行调用方式各有特点，第一种因带参数名，则执行调用时，参数前后位置顺序可以改变，而不影响参数值的传递。第二种因无参数名，则执行调用时，参数值必须与创建中定义的参数位置顺序和数量一致。三种形式存储过程的创建与执行语句313.带输出参数的存储过程1）创建语句语法结构如下所示：CREATE[PROC|PROCEDURE]存储过程名[@输入参数名称参数数据类型[=参数的默认值][,...n][@输出参数名数据类型[=默认值]OUTPUT,...]ASsql_statement三种形式存储过程的创建与执行语句32形参后加关键字OUTPUT，表示此参数为输出参数。存储过程如有返回值，是通过定义输出参数来实现的。注意，输出参数必须位于所有输入参数说明之后。三种形式存储过程的创建与执行语句332）执行语句语法结构如下所示：EXEC[UTE]存储过程名[[@参数名=]{参数值|@变量[OUTPUT]|[默认值]}][,...n]注意，为了接收存储过程的返回值，在执行调用前，必须事先声明一个局部变量作为执行调用存储过程时需要的输出参数，以便存放返回值。管理存储过程课程简介35通过本课程的学习，掌握使用SSMS可视化图形界面和T-SQL语句两种方法来管理存储过程。

主要学习内容：一．查看存储过程二．修改存储过程三．删除存储过程查看存储过程361.使用SSMS可视化图形界面查看1）如图所示，在"对象资源管理器"窗口中，展开指定“数据库”节点→“可编程性”→“存储过程”节点，展开指定的存储过程节点，可以看到此存储节点定义的参数及返回值类型等信息。查看存储过程372）用右键单击指定的存储过程节点从弹出的快捷菜单中选择“查看依赖关系”及“属性”命令，即可查看相关“对象依赖关系”及本存储过程的创建信息。查看存储过程382.使用T_SQL语句查看存储过程被创建后，它的名字被收录在系统表sysobjects中，它的源代码被存放在系统表syscomments中。用户可以使用系统存储过程来查看创建的存储过程的相关信息。查看存储过程391）sp_help可用于显示存储过程的参数及其数据类型，其语法格式如下:sp_help[[@objname=]存储过程名]2）sp_helptext可用于显示存储过程的源代码，语法格式如下:sp_helptext[[@objname=]存储过程]查看存储过程40【例】查看数据库“BookManageDB”中存储过程“ProcSelectUserTypeInfo”的相关信息。代码与运行结果，如图所示：可以看出，运行这两条系统存储过程后，系统分别展示了存储过程的源代码与存储过程的相关信息内容。修改存储过程41修改存储过程。如果需要更改存储过程中的语句或参数，可以删除该存储过程后重新创建该存储过程，也可以直接修改该存储过程。删除后重新创建存储过程时，所有与该存储过程相关的权限都将丢失；而修改存储过程时，过程或参数定义会更改，但权限将保留。修改存储过程42使用ALTERPROCEDURE语句可修改存储过程，语法格式如下所示：ALTERPROC[EDURE]<过程名>[@形参名数据类型=[参数默认值],...][,][@变参名数据类型OUTPUT,...]AS sql_statement从语法中可以看出，修改存储过程使用关键字ALTER，语句中其他语法与创建存储过程的语法基本一样。修改存储过程43【例】修改存储过程“ProcSelectUserBorrowInfo”参数，将形参@UserID添加默认值100。操作步骤如下：修改存储过程441）在“对象资源管理器”中，展开数据库“BookManageDB”节点，然后再展开“可编程性”—“存储过程”节点，右击存储过程“ProcSelectUserBorrowInfo”，在弹出的快捷菜单中选择“修改”命令，在出现的“查询编辑器”中，将代码按要求进行修改，如图所示：修改存储过程452）修改完毕后，单击工具栏上的“执行”按钮即可，系统会在下方消息卡上提示“命令已成功完成”。删除存储过程461.使用SSMS可视化图形界面删除在"对象资源管理器"窗口中，展开指定“数据库”节点→“可编程性”→“存储过程”节点，右键单击指定的存储过程，从弹出的快捷菜单中选择“删除”命令即可完成删除。删除存储过程472.使用T_SQL语句删除删除用户存储过程使用DROP命令，可以从当前数据库中删除一个或多个存储过程，语法格式如下所示:DROP{PROC|PROCEDURE}存储过程名1[,...n]说明：在删除指定存储过程时，需先执行sp_denpeds存储过程来确定是否有对象依赖于此存储过程。删除存储过程48【例】删除数据库“BookManage”的存储过程“ProcSelectUserTypeInfo”。步骤如下；１）如图所示，确认存储过程“ProcSelectUserTypeInfo”无对象依赖。删除存储过程49２）执行删除：代码与运行结果，如图所示：删除完成后，要在"对象资源管理器"窗口中刷新一下数据库“BookManage”的存储过程节点，便可看到“ProcSelectUserTypeInfo1”已被删除。存储过程的应用课程简介51通过本课程的学习，结合实例掌握存储过程的应用。

主要学习内容：一．存储过程的具体应用存储过程的具体应用52【例1】在数据库“BookManageDB”中创建一个单表查询的存储过程“Proc_Select_Users”并执行调用此存储过程查询所有用户信息。分析：本例中的存储过程只需完成简单查询所有用户信息的功能，无需提供额外参数就可查到结果，隶属于无参存储过程。存储过程的具体应用531)如图所示，在"查询编辑器"中,输入以下创建代码并执行。在"对象资源管理器"中，刷新一下“存储过程”节点，展开创建好的“Proc_Select_Users”，可以看到这是一个无参存储过程。存储过程的具体应用542）在"查询编辑器"中,输入执行调用“Proc_Select_Users”的代码，运行结果如图所示，可以看出，无参存储过程只要提供存储过程名，无需提供任何参数即可执行：存储过程的具体应用55【例2】创建一个根据提供的用户号@UserID，查询用户信息的存储过程“Proc_Select_User”，并执行调用此存储过程。存储过程的具体应用561)如图所示，在"查询编辑器"中,输入以下创建代码并执行。在"对象资源管理器"中，展开创建好的“Proc_Select_User”，可以看到这是一个有一个输入参数的存储过程。存储过程的具体应用572）在"查询编辑器"中,输入以下调用“Proc_Select_User”的代码，运行结果如图所示，可以看出，在存储过程中设置了输入形参，调用时必须有实参赋值方可执行：存储过程的具体应用58注意：在调用过程中，首先将实参@UserID赋值1002，然后再将值“1002”传递给存储过程中定义的形参@UserID，根据传递来的用户ID号1002，存储过程完成了在Users表中查询1002用户所有信息的功能。存储过程的具体应用59【例3】创建一个根据提供的用户号@UserID（带有默认值），查询用户信息的存储过程“Proc_Select_User1”，并执行调用此存储过程。分析：此题是例2的扩展版，需要在创建语句中增加输入参数默认值存储过程的具体应用601)如图所示，在"查询编辑器"中,输入以下创建代码并执行。在"对象资源管理器"中，展开创建好的“Proc_Select_User1”，可以看到参数提示拥有默认值。存储过程的具体应用612）在"查询编辑器"中,输入以下调用“Proc_Select_User1”的代码，运行结果如图所示，可以看出,带有输入参数默认值的存储过程，即可提供实参值进行执行调用，也可不提供实参值直接利用默认值1001作为形参初值执行调用：存储过程的具体应用62【例4】创建一个根据提供的数返回平方结果的存储过程“Pro_Calc”，并执行调用此存储过程。存储过程的具体应用631)如图所示，在"查询编辑器"中,输入以下代码并执行。在"对象资源管理器"窗口中，展开创建好的“Pro_Calc”，可以看到这是一个即带输入参数又带输出参数的存储过程。存储过程的具体应用642）在"查询编辑器"中,输入以下调用的代码，运行结果如图所示，要特别注意，为了接收存储过程返回值，必须