为何在Oracle中使用动态SQL？两种写法的差异及优势疑问

一、动态SQL的核心使用场景 #

动态SQL的价值在于应对静态SQL无法处理的灵活场景，主要包括：

• 查询结构不确定：比如业务需要根据用户选择的不同筛选条件，动态拼接WHERE子句；或者需要动态切换查询的表、返回的列
• 执行DDL语句：Oracle静态SQL不允许在存储过程中直接执行CREATE TABLE、ALTER TABLE、DROP INDEX这类DDL操作，必须通过动态SQL的EXECUTE IMMEDIATE来执行
• 动态调用PL/SQL逻辑：比如需要根据参数动态调用不同的存储过程、函数，或者构造动态的逻辑块
• 对象名动态变化：比如需要遍历多个表进行统计，表名是变量而非固定值，静态SQL无法识别变量形式的对象名，只能用动态SQL拼接实现

二、两种写法的核心区别 #

先明确两种写法的本质：

写法1（动态SQL）： #

sql_1 := 'select count(1)
                 from table_1 a
                 where a.col_id = '''|| v_1 ||'''
                 and a.col2 like ''%'|| v_2 ||''';
execute immediate sql_1
        into v_new;

写法2（静态SQL）： #

select count(1)
        into v_new
from table_1 a
where a.col_id = '''|| v_1 ||'''
and a.col2 like ''%'|| v_2 ||''';

两者的核心差异：

• 编译时机：静态SQL在存储过程编译阶段就会完成解析、生成执行计划；动态SQL则是在存储过程运行时，才会解析拼接后的SQL字符串并生成执行计划
• 结构灵活性：静态SQL的表名、列名、基本查询结构必须在编译时完全确定，不能用变量替换；动态SQL可以通过字符串拼接，随时调整这些核心元素
• 变量处理逻辑：你的示例中两种写法都用了字符串拼接变量，这都存在SQL注入风险，但动态SQL可以改用绑定变量（如USING子句）优化，而静态SQL的拼接本质是编译时将变量值硬编码到SQL中，每次不同值都会生成新的SQL语句

三、第一种写法（动态SQL）的优势 #

在你的示例场景中，两者看似效果一致，但动态SQL的优势体现在扩展性和特殊场景适配：

• 适配动态变化的需求：如果后续业务需要调整查询逻辑——比如根据v_1的值切换查询表，或者动态添加/移除WHERE条件，静态SQL无法直接修改，必须重新编译存储过程；而动态SQL只需要调整字符串拼接逻辑即可
• 支持DDL与复杂PL/SQL操作：如果你的存储过程后续需要添加创建临时表、修改字段类型这类操作，静态SQL完全无法实现，必须依赖动态SQL
• 处理动态对象名：如果table_1是一个变量（比如v_table_name），静态SQL会直接把变量名当成表名报错，而动态SQL可以通过'select count(1) from '||v_table_name||' a where ...'的方式实现动态表查询
• 更灵活的执行计划控制：虽然静态SQL的执行计划会被缓存，但动态SQL可以通过DBMS_SQL包精细控制执行流程，或者在数据分布变化较大时强制重新生成执行计划，避免执行计划失效

注意：你的示例中两种写法都存在SQL注入风险，动态SQL的正确写法应使用绑定变量，比如：

sql_1 := 'select count(1)
                 from table_1 a
                 where a.col_id = :1
                 and a.col2 like ''%''||:2';
execute immediate sql_1
        into v_new
        using v_1, v_2;

这种写法既避免了SQL注入，又能让Oracle共享执行计划，提升性能。

内容的提问来源于stack exchange，提问作者LSY FDC