存储过程与触发器ppt优秀课件.ppt

《存储过程与触发器ppt优秀课件.ppt》由会员分享，可在线阅读，更多相关《存储过程与触发器ppt优秀课件.ppt（103页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、存储过程与触发器存储过程与触发器ppt1第1页，本讲稿共103页本章要点本章要点n存储过程概述存储过程概述 n创建存储过程创建存储过程 n执行存储过程执行存储过程 n存储过程的参数存储过程的参数 n存储过程的返回值存储过程的返回值 n查看和修改存储过程查看和修改存储过程 n删除存储过程删除存储过程 n触发器概述触发器概述 ninserted和和deleted表表 n事务的概念及应用事务的概念及应用 n创建触发器创建触发器 n修改和重命名触发器修改和重命名触发器 n删除触发器删除触发器 2第2页，本讲稿共103页7.1 存储过程存储过程在创建在创建SQL Server数据库应用程序时，数据库应用

2、程序时，Transact-SQL语言语言是应用程序和是应用程序和SQL Server数据库之间的主要编程接口。可用数据库之间的主要编程接口。可用如下如下两种两种方法存储和执行方法存储和执行Transact-SQL语句。语句。(1)将将Transact-SQL程序保存在本地，创建向程序保存在本地，创建向SQL Server发送命令发送命令并处理结果的应用程序。并处理结果的应用程序。(2)可以将可以将Transact-SQL程序保存在程序保存在SQL Server中，即存储过中，即存储过程，在本地创建执行存储过程及处理结果的应用程序。程，在本地创建执行存储过程及处理结果的应用程序。任何一组任何一组

3、Transact-SQL语句构成的代码块，都可以作为存储语句构成的代码块，都可以作为存储过程保存起来。它在服务器端对数据库中的数据进行处理，并将过程保存起来。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作，减少了网络上的传输量，同时也提高了客户端的工作库的操作，减少了网络上的传输量，同时也提高了客户端的工作效率（因为这些操作都是在服务器端完成的）。效率（因为这些操作都是在服务器端完成的）。3第3页，本讲稿共103页7.1.1 存储过程概述存储过程概述 存储过程是集中存储在存储过程

4、是集中存储在SQL Server中的中的SQL语语句和流程控制语句的预编译集合句和流程控制语句的预编译集合，用以实现某种任，用以实现某种任务（如查询）。这些语句务（如查询）。这些语句在一个名称下存储在一个名称下存储并作为并作为一个单元进行处理。一个单元进行处理。4第4页，本讲稿共103页使用存储过程的优势使用存储过程的优势 n 可以减少客户端代码的重复可以减少客户端代码的重复。只需创建存储过程一次并将。只需创建存储过程一次并将其存储在数据库中，以后即可在客户端程序中多次调用该存储过其存储在数据库中，以后即可在客户端程序中多次调用该存储过程。存储过程可由在数据库编程方面有专长的人员创建，并可独程

5、。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。立于程序源代码而单独修改。n 允许更快地执行允许更快地执行。如果某操作需要大量的。如果某操作需要大量的Transact-SQL代码或需要重复执行，使用存储过程将比在客户端执行代码或需要重复执行，使用存储过程将比在客户端执行Transact-SQL批代码的执行速度要快。存储过程在服务器批代码的执行速度要快。存储过程在服务器端经过预编译，生成查询计划，可以直接执行，而在客户端经过预编译，生成查询计划，可以直接执行，而在客户端每次运行端每次运行Transact-SQL语句时，都要从客户端重复发送，语句时，都要从客户端重复

6、发送，并且在并且在SQL Server每次执行这些语句时，都要对其进行编每次执行这些语句时，都要对其进行编译和优化。因此，存储过程执行速度更快。译和优化。因此，存储过程执行速度更快。5第5页，本讲稿共103页使用存储过程的优势使用存储过程的优势 n减少网络流量减少网络流量。例如，一个需要数百行。例如，一个需要数百行Transact-SQL代码的操作由一条执行过程代码代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数的单独语句就可实现，而不需要在网络中发送数百行代码。百行代码。n可以简化数据库管理可以简化数据库管理。例如，要修改某种查询，。例如，要修改某种查询，如果查询重复存

7、放在客户机上，则要在所有客户如果查询重复存放在客户机上，则要在所有客户机上修改查询，而使用存储过程可以集中修改。机上修改查询，而使用存储过程可以集中修改。n可作为安全机制使用可作为安全机制使用。例如，即使对于没有直接。例如，即使对于没有直接执行存储过程中语句的权限的用户，也可授予他执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。们执行该存储过程的权限。6第6页，本讲稿共103页存储过程的分类存储过程的分类 n系统存储过程系统存储过程:SQL Server内置的存储过程，存内置的存储过程，存储在储在master库中，主要用途是执行库中，主要用途是执行SQL Server的的某些

8、管理功能、显示有关数据库和用户的信息。某些管理功能、显示有关数据库和用户的信息。系统存储过程名以系统存储过程名以SP_开头，可以在任何数据库开头，可以在任何数据库中执行系统存储过程。中执行系统存储过程。n用户存储过程用户存储过程：用户自行创建并存储在用户数据：用户自行创建并存储在用户数据库中的存储过程。库中的存储过程。n临时存储过程临时存储过程：分为局部临时存储过程和全局临：分为局部临时存储过程和全局临时存储过程。时存储过程。7第7页，本讲稿共103页存储过程的分类存储过程的分类 局部临时存储过程名称以局部临时存储过程名称以#开头，存放在开头，存放在tempdb数据库中，只由创建并连接的用户使

9、数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时用，当该用户断开连接时将自动删除局部临时存储过程。存储过程。全局临时存储过程名称以全局临时存储过程名称以#开头，存放在开头，存放在tempdb数据库中，允许所有连接的用户使用，数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。在所有用户断开连接时自动被删除。n远程存储过程：位于远程服务器上的存储过程。远程存储过程：位于远程服务器上的存储过程。n扩展存储过程：利用外部语言（如扩展存储过程：利用外部语言（如C）编写的存）编写的存储过程，以弥补储过程，以弥补SQL Server的不足之处，扩展新的不足之处，扩展新

10、的功能的功能8第8页，本讲稿共103页7.1.2 创建存储过程创建存储过程 n使用使用CREATE PROCEDURE语句语句 n使用企业管理器中的菜单命令使用企业管理器中的菜单命令 9第9页，本讲稿共103页CREATE PROCEDURE语句语句 CREATE PROC EDURE 存储过程名存储过程名;编号编号 参数参数 数据类型数据类型 VARYING =默认值默认值 OUTPUT ,.nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONAS SQL语句语句 .n 10第10页，本讲稿共103页参数说明参数说明 n存储过程名必须符合标识符的命名

11、规则，且对存储过程名必须符合标识符的命名规则，且对于数据库及其所有者必须是惟一的。于数据库及其所有者必须是惟一的。要创建局要创建局部临时存储过程，可以在存储过程名前面加一部临时存储过程，可以在存储过程名前面加一个个#号，要创建全局临时过程，可以在存储过程号，要创建全局临时过程，可以在存储过程名前面加两个名前面加两个#号。完整的名称（包括号。完整的名称（包括#或或#）不能超过）不能超过128个字符。个字符。n编号编号：可选整数，用来对同名的存储过程分组，：可选整数，用来对同名的存储过程分组，以便用一条以便用一条DROP PROCEDURE语句即可将同语句即可将同组的存储过程一起删除。组的存储过程

12、一起删除。11第11页，本讲稿共103页参数说明参数说明 n参数：过程中的参数参数：过程中的参数。在。在CREATE PROCEDURE语句中可以声明一个或多个参数。语句中可以声明一个或多个参数。用户必须在执行存储过程时提供每个所声明参用户必须在执行存储过程时提供每个所声明参数的值（除非定义了该参数的默认值）。参数数的值（除非定义了该参数的默认值）。参数名称前需要使用名称前需要使用符号。参数名称必须符合标识符号。参数名称必须符合标识符的命名规则。每个过程的参数仅用于该过程符的命名规则。每个过程的参数仅用于该过程本身。在其他过程中可以使用相同的参数名称。本身。在其他过程中可以使用相同的参数名称。

13、默认情况下，参数只能代替常量，而不能用于默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。代替表名、列名或其他数据库对象的名称。12第12页，本讲稿共103页参数说明参数说明 n数据类型：参数的数据类型。所有数据类型数据类型：参数的数据类型。所有数据类型（包括（包括text、ntext和和image）均可以用作存储过）均可以用作存储过程的参数。不过，程的参数。不过，cursor数据类型只能用于数据类型只能用于OUTPUT参数。如果指定的数据类型为参数。如果指定的数据类型为cursor，也必须同时指定，也必须同时指定VARYING和和OUTPUT关键关键字。字。nVA

14、RYING：指定作为输出参数支持的结果集：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅（由存储过程动态构造，内容可以变化）。仅适用于游标参数。适用于游标参数。13第13页，本讲稿共103页参数说明参数说明 n默认值：参数的默认值。如果定义了默认值，默认值：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行存储过程。默认不必指定该参数的值即可执行存储过程。默认值必须是常量或值必须是常量或NULL。如果要在存储过程中。如果要在存储过程中对该参数使用对该参数使用LIKE关键字，那么默认值中可以关键字，那么默认值中可以包含通配符（包含通配符（%、_、和和）。）。nOUT

15、PUT：表明参数是返回参数。该选项的值：表明参数是返回参数。该选项的值可以返回给可以返回给EXECUTE。使用。使用OUTPUT参数参数可将信息返回给调用过程。可将信息返回给调用过程。text、ntext和和image参数可用作参数可用作OUTPUT参数。使用参数。使用OUTPUT关键关键字的输出参数可以是游标占位符。字的输出参数可以是游标占位符。14第14页，本讲稿共103页参数说明参数说明 nRECOMPILE：表明不保存该存储过程的执行：表明不保存该存储过程的执行计划，该存储过程将在运行时重新编译。计划，该存储过程将在运行时重新编译。nENCRYPTION：指定：指定SQL Server

16、对对syscomments表中包含本表中包含本CREATE PROCEDURE语句文本的条目进行加密语句文本的条目进行加密nAS：用于指定该存储过程要执行的操作。：用于指定该存储过程要执行的操作。nSQL语句：存储过程中要包含的语句：存储过程中要包含的Transact-SQL语句。语句。15第15页，本讲稿共103页存储过程定义存储过程定义1.无参数存储过程无参数存储过程2.有参数存储过程（输入参数、输出参数有参数存储过程（输入参数、输出参数output）16第16页，本讲稿共103页例题例题1：建立一个查询存储过程，实现查：建立一个查询存储过程，实现查询成绩表中的所有及格成绩。（无参数询成绩

17、表中的所有及格成绩。（无参数存储过程）存储过程）create proc seleproc as select*from 成绩表成绩表where 成绩成绩=60执行存储过程：执行存储过程：exec seleproc结果如图：结果如图：17第17页，本讲稿共103页例例2：创建一个向成绩表添加记录的存储：创建一个向成绩表添加记录的存储过程。（有参数存储过程）过程。（有参数存储过程）create proc insertproc sno char(10),cno char(3),grade floatas insert into 成绩表成绩表 values(sno,cno,grade)执行存储过程：执

18、行存储过程：exec insertproc 0009,03,9818第18页，本讲稿共103页例例例例3 3：创建一个存储过程，要求根据输入的课程号统：创建一个存储过程，要求根据输入的课程号统：创建一个存储过程，要求根据输入的课程号统：创建一个存储过程，要求根据输入的课程号统计该课程平均分。计该课程平均分。计该课程平均分。计该课程平均分。create proc cnoavgscore cno char(2),avgscore float outputas declare cnon char(2)set cnon=select cnon=课程号课程号from 成绩表成绩表 where 课程号课程

19、号=cno if cnon begin select avgscore=avg(成绩成绩)from 成绩表成绩表 where 课程号课程号=cno end else print 此课程号不存在此课程号不存在 go19第19页，本讲稿共103页运行该存储过程：运行该存储过程：declare i floatexec cnoavgscore 02,i outputprint i20第20页，本讲稿共103页说明：说明：With encryption 子句的作用是将存储过程的原代子句的作用是将存储过程的原代码加密；码加密；With recompile子句的作用是对该存储过程重编译。子句的作用是对该存储

20、过程重编译。21第21页，本讲稿共103页例例4 n创建存储过程创建存储过程“增加成绩增加成绩”，将表，将表“成绩表成绩表”中所有学生的成绩增加中所有学生的成绩增加10%。USE 学生管理学生管理 GO CREATE PROCEDURE 增加成绩增加成绩 AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩*1.1 GO执行存储过程：执行存储过程：exec 增加成绩增加成绩 22第22页，本讲稿共103页例例5 （细节考虑）（细节考虑）n在创建一个存储过程时，如果已经存在同名的存储过程，在创建一个存储过程时，如果已经存在同名的存储过程，则不允许创建新的存储过程。可以将以上代码改写为：则

21、不允许创建新的存储过程。可以将以上代码改写为：USE 学生管理学生管理 -如果存在名称为如果存在名称为“增加成绩增加成绩”的存储过程，则将其删除的存储过程，则将其删除 IF EXISTS(SELECT name FROM sysobjects WHERE name=增加成绩增加成绩 AND type=P)DROP PROCEDURE 增加成绩增加成绩 GO CREATE PROCEDURE 增加成绩增加成绩 AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩*1.1 GO23第23页，本讲稿共103页使用企业管理器创建存储过程使用企业管理器创建存储过程n在企业管理器中，展开要创建存储

22、过程的数据在企业管理器中，展开要创建存储过程的数据库库,用鼠标右击用鼠标右击“存储过程存储过程”，在弹出菜单中单，在弹出菜单中单击击“新建存储过程新建存储过程”命令，打开新建存储过程命令，打开新建存储过程对话框，如下图：对话框，如下图：24第24页，本讲稿共103页使用企业管理器创建存储过程使用企业管理器创建存储过程 在该对话框的文本框中有以下的默认语句：在该对话框的文本框中有以下的默认语句：CREATE PROCEDURE OWNER.PROCEDURE NAME AS 这是这是CREATE PROCEDURE语句的开始部分，语句的开始部分，存储过程的具体文本需要用户自己输入。例如，存储过程

23、的具体文本需要用户自己输入。例如，将以上默认语句修改成：将以上默认语句修改成：CREATE PROCEDURE 增加成绩增加成绩 AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩*1.1 GO25第25页，本讲稿共103页7.1.3 执行存储过程执行存储过程 EXEC UTE 返回状态返回状态=存储过程名存储过程名|存储过程名变量存储过程名变量 参数名称参数名称=值值|变量变量 OUTPUT|DEFAULT ,.n WITH RECOMPILE 26第26页，本讲稿共103页参数说明参数说明n返回状态：是一个可选的整型变量，保存存储过程返回状态：是一个可选的整型变量，保存存储过程的

24、返回状态。这个变量在用于的返回状态。这个变量在用于EXECUTE语句前，语句前，必须在批处理、存储过程或函数中声明过。必须在批处理、存储过程或函数中声明过。n存储过程名：要调用的存储过程的名称。存储过程名：要调用的存储过程的名称。n存储过程名变量：局部变量名，代表存储过程的存储过程名变量：局部变量名，代表存储过程的名称。名称。n参数名称：存储过程的参数，在参数名称：存储过程的参数，在CREATE PROCEDURE语句中定义。参数名称前必须加上符语句中定义。参数名称前必须加上符号号。在使用格式。在使用格式“参数参数=值值”时，参数名称和常时，参数名称和常量不一定按照量不一定按照CREATE P

25、ROCEDURE语句中定义语句中定义的顺序出现。但是，如果有一个参数使用的顺序出现。但是，如果有一个参数使用“参数参数=值值”格式，则其他所有参数都必须使用这种格式。格式，则其他所有参数都必须使用这种格式。27第27页，本讲稿共103页参数说明参数说明n值：过程中参数的值。如果没有指定参数名称，参值：过程中参数的值。如果没有指定参数名称，参数值必须以数值必须以CREATE PROCEDURE语句中定义的语句中定义的顺序给出。顺序给出。如果在如果在CREATE PROCEDURE语句中定义了默语句中定义了默认值，用户执行该存储过程时可以不必指定对应认值，用户执行该存储过程时可以不必指定对应的参数

26、。如果该存储过程使用了带的参数。如果该存储过程使用了带LIKE关键字关键字的参数名称，则默认值必须是常量，并且可以包的参数名称，则默认值必须是常量，并且可以包含含%、_、及及 通配符。通配符。默认值也可以为默认值也可以为NULL。通常，定义存储过程时。通常，定义存储过程时会指定当参数值为会指定当参数值为NULL时应该执行的操作。时应该执行的操作。n变量：是用来保存参数或者返回参数的变量。变量：是用来保存参数或者返回参数的变量。28第28页，本讲稿共103页参数说明参数说明nOUTPUT：指定存储过程必须返回一个参数。该存：指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字储过程的

27、匹配参数也必须由关键字OUTPUT创建。创建。使用游标变量作参数时使用该关键字。使用游标变量作参数时使用该关键字。nDEFAULT：根据存储过程的定义，提供参数的默：根据存储过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了值，或缺少参数，或指定了DEFAULT关键字时，关键字时，就会出错。就会出错。nWITH RECOMPILE：强制编译新的计划。如果所：强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的

28、计使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。用该选项，因为它消耗较多系统资源。29第29页，本讲稿共103页7.1.4 存储过程的参数存储过程的参数 n为了提高存储过程的灵活性，为了提高存储过程的灵活性，SQL Server 2000支支持在存储过程中使用参数。持在存储过程中使用参数。n存储过程的参数分为输入参数和输出参数两种类存储过程的参数分为输入参数和输出参数两种类型，输入参数用于向存储过程中带入数据，而输型，输入参数用于向存储过程中带入数据，而输出参数则能将存储过程

29、中的数据返回到调用程序。出参数则能将存储过程中的数据返回到调用程序。n在定义存储过程时在定义存储过程时,可以同时指定参数可以同时指定参数,格式如下：格式如下：参数名参数名 数据类型数据类型=默认值默认值 OUTPUT,.nn如果参数后面使用如果参数后面使用OUTPUT关键字，则表明它是关键字，则表明它是输出参数。输出参数。30第30页，本讲稿共103页例例6n创建存储过程创建存储过程add_proc，用于计算两个参数之和，用于计算两个参数之和并将其输出。并将其输出。CREATE PROCEDURE add_proc num1 INT=0,num2 INT=0 AS DECLARE num3 I

30、NT SET num3=num1+num2 PRINT num3n该存储过程定义了两个参数num1和num2，它们都是输入参数,参数类型为INT,默认值为0。31第31页，本讲稿共103页执行例执行例6n不带参数执行：不带参数执行：EXEC add_proc 运行结果为：运行结果为：0n带参数执行：带参数执行：EXEC add_proc 13,25 运行结果为：运行结果为：38 有时需要将存储过程中的计算结果返回到调用程序中，以便进行进一步的处理，此时就需要在存储过程中使用输出参数。32第32页，本讲稿共103页例例7n创建存储过程创建存储过程add_proc1，用于计算两个参数之和，用于计算

31、两个参数之和，并使用输出参数返回结果。并使用输出参数返回结果。CREATE PROCEDURE add_proc1 num1 INT=0,num2 INT=0,num3 INT OUTPUT -num3为输出参数为输出参数 AS SET num3=num1+num233第33页，本讲稿共103页执行例执行例7DECLARE num AS INTEXEC add_proc1 12,23,num OUTPUTPRINT num运行结果为：运行结果为：3834第34页，本讲稿共103页例题例题8：编辑一个可自由运算的存储过程：编辑一个可自由运算的存储过程create proc calculate n

32、um1 as float,num2 float,s char(2),num3 float output as set num3=case s when+then num1+num2 when-then num1-num2 when*then num1*num2 when/then num1/num2 end35第35页，本讲稿共103页运行该存储过程运行该存储过程declare num3 floatexec calculate 2,4,/,num3 outputselect num336第36页，本讲稿共103页7.1.6 查看和修改存储过程查看和修改存储过程 n使用企业管理器查看和修改存储过

33、程使用企业管理器查看和修改存储过程 n使用查询分析器查看和修改存储过程使用查询分析器查看和修改存储过程 n使用企业管理器重命名存储过程使用企业管理器重命名存储过程 n使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程n使用使用sp_rename重命名存储过程重命名存储过程37第37页，本讲稿共103页1.使用企业管理器查看和修改存储过程使用企业管理器查看和修改存储过程 n在企业管理器中展开指定的数据库，选中在企业管理器中展开指定的数据库，选中“存储存储过程过程”项，可以查看指定数据库中定义的所有存项，可以查看指定数据库中定义的所有存储过程。储过程。38第38页，本讲稿共10

34、3页1.使用企业管理器查看和修改存储过程使用企业管理器查看和修改存储过程 n双击指定的存储过程，可以打开存储过程属性对双击指定的存储过程，可以打开存储过程属性对话框，查看存储过程的定义情况。话框，查看存储过程的定义情况。用户可以在存储过用户可以在存储过程属性对话框中修程属性对话框中修改存储过程的代码。改存储过程的代码。39第39页，本讲稿共103页2.使用查询分析器查看和修改存储过程使用查询分析器查看和修改存储过程 n右键单击要编辑的存储过程，在弹出菜单中选择右键单击要编辑的存储过程，在弹出菜单中选择“编辑编辑”，可以在右键的窗格中显示指定存储过，可以在右键的窗格中显示指定存储过程的代码。程的

35、代码。40第40页，本讲稿共103页3.使用企业管理器重命名存储过程使用企业管理器重命名存储过程 n在企业管理器，展开在企业管理器，展开“数据库数据库”文件夹，选择存文件夹，选择存储过程所在的数据库，单击储过程所在的数据库，单击“存储过程存储过程”文件夹。文件夹。在右侧的窗口中，列出了选择数据库的所有存储在右侧的窗口中，列出了选择数据库的所有存储过程。过程。n右击要重命名的存储过程，选择右击要重命名的存储过程，选择“重命名重命名”菜单菜单项，就可以在当前位置上修改存储过程的名字。项，就可以在当前位置上修改存储过程的名字。41第41页，本讲稿共103页4.4.使用使用使用使用ALTER PROC

36、EDUREALTER PROCEDURE语句修改存储过程语句修改存储过程语句修改存储过程语句修改存储过程 ALTER PROC EDURE 存储过程名存储过程名;编号编号 参数名参数名 数据类型数据类型 VARYING =默认值默认值 OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASSQL语句语句 .n 各参数含义与各参数含义与CREATE PROCEDURE语句相同语句相同42第42页，本讲稿共103页例例9 n使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程“增加成绩增加成绩”，对其进行加密处理。，

37、对其进行加密处理。USE 学生管理学生管理 GO ALTER PROCEDURE 增加成绩增加成绩 WITH ENCRYPTION AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩+10指定以上语句后，在企业管理器中查看指定以上语句后，在企业管理器中查看“增加成增加成绩绩”存储过程，将弹出一个对话框，提示用户存存储过程，将弹出一个对话框，提示用户存储过程已经加密，不能够查看。储过程已经加密，不能够查看。43第43页，本讲稿共103页5.使用使用sp_rename重命名存储过程重命名存储过程 n系统存储过程系统存储过程sp_rename的功能是更改当前数据的功能是更改当前数据库中用户

38、创建对象（如表、列或用户定义数据类库中用户创建对象（如表、列或用户定义数据类型）的名称。使用型）的名称。使用sp_rename重命名存储过程的重命名存储过程的语法结构如下：语法结构如下：sp_rename objname=对象名对象名,newname=新对象名新对象名 参数说明：参数说明：objname=对象名对象名：指定存储过程的当前：指定存储过程的当前名称。名称。newname=新对象名新对象名：指定存储过程的新：指定存储过程的新名称。名称。44第44页，本讲稿共103页例例10n将存储过程将存储过程add_proc重命名为重命名为add_proc2，则可以，则可以使用以下命令：使用以下命

39、令：EXEC sp_rename add_proc,add_proc2运行结果为：运行结果为：注意注意:更改对象名的任一部分都可能破坏脚本和存储过程。更改对象名的任一部分都可能破坏脚本和存储过程。object 已重命名为已重命名为 add_proc2。45第45页，本讲稿共103页7.1.7 删除存储过程删除存储过程 n使用企业管理器使用企业管理器 n使用使用DROP PROCEDURE 语句语句46第46页，本讲稿共103页使用企业管理器使用企业管理器 在企业管理器中，右击要删除的存储过程，选在企业管理器中，右击要删除的存储过程，选择择“删除删除”命令，并确认删除。命令，并确认删除。47第4

40、7页，本讲稿共103页使用使用DROP PROCEDURE语句语句 DROP PROCEDURE 存储过程名存储过程名 ,.n 48第48页，本讲稿共103页例例11n删除存储过程删除存储过程add_proc，可以使用以下命令：，可以使用以下命令：DROP PROCEDURE add_proc49第49页，本讲稿共103页例例12 删除一组存储过程删除一组存储过程n设某设某“职工职工”数据库中有一个数据库中有一个“职工工资职工工资”表，该表包含有表，该表包含有“基基本工资本工资”、“奖金奖金”和和“实发工资实发工资”等列。创建一组存储过程，等列。创建一组存储过程，求求“职工工资职工工资”表的平

41、均基本工资、平均奖金和平均实发工资。表的平均基本工资、平均奖金和平均实发工资。USE 职工职工 GO CREATE PROC AveSalary;1 AS SELECT AVG(基本工资基本工资)FROM 职工工资职工工资 GO CREATE PROC AveSalary;2 AS SELECT AVG(奖金奖金)FROM 职工工资职工工资 GO CREATE PROC AveSalary;3 AS SELECT AVG(实发工资实发工资)FROM 职工工资职工工资 GO50第50页，本讲稿共103页例例13删除一组存储过程删除一组存储过程使用以下语句执行以上存储过程。使用以下语句执行以上存储

42、过程。EXEC AveSalary;1 EXEC AveSalary;2 EXEC AveSalary;3要删除以上创建的三个存储过程，可以使用语句：要删除以上创建的三个存储过程，可以使用语句：DROP PROCEDURE AveSalary注意，不能写成：注意，不能写成：DROP PROCEDURE AveSalary;1 DROP PROCEDURE AveSalary;2 DROP PROCEDURE AveSalary;351第51页，本讲稿共103页7.2 触发器触发器n触发器概述触发器概述 ninserted和和deleted表表 n事务的概念及应用事务的概念及应用 n创建触发器创

43、建触发器 n修改和重命名触发器修改和重命名触发器 n删除触发器删除触发器 52第52页，本讲稿共103页7.2.1 触发器的基本概念触发器的基本概念 n触发器是一种特殊的存储过程，与普通存储过程触发器是一种特殊的存储过程，与普通存储过程的区别：触发器的执行是由事件触发的，而普通的区别：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。存储过程是由命令调用执行的。n使用触发器有助于强制保持数据库的数据完整性。使用触发器有助于强制保持数据库的数据完整性。例如，在触发器中可以完成如下功能：例如，在触发器中可以完成如下功能：不允许删除或更新特定的记录。不允许删除或更新特定的记录。不允许插入

44、不符合逻辑关系的记录。不允许插入不符合逻辑关系的记录。在删除一条记录的同时删除其他表中与其相关在删除一条记录的同时删除其他表中与其相关的记录。的记录。在修改一条记录的同时修改其他表中与其相关在修改一条记录的同时修改其他表中与其相关的记录。的记录。53第53页，本讲稿共103页SQL Server 2000提供了两种触发器提供了两种触发器 nINSTEAD OF触发器触发器(前置触发器前置触发器)：INSTEAD OF触发器在指定的操作（触发器在指定的操作（INSERT、UPDATE或或DELETE语句）之前被执行，它的功能是不执行语句）之前被执行，它的功能是不执行指定的操作，而是执行指定的操作

45、，而是执行INSTEAD OF触发器中定触发器中定义的操作。可以在义的操作。可以在表和视图表和视图上定义上定义INSTEAD OF触发器。触发器。nAFTER/FOR触发器触发器（后置触发器后置触发器）。在执行了）。在执行了INSERT、UPDATE或或DELETE语句操作之后执语句操作之后执行行AFTER触发器。触发器。AFTER触发器只能在触发器只能在表表上指上指定。定。54第54页，本讲稿共103页触发器的主要优点触发器的主要优点 n触发器是自动执行的，不需要管理员手动维护数触发器是自动执行的，不需要管理员手动维护数据库的数据完整性。据库的数据完整性。n触发器可以对数据库中的相关表进行级

46、联更改。触发器可以对数据库中的相关表进行级联更改。例如，可以在表例如，可以在表“院系院系”中定义触发器，当用户中定义触发器，当用户删除表删除表“院系院系”中的记录时，触发器将删除表中的记录时，触发器将删除表“学生学生”中对应院系的记录。中对应院系的记录。n触发器可以限制向表中插入无效的数据，这一点触发器可以限制向表中插入无效的数据，这一点与与CHECK约束的功能相似。但在约束的功能相似。但在CHECK约束中约束中不能使用到其他表中的字段，而在触发器中则没不能使用到其他表中的字段，而在触发器中则没有此限制。例如，可以在表有此限制。例如，可以在表“学生学生”中定义触发中定义触发器，限制插入的记录其

47、器，限制插入的记录其“院系编号院系编号”字段值必须字段值必须在表在表“院系院系”中存在对应的记录。中存在对应的记录。55第55页，本讲稿共103页7.2.2 inserted和和deleted表表 ndeleted表表用于存储用于存储DELETE和和UPDATE语句所语句所影响的行的复本。在执行影响的行的复本。在执行DELETE或或UPDATE语句时，行从触发器表中删除，并传输到语句时，行从触发器表中删除，并传输到deleted表中。表中。deleted表和触发器表通常没有相表和触发器表通常没有相同的行。同的行。ninserted表表用于存储用于存储INSERT和和UPDATE语句所语句所影响

48、的行的副本。在一个插入或更新事务处理影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到中，新建行被同时添加到inserted表和触发器表表和触发器表中。中。inserted表中的行是触发器表中新行的副本。表中的行是触发器表中新行的副本。56第56页，本讲稿共103页inserted和和deleted表的变化表的变化n在设置触发器条件时，应当为引发触发器的操在设置触发器条件时，应当为引发触发器的操作恰当使用作恰当使用inserted和和deleted表。表。n通常在插入数据时，可以从通常在插入数据时，可以从inserted表中读取新表中读取新插入的值，此时插入的值，此时deleted

49、表不会发生变化。表不会发生变化。n在删除数据时，可以从在删除数据时，可以从deleted表中读取已经删表中读取已经删除或修改的值，而除或修改的值，而inserted表不会发生变化。表不会发生变化。n在更新数据时，在更新数据时，inserted表和表和deleted表都发生变表都发生变化。可以从化。可以从deleted表中读取原有的值，从表中读取原有的值，从inserted表中读取修改后的值。表中读取修改后的值。57第57页，本讲稿共103页7.2.3 事务的概念及应用事务的概念及应用 在触发器中经常会取消用户先前进行的操作，在触发器中经常会取消用户先前进行的操作，例如不允许插入不符合条件的数据

50、。例如不允许插入不符合条件的数据。SQL Server提供了一种叫做事务的机制，它可以保证指定的提供了一种叫做事务的机制，它可以保证指定的对数据库的一系列操作作为一个整体被执行，在对数据库的一系列操作作为一个整体被执行，在最终提交操作之间，用户可以随时取消前面的操最终提交操作之间，用户可以随时取消前面的操作，将数据库还原到没有执行操作前的状态。作，将数据库还原到没有执行操作前的状态。58第58页，本讲稿共103页事务的属性事务的属性n原子性原子性:事务必须是原子工作单元。它对数据库所进行的事务必须是原子工作单元。它对数据库所进行的操作，要么全都执行，要么全都不执行。操作，要么全都执行，要么全都