�� CSDN首发标签：#Oracle #SQL优化 #MERGE #WITH子句 #EXISTS #CASEWHEN #数据库运维 #HIS系统 #存储过程 #数据同步
�� 博文简介：超实用Oracle生产级SQL干货！一次性讲透开发/运维四大神器：CASE WHEN、EXISTS、WITH、MERGE。搭配医院HIS真实业务场景、完整可运行代码、踩坑总结、存储过程封装、自动定时同步，零基础也能直接上手，面试+生产双用！
��️ 文章分类：Oracle实战 / 数据库运维 / SQL性能优化 / 工作实战笔记
简介：精炼汇总 Oracle 运维与开发四大高频核心语法，全部基于医院HIS真实业务场景，涵盖语法讲解、实战踩坑、性能优化、存储过程生产落地。代码极简可直接复用，适合生产开发、面试复习、CSDN收藏。
适用场景：患者数据查询、字段判空、报表统计、复杂SQL拆解、批量增量同步、定时数据归档
兼容环境：Oracle 11g / 12c / 19c 全版本通用
一、CASE WHEN｜行级条件判断
1. 核心作用
逐行执行条件判断，实现状态翻译、空值补全、字段映射，相比 DECODE 支持多条件、范围判断，灵活性更强，是数据清洗、报表输出必备语法。
2. 标准语法
sql
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
ELSE 默认结果
END AS 字段别名 |
3. HIS实战：患者性别翻译 + 空值标记
sql
SELECT
d.brid,
d.brxm,
CASE
WHEN d.brxb IS NULL THEN '性别未录入'
WHEN d.brxb = '1' THEN '男'
WHEN d.brxb = '2' THEN '女'
ELSE '未知'
END AS brxb_text
FROM ms_brda d
WHERE d.jdsj > TRUNC(SYSDATE) - 7; |
4. 避坑要点
- 仅用于前端展示、字段翻译，复杂业务逻辑建议封装存储过程。
二、EXISTS｜高效存在性判断（优化神器）
1. 核心原理
EXISTS 属于半连接查询，匹配到第一条符合条件的数据立即终止扫描，性能碾压 IN、COUNT(*)、LEFT JOIN 判空，是大数据量判存最优方案。
2. 经典踩坑：全局判断 VS 行级判断
这是 90% 开发者都会写错的点，直接决定业务逻辑是否正确。
❌ 错误写法：全局判断（所有行结果一致，无业务意义）
sql
SELECT
d.brid,
d.brxm,
CASE
WHEN EXISTS(SELECT 1 FROM ms_brda c WHERE c.brxb IS NULL)
THEN '有空性别'
ELSE '女'
END AS xb
FROM ms_brda d; |
问题：子查询未关联外层表，仅判断「整张表是否存在空性别」，所有行结果完全一致。
✅ 正确写法：行级关联（核心精髓）
sql
SELECT
d.brid,
d.brxm,
CASE
WHEN EXISTS(
SELECT 1
FROM ms_brda c
WHERE c.brid = d.brid -- 关联外层主键，实现单行独立判断
AND c.brxb IS NULL
AND ROWNUM = 1 -- 性能优化：匹配即停止扫描
) THEN '有空性别'
ELSE TO_CHAR(d.brxb)
END AS xb
FROM ms_brda d
WHERE d.mzhm = '202212135547' OR d.jdsj > TRUNC(SYSDATE) - 12; |
3. 高频实战：NOT EXISTS 反向匹配
查询近30天无缴费记录的患者，替代低效左连接判空。
sql
SELECT d.brid, d.brxm
FROM ms_brda d
WHERE NOT EXISTS(
SELECT 1
FROM pay_record pr
WHERE pr.patient_id = d.brid
AND pr.pay_date >= TRUNC(SYSDATE) - 30
); |
4. 核心总结
三、WITH CTE｜结构化拆解复杂SQL
1. 核心优势
WITH 可定义多个临时结果集，彻底解决多层子查询嵌套混乱问题，代码层级清晰、可读性拉满，可直接作为 MERGE 数据源，无需创建物理临时表。
2. 实战：多CTE链式统计处方数据
sql
WITH
-- 近7天处方数据
recent_pres AS (
SELECT pres_id, doctor_id, patient_id, pres_date, status
FROM prescription
WHERE pres_date >= TRUNC(SYSDATE) - 7
),
-- 筛选未结算处方
unpaid_pres AS (
SELECT doctor_id, patient_id
FROM recent_pres
WHERE status = '未结算'
)
-- 统计医生处方总量、未结算数量
SELECT
d.doctor_id,
d.doctor_name,
COUNT(r.pres_id) AS total_pres_count,
COUNT(u.patient_id) AS unpaid_pres_count
FROM doctor d
LEFT JOIN recent_pres r ON d.doctor_id = r.doctor_id
LEFT JOIN unpaid_pres u ON d.doctor_id = u.doctor_id
GROUP BY d.doctor_id, d.doctor_name; |
3. 关键特性
- 生产高频搭配 MERGE，实现无物理临时表数据同步。
四、MERGE INTO｜增改一体数据同步神器
1. 核心价值
单条SQL完成匹配更新、不匹配新增，彻底抛弃「先查询判断、再UPDATE/INSERT」的繁琐逻辑，是增量同步、日统计、数据修复的核心语法。
2. 标准语法
sql
MERGE INTO 目标表 t
USING 数据源 s
ON (唯一匹配条件)
WHEN MATCHED THEN UPDATE SET 字段=值
WHEN NOT MATCHED THEN INSERT(字段列表) VALUES(对应值); |
3. 基础实战：患者数据增量同步
sql
MERGE INTO ms_brda d
USING temp_patient t
ON (d.brid = t.brid)
WHEN MATCHED THEN
UPDATE SET
d.brxm = t.brxm,
d.brxb = t.brxb,
d.mzhm = t.mzhm,
d.last_update = SYSDATE
WHEN NOT MATCHED THEN
INSERT (brid, brxm, brxb, mzhm, jdsj, last_update)
VALUES (t.brid, t.brxm, t.brxb, t.mzhm, t.jdsj, SYSDATE); |
4. 高阶王炸：WITH + MERGE 无临时表同步
生产最优写法，无需建表，直接统计数据并同步至统计表。
sql
MERGE INTO prescription_stat s
USING (
WITH pres_data AS (
SELECT doctor_id, pres_id
FROM prescription
WHERE pres_date >= TRUNC(SYSDATE) - 1
)
SELECT doctor_id, COUNT(pres_id) AS pres_num
FROM pres_data
GROUP BY doctor_id
) t
ON (s.doctor_id = t.doctor_id AND s.stat_date = TRUNC(SYSDATE))
WHEN MATCHED THEN
UPDATE SET s.pres_num = t.pres_num, s.update_time = SYSDATE
WHEN NOT MATCHED THEN
INSERT (doctor_id, pres_num, stat_date, update_time)
VALUES (t.doctor_id, t.pres_num, TRUNC(SYSDATE), SYSDATE); |
5. 生产避坑
- ON 条件必须唯一，否则抛出 ORA-30926 稳定行异常；
五、生产进阶｜存储过程封装 MERGE（可直接上线）
生产环境定时同步、批量补数，必须封装存储过程，搭配事务、异常捕获、日志输出，保证数据安全稳定。
1. 标准生产存储过程
plsql
CREATE OR REPLACE PROCEDURE P_SYNC_PATIENT_DATA
IS
BEGIN
MERGE INTO ms_brda d
USING temp_patient t
ON (d.brid = t.brid)
WHEN MATCHED THEN
UPDATE SET
d.brxm = t.brxm,
d.brxb = t.brxb,
d.mzhm = t.mzhm,
d.last_update = SYSDATE
WHEN NOT MATCHED THEN
INSERT (brid, brxm, brxb, mzhm, jdsj, last_update)
VALUES (t.brid, t.brxm, t.brxb, t.mzhm, t.jdsj, SYSDATE);
DBMS_OUTPUT.PUT_LINE('同步完成，影响行数：' || SQL%ROWCOUNT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('同步失败：' || SQLCODE || ' - ' || SQLERRM);
END P_SYNC_PATIENT_DATA;
/ |
2. 动态入参版：按天数增量同步
plsql
CREATE OR REPLACE PROCEDURE P_SYNC_PATIENT_BY_DAY(IN_DAY IN NUMBER)
IS
BEGIN
MERGE INTO ms_brda d
USING (
SELECT brid, brxm, brxb, mzhm, jdsj
FROM temp_patient
WHERE create_time >= SYSDATE - IN_DAY
) t
ON (d.brid = t.brid)
WHEN MATCHED THEN
UPDATE SET d.brxm = t.brxm, d.brxb = t.brxb, d.last_update = SYSDATE
WHEN NOT MATCHED THEN
INSERT (brid, brxm, brxb, mzhm, jdsj, last_update)
VALUES (t.brid, t.brxm, t.brxb, t.mzhm, t.jdsj, SYSDATE);
DBMS_OUTPUT.PUT_LINE('同步'||IN_DAY||'天数据，行数：'||SQL%ROWCOUNT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('异常：'||SQLERRM);
END P_SYNC_PATIENT_BY_DAY;
/ |
3. 全功能版：条件更新 + 自动清理脏数据
plsql
CREATE OR REPLACE PROCEDURE P_SYNC_PATIENT_FULL
IS
BEGIN
MERGE INTO ms_brda d
USING temp_patient t
ON (d.brid = t.brid)
WHEN MATCHED THEN
UPDATE SET d.brxb = t.brxb, d.last_update = SYSDATE
WHERE d.brxb IS NULL -- 仅修复空性别异常数据
DELETE WHERE d.is_valid = '0'-- 自动清理作废脏数据
WHEN NOT MATCHED THEN
INSERT (brid, brxm, brxb, mzhm, is_valid, last_update)
VALUES (t.brid, t.brxm, t.brxb, t.mzhm, '1', SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('执行失败：'||SQLERRM);
END P_SYNC_PATIENT_FULL;
/ |
4. 自动化落地：JOB定时任务
配置每日凌晨自动执行，实现无人值守数据同步。
plsql
-- 每日00:30自动执行患者数据同步
BEGIN
DBMS_JOB.SUBMIT(
JOB => 1,
WHAT => 'P_SYNC_PATIENT_DATA;',
NEXT_DATE => TO_DATE('2026-07-01 00:30:00','YYYY-MM-DD HH24:MI:SS'),
INTERVAL => 'TRUNC(SYSDATE+1) + 30/1440'
);
COMMIT;
END;
/ |
六、核心语法速查表（面试+生产常备）
语法 | 核心用途 | 生产关键要点 |
CASE WHEN | 行级状态翻译、字段判空 | 从上至下匹配，必须ELSE兜底防空值 |
EXISTS | 高效数据存在性判断 | 行级需关联主键，性能完胜IN/COUNT |
WITH | 拆解复杂SQL、生成临时数据源 | 无物理表，可直接对接MERGE做同步 |
MERGE | 批量增量增改数据同步 | 唯一条件匹配、原子执行、支持定时调度 |
七、全文总结
本文汇总的四大 Oracle 语法，是从普通SQL编写进阶到生产运维开发的核心分水岭。全覆盖数据查询、性能优化、报表统计、增量同步、自动化运维五大场景。
所有案例基于医院HIS真实业务编写，代码规范精简、无冗余，可直接修改字段落地生产，兼顾日常工作使用与面试复盘，是一套高实用性的Oracle实战常备手册。
