第8章 数据操作与事务控制

1、1Oracle SQL开发基础开发基础2课程结构课程结构内容内容课时（课时（H H）第一章 Oracle数据库基础1.8第二章 编写简单的查询语句1.2第三章 限制数据和对数据排序1.5第四章 单行函数2.5第五章 多表查询2第六章 分组函数2第七章 子查询2第八章 数据操作与事务控制3.5第九章 表和约束4第十章 其他数据库对象2.53第八章第八章 数据操作与事务控制数据操作与事务控制目标目标:本章旨在向学员介绍：本章旨在向学员介绍：1) 数据操作语句数据操作语句2）事务控制语句）事务控制语句时间：时间： 3.5学时学时教学方法：教学方法：讲授讲授ppt上机练习上机练习4本章要点本章要点IN

2、SERT语句UPDATE语句DELETE语句9i新增MERGE语句COMMIT命令ROLLBACK命令管理锁5第八章第八章 数据操作与事务控制数据操作与事务控制数据操作语言（DML： Data Manipulation Language） 主要包括以下语句：INSERTUPDATEDELETEMERGE事务是一组相关的DML语句的逻辑组合。事务控制主要包括下列命令：COMMITROLLBACKSAVEPOINT6第八章第八章 数据操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁78.1

3、.1INSERT语法结构语法结构语法如下：一次只插入一行NULL的使用，连续的单引号（）也可以表示空值。插入日期型数据 插入特殊字符 插入多行数据 按列的默认顺序列出各个列的值在Insert子句中可以随意列出列名和他们的值字符和日期型数据应该包含在单引号中INSERT INTO 表名(列名1,列名2，列名n)VALUES (值1,值2，值n);88.1.2INSERT语句插入单行数据语句插入单行数据 1/2例例8-1 将一个新成立部门的信息写入将一个新成立部门的信息写入departments表表INSERT INTO departments VALUES(300,Operations,110,

4、1500);98.1.2INSERT语句插入单行数据语句插入单行数据 2/2显示默认值概述 使用Default表示默认值符合SQL：1999标准可以使用显示默认值控制默认值的使用显示默认值可以在insert和update语句中使用108.1.3INSERT语句插入空值（语句插入空值（NULL）例例8-2 将一个新成立部门的信息写入将一个新成立部门的信息写入departments中中 ，其中管，其中管理者未知。理者未知。或或INSERT INTO departments VALUES(310,Operations, NULL,1500);INSERT INTO departments (depa

5、rtment_id,department_name,location_id) VALUES(310,Operations,1500);INSERT INTO departments VALUES(310,Operations, ,1500);118.1.4INSERT语句插入日期型数据语句插入日期型数据例例8-5 将一新入职员工信息写入将一新入职员工信息写入employees表表或INSERT INTO employees(employee_id,last_name,email,hire_date,job_id)VALUES(210,Wang,SWANG, 10-9月-06,IT_PROG);

6、INSERT INTO employees(employee_id,last_name,email,hire_date,job_id)VALUES(210,Wang,SWANG,TO_DATE(2006-9-10,YYYY-MM-DD),IT_PROG);128.1.5INSERT语句插入特殊字符语句插入特殊字符查看ESCAPE转义符用哪个符号表示。 INSERT语句中使用“”符对特殊符号转义。 INSERT INTO testVALUES（&TEST&）;SHOW ESCAPE； 查看ESCAPE状态escape OFF 返回ESCAPE状态为OFFSET ESCAPE ON

7、； 设定ESCAPE状态为ONSHOW ESCAPE； 查看ESCAPE状态escape (hex 5c) 返回ESCAPE符号为“” INSERT INTO test VALUES（&TEST&）;138.1.6INSERT语句插入多行数据语句插入多行数据语法例例8-7 将受雇日期在将受雇日期在“1995-1-1”之前的员工信息复制到之前的员工信息复制到hemployees表中。表中。INSERT INTO表名(列名1,列名2，列名n) 子查询 ；INSERT INTO hemployees SELECT * FROM employees WHERE hire_dateTO_

8、DATE(1995-1-1,YYYY-MM-DD);不必书写values子句INSERT子句中列的数量和类型必须和子查询中列的数量和类型相匹配14第八章第八章 数据操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁158.2.1UPDATE语法结构语法结构UPDATE语法结构UPDATE简单修改 UPDATE employees SET salary=salary+200,manager_id=103 WHERE department_id=60;UPDATE 表名 SET 列名=表达式

9、，列名=表达式，WHERE 条件表达式；UPDATE employees SET salary=salary*(1+0.2);168.2.2UPDATE嵌入子查询修改嵌入子查询修改 1/2嵌入子查询修改除基于表自身实现嵌入子查询的方式实现修改操作外，也可以在子查询中基于其他表实现修改操作。UPDATE employees SET department_id=10, salary=500+(SELECT AVG(salary) FROM employees)WHERE job_id=(SELECT job_id FROM employees WHERE employee_id=110)AND e

10、mployee_id110; 178.2.2UPDATE嵌入子查询修改嵌入子查询修改 2/2相关子查询修改相关子查询修改update employees a set salary = salary+(select avg(salary) from employees b where b.department_id = a.department_id)188.2.3修改数据修改数据(完整性错误完整性错误)update employees set department_id = 55 where department_id = 100;ORA-02291: 违反完整约束条件 (NEU.EMP_DE

11、PT_FK) - 未找到父项关键字19第八章第八章 数据操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁208.3.1DELETE语法结构语法结构 DELETE语法结构DELETE删除数据 DELETE FROM 表名WHERE 条件表达式；DELETE FROM departmentsWHERE department_id=210;218.3.2DELETE删除数据删除数据 1/3例例8-12 删除管理者编号（manager_id）为205的部门，相应部门的员工予以解聘，不包括205

12、号员工。DELETE语句不能删除被其他表引用了的记录值。DELETE FROM employeesWHERE department_id IN (SELECT department_id FROM departments WHERE manager_id =205)AND employee_id205;228.3.2DELETE删除数据删除数据 2/3删除数据 （完整性错误） delete from departments where department_id = 100;ORA-02292: 违反完整约束条件 (NEU.EMP_DEPT_FK) - 已找到子记录日志238.3.2DELET

13、E删除数据删除数据 3/3删除数据 （相关子查询）delete emp_copy a where exists (select 1 from employees b where b.employee_id = a.employee_id)24第八章第八章 数据操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁258.4合并数据合并数据根据指定的条件执行插入或者更新操作如果满足条件的行存在，执行更新操作，否则执行插入操作优点：避免独立的数据更新提高效率而且使用方便 在数据仓库应用中经常使用

14、268.4.1MERGE语法结构语法结构 1/2MERGE语法结构MERGE INTO table_name t_alias USING table | view | subquery t_alias ON ( join condition ) WHEN MATCHED THEN UPDATE SET col1=col1_val,col2=col2_val WHEN NOT MATCHED THEN INSERT（column_list）VALUES（column_values）;278.4.1MERGE语法结构语法结构 2/2INTO 子句：指定更新或插入的目标表USING 子句：指定更新或

15、插入的数据源，它可以是表、视图、子查询ON 子句：合并操作的条件判断语句288.4.2MERGE语句合并数据语句合并数据MERGE INTO emp aUSING employees b ON(a.employee_id=b.employee_id)WHEN MATCHED THEN UPDATE SET a.email=b.email,a.phone_number=b.phone_number, a.salary=b.salary, a.manager_id=b.manager_id, a.department_id=b.department_idWHEN NOT MATCHED THEN

16、INSERT (employee_id,email,phone_number,salary,manager_id,department_id) VALUES(b.employee_id, b.email, b.phone_number, b.salary, b.manager_id, b.department_id);该例子显示匹配employees表中的employee_id列与emp表中的employee_id列。如果找到了一个匹配，用employees表中匹配行的列值更新emp表中匹配的列值。如果相匹配行没有找到， employees表中的列值被插入到emp表中。 29第八章第八章 数据

17、操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁308.5.1ROWID介绍介绍 1/2ROWID：伪列，是表中虚拟的列，是系统自动产生的。每一行记录中都包含ROWID，表示这一行的唯一地址。ROWID标识了Oracle如何定位行，通过 ROWID 能快速定位一行记录。ROWID CNAME AAAHjXAAOAAAADaAAA 姚明 AAAHjXAAOAAAADaAAC 成龙 lSELECT rowid,cnamelFROM dossier;318.5.1ROWID介绍介绍 2/2R

18、OWID的格式：ROWID中包含该行数据的物理位置信息，所以能快速的定位记录ROWID CNAME AAAHjXAAOAAAADaAAA 姚明 AAAHjXAAOAAAADaAAC 成龙 l例： AAAHjXAAOAAAADaAAA 数据对象编号 文件编号 块编号 行编号 AAAHjX AAO AAAADa AAAlOOOOOOlFFFlBBBBBBlRRRl数据对象编号l相关文件编号l块编号l行编号328.5.2使用使用ROWID进行数据操作进行数据操作ROWID的应用：快速定位单行记录，DML语句可以使用ROWID操作数据，效率最快作为表行的唯一标识例:使用ROWNUM修改数据 UPDAT

19、E employees SET first_name = first_name | * WHERE ROWID = AAAMg6AAFAAAABUAAA;33第八章第八章 数据操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁348.6.1事务概念及特征事务概念及特征 1/3概念：事务（Transaction）也称工作单元，是一个或多个SQL语句所组成的序列，这些SQL操作作为一个完整的工作单元，要么全部执行，要么全部不执行。通过事务的使用，能够使一系列相关操作关联起来，防止出现数据不一

20、致现象。事务的组成：在ORACLE数据库中，事务由以下语句组成：一组相关的DML语句，修改的数据在该组语句中保持一致一个 DDL语句或者一个 DCL语句或者一个TCL语句358.6.1事务概念及特征事务概念及特征 2/3可用四个字母的缩写表示：即ACID原子性（Atomicity）事务就像一个独立的工作单元。原子性保证要么所有的操作都成功，要不全都失败。如果所有的动作都成功了，我们就说这个事务成功了，不然就是失败的，然后回滚。一致性（Consistency）一旦事务完成了（不管是成功的，还是失败的），整个系统处于操作规则的统一状态，也就是说，数据不会损坏。368.6.1事务概念及特征事务概念及

21、特征 3/3隔离性（Isolation）事务的隔离性是指数据库中一个事务的执行不能被其它事务干扰。所以，事务应该隔离起来，目的为了防止同时的读和写操作。这就需要事务与锁同时使用。持久性（Durability）事务的持久性也称为永久性（Permanence），指事务一旦提交，则其对数据库中数据的改变就是永久的。378.6.2事务控制事务控制 1/8事务的控制分为：显式控制及隐式控制。显示控制：显示提交：Commit显示回滚：Rollback隐式控制：隐式提交：当下列任意一种情况发生时，会发生隐式提交执行一个DDL 语句执行一个DCL 语句从 SQL*Plus正常退出（即使用EXIT或QUIT命令

22、退出）隐式回滚：当下列任意一种情况发生时，会发生隐式回滚从SQL*Plus中强行退出客户端连接到服务器端异常中断系统崩溃注意：PL/Sql developer工具和Sqlplus略有不同。388.6.2事务控制事务控制 2/8事务控制的命令主要有以下三个：事务提交：COMMIT事务回滚：ROLLBACK设立保存点：SAVEPOINT（作为辅助命令使用）设置保存点语法：SAVEPOINT 保存点名称； 定义保存点ROLLBACK TO保存点名称; 回滚到已定义保存点398.6.2事务控制事务控制 3/8开始：事务开始于上一个事务结束后执行的第一个DML语句结束：事务结束于下面的任一种情况的发生：

23、事务结束于下面的任一种情况的发生：执行了执行了COMMIT 或者或者ROLLBACK命令命令隐式提交（单个的隐式提交（单个的DDL或或DCL语句）或自动提交语句）或自动提交用户退出用户退出系统崩溃系统崩溃408.6.2事务控制事务控制 4/8设置格式：例例8-14 SQL*Plus自动提交的应用示例自动提交的应用示例SET AUTOCOMMIT ON|OFF;SHOW AUTOCOMMIT; 查看AUTOCOMMIT变量状态autocommit OFFSET AUTOCOMMIT ON； 把变量状态设置为ONINSERT INTO test VALUES (TEST);已创建 1 行提交完成

24、已经自动提交l事务自动提交 418.6.2事务控制事务控制 5/8DELETE FROM test ;ROLLBACK; 撤消DELETE操作INSERT INTO test VALUES(A);SAVEPOINT insert_a; 定义insert_a保存点INSERT INTO test VALUES(B);SAVEPOINT insert_b; 定义insert_b保存点INSERT INTO test VALUES(C);ROLLBACK TO insert_b; 撤消操作到insert_b保存点DELETE FROM test WHERE test_str = A;COMMIT;

25、将所有修改写入数据库ROLLBACK; 所有操作已经COMMIT提交，不能 回滚l事务控制示例42执行执行Commit或或Rollback前的数据状态前的数据状态 数据变化前的状态可以被恢复数据变化前的状态可以被恢复当前会话可以使用当前会话可以使用SELECT语句来验证语句来验证 DML操作后的结果操作后的结果其它会话不能查看由当前用户的其它会话不能查看由当前用户的DML操作结果操作结果受影响记录被锁定，也就是其它用户不能改变受影响记录受影响记录被锁定，也就是其它用户不能改变受影响记录中的数据中的数据8.6.2事务控制事务控制 6/8438.6.2事务控制事务控制 7/8Commit后的状态在

26、数据库中数据变化成为永久性的在数据库中数据变化成为永久性的先前的数据状态永久性的消失先前的数据状态永久性的消失所有用户所有用户/会话都可以查询提交后的结果会话都可以查询提交后的结果锁定的记录被释放，可以有效地被其他用户操作锁定的记录被释放，可以有效地被其他用户操作所有的存储节点被清除所有的存储节点被清除448.6.2事务控制事务控制 8/8Rollback后的数据状态使用使用ROLLBACK 可以放弃所有悬而未决的变化。可以放弃所有悬而未决的变化。数据变化是可以撤销的数据变化是可以撤销的先前的数据状态被恢复先前的数据状态被恢复锁定的记录被释放锁定的记录被释放所有的存储节点被清除所有的存储节点被

27、清除458.6.3读一致性读一致性 1/2读一致性保证了不同会话在同一时间查看数据时，数据一致。ORACLE在两个不同级别上提供读一致性：语句级读一致性和事务级一致性。事务级一致性，当一个会话正在修改数据时，其它的会话将看不到该会话未提交的修改。语句级读一致性，保证单个查询所返回的数据与该查询开始时刻相一致。所以一个查询从不会看到在查询执行过程中提交的其它事务所作的任何修改。468.6.3读一致性读一致性 2/247第八章第八章 数据操作与事务控制数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁48

28、8.7.1并发事务并发事务并发事务并发事务：如果多个事务同时访问某一个资源，比如同时修改表中的某一行。并发事务可能出现如下问题:1.脏读取:就是读取了未提交的数据，如B事务读取了A事务未提交的数据2.不可重复读取:就是一个事务在读取一个数据时，数据不同，可能是另一个事务修改了数据。3.更新丢失:就是两个事务同时更新了一条数据，先更新的就会丢失数据。4.幻想读:事务A在读取数据的时候，数据发生了变化。以上问题如何解决呢？以上问题如何解决呢？ 锁机制锁机制498.7.2锁的概念锁的概念 1/2锁用来在多用户并发访问和操作数据库时保证数据的一致性。锁由Oracle自动管理。如一个DML操作，ORAC

29、LE默认的机制是在DML操作涉及到的行上加锁（行级别），但不会在更高的级别（表级别）上加更严格的锁，比如只改某行的数据不会锁住整个表。这提供了很好的并发性，因为整个表没有锁定，只是某些行被锁定了，其他用户可以修改其他行数据。查询不需要任何锁。508.7.2锁的概念锁的概念 2/2锁的生命周期，锁在被相关的操作申请并持有后，会一直保持到事务的结束。事务结束后，锁才会被释放。锁的内部维护机制是采用排队机制（enqueue），一个对象的排它锁被持有后，该对象相同级别的锁被其他事务申请时候，所有等待该锁的事务都在一个等待队列中排队，其他事务处于等待状态。直到该锁被释放，等待的事务才重新竞争使用该资源。

30、锁的模式锁的模式排它锁模式（Exclusive）排它锁在被释放之前，会阻止其锁住的资源被其他任何事务共享。共享模式（Share）518.7.3锁的分类锁的分类 1/2两种类型的锁，DML锁和DDL锁。DML锁，也称数据锁，用于在数据被多个不同的用户改变时，保证数据的完整性。DDL锁，也称为数据字典锁，执行DDL语句时，DDL语句涉及到的对象获得DDL锁。由于被持有的时间很短，因此很少看到冲突的DDL锁，并且以nowait方式被请求。528.7.3锁的分类锁的分类 2/23种DDL锁类型：排它的DDL锁，很多对象的创建、修改和删除定义时候都需要获得该锁。比如执行Create Table、Drop

31、 Table等时会获得表上的排它DDL锁。共享的DDL锁。在执行Grant、Create Procedure等命令时，会获得命令相关操作对象的共享DDL锁。Breakable Parse Lock，用来在共享SQL区校验语句。538.7.4锁的使用锁的使用 1/3ORACLE中实现加锁机制主要有两种方式：1.自动加锁2.手动加锁548.7.4锁的使用锁的使用 2/3自动加锁用户在执行INSERT，UPDATE，DELETE，DCL，DDL语句时，ORACLE会自动加锁。在一个事务中，ORACLE会自动锁定执行以上语句的数据库对象，只有事务结束（提交或者回滚），资源才能被释放。默认情况下，更新数据时ORACLE会自动提供排他锁，使不同用户在更新表中