大数据开发必备技能_第二阶段12_全量同步技术解析与实践指南

如果你对大数据开发充满好奇，但苦于零基础不知从何入手，这系列文章就是为你量身定制的！

零门槛入门：无需编程经验，从最基础的SQL查询讲起，手把手带你理解数据操作的核心逻辑。

实战为王：每章附赠真实场景练习题，边学边练，拒绝纸上谈兵。

转行利器：内容直击企业高频需求（数据清洗、分析、可视化），搭配职场避坑指南，助你快速构建“可迁移技能”。

系统进阶：从数据类型到复杂查询，从运算符到执行顺序，层层递进，帮你搭建扎实的大数据地基。

无论你是学生、职场新人，还是想跨界转行的探索者，跟着这份指南，用一杯伯牙绝弦的时间，迈出大数据开发的第一步！

🎉 致亲爱的读者朋友们：
📢 放心追更！本系列文章每日三篇干货更新，助你稳扎稳打！
🎥 配套视频解说正在全力打磨中，即将震撼上线——
🔥 关注收藏不迷路，精彩内容绝不辜负！
 

目录

1. 全量同步概述

2. 实现逻辑与步骤

3. 技术细节：动态SQL与存储过程

4. 代码示例解析

5. 实战练习

6. 常见面试题

7. 总结与注意事项

---

1. 全量同步概述

• 定义：用源表数据完全覆盖目标表，保证两者数据一致。

• 适用场景：数据量较小、无需保留历史版本、低频同步任务。

• 核心步骤：

  1. 清空目标表（如TRUNCATE）。

  2. 将源表数据插入目标表。

---

2. 实现逻辑与步骤

1. 创建目标表：结构与源表一致，可附加同步时间字段（如etl_dt）。

2. 清空目标表：使用动态SQL执行TRUNCATE，避免硬编码表名。

3. 插入数据：直接查询源表并插入目标表。

4. 封装存储过程：将逻辑封装为可复用的代码单元，支持异常处理。

---

3. 技术细节

动态SQL

• 作用：解决表名或条件不固定的场景（如动态清空表）。

• 语法：EXECUTE IMMEDIATE 'SQL语句'。

• 示例：

  sql

  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || table_name;  

存储过程

• 优势：封装逻辑、支持事务控制（如COMMIT）、异常捕获。

• 注意点：TRUNCATE是DDL语句，默认提交事务，需谨慎使用。

---

4. 代码示例解析

基础全量同步

sql

-- 创建目标表  
CREATE TABLE emp_tar AS  
SELECT e.*, SYSDATE etl_dt FROM emp e WHERE 1=2;  

-- 存储过程实现  
CREATE OR REPLACE PROCEDURE p_quanl IS  
BEGIN  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE emp_tar';  
  INSERT INTO emp_tar  
  SELECT t.*, SYSDATE FROM emp_source t;  
  COMMIT;  
EXCEPTION  
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);  
END; 

带参数的全量同步

sql

-- 根据工种参数过滤数据  
CREATE OR REPLACE PROCEDURE p_22(p_job VARCHAR2) IS  
BEGIN  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE emp_0318';  
  INSERT INTO emp_0318  
  SELECT * FROM emp WHERE job != p_job;  
  COMMIT;  
EXCEPTION  
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);  
END;  

---

5. 实战练习

1. 基础练习：将dept表全量同步到dept_backup表，附加etl_dt字段。

2. 进阶练习：编写存储过程，根据部门编号（deptno）动态同步数据到不同目标表（如dept_10, dept_20）。

3. 思考题：如果源表数据量极大，全量同步可能有哪些性能问题？如何优化？

---

6. 常见面试题

1.全量同步 vs 增量同步：各自的优缺点和适用场景是什么？

• 全量同步：

  • 优点：简单直接，无需追踪数据变化；保证目标表与源表完全一致。

  • 缺点：数据量大时效率低，资源消耗高；频繁同步可能导致业务中断。

  • 适用场景：数据量小、低频同步（如每日一次）、目标表需要完全覆盖（如初始化）。

• 增量同步：

  • 优点：仅同步新增或修改数据，效率高；资源占用少。

  • 缺点：需额外机制追踪变化（如时间戳、日志、触发器）；可能因追踪遗漏导致数据不一致。

  • 适用场景：数据量大、高频同步（如实时或每小时）、仅需更新变化部分（如订单状态变更）。

---

2.动态SQL的作用：举例说明哪些场景必须使用动态SQL？

• 作用：在运行时动态构建和执行SQL语句，解决静态SQL无法处理的不确定性。

• 必须使用动态SQL的场景：

  1. 动态表名或列名：根据参数选择不同表（如按月份分表：sales_2023_01）。

  2. 动态条件过滤：根据用户输入动态生成WHERE条件（如多条件筛选）。

  3. 执行DDL语句：TRUNCATE、CREATE TABLE等操作需通过动态SQL执行。

  • 示例：

    sql

    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;  

---

3.TRUNCATE与DELETE的区别：事务控制和性能影响有何不同？

特性	TRUNCATE	DELETE
类型	DDL语句	DML语句
事务控制	不可回滚（隐式提交）	可回滚（需显式COMMIT或ROLLBACK）
性能	高效（直接释放数据页，不记录逐行删除）	低效（记录逐行删除日志）
使用场景	快速清空全表	删除部分数据（带WHERE条件）

---

4.存储过程优势：为何在数据同步中使用存储过程？

1. 封装性：将同步逻辑（如清空表、插入数据）封装为单一单元，便于维护和复用。

2. 事务管理：支持COMMIT和ROLLBACK，确保数据一致性（如清空表后插入失败可回滚）。

3. 性能优化：在数据库服务器端执行，减少网络传输开销。

4. 异常处理：通过EXCEPTION块捕获错误，避免同步中断。

5. 动态SQL支持：灵活处理动态表名或条件。

---

5.异常处理：如何在全量同步中实现错误日志记录？

1. 捕获异常：在存储过程中使用EXCEPTION块捕获错误。

2. 记录日志：将错误信息写入日志表或输出到日志文件。

   • 示例（PL/SQL）：

     sql

     CREATE OR REPLACE PROCEDURE p_sync IS  
     BEGIN  
       EXECUTE IMMEDIATE 'TRUNCATE TABLE target_table';  
       INSERT INTO target_table SELECT * FROM source_table;  
       COMMIT;  
     EXCEPTION  
       WHEN OTHERS THEN  
         INSERT INTO error_log (error_message, error_time)  
         VALUES (SQLERRM, SYSDATE);  
         ROLLBACK;  
     END;  

---

7. 总结与注意事项

• 核心要点：清空目标表 → 插入源数据 → 事务控制。

• 注意事项：

  • TRUNCATE不可回滚，生产环境慎用（可改用DELETE+COMMIT）。

  • 数据量大时全量同步效率低，需考虑分批次或增量同步。

  • 动态SQL需防范SQL注入，避免拼接未校验的输入参数。

• 适用性：适合小数据、低频任务；不适用实时或高频同步场景。

---

附录：完整代码示例与练习答案

源系统 来源表   
      数据的生成时间   数据的修改时间
目标表

把a表的数据同步到b表

-- 全量同步
 逻辑：用源表的数据直接覆盖目标表。
 
 实现的逻辑：在往目标表中插入数据之前，【先清空目标表】，然后查询源表的数据，直接插入目标表。
 
 适用于数据量小的情况；


a表数据全量更新到b

来源表 a (没有表结构数据 模拟) 
目标表 b

查询出a表 所有的 数据 
写入前清空b表数据
写入到b表 

技术：
1.创建目标表 b

2.写入前清空b表数据(动态sql)

3.插入

4.存储过程


 
 --1 创建来源表  etl_dt
 
 create table emp_source as
 select e.*,sysdate create_date,sysdate last_update_date 
 from emp e where 1=2;
 
insert into emp_source
select e.*, hiredate create_date,sysdate last_update_date from emp e ;
 
select * from emp_source;




 --2 创建数据同步的目标表
 
etl 时间

create table emp_tar 
as select e.*,sysdate etl_dt from emp e  --etl_dt 数据同步时间
where 1=2;
 
select * from emp_source; --来源表
select * from emp_tar;--目标表
 
 --3开发全量同步的存储过程
 create or replace procedure p_quanl is
 begin
   --全量同步数据到目标表
   --清空目标表数据
   execute immediate 'truncate table emp_tar';
   --插入目标表数据 数据来源：来源表
   insert into emp_tar
     select t.empno,
            t.ename,
            t.job,
            t.mgr,
            t.hiredate,
            t.sal,
            t.comm,
            t.deptno,
            sysdate etl_dt
       from emp_source t;
   commit;
 exception
   when others then
     dbms_output.put_line(sqlerrm);
 end;
 
 
begin
   p_quanl;
end;
 
 
 
 
 
 --模拟来源表产生变化
 
 delete from emp_source t where  t.deptno=20;
 
 select * from emp_source; 
 
 
 
练习
通过入参 p_job 工种,将非这个工种的数据,全量同步到 emp_0318这个表
  (全量用truncate 实现,使用动态sql实现); 
create table emp_0318 as select * from emp where 1=2;



create or replace procedure p_22(p_job varchar2) is
begin
  execute immediate 'truncate table emp_0318';
  insert into emp_0318
    select  t.empno,
        t.ename,
        t.job,
        t.mgr,
        t.hiredate,
        t.sal,
        t.comm,
        t.deptno from emp t where t.job != p_job;
  commit;

exception
  when others then
    dbms_output.put_line(sqlerrm);
end;

begin
  p_22('CLERK');
end; 


---------  存储过程 & 动态sql & 全量同步   --------

1.存储过程是封装一段 数据的 同步&转换 逻辑;

2.动态sql,当sql中存在不稳定因素,比如,表名不确定,筛选条件不确定,
或者是 ddl语句(truncate / drop )不能直接运行,这个时候需要 拼接一个
变量的sql语句字符串,然后用 execute immediate sql语句字符串 去动态执行
如果这个sql是 select 语句 一般后面还有 into 变量赋值。

3.全量同步
  在插入目标表的时候,需要先清空目标表,这样才能保证目标表的数据不会重复。
  (否则 我们调用一次存储过程,目标表的数据就会重复一次)