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

1OracleSQL开发基础2课程结构内容课时（H）第一章Oracle数据库基础1.8第二章编写简单的查询语句1.2第三章限制数据和对数据排序1.5第四章单行函数2.5第五章多表查询2第六章分组函数2第七章子查询2第八章数据操作与事务控制3.5第九章表和约束4第十章其他数据库对象2.53第八章数据操作与事务控制目标:本章旨在向学员介绍：1)数据操作语句2）事务控制语句时间：3.5学时教学方法：讲授ppt＋上机练习4本章要点•INSERT语句•UPDATE语句•DELETE语句•9i新增MERGE语句•COMMIT命令•ROLLBACK命令•管理锁5第八章数据操作与事务控制•数据操作语言（DML：DataManipulationLanguage）•主要包括以下语句：–INSERT–UPDATE–DELETE–MERGE•事务是一组相关的DML语句的逻辑组合。事务控制主要包括下列命令：–COMMIT–ROLLBACK–SAVEPOINT6第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁78.1.1INSERT语法结构•语法如下：–一次只插入一行–NULL的使用，连续的单引号（‘’）也可以表示空值。–插入日期型数据–插入特殊字符–插入多行数据–按列的默认顺序列出各个列的值–在Insert子句中可以随意列出列名和他们的值–字符和日期型数据应该包含在单引号中INSERTINTO表名[(列名1[,列名2，…，列名n])]VALUES(值1[,值2，…，值n]);88.1.2INSERT语句插入单行数据1/2•例8-1将一个新成立部门的信息写入departments表INSERTINTOdepartmentsVALUES(300,'Operations',110,1500);98.1.2INSERT语句插入单行数据2/2•显示默认值概述–使用Default表示默认值–符合SQL：1999标准–可以使用显示默认值控制默认值的使用–显示默认值可以在insert和update语句中使用108.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);118.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’);128.1.5INSERT语句插入特殊字符•查看ESCAPE转义符用哪个符号表示。•INSERT语句中使用“\”符对特殊符号转义。INSERTINTOtestVALUES（‘\&TEST\&’）;SHOWESCAPE；——查看ESCAPE状态escapeOFF——返回ESCAPE状态为OFFSETESCAPEON；——设定ESCAPE状态为ONSHOWESCAPE；——查看ESCAPE状态escape\(hex5c)——返回ESCAPE符号为“\”INSERTINTOtestVALUES（‘&TEST&’）;138.1.6INSERT语句插入多行数据•语法•例8-7将受雇日期在“1995-1-1”之前的员工信息复制到hemployees表中。INSERTINTO表名[(列名1[,列名2，…，列名n])]子查询；INSERTINTOhemployeesSELECT*FROMemployeesWHEREhire_dateTO_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简单修改UPDATEemployeesSETsalary=salary+200,manager_id=103WHEREdepartment_id=60;UPDATE表名SET列名=表达式[，列名=表达式，···][WHERE条件表达式]；UPDATEemployeesSETsalary=salary*(1+0.2);168.2.2UPDATE嵌入子查询修改1/2•嵌入子查询修改•除基于表自身实现嵌入子查询的方式实现修改操作外，也可以在子查询中基于其他表实现修改操作。UPDATEemployeesSETdepartment_id=10,salary=500+(SELECTAVG(salary)FROMemployees)WHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=110)ANDemployee_id110;178.2.2UPDATE嵌入子查询修改2/2•相关子查询修改–updateemployeesa–setsalary=salary+(selectavg(salary)fromemployeesbwhereb.department_id=a.department_id)188.2.3修改数据(完整性错误)•updateemployeessetdepartment_id=55wheredepartment_id=100;•ORA-02291:违反完整约束条件(NEU.EMP_DEPT_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条件表达式]；DELETEFROMdepartmentsWHEREdepartment_id=210;218.3.2DELETE删除数据1/3•例8-12删除管理者编号（manager_id）为205的部门，相应部门的员工予以解聘，不包括205号员工。•DELETE语句不能删除被其他表引用了的记录值。DELETEFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHEREmanager_id=205)ANDemployee_id205;228.3.2DELETE删除数据2/3•删除数据（完整性错误）–deletefromdepartmentswheredepartment_id=100;–ORA-02292:违反完整约束条件(NEU.EMP_DEPT_FK)-已找到子记录日志238.3.2DELETE删除数据3/3•删除数据（相关子查询）–deleteemp_copya–whereexists(select'1'fromemployeesbwhereb.employee_id=a.employee_id)24第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁258.4合并数据•根据指定的条件执行插入或者更新操作•如果满足条件的行存在，执行更新操作，否则执行插入操作•优点：避免独立的数据更新提高效率而且使用方便在数据仓库应用中经常使用268.4.1MERGE语法结构1/2•MERGE语法结构MERGEINTOtable_name[t_alias]USING{table|view|subquery}[t_alias]ON(joincondition)WHENMATCHEDTHENUPDATESETcol1=col1_val[,col2=col2_val···]WHENNOTMATCHEDTHENINSERT（column_list）VALUES（column_values）;278.4.1MERGE语法结构2/2–INTO子句：指定更新或插入的目标表–USING子句：指定更新或插入的数据源，它可以是表、视图、子查询–ON子句：合并操作的条件判断语句288.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表中。29第八章数据操作与事务控制数据操作与事务控制:8.1插入数据8.2修改数据8.3删除数据8.4合并数据8.5使用ROWID操作数据8.6事务处理8.7锁308.5.1ROWID介绍1/2•ROWID：–伪列，是表中虚拟的列，是系统自动产生的。–每一行记录中都包含ROWID，表示这一行的唯一地址。–ROWID标识了Oracle如何定位行，通过ROWID能快速定位一行记录。ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龙SELECTrowid,cnameFROMdossier;318.5.1ROWID介绍2/2•ROWID的格式：–ROWID中包含该行数据的物理位置信息，所以能快速的定位记录ROWIDCNAMEAAAHjXAAOAAAADaAAA姚明AAAHjXAAOAAAADaAAC成龙例：AAAHjXAAOAAAADaAAA数据对象编号文件编号块编号行编号AAAHjXAAOAAAADaAAAOOOOOOFFFBBBBBBRRR数据对象编号相关文件编号块编号行编号328.5.2使用ROWID进行数据操作•ROWID的应用：–快速定位单行记录，DML语句可以使用ROWID操作数据，效率最快–作为表行的唯一标识例:使用ROWNUM修改数据UPDATEemployeesSETfirst_name=first_name||'*'WHEREROWID='AAAMg6AAFAAAABUAAA';33第八章