Oracle数据库绑定变量特性及应用

作者：ITPUB Kenniu  

【IT168技术文档】

关键词:绑定变量(binding variable),共享池(shared buffer pool), SGA(system global area);

        在开发一个数据库系统前,有谁对Oracle 系统了解很多,尤其是它的特性,好象很少吧;对初学者来讲,这更是不可能的事情;仅仅简单掌握了SQL的写法,就开始了数据库的开发,其结果只能是开发一个没有效率,也没有可扩展的系统;
        因此,我写这个主题也是希望让大家更多地掌握Oracle数据库的特性,从而在架构一个新系统时,能考虑系统的可扩展,可伸缩性,也兼顾系统的效率和稳定;
      
        使用绑定变量是Oracle数据库的特性之一;于是大家要问,为什么使用,怎样使用,它的使用限制条件是什么?我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题;

1. 为什么使用绑定变量?
      这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量;如果想要Oracle 运行减慢,甚至完全终止,那就可以不用绑定变量;
这里举例说明上述问题;
为了查询一个员工代号是123,你可以这样查询:
select * from emp where empno=’123’;
你也可以这样查询:
select * from emp where empno=:empno;

    象我们往常一样,你查询员工’123’一次以后,有可能再也不用;接着你有可能查询员工’456’,然后查询’789’等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询(我们叫它硬编码的查询方法);因此,Oracle每次必须分析,解析,安全检查,        优化等等;

    第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;

    第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低; 而执行绑定变量,提交相同对象的完全相同的查询的用户(这句话,大家听起来比较难理解,随后我会给出详细的解释),一次性使用就可重复使用,其效率不言耳语;  打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源;
        
下面举例子去详细论证上述的问题,不使用绑定变量为生病状况:

  这是一个未使用的绑定变量(吃药前):
set echo on;(把执行结果显示出来)
alter system flush shared_pool;
这条语句是清空共项池,每次都必须使用,确保共享池是空的,以提高执行效率;
set timing on(打开记时器.)

declare
    type rc is ref cursor;
    l_rc rc;
    l_dummy all_objects.object_name%type;
    l_start number default dbms_utility.get_time;
begin
    for i in 1 .. 1000
    loop
        open l_rc for
        'select object_name
           from all_objects
          where object_id = ' || i;
        fetch l_rc into l_dummy;
        close l_rc;
    end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
end;
/
PL/SQL 过程已成功完成。

执行时间:  已用时间:  00: 00: 07.03

这是一个使用的绑定变量(吃药后):
set echo on

alter system flush shared_pool;
declare
    type rc is ref cursor;
    l_rc rc;
    l_dummy all_objects.object_name%type;
    l_start number default dbms_utility.get_time;
begin
    for i in 1 .. 1000
    loop
        open l_rc for
        'select object_name
           from all_objects
          where object_id = :x'
        using i;
        fetch l_rc into l_dummy;
        close l_rc;
    end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
end;
PL/SQL 过程已成功完成。

执行时间:  已用时间:  00: 00: 00.75  
大家自己比较结果,相差就是一个数量级;使用绑定变量不仅仅是运行快,而且允许多个用户同时使用;
上述绑定变量的另一种写法供大家参考;

set echo on

alter system flush shared_pool;

declare
    type rc is ref cursor;
    l_rc rc;
    l_dummy all_objects.object_name%type;
    l_start number default dbms_utility.get_time;
begin
    for i in 1 .. 1000
    loop
        open l_rc for
          select object_name
           from all_objects
          where object_id = I;
        fetch l_rc into l_dummy;
        close l_rc;
    end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
end;

上述的环境是在数据哭Oracle 8.1.7, DB OS: Windows Server 2003, 1G Memory, P4 3.4GHZ CPU; 电脑配置不同,执行的结果是有差异的;

2.怎样使用绑定变量?
下面举例说明:
2.1.让Oracle自己绑定变量(也叫静态绑定变量)

set serverout on;
set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
select count(*) into l_count from table1 where col_1=l_param1 and col_2=l_param2;
dbms_output.put_line(l_count);
end;
/
在上面的情况，Oracle会自己绑定变量，即，如果参数保存在一个数组中，select语句放在一个循环中，
select 语句只会编译一次。

2.2 .动态绑定变量
set serverout on;
set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z ';
Execute Immediate l_sql into l_count using l_param1,l_param2;
dbms_output.put_line(l_count);
end;
/

2.3. dbms_output的绑定变量使用
Set echo on;
Set serveroutput on;
Set timming on;
declare
cursor_id integer;
i number;
xSql Varchar2(200);
xOut varchar2(200);
l_start number default dbms_utility.get_time;
xRow  integer;
Begin
  cursor_id:=DBMS_Sql.open_cursor;
  For i in  1..1000 Loop
     DBMS_Sql.parse(cursor_id,'insert into t values(:username,:user_id,Sysdate)',DBMS_SQL.V7);
     DBMS_Sql.bind_variable(cursor_id,'username','test'||to_char(i));
     DBMS_Sql.bind_variable(cursor_id,'user_id',i);
     xRow:=DBMS_Sql.execute(cursor_id);
     --insert into t values('test'||to_char(i),i,Sysdate);
     --xSql:='insert into t values(:username,:user_id,Sysdate)';
     --execute immediate xSql using 'test'||to_char(i),i;
  End loop;
  DBMS_sql.close_cursor(cursor_id);
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2) ||'seconds...');
  --xOut:=to_char(round((dbms_utility.get_time-l_start)/100,2)) ||'seconds...';
  --xOut:='seconds...';
  --return xout;
end;

这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较;

3.  我怎样知道正在使用绑定变量的方法;
下面举例说明;
创建一个Table;
Create table t (xx int);
执行下面的语句;
Begin
   For I in 1..100 loop
       Execute immediate’insert into t values(‘|| t ||’)’;
   End loop;
end;

现在准备好了脚本,开始创建一个字符串中删除常数的一个函数,它采用的是SQL语句为:
        insert into t values(‘hello’,55);
        insert into t values(‘world’,56);
将其转换为
        insert into t values(‘#’,@);
所有相同的语句很显然是可见的(使用绑定变量);上述两个独特的插入语句经过转换后变成同样的语句; 完成的转换函数为:
           Create or replace function remove_constants(p_query in varchar2) return varchar2 as
  l_query long;
  l_char varchar2(1);
  l_in_quates boolean default false;
begin
  for i in 1..length(p_query)
  loop
     l_char:=substr(p_query,i,1);
     if l_char='''' and l_in_quates then
        l_in_quates:=False;
     elsif l_char='''' and not l_in_quates then
     then
        l_in_quates:=true;
        l_query=:l_query||'#';
     end if
     
     if not l_in_quates then
       l_query=:l_query||l_char;
     end if;
  end loop;
  
  l_query:=tranlate(l_query,'0123456789','@@@@@@@@@');
  for i in 1..8 loop
    l_query:=replace(l_query,lpad('@',10-i,'@'),'@');
    l_query:=replace(l_query,lpad('',10-i,''),'');
   
  end loop;
  return upper(l_query);
end;
/
      
接着我们建立一个临时表去保存V$SQLAREA里的语句,所有 Sql的执行结果都写在这里;
建立临时表;
create global temporary table sql_area_tmp on commit preserve rows as
select sql_text,sql_text sql_text_wo_constants from
v$sqlarea where 1=0;

保存数据到临时表上;
insert into sql_area_tmp(sql_text) select sql_text from v$sqlarea;

对临时表中的数据进行更新;删除掉常数;
            Update sql_area_tmp set SQL_TEXT_WO_CONSTANTS= remove_constants(sql_text);

现在我们要找到哪个糟糕的查询
select SQL_TEXT_WO_CONSTANTS,count(*) from sql_area_tmp
group by SQL_TEXT_WO_CONSTANTS
having count(*)>10
order by 2;

SQL_TEXT_WO_CONSTANTS     count(*)
- - - - - - - - - - - - - - - - - - - - - - - - -    - - - - - - - -
INSERT INTO T VALUES(@)         100

另外, 设定如下参数
Alter session set sql_trace=true;
Alter session set timed_statictics=True;
Alter session set events ‘10046 trace name context forever,level <N>’;
这里的’N’ 表示的是1,4,8,12,详细内容请参考相关文档
Alter session set events ‘10046 trace name context off’;
可以用 TKPROF 工具查看绑顶变量执行的结果,如例子:
declare
    l_number number;
    l_text varchar2(5);
begin
    for i in 1 .. 1000
    loop
        l_number := i;
        l_text := 'test'||to_char(i);
        insert into t values(i,l_text);
     end loop;
    commit;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.10          0          0          0           0
Execute   1009      0.09       0.21          0          4       1035        1009
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1011      0.09       0.31          0          4       1035        1009

4.  绑定变量在应用开发环境下的使用;
4.1 在VB.Net or ASP.NET  and VB中的的使用
建议用Oracleclient DB的连接方法,OleDB不支持;下面是使用

OracleClient连接的使用例子(这个代码执行只需要2秒不到);
Begin
        Dim cn01 As New OracleConnection
        Dim CMD01 As New OracleCommand

        Dim Cmd As New OleDbCommand
        Dim i As Integer
        Try
            <add key="DBCONN_SFCFA"  value="User ID=sfcfa;password=SFCFA;Data Source=CIM;" />
            xConnStr = System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA"
            'cn01.ConnectionString()
            cn01.ConnectionString = xConnStr
            cn01.Open()

            TextBox1.Text = Now
            Application.DoEvents()

            xSql = "insert into t values(:username,:userid,sysdate) "
            For i = 1 To 1000
                CMD01 = New OracleClient.OracleCommand(xSql, cn01)
                CMD01.CommandType = CommandType.Text
                CMD01.Parameters.Add("username", "test" + CStr(i))
                CMD01.Parameters.Add("userid", i)
                CMD01.ExecuteNonQuery()
                CMD01.Parameters.Clear()
            Next i

            TextBox2.Text = Now

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.HelpLink + ex.Message)

        End Try
End.

     OleDB(VB,ASP等)不支持绑定变量,或者我没有找到更好的方法去实现它;它有变量的概念但不支持绑定;网络上,有很多帖子说;他实现了绑定变量用VB or ASP;我按照他们的方法去试,发现他们与单纯传参数没有什么区别,请看下面的内容;


OleDB(这个执行需要5秒 :
        Dim xConnStr, xSql As String
        Dim Cn As New OleDbConnection

        Dim cn01 As New OracleConnection
        Dim CMD01 As New OracleCommand

        Dim Cmd As New OleDbCommand
        Dim i As Integer
        Try
           <add key="DBCONN_SFCFA"  value="Provider=MSDAORA.1;User ID=sfcfa;password=SFCFA;Data Source=CIM;"/>
            xConnStr = System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA"
            'Cn.ConnectionString()
            Cn.ConnectionString = xConnStr
            Cn.Open()

            TextBox1.Text = Now
            Application.DoEvents()

            xSql = "insert into t values(?,?,sysdate) "
            For i = 1 To 1000
                Cmd = New OleDbCommand(xSql, Cn)
                Cmd.CommandType = CommandType.Text
                Cmd.Parameters.Add("username", "test" + CStr(i))
                Cmd.Parameters.Add("userid", i)
                Cmd.ExecuteNonQuery()
                Cmd.Parameters.Clear()
            Next i

            TextBox2.Text = Now

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.HelpLink + ex.Message)

        End Try
     
  VB or ASP(耗时也是5秒左右…):
  
   Private Sub Command1_Click()
    Dim sConn As String
    Dim BVCS_CN As ADODB.Connection
    'Dim BVCS as ADODB.
    Dim xCMD As ADODB.Command
    Dim xPre As ADODB.Parameter
    Dim xSql As String
    Dim xSql01 As String
    Dim xRS As ADODB.Recordset
   
    On Error GoTo 1

    SetDBConnection = True
    Set BVCS_CN = New ADODB.Connection
   
    'BVCS_CN.Provider = "MSDAORA"
    'sConn = "DATA SOURCE=" & ServerName & ";"
    sConn = "Provider=MSDAORA.1;Password=sfcfa;User ID=sfcfa;Data Source=cim;"
   
    With BVCS_CN
        .Open sConn
    End With

   
    If BVCS_CN.State = 0 Then
       MsgBox "DB Connection is error"
       Exit Sub
    End If
   
    Text1.Text = Now
    DoEvents
   
    Set xCMD = New ADODB.Command
   
    Dim xTest As String
   
   
    Set xPre = New ADODB.Parameter
   
    'BVCS_CN
   
    For i = 1 To 1000

       With xCMD

          .ActiveConnection = BVCS_CN

          .CommandText = " Insert into TT(username,userid) values(?,?) "
          .CommandType = adCmdText
          .Parameters.Append .CreateParameter("username", adBSTR, adParamInput, 30, "test" + CStr(i))
          .Parameters.Append .CreateParameter("userid", adInteger, adParamInput, 4, i)
          .Prepared = True
          .Execute
       End With


       xCMD.Parameters.Delete 1
       xCMD.Parameters.Delete 0
      
    Next i
   

   
    Set xCMD = Nothing
   
    Text2.Text = Now
   
    Exit Sub
1:
    Set xCMD = Nothing

     MsgBox Error$
     For Each objErr In BVCS_CN.Errors
        MsgBox objErr.Description
     Next
     BVCS_CN.Errors.Clear
     Exit Sub
     Resume Next
End Sub

4.2 在Delphi中的使用情况;
这里特殊说明, Borland Delphi 4.0以上的版本已经开始完全支持绑定变量的概念,因此,它执行数据库的查询效率要好于其他开发工具;执行的结果不到2秒;
procedure TForm1.Button1Click(Sender: TObject);
Var
   i :Integer;
begin

  edit1.text:=DatetimeToStr(now);
  For i := 1 to 1000 do
  //Begin
      With Query1 do
      Begin
         close;
         Sql.clear;
         Sql.add('Insert into t Values(:username,:user_id,sysdate) ');
         ParamByName('username').AsString :='test' ;
         ParamByName('user_id').AsInteger :=i ;
         execSql;
      End;
  //end;

  //edit2.text:=DateToStr(now);
    edit2.text:=DatetimetoStr(now);
end;  

4.3. 在Java中的使用绑定变量

String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();
在Java中，结合使用setXXX 系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL 语句的性能。

4.4  C#同VB.NET ,这里不在赘述;

5.  绑定变量使用限制条件是什么?
    为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询(这句并不完全可信,有兴趣的可以自己琢磨).

    数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

    当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).
        共享的语句必须满足三个条件:

A.      字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同.
      例如:
          SELECT * FROM EMP;
      和下列每一个都不同
          SELECT * from EMP;
          Select * From Emp;
          SELECT      *     FROM EMP;

B.      两个语句所指的对象必须完全相同:
例如:
   用户                对象名                如何访问
Jack                sal_limit                private synonym
                Work_city                public synonym
                Plant_detail                public synonym

Jill                sal_limit                private synonym
                Work_city                public synonym
                Plant_detail                table owner

    考虑一下下列SQL语句能否在这两个用户之间共享.
   
SQL        能否共享        原因
select max(sal_cap) from sal_limit;        不能        每个用户都有一个private synonym - sal_limit , 它们是不同的对象
select count(*0 from work_city where sdesc like 'NEW%';        能        两个用户访问相同的对象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id        不能        用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.       
  
C.      两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

例如：

第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;

b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;                            


6.  总结
       不使用绑定变量是做着等死,使用绑定变量不一定不会死;没有任何的良药会包治百病,所以在如何合理有效地使用绑定变量仍就需要大家去摸索;

相关文献:
1. [Oracle 高级专家编程] 作者:Thomas Kytes  袁勤勇,张玉魁编译;清华大学出版社;
2. ORACLE SQL性能优化系列  from www.dbasupport.com
3. Oracle 绑定变量的用法   from 王者之剑（ www.albertsong.com）