数据库完整性与安全

1、/，数据库原理与应用，滁州大学计算机与信息工程学院，第12讲，数据库完整性与安全性，教学目标：理解存储过程和触发器。/，目录，存储过程，触发器，/，1存储过程，存储过程是一组为完成特定功能而组装的命名SQL语句。该集合被编译并存储在数据库中，可以根据实际情况重新编译；存储过程可以直接或远程运行。存储过程直接在服务器端运行。使用存储过程有以下优点：封装业务操作可以为复杂的业务操作编写存储过程，并将它们放入数据库；用户可以调用存储过程来执行，但是业务操作对用户是不可见的。如果存储过程只修改执行器而不修改接口，则用户程序不需要修改，从而达到业务封装的效果。事务控制便于事务管理，可以在存储过程中使用。

2、用户可以根据业务的性质定义交易，并对交易进行相应的层级操作。/，1存储过程，为了实现一定程度的安全保护，存储过程存储在数据库中并在服务器端运行；对于不允许用户直接操作的表或视图，可以通过调用存储过程来间接访问它们，以实现一定程度的安全性；这种安全性是由于用户对存储过程只有执行权限，而没有查看权限。拥有存储过程的执行权限，并自动获取存储过程中相应表或视图的操作权限；这些操作权限只能通过执行存储过程来实现，一旦存储过程被分离，相应的操作权限将会丢失。注意：您只需要向存储过程授予执行权限，而不需要向表或视图授予操作权限。它特别适用于一般的统计和查询的统计和查询操作，特别是对于最终的统计，往往涉及到大

3、量的数据和很多的表格。如果在客户端实现，数据流和网络流量会很大；在许多情况下，管理信息系统的设计者使用存储过程来实现复杂的查询和统计，从而避免了客户端的大量编程。/，1存储过程，减少网络流量存储过程只在服务器端执行，客户端只接收结果；因为存储过程和数据通常在一台服务器上，所以可以减少大量的网络流量。在使用存储过程之前，必须首先创建存储过程。您可以修改和删除存储过程。创建存储过程后，必须授予它EXECUTE权限，否则只能由创建者执行。1.1创建存储过程1.2执行存储过程1.3修改和删除存储过程，/，1.1创建存储过程，语法：create procedure (=output，=output) a

4、s其中：过程的名称必须符合标识符规则，并且在数据库中是唯一的；参数名，存储过程可以不带参数，参数可以是变量、常量和表达式；输出：指示此参数是输出参数，由调用者获取和使用。默认情况下，它意味着输入参数。/，1.1创建存储过程。如果存储过程的输出参数取一个设置值，则输出参数不是在存储过程的参数中定义的，而是在存储过程中定义一个临时表来存储该设置值。在临时表的表名前添加一个#符号，例如，#myTemp在存储过程的末尾，并使用语句：SELECT * FROM #myTemp将结果集返回给调用方。存储过程完成后，临时表将自动删除。注意：用户定义的存储过程只能在当前数据库中创建；存储过程的最大大小不能超过

5、128兆字节。如果超过128兆字节，它可以作为另一个存储过程写入，然后在存储过程中调用。/，1.1创建一个存储过程，例如，9.23输入某个同学的学号，并计算该同学的平均分数。CREATE PROCESSE ProstudentByn1(SnO char(7)AS SELECT a.studentNo，studentName，avg(score) FROM Student a，Score b其中a. studentno=b. studentno和a. studentno=SnO由a . Student no分组，/，1.1创建一个存储过程，示例9.24输入某个同学的学生编号，计算该同学的平均分数

6、，分析：该过程涉及三个参数：一个输入参数设置为sNo，用于接收某个同学的学号；两个输出参数，用于返回被查询的同学的名字和平均成绩，设置为s name和avg实现方法：根据输入参数sNo，查询同学的名字并将其放入输出参数sName，因为学生号在学生表中是唯一的，使用命令：从学生中选择SnaMe=学生名，其中学生号=sNo，/，1.1创建一个存储过程，使用另一个查询，根据输入参数sNo， 找出这个学生的课程选择的平均分数，并把它放在输出参数avg中，因为这个学生只有一个平均分数。 使用以下命令：从分数中选择avg=avg(分数)，其中studentno=sno group by studentno

7、，/，1.1创建一个存储过程：创建过程prostudentbyno21 (snochar (7)，sName varchar(20) OUTPUT，avg numeric(5，1) output)作为开始-将学生姓名查询到输出参数sName中，选择sname=学生姓名，其中学生编号=SnO-查询学生选修课的平均分数并将其输入输出参数avg SELECT=AVG(分数)使用存储过程时，必须执行命令。Execute语法：EXECUTE=，=OUTPUT，=，=OUTPUT注意：EXECUTE的参数必须与相应过程的参数相匹配。示例9.27执行存储过程prostudentbyno1执行prostude

8、ntbyno1 080001，/，1.2执行存储过程，示例9.28执行存储过程prostudentbyno2声明sname varchar (20)，avg numeric，1)EXECUTE prostudentbyno 2 0800001，sName OUTPUT，avg OUTPUT SELECT sName，avg示例9.29执行过程pro institute EXECUTE pro institute information management institute还可以使用以下命令：declare institute varchar(30)set institute=informa

9、tion management institute修改后的存储过程的语法是：alter procedure=output ,=output as。注意：由于存储过程是在服务器端执行的，所以在程序中不需要输出命令SELECT，并且从SELECT得到的输出不会出现在客户端。示例9.30修改存储过程proStudentAvg，并删除显示结果内容。ALTER PROCESSE Prostudenttavg(SnO char(7)，average number(6，2)Output)AS BEgin DECLARE SName varchar(20)，CNAME varchar(20)DECLARE g

10、rade TINYINT，SUM int，COUNT TINYINT SELECT SUM=0，COUNT=0，/，1.3修改和删除存储过程，删除存储过程语法：DROP PROCESSE示例9.31删除存储过程prostudentavg drop存储过程Prostudenttavg，/，目录，存储过程，触发器，/，2触发器和触发器是一类事件触发器可以执行更复杂的检查和操作，并具有更复杂和强大的数据控制能力。触发器是一种特殊的存储过程，它可以自动响应任何原因引起的任何数据更改。对于每个SQL语句，触发器只执行一次，事务可以在触发器中使用。事务定义：开始事务提交事务回滚事务有两个在触发器语句中使用

11、的特殊表。不同的数据库管理系统有不同的名称：删除和插入的表在SQL Server中使用；Oracle数据库使用新旧表。/，2触发器，注意：这两个表的结构与活动表的结构完全一致；当活动表的SQL语句启动时，自动生成这两个表的结构和内容；当执行SQL语句时，这两个表将被删除。以SQL Server为例介绍了触发器：deleted表存储受执行DELETE和UPDATE语句影响的行的副本；在执行DELETE和UPDATE语句之前受影响的行被转移到已删除的表中。修改前删除的元组或元组值存储在表中。insert表存储受执行InserT和UPDATE语句影响的行的副本；在执行INSERT和UPDATE语句期

12、间，新的行被添加到插入的表和触发器表中。将插入的元组或修改后的元组值存储到表中，同时更新基本表。，/，2触发器，事实上，UPDATE命令被删除，然后被插入，旧行首先被复制到被删除的表中，而新行同时被复制到被插入的表和基本表中。触发器仅在当前数据库中生成触发器。有三种类型的触发器，即插入、删除和更新。插入、删除和更新可以用作一种触发器；查询操作不会生成触发器操作，也没有查询触发器类型。4.1创建触发器4.2修改和删除触发器4.3触发器的功能，/，2.1创建触发器，创建触发器的语法：为AS创建触发器，其中：触发器的名称在数据库中必须是唯一的；触发器动作的基本表，也称为触发器的目标表；触发事件，可以

13、是插入、更新和删除事件，也可以是这些事件的组合。/，2.1创建触发器。INSERT类型的触发器意味着当插入指定的表时，系统自动执行触发器代码。UPDATE触发器是指当指定的表被更新时，系统自动执行的触发器代码。DELETE类型的触发器意味着当删除指定的表时，系统自动执行触发器代码。触发器操作的执行器，即一个SQL语句块。如果触发器执行器的执行失败，激活触发器的事件将被终止，并且触发器的目标表或可能受触发器影响的其他表不会改变，也就是说，事务将回滚。/，2.1创建触发器，示例9.32创建触发器，以确保学生表中的性别只能取男性和女性。分析：在本例中，需要使用两个触发器，插入和修改，因为可能打破“性

14、别只能取男性和女性”约束的操作是插入和修改操作。默认条件是：如果插入的表中有一条记录的性别值不是“男”或“女”(因为插入的表保存修改后的记录，您只需要判断插入的表)，那么取消此操作。插入触发器在学生身上创建触发器插入，如果存在(从插入的性别不在的地方选择*)回滚，/，2.1创建触发器，修改触发器在学生身上创建触发器插入，如果存在，则进行更新(从插入的性别不在的地方选择*(男性，回滚此示例也可以合并到一个触发器中在学生身上创建触发器插入，如果存在，则进行更新(从插入的性别不在的地方选择*)回滚此示例的插入表结构与学生表结构相同。/，2.2修改和删除触发器，语法为：将触发器更改为打开状态，对于，/，2.2修改和删除触发器，可以在不需要时删除，以及删除语法：删除触发器示例9.36删除触发器类。DROP TRIGGER classUpt，/，2.3触发器的功能，触发器通常用于确保完整性并在一定程度上实现安全性，例如，触发器可以用于审计。注意：原则上，表中定义的触发器数量不受限制；因为触发器是自动执