《存储过程与触发器pptPPT讲稿.ppt》由会员分享，可在线阅读，更多相关《存储过程与触发器pptPPT讲稿.ppt（103页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、存储过程与触发器存储过程与触发器ppt1第1页，共103页，编辑于2022年，星期六本章要点本章要点n存储过程概述存储过程概述 n创建存储过程创建存储过程 n执行存储过程执行存储过程 n存储过程的参数存储过程的参数 n存储过程的返回值存储过程的返回值 n查看和修改存储过程查看和修改存储过程 n删除存储过程删除存储过程 n触发器概述触发器概述 ninserted和和deleted表表 n事务的概念及应用事务的概念及应用 n创建触发器创建触发器 n修改和重命名触发器修改和重命名触发器 n删除触发器删除触发器 2第2页，共103页，编辑于2022年，星期六7.1 存储过程存储过程在创建在创建SQL
2、Server数据库应用程序时，数据库应用程序时，Transact-SQL语言是应用程语言是应用程序和序和SQL Server数据库之间的主要编程接口。可用如下数据库之间的主要编程接口。可用如下两种两种方法存储方法存储和执行和执行Transact-SQL语句。语句。(1)将将Transact-SQL程序保存在本地，创建向程序保存在本地，创建向SQL Server发送命令发送命令并处理结果的应用程序。并处理结果的应用程序。(2)可以将可以将Transact-SQL程序保存在程序保存在SQL Server中，即存中，即存储过程，在本地创建执行存储过程及处理结果的应用程序。储过程，在本地创建执行存储过
3、程及处理结果的应用程序。任何一组任何一组Transact-SQL语句构成的代码块，都可以作为存语句构成的代码块，都可以作为存储过程保存起来。它在服务器端对数据库中的数据进行处理，储过程保存起来。它在服务器端对数据库中的数据进行处理，并将结果返回到客户端。这样就避免了从客户端多次连接并并将结果返回到客户端。这样就避免了从客户端多次连接并访问数据库的操作，减少了网络上的传输量，同时也提高了访问数据库的操作，减少了网络上的传输量，同时也提高了客户端的工作效率（因为这些操作都是在服务器端完成的）。客户端的工作效率（因为这些操作都是在服务器端完成的）。3第3页，共103页，编辑于2022年，星期六7.1
4、.1 存储过程概述存储过程概述 存储过程是集中存储在存储过程是集中存储在SQL Server中的中的SQL语句语句和流程控制语句的预编译集合和流程控制语句的预编译集合，用以实现某种任务，用以实现某种任务（如查询）。这些语句（如查询）。这些语句在一个名称下存储在一个名称下存储并作为一并作为一个单元进行处理。个单元进行处理。4第4页，共103页，编辑于2022年，星期六使用存储过程的优势使用存储过程的优势 n 可以减少客户端代码的重复可以减少客户端代码的重复。只需创建存储过程一次并将其。只需创建存储过程一次并将其存储在数据库中，以后即可在客户端程序中多次调用该存储存储在数据库中，以后即可在客户端程
5、序中多次调用该存储过程。存储过程可由在数据库编程方面有专长的人员创建，过程。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。并可独立于程序源代码而单独修改。n 允许更快地执行允许更快地执行。如果某操作需要大量的。如果某操作需要大量的Transact-SQL代码或需要重复执行，使用存储过程将比在客户端执行代码或需要重复执行，使用存储过程将比在客户端执行Transact-SQL批代码的执行速度要快。存储过程在服务器批代码的执行速度要快。存储过程在服务器端经过预编译，生成查询计划，可以直接执行，而在客户端经过预编译，生成查询计划，可以直接执行，而在客户端每次运行端每次运
6、行Transact-SQL语句时，都要从客户端重复发送，语句时，都要从客户端重复发送，并且在并且在SQL Server每次执行这些语句时，都要对其进行编每次执行这些语句时，都要对其进行编译和优化。因此，存储过程执行速度更快。译和优化。因此，存储过程执行速度更快。5第5页，共103页，编辑于2022年，星期六使用存储过程的优势使用存储过程的优势 n减少网络流量减少网络流量。例如，一个需要数百行。例如，一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。就可实现，而不需要在网络中发送数百行代码。
7、n可以简化数据库管理可以简化数据库管理。例如，要修改某种查询，。例如，要修改某种查询，如果查询重复存放在客户机上，则要在所有客户如果查询重复存放在客户机上，则要在所有客户机上修改查询，而使用存储过程可以集中修改。机上修改查询，而使用存储过程可以集中修改。n可作为安全机制使用可作为安全机制使用。例如，即使对于没有直接。例如，即使对于没有直接执行存储过程中语句的权限的用户，也可授予他执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。们执行该存储过程的权限。6第6页，共103页，编辑于2022年，星期六存储过程的分类存储过程的分类 n系统存储过程系统存储过程:SQL Server内置
8、的存储过程，存内置的存储过程，存储在储在master库中，主要用途是执行库中，主要用途是执行SQL Server的的某些管理功能、显示有关数据库和用户的信息。某些管理功能、显示有关数据库和用户的信息。系统存储过程名以系统存储过程名以SP_开头，可以在任何数据库开头，可以在任何数据库中执行系统存储过程。中执行系统存储过程。n用户存储过程用户存储过程：用户自行创建并存储在用户数据：用户自行创建并存储在用户数据库中的存储过程。库中的存储过程。n临时存储过程临时存储过程：分为局部临时存储过程和全局临：分为局部临时存储过程和全局临时存储过程。时存储过程。7第7页，共103页，编辑于2022年，星期六存储
9、过程的分类存储过程的分类 局部临时存储过程名称以局部临时存储过程名称以#开头，存放在开头，存放在tempdb数据库中，只由创建并连接的用户使数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时用，当该用户断开连接时将自动删除局部临时存储过程。存储过程。全局临时存储过程名称以全局临时存储过程名称以#开头，存放在开头，存放在tempdb数据库中，允许所有连接的用户使用，数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。在所有用户断开连接时自动被删除。n远程存储过程：位于远程服务器上的存储过程。远程存储过程：位于远程服务器上的存储过程。n扩展存储过程：利用外部语言
10、（如扩展存储过程：利用外部语言（如C）编写的存）编写的存储过程，以弥补储过程，以弥补SQL Server的不足之处，扩展新的不足之处，扩展新的功能的功能8第8页，共103页，编辑于2022年，星期六7.1.2 创建存储过程创建存储过程 n使用使用CREATE PROCEDURE语句语句 n使用企业管理器中的菜单命令使用企业管理器中的菜单命令 9第9页，共103页，编辑于2022年，星期六CREATE PROCEDURE语句语句 CREATE PROC EDURE 存储过程名存储过程名;编号编号 参数参数 数据类型数据类型 VARYING =默认值默认值 OUTPUT ,.nWITH RECOM
11、PILE|ENCRYPTION|RECOMPILE,ENCRYPTIONAS SQL语句语句 .n 10第10页，共103页，编辑于2022年，星期六参数说明参数说明 n存储过程名必须符合标识符的命名规则，且对存储过程名必须符合标识符的命名规则，且对于数据库及其所有者必须是惟一的。于数据库及其所有者必须是惟一的。要创建局要创建局部临时存储过程，可以在存储过程名前面加一部临时存储过程，可以在存储过程名前面加一个个#号，要创建全局临时过程，可以在存储过程号，要创建全局临时过程，可以在存储过程名前面加两个名前面加两个#号。完整的名称（包括号。完整的名称（包括#或或#）不能超过）不能超过128个字符。
12、个字符。n编号编号：可选整数，用来对同名的存储过程分组，：可选整数，用来对同名的存储过程分组，以便用一条以便用一条DROP PROCEDURE语句即可将同语句即可将同组的存储过程一起删除。组的存储过程一起删除。11第11页，共103页，编辑于2022年，星期六参数说明参数说明 n参数：过程中的参数参数：过程中的参数。在。在CREATE PROCEDURE语句中可以声明一个或多个参数。语句中可以声明一个或多个参数。用户必须在执行存储过程时提供每个所声明参用户必须在执行存储过程时提供每个所声明参数的值（除非定义了该参数的默认值）。参数数的值（除非定义了该参数的默认值）。参数名称前需要使用名称前需要
13、使用符号。参数名称必须符合标识符号。参数名称必须符合标识符的命名规则。每个过程的参数仅用于该过程符的命名规则。每个过程的参数仅用于该过程本身。在其他过程中可以使用相同的参数名称。本身。在其他过程中可以使用相同的参数名称。默认情况下，参数只能代替常量，而不能用于默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。代替表名、列名或其他数据库对象的名称。12第12页，共103页，编辑于2022年，星期六参数说明参数说明 n数据类型：参数的数据类型。所有数据类型数据类型：参数的数据类型。所有数据类型（包括（包括text、ntext和和image）均可以用作存储过）均可以用作存
14、储过程的参数。不过，程的参数。不过，cursor数据类型只能用于数据类型只能用于OUTPUT参数。如果指定的数据类型为参数。如果指定的数据类型为cursor，也必须同时指定，也必须同时指定VARYING和和OUTPUT关键关键字。字。nVARYING：指定作为输出参数支持的结果集：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅（由存储过程动态构造，内容可以变化）。仅适用于游标参数。适用于游标参数。13第13页，共103页，编辑于2022年，星期六参数说明参数说明 n默认值：参数的默认值。如果定义了默认值，默认值：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行存
15、储过程。默认不必指定该参数的值即可执行存储过程。默认值必须是常量或值必须是常量或NULL。如果要在存储过程中。如果要在存储过程中对该参数使用对该参数使用LIKE关键字，那么默认值中可以关键字，那么默认值中可以包含通配符（包含通配符（%、_、和和）。）。nOUTPUT：表明参数是返回参数。该选项的值：表明参数是返回参数。该选项的值可以返回给可以返回给EXECUTE。使用。使用OUTPUT参数参数可将信息返回给调用过程。可将信息返回给调用过程。text、ntext和和image参数可用作参数可用作OUTPUT参数。使用参数。使用OUTPUT关键关键字的输出参数可以是游标占位符。字的输出参数可以是游
16、标占位符。14第14页，共103页，编辑于2022年，星期六参数说明参数说明 nRECOMPILE：表明不保存该存储过程的执行：表明不保存该存储过程的执行计划，该存储过程将在运行时重新编译。计划，该存储过程将在运行时重新编译。nENCRYPTION：指定：指定SQL Server对对syscomments表中包含本表中包含本CREATE PROCEDURE语句文本的条目进行加密语句文本的条目进行加密nAS：用于指定该存储过程要执行的操作。：用于指定该存储过程要执行的操作。nSQL语句：存储过程中要包含的语句：存储过程中要包含的Transact-SQL语句。语句。15第15页，共103页，编辑于
17、2022年，星期六存储过程定义存储过程定义1.无参数存储过程无参数存储过程2.有参数存储过程（输入参数、输出参数有参数存储过程（输入参数、输出参数output）16第16页，共103页，编辑于2022年，星期六例题例题1：建立一个查询存储过程，实现查：建立一个查询存储过程，实现查询成绩表中的所有及格成绩。（无参数询成绩表中的所有及格成绩。（无参数存储过程）存储过程）create proc seleproc as select*from 成绩表成绩表where 成绩成绩=60执行存储过程：执行存储过程：exec seleproc结果如图：结果如图：17第17页，共103页，编辑于2022年，星期
18、六例例2：创建一个向成绩表添加记录的存储：创建一个向成绩表添加记录的存储过程。（有参数存储过程）过程。（有参数存储过程）create proc insertproc sno char(10),cno char(3),grade floatas insert into 成绩表成绩表 values(sno,cno,grade)执行存储过程：执行存储过程：exec insertproc 0009,03,9818第18页，共103页，编辑于2022年，星期六例例3：创建一个存储过程，要求根据输入的课程号：创建一个存储过程，要求根据输入的课程号统计该课程平均分。统计该课程平均分。create proc
19、cnoavgscore cno char(2),avgscore float outputas declare cnon char(2)set cnon=select cnon=课程号课程号from 成绩表成绩表 where 课程号课程号=cno if cnon begin select avgscore=avg(成绩成绩)from 成绩表成绩表 where 课程号课程号=cno end else print 此课程号不存在此课程号不存在 go19第19页，共103页，编辑于2022年，星期六运行该存储过程：运行该存储过程：declare i floatexec cnoavgscore 02,
20、i outputprint i20第20页，共103页，编辑于2022年，星期六说明：说明：With encryption 子句的作用是将存储过程的原代子句的作用是将存储过程的原代码加密；码加密；With recompile子句的作用是对该存储过程重编译。子句的作用是对该存储过程重编译。21第21页，共103页，编辑于2022年，星期六例例4 n创建存储过程创建存储过程“增加成绩增加成绩”，将表，将表“成绩表成绩表”中所有学生的成绩增加中所有学生的成绩增加10%。USE 学生管理学生管理 GO CREATE PROCEDURE 增加成绩增加成绩 AS UPDATE 成绩表成绩表 SET 成绩成
21、绩=成绩成绩*1.1 GO执行存储过程：执行存储过程：exec 增加成绩增加成绩 22第22页，共103页，编辑于2022年，星期六例例5 （细节考虑）（细节考虑）n在创建一个存储过程时，如果已经存在同名的存储过程，则不在创建一个存储过程时，如果已经存在同名的存储过程，则不允许创建新的存储过程。可以将以上代码改写为：允许创建新的存储过程。可以将以上代码改写为：USE 学生管理学生管理 -如果存在名称为如果存在名称为“增加成绩增加成绩”的存储过程，则将其删除的存储过程，则将其删除 IF EXISTS(SELECT name FROM sysobjects WHERE name=增加成绩增加成绩
22、AND type=P)DROP PROCEDURE 增加成绩增加成绩 GO CREATE PROCEDURE 增加成绩增加成绩 AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩*1.1 GO23第23页，共103页，编辑于2022年，星期六使用企业管理器创建存储过程使用企业管理器创建存储过程n在企业管理器中，展开要创建存储过程的数据在企业管理器中，展开要创建存储过程的数据库库,用鼠标右击用鼠标右击“存储过程存储过程”，在弹出菜单中单，在弹出菜单中单击击“新建存储过程新建存储过程”命令，打开新建存储过程命令，打开新建存储过程对话框，如下图：对话框，如下图：24第24页，共103页，编
23、辑于2022年，星期六使用企业管理器创建存储过程使用企业管理器创建存储过程 在该对话框的文本框中有以下的默认语句：在该对话框的文本框中有以下的默认语句：CREATE PROCEDURE OWNER.PROCEDURE NAME AS 这是这是CREATE PROCEDURE语句的开始部分，语句的开始部分，存储过程的具体文本需要用户自己输入。例如，存储过程的具体文本需要用户自己输入。例如，将以上默认语句修改成：将以上默认语句修改成：CREATE PROCEDURE 增加成绩增加成绩 AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩*1.1 GO25第25页，共103页，编辑于2022
24、年，星期六7.1.3 执行存储过程执行存储过程 EXEC UTE 返回状态返回状态=存储过程名存储过程名|存储过程名变量存储过程名变量 参数名称参数名称=值值|变量变量 OUTPUT|DEFAULT ,.n WITH RECOMPILE 26第26页，共103页，编辑于2022年，星期六参数说明参数说明n返回状态：是一个可选的整型变量，保存存储过程返回状态：是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于的返回状态。这个变量在用于EXECUTE语句前，语句前，必须在批处理、存储过程或函数中声明过。必须在批处理、存储过程或函数中声明过。n存储过程名：要调用的存储过程的名称。存储过程名
25、：要调用的存储过程的名称。n存储过程名变量：局部变量名，代表存储过程的名存储过程名变量：局部变量名，代表存储过程的名称。称。n参数名称：存储过程的参数，在参数名称：存储过程的参数，在CREATE PROCEDURE语句中定义。参数名称前必须加上符语句中定义。参数名称前必须加上符号号。在使用格式。在使用格式“参数参数=值值”时，参数名称和常时，参数名称和常量不一定按照量不一定按照CREATE PROCEDURE语句中定义语句中定义的顺序出现。但是，如果有一个参数使用的顺序出现。但是，如果有一个参数使用“参数参数=值值”格式，则其他所有参数都必须使用这种格式。格式，则其他所有参数都必须使用这种格式
26、。27第27页，共103页，编辑于2022年，星期六参数说明参数说明n值：过程中参数的值。如果没有指定参数名称，参值：过程中参数的值。如果没有指定参数名称，参数值必须以数值必须以CREATE PROCEDURE语句中定义的语句中定义的顺序给出。顺序给出。如果在如果在CREATE PROCEDURE语句中定义了默语句中定义了默认值，用户执行该存储过程时可以不必指定对应认值，用户执行该存储过程时可以不必指定对应的参数。如果该存储过程使用了带的参数。如果该存储过程使用了带LIKE关键字关键字的参数名称，则默认值必须是常量，并且可以包的参数名称，则默认值必须是常量，并且可以包含含%、_、及及 通配符。
27、通配符。默认值也可以为默认值也可以为NULL。通常，定义存储过程时。通常，定义存储过程时会指定当参数值为会指定当参数值为NULL时应该执行的操作。时应该执行的操作。n变量：是用来保存参数或者返回参数的变量。变量：是用来保存参数或者返回参数的变量。28第28页，共103页，编辑于2022年，星期六参数说明参数说明nOUTPUT：指定存储过程必须返回一个参数。该存：指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字储过程的匹配参数也必须由关键字OUTPUT创建。创建。使用游标变量作参数时使用该关键字。使用游标变量作参数时使用该关键字。nDEFAULT：根据存储过程的定义，提供参数的默
28、：根据存储过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了值，或缺少参数，或指定了DEFAULT关键字时，关键字时，就会出错。就会出错。nWITH RECOMPILE：强制编译新的计划。如果所：强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的计使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资
29、源。用该选项，因为它消耗较多系统资源。29第29页，共103页，编辑于2022年，星期六7.1.4 存储过程的参数存储过程的参数 n为了提高存储过程的灵活性，为了提高存储过程的灵活性，SQL Server 2000支支持在存储过程中使用参数。持在存储过程中使用参数。n存储过程的参数分为输入参数和输出参数两种类存储过程的参数分为输入参数和输出参数两种类型，输入参数用于向存储过程中带入数据，而输型，输入参数用于向存储过程中带入数据，而输出参数则能将存储过程中的数据返回到调用程序。出参数则能将存储过程中的数据返回到调用程序。n在定义存储过程时在定义存储过程时,可以同时指定参数可以同时指定参数,格式如
30、下：格式如下：参数名参数名 数据类型数据类型=默认值默认值 OUTPUT,.nn如果参数后面使用如果参数后面使用OUTPUT关键字，则表明它是关键字，则表明它是输出参数。输出参数。30第30页，共103页，编辑于2022年，星期六例例6n创建存储过程创建存储过程add_proc，用于计算两个参数之和，用于计算两个参数之和并将其输出。并将其输出。CREATE PROCEDURE add_proc num1 INT=0,num2 INT=0 AS DECLARE num3 INT SET num3=num1+num2 PRINT num3n该存储过程定义了两个参数该存储过程定义了两个参数num1和
31、和num2，它们都是输，它们都是输入参数入参数,参数类型为参数类型为INT,默认值为默认值为0。31第31页，共103页，编辑于2022年，星期六执行例执行例6n不带参数执行：不带参数执行：EXEC add_proc 运行结果为：运行结果为：0n带参数执行：带参数执行：EXEC add_proc 13,25 运行结果为：运行结果为：38 有时需要将存储过程中的计算结果返回到调用程序中，有时需要将存储过程中的计算结果返回到调用程序中，以便进行进一步的处理，此时就需要在存储过程中使用以便进行进一步的处理，此时就需要在存储过程中使用输出参数。输出参数。32第32页，共103页，编辑于2022年，星期
32、六例例7n创建存储过程创建存储过程add_proc1，用于计算两个参数之和，用于计算两个参数之和，并使用输出参数返回结果。并使用输出参数返回结果。CREATE PROCEDURE add_proc1 num1 INT=0,num2 INT=0,num3 INT OUTPUT -num3为输出参数为输出参数 AS SET num3=num1+num233第33页，共103页，编辑于2022年，星期六执行例执行例7DECLARE num AS INTEXEC add_proc1 12,23,num OUTPUTPRINT num运行结果为：运行结果为：3834第34页，共103页，编辑于2022年
33、，星期六例题例题8：编辑一个可自由运算的存储过程：编辑一个可自由运算的存储过程create proc calculate num1 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页，编辑于2022年，星期六运行该存储过程运行该存储过程declare num3 floatexec calculate 2,4,/,num3 o
34、utputselect num336第36页，共103页，编辑于2022年，星期六7.1.6 查看和修改存储过程查看和修改存储过程 n使用企业管理器查看和修改存储过程使用企业管理器查看和修改存储过程 n使用查询分析器查看和修改存储过程使用查询分析器查看和修改存储过程 n使用企业管理器重命名存储过程使用企业管理器重命名存储过程 n使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程n使用使用sp_rename重命名存储过程重命名存储过程37第37页，共103页，编辑于2022年，星期六1.使用企业管理器查看和修改存储过程使用企业管理器查看和修改存储过程 n在企业管理器中展开指定
35、的数据库，选中在企业管理器中展开指定的数据库，选中“存储存储过程过程”项，可以查看指定数据库中定义的所有存项，可以查看指定数据库中定义的所有存储过程。储过程。38第38页，共103页，编辑于2022年，星期六1.使用企业管理器查看和修改存储过程使用企业管理器查看和修改存储过程 n双击指定的存储过程，可以打开存储过程属性对双击指定的存储过程，可以打开存储过程属性对话框，查看存储过程的定义情况。话框，查看存储过程的定义情况。用户可以在存储过程用户可以在存储过程属性对话框中修改存属性对话框中修改存储过程的代码。储过程的代码。39第39页，共103页，编辑于2022年，星期六2.使用查询分析器查看和修
36、改存储过程使用查询分析器查看和修改存储过程 n右键单击要编辑的存储过程，在弹出菜单中选择右键单击要编辑的存储过程，在弹出菜单中选择“编辑编辑”，可以在右键的窗格中显示指定存储过，可以在右键的窗格中显示指定存储过程的代码。程的代码。40第40页，共103页，编辑于2022年，星期六3.使用企业管理器重命名存储过程使用企业管理器重命名存储过程 n在企业管理器，展开在企业管理器，展开“数据库数据库”文件夹，选择存文件夹，选择存储过程所在的数据库，单击储过程所在的数据库，单击“存储过程存储过程”文件夹。文件夹。在右侧的窗口中，列出了选择数据库的所有存储在右侧的窗口中，列出了选择数据库的所有存储过程。过
37、程。n右击要重命名的存储过程，选择右击要重命名的存储过程，选择“重命名重命名”菜单菜单项，就可以在当前位置上修改存储过程的名字。项，就可以在当前位置上修改存储过程的名字。41第41页，共103页，编辑于2022年，星期六4.4.使用使用使用使用ALTER PROCEDUREALTER PROCEDURE语句修改存储过程语句修改存储过程语句修改存储过程语句修改存储过程 ALTER PROC EDURE 存储过程名存储过程名;编号编号 参数名参数名 数据类型数据类型 VARYING =默认值默认值 OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENC
38、RYPTIONASSQL语句语句 .n 各参数含义与各参数含义与CREATE PROCEDURE语句相同语句相同42第42页，共103页，编辑于2022年，星期六例例9 n使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程“增加成绩增加成绩”，对其进行加密处理。，对其进行加密处理。USE 学生管理学生管理 GO ALTER PROCEDURE 增加成绩增加成绩 WITH ENCRYPTION AS UPDATE 成绩表成绩表 SET 成绩成绩=成绩成绩+10指定以上语句后，在企业管理器中查看指定以上语句后，在企业管理器中查看“增加成绩增加成绩”存储过程，将弹出一个对话框，提
39、示用户存储过程已存储过程，将弹出一个对话框，提示用户存储过程已经加密，不能够查看。经加密，不能够查看。43第43页，共103页，编辑于2022年，星期六5.使用使用sp_rename重命名存储过程重命名存储过程 n系统存储过程系统存储过程sp_rename的功能是更改当前数据库的功能是更改当前数据库中用户创建对象（如表、列或用户定义数据类型）中用户创建对象（如表、列或用户定义数据类型）的名称。使用的名称。使用sp_rename重命名存储过程的语法结重命名存储过程的语法结构如下：构如下：sp_rename objname=对象名对象名,newname=新对象名新对象名 参数说明：参数说明：obj
40、name=对象名对象名：指定存储过程的当前：指定存储过程的当前名称。名称。newname=新对象名新对象名：指定存储过程的新：指定存储过程的新名称。名称。44第44页，共103页，编辑于2022年，星期六例例10n将存储过程将存储过程add_proc重命名为重命名为add_proc2，则可以，则可以使用以下命令：使用以下命令：EXEC sp_rename add_proc,add_proc2运行结果为：运行结果为：注意注意:更改对象名的任一部分都可能破坏脚本和存储过程。更改对象名的任一部分都可能破坏脚本和存储过程。object 已重命名为已重命名为 add_proc2。45第45页，共103页
41、，编辑于2022年，星期六7.1.7 删除存储过程删除存储过程 n使用企业管理器使用企业管理器 n使用使用DROP PROCEDURE 语句语句46第46页，共103页，编辑于2022年，星期六使用企业管理器使用企业管理器 在企业管理器中，右击要删除的存储过程，选在企业管理器中，右击要删除的存储过程，选择择“删除删除”命令，并确认删除。命令，并确认删除。47第47页，共103页，编辑于2022年，星期六使用使用DROP PROCEDURE语句语句 DROP PROCEDURE 存储过程名存储过程名 ,.n 48第48页，共103页，编辑于2022年，星期六例例11n删除存储过程删除存储过程ad
42、d_proc，可以使用以下命令：，可以使用以下命令：DROP PROCEDURE add_proc49第49页，共103页，编辑于2022年，星期六例例12 删除一组存储过程删除一组存储过程n设某设某“职工职工”数据库中有一个数据库中有一个“职工工资职工工资”表，该表包含有表，该表包含有“基本工资基本工资”、“奖金奖金”和和“实发工资实发工资”等列。创建一组存储过等列。创建一组存储过程，求程，求“职工工资职工工资”表的平均基本工资、平均奖金和平均实发表的平均基本工资、平均奖金和平均实发工资。工资。USE 职工职工 GO CREATE PROC AveSalary;1 AS SELECT AVG
43、(基本工资基本工资)FROM 职工工资职工工资 GO CREATE PROC AveSalary;2 AS SELECT AVG(奖金奖金)FROM 职工工资职工工资 GO CREATE PROC AveSalary;3 AS SELECT AVG(实发工资实发工资)FROM 职工工资职工工资 GO50第50页，共103页，编辑于2022年，星期六例例13删除一组存储过程删除一组存储过程使用以下语句执行以上存储过程。使用以下语句执行以上存储过程。EXEC AveSalary;1 EXEC AveSalary;2 EXEC AveSalary;3要删除以上创建的三个存储过程，可以使用语句：要删除
44、以上创建的三个存储过程，可以使用语句：DROP PROCEDURE AveSalary注意，不能写成：注意，不能写成：DROP PROCEDURE AveSalary;1 DROP PROCEDURE AveSalary;2 DROP PROCEDURE AveSalary;351第51页，共103页，编辑于2022年，星期六7.2 触发器触发器n触发器概述触发器概述 ninserted和和deleted表表 n事务的概念及应用事务的概念及应用 n创建触发器创建触发器 n修改和重命名触发器修改和重命名触发器 n删除触发器删除触发器 52第52页，共103页，编辑于2022年，星期六7.2.1
45、触发器的基本概念触发器的基本概念 n触发器是一种特殊的存储过程，与普通存储过程触发器是一种特殊的存储过程，与普通存储过程的区别：触发器的执行是由事件触发的，而普通的区别：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。存储过程是由命令调用执行的。n使用触发器有助于强制保持数据库的数据完整性。使用触发器有助于强制保持数据库的数据完整性。例如，在触发器中可以完成如下功能：例如，在触发器中可以完成如下功能：不允许删除或更新特定的记录。不允许删除或更新特定的记录。不允许插入不符合逻辑关系的记录。不允许插入不符合逻辑关系的记录。在删除一条记录的同时删除其他表中与其相关在删除一条记录的同时删
46、除其他表中与其相关的记录。的记录。在修改一条记录的同时修改其他表中与其相关在修改一条记录的同时修改其他表中与其相关的记录。的记录。53第53页，共103页，编辑于2022年，星期六SQL Server 2000提供了两种触发器提供了两种触发器 nINSTEAD OF触发器触发器(前置触发器前置触发器)：INSTEAD OF触发器在指定的操作（触发器在指定的操作（INSERT、UPDATE或或DELETE语句）之前被执行，它的功能是不执行语句）之前被执行，它的功能是不执行指定的操作，而是执行指定的操作，而是执行INSTEAD OF触发器中定触发器中定义的操作。可以在义的操作。可以在表和视图表和视
47、图上定义上定义INSTEAD OF触发器。触发器。nAFTER/FOR触发器触发器（后置触发器后置触发器）。在执行了）。在执行了INSERT、UPDATE或或DELETE语句操作之后执语句操作之后执行行AFTER触发器。触发器。AFTER触发器只能在触发器只能在表表上指上指定。定。54第54页，共103页，编辑于2022年，星期六触发器的主要优点触发器的主要优点 n触发器是自动执行的，不需要管理员手动维护数触发器是自动执行的，不需要管理员手动维护数据库的数据完整性。据库的数据完整性。n触发器可以对数据库中的相关表进行级联更改。触发器可以对数据库中的相关表进行级联更改。例如，可以在表例如，可以在
48、表“院系院系”中定义触发器，当用户中定义触发器，当用户删除表删除表“院系院系”中的记录时，触发器将删除表中的记录时，触发器将删除表“学生学生”中对应院系的记录。中对应院系的记录。n触发器可以限制向表中插入无效的数据，这一点触发器可以限制向表中插入无效的数据，这一点与与CHECK约束的功能相似。但在约束的功能相似。但在CHECK约束中约束中不能使用到其他表中的字段，而在触发器中则没不能使用到其他表中的字段，而在触发器中则没有此限制。例如，可以在表有此限制。例如，可以在表“学生学生”中定义触发中定义触发器，限制插入的记录其器，限制插入的记录其“院系编号院系编号”字段值必须字段值必须在表在表“院系院
49、系”中存在对应的记录。中存在对应的记录。55第55页，共103页，编辑于2022年，星期六7.2.2 inserted和和deleted表表 ndeleted表表用于存储用于存储DELETE和和UPDATE语句所语句所影响的行的复本。在执行影响的行的复本。在执行DELETE或或UPDATE语句时，行从触发器表中删除，并传输到语句时，行从触发器表中删除，并传输到deleted表中。表中。deleted表和触发器表通常没有相表和触发器表通常没有相同的行。同的行。ninserted表表用于存储用于存储INSERT和和UPDATE语句所语句所影响的行的副本。在一个插入或更新事务处理中，影响的行的副本。
50、在一个插入或更新事务处理中，新建行被同时添加到新建行被同时添加到inserted表和触发器表中。表和触发器表中。inserted表中的行是触发器表中新行的副本。表中的行是触发器表中新行的副本。56第56页，共103页，编辑于2022年，星期六inserted和和deleted表的变化表的变化n在设置触发器条件时，应当为引发触发器的操作在设置触发器条件时，应当为引发触发器的操作恰当使用恰当使用inserted和和deleted表。表。n通常在插入数据时，可以从通常在插入数据时，可以从inserted表中读取新表中读取新插入的值，此时插入的值，此时deleted表不会发生变化。表不会发生变化。n在