印刻数据库

外观

Oracle高级SQL特性 ​

Oracle数据库提供了丰富的高级SQL特性，这些特性可以帮助开发人员编写更复杂、更高效的查询语句，处理各种复杂的数据场景。本文将介绍Oracle的高级SQL特性，包括子查询、分析函数、集合操作、递归查询、JSON处理等。

子查询 ​

子查询是嵌套在另一个SQL语句中的查询，用于从多个表中获取数据或执行复杂的逻辑。

子查询分类 ​

1. 标量子查询 ​

返回单个值的子查询，可以在SELECT、WHERE、HAVING等子句中使用。

sql
-- 查询每个部门的平均薪资以及薪资高于部门平均薪资的员工
SELECT e.first_name, e.last_name, e.salary, e.department_id,
       (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_dept_salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

2. 行子查询 ​

返回单行多列的子查询，与单行比较运算符一起使用。

sql
-- 查询与Scott同部门同职位的员工
SELECT first_name, last_name, department_id, job_id
FROM employees
WHERE (department_id, job_id) = (
    SELECT department_id, job_id FROM employees WHERE last_name = 'Scott'
);

3. 表子查询 ​

返回多行多列的子查询，与IN、EXISTS等关键字一起使用。

sql
-- 查询部门平均薪资高于公司平均薪资的部门员工
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
);

4. 相关子查询 ​

子查询中引用了外部查询的列，每行外部查询都会执行一次子查询。

sql
-- 查询每个员工的薪资排名（部门内）
SELECT e1.first_name, e1.last_name, e1.salary, e1.department_id,
       (SELECT COUNT(*) + 1 FROM employees e2 
        WHERE e2.department_id = e1.department_id AND e2.salary > e1.salary) AS rank
FROM employees e1
ORDER BY department_id, rank;

5. 嵌套子查询 ​

子查询中嵌套了另一个子查询。

sql
-- 查询薪资高于部门平均薪资且部门平均薪资高于公司平均薪资的员工
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id
) AND department_id IN (
    SELECT department_id FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
);

子查询优化建议 ​

  • 尽量使用表连接替代相关子查询，提高性能
  • 使用EXISTS替代IN，尤其是当子查询结果集较大时
  • 使用WITH子句（CTE）简化复杂子查询
  • 为子查询中的表添加适当的索引

分析函数（窗口函数） ​

分析函数是Oracle中功能强大的高级SQL特性，用于计算基于一组行的聚合值，但不会减少结果集中的行数。

分析函数基本语法 ​

sql
function_name([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY order_expression [ASC|DESC], ...]
    [windowing_clause]
)

常用分析函数 ​

1. 排名函数 ​

  • RANK()：返回排名，相同值有相同排名，后续排名跳过
  • DENSE_RANK()：返回排名，相同值有相同排名，后续排名不跳过
  • ROW_NUMBER()：返回行号，即使相同值也有不同行号
sql
-- 查询员工薪资排名
SELECT first_name, last_name, salary, department_id,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

2. 聚合分析函数 ​

在分析子句中使用聚合函数，如SUM、AVG、MAX、MIN等。

sql
-- 查询员工累计薪资
SELECT first_name, last_name, salary, department_id,
       SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS cumulative_salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary,
       MAX(salary) OVER (PARTITION BY department_id) AS max_dept_salary
FROM employees;

3. 窗口框架子句 ​

定义分析函数的计算窗口，如：

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
sql
-- 查询员工薪资的移动平均
SELECT first_name, last_name, salary, department_id,
       AVG(salary) OVER (
           PARTITION BY department_id 
           ORDER BY salary DESC 
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS moving_avg
FROM employees;

4. 其他常用分析函数 ​

  • LAG()：获取前N行的值
  • LEAD()：获取后N行的值
  • FIRST_VALUE()：获取窗口中的第一个值
  • LAST_VALUE()：获取窗口中的最后一个值
  • NTILE()：将结果集分成N个桶
sql
-- 查询员工薪资的前后值
SELECT first_name, last_name, salary, department_id,
       LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary DESC) AS prev_salary,
       LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary DESC) AS next_salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS first_salary,
       LAST_VALUE(salary) OVER (
           PARTITION BY department_id 
           ORDER BY salary DESC 
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_salary,
       NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

集合操作 ​

Oracle支持标准的集合操作，用于合并或比较多个查询的结果集。

1. UNION 和 UNION ALL ​

  • UNION：合并两个结果集，去除重复行
  • UNION ALL：合并两个结果集，保留重复行
sql
-- 查询所有部门的经理和员工
SELECT first_name, last_name, 'Manager' AS role
FROM employees
WHERE employee_id IN (SELECT manager_id FROM departments)
UNION ALL
SELECT first_name, last_name, 'Employee' AS role
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM departments WHERE manager_id IS NOT NULL);

2. INTERSECT ​

返回两个结果集的交集（同时存在于两个结果集中的行）。

sql
-- 查询既在部门10又在部门20工作过的员工（假设存在历史表）
SELECT employee_id FROM employees_current WHERE department_id = 10
INTERSECT
SELECT employee_id FROM employees_history WHERE department_id = 20;

3. MINUS ​

返回第一个结果集中存在但第二个结果集中不存在的行。

sql
-- 查询在部门10工作但不在部门20工作的员工
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
MINUS
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20;

递归查询 ​

递归查询用于处理层次化数据，如组织结构、文件系统等。Oracle使用CONNECT BY子句或WITH递归子句实现递归查询。

1. CONNECT BY 语法 ​

sql
-- 查询员工的组织结构（使用CONNECT BY）
SELECT level, employee_id, first_name, last_name, manager_id,
       LPAD(' ', (level-1)*2) || first_name || ' ' || last_name AS org_tree
FROM employees
START WITH manager_id IS NULL  -- 根节点条件
CONNECT BY PRIOR employee_id = manager_id  -- 递归条件
ORDER SIBLINGS BY last_name;  -- 同级排序

2. WITH 递归子句（ANSI标准） ​

sql
-- 使用WITH递归查询组织结构
WITH org_hierarchy (level_num, employee_id, first_name, last_name, manager_id) AS (
    -- 锚点成员（根节点）
    SELECT 1, employee_id, first_name, last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归成员
    SELECT oh.level_num + 1, e.employee_id, e.first_name, e.last_name, e.manager_id
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT level_num, employee_id, first_name, last_name, manager_id,
       LPAD(' ', (level_num-1)*2) || first_name || ' ' || last_name AS org_tree
FROM org_hierarchy
ORDER BY level_num, last_name;

递归查询的高级用法 ​

循环检测 ​

sql
-- 使用NOCYCLE避免循环
SELECT level, employee_id, first_name, last_name, manager_id
FROM employees
START WITH employee_id = 100
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

递归深度限制 ​

sql
-- 限制递归深度为3
SELECT level, employee_id, first_name, last_name, manager_id
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id AND level <= 3;

JSON处理 ​

Oracle从12c开始提供了强大的JSON处理功能，支持JSON数据的存储、查询、修改和索引。

1. JSON数据类型 ​

sql
-- 创建包含JSON列的表
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_info JSON,
    created_date DATE DEFAULT SYSDATE
);

-- 插入JSON数据
INSERT INTO customers (customer_id, customer_info)
VALUES (1, '{"name": "John Doe", "email": "johndoe@example.com", "address": {"city": "Beijing", "street": "Chaoyang Road"}, "phone_numbers": ["13800138000", "13900139000"]}');

2. JSON查询 ​

使用JSON_VALUE、JSON_QUERY、JSON_TABLE等函数查询JSON数据。

sql
-- 查询JSON中的标量值
SELECT customer_id,
       JSON_VALUE(customer_info, '$.name') AS name,
       JSON_VALUE(customer_info, '$.email') AS email,
       JSON_VALUE(customer_info, '$.address.city') AS city
FROM customers;

-- 查询JSON中的复杂结构
SELECT customer_id,
       JSON_QUERY(customer_info, '$.address') AS address,
       JSON_QUERY(customer_info, '$.phone_numbers') AS phone_numbers
FROM customers;

-- 将JSON数组转换为关系表
SELECT customer_id, phone_number
FROM customers,
     JSON_TABLE(customer_info, '$.phone_numbers[*]' 
                COLUMNS (phone_number VARCHAR2(20) PATH '$'));

3. JSON修改 ​

使用JSON_MERGEPATCH函数修改JSON数据。

sql
-- 修改JSON数据
UPDATE customers
SET customer_info = JSON_MERGEPATCH(customer_info, '{"email": "newemail@example.com", "age": 30}')
WHERE customer_id = 1;

4. JSON索引 ​

创建JSON索引以提高查询性能。

sql
-- 创建JSON搜索索引
CREATE SEARCH INDEX customers_json_idx ON customers(customer_info) FOR JSON;

-- 创建JSON值索引
CREATE INDEX customers_name_idx ON customers(JSON_VALUE(customer_info, '$.name'));

高级连接技术 ​

1. 自连接 ​

将表与自身连接，用于比较同一表中的行。

sql
-- 查询员工及其经理的信息
SELECT e.first_name || ' ' || e.last_name AS employee_name,
       m.first_name || ' ' || m.last_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

2. 交叉连接（Cartesian Product） ​

返回两个表的笛卡尔积，需谨慎使用。

sql
-- 查询所有部门和所有职位的组合
SELECT d.department_name, j.job_title
FROM departments d
CROSS JOIN jobs j
ORDER BY d.department_name, j.job_title;

3. 外连接的高级用法 ​

全外连接 ​

sql
-- 查询所有员工及其部门，包括没有员工的部门和没有部门的员工
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

多表外连接 ​

sql
-- 查询员工、部门和位置信息
SELECT e.first_name, e.last_name, d.department_name, l.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN locations l ON d.location_id = l.location_id;

批量操作 ​

1. BULK COLLECT ​

使用BULK COLLECT将查询结果批量收集到集合中，提高性能。

sql
DECLARE
    TYPE emp_rec_type IS RECORD (
        first_name employees.first_name%TYPE,
        last_name employees.last_name%TYPE,
        salary employees.salary%TYPE
    );
    TYPE emp_tab_type IS TABLE OF emp_rec_type;
    emp_tab emp_tab_type;
BEGIN
    -- 批量收集数据
    SELECT first_name, last_name, salary
    BULK COLLECT INTO emp_tab
    FROM employees
    WHERE department_id = 60;
    
    -- 处理数据
    FOR i IN 1..emp_tab.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(emp_tab(i).first_name || ' ' || emp_tab(i).last_name || ': ' || emp_tab(i).salary);
    END LOOP;
END;

2. FORALL ​

使用FORALL语句批量执行DML操作，提高性能。

sql
DECLARE
    TYPE emp_id_type IS TABLE OF employees.employee_id%TYPE;
    emp_ids emp_id_type;
BEGIN
    -- 获取员工ID列表
    SELECT employee_id BULK COLLECT INTO emp_ids
    FROM employees
    WHERE department_id = 60;
    
    -- 批量更新
    FORALL i IN 1..emp_ids.COUNT
        UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id = emp_ids(i);
    
    COMMIT;
END;

高级分组和聚合 ​

1. ROLLUP和CUBE ​

用于生成小计和总计行。

sql
-- 使用ROLLUP生成小计和总计
SELECT department_id, job_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id)
ORDER BY department_id, job_id;

-- 使用CUBE生成所有可能的小计和总计
SELECT department_id, job_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id)
ORDER BY department_id, job_id;

2. GROUPING SETS ​

指定要计算的分组集，替代多个UNION查询。

sql
-- 使用GROUPING SETS替代UNION
SELECT department_id, job_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY GROUPING SETS (
    (department_id, job_id),  -- 按部门和职位分组
    (department_id),          -- 仅按部门分组
    (job_id),                -- 仅按职位分组
    ()                       -- 总计
)
ORDER BY department_id, job_id;

条件表达式的高级用法 ​

1. CASE表达式的高级用法 ​

sql
-- 使用CASE表达式进行复杂条件判断
SELECT first_name, last_name, salary,
       CASE 
           WHEN salary < 5000 THEN 'Low'
           WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
           WHEN salary > 10000 THEN 'High'
           ELSE 'Unknown'
       END AS salary_level,
       CASE department_id
           WHEN 10 THEN 'Administration'
           WHEN 20 THEN 'Marketing'
           WHEN 30 THEN 'Purchasing'
           WHEN 40 THEN 'Human Resources'
           WHEN 50 THEN 'Shipping'
           ELSE 'Other'
       END AS department_name
FROM employees;

2. DECODE函数 ​

Oracle特有的条件表达式，类似于简化的CASE表达式。

sql
-- 使用DECODE函数进行条件判断
SELECT first_name, last_name, salary,
       DECODE(
           TRUNC(salary/5000),
           0, 'Low',
           1, 'Medium',
           2, 'High',
           'Very High'
       ) AS salary_level
FROM employees;

版本差异 ​

Oracle 12c ​

  • 引入原生JSON支持
  • 支持WITH递归查询
  • 增强了分析函数功能
  • 引入IDENTITY列

Oracle 18c ​

  • 增强了JSON处理功能
  • 引入JSON_MERGEPATCH函数
  • 支持JSON路径表达式的增强功能

Oracle 19c ​

  • 长期支持版本
  • 增强了分析函数的性能
  • 支持更多的JSON函数

Oracle 21c ​

  • 支持JSON数据类型的增强
  • 引入新的分析函数
  • 增强了批量操作的性能

常见问题（FAQ） ​

Q: 分析函数和聚合函数有什么区别？ ​

A: 聚合函数会将多行数据合并为单行，而分析函数不会减少结果集的行数，它会为每一行计算一个结果，基于窗口内的多行数据。

Q: 什么时候使用子查询，什么时候使用表连接？ ​

A: 当查询逻辑简单且性能要求不高时，可以使用子查询；当查询涉及多个表的连接且需要高性能时，建议使用表连接，特别是内连接。

Q: 递归查询的最大深度是多少？ ​

A: 默认情况下，Oracle递归查询的最大深度是100。可以通过CONNECT BY子句中的LEVEL伪列或设置RECURSIVE_MAXDEPTH参数来调整。

Q: JSON数据类型和VARCHAR2存储JSON有什么区别？ ​

A: JSON数据类型提供了严格的JSON格式验证，而VARCHAR2存储JSON不会自动验证格式。此外，JSON数据类型支持更高效的JSON函数和索引。

Q: 如何优化包含分析函数的查询？ ​

A: 可以通过以下方式优化：

  • 减少PARTITION BY子句中的列数量
  • 合理设置窗口框架
  • 为分析函数的ORDER BY子句添加索引
  • 考虑使用并行查询

Q: 什么时候使用ROLLUP，什么时候使用CUBE？ ​

A: ROLLUP生成的分组集少于CUBE，适合需要逐层小计的场景；CUBE生成所有可能的分组集，适合需要全面统计的场景。

总结 ​

Oracle的高级SQL特性为开发人员提供了强大的工具，可以处理各种复杂的数据场景。本文介绍了子查询、分析函数、集合操作、递归查询、JSON处理、高级连接、批量操作和高级分组等特性。

在实际生产环境中，应根据具体业务需求选择合适的高级SQL特性，并注意性能优化。以下是一些最佳实践：

  • 优先使用ANSI标准语法，提高代码的可移植性
  • 合理使用分析函数，避免过度复杂的窗口定义
  • 谨慎使用递归查询，防止无限循环
  • 对JSON数据创建适当的索引，提高查询性能
  • 考虑使用批量操作替代单行操作，提高性能
  • 定期收集表统计信息，优化执行计划

随着Oracle版本的更新，高级SQL特性不断增强，如更强大的JSON支持、更高效的分析函数和更灵活的批量操作等。开发人员应关注新版本的特性，提高开发效率和性能。