Oracle数据库开发规范篇「④」——PLSQL编写规范【中】（绑定变量)

梁敬彬梁敬弘兄弟出品

往期回顾
Oracle数据库开发规范篇「① 」——SQL编写规范【上】（书写、表名、Select/insert、类型转换、NULL、LIKE)
Oracle数据库开发规范篇「②」——SQL编写规范【下】（绑定变量、动态SQL、SQL嵌套、排序、HINT、并行)
Oracle数据库开发规范篇「③」——PLSQL编写规范【上】（注释、最小化测试案例)

2.3 绑定变量

绑定变量(binding variable)是指在sql语句的条件中使用变量而不是常量，比如我们要查询名为Kelson的员工,查询语句可以这样写:

select * from hr_staff where staff_name=’Kelson’;

也可以这样写:

select * from hr_staff where staff_name=:staff_name;

后一种写法就采用了绑定变量。

要理解绑定变量带来的优势，首先要解释下硬解析。所谓硬解析就是当一条SQL语句首次运行时，数据库引擎需要对其进行语法分析，语义识别，跟据统计信息生成最佳的执行计划的过程。

回到刚才的例子，如果我们想继续查询名为”CongHui”的员工信息，如果继续采用第一种写法，则数据库引擎会认为这个是一条全新的语句，因此数据库引擎会重新进行语法分析，语义识别，跟据统计信息生成最佳的执行计划，然后对其执行，即做一次新的硬解析。如果采用了绑定变量，则数据库引擎会任务这是一条已经分析过的语句，会直接读取内存中的执行计划，然后执行，这样就避免了一次硬解析。

下面看一个例子来了解绑定变量的使用带来的性能上的差别：

首先是不使用绑定变量的：

alter system flush shared_pool;
set timing on
begin
for i in 1 .. 1000
loop
execute immediate  'select object_name from dba_objects where object_id= ' || i;
end loop;
end;
/
PL/SQL 过程已成功完成。
执行时间: 已用时间: 00: 00: 07.42

下面是使用绑定变量的情况:

alter system flush shared_pool;
set timing on
begin
for i in 1 .. 1000
loop
execute immediate 'select object_name from dba_objects where object_id=:1' using i;
end loop;
end;
/
PL/SQL 过程已成功完成。
执行时间: 已用时间: 00: 00: 00.14

同样的操作,区别就在与是否使用了绑定变量，执行时间就从7.42秒减少到0.14秒,这个还仅仅是针对单个Session的操作，如果有较多的并发用户，性能提升会更加明显，由此可见，合理的使用绑定变量可以极大地提升系统的性能。

除了有效地降低硬解析以外，绑定变量的使用可以使得Oracle应用程序具有更好的可伸缩性，解决library cache的过度耗用以提高性能。可以在library cache中共享游标，避免硬解析以及与之相关的额外开销，在大批量数据操作时将呈数量级来减少闩锁的使用，避免闩锁的竞争。因此在实际开发过程中合理的使用绑定变量是非常重要的。

2.3.1 相似语句需考虑绑定变量

当然和任何事物一样，绑定变量也有着一定的适用场景，使用不当则会适得其反。

绑定变量比较适合于下列场景：SQL语句重复执行频度高，处理的数据量较少，结果集也相对较小，尤其是使用表上的索引来缩小中间结果集，在这种场景下，解析时间通常会接近或高于执行时间，因此比较适合使用绑定变量。
但是在某些环境尤其是数据仓库环境下，SQL语句执行时间远高于其解析时间，可能的情况有SQL语句执行次数较少,SQL重复频次小，返回的数据量大，全表扫描使用较多等。在这些环境下，使用绑定变量对于总响应时间影响不大。
且使用绑定变量时，查询优化器会忽略其具体值，因此其预估的准确性远不如使用字面量值真实，尤其是在表存在数据倾斜(表上的数据非均匀分布)的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。
一旦这种低效的执行计划在大数据环境情况下被执行，会大量的占用系统资源，从而有可能极大地降低数据库的整体性能。
因此绑定变量的使用一定需要考虑其适应的场景，这样才能最大限度的发挥它的作用。

2.3.2 动态SQL最容易遗忘绑定变量

首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

在实际开发过程中，对静态SQL采用绑定变量比较简单，大家也往往会记得。但是经常会忽略对动态SQL采用绑定变量，下面是一个对动态SQL使用绑定变量的简单例子，希望大家可以举一反三，更加深入的理解下这个问题。

declare i int; 
str_obj_name varchar2(100);
begin
select min(object_id) into i from dba_objects;
--下面这句是静态SQL绑定变量
select object_name into str_obj_name from dba_objects where object_id=i ;
--下面这句就是动态SQL通过using使用了绑定变量
execute immediate 'select object_name from dba_objects where object_id=:1' 
into str_obj_name  
using i;
end;
/

未完待续…
Oracle数据库开发规范篇「⑤」【开发篇完结】——PLSQL编写规范【下】（批量提交、封装、包、动态SQL)

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列

三分钟讲述个人感悟——感恩，回馈