云南公司做网站的价格网站模版整站下载

云南公司做网站的价格,网站模版整站下载,wordpress添加专题功能,程序员招聘求职的网站Oracle 事务与约束详解 事务和约束是 Oracle 数据库数据完整性和一致性的两大基石。事务确保操作的原子性#xff0c;约束确保数据的业务规则。一、事务#xff08;Transaction#xff09;基础 1.1 ACID 特性特性说明Oracle 实现原子性 (Atomicity)要么全部成功#xff0c;…Oracle 事务与约束详解事务和约束是 Oracle 数据库数据完整性和一致性的两大基石。事务确保操作的原子性约束确保数据的业务规则。一、事务Transaction基础1.1 ACID 特性特性说明Oracle 实现原子性 (Atomicity)要么全部成功要么全部回滚Undo 表空间、回滚段一致性 (Consistency)事务前后数据状态合法约束检查、触发器隔离性 (Isolation)并发事务互不干扰锁机制、MVCC持久性 (Durability)提交后永久保存Redo 日志、数据文件1.2 事务生命周期-- 1. 开始事务Oracle 自动开始UPDATEemployeesSETsalary8000WHEREemployee_id101;-- 2. 执行 DML 操作INSERTINTOdepartmentsVALUES(280,Cloud Engineering,1700);-- 3. 保存点可选SAVEPOINTsp1;-- 4. 继续操作DELETEFROMemployeesWHEREemployee_id999;-- 5. 回滚到保存点ROLLBACKTOsp1;-- 仅撤销 DELETE-- 6. 提交事务COMMIT;-- 所有更改永久生效-- 或回滚整个事务ROLLBACK;-- 撤销所有更改二、Oracle 事务控制语句2.1 显式控制-- COMMIT提交事务COMMIT[WORK][COMMENT注释];-- COMMENT 用于分布式事务-- ROLLBACK回滚事务ROLLBACK[WORK];ROLLBACK[WORK]TO[SAVEPOINT]保存点名称;-- 部分回滚-- SAVEPOINT设置保存点SAVEPOINT保存点名称;-- SET TRANSACTION设置事务属性SETTRANSACTIONREADONLY;-- 只读事务SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- 设置隔离级别SETTRANSACTIONNAME订单处理;-- 命名事务2.2 隐式提交以下操作会自动提交当前事务DDL 语句CREATE,ALTER,DROP,TRUNCATEDCL 语句GRANT,REVOKE退出 SQL*Plus正常退出陷阱示例UPDATEemployeesSETsalary9000WHEREemployee_id101;CREATETABLEtemp_table(id NUMBER);-- 自动提交 UPDATEROLLBACK;-- 已无法回滚 salary 的修改2.3 自治事务在触发器或存储过程中独立提交不影响主事务。CREATEORREPLACEPROCEDURElog_error(err_msg VARCHAR2)ISPRAGMA AUTONOMOUS_TRANSACTION;-- 声明自治事务BEGININSERTINTOerror_log(message,log_date)VALUES(err_msg,SYSDATE);COMMIT;-- 独立提交END;/三、约束Constraint类型3.1 约束分类总览CREATETABLEemployees(employee_id NUMBER(6)CONSTRAINTemp_pkPRIMARYKEY,-- 主键约束email VARCHAR2(25)CONSTRAINTemp_email_ukUNIQUE,-- 唯一约束first_name VARCHAR2(20)NOTNULL,-- 非空约束last_name VARCHAR2(25)NOTNULL,salary NUMBER(8,2)CONSTRAINTemp_salary_ckCHECK(salary0),-- 检查约束hire_dateDATEDEFAULTSYSDATE,department_id NUMBER(4)CONSTRAINTemp_dept_fkREFERENCESdepartments(department_id)-- 外键约束);3.2 主键约束PRIMARY KEY作用唯一标识一行非空且唯一-- 建表时创建CREATETABLEproducts(product_id NUMBERPRIMARYKEY,name VARCHAR2(100));-- 表级约束推荐可命名CREATETABLEproducts(product_id NUMBER,name VARCHAR2(100),CONSTRAINTprod_pkPRIMARYKEY(product_id));-- 组合主键CREATETABLEorder_items(order_id NUMBER,item_id NUMBER,quantity NUMBER,CONSTRAINTpk_order_itemsPRIMARYKEY(order_id,item_id));-- 修改表添加主键ALTERTABLEproductsADDCONSTRAINTprod_pkPRIMARYKEY(product_id);-- 禁用主键数据仓库批量加载时ALTERTABLEproductsDISABLECONSTRAINTprod_pk;ALTERTABLEproductsENABLECONSTRAINTprod_pk;3.3 外键约束FOREIGN KEY作用维护表间引用完整性-- 基础外键CREATETABLEemployees(employee_id NUMBERPRIMARYKEY,department_id NUMBERCONSTRAINTemp_dept_fkREFERENCESdepartments(department_id));-- 级联删除ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id)ONDELETECASCADE;-- 删除部门时员工自动删除-- 级联置空ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id)ONDELETESETNULL;-- 删除部门时员工 dept_id 设为 NULL-- 外键限制-外键列必须是主键或唯一键-外键列类型必须与引用列一致-外键可以引用同一表自引用外键检查时机-- IMMEDIATE默认每行 DML 后立即检查-- DEFERRED事务提交时检查用于循环依赖ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id)DEFERRABLE INITIALLY DEFERRED;-- 可延迟约束3.4 唯一约束UNIQUE作用确保列值唯一但允许多个 NULL-- 列级唯一约束CREATETABLEusers(user_id NUMBERPRIMARYKEY,email VARCHAR2(100)UNIQUE);-- 表级唯一约束可命名CREATETABLEusers(user_id NUMBERPRIMARYKEY,email VARCHAR2(100),CONSTRAINTusers_email_ukUNIQUE(email));-- 组合唯一约束CREATETABLEuser_roles(user_id NUMBER,role_id NUMBER,CONSTRAINTuk_user_rolesUNIQUE(user_id,role_id));3.5 非空约束NOT NULL作用列不能为 NULL-- 建表时定义CREATETABLEproducts(product_id NUMBERNOTNULL,name VARCHAR2(100)NOTNULL);-- 修改表添加非空约束ALTERTABLEproductsMODIFY(nameNOTNULL);-- 删除非空约束通过 MODIFYALTERTABLEproductsMODIFY(nameNULL);3.6 检查约束CHECK作用强制列值满足条件-- 检查 salary 范围CREATETABLEemployees(employee_id NUMBERPRIMARYKEY,salary NUMBER(8,2)CHECK(salaryBETWEEN1000AND50000));-- 复杂检查约束CREATETABLEemployees(employee_id NUMBERPRIMARYKEY,salary NUMBER(8,2),commission_pct NUMBER(2,2),CONSTRAINTchk_salary_commissionCHECK((salary0ANDcommission_pctISNULL)OR(salary5000ANDcommission_pctBETWEEN0AND0.5)));-- 修改表添加检查约束ALTERTABLEemployeesADDCONSTRAINTchk_hire_dateCHECK(hire_dateSYSDATE);3.7 默认值DEFAULT作用插入时未指定值则使用默认值CREATETABLEorders(order_id NUMBERPRIMARYKEY,order_dateDATEDEFAULTSYSDATE,-- 当前日期statusVARCHAR2(20)DEFAULTNEW,created_by VARCHAR2(50)DEFAULTUSER-- 当前用户);-- 插入时使用默认值INSERTINTOorders(order_id)VALUES(101);-- order_dateSYSDATE, statusNEW四、约束状态管理4.1 约束状态组合每个约束有四种状态组合ENABLE VALIDATE默认状态启用且验证现有数据最严格ENABLE NOVALIDATE启用但不验证现有数据仅对新数据生效DISABLE VALIDATE禁用但验证现有数据阻止新DMLDISABLE NOVALIDATE禁用且不验证数据仓库场景-- 查看约束状态SELECTconstraint_name,status,validatedFROMuser_constraintsWHEREtable_nameEMPLOYEES;-- 禁用约束数据加载时提升性能ALTERTABLEemployeesDISABLECONSTRAINTemp_salary_ck;-- 启用并验证加载后重新启用ALTERTABLEemployeesENABLEVALIDATECONSTRAINTemp_salary_ck;-- 启用但不验证存在脏数据时ALTERTABLEemployeesENABLENOVALIDATECONSTRAINTemp_salary_ck;-- 延迟约束检查事务级ALTERTABLEemployeesMODIFYCONSTRAINTemp_dept_fk DEFERRABLE INITIALLY IMMEDIATE;-- 在事务中临时延迟SETCONSTRAINTemp_dept_fk DEFERRED;INSERTINTOemployees(...)VALUES(...);-- 暂时违反外键UPDATEdepartmentsSETdepartment_id...;-- 使引用有效COMMIT;-- 提交时检查五、事务与约束的交互5.1 约束检查时机IMMEDIATE 约束默认-- 每行 DML 后立即检查UPDATEemployeesSETdepartment_id999WHEREemployee_id101;-- 报错ORA-02291: 违反外键约束DEFERRED 约束-- 事务提交时检查SETCONSTRAINTemp_dept_fk DEFERRED;INSERTINTOemployees(employee_id,department_id)VALUES(999,999);-- 成功-- 在提交前插入被引用的部门INSERTINTOdepartments(department_id)VALUES(999);COMMIT;-- 检查通过成功提交5.2 违反约束的事务回滚BEGININSERTINTOemployees(employee_id,salary)VALUES(999,-100);-- 违反检查约束EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(违反约束: ||SQLERRM);ROLLBACK;-- 回滚整个事务END;/5.3 约束与性能-- 批量插入时禁用约束可提升性能ALTERTABLEemployeesDISABLECONSTRAINTemp_salary_ck;-- 批量插入INSERTINTOemployeesSELECT*FROMemployees_staging;-- 启用并验证ALTERTABLEemployeesENABLEVALIDATECONSTRAINTemp_salary_ck;六、高级主题6.1 约束异常处理-- 创建异常表EXECDBMS_ERRLOG.CREATE_ERROR_LOG(EMPLOYEES,EMPLOYEES_ERRLOG);-- 批量插入时记录异常INSERTINTOemployeesSELECT*FROMemployees_temp LOGERRORSINTOEMPLOYEES_ERRLOG(Batch Load)REJECTLIMITUNLIMITED;-- 查看异常记录SELECTora_err_number$,ora_err_mesg$,first_nameFROMEMPLOYEES_ERRLOG;6.2 视图上的约束-- WITH CHECK OPTION防止通过视图插入不符合视图条件的数据CREATEVIEWhigh_empASSELECT*FROMemployeesWHEREsalary10000WITHCHECKOPTION;-- 尝试插入 low salary 将失败INSERTINTOhigh_empVALUES(...,5000);-- ORA-01402: 违反 CHECK OPTION6.3 物化视图约束-- 物化视图可添加约束CREATEMATERIALIZEDVIEWemp_mv BUILD IMMEDIATE REFRESH FASTONCOMMITENABLEQUERY REWRITEASSELECTemployee_id,salaryFROMemployees;-- 添加主键约束ALTERMATERIALIZEDVIEWemp_mvADDCONSTRAINTemp_mv_pkPRIMARYKEY(employee_id);七、最佳实践与避坑指南7.1 约束设计最佳实践✅命名规范表名_列名_约束类型如emp_salary_ck✅尽早定义建表时定义约束避免后期数据污染✅优先使用 NOT NULL约束开销最小性能影响最低✅外键加索引外键列必须创建索引避免全表锁✅检查约束简单避免复杂函数影响 DML 性能✅文档化约束记录业务含义便于维护7.2 常见陷阱❌外键无索引导致子表全表锁并发性能差❌循环外键A→B→C→A设计缺陷❌延迟约束滥用增加复杂性难以调试❌CHECK 中使用函数导致无法创建函数索引❌大批量 DML 不禁用约束性能慢且产生大量 UNDO7.3 外键索引示例-- 外键列必须创建索引CREATETABLEorder_items(order_id NUMBER,item_id NUMBER,CONSTRAINTfk_order_itemsFOREIGNKEY(order_id)REFERENCESorders(order_id));-- 创建索引否则删除父表记录时会锁整个子表CREATEINDEXidx_order_items_order_idONorder_items(order_id);八、查询与维护8.1 查询约束信息-- 查询表的所有约束SELECTconstraint_name,constraint_type,search_condition,status,validatedFROMuser_constraintsWHEREtable_nameEMPLOYEESORDERBYconstraint_type;-- 约束类型说明-- P: Primary Key, U: Unique, R: Foreign Key, C: Check-- 查询外键引用关系SELECTa.constraint_name,a.table_name,b.column_name,a.r_constraint_name,c.table_nameASref_table,d.column_nameASref_columnFROMuser_constraints aJOINuser_cons_columns bONa.constraint_nameb.constraint_nameJOINuser_constraints cONa.r_constraint_namec.constraint_nameJOINuser_cons_columns dONc.constraint_named.constraint_nameWHEREa.constraint_typeRANDa.table_nameEMPLOYEES;8.2 批量操作约束-- 导出约束定义数据迁移时使用SELECTDBMS_METADATA.GET_DDL(CONSTRAINT,constraint_name)FROMuser_constraintsWHEREtable_nameEMPLOYEES;-- 禁用表的所有约束BEGINFORcIN(SELECTconstraint_nameFROMuser_constraintsWHEREtable_nameEMPLOYEES)LOOPEXECUTEIMMEDIATEALTER TABLE employees DISABLE CONSTRAINT ||c.constraint_name;ENDLOOP;END;/九、总结事务 vs 约束对比维度事务约束作用保证操作原子性保证数据合法性粒度会话级跨语句行级单条数据生命周期显式/隐式开始和结束持久化在数据字典中性能影响UNDO/REDO 开销索引维护、检查开销典型场景转账、订单创建主键唯一、外键关联设计原则事务要短避免长事务占用资源约束要早建表时定义数据入库前校验外键要索引避免性能陷阱延迟要谨慎仅用于循环依赖场景掌握事务和约束是构建健壮、可靠、高性能Oracle 应用的基石。