第10章存储过程和触发器Word下载.docx
- 文档编号:20758418
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:20
- 大小:27.19KB
第10章存储过程和触发器Word下载.docx
《第10章存储过程和触发器Word下载.docx》由会员分享,可在线阅读,更多相关《第10章存储过程和触发器Word下载.docx(20页珍藏版)》请在冰豆网上搜索。
(5)减少网络流量。
因为存储过程存储在服务器上,并在服务器上运行。
一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行;
而不需要在网络中发送数百行代码,这样就可以减少网络流量。
10.1.2存储过程的分类
在SQLServer2005中存储过程可以分为两类:
系统存储过程和用户存储过程。
1.系统存储过程
系统存储过程是由SQLServer系统提供的存储过程,可以作为命令执行各种操作。
系统存储过程主要用来从系统表中获取信息,为系统管理员管理SQLServer提供帮助,为用户查看数据库对象提供方便。
例如,执行sp_helptext系统存储过程可以显示规则、默认值、未加密的存储过程、用户函数、触发器或视图的文本信息;
执行sp_depends系统存储过程可以显示有关数据库对象相关性的信息;
执行sp_rename系统存储过程可以更改当前数据库中用户创建对象的名称。
SQLServer中许多管理工作是通过执行系统存储过程来完成的,许多系统信息也可以通过执行系统存储过程而获得。
系统存储过程定义在系统数据库master中,其前缀是sp_。
在调用时不必在存储过程前加上数据库名。
有关系统存储过程的详细介绍请参考SQLServer联机丛书。
除了以sp_为前缀的系统存储过程,我们还常见到以xp_为前缀的存储过程,这种存储过程为扩展存储过程。
扩展存储过程主要用于扩展SQLServer的功能。
2.用户存储过程
用户存储过程是指用户根据自身需要,为完成某一特定功能,在用户数据库中创建的存储过程。
用户创建存储过程时,存储过程名的前面加上“##”,是表示创建全局临时存储过程;
在存储过程名的前面加上“#”,是表示创建局部临时存储过程。
局部临时存储过程只能在创建它的会话中可用,当前会话结束时除去;
全局临时存储过程可以在所有会话中使用,即所有用户均可以访问该过程。
它们都保存在tempdb数据库中。
10.1.3存储过程的创建与管理
在SQLServer2005中通常可以使用两种方法创建存储过程:
一种是使用图形化管理工具SQLServerManagementStudio创建存储过程;
另一种是使用T-SQL语句创建存储过程。
创建存储过程时,需要注意下列事项:
●只能在当前数据库中创建存储过程。
●创建存储过程时,应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。
●在用户存储过程的定义中不能使用下列对象创建语句:
CREATEVIEW、CREATEDEFAULT、CREATERULE、CREATEPROCEDURE、CREATETRIGGER。
即在存储过程的创建中不能嵌套创建以上这些对象。
存储过程创建后,可以使用EXECUTE语句来执行(可以简写为EXEC),如果它是一个批处理中的第一条语句,则关键字EXECUTE(或EXEC)也可省略。
10.1.4使用SSMS创建和执行存储过程
【案例10-1】执行系统存储过程sp_help查看教务管理数据库stu中class表的信息。
程序清单:
usestu
go
execsp_helpclass
运行以上程序,结果如图10-2所示。
图10-2执行系统存储过程sp_help查看class表信息
【案例10-2】在数据库stu中,创建一个名称为“stu_softjs”的存储过程,通过该存储过程可查询出软件工程系所有教授的信息。
操作步骤如下:
(1)启动SQLServerManagementStudio,在【对象资源管理器】窗口中,依次展开【数据库】→【stu】→【可编程性】节点。
(2)右键单击【存储过程】节点,选择【新建存储过程】命令,打开【创建存储过程模版】文档窗口,如图10-3所示。
(3)用户在模版文档窗口中根据相应提示输入存储过程名称和T-SQL语句。
创建存储过程“stu_softjs”,如图10-4所示。
(4)单击【执行】按钮,完成存储过程的创建。
【提示】
·
在模板文档窗口中可以把不必要的参数去掉。
在第
(2)步骤右键单击“存储过程”节点,选择“刷新”,即可看到刚刚创建好的存储过程。
图10-3【创建存储过程模版】文档窗口
图10-4创建存储过程“stu_softjs”
【案例10-3】使用SSMS执行上面例子中创建的存储过程“stu_softjs”。
⑴启动SQLServerManagementStudio,在【对象资源管理器】中依次展开【数据库】→【student】→【可编程性】→【存储过程】节点。
⑵右键单击【stu_softjs】存储过程,选择【执行存储过程】命令,如图10-5所示。
⑶打开【执行过程】对话框,再单击【确定】按钮即可。
图10-5选择【执行存储过程】命令
10.1.5使用SSMS查看、修改和删除存储过程
【案例10-4】使用SSMS,查看上例中创建的存储过程“stu_softjs”的属性。
(1)在如图10-5所示的右键菜单中,选择【属性】菜单,打开【存储过程属性】对话框。
(2)选择【常规】选项卡:
可以查看到该存储过程属于哪个数据库、创建如期和属于男个数据库用户等信息。
(3)选择【权限】选项卡:
可以为该存储过程添加用户并授予其权限。
(4)选择【扩展属性】选项卡:
可以了解排序规则等扩展属性。
在如图10-4所示的右键菜单中,选择“删除”菜单命令可以删除指定的存储过程;
选择“修改”命令进入存储过程文本修改状态,可对存储过程进行修改;
选择“重命名”可以实现存储过程的名称的更改。
10.1.6使用T-SQL语句创建和执行存储过程
1.创建存储过程
使用T-SQL语句CREATEPROC可以创建存储过程,其基本语句格式如下所示。
CREATEPROC[EDURE]存储过程名
[{@参数1数据类型}[=默认值][OUTPUT]][,n]
[WHTIENCRYPTION│RECOMPILE]
AS
SQL语句
参数含义:
◆存储过程名:
要符合标识符规则,少于128个字符。
◆@参数:
过程中的参数。
在CREATEPROCEDURE语句中可以声明一个或多个参数。
◆OUTPUT:
表明该参数是一个返回参数。
◆AS:
用于指定该存储过程要执行的操作。
◆SQL语句:
是存储过程中要包含的任意数目和类型的T-SQL语句。
◆ENCRYPTION:
用于加密存储过程文本。
本加密的存储过程,其图标上有“加锁”标志,其定义内容不可修改,也不可通过系统存储过程sp_helptext查看。
◆RECOMPILE:
设置该选项后,存储过程将在运行时重新编译。
【案例10-5】在“stu”数据库中,创建一个存储过程“stu_softxs”,通过该存储过程可以查询软件技术专业的学生信息。
(1)在“查询编辑器”窗口中输入如下代码:
USEstu
GO
CREATEPROCstu_softxs
AS
Select*
Fromstudent
Wheres_classidin
(SELECTc_idFROMclass
WHEREsp_idin
(SELECTsp_id
FROMspeciality
WHEREsp_name=’软件技术’)
)
(2)单击“分析”按钮,进行语法检查;
再单击“执行”按钮,创建存储过程。
2.执行存储过程
执行存储过程的基本语句格式:
[EXEC]procedure_name[Value_List]
参数含义:
●procedure_name:
要执行的存储过程的名称。
●Value_List:
输入参数值。
【案例10-6】执行以上存储过程“stu_softxs”。
代码如下:
USESTU
EXECstu_softxs
执行以上代码,结果如图10-6所示。
图10-6执行存储过程“stu_softxs”结果
10.1.7创建和执行带参数的存储过程
带参数的存储过程可以扩展存储过程的功能。
使用输入参数,可以将外部信息输入到存储过程;
使用输出参数,可以将存储过程内的信息转到外部。
创建带参数的存储过程时,参数可以是一个,也可以是多个,多个参数时,参数之间用逗号分隔。
所有数据类型均可以作为存储过程的参数,一般情况下,参数的数据类型要与它相关的字段的数据类型一致。
1.带有输入参数的存储过程
【案例10-7】在“stu”数据库中,创建存储过程“stu_zymc”,该存储过程带有一个用于接收用户输入专业名称的输入参数。
该存储过程要求被执行时,它将根据用户输入的专业名称列出该专业的所有班级信息。
代码如下:
CREATEPROCstu_zymc@zymcvarchar(20)/*变量参数的数据类型与长度都要和表中相关字段的定义一致*/
SELECT*FROMclass
WHEREsp_idin
WHEREsp_name=@zymc)
执行该存储过程“stu_zymc”,代码如下:
Usestu
Go
EXECstu_zymc软件技术
执行结果如图10-7所示。
执行带参数的存储过程,有两种方式。
按位置转递。
在调用存储过程时,直接给出参数值。
如果多于一个参数,给出的参数值要与定义的参数顺序一致。
例如:
EXECstu_zymc软件技术。
使用参数名称转递。
在调用存储过程时,按“参数名=参数值”的形式给出参数值。
采用此方式,参数如果多于一个时,给出的参数顺序可以与定义的参数的顺序不一致。
EXECstu_zymc@zymc=软件技术。
图10-7执行存储过程显示出相应专业的班级信息
2.带有参数默认值的存储过程
【案例10-8】如果要求上例中创建的存储过程stu_zymc在被执行时不给出参数值将默认显示软件技术专业的班级信息,则创建该存储过程的代码为:
/*变量参数的默认值是“软件技术”专业*/
CREATEPROCstu_zymc@zymcvarchar(20)=’软件技术’
AS
SELECT*FROMclass
WHEREsp_idin
FROMspeciality
不带参数值执行该存储过程,代码:
EXECstu_zymc
3.带有输出参数的存储过程
输出参数用于在存储过程中返回值,使用OUTPUT声明输出参数。
【案例10-9】在stu数据库中,创建一个带有输出参数的存储过程stu_xspjf,其中输出参数用于返回。
创建该存储过程代码:
USEstu
GO/*输出参数的数据类型要与它接收的值的类型一致*/
createprocstu_xspjf@xspjftinyintoutput
as
select@xspjf=avg(sc_grade)
fromstucourse
执行该存储过程并输出显示的代码如下,执行结果如图10-8所示。
declare@avgtinyint
execstu_xspjf@avgoutput
print'
学号为的同学的平均分为:
'
+ltrim(str(@avg))+'
分'
执行带有输出参数的存储过程时,需要声明变量来接收存储过程中由输出参数返回来的返回值。
一般情况下,声明的变量的数据类型要与存储过程的输出参数的数据类型一致。
在使用该变量时,还必须为它加上OUTPUT声明。
图10-8执行带有输出参数的存储过程并输出显示信息
10.1.8使用T-SQL语句查看、修改和删除存储过程
1使用SSMS查看、修改和删除存储过程
请参阅【案例10-4】、【提示】和图10-5。
2.使用系统存储过程查看存储过程信息
(1)使用sp_help查看存储过程的一般信息,包含存储过程的名称、拥有者、类型和创建时间,其语法格式为:
ExecSp_help存储过程名
(2)使用sp_helptext查看存储过程的定义信息,其语法格式为:
ExecSp_helptext存储过程名
【案例10-10】分别使用系统存储过程sp_help和sp_helptext查看stu数据库中的存储过程“stu_xspjf”的定义、相关性及一般信息。
代码如下,结果如图10-9所示。
EXECsp_helpstu_xspjf
EXECsp_helptextstu_xspjf
GO
图10-9使用系统存储过程查看存储过程信息
3.使用语句修改存储过程
使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程,ALTERPROCEDURE基本语句格式如下。
ALTERPROC[EDURE]存储过程名
[{@参数1数据类型}[=默认值][OUTPUT],
…….
{@参数n数据类型}[=默认值][OUTPUT]
]
各参数含义与CREATEPROCDURE语句相同,只是把创建时的CREATE改为了ALTER。
因为修改和创建时的过程方法一样,在这里不再另外举例说明。
4.使用语句删除存储过程
当存储过程没有存在的意义时,可以使用DROPPROCEDURE语句将其删除。
用于删除存储过程的基本语句格式:
DROPPROCEDURE存储过程名[,…n]
【案例10-11】删除“stu”数据库中的存储过程“stu_xspjf”。
DROPPROCEDUREstu_xspjf
10.2触发器概述
10.2.1触发器的概念
1.触发器的作用
触发器是一种特殊类型的存储过程,它在指定的表中的数据发生变化时自动生效,实现表间的数据完整性和复杂的业务规则。
与前面介绍过的存储过程不同,存储过程可以通过存储过程名字被直接调用,而触发器不能,触发器主要通过事件进行触发而自动执行的。
当对某一表进行诸如INSERT、UPDATE或DELETE操作时,如果在这些操作上定义了触发器,SQLServer就会自动执行触发器(执行触发器中所定义的SQL语句),从而确保对数据的处理必须符合由这些SQL语句所定义的规则。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性,除此之外,触发器还有其他许多不同的功能。
1强化约束:
触发器能够实现比CHECK语句更为复杂的约束。
CHECK约束不允许引用其他表中的列来完成检查工作,而触发器可以引用其他表中的列。
2跟踪变化:
触发器可以侦测数据库的操作,从而不允许数据库中未经许可的指定更新和变化。
3级联运行:
触发器可以侦测数据库内的操作,并自动地级联影响整个数据库的相关内容。
例如,某个表上的触发器中包含有对另外一个表的数据操作(如插入、更新、删除),而该操作又导致该表上触发器被触发。
2.触发器类型
在SQLServer2005中,触发器分为DML触发器和DDL触发器两大类。
当数据库中发生数据操作语言(DML)事件时将调用DML触发器,当服务器或数据库中发生数据定义语言(DDL)事件时将调用DDL触发器。
DML触发器是当数据库服务器中发生数据库操作语言(DML)事件时要执行的操作。
DML事件包括对表或视图发出的UPDATE、INSERT或DELETE语句。
DML触发器用于在数据库修改时强制执行业务规则,以及扩展SQLServer2005约束、默认值和规则的完整性检查逻辑。
根据DML触发器被激活的时机不同又可以分为AFTER触发器和INSTEADOF触发器。
1AFTER触发器又称为后触发器。
在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。
指定AFTER触发器与指定FOR相同,它是MicrosoftSQLServer早期版本中唯一可用的选项,但AFTER触发器只能在表上指定。
2INSTEADOF触发器又称为替代触发器。
该类触发器代替触发器操作执行,即触发器在数据发生变动之前被触发,取代变动数据的操作(INSERT、UPDATE或DELETE操作),执行触发器定义的操作。
该类触发器既可在表上定义,也可在视图上定义。
对于每个触发器操作(INSERT、UPDATE和DELETE)只能定义一个INSTEADOF触发器。
DDL触发器是SQLServer2005的新增功能。
DDL触发器是一种特殊的触发器,它不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。
相反,它在响应数据定义语言(DDL)语句时触发,这些语句主要是以CREATE、ALTER和DROP开头的语句。
它们可以用于数据库中执行管理任务,例如,审核以及规范数据库操作。
因为DDL触发器和DML触发器可以使用相似的SQL语法进行创建、修改和删除,它们还具有其他相似的行为。
所以这里只介绍DML触发器的创建与使用。
10.2.2inserted表和deleted表
系统为每个触发器创建两个特殊临时表:
inserted表和deleted表。
这两个表都是逻辑表,由系统管理存储在内存中,它们在结构上与该触发器作用的表相同。
这两个表是只读,用户不能对其修改和写入内容,但可以在触发器执行过程中引用这两个表中的数据。
当触发器工作完成后,与该触发器相关的这两个表也被删除。
Inserted表用于存储INSERT和UPDATE语句所影响的行的副本。
如果表存在INSERT触发器,向表中插入数据时,系统将自动创建一个与触发器具有相同表结构的Inserted临时表,新的记录被添加到触发器表和Inserted表中。
Inserted表就是用来存储向原表插入的纪录副本。
Deleted表用于存储DELETE和UPDATE语句所影响的行的副本。
在执行DELETE或UPDATE语句时,从触发器表中删除原记录,并把删除的记录的副本临时存放到daleted表中。
这样做的目的是:
一旦触发器遇到了强迫它中止的语句被执行时,删除的那些行可以从deleted表中得以恢复。
修改表中的数据,相当于删除一条旧的记录,添加一条新的记录。
其中,被删除的记录放在Deleted表中,添加的新的记录放在Inserted表中。
10.2.3创建与管理触发器
1.使用T-SQL创建与管理触发器
T-SQL语言使用CREATETRIGGER命令创建触发器。
创建DML触发器的基本语句格式:
CREATETRIGGER触发器名
ON表|视图
FOR|AFTER|INSTEADOF
INSERT|UPDATE|DELETE
DML语句
【案例10-12】在stu数据库的学生表student中创建一个触发器tr_scxs,当学生表student有记录被删除时,显示“XXX同学信息已被你成功删除!
”。
创建该触发器代码如下:
CREATETRIGGERtr_scxs
ONstudent
FORDELETE
BEGIN
DECLARE@xsxxCHAR(10)
SELECT@xsxx=s_nameFROMDELETED
PRINT@xsxx+‘同学信息已被你成功删除!
’
END
创建触发器后,删除一条记录,验证该触发器,代码如下:
Usestu
Deletestudentwheres_num='
执行以上删除记录语句后,结果如图10-10所示。
图10-10删除记录激活触发器返回信息
虽然DML触发器可以引用临时表,但不能对临时表或系统表创建DML触发器。
对含有用DELETE或UPDATE操作定义的外键的表,不能定义INSTEADOFDELETE和INSTEADOFUPDATE触发器。
TRUNCATETABLB语句不会触发DELETE触发器,因为TRUNCATETABLB语句没有执行记录。
【案例10-13】在stu数据库中创建一个删除触发器tr_delxs,当表student中的记录要被删除时,激活该触发器,显示“不能删除本表中的数据!
请与管理员联系!
”的提示信息。
CREATETRIGGERtr_delxs
O
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 10 存储 过程 触发器
![提示](https://static.bdocx.com/images/bang_tan.gif)