（课堂笔记）PL/SQL：异常处理、数据同步、日志

PL/SQL 异常处理与数据同步要点

1. 异常处理：使用EXCEPTION捕获错误，SQLERRM获取错误信息，结合ROLLBACK回滚事务，确保数据一致性。

2. 数据同步：

   • 全量同步：清空目标表后插入源表数据。
   • 增量同步：通过MERGE语句按主键比对更新或插入数据，高效处理增量变更。

3. 日志记录：记录程序运行状态（步骤、时间、数据量等），异常时保存错误信息，便于排查问题。

4. 关键语法：

   • TRUNCATE快速清表
   • SQL%ROWCOUNT获取影响行数
   • COMMIT/ROLLBACK控制事务

适用于ETL、数据迁移等场景，确保数据同步的可靠性和可追溯性。

PL/SQL：异常处理补充

---

数据源表到目标表的数据同步，注意事项或规则有哪些

📘 PL/SQL 课堂笔记

一、异常处理（Exception Handling）

🎯 使用场景

• 程序可能出现意外错误时，根据异常决定：继续运行、中断程序、提取错误信息等。

📝 语法结构

sql

EXCEPTION WHEN 异常场景 THEN 处理动作;

• OTHERS：捕获所有未明确指定的异常

🔧 关键内置变量

• SQLERRM：返回当前异常的完整错误信息

📌 示例1：捕获异常并显示信息

sql

EXCEPTION WHEN OTHERS THEN V_STR := SQLERRM; DBMS_OUTPUT.PUT_LINE('异常信息：' || V_STR);

📌 示例2：带事务控制的异常处理

sql

EXCEPTION WHEN OTHERS THEN ROLLBACK; -- 回滚DML操作 RAISE; -- 重新抛出异常，中断程序

✅ 工作中常用模式

sql

EXCEPTION WHEN OTHERS THEN ROLLBACK; INSERT INTO 日志表 VALUES (SQLERRM); COMMIT;

二、数据同步（Data Synchronization）

🧠 定义

将 A 表数据经过加工后写入 B 表的过程。

📌 全量同步

• 步骤：清空目标表 → 插入全部源表数据

• 特点：每次调用后数据为最新快照

sql

EXECUTE IMMEDIATE 'TRUNCATE TABLE 目标表'; INSERT INTO 目标表 SELECT * FROM 源表;

📌 增量同步

方式1：按时间段增量（如按年）

• 先删除目标表中该时间段数据

• 再插入源表中该时间段数据

sql

DELETE FROM 目标表 WHERE TO_CHAR(HIREDATE,'YYYY') = 年份; INSERT INTO 目标表 SELECT * FROM 源表 WHERE ...;

方式2：主键比对（MERGE）

• 用于源表数据可能更新或新增的场景

MERGE 语法模板

sql

MERGE INTO 目标表 M USING (SELECT * FROM 源表) N ON (M.主键 = N.主键) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...

• MATCHED：更新已有记录

• NOT MATCHED：插入新记录

完整示例

--示例：用 EMP 表的数据 插入更新到 EMP_0508 CREATE OR REPLACE PROCEDURE P_033 IS BEGIN MERGE INTO EMP_0508 M USING (SELECT * FROM EMP) N ON (M.EMPNO = N.EMPNO) WHEN MATCHED THEN UPDATE SET M.ENAME = N.ENAME, M.JOB = N.JOB, M.MGR = N.MGR, M.HIREDATE = N.HIREDATE, M.SAL = N.SAL, M.COMM = N.COMM, M.DEPTNO = N.DEPTNO WHEN NOT MATCHED THEN INSERT (M.EMPNO,M.ENAME,M.JOB,M.MGR,M.HIREDATE,M.SAL,M.COMM,M.DEPTNO) VALUES (N.EMPNO,N.ENAME,N.JOB,N.MGR,N.HIREDATE,N.SAL,N.COMM,N.DEPTNO); COMMIT; END; --调用 BEGIN P_033; END; --验证 SELECT * FROM EMP_0508;

三、日志记录（Logging）

🎯 目的

记录程序运行状态（步骤、时间、数据量、异常等），便于调试和调优。

🗃️ 日志表示例

sql

CREATE TABLE T_LOG ( PRO_NAME VARCHAR2(100), -- 程序名 STEP_NAME VARCHAR2(100), -- 步骤名 START_TIME DATE, -- 开始时间 END_TIME DATE, -- 结束时间 CNT NUMBER, -- 处理行数 STATUS VARCHAR2(100), -- 状态（Success/Error） MARK VARCHAR2(100) -- 备注/错误信息 );

📌 典型日志记录流程

1. 记录程序名、步骤名、开始时间

2. 执行业务逻辑

3. 记录结束时间、处理行数、状态

4. 异常时回滚并记录错误信息

sql

STEP_NAME := '同步数据'; START_TIME := SYSDATE; -- 业务操作 V_CNT := SQL%ROWCOUNT; STATUS := 'Success'; END_TIME := SYSDATE; INSERT INTO T_LOG VALUES (...);

⚠️ 异常块中的日志

sql

EXCEPTION WHEN OTHERS THEN ROLLBACK; MARK := SQLERRM; INSERT INTO T_LOG VALUES (...); COMMIT;

完整示例

--开发存储过程 并记录日志信息 CREATE OR REPLACE PROCEDURE P_034 IS PRO_NAME VARCHAR2(100) := 'P_034'; -- 记录程序的名称 STEP_NAME VARCHAR2(100); -- 用于接收程序的步骤名 START_TIME DATE; -- 用于接收步骤的开始时间 END_TIME DATE; --用于接收步骤的结束时间 STATUS VARCHAR2(100) := 'Error'; -- 用于记录步骤执行状态 V_CNT NUMBER; -- 用于接收总共处理了多少行数据 MARK VARCHAR2(100); -- 作为一个备注项 BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_0508'; STEP_NAME := '同步数据 EMP 到 EMP_0508'; START_TIME := SYSDATE; INSERT INTO EMP_0508 SELECT EMPNO,ENAME,JOB,MGR,HIREDATE, SAL / 0 AS SAL,COMM,DEPTNO FROM EMP; V_CNT := SQL%ROWCOUNT; COMMIT; STATUS := 'Success'; END_TIME := SYSDATE; MARK := '程序执行成功了！'; INSERT INTO T_LOG VALUES (PRO_NAME,STEP_NAME,START_TIME,END_TIME,V_CNT,STATUS, MARK); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; MARK := SQLERRM; INSERT INTO T_LOG VALUES (PRO_NAME,STEP_NAME,START_TIME,END_TIME,V_CNT,STATUS, MARK); COMMIT; END; --调用： BEGIN P_034; END; --验证 SELECT * FROM EMP_0508;-- 数据同步过来了 SELECT * FROM T_LOG;

四、关键点速记表