oracle之with

博客展示了一段数据库代码,使用了公共表表达式(CTE),通过`with a as`定义临时结果集`a`,从`blacklist`表中选取`phoneno`列,再从`a`中选取所有数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

with a as (select phoneno from blacklist)
select * from a;
<think>我们注意到用户的上一个问题与Oracle临时表相关,但当前问题转向了WITH子句(公共表表达式,CTE)的用法和示例。根据引用[2]提供的线索(展示了WITH子句中定义PL/SQL函数的示例),以及用户问题的明确要求,我们将重点解析Oracle WITH子句。 以下为结构化回答: ### 一、WITH子句的核心概念 **公共表表达式(CTE)** 是SQL标准中用于创建**临时命名结果集**的语法结构,在Oracle中称为`WITH子句`或`子查询因子化`: - **临时性**:仅在当前SQL语句内有效 - **可重用性**:同一查询中可多次引用 - **递归支持**:实现层次结构查询(如组织树)[^2] ### 二、基础语法结构 ```sql WITH cte_name (column_list) AS ( -- 定义CTE SELECT ... -- 基础查询 ) SELECT * FROM cte_name; -- 主查询引用CTE ``` ### 三、典型使用场景与示例 #### 场景1:简化复杂查询(替代嵌套子查询) ```sql -- 计算部门平均工资及与总平均工资的差异 WITH dept_salaries AS ( SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id ), company_avg AS ( SELECT AVG(avg_sal) total_avg FROM dept_salaries ) SELECT d.department_name, ds.avg_sal, ds.avg_sal - ca.total_avg AS diff FROM dept_salaries ds JOIN departments d ON d.department_id = ds.department_id CROSS JOIN company_avg ca; ``` #### 场景2:递归查询(处理层次结构) ```sql -- 查询员工及其所有下属的层级关系 WITH emp_hierarchy (employee_id, name, manager_id, lvl) AS ( -- 锚点成员:顶层管理者 SELECT employee_id, last_name, manager_id, 1 FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员:逐级向下 SELECT e.employee_id, e.last_name, e.manager_id, eh.lvl + 1 FROM emp_hierarchy eh JOIN employees e ON eh.employee_id = e.manager_id ) SELECT * FROM emp_hierarchy; ``` #### 场景3:在WITH子句中定义函数(Oracle 12c+) ```sql -- 创建内联PL/SQL函数进行数据转换[^2] WITH FUNCTION format_name(p_first VARCHAR2, p_last VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN INITCAP(p_last) || ', ' || INITCAP(p_first); END; SELECT format_name(first_name, last_name) AS full_name, salary FROM employees WHERE ROWNUM <= 5; /* 输出示例: FULL_NAME SALARY ----------------- ------ King, Steven 24000 Kochhar, Neena 17000 ... */ ``` ### 四、性能优化要点 1. **物化提示**:使用`MATERIALIZE`提示强制Oracle将CTE结果存入临时段(适用于复杂CTE多次引用) ```sql WITH dept_data AS ( SELECT /*+ MATERIALIZE */ * FROM departments WHERE location_id = 1700 ) SELECT ... FROM dept_data; ``` 2. **与临时表的性能对比**: | 场景 | CTE优势 | 临时表优势 | |-----------------------|----------------------------------|-----------------------------| | 简单查询/单次引用 | 无额外I/O开销 | - | | 大数据集多次引用 | 可能重复计算 | 结果持久化减少计算量 | | 跨程序单元调用 | 不可用 | 会话级数据保留 | | 递归查询 | 原生支持 | 需手动实现递归逻辑 | ### 五、使用限制 1. 单个WITH子句最多支持32767个CTE 2. 递归CTE中必须使用`UNION ALL` 3. 函数定义仅限12c及以上版本[^2] ### 六、最佳实践 1. **命名清晰**:使用有意义的CTE名称(如`emp_hierarchy`而非`t1`) 2. **避免深度嵌套**:优先使用多个CTE而非嵌套子查询 3. **谨慎使用递归**:设置`MAX_RECURSION_LEVEL`防止无限循环 ```sql SELECT * FROM emp_hierarchy OPTION (MAX_RECURSION 10); -- 限制递归10层 ``` > **关键总结**:WITH子句通过模块化查询逻辑显著提升复杂SQL的可读性,特别适合需要**分步计算**或**递归处理**的场景,但在大数据集多次引用时需评估物化策略[^2]。 §§相关问题§§ 1. 如何在Oracle中调试递归CTE? 2. WITH子句中的函数与存储过程有何区别? 3. 如何强制Oracle物化CTE结果? 4. CTE与内联视图的性能差异如何量化? 5. 递归CTE在树形结构查询中的具体应用案例?</think>### Oracle WITH子句(CTE)详解 Oracle的**WITH子句**(公共表表达式,CTE)是编写复杂SQL的核心工具,它通过创建**临时命名结果集**提升代码可读性和性能。以下是关键知识点和示例: --- #### 一、核心作用与优势 1. **模块化查询** 将复杂查询拆分为多个逻辑单元,类似临时视图 2. **递归查询支持** 处理层次结构数据(如组织树、BOM表) 3. **性能优化** 减少重复计算,优化器可缓存中间结果 4. **代码复用** 同一查询中多次引用同一CTE --- #### 二、基础语法结构 ```sql WITH cte_name1 AS (SELECT ...), -- CTE定义 cte_name2 AS (SELECT ...) -- 可定义多个CTE SELECT ... -- 主查询 FROM cte_name1 JOIN cte_name2 ON ... ``` --- #### 三、典型使用场景与示例 ##### 场景1:简化复杂查询(替代嵌套子查询) ```sql -- 计算部门平均工资及与总平均工资的差异 WITH dept_salaries AS ( SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id ), company_avg AS ( SELECT AVG(salary) total_avg FROM employees ) SELECT d.department_name, ds.avg_sal, ds.avg_sal - ca.total_avg AS diff FROM dept_salaries ds JOIN departments d ON d.department_id = ds.department_id CROSS JOIN company_avg ca; ``` ##### 场景2:递归查询(层次结构遍历) ```sql -- 查询员工及其所有下属的层级关系 WITH emp_hierarchy (employee_id, name, manager_id, lvl) AS ( -- 锚点成员:顶层管理者 SELECT employee_id, last_name, manager_id, 1 FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员:逐级向下 SELECT e.employee_id, e.last_name, e.manager_id, eh.lvl + 1 FROM emp_hierarchy eh JOIN employees e ON eh.employee_id = e.manager_id ) SELECT * FROM emp_hierarchy; ``` ##### 场景3:在WITH中定义函数(Oracle 12c+) ```sql -- 创建内联PL/SQL函数进行数据转换[^2] WITH FUNCTION format_name(p_first VARCHAR2, p_last VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN INITCAP(p_last) || ', ' || INITCAP(p_first); END; SELECT format_name(first_name, last_name) AS full_name, salary FROM employees WHERE ROWNUM <= 5; /* 输出示例: FULL_NAME SALARY ----------------- ------ King, Steven 24000 Kochhar, Neena 17000 ... */ ``` --- #### 四、性能优化技巧 1. **物化提示** 强制Oracle将CTE结果存入临时段(适用于复杂CTE多次引用) ```sql WITH dept_data AS ( SELECT /*+ MATERIALIZE */ * FROM departments WHERE location_id = 1700 ) SELECT ... FROM dept_data; ``` 2. **递归查询优化** 使用`SEARCH DEPTH FIRST`控制遍历顺序: ```sql WITH RECURSIVE org_tree AS (...) SEARCH DEPTH FIRST BY department_id SET order_seq SELECT * FROM org_tree; ``` 3. **与临时表对比** | 场景 | CTE优势 | 临时表优势 | |-----------------------|----------------------------------|-----------------------------| | 简单查询/单次引用 | 无额外I/O开销 | - | | 大数据集多次引用 | 可能重复计算 | 结果持久化减少计算量 | | 跨程序单元调用 | 不可用 | 会话级数据保留 | --- #### 五、使用限制 1. 单个WITH子句最多支持32767个CTE 2. 递归CTE中必须使用`UNION ALL` 3. 函数定义仅限12c及以上版本[^2] > **最佳实践**:优先用CTE处理逻辑分步的查询,当需要跨SQL复用数据时改用全局临时表。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值