【Oracle数据库指南】第06篇：Oracle DML语句与事务控制——数据操作与ACID特性深度解析

上一篇【第05篇】Oracle子查询与集合操作——嵌套查询与结果合并全解析下一篇【第07篇】SQL*Plus基础——登录、环境设置与缓冲区操作摘要本文全面讲解Oracle DML数据操作语言语句包括INSERT、UPDATE、DELETE和MERGE的详细用法以及Oracle事务管理的核心机制——COMMIT、ROLLBACK、SAVEPOINT。深入理解ACID特性和Oracle的多版本并发控制MVCC机制帮助读者写出正确、高效、安全的数据操作代码。一、引言DMLData Manipulation Language数据操作语言是数据库操作的核心负责数据的增删改。不同于DDL如CREATE TABLEDML语句不会自动提交需要显式执行COMMIT或ROLLBACK来完成事务。Oracle的DML操作具有以下特点多版本并发控制MVCC读操作不阻塞写操作写操作不阻塞读操作行级锁定DML只锁定受影响的行而非整张表撤销数据UNDO所有修改操作都有对应的撤销记录支持回滚二、INSERT语句2.1 基本INSERT-- 基本语法INSERT INTO 表名 [(列名列表)] VALUES (值列表)-- 方式1指定列名推荐INSERTINTOemployees(employee_id,first_name,last_name,email,hire_date,job_id,salary)VALUES(207,Zhang,Wei,ZWEI,TO_DATE(2024-03-01,YYYY-MM-DD),IT_PROG,6000);-- 方式2不指定列名值的顺序必须与表定义的列顺序完全一致不推荐INSERTINTOemployeesVALUES(208,Li,Mei,LMEI,18612345678,TO_DATE(2024-03-01,YYYY-MM-DD),SA_REP,5000,0.15,149,80);-- 插入NULL值INSERTINTOemployees(employee_id,first_name,last_name,email,hire_date,job_id,salary)VALUES(209,Wang,Gang,WGANG,SYSDATE,HR_REP,NULL);-- 显式NULL-- 插入部分列未指定的列自动填充NULL或默认值INSERTINTOemployees(employee_id,first_name,last_name,email,hire_date,job_id)VALUES(210,Liu,Yang,LYANG,SYSDATE,FI_ACCOUNT);-- salary列未指定若有DEFAULT值则用DEFAULT否则为NULLCOMMIT;-- 提交事务2.2 基于子查询的INSERT-- INSERT ... SELECT将查询结果批量插入-- 将employees表中IT部门的员工信息备份到备份表CREATETABLEit_employees_backupASSELECT*FROMemployeesWHERE12;-- 创建空表DDL会自动提交INSERTINTOit_employees_backupSELECT*FROMemployeesWHEREdepartment_id60;SELECTCOUNT(*)FROMit_employees_backup;COMMIT;-- 跨表数据迁移只复制特定列INSERTINTOemployees_archive(employee_id,last_name,salary,dept_id,archive_date)SELECTemployee_id,last_name,salary,department_id,SYSDATEFROMemployeesWHEREhire_dateTO_DATE(2005-01-01,YYYY-MM-DD);COMMIT;2.3 多行INSERTINSERT ALL-- 无条件多行INSERT每行数据插入多个表INSERTALLINTOemployees_log(log_id,action,employee_id,log_time)VALUES(emp_log_seq.NEXTVAL,INSERT,211,SYSDATE)INTOemployees(employee_id,first_name,last_name,email,hire_date,job_id,salary)VALUES(211,Chen,Fang,CFANG,SYSDATE,SA_REP,4500)SELECT*FROMDUAL;-- 条件多行INSERT根据条件决定插入哪些表INSERTALLWHENsalary5000THENINTOlow_salary_emp(employee_id,last_name,salary)VALUES(employee_id,last_name,salary)WHENsalaryBETWEEN5001AND10000THENINTOmid_salary_emp(employee_id,last_name,salary)VALUES(employee_id,last_name,salary)WHENsalary10000THENINTOhigh_salary_emp(employee_id,last_name,salary)VALUES(employee_id,last_name,salary)SELECTemployee_id,last_name,salaryFROMemployees;-- FIRST关键字找到第一个匹配条件后停止避免一行数据插入多个表INSERTFIRSTWHENsalary15000THENINTOexec_employeesVALUES(employee_id,last_name,salary)WHENsalary10000THENINTOsenior_employeesVALUES(employee_id,last_name,salary)WHENsalary5000THENINTOregular_employeesVALUES(employee_id,last_name,salary)ELSEINTOjunior_employeesVALUES(employee_id,last_name,salary)SELECTemployee_id,last_name,salaryFROMemployees;COMMIT;三、UPDATE语句3.1 基本UPDATE-- 语法UPDATE 表名 SET 列1值1 [, 列2值2 ...] [WHERE 条件]-- 警告没有WHERE子句的UPDATE会更新表中所有行-- 更新单列UPDATEemployeesSETsalarysalary*1.10WHEREdepartment_id60;-- IT部门涨薪10%-- 更新多列UPDATEemployeesSETsalary9000,job_idIT_MAN,commission_pctNULLWHEREemployee_id207;-- 使用表达式更新UPDATEemployeesSETsalaryCASEWHENjob_idIT_PROGTHENsalary*1.15WHENjob_idSA_REPTHENsalary*1.10ELSEsalary*1.05END,last_modified_dateSYSDATEWHEREdepartment_idIN(60,80);COMMIT;3.2 基于子查询的UPDATE-- 使用子查询的UPDATE根据另一张表的数据更新当前表-- 将员工薪资更新为其所在部门的平均薪资示例实际应慎用UPDATEemployees eSETsalary(SELECTAVG(salary)FROMemployeesWHEREdepartment_ide.department_id)WHEREdepartment_id60;ROLLBACK;-- 演示用回滚-- 使用子查询同时更新多列UPDATEemployees eSET(salary,job_id)(SELECTAVG(salary),MAX(job_id)FROMemployeesWHEREdepartment_id60)WHEREemployee_id207;ROLLBACK;-- 基于另一张表的条件更新UPDATEemployeesSETsalarysalary*1.20WHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHERElocation_id1700);COMMIT;四、DELETE语句4.1 基本DELETE-- 语法DELETE [FROM] 表名 [WHERE 条件]-- 警告没有WHERE子句的DELETE会删除表中所有行-- 删除单行DELETEFROMemployeesWHEREemployee_id210;-- 条件删除多行DELETEFROMemployeesWHEREdepartment_id60ANDsalary4000;-- 基于子查询的删除DELETEFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHERElocation_id1900);COMMIT;4.2 DELETE vs TRUNCATE的对比特性DELETETRUNCATE语句类型DML可回滚DDL不可回滚是否记录UNDO是每行都记录否只记录页级别操作是否可以加WHERE条件是否触发TRIGGER是否Oracle默认重置AUTO INCREMENT否是重置序列等性能慢大量数据时快几乎瞬间-- TRUNCATE清空表DDL操作立即提交不可回滚TRUNCATETABLEit_employees_backup;-- 立即清空无法回滚-- 对比DELETE ALL可回滚DELETEFROMit_employees_backup;-- DML可以ROLLBACKROLLBACK;-- 还原数据五、MERGE语句UPSERTMERGE是Oracle 9i引入的强大语句可以在单条SQL中实现存在则更新不存在则插入的逻辑即UPSERT。-- MERGE基本语法MERGEINTOtarget_table tUSINGsource_table sON(t.key_columns.key_column)-- 匹配条件WHENMATCHEDTHENUPDATESETt.col1s.col1,t.col2s.col2[DELETEWHERE条件]WHENNOTMATCHEDTHENINSERT(col1,col2)VALUES(s.col1,s.col2);-- 实战示例将临时导入表中的数据同步到正式员工表MERGEINTOemployees tUSINGemployees_import sON(t.employee_ids.employee_id)WHENMATCHEDTHENUPDATESETt.salarys.salary,t.job_ids.job_id,t.last_updateSYSDATEWHEREt.salary!s.salaryORt.job_id!s.job_id-- 只在有变化时更新WHENNOTMATCHEDTHENINSERT(employee_id,first_name,last_name,email,hire_date,job_id,salary,department_id)VALUES(s.employee_id,s.first_name,s.last_name,s.email,NVL(s.hire_date,SYSDATE),s.job_id,s.salary,s.department_id);COMMIT;六、事务控制6.1 事务的ACID特性Oracle通过UNDO表空间、重做日志、锁机制共同保证事务的ACID特性特性说明Oracle实现机制原子性Atomicity事务中的操作要么全部成功要么全部回滚UNDO记录 ROLLBACK机制一致性Consistency事务前后数据库保持一致状态约束检查 UNDO隔离性Isolation并发事务相互隔离互不干扰行级锁 MVCC持久性Durability提交的事务永久保存REDO日志 检查点机制6.2 事务控制语句-- COMMIT提交事务使修改永久生效-- 触发时机-- 1. 显式执行COMMIT-- 2. 执行DDL语句CREATE/ALTER/DROP等会隐式提交-- 3. 正常退出SQL*PlusEXIT/QUIT-- ROLLBACK回滚事务撤销所有未提交的修改-- 触发时机-- 1. 显式执行ROLLBACK-- 2. 异常退出断连、崩溃-- SAVEPOINT在事务中设置保存点可以部分回滚INSERTINTOemployees(employee_id,first_name,last_name,email,hire_date,job_id,salary)VALUES(220,Test1,User,TUSER1,SYSDATE,IT_PROG,5000);SAVEPOINTsp1;-- 设置保存点INSERTINTOemployees(employee_id,first_name,last_name,email,hire_date,job_id,salary)VALUES(221,Test2,User,TUSER2,SYSDATE,IT_PROG,5500);SAVEPOINTsp2;-- 设置第二个保存点UPDATEemployeesSETsalary99999WHEREemployee_id220;-- 错误操作ROLLBACKTOSAVEPOINTsp1;-- 回滚到sp1sp1之后的操作全部撤销-- 此时只有员工220还存在且薪资是5000员工221已撤销COMMIT;-- 提交员工220的插入6.3 Oracle的隔离级别Oracle支持两种标准隔离级别默认READ COMMITTED-- 查看当前隔离级别-- Oracle使用MVCC默认隔离级别接近REPEATABLE READ不会出现幻读-- 设置会话隔离级别SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;-- 读已提交默认SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- 串行化SETTRANSACTIONREADONLY;-- 只读事务快照读-- Oracle读不阻塞写、写不阻塞读的原因-- 读操作通过UNDO数据读取旧版本数据一致性读不需要加共享锁-- 写操作只锁定被修改的行行级排他锁6.4 行级锁与并发控制-- SELECT FOR UPDATE显式加行锁阻止其他事务修改SELECTemployee_id,salaryFROMemployeesWHEREdepartment_id60FORUPDATE;-- 锁定查询到的所有行直到COMMIT或ROLLBACK-- NOWAIT如果行已被锁定立即报错而非等待SELECTemployee_id,salaryFROMemployeesWHEREemployee_id100FORUPDATENOWAIT;-- WAIT n等待n秒超时则报错SELECTemployee_id,salaryFROMemployeesWHEREemployee_id100FORUPDATEWAIT5;-- SKIP LOCKED跳过已锁定的行适用于并发任务处理SELECTemployee_id,task_statusFROMjob_queueWHEREtask_statusPENDINGFORUPDATESKIP LOCKED;-- 多个进程并发处理队列各自取未锁定的任务七、综合实战案例案例一安全的批量数据更新-- 企业实践分批次更新大量数据避免大事务和锁表-- 每次更新1000行提交后继续下一批DECLAREv_rows_updated NUMBER;v_batch_size NUMBER :1000;BEGINLOOPUPDATEemployeesSETsalarysalary*1.05WHEREdepartment_id60ANDrownumv_batch_sizeANDsalary_updatedN;-- 未处理标志v_rows_updated :SQL%ROWCOUNT;COMMIT;-- 分批提交EXITWHENv_rows_updated0;-- 没有更多行时退出ENDLOOP;DBMS_OUTPUT.PUT_LINE(批量更新完成);END;/案例二完整的业务事务处理-- 模拟员工部门调动的完整事务DECLAREv_emp_id employees.employee_id%TYPE:150;v_new_dept departments.department_id%TYPE:90;v_old_dept departments.department_id%TYPE;v_old_salary employees.salary%TYPE;ex_emp_not_found EXCEPTION;ex_dept_not_found EXCEPTION;BEGIN-- 步骤1查询员工当前信息SELECTdepartment_id,salaryINTOv_old_dept,v_old_salaryFROMemployeesWHEREemployee_idv_emp_id;-- 步骤2验证新部门存在DECLAREv_dummy NUMBER;BEGINSELECT1INTOv_dummyFROMdepartmentsWHEREdepartment_idv_new_dept;EXCEPTIONWHENNO_DATA_FOUNDTHENRAISE ex_dept_not_found;END;-- 步骤3记录调动历史INSERTINTOjob_history(employee_id,start_date,end_date,job_id,department_id)SELECTemployee_id,hire_date,SYSDATE,job_id,department_idFROMemployeesWHEREemployee_idv_emp_id;SAVEPOINTafter_history;-- 步骤4更新员工记录UPDATEemployeesSETdepartment_idv_new_dept,hire_dateSYSDATE-- 更新到新部门的入职日期WHEREemployee_idv_emp_id;-- 步骤5记录操作日志INSERTINTOchange_log(change_time,change_type,description)VALUES(SYSDATE,DEPT_TRANSFER,员工||v_emp_id||从部门||v_old_dept||调至||v_new_dept);COMMIT;DBMS_OUTPUT.PUT_LINE(员工调动事务成功提交);EXCEPTIONWHENNO_DATA_FOUNDTHENROLLBACK;DBMS_OUTPUT.PUT_LINE(错误员工不存在);WHENex_dept_not_foundTHENROLLBACK;DBMS_OUTPUT.PUT_LINE(错误目标部门不存在);WHENOTHERSTHENROLLBACK;DBMS_OUTPUT.PUT_LINE(未知错误||SQLERRM);RAISE;-- 重新抛出异常END;/八、常见问题与陷阱Q1DDL语句为什么会自动提交Oracle中DDL语句CREATE/ALTER/DROP/TRUNCATE等执行前后都会隐式提交当前事务。这意味着-- 危险操作演示INSERTINTOtest_tableVALUES(1,test);-- 未提交的INSERTCREATETABLEanother_test(id NUMBER);-- DDL触发隐式COMMITROLLBACK;-- 已经来不及了INSERT已被提交最佳实践在执行DDL之前确保先COMMIT或ROLLBACK当前事务。Q2UPDATE/DELETE没有WHERE条件怎么办-- 如果误执行了没有WHERE的UPDATE/DELETE-- 方法1立即ROLLBACK如果事务未提交ROLLBACK;-- 方法2使用Flashback Query恢复需要充足的UNDO保留期-- 查看5分钟前的数据状态SELECT*FROMemployeesASOFTIMESTAMP(SYSTIMESTAMP-INTERVAL5MINUTE);-- 使用Flashback恢复表FLASHBACKTABLEemployeesTOTIMESTAMP(SYSTIMESTAMP-INTERVAL5MINUTE);Q3大事务的风险-- 问题一个长时间运行的大事务会占用大量UNDO空间-- 如果UNDO表空间不足会报ORA-01555: snapshot too old-- 解决方案对大批量操作进行分批处理-- 示例分批删除旧数据BEGINLOOPDELETEFROMorder_historyWHEREcreate_dateADD_MONTHS(SYSDATE,-24)ANDROWNUM10000;-- 每批10000行EXITWHENSQL%ROWCOUNT0;COMMIT;ENDLOOP;END;/九、最佳实践及时提交或回滚DML操作后尽快COMMIT或ROLLBACK长时间持锁会影响并发性能避免大事务大批量DML操作应分批进行防止UNDO空间耗尽先SELECT后UPDATE/DELETE修改前先查询确认条件避免误操作使用SAVEPOINT复杂业务逻辑中适时设置SAVEPOINT支持局部回滚注意DDL的隐式提交执行DDL前先处理未完成的DML事务生产环境谨用TRUNCATETRUNCATE不可回滚务必确认后再执行十、总结本文系统讲解了Oracle DML和事务控制的完整体系INSERT单行、多行、子查询INSERT和INSERT ALLUPDATE单列、多列、基于子查询的UPDATEDELETE条件删除及与TRUNCATE的对比MERGEUPSERT语义的实现事务控制COMMIT、ROLLBACK、SAVEPOINT的使用ACID特性Oracle通过UNDO/REDO/锁机制保证事务完整性掌握DML和事务控制是Oracle开发的核心基础技能。下一篇将进入SQL*Plus的学习掌握这个强大的命令行工具的使用技巧。上一篇【第05篇】Oracle子查询与集合操作——嵌套查询与结果合并全解析下一篇【第07篇】SQL*Plus基础——登录、环境设置与缓冲区操作参考资料《Oracle 11g数据库管理员指南》— 刘宪军著Oracle官方文档SQL Language Reference - DML StatementsOracle官方文档SQL Language Reference - Transaction Control