Oracle SQL 书写规范

SQL 书写规范概述

良好的 SQL 书写规范不仅能够提高代码的可读性和可维护性，还能够提高查询性能，减少系统资源消耗。在 Oracle 数据库中，SQL 语句的编写方式直接影响优化器生成的执行计划，进而影响查询性能。因此，制定和遵循 SQL 书写规范对于 DBA 和开发人员来说至关重要。

基础书写规范

1. 命名规范

表名和列名：使用有意义的名称，避免使用缩写和简写
统一命名风格：选择一种命名风格并保持一致，推荐使用下划线分隔的小写命名（snake_case）
避免保留字：不要使用 Oracle 保留字作为表名、列名或其他对象名
长度限制：表名和列名长度不应超过 30 个字符

示例：

sql

-- 推荐
SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE department_id = 10;

-- 不推荐
SELECT empid, fname, lname, hdate FROM emp WHERE deptid = 10;

2. 格式规范

大写关键字：SQL 关键字（如 SELECT、FROM、WHERE 等）使用大写
缩进：使用 4 个空格进行缩进，提高代码可读性
换行：每个主要子句（SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY）单独一行
逗号位置：将逗号放在列名之前，便于添加、删除或注释列
条件格式：每个条件单独一行，使用适当的缩进

示例：

sql

-- 推荐
SELECT
    e.employee_id
    ,e.first_name
    ,e.last_name
    ,d.department_name
    ,j.job_title
FROM
    employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN jobs j ON e.job_id = j.job_id
WHERE
    e.hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD')
    AND e.salary > 5000
    AND d.location_id = 1700
ORDER BY
    e.last_name
    ,e.first_name;

-- 不推荐
select e.employee_id,e.first_name,e.last_name,d.department_name,j.job_title from employees e join departments d on e.department_id=d.department_id join jobs j on e.job_id=j.job_id where e.hire_date>to_date('2020-01-01','YYYY-MM-DD') and e.salary>5000 and d.location_id=1700 order by e.last_name,e.first_name;

3. 注释规范

单行注释：使用 -- 注释单行
多行注释：使用 /* */ 注释多行或复杂逻辑
注释内容：注释应清晰、简洁，说明 SQL 的目的和逻辑
避免过度注释：只注释复杂或难以理解的代码，不要注释显而易见的内容

示例：

sql

-- 查询 2020 年以后入职的员工信息
SELECT
    employee_id
    ,first_name
    ,last_name
    ,hire_date
FROM
    employees
WHERE
    hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');

/*
 * 复杂查询示例：
 * 查询部门平均工资超过 8000 的部门
 * 并按平均工资降序排列
 */
SELECT
    d.department_id
    ,d.department_name
    ,ROUND(AVG(e.salary), 2) AS avg_salary
FROM
    employees e
    JOIN departments d ON e.department_id = d.department_id
GROUP BY
    d.department_id
    ,d.department_name
HAVING
    AVG(e.salary) > 8000
ORDER BY
    avg_salary DESC;

性能优化规范

1. 避免全表扫描

使用索引：为查询条件中的列创建适当的索引
避免使用函数：不要在 WHERE 子句的索引列上使用函数
避免 IS NULL/IS NOT NULL：在索引列上使用 IS NULL/IS NOT NULL 可能导致全表扫描
合理使用 LIKE：避免使用 LIKE '%value%'，这会导致全表扫描

示例：

sql

-- 推荐：使用索引列，避免函数
SELECT * FROM employees WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');

-- 不推荐：在索引列上使用函数
SELECT * FROM employees WHERE TRUNC(hire_date) > TO_DATE('2020-01-01', 'YYYY-MM-DD');

-- 推荐：使用 LIKE 'value%'，可以使用索引
SELECT * FROM employees WHERE last_name LIKE 'S%';

-- 不推荐：使用 LIKE '%value%'，会导致全表扫描
SELECT * FROM employees WHERE last_name LIKE '%mith%';

2. 优化 JOIN 操作

使用明确的 JOIN 语法：使用 ANSI JOIN 语法，避免使用隐式 JOIN
JOIN 顺序：将返回结果集较小的表放在前面
避免笛卡尔积：确保所有 JOIN 都有适当的连接条件
使用适当的索引：为连接列创建索引

示例：

sql

-- 推荐：使用 ANSI JOIN 语法
SELECT
    e.first_name
    ,e.last_name
    ,d.department_name
FROM
    employees e
    JOIN departments d ON e.department_id = d.department_id;

-- 不推荐：使用隐式 JOIN
SELECT
    e.first_name
    ,e.last_name
    ,d.department_name
FROM
    employees e
    ,departments d
WHERE
    e.department_id = d.department_id;

3. 优化子查询

使用 EXISTS 代替 IN：对于大表，EXISTS 通常比 IN 更高效
使用 JOIN 代替子查询：在很多情况下，JOIN 比子查询更高效
避免相关子查询：相关子查询会逐行执行，性能较差
使用 WITH 子句：对于复杂查询，使用 WITH 子句提高可读性和性能

示例：

sql

-- 推荐：使用 EXISTS
SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location_id = 1700);

-- 不推荐：使用 IN
SELECT * FROM employees e WHERE e.department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- 推荐：使用 WITH 子句
WITH dept_ids AS (
    SELECT department_id FROM departments WHERE location_id = 1700
)
SELECT * FROM employees e WHERE e.department_id IN (SELECT department_id FROM dept_ids);

4. 优化聚合操作

使用适当的 GROUP BY：只在必要的列上使用 GROUP BY
避免 HAVING 子句：尽可能使用 WHERE 子句代替 HAVING 子句
使用 ROLLUP/CUBE：对于需要多级汇总的查询，使用 ROLLUP 或 CUBE
使用窗口函数：对于复杂的分析查询，使用窗口函数提高性能

示例：

sql

-- 推荐：使用 WHERE 子句过滤数据
SELECT department_id, AVG(salary) AS avg_salary FROM employees WHERE salary > 5000 GROUP BY department_id;

-- 不推荐：使用 HAVING 子句过滤数据
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 5000;

-- 使用窗口函数计算累计和
SELECT
    employee_id
    ,first_name
    ,salary
    ,SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM
    employees;

5. 优化排序操作

避免不必要的排序：只在必要时使用 ORDER BY
使用索引排序：确保 ORDER BY 子句中的列与索引顺序一致
限制排序数据量：使用 ROWNUM 或 FETCH FIRST 限制排序数据量
优化 UNION 操作：如果不需要去重，使用 UNION ALL 代替 UNION

示例：

sql

-- 推荐：使用索引排序
SELECT employee_id, first_name, last_name FROM employees ORDER BY employee_id;

-- 不推荐：不使用索引排序
SELECT employee_id, first_name, last_name FROM employees ORDER BY hire_date;

-- 推荐：使用 FETCH FIRST 限制排序数据量
SELECT employee_id, first_name, last_name FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;

-- 推荐：使用 UNION ALL 代替 UNION（如果不需要去重）
SELECT employee_id, first_name FROM employees WHERE department_id = 10 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 20;

生产环境规范

1. 使用绑定变量

避免硬解析：使用绑定变量代替字面量，减少硬解析
使用游标变量：对于动态 SQL，使用游标变量减少硬解析
设置 cursor_sharing：考虑将 cursor_sharing 设置为 FORCE 或 SIMILAR（但需谨慎）

示例：

sql

-- 推荐：使用绑定变量
DECLARE
    v_dept_id NUMBER := 10;
BEGIN
    FOR rec IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = v_dept_id) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
    END LOOP;
END;
/

-- 不推荐：使用字面量
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10;

2. 限制返回数据量

只查询需要的列：避免使用 SELECT *，只查询必要的列
使用 ROWNUM 或 FETCH FIRST：限制返回的行数
使用分页查询：对于 Web 应用，使用分页查询减少数据传输

示例：

sql

-- 推荐：只查询需要的列
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10;

-- 不推荐：使用 SELECT *
SELECT * FROM employees WHERE department_id = 10;

-- 推荐：使用 FETCH FIRST 限制返回行数
SELECT employee_id, first_name, last_name FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;

-- 分页查询示例
SELECT employee_id, first_name, last_name
FROM (
    SELECT
        employee_id
        ,first_name
        ,last_name
        ,ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
    FROM
        employees
) WHERE rn BETWEEN 11 AND 20;

3. 避免长时间运行的查询

拆分复杂查询：将复杂查询拆分为多个简单查询
使用并行查询：对于大型查询，考虑使用并行查询
设置合理的超时：为查询设置合理的超时时间
监控长查询：定期监控和分析长时间运行的查询

示例：

sql

-- 使用并行查询（适用于大型查询）
SELECT /*+ PARALLEL(4) */ employee_id, first_name, last_name FROM employees;

4. 事务管理规范

短事务原则：事务应尽可能短，减少锁持有时间
避免在事务中执行 DDL：DDL 操作会隐式提交事务
使用显式提交：使用显式的 COMMIT 或 ROLLBACK，避免隐式提交
设置合理的隔离级别：根据业务需求选择适当的隔离级别

示例：

sql

-- 推荐：短事务
BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

19c 和 21c 新特性规范

Oracle 19c 新特性

SQL:1999 语法增强：支持更多 SQL:1999 语法
JSON 支持增强：使用 JSON_PATH 访问 JSON 数据
自动索引：利用自动索引功能优化查询
实时统计信息：使用 DBMS_STATS.GATHER_TABLE_STATS 的 OPTIONS => 'GATHER AUTO' 选项

示例：

sql

-- 使用 JSON_PATH 访问 JSON 数据
SELECT
    json_column.name
    ,json_column.age
FROM
    json_table
WHERE
    json_value(json_column, '$.department') = 'IT';

Oracle 21c 新特性

SQL Macros：使用 SQL Macros 简化复杂查询
JSON 增强：支持 JSON 类型和更多 JSON 函数
批量数据加载：使用 INSERT ALL 或 MERGE 进行批量数据加载
轻量级连接：使用轻量级连接减少连接开销

示例：

sql

-- 使用 SQL Macros
CREATE OR REPLACE FUNCTION get_employees(p_dept_id NUMBER) RETURN VARCHAR2 SQL_MACRO IS
BEGIN
    RETURN 'SELECT * FROM employees WHERE department_id = p_dept_id';
END;
/

-- 使用 SQL Macros 查询
SELECT * FROM get_employees(10);

常见问题 (FAQ)

为什么要使用绑定变量？

使用绑定变量可以减少硬解析，提高系统性能。硬解析是指 Oracle 对每条 SQL 语句进行语法分析、语义分析、生成执行计划等操作，这些操作会消耗大量的 CPU 和内存资源。使用绑定变量可以让 Oracle 重用执行计划，减少硬解析的开销。

为什么要避免使用 SELECT *？

避免使用 SELECT * 的原因包括：

增加网络传输量：查询不必要的列会增加网络传输量
增加 I/O 操作：查询不必要的列会增加 I/O 操作
影响索引使用：如果只查询索引列，Oracle 可以只扫描索引，不访问表
代码可维护性差：如果表结构发生变化，SELECT * 可能导致应用程序错误

如何优化 IN 子查询？

优化 IN 子查询的方法包括：

使用 EXISTS 代替 IN
使用 JOIN 代替 IN 子查询
确保子查询返回的结果集较小
使用 WITH 子句缓存子查询结果

什么时候使用并行查询？

并行查询适用于以下情况：

大型表的全表扫描
大型索引的创建或重建
复杂的聚合查询
数据仓库环境中的大型查询

如何避免死锁？

避免死锁的方法包括：

保持事务简短
以相同的顺序访问资源
使用适当的锁级别
避免长时间持有锁
使用 NOWAIT 或 WAIT 子句

19c 和 21c 在 SQL 书写方面有什么新特性？

Oracle 19c 和 21c 在 SQL 书写方面的新特性包括：

SQL:1999 语法增强
JSON 支持增强
自动索引
实时统计信息
SQL Macros
批量数据加载增强
轻量级连接

总结

良好的 SQL 书写规范对于提高数据库性能、减少系统资源消耗和提高代码可维护性至关重要。DBA 和开发人员应该遵循 SQL 书写规范，包括命名规范、格式规范、性能优化规范和生产环境规范。

在实际工作中，应该根据具体的业务需求和系统环境，选择合适的 SQL 书写方式。同时，应该定期监控和分析 SQL 语句的执行情况，不断优化和改进 SQL 书写规范。

随着 Oracle 版本的升级，新的 SQL 特性和优化技术不断出现，DBA 和开发人员应该及时学习和掌握这些新特性，提高 SQL 编写的效率和质量。

Oracle SQL 书写规范 ​

SQL 书写规范概述 ​

基础书写规范 ​

1. 命名规范 ​

2. 格式规范 ​

3. 注释规范 ​

性能优化规范 ​

1. 避免全表扫描 ​

2. 优化 JOIN 操作 ​

3. 优化子查询 ​

4. 优化聚合操作 ​

5. 优化排序操作 ​

生产环境规范 ​

1. 使用绑定变量 ​

2. 限制返回数据量 ​

3. 避免长时间运行的查询 ​

4. 事务管理规范 ​

19c 和 21c 新特性规范 ​

Oracle 19c 新特性 ​

Oracle 21c 新特性 ​

常见问题 (FAQ) ​

为什么要使用绑定变量？ ​

为什么要避免使用 SELECT *？ ​

如何优化 IN 子查询？ ​

什么时候使用并行查询？ ​

如何避免死锁？ ​

19c 和 21c 在 SQL 书写方面有什么新特性？ ​

总结 ​