数据操作与事务控制

数据操作与事务控制课程结构内容课时（H）第一章Oracle数据库基础1.8第二章编写简单的查询语句1.2第三章限制数据和对数据排序1.5第四章单行函数2.5第五章多表查询2第六章分组函数2第七章子查询2第八章数据操作与事务控制3.5第九章表和约束4第十章其他数据库对象2.5第2页,共59页，星期六，2024年，5月第八章数据操作与事务控制目标:本章旨在向学员介绍：1)数据操作语句2）事务控制语句时间：3.5学时教学方法：讲授ppt＋上机练习第3页,共59页，星期六，2024年，5月本章要点INSERT语句UPDATE语句DELETE语句9i新增MERGE语句COMMIT命令ROLLBACK命令管理锁第4页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作语言（DML：DataManipulationLanguage）主要包括以下语句：INSERTUPDATEDELETEMERGE事务是一组相关的DML语句的逻辑组合。事务控制主要包括下列命令：COMMITROLLBACKSAVEPOINT第5页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第6页,共59页，星期六，2024年，5月8.1.1INSERT语法结构语法如下：一次只插入一行NULL的使用，连续的单引号（‘’）也可以表示空值。插入日期型数据插入特殊字符插入多行数据按列的默认顺序列出各个列的值在Insert子句中可以随意列出列名和他们的值字符和日期型数据应该包含在单引号中INSERTINTO表名[(列名1[,列名2，…，列名n])]VALUES(值1[,值2，…，值n]);第7页,共59页，星期六，2024年，5月8.1.2INSERT语句插入单行数据1/2例8-1将一个新成立部门的信息写入departments表INSERTINTOdepartmentsVALUES(300,'Operations',110,1500);第8页,共59页，星期六，2024年，5月8.1.2INSERT语句插入单行数据2/2显示默认值概述

使用Default表示默认值符合SQL：1999标准可以使用显示默认值控制默认值的使用显示默认值可以在insert和update语句中使用第9页,共59页，星期六，2024年，5月8.1.3INSERT语句插入空值（NULL）例8-2将一个新成立部门的信息写入departments中

，其中管理者未知。或或INSERTINTOdepartmentsVALUES(310,'Operations',NULL,1500);INSERTINTOdepartments(department_id,department_name,location_id)VALUES(310,'Operations',1500);INSERTINTOdepartmentsVALUES(310,'Operations','',1500);第10页,共59页，星期六，2024年，5月8.1.4INSERT语句插入日期型数据例8-5将一新入职员工信息写入employees表或INSERTINTOemployees(employee_id,last_name,email,hire_date,job_id)VALUES(210,’Wang’,’SWANG’,’10-9月-06’,’IT_PROG’);INSERTINTOemployees(employee_id,last_name,email,hire_date,job_id)VALUES(210,’Wang’,’SWANG’,TO_DATE(’2006-9-10’,’YYYY-MM-DD’),’IT_PROG’);第11页,共59页，星期六，2024年，5月8.1.5INSERT语句插入特殊字符查看ESCAPE转义符用哪个符号表示。INSERT语句中使用“\”符对特殊符号转义。INSERTINTOtestVALUES（‘\&TEST\&’）;SHOWESCAPE；——查看ESCAPE状态escapeOFF——返回ESCAPE状态为OFFSETESCAPEON；——设定ESCAPE状态为ONSHOWESCAPE；——查看ESCAPE状态escape"\"(hex5c)——返回ESCAPE符号为“\”

INSERTINTOtestVALUES（‘&TEST&’）;第12页,共59页，星期六，2024年，5月8.1.6INSERT语句插入多行数据语法例8-7将受雇日期在“1995-1-1”之前的员工信息复制到hemployees表中。INSERTINTO表名[(列名1[,列名2，…，列名n])]子查询；INSERTINTOhemployeesSELECT*FROMemployeesWHEREhire_date<TO_DATE(‘1995-1-1’,’YYYY-MM-DD’);不必书写values子句

INSERT子句中列的数量和类型必须和子查询中列的数量和类型相匹配第13页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第14页,共59页，星期六，2024年，5月8.2.1UPDATE语法结构UPDATE语法结构UPDATE简单修改UPDATEemployeesSETsalary=salary+200,manager_id=103WHEREdepartment_id=60;UPDATE表名

SET列名=表达式[，列名=表达式，···][WHERE条件表达式]；UPDATEemployeesSETsalary=salary*(1+0.2);第15页,共59页，星期六，2024年，5月8.2.2UPDATE嵌入子查询修改1/2嵌入子查询修改除基于表自身实现嵌入子查询的方式实现修改操作外，也可以在子查询中基于其他表实现修改操作。UPDATE

employeesSETdepartment_id=10,salary=500+(SELECTAVG(salary) FROMemployees)WHEREjob_id=(SELECTjob_idFROMemployees WHEREemployee_id=110)ANDemployee_id<>110;第16页,共59页，星期六，2024年，5月8.2.2UPDATE嵌入子查询修改2/2相关子查询修改updateemployeesasetsalary=salary+(select

avg(salary)fromemployeesbwhereb.department_id=a.department_id)第17页,共59页，星期六，2024年，5月8.2.3修改数据(完整性错误)updateemployeessetdepartment_id=55wheredepartment_id=100;ORA-02291:违反完整约束条件(NEU.EMP_DEPT_FK)-未找到父项关键字第18页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第19页,共59页，星期六，2024年，5月8.3.1DELETE语法结构DELETE语法结构DELETE删除数据DELETE[FROM]表名[WHERE条件表达式]；DELETEFROMdepartmentsWHEREdepartment_id=210;第20页,共59页，星期六，2024年，5月8.3.2DELETE删除数据1/3例8-12删除管理者编号（manager_id）为205的部门，相应部门的员工予以解聘，不包括205号员工。DELETE语句不能删除被其他表引用了的记录值。DELETEFROMemployeesWHEREdepartment_idIN(SELECTdepartment_id FROMdepartments WHEREmanager_id=205)ANDemployee_id<>205;第21页,共59页，星期六，2024年，5月8.3.2DELETE删除数据2/3删除数据（完整性错误）deletefromdepartmentswheredepartment_id=100;ORA-02292:违反完整约束条件(NEU.EMP_DEPT_FK)-已找到子记录日志第22页,共59页，星期六，2024年，5月8.3.2DELETE删除数据3/3删除数据（相关子查询）deleteemp_copya

where

exists(select'1'fromemployeesbwhereb.employee_id=a.employee_id)第23页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第24页,共59页，星期六，2024年，5月8.4合并数据根据指定的条件执行插入或者更新操作如果满足条件的行存在，执行更新操作，否则执行插入操作优点： 避免独立的数据更新 提高效率而且使用方便 在数据仓库应用中经常使用第25页,共59页，星期六，2024年，5月8.4.1MERGE语法结构1/2MERGE语法结构MERGEINTOtable_name[t_alias]USING{table|view|subquery}[t_alias]ON(joincondition)WHENMATCHEDTHENUPDATESETcol1=col1_val[,col2=col2_val···]WHENNOTMATCHEDTHENINSERT（column_list）VALUES（column_values）;第26页,共59页，星期六，2024年，5月8.4.1MERGE语法结构2/2INTO子句：指定更新或插入的目标表USING子句：指定更新或插入的数据源，它可以是表、视图、子查询ON子句：合并操作的条件判断语句 第27页,共59页，星期六，2024年，5月8.4.2MERGE语句合并数据MERGEINTOempaUSINGemployeesbON(a.employee_id=b.employee_id)WHENMATCHEDTHENUPDATESETa.email=b.email,a.phone_number=b.phone_number,a.salary=b.salary,a.manager_id=b.manager_id,a.department_id=b.department_idWHENNOTMATCHEDTHENINSERT(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表中。

第28页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第29页,共59页，星期六，2024年，5月8.5.1ROWID介绍1/2ROWID：伪列，是表中虚拟的列，是系统自动产生的。每一行记录中都包含ROWID，表示这一行的唯一地址。ROWID标识了Oracle如何定位行，通过ROWID能快速定位一行记录。ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龙SELECTrowid,cnameFROMdossier;第30页,共59页，星期六，2024年，5月8.5.1ROWID介绍2/2ROWID的格式：ROWID中包含该行数据的物理位置信息，所以能快速的定位记录ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龙例：AAAHjXAAOAAAADaAAA数据对象编号文件编号块编号行编号

AAAHjXAAOAAAADaAAAOOOOOOFFFBBBBBBRRR数据对象编号相关文件编号块编号行编号第31页,共59页，星期六，2024年，5月8.5.2使用ROWID进行数据操作ROWID的应用：快速定位单行记录，DML语句可以使用ROWID操作数据，效率最快作为表行的唯一标识例:使用ROWNUM修改数据UPDATEemployeesSETfirst_name=first_name||'*'WHEREROWID='AAAMg6AAFAAAABUAAA';第32页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第33页,共59页，星期六，2024年，5月8.6.1事务概念及特征1/3概念：事务（Transaction）也称工作单元，是一个或多个SQL语句所组成的序列，这些SQL操作作为一个完整的工作单元，要么全部执行，要么全部不执行。通过事务的使用，能够使一系列相关操作关联起来，防止出现数据不一致现象。事务的组成：在ORACLE数据库中，事务由以下语句组成：一组相关的DML语句，修改的数据在该组语句中保持一致一个DDL语句或者一个DCL语句或者一个TCL语句第34页,共59页，星期六，2024年，5月8.6.1事务概念及特征2/3可用四个字母的缩写表示：即ACID原子性（Atomicity）事务就像一个独立的工作单元。原子性保证要么所有的操作都成功，要不全都失败。如果所有的动作都成功了，我们就说这个事务成功了，不然就是失败的，然后回滚。一致性（Consistency）一旦事务完成了（不管是成功的，还是失败的），整个系统处于操作规则的统一状态，也就是说，数据不会损坏。第35页,共59页，星期六，2024年，5月8.6.1事务概念及特征3/3隔离性（Isolation）事务的隔离性是指数据库中一个事务的执行不能被其它事务干扰。所以，事务应该隔离起来，目的为了防止同时的读和写操作。这就需要事务与锁同时使用。持久性（Durability）事务的持久性也称为永久性（Permanence），指事务一旦提交，则其对数据库中数据的改变就是永久的。第36页,共59页，星期六，2024年，5月8.6.2事务控制1/8事务的控制分为：显式控制及隐式控制。显示控制：显示提交：Commit显示回滚：Rollback隐式控制：隐式提交：当下列任意一种情况发生时，会发生隐式提交执行一个DDL语句执行一个DCL语句从SQL*Plus正常退出（即使用EXIT或QUIT命令退出）隐式回滚：当下列任意一种情况发生时，会发生隐式回滚从SQL*Plus中强行退出客户端连接到服务器端异常中断系统崩溃注意：PL/Sqldeveloper工具和Sqlplus略有不同。第37页,共59页，星期六，2024年，5月8.6.2事务控制2/8事务控制的命令主要有以下三个：事务提交：COMMIT事务回滚：ROLLBACK设立保存点：SAVEPOINT（作为辅助命令使用）设置保存点语法：SAVEPOINT保存点名称；——定义保存点ROLLBACKTO保存点名称;——回滚到已定义保存点第38页,共59页，星期六，2024年，5月8.6.2事务控制3/8开始：事务开始于上一个事务结束后执行的第一个DML语句结束：事务结束于下面的任一种情况的发生：执行了COMMIT或者ROLLBACK命令隐式提交（单个的DDL或DCL语句）或自动提交用户退出系统崩溃第39页,共59页，星期六，2024年，5月8.6.2事务控制4/8设置格式：例8-14

SQL*Plus自动提交的应用示例SETAUTOCOMMIT[ON|OFF];SHOWAUTOCOMMIT;——查看AUTOCOMMIT变量状态autocommitOFFSETAUTOCOMMITON；——把变量状态设置为ONINSERTINTOtestVALUES(‘TEST’);已创建1行提交完成——已经自动提交事务自动提交第40页,共59页，星期六，2024年，5月8.6.2事务控制5/8DELETEFROMtest;ROLLBACK;——撤消DELETE操作INSERTINTOtestVALUES(’A’);SAVEPOINTinsert_a;——定义insert_a保存点INSERTINTOtestVALUES(’B’);SAVEPOINTinsert_b;——定义insert_b保存点INSERTINTOtestVALUES(’C’);ROLLBACKTOinsert_b;——撤消操作到insert_b保存点DELETEFROMtestWHEREtest_str=‘A’;COMMIT;——将所有修改写入数据库ROLLBACK;——所有操作已经COMMIT提交，不能 回滚事务控制示例第41页,共59页，星期六，2024年，5月执行Commit或Rollback前的数据状态数据变化前的状态可以被恢复当前会话可以使用SELECT语句来验证DML操作后的结果其它会话不能查看由当前用户的DML操作结果受影响记录被锁定，也就是其它用户不能改变受影响记录中的数据8.6.2事务控制6/8第42页,共59页，星期六，2024年，5月8.6.2事务控制7/8Commit后的状态在数据库中数据变化成为永久性的先前的数据状态永久性的消失所有用户/会话都可以查询提交后的结果锁定的记录被释放，可以有效地被其他用户操作所有的存储节点被清除第43页,共59页，星期六，2024年，5月8.6.2事务控制8/8Rollback后的数据状态使用ROLLBACK可以放弃所有悬而未决的变化。数据变化是可以撤销的先前的数据状态被恢复锁定的记录被释放所有的存储节点被清除第44页,共59页，星期六，2024年，5月8.6.3读一致性1/2读一致性保证了不同会话在同一时间查看数据时，数据一致。ORACLE在两个不同级别上提供读一致性：语句级读一致性和事务级一致性。事务级一致性，当一个会话正在修改数据时，其它的会话将看不到该会话未提交的修改。语句级读一致性，保证单个查询所返回的数据与该查询开始时刻相一致。所以一个查询从不会看到在查询执行过程中提交的其它事务所作的任何修改。第45页,共59页，星期六，2024年，5月8.6.3读一致性2/2UPDATEemp

SETsal=2000WHEREename=

'SCOTT';数据块回滚段发生变化及未发生变化的数据变化前的老数据用户A用户B读取一致的镜像SELECT*

FROM emp;第46页,共59页，星期六，2024年，5月第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁第47页,共59页，星期六，2024年，5月8.7.1并发事务并发事务并发事务：如果多个事务同时访问某一个资源，比如同时修改表中的某一行。并发事务可能出现如下问题:1.脏读取:就是读取了未提交的数据，如B事务读取了A事务未提交的数据2.不可重复读取:就是一个事务在读取一个数据时，数据不同，可能是另一个事务修改了数据。3.更新丢失:就是两个事务同时更新了一条数据，先更新的就会丢失数据。4.幻想读:事务A在读取数据的时候，数据发生了变化。以上问题如何解决呢？

锁机制第48页,共59页，星期六，2024年，5月8.7.2锁的概念1/2锁用来在多用户并发访问和操作数据库时保证数据的一致性。锁由Oracle自动管理。如一个DML操作，ORACLE默认的机制是在DML操作涉及到的行上加锁（行级别），但不会在更高的级别（表级别）上加更严格的锁，比如只改某行的数据不会锁住整个表。这提供了很好的并发性，因为整个表没有锁定，只是某些行被锁定了，其他用户可以修改其他行数据。查询不需要任何锁。第49页,共59页，星期六，2024年，5月8.7.2锁的概念2/2锁的生命周期，锁在被相关的操作申请并持有后，会一直保持到事务的结束。事务结束后，锁才会被释放。锁的内部维护机制是采用排队机制（enqueue），一个对象的排它锁被持有后，该对象相同级别的锁被其他事务申请时候，所有等待该锁的事务都在一个等待队列中排队，其他事务处于等待状态。直到该锁被释放，等待的事务才重新竞争使用该资源。锁的模式排它锁模式（Exclusive）排它锁在被释放之前，会阻止其锁住的资源被其他任何事务共享。共享模式（Share）第50页,共59页，星期六，2024年，5月8.7.3锁的分类1/2两种类型的锁，DML锁和DDL锁。DML锁，也称数据锁，用于在数据被多个不同的用户改变时，保证数据的完整性。DDL锁，也称为数据字典锁，执行DDL语句时，DDL语句涉及到的对象获得DDL锁。由于被持有的时间很短，因此很少看到冲突的DDL锁，并且以nowait方式被请求。第51页,共59页，星期六，2024年，5月8.7.3锁的分类2/23种DDL锁类型：排它的DDL锁，很多对象的创建、修改和删除定义时候都需要获得该锁。比如执行CreateTable、DropTable等时会获得表上