oracle开发过程中尽量使用绑定变量

在存储过程或者java程序使用sql的过程中，尽量使用绑定变量。否则硬解析太多，比较拖性能。

下面查询只是空格多了几个。

SQL> select * from ml_1234 where a=          2
  2  ;
 
                                      A
---------------------------------------
 
SQL> select * from ml_1234 where a=      2
  2  ;
 

 

查看代码解析

 
SQL> select sql_text from v$sqlarea where sql_text like 'select * from ml_1234 %';
 
SQL_TEXT
--------------------------------------------------------------------------------
select * from ml_1234 where a=1
select * from ml_1234
select * from ml_1234 where a=  1
select * from ml_1234 where a=          2
select * from ml_1234 where a=   2
 
SQL> 

如果使用绑定变量，出来就是一条解析sql， 他们可以共用解析，只走软解析。

SQL> var v1 number
SQL> begin
  2  :v1:=2;
  3  end;
  4  /
 
PL/SQL procedure successfully completed
v1
---------
2
 
SQL> select * from ml_1234 where a=:v1;
 
                                      A
---------------------------------------
v1
---------
2
 
SQL> var v1 number
SQL> begin
  2  :v1:=4;
  3  end;
  4  /
 
PL/SQL procedure successfully completed
v1
---------
4
 
SQL> select * from ml_1234 where a=:v1;
 
                                      A
---------------------------------------
v1
---------
4
 
SQL> select sql_text from v$sqlarea where sql_text like 'select * from ml_1234 %';
 
SQL_TEXT
--------------------------------------------------------------------------------
select * from ml_1234 where a=1
select * from ml_1234
select * from ml_1234 where a=  1
select * from ml_1234 where a=          2
select * from ml_1234 where a=   2
select * from ml_1234 where a=:v1