用Oracle表函式解決繫結變數集合問題

宣告：該方法是受到工作中同事案例啟發，在此感謝。

 

繫結變數是我們經常使用的PL/SQL編碼技術。透過在SQL語句中加入繫結變數，可以提高SQL語句共享程度，降低硬解析。

 

繫結變數是一種佔位符技術，透過指定位置的替換，實現語句最佳化。如下示例：

 

vc_sql := 'select count(*) from emp where empno=:1';

 

execute immediate vc_sql into n_count  using 7844;

 

 

但是，如果我們在語句中使用in等集合運算子，括號中使用繫結變數是有些困難的。

 

vc_sql := 'select count(*) from emp where ename in (:1)';

 

//雖然不會報錯，但是不會查詢出正確的結果；

execute immediate vc_sql into n_count using 'Tom, FORD';

 

 

解決：可以藉助表函式table的特性，將字串轉化為記憶體中的虛擬表。將in集合轉化為子查詢。

 

Table函式的作用是在PL/SQL中，將一個REF CURSOR或者集合轉化為資料集。使用巢狀表table的時候，存在具體化的問題。在9i之後，引入了管道函式pipelined，簡化了資料返回操作和效率。

 

首先，需要構建物件和物件集合。

 

//構建每個單元中的物件型別，與查詢in的列型別一致；

//此外，此處要建立object物件，不要建立record。

create or replace type t_rec_test as object(id number);

 

//物件集合

create or replace type t_rec_table as table of t_rec_test;

 

其次，定義函式，實現字串到物件列表的轉換。

 

//實現字串到物件列表的轉換，使用i_vc_dil作為切分

create or replace function F_SPILE(i_vc_str varchar2, i_vc_dil varchar2)

return t_rec_table pipelined //指定返回列表型別外，使用pipelined關鍵字；

is

  dil varchar2(10);

  l_string varchar2(100);

  l_num number;

 begin

  dil := i_vc_dil;

  if (dil is null) then

      dil := ',';

  end if;

 

  l_string := i_vc_str;

 

  LOOP

    l_num := instr(l_string,dil);

EXIT WHEN(nvl(l_num,0) = 0);   

 

    //使用pipe row，將列表中的一個物件返回

    pipe row(t_rec_test(to_number(substr(l_string,1,l_num - 1))));   

    l_string := substr(l_string,l_num + length(dil));    

  END LOOP; 

 

  pipe row(t_rec_test(to_number(l_string)));

 

  return; //使用pipelined關鍵字宣告的函式，最後返回空即可；

end F_SPILE;

 

說明：使用pipelined關鍵字之後，其中的物件列表t_rec_table無須專門定義一個例項。只要每次將分析建立好的物件pipe出就可以了。

 

定義函式F_SPILE之後，就可以實現將字串轉化為物件列表。

 

最後，呼叫方法，使用table函式呼叫分割函式，作為一個獨立的資料集合進行組織。

 

declare 

  vc_sql varchar2(1000);

  n_count number;

begin

  //SQL語句呼叫書寫，先函式處理，再用table函式包裹為資料表；

vc_sql := 'select count(*) from emp where empno in (select * from table(f_spile(:1, '','')))'; 

 

execute immediate vc_sql into n_count using '434,7782,7788,7900';                   

  dbms_output.put_line(to_char(n_count));

end;

/

//返回結果

SQL>

 

3

 

PL/SQL procedure successfully completed

 

 

那麼，這樣的處理方法效率如何？table函式相當於構建了一張記憶體資料表，相對於其他型別的資料表，訪問和讀寫效率是相當高的。所以，及時使用了函式處理以及in操作，這種方法的效率也是相當高的。

 

 

另一個問題是pipelined關鍵字和pipe管道函式。從資料上看，引入這個特定的目的更多是從效能和寫法上的最佳化，能夠更快的將資料集返回。同樣的例子，如果我們不使用pipelined，函式也是可以實現，如下：

 

//非pipeline版本

create or replace function F_SPILE(i_vc_str varchar2, i_vc_dil varchar2)

return t_rec_table //無pipelined關鍵字

is

  dil varchar2(10);

  l_string varchar2(100);

  l_num number;

  Result t_rec_table; //顯示定義結果集

begin

  dil := i_vc_dil;

  if (dil is null) then

      dil := ',';

  end if;

 

  l_string := i_vc_str;

  Result := t_rec_table(); //初始化結果集

 

  LOOP

    l_num := instr(l_string,dil);

    EXIT WHEN(nvl(l_num,0) = 0); 

    //手工的擴充管理和索引腳標管理

Result.EXTEND;   

Result(Result.COUNT) := t_rec_test(to_number(substr(l_string,1,l_num - 1)));

 

    l_string := substr(l_string,l_num + length(dil));

    

  END LOOP;

 

  Result.EXTEND;   

  Result(Result.COUNT) := t_rec_test(l_string);

 

  return(Result);

 

end F_SPILE;

 

沒有使用Pipelined，我們的功能同樣可以實現。只是寫法上要更加複雜一些，效能上在大資料量時稍差。