Oracle 用with as复制表总结

在 Oracle 中使用 WITH ... AS (CTE) 来“复制表”是一个巧妙的技巧，但它有其特定的用途和限制。

首先，要明确核心概念：WITH ... AS 本身并不直接创建物理表。它定义的是一个临时的、仅存在于当前查询生命周期内的结果集（可以看作是一个临时视图）。

因此，我们用 CTE 来“复制表”，实际上是 “先定义原始表数据的临时视图，然后基于这个视图来创建物理表”。

---

方法一：CTE + CREATE TABLE AS SELECT (CTAS)

这是最直接和常用的方法。利用 CTE 对源数据进行加工筛选后，再用 CTAS 语句将结果持久化到一张新物理表中。

语法结构：

CREATE TABLE new_table_name
AS
WITH cte_name AS (
    -- 在这里定义你的“复制源”，可以进行各种操作
    SELECT *
    -- 可以添加更多操作：筛选列、过滤行、计算新列等
    FROM original_table
    -- WHERE ... 可以在这里加条件
)
SELECT * FROM cte_name;

实用示例：

1. 精确复制表（结构和数据）

   CREATE TABLE employees_copy
   AS
   WITH original_data AS (
       SELECT * FROM employees
   )
   SELECT * FROM original_data;
   

   这等价于直接 CREATE TABLE employees_copy AS SELECT * FROM employees;，这里用 CTE 是为了演示结构。

2. 复制并处理数据（这才是 CTE 的妙用所在）

   -- 复制表，但同时给所有经理加薪10%后再保存
   CREATE TABLE employees_after_raise
   AS
   WITH adjusted_salaries AS (
       SELECT 
           emp_id,
           emp_name,
           job_title,
           -- 使用 CASE 语句进行条件更新
           CASE 
               WHEN job_title LIKE '%Manager%' THEN salary * 1.10 
               ELSE salary 
           END AS new_salary,
           department_id
       FROM employees
       -- 还可以同时过滤掉某些记录
       WHERE status = 'Active'
   )
   SELECT * FROM adjusted_salaries;
   

3. 从复杂查询结果创建新表

   -- 创建一个包含部门统计信息的新表
   CREATE TABLE department_stats
   AS
   WITH dept_agg AS (
       SELECT 
           d.department_name,
           COUNT(e.emp_id) AS employee_count,
           AVG(e.salary) AS avg_salary,
           SUM(e.salary) AS total_budget
       FROM departments d
       LEFT JOIN employees e ON d.department_id = e.department_id
       GROUP BY d.department_name
   )
   SELECT * FROM dept_agg;
   

---

方法二：CTE + INSERT INTO (向已存在的表插入数据)

如果目标表已经存在，你可以使用 CTE 来处理数据，然后插入到目标表中。

语法结构：

INSERT INTO existing_target_table (col1, col2, col3, ...)
WITH cte_name AS (
    SELECT col1, col2, col3, ...
    FROM original_table
    -- 各种转换和过滤
)
SELECT col1, col2, col3, ... FROM cte_name;

示例：

-- 假设我们已经有一个结构相同的表 employees_archive
INSERT INTO employees_archive (employee_id, name, salary, hire_date)
WITH prepared_data AS (
    SELECT 
        emp_id,
        UPPER(emp_name) AS name, -- 在插入前处理数据
        salary,
        hire_date
    FROM employees
    WHERE hire_date < ADD_MONTHS(SYSDATE, -12) -- 只取一年前的员工
)
SELECT employee_id, name, salary, hire_date FROM prepared_data;

---

重要注意事项和局限性

1. 不复制约束和索引：使用 CREATE TABLE ... AS SELECT（无论是否带 CTE）创建的新表只会包含基本的数据和列结构。它不会自动复制源表的：

   • 主键 (Primary Keys)
   • 外键 (Foreign Keys)
   • 唯一约束 (Unique Constraints)
   • 检查约束 (Check Constraints)
   • 索引 (Indexes)
   • 注释 (Comments)
   • 默认值 (Default Values) （在某些情况下会复制）
   • 分区策略 (Partitioning)

2. CTE 是临时的：WITH 子句中定义的结果集仅在它所属的 SQL 语句执行期间存在。语句执行完毕后，它就消失了。新表 employees_copy 是永久的，但定义它的 CTE 不是。

3. 性能考量：对于纯粹的全表复制，直接使用 CREATE TABLE ... AS SELECT 而不嵌套 CTE 效率更高，因为减少了一层解析。CTE 的真正价值在于在复制过程中进行复杂的数据转换、过滤和整合。

总结

在 Oracle 中，WITH ... AS 并不是一个直接的表复制命令，而是一个强大的数据准备工具。你可以把它和 CREATE TABLE ... AS SELECT 或 INSERT INTO ... SELECT 结合使用，来实现一种 “智能复制”——即在复制数据的同时完成清洗、转换、计算和过滤等操作，然后将最终结果保存到物理表中。

如果你的需求只是简单复制，直接用 CREATE TABLE new_table AS SELECT * FROM old_table;。
如果你的复制过程需要复杂的逻辑处理，那么 CTE + CTAS 是你的最佳选择。