Oracle数据库视图, 事务

1. 项目介绍

1.1 需求文档

一、项目目标 构建银行保险业务管理系统，支持客户管理、保险产品管理、保单管理、保费缴纳、理赔管理等功能。

二、核心模块

客户管理：记录客户基本信息（姓名、身份证号、联系方式等）。
产品管理：管理保险产品（名称、类型、保额、保费等）。
保单管理：关联客户与产品，记录保单状态（生效、过期、终止）。
缴费管理：跟踪保费缴纳记录（缴费时间、金额、是否逾期）。
理赔管理：处理理赔申请（申请时间、金额、状态）。

三、业务规则

一个客户可购买多份保单，一份保单仅关联一个产品。
保费逾期超过30天，保单状态自动标记为”终止”。
理赔申请需关联有效保单。

1.2 数据库开发流程

Oracle 数据库开发流程通常包含以下几个关键阶段：

需求分析
- 与用户沟通：和数据库的使用者、业务人员进行交流，了解他们的业务流程、数据处理需求以及数据的来源和去向。
- 收集业务资料：收集相关的业务文档、报表、表格等，以此明确系统需要处理的数据类型和业务规则。
- 明确功能需求：确定数据库需要支持的功能，像数据的录入、查询、修改、删除等操作，以及数据的安全性和完整性要求。
概念设计
- 识别实体：从业务需求里找出关键的实体，例如：客户、订单、产品等。
- 定义属性：明确每个实体的属性，如：客户的姓名、年龄、地址等。
- 确定关系：分析实体之间的关系，如：一对一、一对多、多对多关系，并用实体 - 联系（E-R）图表示出来。
- 编写概念设计文档：对概念模型进行详细描述，方便后续的设计和沟通。
逻辑设计
- 将E-R图转换为关系模型：把概念设计阶段得到的E-R图转化为关系数据库的表结构，确定表名、列名、数据类型等。
- 应用数据库范式：运用数据库范式（如第一范式、第二范式、第三范式）对表结构进行优化，减少数据冗余，提高数据的一致性和完整性。
- 定义主键和外键：为每个表指定主键，用于唯一标识表中的每一行数据；同时，通过外键建立表与表之间的关联关系。
- 创建视图和索引：依据业务需求，设计合适的视图和索引，以提高数据查询的效率。
物理设计
- 选择存储结构：根据数据库的使用场景和性能要求，选择合适的存储结构，如堆表、索引组织表、分区表等。
- 确定表空间：规划数据库的表空间，将不同类型的数据存储在不同的表空间中，便于管理和维护。
- 设置存储参数：对数据库的存储参数进行设置，如块大小、缓冲区大小、日志文件大小等，以优化数据库的性能。
- 设计索引策略：根据查询需求，确定索引的类型和位置，提高数据查询的速度。
数据库实现
- 创建表空间：使用Oracle工具（如：DBeaver、SQL Developer 等）创建表空间。
- 创建表和约束：依据逻辑设计和物理设计的结果，创建数据库表，并定义主键、外键、唯一约束、检查约束等。
- 插入初始数据：向表中插入初始数据，以便进行测试和验证。
- 创建视图、索引和存储过程：根据设计要求，创建视图、索引和存储过程等数据库对象。
测试与调试
- 功能测试：对数据库的各项功能进行测试，确保数据的录入、查询、修改、删除等操作正常运行。
- 性能测试：使用性能测试工具对数据库的性能进行测试，评估数据库的响应时间、吞吐量等指标，找出性能瓶颈并进行优化。
- 数据完整性测试：检查数据库中的数据是否满足完整性约束条件，如：主键约束、唯一约束等。
- 安全测试：对数据库的安全性进行测试，检查用户权限设置是否合理，数据是否得到了有效的保护。
部署与维护
- 数据库部署：将开发好的数据库部署到生产环境中，确保数据库能够正常运行。
- 数据备份与恢复：制定数据备份策略，定期对数据库进行备份，以防止数据丢失；同时，测试数据恢复方案，确保在出现故障时能够及时恢复数据。
- 性能监控与优化：对数据库的性能进行实时监控，根据监控结果对数据库进行优化，如调整存储参数、优化查询语句等。
- 安全管理：定期检查数据库的安全设置，更新用户权限，防止数据泄露和非法访问。
- 故障处理：及时处理数据库中出现的故障，如系统崩溃、数据损坏等，确保数据库的可用性和稳定性。

1.3 逻辑设计: 三范式

1.3.1 三范式

三范式属于数据库逻辑设计的关键方法，其目的是对数据结构进行优化，降低数据冗余，增强数据的一致性和完整性。在逻辑设计阶段，设计人员依据需求分析的结果，把概念模型(如E-R图)转化为具体的数据库表结构，此时就会运用三范式来指导表结构的设计。

什么是范式？

范式就像”整理房间的规则”：先分类(1NF)，再分抽屉(2NF)，最后标签明确(3NF)。
范式可以让你的数据表结构清晰、不重复、不混乱。
- 比如：你把袜子、鞋子、书本乱堆在房间里，找东西会很麻烦。但如果你按规则分类整理（袜子放抽屉、书本摆书架），房间就会整洁高效。

数据库设计：三范式就是教你如何”整理”数据表的规则，避免数据重复和混乱。

三范式的作用：

消除重复数据：避免同一信息在多个表中重复存储（比如用户姓名存了100次）。
减少操作错误：修改数据时，只需改一个地方，不用到处改。
提升查询效率：结构清晰的表，查询速度更快。
防止数据矛盾：比如用户年龄在A表是20岁，B表却是25岁。

第一范式（1NF）：原子性，不可拆分

规则：每个字段的值必须是”最小单位”，不能再拆分。
错误例子：

学生联系方式

张三浙江省杭州市钱塘区11号大街469号1幢4楼
- 问题：”地址”中包含省、市、区、街道等，混在一起，无法单独查询。

学生	联系方式
张三	浙江省杭州市钱塘区11号大街469号1幢4楼

正确设计：

学生	省份	城市	区县	详细地址
张三	138xxx	杭州市	钱塘区	11号大街469号1幢4楼

一范式：一列只存一个信息，不搞大杂烩。

第二范式（2NF）：消除部分依赖

规则：表必须有唯一标识（主键），且其他字段必须完全依赖主键，不能只依赖主键的一部分。
适用场景：有联合主键的表（比如：用”学号+课程”做主键）。

错误例子（学生选课表）：

学号(主键)	课程	成绩	学生姓名	课程老师
001	数学	90	张三	王老师

问题：
- “学生姓名”只依赖”学号”，和”课程”无关。
- “课程老师”只依赖”课程”，和”学号”无关。
后果：如果学生改名，需要修改所有相关记录，容易出错！

正确设计：拆分成3张表
1. 学生表：学号、学生姓名
2. 课程表：课程、课程老师
3. 选课表：学号、课程、成绩

二范式：一张表只干一件事，不相关的信息拆出去。

第三范式（3NF）：消除传递依赖

规则：表中的字段不能依赖其他非主键字段（只能直接依赖主键）。

错误例子（订单表）：

订单号(主键)	用户ID	用户名	用户等级	订单金额
1001	001	张三	VIP	500

问题：
- “用户名”和”用户等级”依赖”用户ID”，而”用户ID”依赖主键”订单号”。
- 这是”传递依赖”：订单号 → 用户ID → 用户名/用户等级。
后果：同一用户的等级更新时，要修改所有历史订单，极容易出错！

正确设计：拆分成2张表
1. 订单表：订单号、用户ID 、订单金额
2. 用户表：用户ID 、用户名、用户等级

三范式：禁止”套娃”依赖，直接挂钩主键！0

小结：

第一范式：字段拆到最小，不搞混合体。
第二范式：拆掉不相关的数据，表各司其职。
第三范式：禁止间接依赖，只认主键。
口诀：一拆到底，二拆无关，三拆传递。

实际应用时：

先按业务需求列出所有字段。
按三范式一步步拆分表。
检查是否有重复、冗余、依赖问题。
根据性能和复杂度适当调整。

1.3.2 反三范式

反三范式就是故意违反三范式的规则，允许数据冗余、允许字段不完全依赖主键。

举个生活例子：
- 按三范式：外卖菜单应该拆分成「菜品表」「商家表」「分类表」，每次点餐都要联表查。
- 反三范式：直接把商家名称、分类名称写在菜品表里，虽然重复，但点餐时查询更快。
核心思想：用空间换时间（牺牲存储空间，提升查询速度）。

为什么需要反三范式？

三范式设计的数据表虽然”整洁”，但可能导致：

查询变慢：需要频繁联表查询（比如查订单时，要关联用户表、商品表）。
开发复杂：写代码时要处理多表关联，容易出错。
性能瓶颈：高并发场景下，联表操作可能拖垮数据库。

反三范式适合的场景：

读多写少的系统（比如：电商首页、报表统计）。
对查询速度要求极高的场景（比如：实时大屏）。
数据量极大，联表查询成本过高。

一句话总结：为了跑得更快，允许房间里多放几双袜子！

反三范式怎么用？

场景1：直接冗余字段

问题：订单表需要显示用户姓名，按三范式必须关联用户表。

反三范式设计：

CREATE TABLE 订单表 (  
    订单号 NUMBER PRIMARY KEY,  
    用户ID NUMBER,  
    用户名 VARCHAR2(50),     -- 直接冗余用户姓名（违反3NF）
    订单金额 NUMBER  
);  

优点：查订单时无需联表查用户表，速度更快。
代价：如果用户改名，需同时更新所有相关订单（可通过触发器或程序控制）。

场景2：预计算统计值

问题：统计商品销量，按三范式需要实时 SUM(订单明细表.数量)。

反三范式设计：

CREATE TABLE 商品表 (  
    商品ID NUMBER PRIMARY KEY,  
    商品名称 VARCHAR2(100),  
    总销量 NUMBER DEFAULT 0     -- 冗余总销量（违反3NF） 
);  

优点：查销量时直接读字段，无需实时计算。
代价：每次下单需更新商品表的总销量（可通过触发器或事务控制）。

场景3：合并常用关联表

问题：查询学生成绩时，需要关联学生表、课程表。

反三范式设计：

CREATE TABLE 成绩表 (  
    学号 NUMBER,  
    学生姓名 VARCHAR2(50),  -- 冗余学生姓名（违反2NF）
    课程ID NUMBER,
    课程名 VARCHAR2(100),   -- 冗余课程名（违反2NF）  
    成绩 NUMBER  
);  

优点：查成绩时无需联表，直接显示学生姓名和课程名。
代价：学生改名或课程改名时，需同步更新所有历史记录（需权衡是否必要）

反三范式的优缺点：

优点	缺点
查询速度大幅提升	数据冗余占用存储空间
简化SQL复杂度	数据更新成本高（易不一致）
减少联表操作压力	需要额外维护数据一致性

使用反三范式的注意事项：

明确场景：
- 读频率远高于写的场景才适合（比如90%读，10%写）。
数据一致性：
- 用触发器、事务或定时任务同步冗余数据，避免脏数据。
别滥用：
- 如果存储空间紧张，或数据频繁修改，优先遵循三范式。
文档注释：
- 在表结构设计文档中注明冗余字段的作用和同步机制。

小结：

反三范式是什么：为了性能，故意允许数据冗余。
什么时候用：读多写少、查询性能瓶颈时。
怎么用：冗余高频查询字段、预计算统计值、合并常用表。
核心口诀：用冗余换速度，但要管好一致性！

1.4 数据字典

数据字典是对数据库中数据的详细描述，涵盖了表名、列名、数据类型、约束条件、数据含义等信息。它通常在逻辑设计基本完成，表结构确定之后生成，用于记录和管理数据库中的元数据。数据字典的作用在于为数据库开发、维护和使用提供统一的标准和参考，方便开发人员、数据库管理员和业务人员之间的沟通和协作。

三范式用于指导数据库表结构的设计，是逻辑设计阶段的重要方法；

数据字典是对设计好的数据表结构和数据的详细记录。

客户表：clients

字段名	数据类型	长度	注释	主键	备注
client_id	NUMBER	10	客户id	是
name	VARCHAR2	50	客户名字		NOT NULL
id_card	VARCHAR2	18	身份证号		唯一约束

产品表：products

字段名	数据类型	长度	注释	主键	备注
product_id	NUMBER	10	产品id	是
product_name	VARCHAR2	50	产品名称		唯一约束 + NOT NULL
product_type	VARCHAR2	20	产品类型		类型: 寿险、车险等

保单表：policies

字段名	数据类型	长度	注释	主键	备注
policy_no	VARCHAR2	20	保单号	是
client_id	NUMBER	10	关联客户		外键字段
product_id	NUMBER	10	关联产品		外键字段
start_date	DATE		生效日期		默认值: 当前日期
end_date	DATE		到期日期
premium_amount	NUMBER	12,2	保单金额		NOT NULL
status	VARCHAR2	10	保单状态		状态: 有效、过期

缴费记录表：premium_payments

字段名	数据类型	长度	注释	主键	备注
payment_id	NUMBER	10	缴费ID	主键
policy_no	VARCHAR2	20	关联保单		外键字段
payment_date	DATE		缴费日期		默认值: 当前日期
amount	NUMBER	12,2	缴费金额		NOT NULL

理赔表：claims

字段名	数据类型	长度	注释	主键	备注
claim_id	NUMBER	10	理赔ID	是
policy_no	VARCHAR2	20	关联保单		外键字段
claim_date	DATE		申请日期		默认值: 当前日期
claim_amount	NUMBER	12,2	理赔金额		NOT NULL
status	VARCHAR2	10	理赔状态		状态: 待审核/已赔付

2. 项目开发

2.1 创建表空间

创建TS_INSUR表空间

CREATE TABLESPACE TS_INSUR
DATAFILE '/home/oracle/TS_INSUR.dbf'  -- 物理文件存储路径
SIZE 10m       -- 初始化物理文件大小
AUTOEXTEND ON  -- 物理文件存储不中时会自动扩容
NEXT 10m       -- 自动扩容大小为10M
MAXSIZE 2G     -- 存储大小上限为2G

2.2 创建用户

-- 创建用户: insur ，并指定表单为: TS_INSUR
CREATE USER insur DEFAULT TABLESPACE TS_INSUR IDENTIFIED BY 123456

说明：用户在创建之后并不具备任何权限(无法登录使用)

2.3 用户授权

在Oracle数据库中，创建完用户之后，需要给所创建用户授权(授权登录)后，用户才可以正常登录使用。

2.3.1 权限

2.3.1.1 什么是权限

权限就像数据库的”钥匙”，决定了用户能在数据库中做什么。比如：用户能否登录、能否查看某张表、能否创建新表等，都由权限控制。

简单来说，权限就是用户执行特定操作的”许可证”。

2.3.1.2 权限的意义

数据库中权限存在的意义：

保障数据安全：防止未授权的用户访问敏感数据（比如：工资表）。
控制操作范围：限制用户只能做职责内的事（比如：普通员工只能查询数据，管理员可以修改数据）。
避免误操作：比如禁止普通用户删除数据库的关键表。

2.3.1.3 权限的分类

在ORACLE中权限可以分为两大类：系统权限和对象权限。

系统权限（System Privileges）

定义：控制用户对整个数据库的操作能力，比如创建表、修改用户、备份数据等。
常见权限举例：
- CREATE SESSION：允许用户登录数据库。
- CREATE TABLE：允许用户创建表。
- CREATE USER：允许用户创建新用户。

对象权限（Object Privileges）

定义：控制用户对某个具体对象（如：表、视图、存储过程）的操作权限。
常见权限举例：
- SELECT：允许查询表中的数据。
- INSERT：允许向表中插入数据。
- EXECUTE：允许执行某个存储过程。

2.3.1.4 权限的使用

通过 GRANT 授予权限，REVOKE 回收权限。

使用GRANT授予权限：

-- 授予系统权限（允许用户创建表）
GRANT CREATE TABLE TO 用户名;

-- 授予对象权限（允许用户查询某张表）
GRANT SELECT ON 表名 TO 用户名;

-- 允许用户将自己的权限转授他人（加上 WITH GRANT OPTION）
GRANT SELECT ON 表名 TO 用户名 WITH GRANT OPTION;

示例：

/* 给user1用户授予系统权限：用户登录、创建表 */
GRANT CREATE SESSION,CREATE TABLE TO user1;

/* 给user1用户授予对象权限：查询、新增 */
GRANT INSERT,SELECT ON employess TO user1;
GRANT UPDATE ON employess TO user1;


/* 1. 系统权限转授他人 */
-- 以具有授予系统权限的用户（如SYSTEM或SYS）登录数据库
-- 授予用户user1创建表的系统权限，并允许其转授该权限
GRANT CREATE TABLE TO user1 WITH ADMIN OPTION;
-- user1登录数据库后，可以将CREATE TABLE权限授予user2
-- 假设user1已登录，可执行以下授权命令
GRANT CREATE TABLE TO user2;


/* 2. 对象权限转授他人 */
-- 以表的所有者用户登录数据库
-- 授予用户user1对表employees的SELECT对象权限，并允许其转授该权限
GRANT SELECT ON employees TO user1 WITH GRANT OPTION;
-- user1登录数据库后，可以将SELECT权限授予user2
-- 假设user1已登录，可执行以下授权命令
GRANT SELECT ON employees TO user2;

使用REVOKE回收权限：

-- 回收系统权限
REVOKE CREATE TABLE FROM 用户名;

-- 回收对象权限
REVOKE SELECT ON 表名 FROM 用户名;

示例：

/* 向user1回收系统权限：创建表、修改表 */
REVOKE CREATE TABLE,ALTER TABLE FROM user1; 

/* 向user1回收对象权限：查询 */
REVOKE SELECT ON employees TO user1;

当回收授予时带有WITH ADMIN OPTION或者WITH GRANT OPTION的权限时，那些由被授权用户转授出去的权限不会自动回收。

2.3.2 角色

2.3.2.1 什么是角色

角色是一组权限的集合，类似现实中的”岗位”。比如：”财务角色”可能包含查询工资表、修改报销记录的权限。通过角色，管理员可以批量分配权限，避免逐个用户设置的麻烦。

2.3.2.2 角色的作用

角色的作用：

简化权限管理：将多个权限打包成一个角色，直接分配给用户。
统一权限标准：同一角色的用户权限一致，减少人为错误。
动态生效：修改角色中的权限，所有关联用户自动生效。

2.3.2.3 常用角色

Oracle 预定义了多个常用角色：

CONNECT：基础权限，允许用户连接数据库（包含 CREATE SESSION）。
RESOURCE：允许用户创建表、视图等对象（包含 CREATE TABLE、CREATE SEQUENCE）。
DBA：管理员角色，拥有所有系统权限（谨慎分配！）。

2.3.2.4 角色的使用

创建角色并分配权限：

-- 创建角色
CREATE ROLE 角色名;

-- 给角色分配权限
GRANT SELECT, INSERT ON 表名 TO 角色名;
GRANT CREATE TABLE TO 角色名;

将角色分配给用户：

-- 将角色赋给用户
GRANT 角色名 TO 用户名;

-- 用户可同时拥有多个角色
GRANT CONNECT, RESOURCE TO 用户名;
-- 查看 CONNECT 角色的权限
-- SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'CONNECT';
-- 查看 RESOURCE 角色的权限
-- SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'RESOURCE';

启用/禁用角色：

用户可以通过 SET ROLE 临时启用或禁用角色（需有权限）：

-- 启用角色
SET ROLE 角色名;

-- 禁用所有角色（仅保留直接授予的权限）
SET ROLE NONE;

回收角色：

REVOKE 角色名 FROM 用户名;

2.3.3 授权

给新建用户insur权限：

-- 说明：此处为方便学习操作，直接给用户赋予了DBA角色(管理员) 【开发中禁止使用】
GRANT DBA TO insur; 

说明：在实际开发场景中，需要创建业务相关角色，按需分配权限。

-- 步骤1：创建业务相关角色，按需分配权限。
CREATE ROLE 角色名;  -- 创建角色
GRANT CREATE SESSION, CREATE TABLE,... TO 角色名;      -- 给角色分配系统权限
GRANT SELECT, INSERT, ... ON 表名 TO 角色名;            -- 给角色分配对象权限
  
-- 步骤2： 将自定义角色分配给指定用户
GRANT 角色名 TO 用户名;               -- 将角色赋给用户

2.4 创建表

建表语句：

CREATE TABLE 表名 (  
    字段1 数据类型 [约束] [DEFAULT 默认值],  
    字段2 数据类型 [约束] [DEFAULT 默认值],  
    ...  
    [CONSTRAINT 约束名] 约束类型 (字段或条件)  
);  

2.4.1 约束

2.4.1.1 概述

没有规矩不成方圆，数据也要守规矩！

什么是约束？

约束就像数据库的”交通规则”，规定数据必须满足什么条件才能存到表里。

举例：
- 你的身份证号不能重复 → 这是”唯一约束”。
- 年龄不能填负数 → 这是”检查约束”。
- 订单必须关联一个真实存在的用户 → 这是”外键约束”。
本质：给数据立规矩，防止乱填乱写！

约束的作用：

保证数据正确性。比如：工资不能是负数，性别只能是”男”或”女”。
维护数据关联性。比如：订单里的用户ID必须是用户表中存在的。
避免重复数据。比如：身份证号、手机号不能重复录入。
强制关键字段必填。比如：姓名不能为空。

Oracle中常用约束分为五类：

约束类型	作用	生活例子
主键约束	唯一标识每行数据，不能重复且不能为空	身份证号（唯一且必须填写）
非空约束	强制字段必须填写	姓名不能为空
唯一约束	字段值不能重复（但允许为空）	手机号不能重复，但可以不填
检查约束	限制字段值的范围	年龄必须>=0，性别只能是[男\|女]
外键约束	确保数据关联正确（比如用户必须存在）	借书卡必须对应图书馆的注册用户

命名格式参考：

主键：pk_表名_字段（如 pk_employees_id）
外键：fk_表名_字段（如 fk_orders_user）
检查约束：ck_表名_字段（如 ck_employees_gender）

2.4.1.2 约束的使用

方式1：创建表时直接定义约束

语法：

CREATE TABLE 表名 (  
    字段1 数据类型 [约束],  
    字段2 数据类型 [约束],  
    ...  
    [CONSTRAINT 约束名] 约束类型 (字段)  
);  

示例：创建雇员表（包含各种约束）

/*准备的数据表及数据*/
CREATE TABLE dept(
 dept_id NUMBER PRIMARY KEY,
 dept_name varchar(20)
);
INSERT INTO dept(dept_id,dept_name) VALUES (1,'研发部');

-- 雇员表
CREATE TABLE emp (  
    emp_id NUMBER PRIMARY KEY,     -- 主键约束（雇员ID唯一且非空） 
    phone VARCHAR2(11) UNIQUE,     -- 唯一约束（手机号不能重复，但可以为空）  
    name VARCHAR2(50) NOT NULL,    -- 非空约束（姓名必须填）  
    age NUMBER CHECK(age >= 0),    -- 检查约束（年龄必须≥0）  
    dept_id NUMBER,                -- 外键约束（部门ID必须是部门表中存在的）  
    CONSTRAINT fk_emp_deptId FOREIGN KEY (dept_id) REFERENCES dept(dept_id) 
    --           外键名      外键约束关键字  当前表中的外键字段   主键表(主键列)
);

-- 测试约束：
INSERT INTO emp(emp_id,phone,name,age,dept_id) 
VALUES (1,'13800138000','张三',20,1); -- 正常插入数据

-- 报错：主键值重复（违反主键约束：唯一性）
INSERT INTO emp(emp_id,phone,name,age,dept_id) VALUES 
(1,'13800138001','李四',21,1); 

-- 报错：手机号重复（违反唯一约束）
INSERT INTO emp(emp_id,phone,name,age,dept_id) 
VALUES (2,'13800138000','王五',22,1); 

-- 报错：name数据为NULL（违反非空约束）
INSERT INTO emp(emp_id,phone,age,dept_id) 
VALUES (3,'13800138001',23,1); 

-- 报错：年龄为负数，违反检查约束
INSERT INTO emp(emp_id,phone,name,age,dept_id) 
VALUES (4,'13800138001','赵六',-10,1); 

-- 报错：dept表中没有部门编号为2的数据，违反外键约束
INSERT INTO emp(emp_id,phone,name,age,dept_id) 
VALUES (5,'13800138001','麻七',20,2); 

方式2：修改表时添加约束

如果表已经存在，可以用 ALTER TABLE 添加约束。

示例：给”用户表”添加外键约束。

-- 确保订单中的用户ID是真实存在的  
ALTER TABLE emp  
ADD CONSTRAINT fk_emp_deptId FOREIGN KEY (dept_id) REFERENCES dept(dept_id);  

删除约束

如果不需要某个约束，可以删除它。

ALTER TABLE 表名 DROP CONSTRAINT 约束名;  

示例：删除用户表的外键约束。

ALTER TABLE emp  
DROP CONSTRAINT fk_emp_deptId;  -- 约束名需根据实际情况填写  

2.4.1.3 逻辑外键

在Oracle数仓开发中，推荐的做法通常是不使用物理外键，而是采用逻辑外键的概念。

物理外键（Physical Foreign Key）

物理外键是在数据库表之间直接创建的外键约束，用于强制引用完整性。这种做法常见于事务型数据库或需要严格数据一致性的场景。

优点：
- 数据完整性：物理外键可以确保数据的引用完整性，防止无效数据的插入。
- 数据库自动维护：数据库系统会自动检查和强制执行这些约束。
缺点：
- 性能影响：在大量数据插入或查询时，物理外键可能会引入额外的性能开销，特别是在高并发的环境中。
- 复杂度增加：在数据仓库中，为了保持查询性能，通常避免使用物理外键，这会增加维护数据一致性的复杂度。

逻辑外键（Logical Foreign Key）

逻辑外键指的是在设计数据库模型时，虽然在数据库表中不直接创建外键约束，但通过业务规则或应用程序逻辑来维护数据间的引用关系。

优点：
- 性能优化：在数据仓库中，为了提高查询性能，通常不会在事实表和维度表之间创建物理外键约束。这样可以避免在进行大量数据插入或查询时的外键检查，提高性能。
- 简化模型：逻辑外键可以帮助简化数据模型，尤其是在复杂的维度模型中，通过应用程序代码来确保数据的一致性和完整性。
缺点：
- 数据一致性风险：如果没有严格的应用程序逻辑来维护这些关系，可能会出现数据不一致的问题。

小结：

在数据库中采用逻辑外键意味着在设计数据库模型时，通过应用程序逻辑来维护数据的引用关系，而不是依赖于数据库层面的外键约束。这样可以更好地优化查询性能，同时通过应用程序层面的控制来确保数据的完整性和一致性。当然，这需要开发者在设计应用逻辑时更加小心，确保数据的准确性和一致性。如果确实需要保证数据的引用完整性并且可以接受一定的性能开销，可以考虑在某些关键的业务逻辑中使用触发器或者存储过程来强制实施类似外键的约束。但在数仓环境中，通常优先考虑查询性能和模型的简化。

2.4.2 默认值

在创建表时，可以给字段指定默认值：

CREATE TABLE 表名 (  
    字段1 数据类型 [约束] [DEFAULT 默认值],  
    字段2 数据类型 [约束] [DEFAULT 默认值],  
    ...  
);  

DEFAULT：插入数据时，如果未指定具体字段值，自动填充默认值。

示例：创建带有默认值的表

CREATE TABLE emp (    
    emp_id NUMBER PRIMARY KEY,  -- 主键约束（唯一且非空）
    
    -- 非空约束 + 默认值（未填姓名时显示“未知”）
    emp_name VARCHAR2(50) DEFAULT '未知' NOT NULL,  
    
    -- 检查约束（性别只能是男/女） ，默认值为：男 
    gender VARCHAR2(3) DEFAULT '男' CHECK (gender IN ('男', '女')),  
    
    work_id VARCHAR2(10) UNIQUE,   -- 唯一约束（工号不可重复，允许为空）
    
    hire_date DATE DEFAULT SYSDATE,  -- 默认值（入职时间默认当前时间）  
    
    dept_id NUMBER,           -- 外键级联删除：部门被删除时，自动删除其部门下的员工
    CONSTRAINT fk_emp_deptId  
               FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
               ON DELETE CASCADE
);

-- 测试：有默认值的字段插入数据时不指定具体数据值
INSERT INTO emp(emp_id,work_id,dept_id) VALUES (1,'A1001',1);

2.4.3 数据表

项目相关的数据表：

-- 创建客户表
CREATE TABLE clients (
    client_id NUMBER(10) PRIMARY KEY,        -- 主键约束
    name VARCHAR2(50) NOT NULL,              -- 非空约束
    id_card VARCHAR2(18) UNIQUE NOT NULL     -- 唯一约束 + 非空约束
);

-- 创建产品表
CREATE TABLE products (
    product_id NUMBER(10) PRIMARY KEY,       -- 主键约束
    product_name VARCHAR2(50) NOT NULL,      -- 非空约束
    product_type VARCHAR2(20) NOT NULL       -- 非空约束
);
-- 创建保单表
CREATE TABLE policies (
    policy_no VARCHAR2(20) PRIMARY KEY,          -- 主键约束
    client_id NUMBER(10) REFERENCES clients(client_id),     -- 外键约束
    product_id NUMBER(10),                                  -- 外键约束  
    start_date DATE DEFAULT SYSDATE NOT NULL,    -- 非空约束 + 默认值(当前日期)
    end_date DATE NOT NULL,                      -- 非空约束
    premium_amount NUMBER(12,2) NOT NULL,        -- 非空约束   
    status VARCHAR2(10) CHECK (status IN ('有效', '过期', '终止')),   -- 检查约束
    CONSTRAINT fk_policies_productId  
               FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- 创建缴费记录表
CREATE TABLE premium_payments (
    payment_id NUMBER(10) PRIMARY KEY,            -- 主键约束
    policy_no VARCHAR2(20),                       -- 外键约束
    payment_date DATE DEFAULT SYSDATE NOT NULL,   -- 非空约束 + 默认值 
    amount NUMBER(12,2) NOT NULL,                 -- 非空约束 
    CONSTRAINT fk_policy_no 
               FOREIGN KEY (policy_no) REFERENCES policies(policy_no)
);
-- 创建理赔表
CREATE TABLE claims (
    claim_id NUMBER(10) PRIMARY KEY,            -- 主键约束
    policy_no VARCHAR2(20),                     -- 外键约束
    claim_date DATE DEFAULT SYSDATE NOT NULL,   -- 非空约束 + 默认值
    claim_amount NUMBER(12,2) NOT NULL,         -- 非空约束
    status VARCHAR2(10) DEFAULT '待审核' 
           CHECK (status IN ('待审核', '已赔付', '已拒绝')),  -- 检查约束 + 默认值 
    CONSTRAINT fk_claims_policyNo
               FOREIGN KEY (policy_no) REFERENCES policies(policy_no)
);

2.4.4 注释

2.4.4.1 COMMNET

在 Oracle 数据库里，使用COMMENT语句能够为数据库对象(表、列或者视图)添加、修改和删除注释。这些注释会被存于数据字典中，能帮助开发者和数据库管理员更便捷地理解数据库对象的用途与结构，提升数据库的可读性。

为表添加注释：

-- 使用COMMENT ON TABLE语句给emp表添加注释
COMMENT ON TABLE emp IS '该表存储了公司员工的基本信息';

为列添加注释：

-- 为emp表的emp_id列添加注释
COMMENT ON COLUMN emp.emp_id IS '员工的唯一标识符';

修改注释：

若要修改已有的注释，只需再次使用COMMENT语句，新的注释会覆盖旧的注释

-- 修改emp表的注释
COMMENT ON TABLE emp IS '更新后的表注释，存储公司员工详细信息';

删除注释：

-- 删除emp表的注释
COMMENT ON TABLE emp IS '';    -- 将注释内容设置为空字符串

2.4.4.2 数据表注释

使用COMMENT语句给项目中的数据表结构添加注释内容：

-- 为clients表添加注释说明
COMMENT ON TABLE clients IS '客户表';
-- 为clients表中的列添加注释说明
COMMENT ON COLUMN clients.client_id IS '客户id(主键)';
COMMENT ON COLUMN clients.name IS '客户名字';
COMMENT ON COLUMN clients.id_card IS '客户身份证号';

-- 为products表添加注释说明
COMMENT ON TABLE products IS '产品表';
-- 为products表中的列添加注释说明
COMMENT ON COLUMN products.product_id IS '产品id(主键)';
COMMENT ON COLUMN products.product_name IS '产品名称';
COMMENT ON COLUMN products.product_type IS '产品类型名';

-- 为policies表添加注释说明
COMMENT ON TABLE policies IS '保单表';
-- 为policies表中的列添加注释说明
COMMENT ON COLUMN policies.policy_no IS '保单号(主键)';
COMMENT ON COLUMN policies.client_id IS '关联客户id(外键,关联客户表)';
COMMENT ON COLUMN policies.product_id IS '关联产品id(外键,关联产品表)';
COMMENT ON COLUMN policies.start_date IS '保单生效日期';
COMMENT ON COLUMN policies.end_date IS '保单到期日期';
COMMENT ON COLUMN policies.premium_amount IS '保单金额';
COMMENT ON COLUMN policies.status IS '保单状态[有效|过期|终止]';

-- 为premium_payments表添加注释说明
COMMENT ON TABLE premium_payments IS '缴费记录表';
-- 为premium_payments表中的列添加注释说明
COMMENT ON COLUMN premium_payments.payment_id IS '缴费ID(主键)';
COMMENT ON COLUMN premium_payments.policy_no IS '关联保单号(外键,关联保单表)';
COMMENT ON COLUMN premium_payments.payment_date IS '缴费日期';
COMMENT ON COLUMN premium_payments.amount IS '缴费金额';

-- 为claims表添加注释说明
COMMENT ON TABLE claims IS '理赔表';
-- 为claims表中的列添加注释说明
COMMENT ON COLUMN claims.claim_id IS '理赔id(主键)';
COMMENT ON COLUMN claims.policy_no IS '关联保单号(外键,关联保单表)';
COMMENT ON COLUMN claims.claim_date IS '理赔申请日期';
COMMENT ON COLUMN claims.claim_amount IS '理赔金额';
COMMENT ON COLUMN claims.status IS '保单状态[待审核|已赔付|已拒绝]';

2.4.5 序列

在数据表中通常主键字段，是需要保证非空且唯一的。如果主键字段下的值是由我们自己来维护会比较麻烦(必须保证值的唯一性)。Oracle数据库为解决这个问题，提供了一种解决方案：序列。

2.4.5.1 什么是序列

序列是ORACLE提供的用于产生一系列唯一数字的对象。序列就像是一个自动生成数字的机器，能够按照预先设定的规则，依次产生一系列唯一的数字。这些数字通常是整数，并且会按照特定的顺序递增或者递减。

序列是ORACLE数据库中的一个对象，是独立于数据表存在的(和表没有关系)，可以通过序列设置表中的number类型的字段自动增长，可被多个表或者同一个表的不同列使用。

开发中通常都是一张表绑定一个唯一的序列对象。

序列的作用：

生成主键值。
- 在数据库中，每张表一般都需要一个主键来唯一标识每一行记录。使用序列可以自动生成唯一的主键值，避免了手动输入主键值可能产生的重复或者错误。
保证数据的唯一性。
- 序列生成的数字是唯一的，这能确保在插入数据时，某些关键列的值不会重复。

2.4.5.2 序列的使用

简单序列

创建序列

create sequence 序列名称  

获取序列值

-- 通过序列的伪列来访问序列的值
序列名.NEXTVAL      -- 返回序列的下一个值
序列名.CURRVAL      -- 返回序列的当前值

注意：在刚创建序列后，是无法提取当前值的，只有先提取下一个值后才能提取当前值。

简单序列的应用示例：

-- 创建名为：employee_seq
CREATE SEQUENCE employee_seq; -- 初始值从1开始，每次递增为1

-- 获取序列的下一个值
SELECT employee_seq.NEXTVAL FROM dual;

-- 获取序列的当前值
SELECT employee_seq.CURRVAL FROM dual;

说明：如有特殊要求，开发中简单序列即可满足主键列插入值要求。

2.4.5.3 复杂序列

创建序列的完整语法：

CREATE SEQUENCE sequence_name  -- 创建序列名称
       [INCREMENT BY n]  -- 递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
       [START WITH n]    -- 开始的值,递增默认是minvalue 递减是maxvalue
       [{MAXVALUE n | NOMAXVALUE}]   -- 最大值k
       [{MINVALUE n | NOMINVALUE}]   -- 最小值
       [{CYCLE | NOCYCLE}]     -- 循环或不循环  默认NOCYCLE
       [{CACHE n | NOCACHE}];  -- 分配并存入到内存中 默认CACHE 20 （减少IO，提高效率）

示例：

CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999
CYCLE
CACHE 20;

employee_seq：序列的名称，根据实际需求来命名。
START WITH 1：表示序列从数字 1 开始生成。
INCREMENT BY 1：意味着每次生成的数字比前一个数字大 1。
MINVALUE 1：指定序列的最小值为 1。
MAXVALUE 99999：设定序列的最大值为 99999。
CYCLE：表示当序列达到最大值后，会重新从最小值开始生成。
CACHE 20：表示 Oracle 会预先缓存 20 个序列值，这样可以提高序列的生成效率。

复杂序列的注意事项：

开始值不能小于最小值

-- 创建非循环序列（开始值小于最小值：报错）
CREATE SEQUENCE seq_test1
START WITH 10    -- 初始值为10
INCREMENT BY 10  -- 增长值为10 
MINVALUE 20      -- 最小值为20
MAXVALUE 100     -- 最大值为100

当非循环序列指定了最大值，而获取的序列又达到最大值的限制时，就会报错

-- 创建非循环序列
CREATE SEQUENCE seq_test2
START WITH 50    -- 初始值为50
INCREMENT BY 10  -- 增长值为10 
MINVALUE 50      -- 最小值为50
MAXVALUE 100     -- 最大值为100
   
-- 假设seq_test2序列的当前值为：100，再次提取下一个值时：系统会报异常信息（最大值为100）
SELECT seq_test2.NEXTVAL FROM dual;   -- 报错

创建循环序列时必须指定最大值

-- 创建循环序列（必须指定最大值，否则会报错）
CREATE SEQUENCE seq_test3
START WITH 10    -- 初始值为10
INCREMENT BY 10  -- 增长值为10 
MINVALUE 5       -- 最小值为5
cycle            -- 开启循环  

在循环序列中，第一次循环是从开始值开始，而第二次循环是从最小值开始循环

-- 创建循环序列
CREATE SEQUENCE seq_test4
START WITH 10    -- 初始值为10
INCREMENT BY 10  -- 增长值为10 
MINVALUE 5       -- 最小值为5
MAXVALUE 200     -- 最大值为200
CYCLE            -- 开启循环
                 -- 没有设置CACHE，默认为20     
                    
-- 假设seq_test4序列的当前值为200（最大值），再次提取下一个值时，会从设置的最小值5开始 
SELECT seq_test4.NEXTVAL FROM dual;   -- 结果为：5        

在循环序列中，必须保证一轮循环的值大于其缓存值

一轮循环值 = 最大值 - 最小值 + 增长值
一轮缓存值 = 缓存值 * 增长值

-- 创建循环序列
CREATE SEQUENCE seq_test5
START WITH 10    -- 初始值为10
INCREMENT BY 10  -- 增长值为10 
MINVALUE 5       -- 最小值为5
MAXVALUE 100     -- 最大值为100
CYCLE            -- 开启循环
CACHE 50         -- 缓存为50（每次生成50个值放在缓存中供使用）

循环序列可使用的公式： (cache * increment) < (max – min + increment)
非循环序列：对缓存的值是否满足一轮循环值没有限制

2.4.5.4 修改和删除序列

修改序列：使用ALTER SEQUENCE语句可以修改序列的属性

ALTER SEQUENCE employee_seq
INCREMENT BY 2     -- 序列的增量改为 2
MAXVALUE 100000;   -- 最大值改为 100000

说明：不能更改序列的START WITH参数

删除序列：使用DROP SEQUENCE语句可以删除序列

DROP SEQUENCE employee_seq;

2.4.6 数据样本

-- 创建序列对象（用于客户表中主键列）
CREATE SEQUENCE clients_seq; 
-- 插入客户数据
INSERT INTO clients VALUES (clients_seq.NEXTVAL, '张三', '110101199001011234');
INSERT INTO clients VALUES (clients_seq.NEXTVAL, '李四', '310101198502021235');
INSERT INTO clients VALUES (clients_seq.NEXTVAL, '王芳芳', '11010819951228234X'); 
INSERT INTO clients VALUES (clients_seq.NEXTVAL, '李国庆', '440305197811235634');

-- 插入产品数据
INSERT INTO products VALUES (1001, '终身寿险', '寿险');
INSERT INTO products VALUES (1002, '家庭车险', '车险');
INSERT INTO products VALUES (1003, '家庭医疗险', '医疗险');

-- 插入保单数据
INSERT INTO policies VALUES ('POL2023001', 1, 1001, DATE '2023-01-01', DATE '2033-01-01', 5000.00, '有效');
INSERT INTO policies VALUES ('POL2023002', 2, 1002, DATE '2023-02-01', DATE '2026-02-01', 4000.00, '有效');
INSERT INTO policies VALUES ('POL2023003', 2, 1003, DATE '2020-03-01', DATE '2021-03-01', 3000.00, '过期');
INSERT INTO policies VALUES ('POL2023004', 2, 1001, DATE '2021-11-01', DATE '2031-11-01', 5000.00, '终止');
INSERT INTO policies VALUES ('POL2023005', 4, 1003, DATE '2024-05-01', DATE '2025-05-01', 3000.00, '有效');
INSERT INTO policies VALUES ('POL2023006', 1, 1002, DATE '2023-07-01', DATE '2026-07-01', 4000.00, '有效');

-- 插入缴费记录
INSERT INTO premium_payments VALUES (1, 'POL2023001', DATE '2023-01-05', 5000.00);
INSERT INTO premium_payments VALUES (2, 'POL2023002', DATE '2023-02-05', 4000.00);
INSERT INTO premium_payments VALUES (3, 'POL2023003', DATE '2023-03-01', 3000.00);
INSERT INTO premium_payments VALUES (4, 'POL2023006', DATE '2023-07-03', 4000.00);

-- 插入理赔数据
INSERT INTO claims VALUES (1, 'POL2023001', DATE '2023-06-01', 100000.00, '已赔付');
INSERT INTO claims VALUES (2, 'POL2023003', DATE '2021-02-28', 50000.00, '待审核');

回顾上午知识点:

约束+默认值

create table 表名
(
    主键列 number primary key ,   -- 主键约束 （oracle会默认创建唯一索引）
    字段名 数据类型(长度) [default 默认值]  [约束]
    .......
    CONSTRTANT 约束名 FK(外键字段) RF 主键表(主键列)
)
   
   
-- 示例
create table student
(
    s_id number primary key ,
    s_name varchar2(20) not null,
    s_phone varchar2(11) unique not null, -- 唯一约束+非空约束（默认创建唯一索引）
    s_gender varchar2(3) default '男' check(s_gender in('男','女'))，
    study_date date default sysdate,
    class_id number REFERENCES classes(class_id)  -- 外键约束
) 
   
   
-- 创建表以后添加约束
alter table student
add CONSTRAINT fk_policies_productId  
               FOREIGN KEY (product_id) REFERENCES products(product_id)
                                 
                  

注释

-- 给表添加说明
comment on table 表名 is '文本说明'
   
-- 给表中的字段添加说明
comment on column 表名.字段名 is '文本说明'
   

序列

-- 创建序列
create sequence 序列名
   
-- 获取下一个值
序列名.nextval
-- 获取当前值
序列号.currval
   
insert into 表名(主键列,  列1,列2,.....)
value (序列号.nextval, .... ,...........)

2.5 业务实现

2.5.1 EXISTS

案例：有购买过任何保险产品的客户

业务背景：识别活跃客户（有购买保险），用于推销新产品。

-- 使用NOT EXISTS筛选无保单记录的客户
SELECT c.client_id, c.name
FROM clients c
WHERE c.client_id IN (
    SELECT client_id FROM policies
    );

以上业务需求还可以使用EXISTS方式来解决。

在 Oracle 数据库里，EXISTS 和 NOT EXISTS 是两个非常实用的操作符，它们主要用于子查询当中，目的是对主查询和子查询之间的数据存在性进行判断。

EXISTS 操作符使用方式：

SELECT * FROM emp e
WHERE EXISTS (
    SELECT 1
    FROM dept d
    WHERE d.dept_id = e.dept_id   -- 索引列
);

基本作用：

EXISTS 操作符的作用是检验子查询是否会返回至少一行数据。如果子查询能返回一行或者多行数据，那么EXISTS就会返回TRUE，主查询会把对应的数据行给查出来；要是子查询没有返回任何数据，EXISTS 就返回 FALSE，主查询不会返回这一行。

使用EXISTS实现：

-- 在client_id创建索引
SELECT c.client_id, c.name
FROM clients c
WHERE EXISTS (
    SELECT 1
    FROM policies p
    WHERE p.client_id = c.client_id
  );

子查询里通常用 SELECT 1，因为 EXISTS 只关心子查询是否有结果返回，而不关心返回的具体内容是什么。

2.5.2 NOT EXISTS

NOT EXISTS 操作符

SELECT * FROM emp e
WHERE NOT EXISTS (
    SELECT 1
    FROM dept d
    WHERE d.dept_id = e.dept_id
);

基本作用：

NOT EXISTS 操作符和 EXISTS 刚好相反。它会检验子查询是否没有返回任何数据。如果子查询没有返回任何数据，NOT EXISTS 就返回 TRUE，主查询会把对应的数据行查出来；要是子查询返回了一行或者多行数据，NOT EXISTS 就返回 FALSE，主查询不会返回这一行。

使用技巧：

和 EXISTS 一样，子查询里通常用 SELECT 1。
NOT EXISTS 能帮助我们找出那些在某个表中不存在对应记录的数据，在数据清理和数据校验的时候非常有用。

案例：从未购买任何保险产品的客户

业务背景：识别潜在客户（注册但未购买保险），用于激活营销。

-- 使用NOT EXISTS筛选无保单记录的客户
SELECT c.client_id, c.name
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM policies p
    WHERE p.client_id = c.client_id
  );

说明：
NOT EXISTS直接排除所有有保单记录的客户。

2.5.3 业务练习

案例1：已过期保单但仍有未处理理赔的客户

业务背景：发现过期保单关联的未处理理赔，避免因保单失效导致纠纷。

-- 使用EXISTS关联过期保单和待审核理赔
SELECT c.client_id, c.name, cl.claim_id, cl.claim_amount
FROM clients c
JOIN policies p ON c.client_id = p.client_id
JOIN claims cl ON p.policy_no = cl.policy_no
WHERE p.status = '过期'
  AND cl.status = '待审核'
  AND EXISTS (
    SELECT 1
    FROM claims
    WHERE policy_no = p.policy_no
      AND status = '待审核'
  );

说明：
- 主查询筛选过期保单和待审核理赔。
- EXISTS进一步验证存在未处理的理赔。

案例2：存在有效保单但从未缴费的客户

业务背景：识别已签订保单但从未缴纳保费的客户，用于催缴或风险评估。

-- 使用NOT EXISTS筛选未缴费的保单
SELECT c.client_id, c.name, p.policy_no
FROM clients c
JOIN policies p ON c.client_id = p.client_id
WHERE p.status = '有效'
  AND NOT EXISTS (
    SELECT 1
    FROM premium_payments pp
    WHERE pp.policy_no = p.policy_no
  );

说明：
主查询关联客户和有效保单。
- NOT EXISTS子查询排除有缴费记录的保单。

小结： ==exists和not exists是应用于相关子查询中==

EXISTS 用于判断子查询是否有结果返回，有结果就返回 TRUE，主查询返回对应数据行。
NOT EXISTS 用于判断子查询是否没有结果返回，没有结果就返回 TRUE，主查询返回对应数据行。
这两个操作符在子查询中使用，能提高查询效率，特别是在处理大数据量的时候。

2.6 视图

2.6.1 什么是视图

在 Oracle 数据库里，视图（View）可以理解成一个 “虚拟的表”。它本身不存储真实的数据，而是基于一张或多张真实表（基表）的查询结果动态生成的 “窗口”。视图的结构和数据是对数据表进行查询的结果。

结论：视图其实就是一个封装了SQL查询语句的对象。

视图的作用：

简化复杂查询如果经常需要写多个表连接的复杂 SQL（比如员工表和部门表连接查姓名、部门名称），可以把这个查询做成视图。之后每次只用查视图，不用重复写连接语句，就像把常用公式存起来直接用一样。
保护数据安全可以通过视图隐藏基表中的敏感列（如薪资、身份证号）或敏感行（如只让销售部门看到自己的数据）。用户只能通过视图访问部分数据，无法直接接触基表的全部信息，相当于给数据加了一层 “过滤罩”。
复用常用查询把常用的查询（比如 “本月销售额前 10 的产品”）做成视图，后续直接查视图即可，避免重复写 SQL，提高开发效率。

2.6.2 视图语法

创建修改视图语法：

CREATE [OR REPLACE] [FORCE] VIEW view_name    
AS subquery    
[WITH CHECK OPTION ]  -- 创建带检查约束的视图
[WITH READ ONLY]      -- 创建只读视图

选项解释：

OR REPLACE ：若所创建的视图已经存在，ORACLE自动重建该视图；

FORCE ：不管基表是否存在ORACLE都会自动创建该视图；

subquery ：一条完整的SELECT语句，可以在该语句中定义别名；

WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；

WITH READ ONLY ：该视图上不能进行任何DML操作。

删除视图语法：

DROP VIEW view_name 

2.6.3 视图的使用

2.6.3.1 简单视图

视图中的语句只是单表查询，并且没有聚合函数，称之为简单视图。

示例：创建视图

CREATE  VIEW view_clients AS 
SELECT * FROM clients;

查询视图

SELECT * FROM view_clients;

对于简单视图，不仅可以用查询，还可以增删改记录。

基于视图实现数据更新：

UPDATE view_clients SET name='测试用户' WHERE client_id=3;

视图是一个虚拟的表，它的数据来自于基表。如果更改了视图的数据，表的数据也自然会变化，当更改了表中的数据，视图也自然会变化。

一个视图所存储的并不是数据，而是一条SQL语句。

2.6.3.2 带检查约束的视图

示例：根据保单表(policies)创建视图view_policies，内容为状态='有效'的记录。

CREATE OR REPLACE VIEW view_policies
AS 
SELECT * FROM policies WHERE status='有效' WITH CHECK OPTION;

测试1：基于视图执行更新语句

UPDATE view_policies SET status='过期' WHERE policy_no='POL2023006';

测试2：基于视图执行添加语句

INSERT INTO view_policies VALUES ('POL2023010', 1, 1003, DATE '2024-01-01', DATE '2025-01-01', 2000.00, '过期');

结论：创建带WITH CHECK OPTION的视图，在INSERT和UPDATE时会去检查视图中子查询的条件。

2.6.3.3 只读视图

如果创建视图时，不希望用户能对视图进行修改，就需要在创建视图时指定WITH READ ONLY选项，这样创建的视图就是一个只读视图。

CREATE OR REPLACE VIEW view_policies
AS 
SELECT * FROM policies WHERE status='有效' WITH READ ONLY;

测试1：基于视图执行添加语句

INSERT INTO view_policies 
VALUES ('POL2023010', 1, 1003, DATE '2024-01-01', DATE '2025-01-01', 2000.00, '有效');

测试2：基于视图执行删除语句

DELETE FROM view_policies WHERE policy_no='POL2023006';

只读视图，只允许进行DQL操作，不允许进行DML操作

2.6.3.4 复杂视图

所谓复杂视图，就是视图的SQL语句中，有聚合函数或多表关联查询。

示例：创建复杂视图（查询客户id，客户名称，保单号，产品名称、保单金额、保单状态）

CREATE OR REPLACE VIEW view_complex
AS 
SELECT c.client_id,c.name,
       pt.product_name,
       po.policy_no,po.premium_amount,po.status
FROM clients c 
     JOIN policies po ON c.client_id = po.client_id
     JOIN products pt ON pt.product_id = po.product_id;

测试1：基于视图修改保单相关数据

UPDATE view_complex SET PREMIUM_AMOUNT=6666 WHERE POLICY_NO='POL2023006';

测试2：基于视图修改客户相关数据

UPDATE view_complex SET name='测试用户' WHERE client_id=3;

执行结果：

意思是说我们所需改的列不属于键保留表(键值保存表)的列。

什么叫键保留表呢？

键保留表是视图修改限制的一个基本概念。该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个所对应的视图中也是键值，那么就称这个表为键保留表。
在上面的示例中，视图中存在三个表，客户表clients、保单表policies、产品表products, 其中保单表policies表就是键保留表，因为保单表policies的主键也是作为视图的主键。键保留表中的字段是可以更新的，而非键保留表是不能更新的。

记住：只要视图中使用了分组、聚合函数，就无法执行update

2.7 事务

2.7.1 什么是事务

在实际的业务开发中，有些业务操作是需要多次访问数据库，也就是说一个业务要发送多条SQL语句给数据库执行。数据库需要将多次接收到的SQL操作作为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句执行失败，就会造成所有的SQL语句全部执行失败。

简而言之，事务指的是逻辑上的一组SQL操作,组成这组操作的各个单元要么全都成功,要么全都失败。

事务作用：保证在一个事务中多次操作表中数据时，要么全都成功,要么全都失败。

2.7.2 事务的应用场景

转账业务：比如A给B转账，那么A出账和B入账两次SQL操作要作为一个整体执行，要么全部都成功，要么全部都失败。

2.7.3 事务的特性

事务的四大特性（ACID）：

原子性（Atomicity）： 要么全做，要么全不做。就像前面说的转账，扣钱和加钱是一个 “原子” 操作，不能拆分开。如果中间出错，比如余额不足，系统会把扣钱的操作也撤销，回到没转之前的状态。
一致性（Consistency）： 数据从一个合法状态变到另一个合法状态。比如转账前后，你的账户余额减少的钱 + 对方增加的钱 = 转账金额，不会多也不会少。事务必须保证逻辑上的正确性，比如不能让余额变成负数（这需要业务逻辑配合，比如余额检查）。
隔离性（Isolation）： 多个事务同时运行时，互不干扰。比如你和朋友同时给同一个账户转账，你的事务没提交时，朋友看不到你中间的 “半成品” 数据，避免看到 “脏数据”（比如你扣了钱但还没给对方加钱时，朋友看到你的余额少了，但对方余额还没多，这就是脏数据）。

Oracle 默认的隔离级别是 “读提交”（Read Committed）
持久性（Durability）： 事务提交后，数据永久保存。哪怕数据库突然断电、服务器爆炸，只要提交了，数据就会写入磁盘，不会丢。

2.7.4 事务的并发问题（扩展知识）

（PPT演示）

Oracle 支持的隔离级别（2 种）：

读提交（Read Committed，默认级别）
- 事务在读取数据时，只能看到其他事务 已经提交的数据（不会读到未提交的 “脏数据”）。
- 每次读取数据时，获取的是 最新的已提交版本。例如，若事务 A 在更新数据但未提交时，事务 B 多次读取该数据，每次会看到不同的已提交版本（如果其他事务在此期间提交了修改）。
- 适用场景
  - 大多数日常业务场景（如电商、日志系统），默认级别已能满足一致性和性能平衡。
可序列化（Serializable）
- 确保事务的执行效果与 “串行执行”（一个接一个执行）完全一致，避免 不可重复读 和幻读问题。
- 事务在读取数据时，会固定看到事务开始时的版本，后续其他事务提交的修改对当前事务不可见（直到当前事务提交或回滚）。
- 适用场景
  - 对数据一致性要求极高的场景（如金融交易、库存扣减），需保证多次读取结果一致，且避免幻读（例如：事务 A 查询符合条件的 10 条记录，事务 B 插入第 11 条并提交，事务 A 再次查询时仍只看到 10 条）。

Oracle 不支持的隔离级别：

读未提交（Read Uncommitted） Oracle 不支持此级别，因为该级别允许读取未提交的脏数据，可能导致数据不一致（如转账时看到对方账户余额已增加，但实际转账事务未提交，最终回滚）。
可重复读（标准 SQL 中的级别） Oracle 没有单独的 “可重复读” 隔离级别，而是通过 Serializable 级别 间接实现类似效果（两者的区别在于：标准可重复读仅避免不可重复读，而 Serializable 还避免幻读）。

Oracle如何设置隔离级别？

通过 SET TRANSACTION ISOLATION LEVEL 语句设置，需在事务开始前（第一条 DML 语句执行前）执行：

-- 设置为读提交（默认，可省略）  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  

-- 设置为可序列化  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  

隔离级别对比与选择

隔离级别	脏读	不可重复读	幻读	并发性能	一致性强度
读提交（默认）	否	是（可能）	是（可能）	高	中等
可序列化	否	否	否	中（可能版本冲突回滚）	最高

2.7.5 事务的使用

事务的 “生命周期”

开始事务：不用手动写命令！Oracle 中，执行第一条 DML 语句（INSERT/UPDATE/DELETE）时，事务自动开始。
提交事务（COMMIT）：确认所有操作都没问题，用 COMMIT; 把操作 “钉死”，数据永久生效，别人也能看到你的修改。
回滚事务（ROLLBACK）：如果中间出错（比如余额不足），用 ROLLBACK; 把所有操作撤销，回到事务开始前的状态，就像啥都没发生过。

事务操作示例：银行转账

准备工作：创建账户表并初始化数据

-- 1. 创建账户表（账户ID、姓名、余额）
CREATE TABLE bank_account (
    account_id   NUMBER(10) PRIMARY KEY,  -- 账户ID（主键）
    account_name VARCHAR2(50) NOT NULL,   -- 账户名
    balance      NUMBER(10, 2) NOT NULL   -- 余额（精确到分）
);
   
-- 2. 插入测试数据（张三余额10000元，李四余额5000元）
INSERT INTO bank_account (account_id, account_name, balance) 
VALUES (1001, '张三', 10000.00);
   
INSERT INTO bank_account (account_id, account_name, balance) 
VALUES (1002, '李四', 5000.00);
   
-- 3. 提交初始数据（事务自动提交，因INSERT是DML，需手动提交）
COMMIT;

事务核心逻辑：转账操作

在 Oracle 中，事务自动开始于第一条 DML 语句（如UPDATE），结束于COMMIT（提交）或ROLLBACK（回滚）。

场景 1：正常转账（成功提交）

目标：张三（1001）给李四（1002）转账 3000 元。

操作步骤：

-- 1. 执行第一条UPDATE：扣减张三的余额（事务自动开始）
UPDATE bank_account 
SET balance = balance - 3000 
WHERE account_id = 1001;  -- 张三余额变为7000元（10000-3000）
   
-- 2. 执行第二条UPDATE：增加李四的余额
UPDATE bank_account 
SET balance = balance + 3000 
WHERE account_id = 1002;  -- 李四余额变为8000元（5000+3000）
   
-- 3. 提交事务（所有操作生效）
COMMIT;

验证结果：

-- 查询两个账户的余额
SELECT account_id, account_name, balance 
FROM bank_account 
WHERE account_id IN (1001, 1002);

场景 2：转出账户余额不足（回滚）

目标：张三尝试给李四转账 15000 元（张三当前余额 7000 元：余额不足）。

操作步骤：

-- 1. 执行第一条UPDATE：扣减张三的余额（事务自动开始）
UPDATE bank_account 
SET balance = balance - 15000 
WHERE account_id = 1001;  -- 张三余额变为-8000元（7000-15000）
   
-- 2. 发现余额不足，手动回滚事务（撤销所有操作）
ROLLBACK;

验证结果：

-- 查询两个账户的余额（恢复为转账前状态）
SELECT account_id, account_name, balance 
FROM bank_account 
WHERE account_id IN (1001, 1002);

Oracle数据库 视图, 事务

1. 项目介绍

1.1 需求文档

1.2 数据库开发流程

1.3 逻辑设计: 三范式

1.3.1 三范式

1.3.2 反三范式

1.4 数据字典

2. 项目开发

2.1 创建表空间

2.2 创建用户

2.3 用户授权

2.3.1 权限

2.3.1.1 什么是权限

2.3.1.2 权限的意义

2.3.1.3 权限的分类

2.3.1.4 权限的使用

2.3.2 角色

2.3.2.1 什么是角色

2.3.2.2 角色的作用

2.3.2.3 常用角色

2.3.2.4 角色的使用

2.3.3 授权

2.4 创建表

2.4.1 约束

2.4.1.1 概述

2.4.1.2 约束的使用

2.4.1.3 逻辑外键

2.4.2 默认值

2.4.3 数据表

2.4.4 注释

2.4.4.1 COMMNET

2.4.4.2 数据表注释

2.4.5 序列

2.4.5.1 什么是序列

2.4.5.2 序列的使用

2.4.5.3 复杂序列

2.4.5.4 修改和删除序列

2.4.6 数据样本

2.5 业务实现

2.5.1 EXISTS

2.5.2 NOT EXISTS

2.5.3 业务练习

2.6 视图

2.6.1 什么是视图

2.6.2 视图语法

2.6.3 视图的使用

2.6.3.1 简单视图

2.6.3.2 带检查约束的视图

2.6.3.3 只读视图

2.6.3.4 复杂视图

2.7 事务

2.7.1 什么是事务

2.7.2 事务的应用场景

2.7.3 事务的特性

2.7.4 事务的并发问题（扩展知识）

2.7.5 事务的使用

CATALOG

FEATURED TAGS

FRIENDS

Oracle数据库视图, 事务