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. 标准语法sqlCASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2ELSE 默认结果END AS 字段别名3. HIS实战患者性别翻译 空值标记sqlSELECTd.brid,d.brxm,CASEWHEN d.brxb IS NULL THEN 性别未录入WHEN d.brxb 1 THEN 男WHEN d.brxb 2 THEN 女ELSE 未知END AS brxb_textFROM ms_brda dWHERE d.jdsj TRUNC(SYSDATE) - 7;4. 避坑要点条件从上至下匹配精准条件前置宽泛条件后置必须使用ELSE兜底防止查询出现空值异常仅用于前端展示、字段翻译复杂业务逻辑建议封装存储过程。二、EXISTS高效存在性判断优化神器1. 核心原理EXISTS 属于半连接查询匹配到第一条符合条件的数据立即终止扫描性能碾压 IN、COUNT(*)、LEFT JOIN 判空是大数据量判存最优方案。2. 经典踩坑全局判断 VS 行级判断这是 90% 开发者都会写错的点直接决定业务逻辑是否正确。❌ 错误写法全局判断所有行结果一致无业务意义sqlSELECTd.brid,d.brxm,CASEWHEN EXISTS(SELECT 1 FROM ms_brda c WHERE c.brxb IS NULL)THEN 有空性别ELSE 女END AS xbFROM ms_brda d;问题子查询未关联外层表仅判断「整张表是否存在空性别」所有行结果完全一致。✅ 正确写法行级关联核心精髓sqlSELECTd.brid,d.brxm,CASEWHEN EXISTS(SELECT 1FROM ms_brda cWHERE c.brid d.brid -- 关联外层主键实现单行独立判断AND c.brxb IS NULLAND ROWNUM 1 -- 性能优化匹配即停止扫描) THEN 有空性别ELSE TO_CHAR(d.brxb)END AS xbFROM ms_brda dWHERE d.mzhm 202212135547 OR d.jdsj TRUNC(SYSDATE) - 12;3. 高频实战NOT EXISTS 反向匹配查询近30天无缴费记录的患者替代低效左连接判空。sqlSELECT d.brid, d.brxmFROM ms_brda dWHERE NOT EXISTS(SELECT 1FROM pay_record prWHERE pr.patient_id d.bridAND pr.pay_date TRUNC(SYSDATE) - 30);4. 核心总结子查询统一写SELECT 1无需查询具体字段大数据量判存优先 EXISTS禁用 IN想要逐行独立判断必须关联外层主键。三、WITH CTE结构化拆解复杂SQL1. 核心优势WITH 可定义多个临时结果集彻底解决多层子查询嵌套混乱问题代码层级清晰、可读性拉满可直接作为 MERGE 数据源无需创建物理临时表。2. 实战多CTE链式统计处方数据sqlWITH-- 近7天处方数据recent_pres AS (SELECT pres_id, doctor_id, patient_id, pres_date, statusFROM prescriptionWHERE pres_date TRUNC(SYSDATE) - 7),-- 筛选未结算处方unpaid_pres AS (SELECT doctor_id, patient_idFROM recent_presWHERE status 未结算)-- 统计医生处方总量、未结算数量SELECTd.doctor_id,d.doctor_name,COUNT(r.pres_id) AS total_pres_count,COUNT(u.patient_id) AS unpaid_pres_countFROM doctor dLEFT JOIN recent_pres r ON d.doctor_id r.doctor_idLEFT JOIN unpaid_pres u ON d.doctor_id u.doctor_idGROUP BY d.doctor_id, d.doctor_name;3. 关键特性临时结果集仅当前SQL生效执行后自动销毁支持多段定义后段CTE可直接引用前段结果生产高频搭配 MERGE实现无物理临时表数据同步。四、MERGE INTO增改一体数据同步神器1. 核心价值单条SQL完成匹配更新、不匹配新增彻底抛弃「先查询判断、再UPDATE/INSERT」的繁琐逻辑是增量同步、日统计、数据修复的核心语法。2. 标准语法sqlMERGE INTO 目标表 tUSING 数据源 sON (唯一匹配条件)WHEN MATCHED THEN UPDATE SET 字段值WHEN NOT MATCHED THEN INSERT(字段列表) VALUES(对应值);3. 基础实战患者数据增量同步sqlMERGE INTO ms_brda dUSING temp_patient tON (d.brid t.brid)WHEN MATCHED THENUPDATE SETd.brxm t.brxm,d.brxb t.brxb,d.mzhm t.mzhm,d.last_update SYSDATEWHEN NOT MATCHED THENINSERT (brid, brxm, brxb, mzhm, jdsj, last_update)VALUES (t.brid, t.brxm, t.brxb, t.mzhm, t.jdsj, SYSDATE);4. 高阶王炸WITH MERGE 无临时表同步生产最优写法无需建表直接统计数据并同步至统计表。sqlMERGE INTO prescription_stat sUSING (WITH pres_data AS (SELECT doctor_id, pres_idFROM prescriptionWHERE pres_date TRUNC(SYSDATE) - 1)SELECT doctor_id, COUNT(pres_id) AS pres_numFROM pres_dataGROUP BY doctor_id) tON (s.doctor_id t.doctor_id AND s.stat_date TRUNC(SYSDATE))WHEN MATCHED THENUPDATE SET s.pres_num t.pres_num, s.update_time SYSDATEWHEN NOT MATCHED THENINSERT (doctor_id, pres_num, stat_date, update_time)VALUES (t.doctor_id, t.pres_num, TRUNC(SYSDATE), SYSDATE);5. 生产避坑ON 条件必须唯一否则抛出 ORA-30926 稳定行异常支持条件更新、联动 DELETE 清理脏数据语句具备原子性要么全部成功要么全部回滚。五、生产进阶存储过程封装 MERGE可直接上线生产环境定时同步、批量补数必须封装存储过程搭配事务、异常捕获、日志输出保证数据安全稳定。1. 标准生产存储过程plsqlCREATE OR REPLACE PROCEDURE P_SYNC_PATIENT_DATAISBEGINMERGE INTO ms_brda dUSING temp_patient tON (d.brid t.brid)WHEN MATCHED THENUPDATE SETd.brxm t.brxm,d.brxb t.brxb,d.mzhm t.mzhm,d.last_update SYSDATEWHEN NOT MATCHED THENINSERT (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;EXCEPTIONWHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE(同步失败 || SQLCODE || - || SQLERRM);END P_SYNC_PATIENT_DATA;/2. 动态入参版按天数增量同步plsqlCREATE OR REPLACE PROCEDURE P_SYNC_PATIENT_BY_DAY(IN_DAY IN NUMBER)ISBEGINMERGE INTO ms_brda dUSING (SELECT brid, brxm, brxb, mzhm, jdsjFROM temp_patientWHERE create_time SYSDATE - IN_DAY) tON (d.brid t.brid)WHEN MATCHED THENUPDATE SET d.brxm t.brxm, d.brxb t.brxb, d.last_update SYSDATEWHEN NOT MATCHED THENINSERT (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;EXCEPTIONWHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE(异常||SQLERRM);END P_SYNC_PATIENT_BY_DAY;/3. 全功能版条件更新 自动清理脏数据plsqlCREATE OR REPLACE PROCEDURE P_SYNC_PATIENT_FULLISBEGINMERGE INTO ms_brda dUSING temp_patient tON (d.brid t.brid)WHEN MATCHED THENUPDATE SET d.brxb t.brxb, d.last_update SYSDATEWHERE d.brxb IS NULL -- 仅修复空性别异常数据DELETE WHERE d.is_valid 0-- 自动清理作废脏数据WHEN NOT MATCHED THENINSERT (brid, brxm, brxb, mzhm, is_valid, last_update)VALUES (t.brid, t.brxm, t.brxb, t.mzhm, 1, SYSDATE);COMMIT;EXCEPTIONWHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE(执行失败||SQLERRM);END P_SYNC_PATIENT_FULL;/4. 自动化落地JOB定时任务配置每日凌晨自动执行实现无人值守数据同步。plsql-- 每日00:30自动执行患者数据同步BEGINDBMS_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(SYSDATE1) 30/1440);COMMIT;END;/六、核心语法速查表面试生产常备语法核心用途生产关键要点CASE WHEN行级状态翻译、字段判空从上至下匹配必须ELSE兜底防空值EXISTS高效数据存在性判断行级需关联主键性能完胜IN/COUNTWITH拆解复杂SQL、生成临时数据源无物理表可直接对接MERGE做同步MERGE批量增量增改数据同步唯一条件匹配、原子执行、支持定时调度七、全文总结本文汇总的四大 Oracle 语法是从普通SQL编写进阶到生产运维开发的核心分水岭。全覆盖数据查询、性能优化、报表统计、增量同步、自动化运维五大场景。所有案例基于医院HIS真实业务编写代码规范精简、无冗余可直接修改字段落地生产兼顾日常工作使用与面试复盘是一套高实用性的Oracle实战常备手册。
