梁敬彬梁敬弘兄弟出品
往期回顾
Oracle数据库开发规范篇「① 」——SQL编写规范【上】（书写、表名、Select/insert、类型转换、NULL、LIKE)
Oracle数据库开发中，良好的SQL编写习惯不仅能提高代码可读性，更能显著影响系统性能和稳定性。本文介绍SQL编写规范的后半部分，包括绑定变量、动态SQL、SQL嵌套、排序、HINT和并行处理等关键方面。
1.8 绑定变量（适用于OLTP系统）
SQL中常量的直接使用，会导致频繁的硬解析，进而严重影响数据库的性能。
✗ 不良示例（不使用绑定变量）:
-- 每次执行都会进行硬解析，即使只有参数不同
-- 会在共享池中创建多个执行计划
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE IMMEDIATE 'SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = ' || i;
END LOOP;
END;
/
✓ 推荐示例（使用绑定变量）:
-- 使用绑定变量，只进行一次硬解析，之后都是软解析
DECLARE
v_emp_rec EMPLOYEES%ROWTYPE;
v_sql VARCHAR2(200) := 'SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :emp_id';
v_cursor SYS_REFCURSOR;
BEGIN
FOR i IN 1..1000 LOOP
OPEN v_cursor FOR v_sql USING i;
FETCH v_cursor INTO v_emp_rec;
CLOSE v_cursor;
END LOOP;
END;
/
-- 在应用程序中使用绑定变量的JDBC示例
/*
Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = ?");
pstmt.setInt(1, employeeId);
ResultSet rs = pstmt.executeQuery();
*/
1.9 动态SQL
由于动态SQL是在运行时才进行确定的，相对来说会更多的损失一些系统性能来换取灵活性，所以，原则上不允许使用动态SQL。在不得不使用的情况下，必须使用绑定变量。
✗ 不推荐（无绑定变量的动态SQL）:
CREATE OR REPLACE PROCEDURE search_employees(
p_dept_id IN NUMBER DEFAULT NULL,
p_job_id IN VARCHAR2 DEFAULT NULL
) IS
v_sql VARCHAR2(1000);
v_cursor SYS_REFCURSOR;
v_emp_rec EMPLOYEES%ROWTYPE;
BEGIN
v_sql := 'SELECT * FROM EMPLOYEES WHERE 1=1';
-- 构建动态条件，没有使用绑定变量
IF p_dept_id IS NOT NULL THEN
v_sql := v_sql || ' AND DEPARTMENT_ID = ' || p_dept_id;
END IF;
IF p_job_id IS NOT NULL THEN
v_sql := v_sql || ' AND JOB_ID = ''' || p_job_id || '''';
END IF;
OPEN v_cursor FOR v_sql;
LOOP
FETCH v_cursor INTO v_emp_rec;
EXIT WHEN v_cursor%NOTFOUND;
-- 处理记录
END LOOP;
CLOSE v_cursor;
END;
/
✓ 推荐（必须使用的动态SQL加上绑定变量）:
CREATE OR REPLACE PROCEDURE search_employees(
p_dept_id IN NUMBER DEFAULT NULL,
p_job_id IN VARCHAR2 DEFAULT NULL
) IS
v_sql VARCHAR2(1000);
v_cursor SYS_REFCURSOR;
v_emp_rec EMPLOYEES%ROWTYPE;
v_bind_values DBMS_SQL.VARCHAR2_TABLE;
v_bind_names DBMS_SQL.VARCHAR2_TABLE;
v_bind_count NUMBER := 0;
BEGIN
v_sql := 'SELECT * FROM EMPLOYEES WHERE 1=1';
-- 使用绑定变量构建动态条件
IF p_dept_id IS NOT NULL THEN
v_bind_count := v_bind_count + 1;
v_sql := v_sql || ' AND DEPARTMENT_ID = :b' || v_bind_count;
v_bind_names(v_bind_count) := 'b' || v_bind_count;
v_bind_values(v_bind_count) := p_dept_id;
END IF;
IF p_job_id IS NOT NULL THEN
v_bind_count := v_bind_count + 1;
v_sql := v_sql || ' AND JOB_ID = :b' || v_bind_count;
v_bind_names(v_bind_count) := 'b' || v_bind_count;
v_bind_values(v_bind_count) := p_job_id;
END IF;
-- 使用DBMS_SQL动态绑定
DECLARE
v_cursor_id NUMBER;
v_rows_processed NUMBER;
BEGIN
v_cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor_id, v_sql, DBMS_SQL.NATIVE);
-- 绑定所有变量
FOR i IN 1..v_bind_count LOOP
DBMS_SQL.BIND_VARIABLE(v_cursor_id, v_bind_names(i), v_bind_values(i));
END LOOP;
v_rows_processed := DBMS_SQL.EXECUTE(v_cursor_id);
-- 处理结果...
DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;
END;
/
1.10 SQL子查询嵌套不宜超过3层
禁止使用多层的SQL嵌套，除了分页查询，一般SQL语句建议不超过3层嵌套，过于复杂的SQL可根据业务逻辑拆分为多条SQL来实现，或者使用with递归。
✗ 不推荐（过多嵌套）:
SELECT a.column1, a.column2
FROM table_a a
WHERE a.id IN (
SELECT b.a_id
FROM table_b b
WHERE b.status = 'ACTIVE'
AND b.value > (
SELECT AVG(c.value)
FROM table_c c
WHERE c.type IN (
SELECT d.type
FROM table_d d
WHERE d.category = 'PREMIUM'
)
)
);
✓ 推荐（使用WITH子句）:
WITH
premium_types AS (
SELECT type
FROM table_d
WHERE category = 'PREMIUM'
),
avg_values AS (
SELECT AVG(value) AS avg_value
FROM table_c
WHERE type IN (SELECT type FROM premium_types)
),
eligible_b AS (
SELECT a_id
FROM table_b
WHERE status = 'ACTIVE'
AND value > (SELECT avg_value FROM avg_values)
)
SELECT a.column1, a.column2
FROM table_a a
WHERE a.id IN (SELECT a_id FROM eligible_b);
1.11 排序的使用
避免不必要的排序，对查询结果进行的排序会大大降低系统的性能。应将大多数的排序工作交给应用层去完成
1.12 尽量避免HINT在代码中出现
Hint是Oracle提供的一种语法，能够通过它影响SQL的执行方式。但是执行计划的确定应随环境的变化而变化。通过Hint的影响，使执行计划固化下来，有时会导致错误的结果。一般不建议使用。
✗ 不推荐（使用HINT）:
-- 使用FULL提示强制全表扫描
SELECT /*+ FULL(employees) */ employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;
-- 使用INDEX提示强制使用特定索引
SELECT /*+ INDEX(employees EMP_DEPARTMENT_IX) */ *
FROM employees
WHERE department_id = 20;
✓ 推荐（让优化器自己选择）:
-- 让优化器选择最佳路径
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;
-- 确保有适当的索引和统计信息
-- EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
1.13 用到并行度需谨慎
需要明确并行执行是一项比较重要的技术，但它不是在做优化。由于整个系统的资源总量是固定的，那么并行执行的SQL语句不可避免的会对其他语句的性能产生影响，所以并行技术的使用需要考虑很多因素，例如CPU核数、系统负载等等。此项技术常在OLAP系统中使用。
这里尤其需要注意的是，千万别直接对表和索引的属性设置并行度，因为这一设置，会让所有对其的访问都产生并行。
✗ 不要直接对表和索引的属性设置并行度
-- 危险操作：永久修改表的并行度属性
-- 这会导致所有对该表的操作默认尝试使用并行
ALTER TABLE SALES_HISTORY PARALLEL 8; -- 不推荐
-- 危险操作：永久修改索引的并行度属性
-- 会影响索引的所有操作，包括在OLTP环境中的查询
ALTER INDEX SALES_DATE_IDX PARALLEL 4; -- 不推荐
-- 查看表的并行度设置
SELECT table_name, degree
FROM user_tables
WHERE table_name = 'SALES_HISTORY';
-- 查看索引的并行度设置
SELECT index_name, degree
FROM user_indexes
WHERE index_name = 'SALES_DATE_IDX';
✓ 推荐做法：
-- 推荐：仅在特定SQL语句需要并行时使用HINT
SELECT /*+ PARALLEL(4) */ *
FROM SALES_HISTORY
WHERE sale_date BETWEEN TO_DATE('01-JAN-2023') AND TO_DATE('31-DEC-2023');
-- 推荐：需要并行时临时设置会话级别的并行度，使用完后恢复
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION ENABLE PARALLEL QUERY;
-- 执行需要并行的操作
INSERT /*+ APPEND PARALLEL(4) */ INTO SALES_SUMMARY
SELECT /*+ PARALLEL(4) */ product_id, SUM(amount)
FROM SALES_HISTORY
GROUP BY product_id;
-- 操作完成后关闭并行
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL QUERY;
-- 如果表被设置了并行属性，可以重置回默认值
ALTER TABLE SALES_HISTORY NOPARALLEL;
ALTER INDEX SALES_DATE_IDX NOPARALLEL;
总结
本文介绍了Oracle SQL编写的高级规范，包括绑定变量使用、动态SQL构建、SQL嵌套控制、排序优化、HINT使用以及并行处理。正确遵循这些规范，能够显著提高SQL代码的质量和执行效率，减少系统资源消耗，提升整体数据库性能。
在实际开发中，应根据具体业务场景灵活应用这些规范，确保系统稳定高效运行。对于任何例外情况，建议在代码中添加注释说明原因，以便后续维护。
未完待续…
Oracle数据库开发规范篇「③」——PLSQL编写规范【上】（注释、最小化测试案例)
系列回顾
“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列
三分钟讲述个人感悟——感恩，回馈
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
