7-3 数据库完整性与安全

1/数据库原理与应用滁州学院计算机与信息工程学院2/第12讲数据库完整性与安全•教学目标：–了解存储过程和触发器。3/目录12存储过程触发器4/1存储过程•存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合–该集合编译后存放在数据库中，可根据实际情况重新编译；–存储过程可直接运行，也可远程运行；–存储过程直接在服务器端运行。•使用存储过程具有如下优点：–将业务操作封装•可为复杂的业务操作编写存储过程，放在数据库中；•用户可调用存储过程执行，而业务操作对用户是不可见的；•若存储过程仅修改了执行体，没有修改接口，则用户程序不需要修改，达到业务封装的效果。–便于事务管理•事务控制可以用在存储过程中；•用户可依据业务的性质定义事务，并对事务进行相应级别的操作。5/1存储过程–实现一定程度的安全性保护•存储过程存放在数据库中，且在服务器端运行；•对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性；•这种安全性缘于用户对存储过程只有执行权限，没有查看权限；•拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图的操作权限；•这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。–注意：对存储过程只需授予执行权限，不需授予表或视图的操作权限。–特别适合统计和查询操作•一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大；•很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程。6/1存储过程–减少网络通信量•存储过程仅在服务器端执行，客户端只接收结果；•由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。–使用存储过程前，首先要创建存储过程。可对存储过程进行修改和删除。–创建存储过程后，必须对存储过程授予执行EXECUTE的权限，否则该存储过程仅可以供创建者执行。•1.1创建存储过程•1.2执行存储过程•1.3修改和删除存储过程7/1.1创建存储过程•语法：CREATEPROCEDUREprocedureName[(@parameterNamedatatype[=defaultValue][OUTPUT][,@parameterNamedatatype[=defaultValue][OUTPUT]])]ASSQL-Statements–其中：•procedureName：过程名，必须符合标识符规则，且在数据库中唯一；•parameterName：参数名，存储过程可不带参数，参数可以是变量、常量和表达式；•OUTPUT：说明该参数是输出参数，被调用者获取使用。缺省时表示是输入参数。8/1.1创建存储过程•如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值。–临时表的表名前加一个#符号，如#myTemp–在存储过程尾部，使用语句：SELECT*FROM#myTemp将结果集合返回给调用者。–存储过程结束后，临时表自动被删除。•注意：–用户定义的存储过程只能在当前数据库中创建；–一个存储过程最大不能超过128MB。若超过128MB，可将超出的部分编写为另一个存储过程，然后在存储过程中调用。9/1.1创建存储过程•[例9.23]输入某个同学的学号，统计该同学的平均分。CREATEPROCEDUREproStudentByNo1(@sNochar(7))ASSELECTa.studentNo,studentName,avg(score)FROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDa.studentNo=@sNoGROUPBYa.studentNo10/1.1创建存储过程•[例9.24]输入某个同学的学号，统计该同学的平均分，并返回该同学的姓名和平均分。•分析：–该过程涉及三个参数：•一个输入参数，设为@sNo，用于接收某同学的学号；•两个输出参数，用于返回查询到的同学姓名和平均分，设为@sName和@avg–实现方法：•用一个查询，根据输入参数@sNo，查询出该同学的姓名并放到输出参数@sName中–由于在学生表中学号是唯一的，使用命令：SELECT@snName=studentNameFROMStudentWHEREstudentNo=@sNo11/1.1创建存储过程•用另一个查询，根据输入参数@sNo，查询出该同学的选课平均分并放到输出参数@avg中–由于该同学的平均分也只有一个，使用命令：SELECT@avg=AVG(score)FROMScoreWHEREstudentNo=@sNoGROUPBYstudentNo12/1.1创建存储过程•存储过程为：CREATEPROCEDUREproStudentByNo21(@sNochar(7),@sNamevarchar(20)OUTPUT,@avgnumeric(5,1)OUTPUT)ASBEGIN--查询同学的姓名放入输出参数@sName中SELECT@sName=studentNameFROMStudentWHEREstudentNo=@sNo--查询同学选课的平均分放入输出参数@avg中SELECT@avg=AVG(score)FROMScoreWHEREstudentNo=@sNoGROUPBYstudentNoEND13/1.2执行存储过程•使用存储过程时，必须执行命令EXECUTE•语法：EXECUTEprocedurName[[@parameterName=]expr,[@parameterName=]@variableName[OUTPUT][,[@parameterName=]expr,[@parameterName=]@variableName[OUTPUT]]]–注意：EXECUTE的参数必须与对应的PROCEDURE的参数相匹配。•[例9.27]执行存储过程proStudentByNo1EXECUTEproStudentByNo1'0800001'14/1.2执行存储过程•[例9.28]执行存储过程proStudentByNo2DECLARE@sNamevarchar(20),@avgnumeric(5,1)EXECUTEproStudentByNo2'0800001',@sNameOUTPUT,@avgOUTPUTSELECT@sName,@avg•[例9.29]执行过程proInstituteEXECUTEproInstitute'信息管理学院'–也可以使用命令：DECLARE@institutevarchar(30)SET@institute='信息管理学院'EXECUTEproInstitute@institute15/1.3修改和删除存储过程•修改存储过程•语法为：ALTERPROCEDUREprocedureName[@parameterNamedatatype[=defaultValue][OUTPUT][,@parameterNamedatatype[=defaultValue][OUTPUT]]]ASSQL-Statements–注意：由于存储过程是在服务器端执行，程序中不需要有输出命令SELECT，由SELECT引出的输出不会在客户端出现。•[例9.30]修改存储过程proStudentAvg，将显示结果内容删除。ALTERPROCEDUREproStudentAvg(@sNochar(7),@avgnumeric(6,2)OUTPUT)ASBEGINDECLARE@sNamevarchar(20),@cNamevarchar(20)DECLARE@gradetinyint,@sumint,@counttinyintSELECT@sum=0,@count=0……16/1.3修改和删除存储过程•删除存储过程–语法：DROPPROCEDUREprocedureName–[例9.31]删除存储过程proStudentAvgDROPPROCEDUREproStudentAvg17/目录12存储过程触发器18/2触发器•触发器(trigger)是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。•触发器可进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。•触发器是一种特殊的存储过程，不管什么原因造成的数据变化都能自动响应，对于每条SQL语句，触发器仅执行一次，事务可用于触发器中。•事务定义：BEGINTRANSACTION[transactionName]COMMITTRANSACTION[transactionName]ROLLBACKTRANSACTION[transactionName]•有两个特殊的表用在触发器语句中，不同的数据库管理系统其名称不一样：–在SQLServer中使用deleted和inserted表；–Oracle数据库使用old和new表。19/2触发器•注意：–这两张表的结构与作用的表结构完全一致；–当作用表的SQL语句开始时，自动产生这两张表的结构与内容；–当SQL语句执行完毕，这两张表也随即删除。•下面以SQLServer为例介绍触发器：•deleted表–存储DELETE和UPDATE语句执行时所影响的行的拷贝；–在DELETE和UPDATE语句执行前被作用的行转移到deleted表中。•将被删除的元组或修改前的元组值存入该表中•inserted表–存储INSERT和UPDATE语句执行时所影响的行的拷贝；–在INSERT和UPDATE语句执行期间，新行被同时加到inserted表和触发器表中。•将被插入的元组或修改后的元组值存入该表中，同时更新基本表。20/2触发器•实际上，UPDATE命令是删除后紧跟着插入，旧行首先拷贝到deleted表中，新行同时拷贝到inserted表和基本表中。•触发器仅在当前数据库中生成–触发器有三种类型，即插入、删除和更新；–插入、删除和更新可作为一种类型的触发器；–查询操作不会产生触发动作，没有查询触发器类型。•4.1创建触发器•4.2修改和删除触发器•4.3触发器的作用21/2.1创建触发器•创建触发器的语法：CREATETRIGGERtriggerNameONtableNameFORINSERT|UPDATE|DELETEASSQL-Statement–其中：•triggerName：触发器的名称，在数据库中必须唯一；•tableName：触发器作用的基本表，该表也称为触发器的目标表；•INSERT|UPDATE|DELETE：触发器事件，触发器的事件可以是插入INSERT、更新UPDATE和删除DELETE事件，也可以是这几个事件的组合。22/2.1创建触发器•INSERT类型的触发器是指：当对指定表tableName执行了插入操作时系统自动执行触发器代码。•UPDATE类型的触发器是指：当对指定表tableName执行了更新操作时系统自动执行触发器代码。•DELETE类型的触发器是指：当对指定表tableName执行了删除操作时系统自动执行触发器代码。•SQL-Statement：触发动作的执行体，即一段SQL语句块–如果该触发执行体执行失败，则激活触发器的事件就会终止，且触发器的目标表tableName或触发器可能影响的其它表不发生任何变化，即执行事务的回滚操作。23/2.1创建触发器•[例9.32]创建触发器，保证学生表中的性别仅能取男和女。•分析：–本例需要使用插入和修改两个触发器，因为可能破坏约束“性别仅能取男和女”的操作是插入和修改操作。–违约条件是：•如果在inserted表中存在有性别取值不为“男”或“女”的记录（由于inserted表保存了修改后的记录，只要对inser