《SQL Server数据库管理、开发与实践 教学课件 ppt 作者 郑诚 第9章 存储过程和触发器》由会员分享，可在线阅读，更多相关《SQL Server数据库管理、开发与实践 教学课件 ppt 作者 郑诚 第9章 存储过程和触发器（67页珍藏版）》请在金锄头文库上搜索。
1、本章要求,第9章 存储过程和触发器,存储过程的概念 存储过程的优点 创建、执行、查看存储过程 触发器的概念及优点 创建、查看、修改触发器 禁用和启动触发器,主要内容,第9章 存储过程和触发器,1.存储过程概述 2.存储过程的创建与管理 3.触发器概述 4.触发器的创建与管理 5.综合实例使用触发器向MingRiBook数据库的user表中添加数据,9.1.1 存储过程的概念 9.1.2 存储过程的优点,9.1 存储过程概述,存储过程与其他编程语言中的过程类似，它可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的编程语句；向调用过程或
2、批处理返回状态值，以指明成功或失败（以及失败的原因）。 SQL Server提供了3种类型的存储过程。各类型存储过程如下： 系统存储过程：用来管理SQL Server和显示有关数据库和用户的信息的存储过程。 自定义存储过程：用户在SQL Server中通过采用SQL语句创建存储过程。 扩展存储过程：通过编程语言（例如，C）创建外部例程，并将这个例程在SQL Server中作为存储过程使用。,9.1.1 存储过程的概念,存储过程的优点表现在以下几个方面： （1）存储过程可以嵌套使用，支持代码重用。 （2）存储过程可以接受与使用参数动态执行其中的SQL语句。 （3）存储过程比一般的SQL语句执行速
3、度快。存储过程在创建时已经被编译，每次执行时不需要重新编译。而SQL语句每次执行都需要编译。 （4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。 （5）存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。 （6）存储过程可以减少网络通信流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。 （7）存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应
4、用程序不受SQL Injection攻击。 说明：SQL Injection是一种攻击方法，它可以将恶意代码插入到以后将传递给SQL Server供分析和执行的字符串中。任何构成SQL语句的过程都应进行注入漏洞检查，因为SQL Server将执行其接收到的所有语法有效的查询。,9.1.2 存储过程的优点,9.2.1 使用向导创建存储过程 9.2.2 使用CREATE PROC语句创建存储过程 9.2.3 执行存储过程 9.2.4 查看存储过程 9.2.5 修改存储过程 9.2.6 重命名存储过程 9.2.7 删除存储过程,9.2 存储过程的创建与管理,在SQL Server 2008中，使用向
5、导创建存储过程的步骤如下： （1）启动SQL Server Management Studio，并连接到SQL Server 2008中的数据库。 （2）在“对象资源管理器”中选择指定的服务器和数据库，展开数据库的“可编辑性”节点，鼠标右键单击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”命令，如图9-1所示。 图9-1 选择“新建存储过程”选项,9.2.1 使用向导创建存储过程,（3）在弹出的“连接到数据库引擎”窗口中，单击“连接”按钮，便出现创建存储过程的窗口，如图9-2所示。 图9-2 创建存储过程窗口 在存储过程窗口的文本框中，可以看到系统自动给出了创建存储过程的格式模板语句，工
6、具模板格式进行修改来创建新的存储过程,【例9-1】 创建一个名称为Proc_Stu的存储过程，要求完成以下功能：在Student表中查询男生的Sno，Sex，Sage这几个字段的内容。 具体的操作步骤如下： （1）在创建存储过程的窗口中单击“查询”菜单，选择“指定模板参数的值”，弹出“指定模板参数的值”对话框，如图9-3所示。 图9-3 指定模板参数的值,【例9-1】指定模板参数的值,（2）在“指定模板参数的值”对话框中将“Procedure_Name”参数对应的名称修改为“Proc_Stu”，单击“确定”按钮，关闭此对话框。 （3）在创建存储过程的窗口中，将对应的SELECT语句修改为以下的
7、语句： SELECT Sno,Sname,Sex,Sage FROM Student WHERE Sex=男,在SQL语言中，可以使用CREATE PROCEDURE语句创建存储过程，其语法格式如下。 CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n AS sql_statement CREATE PROC语句的参数及说明如表9-1所示。 表9-1 CREATE PROC语句的参数及说明,9.2.2 使用CREATE PROC语句创建存储过程,【例9-2】 使用C
8、REATE PROCEDURE语句创建一个存储过程，用来根据学生编号查询学生信息，SQL语句如下。 Create Procedure Proc_Stu Proc_Sno int as select * from Student where Sno = Proc_Sno,【例9-2】使用CREATE PROCEDURE语句创建一个存储过程,存储过程创建完成后，可以通过EXECUTE执行，可简写为EXEC。 1EXECUTE EXECUTE用来执行 Transact-SQL中的命令字符串、字符串或执行下列模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。 EXECUTE的
9、语法如下： EXEC | EXECUTE return_status = module_name ;number | module_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE ;,9.2.3 执行存储过程,EXECUTE语句的参数及说明如表9-2所示。 表9-2 EXECUTE语句的参数及说明 注意：后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。,2使用EXECUTE执行存储过程 【例9-3】 使
10、用EXECUTE执行存储过程Proc_Stu，SQL语句如下。 exec Proc_Stu 使用EXECUTE执行存储过程的步骤如下： （1）打开SQL Server Management Studio，并连接到SQL Server2008中的数据库。 （2）单击工具栏中“ ”按钮，新建查询编辑器。并输入如下SQL语句代码。 exec Proc_Stu （3）单击“ ”按钮，就可以执行上述SQL语句代码，即可完成执行“Proc_Stu”存储过程。执行结果如图9-4所示。 图9-4 执行存储过程的结果,【例9-3】执行存储过程的结果,许多系统存储过程、系统函数和目录视图都提供有关存储过程的信息。
11、您可以使用这些系统存储过程来查看存储过程的定义，即用于创建存储过程的 Transact-SQL 语句。 可以通过下面3种系统存储过程和目录视图查看存储过程： 1使用sys.sql_modules查看存储过程的定义 sys.sql_modules为系统视图，通过该视图可以查看数据库中的存储过程。查看存储过程的操作方法如下： （1）单击工具栏中“ ”按钮，新建查询编辑器。 （2）在新建查询编辑器输入如下代码： select * from sys.sql_modules （3）单击“ ”按钮，执行该查询命令。查询结果如图9-5所示。 图9-5 使用sys.sql_modules视图查询的存储过程,9
12、.2.4 查看存储过程,2使用 OBJECT_DEFINITION 查看存储过程的定义 返回指定对象定义的 Transact-SQL 源文本。语法如下： OBJECT_DEFINITION ( object_id ) 参数说明： object_id：要使用的对象的 ID。object_id 的数据类型为 int，并假定表示当前数据库上下文中的对象。 【例9-4】 使用OBJECT_DEFINITION查看ID为“1701581100”存储过程的代码。SQL语句如下。 select OBJECT_DEFINITION(1701581100) 3使用 sp_helptext 查看存储过程的定义 显
13、示用户定义规则的定义、默认值、未加密的 Transact-SQL 存储过程、用户定义 Transact-SQL 函数、触发器、计算列、CHECK 约束、视图或系统对象（如系统存储过程）。 语法如下： sp_helptext objname = name , columnname = computed_column_name 参数说明： objname = name：架构范围内的用户定义对象的限定名称和非限定名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。name 的数据类型为 nvarchar(776)
14、，无默认值。 columnname = computed_column_name：要显示其定义信息的计算列的名称。必须将包含列的表指定为 name。column_name 的数据类型为 sysname，无默认值。,【例9-4】存储过程的代码,【例9-5】 通过sp_helptext系统存储过程查看名为“Proc_Stu”存储过程的代码，SQL语句如下。 sp_helptext Proc_Stu 操作步骤如下： （1）打开SQL Server Management Studio，并连接到SQL Server2008中的数据库。 （2）选择存储过程所在的数据库，例如“db_2008”数据库。 （3
15、）单击工具栏中“ ”按钮，新建查询编辑器。并输入如下SQL语句代码。 sp_helptext Proc_Stu （4）单击“ ”按钮，就可以执行上述SQL语句代码。执行结果如图9-6所示。 图9-6 查看“Proc_Stu”存储过程的结果,【例9-5】查看“Proc_Stu”存储过程的结果,修改存储过程可以改变存储过程当中的参数或者语句，可以通过SQL语句中的ALTER PROCEDURE语句实现。虽然删除并重新创建该存储过程，也可以达到修改存储过程的目的，但是将丢失与该存储过程关联的所有权限。 1ALTER PROCEDURE语句 ALTER PROCEDURE语句用来修改通过执行CREATE PROCEDURE语句创建的过程。该语句修改存储过程时不会更改权限，也不影响相关的存储过程或触发器。 ALTER PROCEDURE语句的语法如下： ALTER PROC | PROCEDURE schema_name. procedure_name ; number parameter type_schema_name. data_type VARYING = default OUT PUT ,.n WITH ,.n FOR REPLICATION AS .n | := ENCRYPTION RECOMPIL
