第三章存储过程与触发器

第三章存储过程与触发器1第1页，课件共40页，创作于2023年2月第4章存储过程和触发器

4.1存储过程4.2触发器第2页，课件共40页，创作于2023年2月4.1存储过程4.1.1存储过程基本概念4.1.2创建和执行存储过程4.1.3存储过程的修改和删除第3页，课件共40页，创作于2023年2月4.1.1存储过程基本概念存储过程是指经过预先编译的SQL语句的集合，可以以一种可执行的形式永久地存储在数据库中。需要时只需调用该过程就可以完成相应的操作。存储过程类似于程序设计语言中的过程和函数。第4页，课件共40页，创作于2023年2月4.1.1存储过程基本概念(续)存储过程具有以下优点。（1）运行速度快（2）模块化编程，增强代码的重用性和共享性（3）减少网络通信量（4）保证系统的安全性第5页，课件共40页，创作于2023年2月4.1.1存储过程基本概念(续)在SQLServer中，存储过程有两种类型：系统存储过程用户定义的存储过程系统存储过程存放在master数据库中并以sp_为前缀，主要是从系统表中获取信息，为系统管理员管理SQLServer提供支持。用户自定义存储过程是由用户创建并能完成某一特定功能的存储过程。对于用户创建的存储过程，尽量不要以sp_作为其名称的前缀。第6页，课件共40页，创作于2023年2月4.1.2创建和执行存储过程一、创建存储过程T-SQL语句格式：CREATEPROC[EDURE]procedure_name[@parameterdata_type[=default][OUTPUT]][,…][WITHENCRYPTION]ASsql_statement；procedure_name：存储过程名parameter：存储过程中定义的输入/输出参数data_type：参数的数据类型default：定义参数的默认值OUTPUT：输出参数WITHENCRYPTION：加密存储过程sql_statement：存储过程中要执行的T-SQL语句第7页，课件共40页，创作于2023年2月一、创建存储过程(续)创建存储过程时，需要注意以下几点：（1）CREATEPROCEDURE不能与其他的T-SQL语句位于同一个批处理中。（2）创建存储过程的权限默认属于数据库拥有者。（3）只能在当前数据库中创建存储过程。（4）成功执行CREATEPROCEDURE语句后，存储过程名称将存储在sysobjects系统表中，而CREATEPROCEDURE语句的文本将存储在syscomments中。（5）存储过程可以嵌套调用。（6）存储过程中不允许使用创建数据库对象的语句。第8页，课件共40页，创作于2023年2月一、创建存储过程(续)例4.1在学生-课程数据库中创建一个存储过程，查看“张明”同学的有关基本信息和选修课程的情况。

CREATEPROCEDUREstudent_course

ASSELECTStudent.Sno,Sname,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDSname='张明'; 为了避免出现重复的存储过程名称而无法建立该存储过程，可以先使用以下语句：

IFEXISTS（SELECTnameFROMsysobjectsWHEREname='student_course'ANDtype='P')

DROPPROCEDUREstudent_course；第9页，课件共40页，创作于2023年2月一、创建存储过程(续)系统存储过程sp_helptext： 查看存储过程中的SQL语句系统存储过程sp_depends： 查看存储过程使用的对象以及调用该存储过程的其他存储过程的名称。

第10页，课件共40页，创作于2023年2月二、执行存储过程EXECUTE语句格式如下：[EXEC[UTE]][@return_status=]<procedure_name>[[@parameter=]{value|@variable[OUTPUT]}[,...n]];-return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在EXECUTE语句之前，必须声明过。-procedure_name：被调用执行的存储过程名。-parameter：是CREATEPROC语句中定义的存储过程参数。在以@parameter_name

=

value格式使用时，参数名称和常量不一定按照CREATEPROC语句中定义的顺序出现，但是若有一个参数使用@parameter_name=value格式，则其他所有参数都必须使用这种格式。-variable：保存输入参数或者输出参数值的变量。-OUTPUT：输出参数。存储过程的匹配参数也须指定关键字OUTPUT。第11页，课件共40页，创作于2023年2月二、执行存储过程(续)例4.2调用例4.1定义的存储过程的方法为 EXECUTEstudent_course;即可查询“张明”同学的相关信息。第12页，课件共40页，创作于2023年2月三、通过存储过程传递参数

参数：输入参数和输出参数输入参数：当程序执行存储过程时，可以通过输入参数向该存储过程传递值，这些值可作为SQL编程语言中的标准变量使用输出参数：存储过程也可以通过OUTPUT参数将值返回至调用程序。 一个存储过程可以定义多达1

024个参数，每个参数必须指定参数名和数据类型，并且参数名必须以“＠”符号作为前缀，也可以为参数指定默认值。第13页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)（1）带输入参数的存储过程例4.3可以查询任意一个学生的选修信息。可将学生姓名作为输入参数把值传递给存储过程。 CREATEPROCEDUREstudent_course1

＠StudentNameVARCHAR(10) ASSELECTStudent.Sno,Sname,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDSname=＠StudentName；第14页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)调用该存储过程的方法为 EXECUTEstudent_course1'张明'；或 EXECUTEstudent_course1

＠StudentName='张明'；第15页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)例4.4创建一个存储过程用于向学生表中插入记录。CREATEPROCEDUREstudent_insert@xhCHAR(6),

@nameVARCHAR(8),

@sexCHAR(2),@ageINT,@sdepCHAR(10)ASINSERTINTOStudentVALUES(@xh,@name,@sex,@age,@sdep);该存储过程通过参数将学生的有关情况传递给存储过程。调用该存储过程的方法为EXECUTEPROCEDUREstudent_insert'950002','李明','男',20,'CS'；第16页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)（2）使用默认参数值例4.5创建一个存储过程，若没有给出学生姓名，则返回所有学生的有关情况。CREATEPROCEDUREstudent_course2@StudentNameVARCHAR(10)=NULLAS IF@StudentNameISNULL BEGIN SELECTStudent.Sno,Sname,Cname,Grade FROMStudent,Course,SC WHEREStudent.Sno=SC.SnoAND Course.Cno=SC.CnoENDELSE第17页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)(续上页)ELSEBEGINSELECTStudent.Sno,Sname,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDSname=@StudentNameEND第18页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)（2）使用输出参数

例4.6创建一个存储过程，返回选修某门课程的学生人数。CREATEPROCEDUREstudent_count@CourseNameVARCHAR(20),@StudentSumINTOUTPUTASSELECT@StudentSum=COUNT(*)FROMCoures,SCWHERECourse.Cno=SC.CnoANDCname=@CourseName；调用该存储过程的例子：DECLARE@studentNumINT;EXECUTEstudent_count'数据库原理及应用',@studnetNumOUTPUT;SELECT'Theresultis：',@studentNum;第19页，课件共40页，创作于2023年2月三、通过存储过程传递参数(续)例4.7创建一个存储过程，输出学生的基本情况。CREATEPROCEDUREstudent_query@xhCHAR(6),@nameVARCHAR(8)OUTPUT,@sexCHAR(2)OUTPUT,@ageINTOUTPUTASSelect@name=Sname,@sex=Ssex,@age=SageFROMStudentWHERESno=@xh;调用该存储过程，查询学号为“950001”同学的相关信息DECLARE@nameVARCHAR(8);DECLARE@sexCHAR(2);DECLARE@ageINT;EXECUTEstudent_query'950001',@nameOUTPUT,@sexOUTPUT,@ageOUTPUT;第20页，课件共40页，创作于2023年2月4.1.3存储过程的修改和删除一、修改存储过程T-SQL语句格式：ALTERPROC[EDURE]procedure_name[@parameterdata_type[=default][OUTPUT]][,…][WITHENCRYPTION]ASsql_statement；二、删除存储过程T-SQL语句格式： DROPPROCEDUREprocedure_name;第21页，课件共40页，创作于2023年2月4.2触发器4.2.1触发器基本概念4.2.2定义触发器4.2.3激活触发器4.2.3删除触发器第22页，课件共40页，创作于2023年2月4.2触发器触发器（Trigger）是由用户定义在关系表上的一类特殊过程，该过程一经定义，任何用户对表的增加、删除、更新操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施比约束更加复杂的检查和操作，具有更强大的数据控制能力。第23页，课件共40页，创作于2023年2月4.2.1触发器基本概念触发器的定义：满足某个特定条件时自动触发执行的特殊存储过程，用于保证表中的数据遵循数据库设计者确定的规则和约束。存储过程是通过名称而被显式调用执行，而触发器不能被显式调用，只能通过事件触发而执行第24页，课件共40页，创作于2023年2月4.2.1触发器基本概念(续)触发器常用于强制业务规则和数据完整性，常常用于下列几种情况：（1）触发器可以通过级联的方式对相关的表进行修改。（2）触发器可以实施比CHECK约束更复杂的约束。（3）触发器可以根据改变前后表中数据的不同来进行相应的操作。（4）对于一个表上的不同操作可以采用不同的触发器，即使是对相同的语句也可以调用不同的触发器完成不同的操作。第25页，课件共40页，创作于2023年2月4.2.1触发器基本概念(续)SQLServer中提供两种触发：

AFTER触发器

INSTEADOF触发器一个触发器与3部分的内容有关：定义触发器的表激活触发器的操作语句触发器被激活时进行的操作第26页，课件共40页，创作于2023年2月4.2.1触发器基本概念(续)在使用触发器的过程中，SQLServer用到了两个特殊的临时表：inserted表和deleted表。它们与创建触发器的表具有相同的结构。触发器执行完毕后，这两个表自动被删除。inserted表： 该表中存放的是由于执行INSERT或UPDATE语句而要向表中插入的新数据行。deleted表： 该表中存放的是由于执行DELETE或UPDATE语句而要从表中删除的数据行。第27页，课件共40页，创作于2023年2月4.2.2定义触发器创建触发器的语句格式为：CREATETRIGGERtrigger_nameON {table_name|view_name} [WITHENCRYPTION] {FOR|AFTER|INSTEADOF} [INSERT,UPDATE,DELETE]

AS sql_statement第28页，课件共40页，创作于2023年2月4.2.2定义触发器(续)例4.8创建一个触发器，当学生表中的记录被更新时，显示表中的所有记录。CREATETRIGGERstudent_change

ONStudentAFTERINSERT,UPDATE,DELETEASSELECT*FROMStudent;第29页，课件共40页，创作于2023年2月4.2.2定义触发器(续)例4.9在Student表上创建DELETE触发器，实现Student表和SC表的级联删除。CREATETRIGGERstudentDelete

ONStudentAFTERDELETE AS DELETEFROMSC WHERESnoIN (SELECTdeleted.Sno FROMdeleted);第30页，课件共40页，创作于2023年2月4.2.2定义触发器(续)例4.10在SC表上创建INSERT触发器，当向SC表中添加学生选课记录时，检查该学生的Sno是否存在。若不存在，则不能将记录插入。 CREATETRIGGERsc_insert

ONSCAFTERINSERT AS IF(SELECTCOUNT(*) FROMStudent,inserted WHEREStudent.Sno=inserted.Sno)=0 BEGIN PRINT'学号不存在，不能插入该记录'

ROLLBACKTRANSACTION END;第31页，课件共40页，创作于2023年2月4.2.2定义触发器(续)例4.11创建UPDATE触发器，禁止对Student表中学生的学号进行修改。CREATETRIGGERstudent_update

ONStudentAFTERUPDATE AS IFUPDATE(Sno) BEGIN PRINT'学生的学号不能修改'

ROLLBACKTRANSACTION END;第32页，课件共40页，创作于2023年2月4.2.2定义触发器(续)例4.12在SC表上创建触发器，当一次向SC表中添加多个记录时，删除学号在Student表中不存在的记录，从而保证数据的一致性。注意，不能在SC表中定义外键约束。CREATETRIGGERsc_insertONSCAFTERINSERT ASIF(SELECTCOUNT(*) FROMStudent,inserted WHEREStudent.Sno=inserted.Sno)<> @@ROWCOUNTBEGIN

DELETEFROMSCWHERESnoNOTIN(SELECTSnoFROMStudent)END;第33页，课件共40页，创作于2023年2月4.2.2定义触发器(续)例4.13在视图上定义INSTEADOF触发器。在第3章曾经介绍，视图中有导出列时不能对视图进行更新，但是，可以利用INSTEADOF触发器对视图进行更新。假设有一个反映学生出生年份的视图CREATEVIEWbirth_view(Sno,Smame,Ssex,Sbirth,Sdept)ASSELECTSno,Sname,Ssex,2008-Sage,SdeptFROMstudent;第34页，课件共40页，创作于2023年2月4.2.2定义触发器(续)CREATETRIGGERbirth_view_insertONbirth_viewINSTEADOFINSERTASDECLARE@SnoCHAR(6)DECLARE@SnameVARCHAR(10)DECLARE@SsexCHAR(2)DECLARE@birthINTDECLARE@SageINTDECLARE@SdeptCHAR(20)SELECT@Sno=Sno,@Sname=Sname,@Ssex=Ssex,@birth=Sbirth,@Sdept=SdeptFROMinsertedSET@Sage=2008－@birthINSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept）VALUES（@Sno,@Sname,@Ssex,@Sage,@Sdept）;第