Oracle資料庫綁定變數特性及應用

        在開發一個資料庫系統前,有誰對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 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.  總結
       不使用綁定變數是做著等死,使用綁定變數不一定不會死;沒有任何的良藥會包治百病,所以在如何合理有效地使用綁定變數仍就需要大家去摸索;