Oracle 开发规范

开发规范概述

Oracle开发规范是确保数据库应用程序质量、性能和可维护性的重要指南。遵循规范可以减少错误、提高开发效率、优化性能并确保代码的一致性。

SQL编写规范

1. 命名规范

表名：使用有意义的名称，采用小写字母和下划线分隔，如 user_info
列名：使用有意义的名称，采用小写字母和下划线分隔，如 user_id、user_name
索引名：使用表名+列名+索引类型的命名方式，如 user_info_user_id_idx
约束名：使用表名+列名+约束类型的命名方式，如 user_info_user_id_pk（主键）、user_info_email_uk（唯一约束）
视图名：使用 v_ 前缀，如 v_user_info
存储过程名：使用 proc_ 前缀，如 proc_get_user_info
函数名：使用 func_ 前缀，如 func_calculate_salary
包名：使用 pkg_ 前缀，如 pkg_user_management

2. 格式化规范

使用缩进（4个空格），提高代码可读性
关键字大写，如 SELECT、FROM、WHERE
每个子句占一行，如 SELECT、FROM、WHERE 分别在不同行
复杂查询使用括号明确优先级
长SQL语句适当换行，保持每行长度不超过120个字符

示例：

sql

-- 推荐
SELECT user_id, user_name, email
FROM user_info
WHERE status = 'ACTIVE'
  AND create_time >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
ORDER BY create_time DESC;

-- 不推荐
select user_id,user_name,email from user_info where status='ACTIVE' and create_time>=to_date('2023-01-01','YYYY-MM-DD') order by create_time desc;

3. 性能优化规范

避免使用 SELECT *，只选择需要的列
合理使用索引，避免全表扫描
避免在WHERE子句中使用函数或表达式，如 WHERE UPPER(name) = 'TEST'
避免使用 NOT IN，考虑使用 NOT EXISTS 或 LEFT JOIN 替代
合理使用绑定变量，避免硬解析
优化JOIN操作，确保连接列有索引
避免使用 ORDER BY RAND() 等性能差的函数

示例：

sql

-- 推荐：使用绑定变量
SELECT * FROM user_info WHERE user_id = :user_id;

-- 不推荐：使用字面量，导致硬解析
SELECT * FROM user_info WHERE user_id = 123;

4. 安全性规范

避免使用动态SQL，如必须使用，确保使用绑定变量防止SQL注入
验证所有用户输入，防止注入攻击
避免在代码中硬编码密码和敏感信息
使用最小权限原则，只授予必要的权限
加密敏感数据，如密码、信用卡号等

示例：

sql

-- 推荐：使用绑定变量防止SQL注入
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name || ' WHERE id = :id' USING id_value;

-- 不推荐：直接拼接字符串，导致SQL注入风险
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name || ' WHERE id = ' || id_value;

PL/SQL规范

1. 基本结构规范

每个PL/SQL块使用 DECLARE、BEGIN、EXCEPTION、END 结构
变量声明在 DECLARE 部分，按类型分组
异常处理在 EXCEPTION 部分，明确处理各种异常情况
每个PL/SQL块有明确的注释，说明其功能和用途

示例：

plsql

PROCEDURE proc_update_user_status (
    p_user_id IN NUMBER,
    p_status IN VARCHAR2
) IS
    v_count NUMBER;
BEGIN
    -- 更新用户状态
    UPDATE user_info
    SET status = p_status,
        update_time = SYSDATE
    WHERE user_id = p_user_id;
    
    -- 检查更新结果
    v_count := SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' rows');
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('User not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END proc_update_user_status;

2. 变量和常量规范

使用有意义的变量名，采用小写字母和下划线分隔
变量类型与列类型保持一致，使用 %TYPE 或 %ROWTYPE 类型
常量使用 CONSTANT 关键字，采用大写字母和下划线分隔
避免使用全局变量，使用参数传递数据

示例：

plsql

DECLARE
    v_user_id     user_info.user_id%TYPE;
    v_user_name   user_info.user_name%TYPE;
    v_user_row    user_info%ROWTYPE;
    c_active_status CONSTANT VARCHAR2(10) := 'ACTIVE';
BEGIN
    -- 代码实现
END;

3. 游标规范

使用显式游标处理多行数据
及时关闭游标，释放资源
考虑使用 FOR 循环游标，自动处理游标打开、关闭和获取
避免在游标循环中执行DML操作，考虑批量处理

示例：

plsql

-- 推荐：使用FOR循环游标
FOR rec IN (
    SELECT user_id, user_name
    FROM user_info
    WHERE status = 'ACTIVE'
) LOOP
    DBMS_OUTPUT.PUT_LINE('User ID: ' || rec.user_id || ', Name: ' || rec.user_name);
END LOOP;

-- 不推荐：手动管理游标
DECLARE
    CURSOR c_user IS
        SELECT user_id, user_name
        FROM user_info
        WHERE status = 'ACTIVE';
    v_user_id     user_info.user_id%TYPE;
    v_user_name   user_info.user_name%TYPE;
BEGIN
    OPEN c_user;
    LOOP
        FETCH c_user INTO v_user_id, v_user_name;
        EXIT WHEN c_user%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('User ID: ' || v_user_id || ', Name: ' || v_user_name);
    END LOOP;
    CLOSE c_user;
END;

4. 异常处理规范

明确处理预期的异常，避免使用 WHEN OTHERS 捕获所有异常
在 WHEN OTHERS 块中记录错误信息并重新抛出异常
使用 SQLCODE 和 SQLERRM 获取错误代码和错误信息
考虑使用自定义异常，提高代码可读性和可维护性

示例：

plsql

DECLARE
    e_user_not_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_user_not_found, -20001);
BEGIN
    -- 代码实现
    
    IF v_count = 0 THEN
        RAISE e_user_not_found;
    END IF;
    
EXCEPTION
    WHEN e_user_not_found THEN
        DBMS_OUTPUT.PUT_LINE('Custom Error: User not found');
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: No data found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected Error: ' || SQLCODE || ' - ' || SQLERRM);
        RAISE;
END;

存储过程和函数规范

1. 参数规范

使用 IN、OUT、IN OUT 明确参数类型
避免使用 IN OUT 参数，优先使用 IN 和 OUT 分离
参数名使用 p_ 前缀，如 p_user_id
为参数提供默认值，提高函数的灵活性

示例：

plsql

FUNCTION func_calculate_salary (
    p_basic_salary IN NUMBER,
    p_bonus_percent IN NUMBER DEFAULT 10,
    p_tax_rate IN NUMBER DEFAULT 20
) RETURN NUMBER IS
    v_salary NUMBER;
BEGIN
    v_salary := p_basic_salary + (p_basic_salary * p_bonus_percent / 100);
    v_salary := v_salary - (v_salary * p_tax_rate / 100);
    RETURN v_salary;
END func_calculate_salary;

2. 性能规范

避免在存储过程中使用 DBMS_OUTPUT.PUT_LINE 进行大量输出
考虑使用批量处理，减少上下文切换
避免在循环中执行SQL语句，使用 FORALL 或 BULK COLLECT 进行批量操作
使用 NOCOPY 提示，提高大型对象参数的传递效率

示例：

plsql

-- 使用BULK COLLECT和FORALL进行批量处理
DECLARE
    TYPE t_user_id_tab IS TABLE OF user_info.user_id%TYPE;
    v_user_ids t_user_id_tab;
BEGIN
    -- 批量获取用户ID
    SELECT user_id
    BULK COLLECT INTO v_user_ids
    FROM user_info
    WHERE status = 'INACTIVE';
    
    -- 批量更新用户状态
    FORALL i IN v_user_ids.FIRST .. v_user_ids.LAST
        UPDATE user_info
        SET status = 'ACTIVE',
            update_time = SYSDATE
        WHERE user_id = v_user_ids(i);
    
    COMMIT;
END;

3. 事务规范

明确事务边界，避免长事务
在存储过程中使用 COMMIT 或 ROLLBACK 时要谨慎，考虑调用者的事务需求
避免在循环中提交事务，减少系统开销
考虑使用自治事务处理日志记录等操作

示例：

plsql

-- 使用自治事务记录日志
PROCEDURE proc_log_operation (
    p_operation IN VARCHAR2,
    p_description IN VARCHAR2
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO operation_log (
        operation,
        description,
        log_time
    ) VALUES (
        p_operation,
        p_description,
        SYSDATE
    );
    
    COMMIT;
END proc_log_operation;

视图和物化视图规范

1. 视图规范

视图名使用 v_ 前缀
视图有明确的注释，说明其用途和数据来源
避免在视图中使用复杂的JOIN和子查询，影响性能
考虑使用物化视图提高查询性能

2. 物化视图规范

物化视图名使用 mv_ 前缀
配置适当的刷新策略（ON DEMAND 或 ON COMMIT）
定期刷新物化视图，确保数据一致性
考虑使用增量刷新，减少刷新时间
为物化视图创建适当的索引，提高查询性能

性能优化规范

1. 索引优化

为频繁查询的列创建索引
考虑使用组合索引，覆盖常用的查询条件
避免过多索引，影响写入性能
定期重建和收集索引统计信息
考虑使用函数索引，优化函数查询

2. 分区优化

对大型表进行分区，提高查询性能
选择合适的分区键（时间、范围、列表等）
考虑使用分区裁剪，减少扫描的数据量
定期维护分区表（合并、拆分、删除等）

3. 执行计划优化

定期分析执行计划，识别性能瓶颈
使用 EXPLAIN PLAN 或 DBMS_XPLAN 查看执行计划
考虑使用提示（HINT）优化执行计划，但要谨慎使用
收集准确的统计信息，确保优化器生成正确的执行计划

4. 内存使用优化

合理使用绑定变量，减少硬解析
优化SQL语句，减少内存使用
考虑使用结果缓存，提高查询性能
监控内存使用情况，避免内存泄漏

19c 与 21c 开发差异

Oracle 19c 特性

支持自动索引管理
支持SQL计划管理
增强了JSON支持
支持分区表在线迁移
增强了PL/SQL的性能

Oracle 21c 特性

支持区块链表
支持私有临时表
增强了JSON支持，如JSON路径表达式
支持SQL宏，简化复杂查询
增强了PL/SQL的安全性

开发工具推荐

SQL Developer：Oracle官方免费工具，支持SQL开发、调试和性能优化
PL/SQL Developer：商业化工具，功能强大，支持PL/SQL开发、调试和性能优化
TOAD：商业化工具，支持数据库开发、管理和性能优化
VS Code：开源编辑器，通过插件支持Oracle开发

最佳实践

遵循命名规范：使用一致的命名规范，提高代码可读性和可维护性
优化SQL语句：编写高效的SQL语句，减少数据库负载
使用绑定变量：避免硬解析，提高系统性能
合理使用索引：为频繁查询的列创建索引，提高查询性能
处理异常情况：明确处理各种异常，提高代码的健壮性
定期审查代码：定期审查和优化代码，提高代码质量
使用版本控制：将数据库代码纳入版本控制，便于管理和追溯
测试代码：编写测试用例，验证代码的正确性和性能
文档化代码：为代码添加适当的注释，提高可维护性
培训和知识共享：定期进行培训和知识共享，提高团队整体水平

结论

Oracle开发规范是确保数据库应用程序质量、性能和可维护性的重要指南。遵循规范可以减少错误、提高开发效率、优化性能并确保代码的一致性。随着Oracle版本的不断更新，开发规范也需要不断演进，适应新的特性和最佳实践。通过制定和遵循统一的开发规范，DBA和开发团队可以更好地协作，开发出高质量、高性能的数据库应用程序。

Oracle 开发规范 ​

开发规范概述 ​

SQL编写规范 ​

1. 命名规范 ​

2. 格式化规范 ​

3. 性能优化规范 ​

4. 安全性规范 ​

PL/SQL规范 ​

1. 基本结构规范 ​

2. 变量和常量规范 ​

3. 游标规范 ​

4. 异常处理规范 ​

存储过程和函数规范 ​

1. 参数规范 ​

2. 性能规范 ​

3. 事务规范 ​

视图和物化视图规范 ​

1. 视图规范 ​

2. 物化视图规范 ​

性能优化规范 ​

1. 索引优化 ​

2. 分区优化 ​

3. 执行计划优化 ​

4. 内存使用优化 ​

19c 与 21c 开发差异 ​

Oracle 19c 特性 ​

Oracle 21c 特性 ​

开发工具推荐 ​

最佳实践 ​

结论 ​

Oracle 开发规范

开发规范概述

SQL编写规范

1. 命名规范

2. 格式化规范

3. 性能优化规范

4. 安全性规范

PL/SQL规范

1. 基本结构规范

2. 变量和常量规范

3. 游标规范

4. 异常处理规范

存储过程和函数规范

1. 参数规范

2. 性能规范

3. 事务规范

视图和物化视图规范

1. 视图规范

2. 物化视图规范

性能优化规范

1. 索引优化

2. 分区优化

3. 执行计划优化

4. 内存使用优化

19c 与 21c 开发差异

Oracle 19c 特性

Oracle 21c 特性

开发工具推荐

最佳实践

结论