Oracle动态sql绑定变量提示

在 Oracle 中不使用绑定变量的危险

许多应用程序中最大的问题是不使用绑定变量。Oracle 绑定变量是使 Oracle SQL 可重入的一个非常重要的方法。

为什么使用绑定变量是个问题?

Oracle 使用签名生成算法根据 SQL 语句中的字符为每个 SQL 语句分配一个哈希值。语句中的任何更改(一般而言)都将导致新的哈希值，因此 Oracle 假定它是一个新语句。每个新语句都必须经过验证、解析并生成和存储执行计划，这些都是高开销的过程。

Ad-hoc 查询生成器(Crystal Reports、Discoverer、Business Objects)不使用绑定变量，这是 Oracle 开发cursor_sharing参数以强制 SQL 使用绑定变量(当cursor_sharing=force时)的主要原因。

绑定变量和共享池使用

使用绑定变量会对共享池中的压力产生巨大影响，了解如何在 Oracle 中定位类似的 SQL非常重要。 此脚本 显示如何检查共享池中使用绑定变量的 SQL。下面是使用绑定变量且 SQL 是完全可重入的数据库的示例输出：

时间：03:15 PM 绑定变量利用率 PERFSTAT

当 SQL 放在 PL/SQL 中时，嵌入式 SQL 永远不会改变，并且会维护和搜索单个库缓存条目，大大提高了库缓存命中率并减少了解析开销。

以下是在 Oracle 存储过程和包中放置 SQL 的一些特别值得注意的优点：

高生产力： PL/SQL 是所有 Oracle 环境通用的语言。当应用程序设计为使用 PL/SQL 过程和包时，开发人员的工作效率会提高，因为它避免了重写代码的需要。此外，迁移到不同编程环境和前端工具的复杂性将大大降低，因为 Oracle 流程逻辑代码与数据所在的数据库一起维护在数据库内部。应用程序代码变成一个简单的“外壳”，由对存储过程和函数的调用组成。

改进的安全性： 利用“grant execute”结构，可以限制对 Oracle 的访问，使用户只能运行过程中的命令。例如，它允许最终用户访问在一个特定表中具有删除命令的过程，而不是直接将删除权限授予最终用户。数据库的安全性进一步提高，因为您可以定义哪些变量、过程和游标是公共的，哪些是私有的，从而完全限制对 PL/SQL 包内的这些对象的访问。使用“授权”安全模型，SQL*Plus 等后门可能会导致问题;使用“授权执行”，您可以强制最终用户按照您的规则行事。

应用程序可移植性： 每个用 PL/SQL 编写的应用程序都可以转移到安装了 Oracle 数据库的任何其他环境，而不管平台如何。没有任何嵌入式 PL/SQL 或 SQL 的系统成为“数据库不可知论者”，并且可以移动到其他平台而无需更改任何一行代码。

代码封装：将所有相关的存储过程和函数放入包中，可以将存储过程、变量和数据类型封装在数据库中的一个程序单元中，使包非常适合应用程序中的代码组织。

全局变量和游标： 包可以具有可供包内所有过程和函数使用的全局变量和游标。

绑定变量在动态 SQL 处理中的内存管理和性能增强中起着至关重要的作用。当使用不同的硬编码值多次执行 SQL 查询时，它会根据唯一的硬编码值每次解析一次，如下所示，

1. 从员工中删除employee_id=100;

2. DELETE FROM 员工 WHERE employee_id=101;

3. DELETE FROM 员工 WHERE employee_id=102;

通过执行带有适当列的 V$SQL 视图，可以收集与上述语句相关的解析信息，如下所示，

选择 sql_id，

sql_text,

first_load_time,

哈希值

从 v$sql

WHERE sql_text LIKE '从员工中删除 WHERE employee_id=%';

脚本输出：

在上述结果集中，所有三个查询都使用不同的哈希值进行解析。

但是，当查询语句使用绑定变量而不是硬编码值时，查询不会解析绑定变量的多个值，而只会解析一次，而与执行次数无关。

从员工那里删除employee_id =：employee_id；

现在，下面的 V$SQL 查询结果为

选择 sql_id，
  sql_text,
  first_load_time,
  哈希值
从 v$sql
WHERE sql_text LIKE '从员工中删除 WHERE employee_id=%';

脚本输出：

通过检查上面的结果集，它表明只有一个查询实例被解析，而与语句执行的次数无关。这种行为背后的原因是，查询的解析发生在绑定值分配之前，因此，查询对于多个绑定值是通用的。未能使用绑定变量可能会用大量相同的查询填满共享池空间，从而导致性能下降和资源限制。

在下面的匿名块中，通过在运行时动态分配表名和列名，从员工表中删除员工 ID 100 到 105。

注意：绑定值不能用于传递模式对象。

1. 设置SERVEROUTPUT ON 200000;

2. 声明

3. l_vc_table_name VARCHAR2(30):='Employees';

4. l_vc_column_name VARCHAR2(30):='Employee_id';

5. 开始

6. 为我在 100..105

7. 循环

8. EXECUTE immediate 'delete from '||l_vc_table_name||' 其中'||l_vc_column_name||'='||i;

9. 结束循环i;

10. 结束;

11. /

脚本说明

在这里，如上述情况所述，上述匿名块可以通过使用绑定值进行有效修改，如下所示，

1. 设置SERVEROUTPUT ON 200000;

2. 声明

3. l_vc_table_name VARCHAR2(30):='Employees';

4. l_vc_column_name VARCHAR2(30):='Employee_id';

5. 开始

6. 为我在 100..105

7. 循环

8. EXECUTE immediate 'delete from '||l_vc_table_name||' 其中 '||l_vc_column_name||'=:i' 使用 i;

9. 结束循环i;

10. 结束;

11. /

脚本说明