chp5数据库完整性new2

《chp5数据库完整性new2》由会员分享，可在线阅读，更多相关《chp5数据库完整性new2（48页珍藏版）》请在金锄头文库上搜索。

1、数据库原理与应用数据库原理与应用刘伟法刘伟法( () )常熟理工学院计算机科学与工程系常熟理工学院计算机科学与工程系第第5章章 数据库完整性数据库完整性l数据库完整性机制数据库完整性机制l触发器触发器l-l存储过程存储过程数据库的完整性是指数据库的完整性是指数据的正确性数据的正确性和和相容性相容性。完整性检查和控制的防范对象是不合语义的、不完整性检查和控制的防范对象是不合语义的、不正确的数据。正确的数据。DBMS对完整性的维护机制：对完整性的维护机制：1、提供定义完整性约束条件的机制、提供定义完整性约束条件的机制2、提供完整性检查的方法、提供完整性检查的方法3、违约处理、违约处理5.1 实体完

2、整性实体完整性1、实体完整性定义、实体完整性定义PRIMARYKEY2、实体完整性检查实体完整性检查主码值必须惟一主码值必须惟一主码不能为空主码不能为空5.2 参照完整性参照完整性1、参照完整性定义、参照完整性定义FOREIGNKEY(属性属性列列)REFERENCES表名表名(主码主码)或：或：foreignkey(属性列属性列)references表表名名(主码主码)onupdatecascade|noactionondeletecascade|noaction2、参照完整性检查和处理参照完整性检查和处理(例例student与与sc之间的之间的参照关系参照关系)l拒绝执行拒绝执行l级联操作

3、级联操作l设置为空值：例：学生表设置为空值：例：学生表与与专业表专业表5.3 用户自定义完整性用户自定义完整性1、不允许取空值、不允许取空值NOTNULL2、列值惟一列值惟一UNIQUE3、CHECK子句子句CHECK(约束条件表达式约束条件表达式)元组级约束条件，例：元组级约束条件，例：check(ssex=女女orsnamenotlikeMs.%)例lCreatetablemajor（lmajornochar(6),lmajornamevarchar(20)l)lCreatetablestudent(lSnochar(10),lSnamevarchar(10),lSsexchar(2),l

4、Sagetinyint,lMajornochar(6)l)Createtablecourse(Cnochar(3),Cnamevarchar(20)CCREDITNUMERIC(5,1)5.4 触发器触发器1、触发器、触发器(Trigger)触发器是用户定义在关系表上的一类由事触发器是用户定义在关系表上的一类由事件驱动的特殊存储过程。件驱动的特殊存储过程。当发生表的当发生表的UPDATE、DELETE、INSERT操操作时，触发器生效。作时，触发器生效。5.4 触发器（续）触发器（续）2、使用触发器的优点、使用触发器的优点触发器是自动的；触发器是自动的；触发器可以通过数据库中的相关表进行层触发

5、器可以通过数据库中的相关表进行层叠更改。叠更改。触发器可以强制限制，这些限制比用触发器可以强制限制，这些限制比用CHECK约束约束所定义的更复杂。所定义的更复杂。5.4 触发器（续）触发器（续）3、定义触发器、定义触发器(sqlserver为例为例)CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETEASsql_statement.n加密触发器激活的顺序，AFTER表示执行操作后，INSTEAD OF 操作前。触发器激活后所作的操作，例如检查，回滚，插入，删除等5.4

6、 触发器（续）触发器（续）4、AFTER和和INSTEADOF的区别的区别AFTER:触发发生在触发发生在SQL操作后，若发生错误，可操作后，若发生错误，可利用回滚恢复原数据。利用回滚恢复原数据。INSTEADOF：触发发生在触发发生在SQL操作前，且如何操操作前，且如何操作由触发器操作来实现。作由触发器操作来实现。INSTEADOF触发器的主要优点是可以使不能更触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用新的视图支持更新。基于多个基表的视图必须使用INSTEADOF触发器来支持引用多个表中数据的触发器来支持引用多个表中数据的插入、更新和删除操作。插入、更新和删除

7、操作。每个表只能有一个同类型的每个表只能有一个同类型的INSTEADOF触发器触发器lAfter触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用RollbackTransaction语句来回滚本次的操作。lInsteadOf触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。创建Trigger说明：lAfter只能用于数据表上，insteadof可以用于表与视图上l允许建立多个同类操作的a

8、fter触发器，但同类的insteadof触发器只能有一个l只有after触发器才能设置执行次序l可以设置为first或lastl可以用系统存储过程sp_settriggerorder设置lsp_settriggerordertriggername=MyTrigger,order=first,stmttype=UPDATEl在对基础表进行更新前激发INSTEADOF触发器lALTERTRIGGER语句更改了第一个或最后一个触发器，则将除去First或Last特性l两个触发器完全相同，会先执行后建立的触发器。执行的顺序与建立的顺序相反。l创建触发器使得course表插入数据后自动显示course

9、的内容l创建触发器替代course表的插入或更新操作，显示course的内容5.4 触发器（续）触发器（续）5、DELETED表和表和INSERTED表表(内存表内存表)DELETED表用于存储表用于存储DELETE和和UPDATE语语句所影响的行的副本。句所影响的行的副本。INSERTED表用于存储表用于存储INSERT和和UPDATE语句所影响的行的副本。语句所影响的行的副本。INSERTED表中的行是表中的行是触发器表中新行的副本。触发器表中新行的副本。例：创建触发器使得course表插入，更新，删除数据后自动显示DELETED表和表和INSERTED表表的内容。5.4 触发器（续）触发

10、器（续）CREATETRIGGERtri_xgONCOURSEFORINSERT,UPDATEASIF(SELECTcount(*)FROMCOURSE,INSERTEDWHERECOURSE.Cname=INSERTED.Cname)1BEGINPRINTNOTINSERTROLLBACKTRANSACTIONEND例例1在在COURSE表上创建触发器，检查插入和更新时是否出现表上创建触发器，检查插入和更新时是否出现课程名相同的记录，若有则不插入。课程名相同的记录，若有则不插入。触发器名字判断插入或更新的数据是否与表中课程名相同操作回滚lcreatetriggertri_xg1oncours

11、elforinsert,updatelaslif(selectcount(*)fromcourselwherecname=(selectcnamefrominserted)1lbeginlrollbacktransactionlprintcannotbeinsertedlend5.4 触发器（续）触发器（续）例例2插入选课记录时，检查若选修课的人数已满插入选课记录时，检查若选修课的人数已满5人，则拒绝人，则拒绝插入。插入。CREATETRIGGERtri_XKONSCFORINSERTASIF(SELECTCOUNT(*)FROMSCWHERECNO=(selectCNOfromINSERTE

12、D)5BEGINPRINT选课已满选课已满ROLLBACKENDl检查触发器内容：lsp_helptext触发器名称l测试触发器5.4 触发器（续）触发器（续）例例3在订单表在订单表(ORDER)中插入或修改订购数量中插入或修改订购数量(NUM),必须满足必须满足客户相应等级的数量上限和下限。客户相应等级的数量上限和下限。(CUSTOMER,CUSTOMERLEVEL,ORDER表表)lcreatetablecustomerlevell(lclevelsmallintidentity(1,1)primarykey,lnumupint,lnumdownintl)lgolcreatetablecu

13、stomerl(lcidintidentity(1,1)primarykey,lcnamevarchar(20),lclevelsmallintforeignkeyreferencescustomerlevel(clevel)l)lgolcreatetableorderl(lorderidintidentity(1,1)primarykey,lcidintforeignkeyreferencescustomer(cid),lnumintl)lgolinsertintocustomer(cname)values(John)linsertintocustomer(cname)values(Alic

14、e)linsertintocustomer(cname)values(White)lgolinsertintocustomerlevelvalues(1000,100)linsertintocustomerlevelvalues(5000,1000)linsertintocustomerlevelvalues(2000,1000)lgolupdatecustomersetclevel=1wherecid=1lupdatecustomersetclevel=2wherecid=2lupdatecustomersetclevel=3wherecid=3lgolinsertintoordervalu

15、es(1,600)linsertintoordervalues(2,1500)linsertintoordervalues(1,800)lgo5.4 触发器（续）触发器（续）CREATETRIGGERCHECK_NUMONORDERFORINSERT,UPDATEASIFUPDATE(NUM)BEGINIFEXISTS(SELECT*FROMINSERTED,CUSTOMER,CUSTOMERLEVELWHEREINSERTED.CID=CUSTOMER.CIDANDCUSTOMERLEVEL.CLEVEL=CUSTOMER.CLEVELANDNUMNOTBETWEENCUSTOMERLEVE

16、L.NUMDOWNANDCUSTOMERLEVEL.NUMUP)BEGINPRINT订购数量违反了客户的限额！订购数量违反了客户的限额！ROLLBACKENDENDlcreatetriggertri_checknumonorderlforinsert,updatelaslifupdate(num)lbeginlifnotexists(select*fromcustomerc,customerlevelcl,insertedwherec.cid=inserted.cidandc.clevel=cl.clevelandinserted.numbetweencl.numdownandcl.numup

17、)lbeginlprint订单数量违反限额！lrollbacklendlendl经常在school数据库中查找以下信息：学号、姓名、课程号、课程名、成绩，l请建立视图vwScore简化该查询。lcreateviewvwscorelaslselectst.sno,st.sname,o,ame,sc.gradefromsc,student,coursewheresc.sno=o=ol并利用视图查看这些数据；l利用视图更新学号为9521101,课程号为c01的成绩改为78，课程名改为大学物理。l（使用替代触发器实现仅用一个update语句完成2个更新.）l（测试方法： updatevwscorese

18、tgrade=78,cname=大学物理wheresno=9521101andcno=c01)lcreate trigger tri_upscore lon vwscore linstead of update las lbegin lupdate sc set grade=(select grade from inserted) where sno=(select sno from deleted) and cno=(select cno from deleted) lupdate course set cname=(select cname from inserted) where cno

19、=(select cno from deleted) lend例例4创建一个包括学生编号和教师编号、姓名以及创建一个包括学生编号和教师编号、姓名以及类别的视图，建立更新触发器，使更新结果返回到类别的视图，建立更新触发器，使更新结果返回到源基本表中。源基本表中。什么视图是可更新的？什么视图是可更新的？行列子集视图行列子集视图建立建立INSTEADOF触发器，根据类别更新相应的触发器，根据类别更新相应的基本表。基本表。5.4 触发器（续）触发器（续）创建一个包括学生编号和教师编号、姓名以及类别创建一个包括学生编号和教师编号、姓名以及类别的视图的视图,并创建替代触发器完成对姓名的更改并创建替代触发器

20、完成对姓名的更改lcreateviewJS_AND_ST1(pid,xm,lb)laslselectxh,xm,studentfromxs$lunionlselectgh,xm,teacherfromjs$行列子集视图说明行列子集视图说明l创建计算机系学生的视图l修改一个计算机系学生l修改一个非计算机系学生l将一个计算机系学生的系别改为机械系l插入一个计算机系的学生l插入一个非计算机系的学生l创建一个带withcheckoption的视图，再测试：l将一个计算机系学生得系别改为机械系l插入一个非计算机系的学生CREATETRIGGERUPDATE_1ONJS_AND_ST1INSTEADOFU

21、PDATEASIFUPDATE(XM)BEGINDECLARELBNVARCHAR(15)DECLAREPIDNVARCHAR(255)DECLAREXMNVARCHAR(255)SETLB=(SELECTLBFROMINSERTED)SETPID=(SELECTPIDFROMINSERTED)SETXM=(SELECTXMFROMINSERTED)IFLB=STUDENTBEGINUPDATEXS$SETXM=XMWHEREXH=PIDENDIFLB=TEACHERBEGINUPDATEJS$SETXM=XMWHEREGH=PIDENDENDSQL编程编程使用变量和参数使用变量和参数1、Tr

22、ansact-SQL局部变量：局部变量：Transact-SQL变量是变量是Transact-SQL批处理和脚本中能够保存数据值的对象。批处理和脚本中能够保存数据值的对象。声明或定义变量后，批处理中的一个声明或定义变量后，批处理中的一个Transact-SQL语句可以设置变量值，而语句可以设置变量值，而该批处理中后面的语句可以从变量获得此该批处理中后面的语句可以从变量获得此值。例如：值。例如：计算2个数的和lDeclare x intlDeclare x intlDeclare x intlSetx=10lSety=20lSetz=x+ylSelectx,y,z查询指定学号的学生详细信息l例如

23、：例如：lDeclare SnoVar char(10)lSet snovar=9521101lSELECT *lFROM studentlWHERE sno = SnoVar使用变量和参数使用变量和参数2、Transact-SQL参数参数：参数参数是在是在存储过程存储过程和和执行执行该该存储过程存储过程的的批处理批处理或或脚本之间传递脚本之间传递数据数据的的对象对象。参数可以是输入参数也可以是输出参数参数可以是输入参数也可以是输出参数。例如：。例如：Create procedure parasample SnoVar char(10)asSELECT *FROM studentWHERE s

24、no = SnoVarExec parasample95211015.5 存储过程存储过程存储过程存储过程：SQL语句和控制流语句的预编译集语句和控制流语句的预编译集合。合。CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nASsql_statement.n存储过程名存储过程名过程的参数，存过程的参数，存储过程最多可以储过程最多可以有有 2100 2100 个参数。个参数。参数不能用于代参数不能用于代替表名、列名或替表名、列名或其它数据库对象其它数据库对象的名称的名称。 标明参数为返回参数

25、标明参数为返回参数过程中要包含的任意数目和类型的过程中要包含的任意数目和类型的 Transact-SQL 语句。语句。 5.5 存储过程（续）存储过程（续）例例1 创建不带参数的存储过程：查询计算机系学生的考试成创建不带参数的存储过程：查询计算机系学生的考试成绩，列出学生的姓名、课程名和成绩绩，列出学生的姓名、课程名和成绩CREATEPROCSTUDENT_GRADE1ASSELECTSNAME,CNAME,GRADEFROMSTUDENT,SC,COURSEWHERESTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNOANDSDEPT=计算机系计算机系执行存储过程：执

26、行存储过程：EXECSTUDENT_GRADE15.5 存储过程（续）存储过程（续）例例2 创建带有输入参数的存储过程：查询某个指定系学生的创建带有输入参数的存储过程：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩考试情况，列出学生的姓名、所在系、课程名和考试成绩CREATEPROCSTUDENT_GRADE2DEPTCHAR(20)ASSELECTSNAME,SDEPT,CNAME,GRADEFROMSTUDENT,SC,COURSEWHERESTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNOANDSDEPT=DEPT执行存储过程：执行存储过

27、程：EXECSTUDENT_GRADE2信息系信息系5.5 存储过程（续）存储过程（续）例例3创建带有多个输入参数的存储过程：查询某个学生的创建带有多个输入参数的存储过程：查询某个学生的某门课程的考试成绩、列出学生的姓名、课程名和成绩某门课程的考试成绩、列出学生的姓名、课程名和成绩CREATEPROCSTUDENT_GRADE3STUDENT_NAMECHAR(10),COURSE_NAMECHAR(20)ASSELECTSNAME,CNAME,GRADEFROMSTUDENT,SC,COURSEWHERESTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNOANDSNA

28、ME=STUDENT_NAMEANDCNAME=COURSE_NAME执行存储过程：执行存储过程：EXECSTUDENT_GRADE3刘晨刘晨,VB5.5 存储过程（续）存储过程（续）例例4 4 创建带有多个输入参数并有默认值的存储过程：创建带有多个输入参数并有默认值的存储过程：查询某个学生的某门课程的考试成绩、列出学生查询某个学生的某门课程的考试成绩、列出学生的姓名、课程名和成绩，若没有指定课程，则默的姓名、课程名和成绩，若没有指定课程，则默认课程为数据库基础。认课程为数据库基础。CREATEPROCSTUDENT_GRADE4STUDENT_NAMECHAR(10),COURSE_NAME

29、CHAR(20)数据库基础数据库基础ASSELECTSNAME,CNAME,GRADEFROMSTUDENT,SC,COURSEWHERESTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNOANDSNAME=STUDENT_NAMEANDCNAME=COURSE_NAME执行存储过程：执行存储过程：EXECSTUDENT_GRADE4刘晨刘晨5.5 存储过程（续）存储过程（续）例例5创建带有输出参数存储过程：统计指定课程名的平均成创建带有输出参数存储过程：统计指定课程名的平均成绩，并将统计的结果用输出参数返回。绩，并将统计的结果用输出参数返回。CREATEPROCAVG

30、GRADECNCHAR(10),AVGGRADEINTOUTPUTASSELECTAVGGRADE=AVG(GRADE)FROMSC,COURSEWHERESC.CNO=COURSE.CNOANDCNAME=CN执行存储过程：执行存储过程：DECLAREACGGRADEINTEXECAVGGRADEVB,AVGGRADEOUTPUTPRINTAVGGRADE5.5 存储过程（续）存储过程（续）例例6建立带通配符参数的存储过程：根据姓名查询建立带通配符参数的存储过程：根据姓名查询学生的基本情况。学生的基本情况。CREATEPROCS_NAMESNVARCHAR(10)=%ASSELECT*FRO

31、MSTUDENTWHERESNAMELIKESN执行存储过程：执行存储过程：EXECS_NAME李李%5.5 存储过程（续）存储过程（续）例例7建立存储过程建立存储过程check_Xk，功能是根据提供的功能是根据提供的参数学号和课程号，完成选课记录的插入功能。要参数学号和课程号，完成选课记录的插入功能。要求如下：求如下：首先检查该课程选课人数是否已满（可自己根据表首先检查该课程选课人数是否已满（可自己根据表中数据的情况定义一个限定值例如中数据的情况定义一个限定值例如5），若满，则），若满，则返回一个返回一个0；若不满，继续检查该学生是否已经选；若不满，继续检查该学生是否已经选满满3门课程，若满

32、，则返回门课程，若满，则返回1，否则将选课记录插，否则将选课记录插入到入到SC表中，并返回表中，并返回1。lcreate procedure check_xk sno char(10),cno char(10)laslif (select count(*) from sc where cno=cno)=5lreturn 0lelselbeginlif (select count(*) from sc where sno=sno)=3lreturn -1lelselbeginl insert into sc(sno,cno) values(sno,cno)l return 1lendlendl测

33、试：ldeclareiintlexeci=check_xk9512102,C09lprintil教材p248例10利用存储过程来实现下面的应利用存储过程来实现下面的应用用: 从一个账户转指定数额的款项到另一个账户从一个账户转指定数额的款项到另一个账户中。中。要求如下：要求如下：首先检查转出账户是否为空，若空，则返回一个首先检查转出账户是否为空，若空，则返回一个1；再检查转出账户是否够转出的数量，若不足，则再检查转出账户是否够转出的数量，若不足，则返回返回1，否则完成转帐，并返回，否则完成转帐，并返回0。并使用事务。并使用事务确保数据的一致。确保数据的一致。lcreate procedure t

34、ransfer inAccount int,outAccount int,amount moneylasldeclare totalDeposit moneylbeginlselect totalDeposit=total from account where accountnum=outAccountlif totalDeposit is null lbeginlreturn -1lendlif totalDepositamount lbeginlreturn 1lendlelselbeginlbegin transactionlupdate account set total=total-

35、amount where accountnum=outaccountlupdate account set total=total+amount where accountnum=inaccountlcommitlreturn 0lendlendldeclarereturnvalueintlexecreturnvalue=transfer1001,1002,1500lprintreturnvalue:+cast(returnvalueaschar)附：创建表account的sql语句lcreatetableaccountl(lAccountNumintprimarykey,lTotalmoneyl)lgolinsertintoaccountvalues(1001,500)linsertintoaccountvalues(1002,12000)lgo小结小结l 三类完整性约束l 触发器的作用l 存储过程的使用