《存储过程的创建和使用.ppt》由会员分享，可在线阅读，更多相关《存储过程的创建和使用.ppt（38页珍藏版）》请在三一办公上搜索。
1、第9章 存储过程的创建和使用,本章学习目标,了解存储过程的概念了解使用存储过程的优点了解系统存储过程的特点及用途掌握创建存储过程的方法掌握执行存储过程的方法掌握查看和修改存储过程的方法掌握删除存储过程的方法,9.1 概述,9.1.1 存储过程的概念,在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。,9.1.1 存储过程的概念,存储过程有以下特点
2、：存储过程中可以包含一条或多条Transact-SQL语句。存储过程可以接受输入参数并可以返回输出值。在一个存储过程中可以调用另一个存储过程。存储过程可以返回执行情况的状态代码给调用它的程序。,9.1.2 存储过程的优点,使用存储过程有很多优点，具体如下：实现了模块化编程，一个存储过程可以被多个用户共享和重用。存储过程具有对数据库立即访问的功能。使用存储过程可以加快程序的运行速度。使用存储过程可以减少网络流量。使用存储过程可以提高数据库的安全性。,9.1.3 存储过程的分类,在SQL Server中的存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。系统存储过程：由系统自动创建，系
3、统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQL Server 2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。用户自定义存储过程：是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。可以接受输入参数，返回输出参数。,9.1.3 存储过程的分类,在SQL Server 2005中，用户自定义存储过程有两种类型：Transact-SQL存储过程和CLR存储过程。Transact-SQL存储过程：是指保存的Transact-SQL语句集合。CLR存储过程：是指对Microsoft.NET Framework公共语言运行时(CLR)
4、方法的引用，可以接受和返回用户提供的参数。,9.2 创建存储过程,在SQL Server中，可以使用两种方法创建存储过程：当创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其他存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。,9.2.1 使用SQL Server管理控制台创建存储过程,在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程”选项。例9-2-1：创建一个名称为“StuInfo”的存储过程
5、，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。,9.2.2 使用Transact-SQL语句创建存储过程,可以使用CREATE PROCEDURE命令创建存储过程，考虑下列几个事项：CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。必须具有数据库的CREATE PROCEDURE权限。只能在当前数据库中创建存储过程。不要创建任何使用sp_作为前缀的存储过程。,9.2.2 使用Transact-SQL语句创建存储过程,CREATE PROCEDURE的语法形式如下：CREATE PROC|PRO
6、CEDURE schema_name.procedure_name parameter type_schema_name.data_type VARYING=default OUT|OUTPUT,.n WITH ENCRYPTION AS;.n;:=BEGIN statements END,9.2.2 使用Transact-SQL语句创建存储过程,其中，各参数的意义如下：schema_name：过程所属架构的名称。procedure_name：新存储过程的名称。parameter：过程中的参数。type_schema_name.data_type：参数以及所属架构的数据类型。VARYING：指
7、定作为输出参数支持的结果集。仅适用于cursor参数。,9.2.2 使用Transact-SQL语句创建存储过程,default：参数的默认值。OUTPUT：指示参数是输出参数。ENCRYPTION：将CREATE PROCEDURE语句的原始文本加密。：要包含在过程中的一个或多个Transact-SQL语句。,9.2.2 使用Transact-SQL语句创建存储过程,例9-2-2：创建一个存储过程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。例9-2-2：创建一个带有参数的存储过程
8、Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。,9.2.2 使用Transact-SQL语句创建存储过程,例9-2-3：创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生编号，在T_STUDENT中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。,9.3 执行存储过程,存储过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：EXECUTE return_status=procedure_name|procedure_
9、name_var parameter=value|variableOUTPUT|DEFAULT,.n,9.3 执行存储过程,其中，各选项的含义如下：EXECUTE：执行存储过程的命令关键字。return_status：是一个可选的整型变量，保存存储过程的返回状态。procedure_name：指定执行的存储过程的名称。procedure_name_var：是局部定义变量名，代表存储过程名称。parameter：是在创建存储过程时定义的过程参数。,9.3 执行存储过程,例9-3-1：执行前面创建的StuInfo存储过程，它是一个无参的存储过程。注意：如果省略EXECUTE关键字，则存储过程必须是
10、批处理中的第一条语句，否则会出错。例9-3-2：执行存储过程StuScoreInfo。,9.3 执行存储过程,例9-3-3：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。例9-3-4：执行存储过程Stu_Age，该存储过程有一个输入参数“学号”，另外，还有一个输出参数Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量ErrorValue得到。,9.4 查看和修改存储过程,9.4.1 查看存储过程,1使用SQL Server管理控制台查看用户创建的存储过程在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程
11、性”文件夹，单击其中的“存储过程”，在右边的窗口中就会显示出当前数据库中的所有存储过程。,9.4.1 查看存储过程,2使用系统存储过程查看用户创建的存储过程sp_help：用于显示存储过程的参数及其数据类型sp_help objname=name参数name为要查看的存储过程的名称。,9.4.1 查看存储过程,sp_helptext：用于显示存储过程的源代码sp_helptext objname=name参数name为要查看的存储过程的名称。,9.4.1 查看存储过程,sp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object参数object为要
12、查看依赖关系的存储过程的名称。,9.4.1 查看存储过程,sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_proceduressp_name=name,sp_owner=owner,sp_qualifier=qualifier其中，sp_name=name 用于指定返回目录信息的过程名；sp_owner=owner 用于指定过程所有者的名称；qualifier=qualifier 用于指定过程限定符的名称。,9.4.1 查看存储过程,例9-4-1：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。例9-4-2：使用系统存储过程查看St
13、uScoreInfo存储过程的相关的数据库对象。,9.4.2 修改存储过程,1使用SQL Server管理控制台修改存储过程使用SQL Server管理控制台可以很方便地修改存储过程的定义。在SQL Server管理控制台中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选择“修改”选项，则会出现与创建存储过程时类似的窗口。在该窗口中，可以直接修改定义该存储过程的Transact-SQL语句。,9.4.2 修改存储过程,2使用Transact-SQL语句修改存储过程使用ALTER PROCEDURE语句可以更改存储过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：A
14、LTER PROC|PROCEDURE schema_name.procedure_name parameter type_schema_name.data_type VARYING=default OUT PUT,.n WITH ENCRYPTIONAS sql_statement.n,9.4.2 修改存储过程,修改存储过程时，应该注意以下几点：如果原来的过程定义是使用WITH ENCRYPTION创建的，那么只有在ALTER PROCEDURE中也包含这个选项时，这个选项才有效。每次只能修改一个存储过程。用ALTER PROCEDURE更改的存储过程的权限保持不变。,9.4.2 修改存储过
15、程,例9-4-3：修改前面创建的Stu_Info存储过程，使之完成以下功能：根据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称和考试分数。,9.5 重命名和删除存储过程,9.5.1 重命名存储过程,1使用SQL Server管理控制台修改存储过程名称通过SQL Server管理控制台可以修改存储过程的名称。方法是：在SQL Server管理控制台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。,9.5.1 重命名存储过程,2使用系统存储
16、过程修改存储过程名称修改存储过程的名称也可以使用系统存储过程sp_rename，其语法形式如下：sp_rename 原存储过程名称，新存储过程名称例9-5-1：使用系统存储过程将Stu_Info存储过程的名称修改为Stu_Info_SCORE。,9.5.2 删除存储过程,1使用SQL Server管理控制台删除存储过程在SQL Server管理控制台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，会弹出“删除对象”对话框。在该对话框中，单击“确定”按钮，即可完成删除操作。,9.5.2 删除存储过程,2使用Transact-SQL语句删除存储过程删除存储过程也可以使用Transac
17、t-SQL语言中的DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：DROP PROC|PROCEDURE schema_name.procedure,.n 例9-5-2：使用DROP命令删除StuInfo和StuScoreInfo两个存储过程。,9.6 思考与练习,1使用存储过程的主要优点有哪些？2存储过程分哪两类，各有什么特点？3创建一个无参存储过程，返回05541班的学生信息。,9.6 思考与练习,4创建一个带参数的存储过程，输入参数为课程名称，查询有哪些班级的哪些学生参加了这门课程的考试及学生的考试成绩，存储过程返回0，则表示程序执行成功，返回其他整数，表示程序在执行时出现错误。5举例说明如何查看存储过程信息。6举例说明如何删除存储过程。,
