【文档说明】[计算机]Ch08存储过程和触发器课件.ppt，共(40)页，407.520 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-7199.html

以下为本文档部分文字说明：

网络数据库开发技术存储过程和触发器张蕾zhanglei@ujn.edu.cn2什么是存储过程SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。3存储过程的类型(

1)系统存储过程系统存储过程是由系统提供的存储过程，作为命令执行各种操作。(2)本地存储过程本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。(3)临时存储过程临时存储过程属

于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。(4)远程存储过程远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程在SQLServer环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先

加载到SQLServer系统中，并且按照使用存储过程的方法执行。4存储过程使用存储过程的优点：(1)存储过程在服务器端运行，执行速度快。(2)存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。(3)确保数据

库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。(4)自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的

任务。5函数有返回值，而存储过程的返回值仅说明该过程是否成功完成。存储过程与函数的比较函数与存储过程基本类似，调用时函数要用表达式方式，而存储过程不能直接调用，必须采用“EXEC存储过程名”或“CALL存储过程名”。6创建存储过程在SQL

Server中，可以使用三种方法创建存储过程：①使用创建存储过程向导创建存储过程。②利用SQLServer企业管理器创建存储过程。③使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。7用户存储过程的创建与执行在用户存储过

程的定义中不能使用下列对象创建语句：CREATEVIEWCREATEDEFAULTCREATERULE1．通过SQL命令创建和执行存储过程如果要通过SQL命令定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩定义如下存储过程USEXSCJGoCREATE

PROCEDUREstudent_gradeASSELECTXS.学号,XS.姓名,KC.课程名,XS_KC.成绩FROMXS,XS_KC,KCWHEREXS.学号=XS_KC.学号ANDXS_KC.课程号=KC.课程号Go8存储过程调用存储过程EXECstudent_gr

adeGO1)创建存储过程语法格式:CREATE{PROC|PROCEDURE}procedure_name/*定义过程名*/[{@parameterdata_type}/*定义参数的类型*/[VARYING][=default][OUTPUT]]/*定义参数的属性*/[,...n1][WITH{

RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]/*定义存储过程的处理方式*/ASsql_statement[...n2]/*执行的操作*/9存储过程对于存储过程要注意下列几点：(1)用户定义的存储过程只能在当前数据库中创建。(2)成功执行CREATEP

ROCEDURE语句后，过程名称存储在sysobjects系统表中，而CREATEPROCEDURE语句的文本存储在syscomments中。(3)自动执行存储过程SQLServer启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在master数据库中创建，并在sysadmi

n固定服务器角色下作为后台过程执行。(4)一个存储过程的最大尺寸为128M。(5)创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。10存储过程2）存储过程的执行通过EXEC命令可以执行一个已定义的存储过程。语法格式：[EXEC[UT

E]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n][WITHR

ECOMPILE]｝存储过程的执行要注意下列几点：(1)如果存储过程名的前三个字符为sp_，SQLServer会在Master数据库中寻找该过程。如果没能找到合法的过程名，SQLServer会寻找所有者名称为dbo的过程。(2

)参数可以通过value或@parameter_name=value提供。(3)执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE关键字。11存储过程3)举例(1)设计简单的存储过程[例]从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学

分。该存储过程不使用任何参数。USEXSCJCREATEPROCEDUREstudent_info/*创建存储过程*/ASSELECTa.学号,姓名,课程名,成绩,学分FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号

GO12存储过程(2)使用带参数的存储过程[例]从XSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。USEXSCJCREATEPROCEDUREstudent_info1

@namechar(8),@cnamechar(16)ASSELECTa.学号,姓名,课程名,成绩,学分FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号WHEREa.姓名=@nameandt

.课程名=@cnameGO13存储过程(3)使用带有通配符参数的存储过程[例]从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则

使用预设的默认值。USEXSCJCREATEPROCEDUREst_info@namevarchar(30)='刘%'ASSELECTa.学号,a.姓名,c.课程名,b.成绩FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNERJOINKCcONc

.课程号=b.课程号WHERE姓名LIKE@nameGO14存储过程(4)使用带OUTPUT参数的存储过程[例]用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。USEXSCJGOIFEXISTS(SEL

ECTnameFROMsysobjectsWHEREname='totalcredit'ANDtype='P')DROPPROCEDUREtotalcreditGOUSEXSCJGOCREATEPROCEDUREtotalcr

edit@namevarchar(40),@totalintOUTPUTASSELECT@total=SUM(学分)FROMXS,XS_KC,KCWHERE姓名=@nameANDXS.学号=XS_KC.

学号GROUPBYXS.学号GO15存储过程(5)使用OUTPUT游标参数的存储过程OUTPUT游标参数用于返回存储过程的局部游标。[例]在XSCJ数据库的XS表上声明并打开一个游标。USEXSCJIFEXISTS(SELEC

TnameFROMsysobjectsWHEREname='st_cursor'andtype='P')DROPPROCEDUREst_cursorGOCREATEPROCEDUREst_cursor@st_cursorCURSORVARY

INGOUTPUTASSET@st_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMXSOPEN@st_cursorGO16存储过程(6)使用WITHENCRYPTION选项WITHENCRYPTION子句对用户隐藏存储过程的文本。[例]创建加密过程，使用

sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。IFEXISTS(SELECTnameFROMsysobjectsWHEREname='encrypt_this'ANDtype='

P')DROPPROCEDUREencrypt_thisGOUSEXSCJGOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMXSGO17存储过程(7)创建用户定义的系统

存储过程[例]创建一个过程，显示表名以xs开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以kc开头的所有表及对应的索引。IFEXISTS(SELECTnameFROMsysobjectsWHEREname='sp_showtable'

ANDtype='P')DROPPROCEDUREsp_showtableGOUSEmasterCREATEPROCEDUREsp_showtable@TABLEvarchar(30)='kc%'ASSELECTtab.nameASTABLE_NAME,inx.nameASINDE

X_NAME,indidASINDEX_IDFROMsysindexesinxINNERJOINsysobjectstabONtab.id=inx.idWHEREtab.nameLIKE@TABLEGOUSEXSCJEXECsp_sho

wtable'xs%'GO18存储过程2．利用企业管理器创建用户存储过程第1步在SQLServer企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图

所示的快捷菜单。第2步选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口中输入定义的存储过程，如图所示，然后选择“确定”按钮。19用户存储过程的编辑修改语法格式：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_typ

e}[VARYING][0=default][OUTPUT]][,...n1][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n2][例]对存储过程student_in

fo1进行修改。USEXSCJGOALTERPROCEDUREstudent_info1@namechar(8),@cnamechar(16)ASSELECTa.学号,姓名,课程名,成绩,学分FROMXSaINNERjoinXS

_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号WHEREa.姓名=@nameandt.课程名=@cnameGO20用户存储过程的删除使用DROPPROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语

法格式：DROPPROCEDURE{procedure}[,...n][例]删除XSCJ数据库中的student_info1存储过程。USEXSCJGODROPPROCEDUREstudent_info1procedure指要删除的存储过程或存储过程组的名称；n

：表示可以指定多个存储过程同时删除。21存储过程的安全性—示例假定EMP表是某用户USER1的私有表，用户SCOTT是开发者，最终用户GREEN，现在要求GREEN只能通过SCOTT创建的存储过程HIRE

_EMP存取EMP表,该存储过程查询或插入雇员记录.从USER1用户环境下,为SCOTT用户授予对EMP表的相应权限。解：GRANTSELECT，INSERT，UPDATE，DELETEONEMPTOSCOTT；SCOTT一旦创建完

HIRE_EMP过程,给GREEN用户授予对该过程的EXECUTE权限.GRANTEXECUTEONHIRE_EMPTOGREEN;22使用系统存储过程来查看存储过程sp_help：用于显示存储过程的参数及其数据类型sp_h

elp[[@objname=]name]参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]参数name为要查看的存储过程的名称。sp_depends：显示和存储过程相关

的数据库对象sp_depends[@objname=]’object’参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表23触发器触发器是一

种特殊的存储过程，它在特定语言事件发生时自动执行，通常用于实现强制业务规则和数据完整性。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。24触发器主要优点触发器是自动

的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。25利用SQL命令创建触发器语法格式CREATETRI

GGERtrigger_nameON{table|view}/*指定操作对象*/[WITHENCRYPTION]/*说明是否采用加密方式*/{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORR

EPLICATION]/*说明该触发器不用于复制*/AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}

updated_bitmask){comparison_operator}column_bitmask[...n]}]/*两个IF子句用于说明触发器执行的条件*/sql_statement[...n]/*一条或若干条SQL语句*/}}26利用SQL命令创建触发器

2．触发器中使用的特殊表inserted逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表

中。3．使用触发器的限制使用触发器有下列限制：(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方

式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。(5)一个表的外键在DELETE、UPDATE操作上定义了级联，不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。27利用SQL命令

创建触发器(7)在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。(8)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATA

BASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE(9)触发器不能返回任何结果，为了阻止从

触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。4．权限CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。28利用SQL命令创建触发器5．举例[例]对于XSC

J数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。USEXSCJ/*使用带有提示消息的触发器*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='reminder'ANDtype='TR')D

ROPTRIGGERreminderGOCREATETRIGGERreminderONXSFORINSERT,UPDATEASRAISERROR(4008,16,10)GO注：4008为用户自定义消息，可用sp_addmessage将自定义消息

添加到sysmessages表中。29利用SQL命令创建触发器[例]在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USEXSCJIFEXISTS(SELECTname

FROMsysobjectsWHEREname='check_trig'ANDtype='TR')DROPTRIGGERcheck_trigGOCREATETRIGGERcheck_trigONXS_KCFORINSERTASSELECT*FROMinser

tedaWHEREa.学号NOTIN(SELECTb.学号FROMXSb)ORa.课程号NOTIN(SELECTc.课程号FROMKCc)BEGINRAISERROR('违背数据的一致性.',16,1)ROLLBACKTR

ANSACTIONEND30利用SQL命令创建触发器[例]在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。USEXSCJGOCREATETRIGGERupdate_tr

igONXS_KCFORupdateAS/*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/IF(COLUMNS_UPDATED()&3)>0BEGINRAISERROR('违背数据的一致性

.',16,1)ROLLBACKTRANSACTIONENDGO31利用SQL命令创建触发器6.INSTEADOF触发器的设计如果视图的数据来自于多个基表，则必须使用INSTEADOF触发器支持引用表中数据的插入、更新和删除操作。如果视图的列

为以下几种情况之一：（1）基表中的计算列。（2）IDENTITYINSERT为OFF的基表中的标识列。（3）具有timestamp数据类型的基表列。该视图的INSERT语句必须为这些列指定值，INSTEADOF触发器在构成将值插入基表的INSER

T语句时会忽略指定的值。32[例]在XSCJ数据库中创建表、视图和触发器，以说明INSTEADOFINSERT触发器的使用。USEXSCJCREATETABLEbooks(BookKeyintIDENTITY(1,1),BookNamenvarchar(10)NOTNULL,

Colornvarchar(10)NOTNULL,ComputedColAS(BookName+Color),Pagesint)GOCREATEVIEWView2/*建立一个视图，包含基表的所有列*/ASSELECTBookKey,BookName,Color,Computed

Col,PagesFROMbooksGO/*在View2视图上创建一个INSTEADOFINSERT触发器*/CREATETRIGGERInsteadTrigonView2INSTEADOFINSERTASBEGIN/*实际插入时，INSERT语句中不包含BookKey字段和ComputedCo

l值*/INSERTINTObooksSELECTBookName,Color,PagesFROMinsertedENDGO33利用企业管理器创建触发器步骤如下：第1步展开服务器组，然后展开服务器。第2步展开“数据库”文件夹，展开将

定义触发器的表所属的数据库，然后单击“表”文件夹。第3步选择将在其上创建触发器的表右击，出现快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，如图所示。执行该命令后，进入如图所示的界面。第4步在“名称”中，单击“新建”，在“文本”框中输入触发器文本。若要检查

语法，单击“检查语法”命令。34触发器的修改1．利用SQL命令修改触发器语法格式：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{{(FOR|AFTER|INSTEADOF){[DELETE][,][INSER

T][,][UPDATE]}[NOTFORREPLICATION]ASsql_statement[...n]}|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}[NOTFORREPLICATIO

N]AS{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){co

mparison_operator}column_bitmask[...n]}sql_statement[...n]}35触发器的修改[例]修改XSCJ数据库中在XS表上定义的触发器reminder。USEXSCJALT

ERTRIGGERreminderONXSFORUPDATEASRAISERROR(“执行的操作是修改”,16,10)GO2.通过企业管理器修改触发器进入企业管理器，修改触发器的步骤与创建的步骤相同，进入界面后在“名称”对应的下

拉表中选择要修改的触发器名即可进入触发器修改状态。说明：trigger_name：指要更改的现有触发器。如果原来的触发器定义是用WITHENCRYPTION或RECOMPILE创建的，那么只有在ALTERTRIGGER中也包含这些选项时，这些选项才有效。其

他参数含义参考创建触发器命令部分。36触发器的删除1.利用SQL命令删除触发器语法格式：DROPTRIGGER{trigger}[,...n]说明：trigger：指要删除的触发器名称，包含触发器所有者名。n：表示可以指定

多个触发器。[例]删除触发器reminder。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGO2．通过企业管理器删除触发器进入界面后在“名称”对应的

下拉表中选择要删除的触发器名，然后选择“删除”按钮。37sp_help、sp_helptext和sp_depends具体用途和语法形式sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help‘触发器名称’sp_helptext：用于查看触发器的正文信息

sp_helptext‘触发器名称’sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends’触发器名称’sp_depends‘表名’38附例：用户登录验证CREATEPROCupUserLogin@strLoginName

NVARCHAR(20),@strLoginPwdNVARCHAR(20),@blnReturnBITOUTPUTAS--定义一个临时用来保存密码的变量DECLARE@strPwdNVARCHAR(20)BEGIN--从表中查询当前用户的

密码，赋值给@strPwd变量，下面要对它进行比较SELECT@strPwd=uLoginPwdFROMuUserWHEREuLoginName=@strLoginNameIF@strLoginPwd=@strPwdBEGINSET@blnRet

urn=1--更新用户最后登录时间UPDATEuUserSETuLastLogin=GETDATE()WHEREuLoginName=@strLoginNameENDELSESET@blnReturn=0END39附例：在sqlserver教学数据库pub中查询state为ut的作者所

出版的著作CREATEPROCEDUREpub_testASBEGINSETNOCOUNTONDECLARE@au_idvarchar(11),@au_fnamevarchar(20),@au_lnamevarchar(40),@mes

sagevarchar(80),@titlevarchar(80)PRINT'--------UtahAuthorsreport--------'DECLAREauthors_cursorCURSORFORSELECTau_

id,au_fname,au_lnameFROMauthorsWHEREstate='UT'ORDERBYau_idOPENauthors_cursorFETCHNEXTFROMauthors_cursorINT

O@au_id,@au_fname,@au_lnameWHILE@@FETCH_STATUS=0BEGINPRINT''SELECT@message='-----BooksbyAuthor:'+@au_fname+''+@au_lnamePRINT@message40DECLAREti

tles_cursorCURSORFORSELECTt.titleFROMtitleauthorta,titlestWHEREta.title_id=t.title_idANDta.au_id=@au_id--Variabl

evaluefromtheoutercursorOPENtitles_cursorFETCHNEXTFROMtitles_cursorINTO@titleIF@@FETCH_STATUS<>0PRINT'<<No

Books>>'WHILE@@FETCH_STATUS=0BEGINSELECT@message=''+@titlePRINT@messageFETCHNEXTFROMtitles_cursorINTO@titleENDCLOSEtitles_cursorDEALLOCATEtitles_c

ursor--Getthenextauthor.FETCHNEXTFROMauthors_cursorINTO@au_id,@au_fname,@au_lnameENDCLOSEauthors_cursorDEALLOCATEa

uthors_cursorEND