数据库基础与项目实训教程——基于SQL Server第8章.ppt

1、第8章 存储过程和触发器,知识导航,学习目标,了解： 存储过程的概念及优点 触发器的概念和类型 掌握： 存储过程的创建、调用和管理方法 触发器的创建和使用方法,存储过程和触发器,8.1 存储过程概述 8.2 存储过程的操作 8.3 触发器概述 8.4 触发器的操作,8.1 存储过程概述,任务描述：存储过程是T-SQL语句和控制流语句组成的集合，是最高级别的应用程序。了解存储过程的基本概念，对于后续相关内容的学习，非常重要。 任务目标：了解存储过程的基本概念及优点。,8.1 存储过程概述,8.1.1 存储过程的定义与类型 存储过程主要分为三种类型： （1）系统存储过程（System Stored

2、 Procedure） 由数据库系统自身创建，其名称附有前缀sp_，存储在master数 据库中，用于管理SQL Server和显示数据库和用户信息 （2）扩展存储过程（Extended Stored Procedure） 属于动态链接库，其名称以xp_为前缀，存储过程只能添加到 master数据库中 （3）用户自定义存储过程 由用户根据对某一个特定功能的需要，在用户数据库中自行创 建并执行用户指定的任务。,8.1 存储过程概述,8.1.2 存储过程的优点 （1）模块化编程 存储过程的创建是为了完成一个特定的功能，由此创建的程序模块存储在数据库中，可被多个用户重复调用和共享，并可独立于应用程序

3、代码。存储过程的使用，大大增加了代码的重用性和共享性，提高了应用程序开发的质量和效率。 （2）提高执行效率，加快运行速度 存储过程在创建时已经在服务器上进行了编译并加以优化，在存储过程第一次被执行时，服务器会将其保留在高速缓存中，当以后再次调用该存储过程时，不再需要经历装载编译等过程，可以立即执行，大大提升了运行速度，改善了系统性能。 （3）降低网络流量 存储过程代码直接存储于数据库中，当对其进行调用时，只需要一条调用语句，不会产生大 量T-SQL语句的代码流量，从而降低网络流量。 （4）提供安全机制 通过向用户授予对存储过程的访问权限，允许用户单独执行存储过程，而不给予其直接访问 存储过程所

4、包含的数据库对象（表、试图）的权限，增强了安全性控制。,8.2 存储过程的操作,任务描述：在广播电视大学“RtvuStu”数据库中创建存储过程，实现对存储过程的调用和管理。 任务目标：掌握存储过程的创建方法，能够对存储过程进行管理和调用。,8.2 存储过程的操作,8.2.1 存储过程的创建 SQL Server中，可使用三种方法创建存储过程： （1）使用企业管理器、 （2）使用向导和使用T-SQL语句中的Create （3）Procedure语句。 在创建存储过程时，不可以使用Create Default、Create Rule、 Create Trigger、Create View语句。,8

5、.2 存储过程的操作,1. 使用企业管理器创建存储过程 1）在桌面单击“开始”“程序”“Microsoft SQL Server”“企业管理器”命令，打开企业管理器，选择服务器。 2）展开需要创建存储过程所在的数据库，单击“存储过程”，如图8-1所示，图右边框列出的是该数据库中的存储过程。,图8-1 企业管理器中的存储过程,8.2 存储过程的操作,使用企业管理器创建存储过程 3）右键单击“存储过程”，或在右边框中任意存储过程上单击右键，在弹出的快捷菜单中选择“新建存储过程”命令，出现“存储过程属性新建存储过程”对话框，如图8-2所示。 4）将图8-2对话框中的“OWNER.PROCEDURE

6、NAME”用需要创建的存储过程的名称代替，再将需要实现功能的SQL语句代码依次输入，编辑存储过程，如图8-3所示。 5）输入完成后，单击“检查语法”按钮，检查输入的SQL语句代码是否正确。 6）检查通过后，单击“确定”按钮，保存存储过程，完成创建,图8-2 新建存储过程 图8-3 新建存储过程中的执行代码,7）存储过程创建完成后，用右键单击该存储过程，在弹出的快捷菜单中选择“所有任务”“管理权限”命令，在弹出的对象属性中，对其使用权限进行设置，如图8-4所示。,图8-4 存储过程的对象属性,8.2 存储过程的操作,2. 使用向导创建存储过程 使用SQL Server提供的向导来创建存储过程，可

7、以根据系统提示 完成操作，步骤如下： 1）打开企业管理器，展开相应的服务器和数据库“RtvuStu”。 2）在菜单栏中选择“工具”“向导”命令，如图8-5所示，弹出“选择向导”对话框，如图8-6所示。,图8-5 向导菜单 图8-6 “选择向导”对话框,8.2 存储过程的操作,2. 使用向导创建存储过程 3）在“选择向导”对话框中，单击“数据库”选项，在展开的列表中，选择“创建存储过程向导”选项，单击“确定”按钮，弹出如图8-7所示的“创建存储过程向导之欢迎”对话框。 4）在“创建存储过程向导之欢迎”对话框中单击“下一步”按钮，弹出“创建存储过程向导之选择数据库”对话框，如图8-8所示。,图8-

8、7 “创建存储过程向导”对话框 图8-8 “选择数据库”对话框,8.2 存储过程的操作,5）在“创建存储过程向导之选择数据库”对话框中，选择本次创建的存储过程所在的数据库，单击“下一步”按钮，弹出“创建存储过程向导之选择存储过程”对话框，如图8-9所示，该对话框中显示了前面选择的数据库中所有的用户表名称，并有三个包含复选框的列，分别代表三种操作：插入、删除和更新。用户可以根据需要设置一个或多个操作。 6）完成操作后，单击“下一步”按钮，弹出“正在完成创建存储过程向导”对话框，单击“完成”按钮，存储过程创建成功，如图8-10所示。,图8-9 “选择存储过程”窗口 图8-10 “正在完成创建存储过

9、程”对话框,8.2 存储过程的操作,7）还可以通过单击图8-10中的“编辑”按钮，进入“编辑存储过程属性”对话框，如图8-11所示。在该窗口上对存储过程进行重新命名，在选择字段中选中的列名称将会被此存储过程使用。 8）要编辑存储过程，可以通过单击“编辑存储过程属性”对话框下方的“编辑SQL”按钮，打开“编辑存储过程SQL”窗口，如图8-12所示，在该窗口内检查存储过程的T-SQL程序代码，在编辑区域内直接编辑代码，完成后，单击“分析”按钮检查语法错误。单击“确定”按钮返回如图8-10所示的“正在完成创建存储过程”对话框。单击“完成”按钮，存储过程创建成功。,图8-11 “编辑存储过程属性”对话

10、框 图8-12 “编辑存储过程SQL”窗口,8.2 存储过程的操作,2. 3使用Create Procedure 语句创建存储过程 使用T-SQL语句中的Create Procedure命令可以直接创建存储 过程。但在使用该命令创建存储过程前，应注意以下几点： 1）Create Procedure语句不能和其他T-SQL语句在同一个批处理中。 2）创建存储过程的权限默认属于数据库拥有者，该拥有者可以将此权限授予其他用户。 3）存储过程是数据库对象，其名称必须遵守标识符命名规则。 4）只能在当前数据库中创建存储过程。,8.2 存储过程的操作,用Create Procedure创建存储过程的语法形

11、式如下： Create Procedure procedure_name Parameter Data_type Varying=DefaultOutput ,.n With Recompile|Encryption|Recompile,Encryption For Replication As Sql_statement .n ,8.2 存储过程的操作,参数说明如下。 1）Procedure_name：指定要创建的存储过程的名称。 2）Parameter：过程中的参数。在 Create Procedure 语句中可以声明一个或多个参数。 3）Data_type：用于指定参数的数据类型。 4）

12、Varying：用于指定作为输出Output参数支持的结果集，仅用于游标参数。 5）Default：用于指定参数的默认值。一旦定义了默认值，即使不给出参数值，该存储过程依然可以被调用。 6）Output：表明该参数是一个返回参数。 7）Recompile：表示该存储过程在运行时将重新编译。 8）Sql_statement：是存储过程中要包含的任意数目和类型的T-SQL语句。,8.2 存储过程的操作,【例8-1】 创建一个存储过程，命名为getCouseName，该存储过程可获取所有“Course”表中的课程名。 在查询分析器中输入如下语句： Use RtvuStu Go Create proc

13、 getCouseName As Select Cname from Course GO,8.2 存储过程的操作,8.2.2 存储过程的调用 在SQL Server数据中，通过使用T-SQL语句中的Execute命令来执行 存储过程，但是当存储过程是批处理的第一条语句时，那么不使用 Execute关键字也可以执行存储过程。 执行存储过程的语法如下： Execute Procedure_name parameter = value | variableOutput I Default ,.n,8.2 存储过程的操作,参数说明如下。 1）Procedure_name：用于指定要执行的存储过程。 2

14、）parameter：是在创建过程时定义的过程参数。调用者向存储过程所传递的参数值由value参数或variable变量提供。 3）variableOutput用于保存存储过程中传递的参数值或返回参数的变量。若使用Output关键字，则表示该参数是一个输出参数，目的是在调用批处理或过程的其他语句中使用其返回值，参数的值必须作为一个变量被返回（即parameter=variable）。 4）Default：使用存储过程提供的默认值。 在执行存储过程时，尽量少用可选参数，否则会影响系统的性能。可以在调用存储过程时传递参数给它，将参数名称的前缀用标示，如 auName。,8.2 存储过程的操作,【例

15、8-2】 执行存储过程getCouseName。 在查询分析器中输入如下语句： Use RtvuStu Go Exec getCouseName Go,【例8-3】 创建存储过程getDetailByName，通过输入参数学生姓名，选出该学生的基本信息，对不存在此学生姓名的输入值，必须作检测，打印信息“不存在此学生”。 在查询分析器中创建存储过程语句如下： Use RtvuStu Go Create proc getDetailByName name nvarchar(10) As If exists Select * From Student Where 姓名= name Begin Sel

16、ect * From Student Where 姓名= name End Else Begin Print 不存在此学生！ End Go 执行该存储过程： Exec getDetailByName name=章山,8.2 存储过程的操作,8.2.3 存储过程的管理 1查看存储过程 通过企业管理器查看存储过程步骤如下： 1）打开企业管理器，展开相应的服务器和数据库“RtvuStu”。 2）选择“存储过程”，在窗口右侧显示当前数据库中所有的存储过程。 3）选择要查看的存储过程，双击或通过右键单击后，选择“属性”选项，弹出“存储过程属性”对话框，在该对话框中查看当前存储过程的T-SQL源代码，如图

17、8-13所示。,8.2 存储过程的操作,图8-13 “存储过程属性”对话框,还可以在查询分析器中使用sp_help、sp_helptext、sp_depends等 系统存储过程来查看当前数据库中的存储过程对象。这些系统存储 过程的用法如下。 1）sp_help：用来报告有关数据库对象（sysobjects表中列出的任何对象）、用户定义数据类型或SQL Server中所提供的数据类型的信息，其语法格式如下： sp_help objname = name 2）sp_helptext：显示用户定义规则的定义、默认值、未加密的T-SQL 存储过程、用户定义 T-SQL函数、视图或系统存储过程，其语法格

18、式如下： sp_helptext objname = name , columnname = computed_column_name 3）sp_depends：用于显示有关数据库对象依赖关系的信息，其语法格式如下： sp_depends objname = object,图8-14 使用系统存储过程查看存储过程,8.2 存储过程的操作,2修改存储过程 可以使用企业管理器修改存储过程，双击要修改的存储过程，在“存储 过程属性”对话框的文本框内修改定义存储过程的T-SQL语句，单击“确定 ”按钮完成修改存储过程。 还可以使用Alter Procedure语句修改存储过程，该语句可以增加或删 除一

19、些存储过程的选项，如是否加密、是否在每次执行时都进行编译等，其 语法格式如下： Alter Procedure procedure_name parameter data_type Varying =default Output ,n With recompile | encryption | recompile, encryption For replication as sql_statement n,8.2 存储过程的操作,【例8-4】修改getCouseName，获取所有“Course”表中的课程名和学分。 在查询分析器中输入如下语句： Use RtvuStu Go Alter pro

20、cedure getCouseName As Select Cname, Credit From Course Go 此外，要修改存储过程的名称，除了可以通过企业管理器直接修改外，还可 以使用系统存储过程sp_rename进行修改，其语法格式如下： sp_rename stored procedure object_name , stored procedure new_name 其中的stored procedure object_name表示存储过程原来的旧名称， stored procedure new_name表示存储过程的新名称。,8.2 存储过程的操作,【例8-5】 将存储过程ge

21、tCouseName重新命名为getCourseInfo。 在查询分析器中输入如下语句： Use RtvuStu Go sp_rename getCouseName，getCourseInfo Go,8.2 存储过程的操作,图8-15 “除去对象”对话框,8.2 存储过程的操作,还可以用Drop Procedure语句删除不再需要使用的存储过程。当某个已被删 除的存储过程再次被其他存储过程调用时，数据库将会发出错误信息。 删除存储过程语句的语法如下： Drop Procedure Procedure_name 【例8-6】 删除存储过程getCourseInfo。 在查询分析器中输入如下语句：

22、 Use RtvuStu Go Drop Procedure getCourseInfo Go,8.3 触发器概述,任务描述：触发器在“RtvuStu”数据库的创建和运用，有助于强制数据完整性，了解触发器的概念和类型，为后续触发器的创建、调用和管理的学习做准备。 任务目标：了解触发器的基本概念及类型。,8.3 触发器概述,8.3.1 触发器的概念及类型 触发器（Trigger） 是SQL Server数据库中一种特殊的存储过程，它与表紧密相连， 基于表而建立，不能由用户直接调用，而是在对表进行插入、更改 或删除记录操作时，被自动激活。 触发器的主要作用是使多个不同用户能够在保持数据完整性和一

23、致性的良好环境下进行数据操作。触发器可以对数据库进行级联修改， 可以完成比Check约束更为复杂的限制。 触发器的主要类型有：Insert（插入）、Update（更新）、Delete（删除） 按触发器被激活的时机来分，又分为Instead of（取代）和After两种类型。,8.3 触发器概述,8.3.2 触发器的工作原理 在激活触发器时，SQL Server会为触发器创建两个临时表：Inserted 表和Deleted表，这两个表与触发器一起储存在内存中，用户通过这两个表 来比较数据修改前后的状态。要注意的是，Inserted表和Deleted表只限于 在触发器中使用，一旦触发器完成后，就无

24、法再次使用。 （1）Inserted表 Inserted表用于存放执行Insert或Update语句时，向表中插入的所有 行。当一个记录插入表中时，与该表关联的插入触发器同时创建一个 Inserted表，该表映射了与触发器相连接的表的列结构。对于插入到表的 每一行数据，相应的Inserted表中也包含该行数据。,8.3 触发器概述,（2）Deleted表 Deleted表用于存放由于执行Delete或Update语句而要从表中删 除的所有行。该表也同样映射了与触发器相连接的表的列结构，从 数据库表中删除的每一行数据都被移动到该删除触发器创建的 Deleted表中，很显然，这两个表不会有共同的行

25、。 Update操作可以看作是删除操作和插入操作的组合，即先执行 一个Delete操作，再执行一个Insert操作，被删除的行先被移动到 Deleted表，新行在插入数据库表的同时，复制一份到该表关联的插 入触发器的Inserted表中。,8.4 触发器的操作,任务描述：为“RtvuStu”数据库创建触发器，当对 该数据库的相关表作插入、修改和删除等操作时， 用触发器来保留表之间已定义的关系，保持数据的 完整性和一致性。 任务目标：掌握触发器的创建、修改和使用方法。,8.4 触发器的操作,8.4.1 触发器的创建 在创建触发器之前，需要考虑以下几点： 1）Create Trigger语句必须是

26、批处理中的第一条语句。 2）创建触发器的权限默认是属于表的所有者的，不能再授权给其他用户。 3）触发器是数据库中的对象，它的名称必须符合数据库对象的命名规则。 4）触发器只能在当前数据库中创建，但它可以引用其他数据库中的对象。 5）不能在临时表或系统表上创建触发器，但触发器可以引用临时表。 6）Truncate Table语句虽然和没有Where子句的Delete语句类似，都用于删除表中的行，但Truncate Table语句不会像Delete语句那样触发Delete触发器，因为它的操作不被记入事务日志。 7）Writetext语句不会触发Insert或Update触发器。,8.4 触发器的操

27、作,1使用企业管理器创建触发器 在企业管理器中创建触发器的操作步骤如下： 1）打开企业管理器，展开相应的服务器和数据库“RtvuStu”。 2）选择需要创建的触发器所在的表，右键单击鼠标，在弹出的快捷菜单中选择“所有任务”“管理触发器”命令，弹出“触发器属性”对话框，如图8-16所示。 3）在“名称”栏中选择“新建”，在其下方的文本框中输入创建触发器的语句。 4）单击“检查语法”按钮，对触发器语句的语法进行检查。 5）单击“确定”按钮，触发器创建完成。,8.4 触发器的操作,图8-16 “触发器属性”对话框,8.4 触发器的操作,2使用T-SQL语句创建触发器 创建触发器的T-SQL语句语法格

28、式如下： Create Trigger trigger_name On table_name|view_name With Encryption For|After|Instead Of Insert,Update,Delete As sql_statement n,8.4 触发器的操作,参数说明如下。 1）trigger_name：触发器的名称，触发器的名称必须符合数据库中对象的命名规则。 2）table_name|view_name：指定激活触发器的表或视图。 3）With Encryption：SQL Server将触发器的定义文本保存在系统分类表syscomments中，选用此项要求对

29、syscomments中包含的Create Trigger语句的相应文本进行加密，防止触发器文本被复制。 4）After：指定只有当引起触发器执行的所有定义的操作都成功执行后，才执行本触发器。 5）Instead of：指定执行本触发器而不是执行引起触发的SQL语句。 6）Insert，Update，Delete：指定对表的哪些数据更新动作将激活本触发器，必须要指定至少一个选项。如果指定不止一个数据更新动作可激活本触发器，则须在这些动作选项之间用逗号隔开。,8.4 触发器的操作,【例8-7】 在数据库RtvuStu中创建一个触发器student_alter，当用户要对表“Student”进行数

30、据更新时（如添加或修改学生信息），该触发器在客户端显示一条消息。 在查询分析器中输入如下语句： Use RtvuStu Go Create trigger student_alter On Student For Insert,Update As Print 对学生基本信息表进行了更新。 Go,8.4 触发器的操作,8.4.2 触发器的使用 触发器和条件约束相同，可用来维持数据的完整性和规则，但触发器不能取 代条件约束。用户可以利用触发器执行比Check条件约束更为复杂的数据检测。 1使用Insert触发器 当Insert触发器被激发时，新的数据行被加到触发器表和Inserted表中， Ins

31、erted表是一个逻辑表，它包含了已经被插入的数据行的一个副本。触发器可 以检查Inserted表，确定是否执行触发器动作和如何执行触发器动作。,【例8-8】 建立一个Insert触发器，当在学生表中插入一条新记录时，触发器显示相关信息。 在查询分析器中输入如下语句： Use RtvuStu Go Create trigger Student_add on Student For Insert As Select * From Inserted Print 插入了一条新数据。 Go 上面的语句创建了一个触发器，当执行如下语句时，该触发器被触发。 Use RtvuStu Go Insert in

32、to Student values(20091330130088,张三,女,1978-6-13, 中文系) Go 一条信息被插入，将输出信息：“插入了一条新数据”。,2使用Update触发器 Update触发器和Insert触发器的工作过程类似，事实上，更新一条记录就相当于插入 一条新记录，同时删除原来的旧记录。 【例8-9】 建立一个Update触发器，当更新“Student”表中的数据时，触发器会显示相关提示。 在查询分析器中输入如下语句： Use RtvuStu Go Create trigger Student_Update on Student For Update as Selec

33、t * from inserted Print 成功修改了一条数据。 Go 上面的语句创建了一个触发器，当执行如下语句时，该触发器被触发。 Use RtvuStu Go Update Student set depart=国学系 Where S_ID= 20091330130088 Go 触发器被触发，输出如下信息：“成功修改了一条数据。”,8.4 触发器的操作,3使用Delete触发器 当Delete触发器被激活时，从影响表删除的行被放置到一个特殊的Deleted 表中。Deleted表是一个逻辑表，它包含了已经被删除的行的副本。 【例8-10】 建立一个触发器，当从学生信息表中删除某一行学

34、生信息时，在其对应的成绩表中删除相关行。 Use RtvuStu Go Create trigger student_delete on Student For Delete As Print 使用触发器删除成绩表中的相关行开始 Delete from Results Where Results.S_ID= deleted.S_ID Print 使用触发器删除成绩表中的相关行结束 Select * From Deleted GO,8.4 触发器的操作,执行如下语句： Use RtvuStu Go Select * From Student Select S_ID,C_ID,Score From

35、 Results Delete From Student Where S_ID= 20022330130088 Go 结果当学号为“20022330130088”的学生信息被从“Student”表中删除时，Delete触发器被激活，成绩表“Results”中的该学员的成绩信息也被删除。,8.4 触发器的操作,8.4.3 触发器的管理 触发器的管理主要指查看已建立的触发器，并对这些触发器进行修改、删除 等操作。 1查看和修改触发器 可以使用企业管理器查看修改触发器信息，步骤如下： 1）打开企业管理器，展开相应的服务器和数据库“RtvuStu”，选择“表”选项。 2）在右侧表列表中，用右键单击想要查看或修改的触发器所在的表，在弹出的快捷菜单中选择“所有任务”“管理触发器”命令，弹出“触发器属性”对话框，在该对话框可以查看到该表所包含的触发器列表。 3）在触发器列表中选择要查看或修改的触发器，可以在其文本框查看该触发器的源代码，亦可对其代码进行修改。 4）修改完成后，单击“语法检查”按钮，通过后单击“确定”按钮即完成触发器的修改。,8.4 触发器的操作,8.4.3 触发器的管理 还可以使用Alter Trigger命令修改触