使用綁定變量帶來的好處不用多說，但是遇到SQL條件中條件值不固定的這類SQL在普通方式下就不能使用綁定變量，看到Tom網站上提供了一種可行的辦法。
網上有不少的地方也提到了這種方法，那就是，先定義一個表類型，将個數不定的參數轉換成這種表類型，再将查詢結果傳給IN子句，這樣就可以實作了SQL語句中的綁定變量方式。
例如：
Select * From 病人費用記錄 Where NO IN(Select * From Table(Cast(zlTools.f_Str2List ('A01,A02,A03') as zlTools. t_StrList)));
Select * From 病人費用記錄 Where NO IN(Select * From The(Select Cast(zlTools.f_Str2List ('A01,A02,A03') as zlTools. t_StrList) From Dual));
Select A.* From 病人費用記錄 A,Table(Cast(zlTools.f_Str2List ('A01,A02,A03') as zlTools. t_StrList)) B Where A.NO=B.Column_Value;
Oracle提供了一個包dbms_utility.comma_to_table可以将參數清單轉換為一個類型表，但是由于結果是通過參數傳出的,無法用于應用程式的SQL語句中。
于是,我們仍然選擇通過建立SQL表類型和相關函數來實作,Type：t_StrList, t_NumList， 函數：f_Str2List, f_Num2List，如下:
Create Or Replace type zltools.t_StrList as table of varchar2(4000)
/
Create Or Replace Function zltools.f_Str2List(Str_In In Varchar2) Return zltools.t_StrList As
v_Str Long Default Str_In || ',';
v_Index Number;
v_List zltools.t_StrList := zltools.t_StrList();
--功能：将由逗号分隔的不帶引号的字元序列轉換為資料表
--參數：Str_In如:G0000123,G0000124,G0000125...
Begin
Loop
v_Index := Instr(v_Str, ',');
Exit When(Nvl(v_Index, 0) = 0);
v_List.Extend;
v_List(v_List.Count) := Trim(Substr(v_Str, 1, v_Index - 1));
v_Str := Substr(v_Str, v_Index + 1);
End Loop;
Return v_List;
End;
/
Create Or Replace type zltools.t_NumList as table of number
/
Create Or Replace Function zltools.f_Num2List(Str_In In Varchar2) Return zltools.t_NumList As
v_Str Long Default Str_In || ',';
v_Index Number;
v_List zltools.t_NumList := zltools.t_NumList();
--功能：将由逗号分隔的數字序列轉換為資料表
--參數：Str_In如:73265,73266,73267....
Begin
Loop
v_Index := Instr(v_Str, ',');
Exit When(Nvl(v_Index, 0) = 0);
v_List.Extend;
v_List(v_List.Count) := To_Number(Trim(Substr(v_Str, 1, v_Index - 1)));
v_Str := Substr(v_Str, v_Index + 1);
End Loop;
Return v_List;
End;
/
但是這種方法有一個問題：
如果使用者使用CBO,使用這種方式的SQL語句，不加處理的話一般(要根據具體SQL分析)會錯誤的使用Hash連接配接來産生執行計劃，不能正确使用索引進而引發性能問題，
因為Type Table不具備統計資訊，在計算成本時，Oracle始終認為它有8168行，在基于成本的優化器模式下，多表連接配接時一般就會選擇Hash連接配接，一般比使用索引的嵌套連接配接效率差。
目前，已知的解決辦法有兩種：
http://www.cnoug.org/viewthread.php?tid=1533&highlight=DROP%2B%2BCONSTRAINT%2Bindex
1. 對構造表的傳回記錄集加RowNum條件，通過子查詢的實體化視圖來改變執行計劃，例如：
select * from table(Cast(zlTools.f_Str2List (v_str) as zlTools.t_StrList)) where rownum<5
2. 在SQL中加入優化器提示，例如：/*+ rule*/，或者/*+ ordered use_nl(t)*/，或者/*+ index(t ind_object_id)*/
第1種方式，需要對rownum的條件指派，如果條件值采用動态的值，那麼就達不到使用綁定變量的效果，如果采用固定值，有兩種方案：
一種是使用一個固定的極限值，例如：99999，另一種是使用綁定變量。
試驗結果見文末，這兩種方式産生的執行計劃采用了Hash連接配接，執行計劃比不使用RowNum實體化視圖更差。
第2種方式，如果使用指定嵌套連接配接提示或指定索引提示則需要對每個SQL針對性處理，需要分析具體的SQL。是以，一般情況建議在SQL中使用/*+ rule*/提示來固定執行計劃。
--0.準備
--先建立表類型及轉換函數: t_StrList,f_Str2List和t_NumList,f_Num2List
SQL> alter session set optimizer_mode=all_rows;
Session altered
SQL> var v_str varchar2(4000);
SQL> exec :v_str :='H0000005,H0000006,H0000007';
PL/SQL 過程已成功完成。
--1.當類型表不使用Rownum實體化視圖時，錯誤地采用了Hash連接配接的執行計劃，産生了6575塊邏輯讀+6909塊實體讀
SQL> Select a.No, a.結算方式, Nvl(b.性質, 1) As 性質, a.金額, a.摘要, a.結算号碼
2 From (Select b.No, Decode(a.記錄性質, 3, a.結算方式, Null) As 結算方式, a.摘要, a.結算号碼, Sum
(1 * a.沖預交) As 金額
3 From 病人預交記錄 a,
4 (Select Distinct No, 結帳id
5 From 病人費用記錄
6 Where 記錄性質 = 1 And No In (
7 Select * From Table(Cast(f_Str2List(:v_str) as t_StrList))
8 ) And 記錄狀态 In (1, 3)) b
9 Where a.結帳id = b.結帳id And a.記錄性質 In (1, 11, 3) And Nvl(a.沖預交, 0) <> 0
10 Group By b.No, Decode(a.記錄性質, 3, a.結算方式, Null), a.摘要, a.結算号碼) a, 結算方式 b
11 Where a.結算方式 = b.名稱(+);
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=23 Card=1 Bytes=100)
1 0 NESTED LOOPS (OUTER) (Cost=23 Card=1 Bytes=100)
2 1 VIEW (Cost=22 Card=1 Bytes=75)
3 2 SORT (GROUP BY) (Cost=22 Card=1 Bytes=94)
4 3 VIEW (Cost=21 Card=1 Bytes=94)
5 4 SORT (UNIQUE) (Cost=21 Card=1 Bytes=149)
6 5 TABLE ACCESS (BY INDEX ROWID) OF '病人預交記錄' (Cost=1 Card=1 Bytes=102)
7 6 NESTED LOOPS (Cost=19 Card=1 Bytes=149)
8 7 HASH JOIN (SEMI) (Cost=17 Card=4 Bytes=188)
9 8 INDEX (FAST FULL SCAN) OF '病人費用記錄_UQ_NO' (UNIQUE) (Cost=4 Card=399 Bytes=17955)
10 8 COLLECTION ITERATOR (PICKLER FETCH) OF 'f_Str2List'
11 7 INDEX (RANGE SCAN) OF '病人預交記錄_IX_結帳ID' (NON-UNIQUE)
12 1 TABLE ACCESS (BY INDEX ROWID) OF '結算方式' (Cost=1 Card=1 Bytes=25)
13 12 INDEX (UNIQUE SCAN) OF '結算方式_UQ_名稱' (UNIQUE)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
6575 consistent gets
6909 physical reads
0 redo size
720 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
--2.當類型表的查詢條件中Rownum使用固定值時,正确地采用了嵌套連接配接的執行計劃，僅僅産生了29塊邏輯讀
SQL> Select a.No, a.結算方式, Nvl(b.性質, 1) As 性質, a.金額, a.摘要, a.結算号碼
2 From (Select b.No, Decode(a.記錄性質, 3, a.結算方式, Null) As 結算方式, a.摘要, a.結算号碼, Sum
(1 * a.沖預交) As 金額
3 From 病人預交記錄 a,
4 (Select Distinct No, 結帳id
5 From 病人費用記錄
6 Where 記錄性質 = 1 And No In (
7 Select * From Table(Cast(f_Str2List(:v_str) as t_StrList)) Where Rownum<4
8 ) And 記錄狀态 In (1, 3)) b
9 Where a.結帳id = b.結帳id And a.記錄性質 In (1, 11, 3) And Nvl(a.沖預交, 0) <> 0
10 Group By b.No, Decode(a.記錄性質, 3, a.結算方式, Null), a.摘要, a.結算号碼) a, 結算方式 b
11 Where a.結算方式 = b.名稱(+);
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=23 Card=4 Bytes=400)
1 0 NESTED LOOPS (OUTER) (Cost=23 Card=4 Bytes=400)
2 1 VIEW (Cost=21 Card=4 Bytes=300)
3 2 SORT (GROUP BY) (Cost=21 Card=4 Bytes=376)
4 3 VIEW (Cost=20 Card=4 Bytes=376)
5 4 SORT (UNIQUE) (Cost=20 Card=4 Bytes=8596)
6 5 TABLE ACCESS (BY INDEX ROWID) OF '病人預交記錄' (Cost=1 Card=1 Bytes=102)
7 6 NESTED LOOPS (Cost=17 Card=4 Bytes=8596)
8 7 NESTED LOOPS (Cost=13 Card=12 Bytes=24564)
9 8 VIEW OF 'VW_NSO_1' (Cost=11 Card=3 Bytes=6006)
10 9 COUNT (STOPKEY)
11 10 COLLECTION ITERATOR (PICKLER FETCH) OF 'f_Str2List'
12 8 INLIST ITERATOR
13 12 INDEX (RANGE SCAN) OF '病人費用記錄_UQ_NO' (UNIQUE) (Cost=1 Card=4 Bytes=180)
14 7 INDEX (RANGE SCAN) OF '病人預交記錄_IX_結帳ID' (NON-UNIQUE)
15 1 TABLE ACCESS (BY INDEX ROWID) OF '結算方式' (Cost=1 Card=1 Bytes=25)
16 15 INDEX (UNIQUE SCAN) OF '結算方式_UQ_名稱' (UNIQUE)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
--3.當類型表的查詢條件中Rownum使用變量時，執行計劃雖然有變化，但是仍然認為類型表傳回的記錄集數為8168,
-- 錯誤地采用了Hash連接配接的執行計劃，産生了374369塊邏輯讀和11192塊實體讀
SQL> var v_count number;
SQL> exec :v_count:=4;
PL/SQL 過程已成功完成。
SQL> Select a.No, a.結算方式, Nvl(b.性質, 1) As 性質, a.金額, a.摘要, a.結算号碼
2 From (Select b.No, Decode(a.記錄性質, 3, a.結算方式, Null) As 結算方式, a.摘要, a.結算号碼, Sum
(1 * a.沖預交) As 金額
3 From 病人預交記錄 a,
4 (Select Distinct No, 結帳id
5 From 病人費用記錄
6 Where 記錄性質 = 1 And No In (
7 Select * From Table(Cast(f_Str2List(:v_str) as t_StrList)) Where Rownum<:v_count
8 ) And 記錄狀态 In (1, 3)) b
9 Where a.結帳id = b.結帳id And a.記錄性質 In (1, 11, 3) And Nvl(a.沖預交, 0) <> 0
10 Group By b.No, Decode(a.記錄性質, 3, a.結算方式, Null), a.摘要, a.結算号碼) a, 結算方式 b
11 Where a.結算方式 = b.名稱(+);
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16306 Card=10945 Bytes=1094500)
1 0 HASH JOIN (OUTER) (Cost=16306 Card=10945 Bytes=1094500)
2 1 VIEW (Cost=16264 Card=10945 Bytes=820875)
3 2 SORT (GROUP BY) (Cost=16264 Card=10945 Bytes=1028830)
4 3 VIEW (Cost=16148 Card=10945 Bytes=1028830)
5 4 SORT (UNIQUE) (Cost=16148 Card=10945 Bytes=23520805)
6 5 HASH JOIN (Cost=238 Card=10945 Bytes=23520805)
7 6 TABLE ACCESS (BY INDEX ROWID) OF '病人預交記錄' (Cost=1 Card=1 Bytes=102)
8 7 NESTED LOOPS (Cost=164 Card=134 Bytes=19698)
9 8 INDEX (FAST FULL SCAN) OF '病人費用記錄_UQ_NO' (UNIQUE) (Cost=4 Card=399 Bytes=17955)
10 8 INDEX (RANGE SCAN) OF '病人預交記錄_IX_結帳ID' (NON-UNIQUE)
11 6 VIEW OF 'VW_NSO_1' (Cost=11 Card=8168 Bytes=16352336)
12 11 COUNT (STOPKEY)
13 12 COLLECTION ITERATOR (PICKLER FETCH) OF 'f_Str2List'
14 1 TABLE ACCESS (FULL) OF '結算方式' (Cost=2 Card=82 Bytes=2050)
統計資訊
----------------------------------------------------------
28 recursive calls
0 db block gets
374369 consistent gets
11192 physical reads
0 redo size
720 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
--4.當類型表的查詢條件中Rownum使用一個固定的極限值時(例如：99999)，情況與使用變量一樣遭，錯誤地采用了Hash連接配接的執行計劃，産生了374354塊邏輯讀和10756塊實體讀
SQL> Select a.No, a.結算方式, Nvl(b.性質, 1) As 性質, a.金額, a.摘要, a.結算号碼
2 From (Select b.No, Decode(a.記錄性質, 3, a.結算方式, Null) As 結算方式, a.摘要, a.結算号碼, Sum
(1 * a.沖預交) As 金額
3 From 病人預交記錄 a,
4 (Select Distinct No, 結帳id
5 From 病人費用記錄
6 Where 記錄性質 = 1 And No In (
7 Select * From Table(Cast(f_Str2List(:v_str) as t_StrList)) Where Rownum<99999
8 ) And 記錄狀态 In (1, 3)) b
9 Where a.結帳id = b.結帳id And a.記錄性質 In (1, 11, 3) And Nvl(a.沖預交, 0) <> 0
10 Group By b.No, Decode(a.記錄性質, 3, a.結算方式, Null), a.摘要, a.結算号碼) a, 結算方式 b
11 Where a.結算方式 = b.名稱(+);
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16306 Card=10945 Bytes=1094500)
1 0 HASH JOIN (OUTER) (Cost=16306 Card=10945 Bytes=1094500)
2 1 VIEW (Cost=16264 Card=10945 Bytes=820875)
3 2 SORT (GROUP BY) (Cost=16264 Card=10945 Bytes=1028830)
4 3 VIEW (Cost=16148 Card=10945 Bytes=1028830)
5 4 SORT (UNIQUE) (Cost=16148 Card=10945 Bytes=23520805)
6 5 HASH JOIN (Cost=238 Card=10945 Bytes=23520805)
7 6 TABLE ACCESS (BY INDEX ROWID) OF '病人預交記錄' (Cost=1 Card=1 Bytes=102)
8 7 NESTED LOOPS (Cost=164 Card=134 Bytes=19698)
9 8 INDEX (FAST FULL SCAN) OF '病人費用記錄_UQ_NO' (UNIQUE) (Cost=4 Card=399 Bytes=17955)
10 8 INDEX (RANGE SCAN) OF '病人預交記錄_IX_結帳ID' (NON-UNIQUE)
11 6 VIEW OF 'VW_NSO_1' (Cost=11 Card=8168 Bytes=16352336)
12 11 COUNT (STOPKEY)
13 12 COLLECTION ITERATOR (PICKLER FETCH) OF 'f_Str2List'
14 1 TABLE ACCESS (FULL) OF '結算方式' (Cost=2 Card=82 Bytes=2050)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
374354 consistent gets
10756 physical reads
0 redo size
720 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
--5.強制使用基于規則的優化器模式
SQL> Select /*+ rule*/ a.No, a.結算方式, Nvl(b.性質, 1) As 性質, a.金額, a.摘要, a.結算号碼
2 From (Select b.No, Decode(a.記錄性質, 3, a.結算方式, Null) As 結算方式, a.摘要, a.結算号碼, Sum
(1 * a.沖預交) As 金額
3 From 病人預交記錄 a,
4 (Select Distinct No, 結帳id
5 From 病人費用記錄
6 Where 記錄性質 = 1 And No In (
7 Select * From Table(Cast(f_Str2List(:v_str) as t_StrList))
8 ) And 記錄狀态 In (1, 3)) b
9 Where a.結帳id = b.結帳id And a.記錄性質 In (1, 11, 3) And Nvl(a.沖預交, 0) <> 0
10 Group By b.No, Decode(a.記錄性質, 3, a.結算方式, Null), a.摘要, a.結算号碼) a, 結算方式 b
11 Where a.結算方式 = b.名稱(+);
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS (OUTER)
2 1 VIEW
3 2 SORT (GROUP BY)
4 3 VIEW
5 4 SORT (UNIQUE)
6 5 TABLE ACCESS (BY INDEX ROWID) OF '病人預交記錄'
7 6 NESTED LOOPS
8 7 NESTED LOOPS
9 8 COLLECTION ITERATOR (PICKLER FETCH) OF 'f_Str2List'
10 8 INDEX (RANGE SCAN) OF '病人費用記錄_UQ_NO' (UNIQUE)
11 7 INDEX (RANGE SCAN) OF '病人預交記錄_IX_結帳ID' (NON-UNIQUE)
12 1 TABLE ACCESS (BY INDEX ROWID) OF '結算方式'
13 12 INDEX (UNIQUE SCAN) OF '結算方式_UQ_名稱' (UNIQUE)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed