Oracle 基础 SQL

Oracle SQL 和 MySQL 有不少差异，比如字符串类型、序列、自增、分页、日期函数、伪列、dual 表等。

零基础学习 Oracle SQL 时，先不要急着背函数。要先理解：Oracle 常用于核心业务系统，SQL 写法不仅要“能查出来”，还要考虑索引、事务、一致性读、执行计划和版本差异。

学习目标

学完本页要能做到：

1. 能创建用户、资产、订单类表。
2. 能解释 number、varchar2、date、timestamp 的选择。
3. 知道老版本为什么常用 sequence，新版本 identity 怎么用。
4. 能写插入、更新、分页、日期范围查询。
5. 知道 rownum 和 order by 的顺序坑。
6. 知道为什么不要对索引列随便包函数。
7. 能写一个带事务和错误处理的 PL/SQL Demo。

Oracle SQL 执行和业务的关系

flowchart TD
    A["业务请求"] --> B["应用发送 SQL"]
    B --> C["Oracle 解析和优化"]
    C --> D["按执行计划访问块"]
    D --> E{"是否修改数据"}
    E -- "否" --> F["基于 SCN 和 Undo 做一致性读"]
    E -- "是" --> G["生成 Undo 和 Redo"]
    G --> H["commit 刷 Redo"]
    F --> I["返回结果"]
    H --> I

这说明两个关键点：

1. 查询不是简单读当前行，Oracle 会基于 SCN 做一致性读。
2. 更新不是只改表数据，还会产生 Undo 和 Redo。

常用类型

类型	场景	原理和注意点
number	数字、主键、金额	可指定精度；金额建议 number(18,2)
varchar2(n)	字符串	Oracle 常用可变字符串类型
char(n)	固定长度编码	不适合大多数业务字段，容易补空格
date	日期时间	包含年月日时分秒，不只是日期
timestamp	更高精度时间	适合需要毫秒/微秒级记录
clob	大文本	不要放在高频列表查询里
blob	二进制大对象	图片、文件通常更建议对象存储，库里存引用

date 不是只有日期

Oracle 的 date 包含时间部分，精度到秒。很多人查询某一天数据时写：

where created_at = date '2026-07-06'

这只能匹配 2026-07-06 00:00:00，不能匹配当天其他时间。正确方式是范围查询。

建表 Demo

create table asset (
  id number primary key,
  asset_no varchar2(64) not null,
  asset_name varchar2(128) not null,
  hospital_id number not null,
  dept_id number not null,
  status number(2) default 1 not null,
  original_amount number(18,2) default 0 not null,
  created_at date default sysdate not null,
  updated_at date default sysdate not null,
  constraint uk_asset_no unique (asset_no)
);

create sequence seq_asset start with 1 increment by 1 cache 100;

create index idx_asset_hospital_status_time
on asset(hospital_id, status, created_at);

插入：

insert into asset(id, asset_no, asset_name, hospital_id, dept_id, original_amount)
values (seq_asset.nextval, 'A-20260706-001', 'CT设备', 1001, 2001, 3600000);

为什么这样设计：

设计	为什么	不这样会怎样
asset_no 唯一约束	防止重复资产编号	并发导入时应用判断可能失效
sequence cache	降低频繁取序列的开销	无 cache 高并发取号更慢
状态字段	支持生命周期流转	无法判断审批、启停、报废状态
时间字段	支持查询、审计、归档	排查问题缺少时间线
联合索引	支持医院资产列表	单列索引可能过滤不充分

Sequence 原理和注意点

Sequence 是 Oracle 老系统非常常见的取号方式。

select seq_asset.nextval from dual;
select seq_asset.currval from dual;

注意：

1. nextval 每调用一次都会消耗一个值。
2. sequence 不保证业务连续无间断。
3. 事务回滚后，已经取走的 sequence 值不会回退。
4. cache 可以提高性能，但实例异常时可能丢失缓存号段。
5. 业务订单号、资产编号通常不要直接用 sequence 裸值对外展示。

sequence 跳号不是 Bug。它保证的是生成唯一递增值，不保证连续无缺口。

Oracle 12c 后的 Identity

create table asset (
  id number generated by default as identity primary key,
  asset_no varchar2(64) not null,
  owner_id number not null,
  created_at date default sysdate not null
);

老系统里序列更常见，新系统可以使用 identity。

方案	优点	边界
Sequence	老版本通用、灵活、可多表复用	要手动写 nextval 或触发器
Identity	建表语义简单	12c 后支持，迁移老系统要注意兼容

dual 表

select sysdate from dual;
select seq_asset.nextval from dual;

dual 是 Oracle 常用的单行虚拟表。

可以把 dual 理解为“我想执行一个表达式，但不需要真正查询业务表”时使用的单行表。

分页

Oracle 12c 后：

select id, asset_no, asset_name
from asset
where status = 1
order by created_at desc
offset 0 rows fetch next 20 rows only;

老版本常见 rownum 写法，但要注意排序和 rownum 的执行顺序。

老版本 rownum 分页正确写法

错误思路：

select *
from asset
where rownum <= 20
order by created_at desc;

这里 rownum <= 20 可能先取前 20 行，再排序，不是“按时间排序后的前 20 行”。

正确写法是先排序，再在外层取 rownum：

select *
from (
  select a.*, rownum rn
  from (
    select id, asset_no, asset_name, created_at
    from asset
    where hospital_id = 1001
      and status = 1
    order by created_at desc
  ) a
  where rownum <= 20
)
where rn > 0;

深分页仍然会慢，因为数据库仍要处理前面大量行。核心列表更适合基于时间或 ID 的游标分页。

日期查询

推荐范围查询：

select *
from app_user
where created_at >= date '2026-07-01'
  and created_at < date '2026-08-01';

不推荐：

where to_char(created_at, 'yyyy-mm-dd') = '2026-07-01'

对列使用函数可能导致普通索引不能直接使用，除非有函数索引。

为什么函数包列会影响索引

索引通常按原始列值排序，例如 created_at。当你写：

where to_char(created_at, 'yyyy-mm-dd') = '2026-07-01'

数据库需要先对每行 created_at 做函数计算，再比较字符串。普通 created_at 索引无法直接按原始有序值定位范围。

更好的范围条件：

where created_at >= to_date('2026-07-01', 'yyyy-mm-dd')
  and created_at <  to_date('2026-07-02', 'yyyy-mm-dd')

Join 和聚合

create table department (
  id number primary key,
  hospital_id number not null,
  dept_name varchar2(128) not null
);

select a.asset_no, a.asset_name, d.dept_name
from asset a
join department d on d.id = a.dept_id
where a.hospital_id = 1001
  and a.status = 1
order by a.created_at desc;

统计：

select dept_id, status, count(*) as asset_count
from asset
where hospital_id = 1001
group by dept_id, status;

Join 和统计要关注执行计划。大表 Join 没索引、过滤条件选择性差、统计信息不准，都可能导致全表扫描、大排序或 Hash Join 消耗大量 PGA/临时表空间。

Update：带状态条件

update asset
set status = 2,
    updated_at = sysdate
where asset_no = 'A-20260706-001'
  and status = 1;

更新后检查影响行数。如果影响 0 行，说明资产不存在或状态已经变化。商业系统不能无脑认为成功。

为什么要带旧状态：

1. 防止并发重复审核。
2. 防止旧请求覆盖新状态。
3. 用影响行数表达状态流转是否成功。

PL/SQL 事务和错误处理 Demo

declare
  v_asset_id number;
begin
  v_asset_id := seq_asset.nextval;

  insert into asset(id, asset_no, asset_name, hospital_id, dept_id, original_amount)
  values (v_asset_id, 'A-20260706-002', '超声设备', 1001, 2002, 800000);

  insert into asset_audit(asset_id, action_name, created_at)
  values (v_asset_id, 'CREATE', sysdate);

  commit;
exception
  when others then
    rollback;
    raise;
end;
/

这个 Demo 体现了：

1. 多条写入要么一起成功，要么一起失败。
2. 异常时 rollback，避免半成功数据。
3. raise 继续抛出异常，不能吞掉错误。

商业场景：资产列表查询

select id, asset_no, asset_name, dept_id, created_at
from asset
where hospital_id = :hospitalId
  and status = :status
  and created_at >= :beginTime
  and created_at < :endTime
order by created_at desc
fetch first 20 rows only;

这个 SQL 背后要考虑：

1. hospital_id, status, created_at 是否有联合索引。
2. 时间范围是否过大。
3. 返回字段是否会大量回表。
4. 是否需要分页或导出。
5. 统计信息是否准确。

常见坑和排查

问题	表现	原因	处理
sequence 跳号	主键不连续	回滚、cache、失败插入	不把连续性作为业务要求
rownum 分页结果不对	排序后数据不是预期	rownum 和 order by 顺序不清	使用嵌套查询或 12c 分页
日期等值查不到	当天数据缺失	date 包含时分秒	用范围查询
函数包索引列	有索引仍扫描	普通索引无法直接利用	改范围条件或函数索引
金额类型随意	精度混乱	number 精度未设计	使用 number(18,2)
catch 后不 raise	应用误判成功	异常被吞	rollback 后继续抛出

面试标准回答

Oracle 基础 SQL 要注意 varchar2、number、date、timestamp、sequence、dual、rownum/fetch 分页等特点。老版本常用 sequence 生成主键，12c 后可以使用 identity。sequence 保证取号唯一递增，但不保证连续，回滚和 cache 都可能造成跳号。Oracle 的 date 包含时分秒，查询某一天数据应使用范围条件，不要对列使用 to_char 这类函数，否则普通索引可能无法直接使用，除非建立函数索引。商业项目写 SQL 还要关注唯一约束、状态条件更新、事务回滚、执行计划和统计信息。