在學習和研究Oracle過程中，我們會遇到很多零散的知識點。在日常工作中主動的去收集這些知識，並且進行適當的整理，能夠做到積少成多。本篇就介紹兩個發現的知識點，權作積累記錄之用。

1、11g中對FORALL的語法最佳化

注：本知識點是一個朋友提供，特此感謝！

Forall是Oracle在PL/SQL中提供的一種批次處理語法。它提供了比傳統for loop更好的處理效能優勢。兩者的差異主要體現在處理引擎上下文切換上的效能損耗優勢。

在PL/SQL語句中出現SQL語句，PL/SQL引擎會將SQL語句傳遞轉化給SQL引擎進行處理。SQL引擎處理後再將結果返回給PL/SQL引擎。這個過程我們稱之為上下文切換（context switch）。在for語句執行的時候，伴隨著頻繁的上下文切換動作。使用forall，就可以避免出現這種情況。

在使用forall的時候，PL/SQL與SQL引擎的互動只有一次。所有的語句引數都是一次性的傳遞給SQL進行執行。這樣，上下文切換動作的損耗就能得到節省。

但是，在Oracle 11g之前，FORALL語句使用時有很多的語句格式限制。主要體現在兩個方面：

ü Forall語句中只能放置一條SQL語句

與for語句語法格式不同的是：forall沒有loop和end loop關鍵字配對。也就意味著forall語句後面只能跟一個SQL語句呼叫。

--ForAll Insert

forall i in 1..t_infos.count

execute immediate 'insert into t (owner, object_id, object_type) values (:1,:2,:3)'

using t_infos(i).owner,

t_infos(i).object_id,

t_infos(i).object_type;

ü SQL語句中的陣列變數限制

使用forall語句中，如果對應的陣列變數元素是一個記錄型別（rowtype, record），那麼語句是不能編譯透過。報錯PLS-00435提示。這樣，就要求每次只能使用基本型別變數作為陣列元素。

下面我們在10g進行試驗。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

構建如下的程式碼片段：

declare

type t_list is table of t%rowtype index by binary_integer;

vc_sql varchar2(1000);

t_infos t_list;

i number;

begin

--Geting Data

select *

bulk collect into t_infos

from dba_objects

where rownum<10000;

--ForAll Insert

forall i in 1..t_infos.count

execute immediate 'insert into t (owner, object_id, object_type) values (:1,:2,:3)'

using t_infos(i).owner,

t_infos(i).object_id,

t_infos(i).object_type;

commit;

end;

/

注意，這裡我們在forall中直接使用了rowtype，繫結變數使用了陣列元素型別。此時，執行是報錯的。

ORA-06550: 第 16 行, 第 30 列:

PLS-00436: 實施限制: 不能引用記錄的 BULK In-BIND 表的欄位

ORA-06550: 第 15 行, 第 6 列:

PL/SQL: Statement ignored

ORA-06550: 第 15 行, 第 6 列:

PLS-00435: 沒有 BULK In-BIND 的 DML 語句在 FORALL 內不能使用

10g下是不能透過使用，只有修改為如下程式碼才能使用。

SQL> declare

2 type t_list_owners is table of t.owner%type index by binary_integer;

3 type t_list_ids is table of t.object_id%type index by binary_integer;

4 type t_list_types is table of t.object_type%type index by binary_integer;

5

6 t_infos_owner t_list_owners;

7 t_infos_id t_list_ids;

8 t_infos_type t_list_types;

9

10 i number;

11 begin

12 --Geting Data

13 select owner, object_id, object_type

14 bulk collect into t_infos_owner, t_infos_id, t_infos_type

15 from dba_objects

16 where rownum<10000;

17

18 --ForAll Insert

19 forall i in 1..t_infos_owner.count

20 execute immediate 'insert into t (owner, object_id, object_type) values (:1,:2,:3)'

21 using t_infos_owner(i),

22 t_infos_id(i),

23 t_infos_type(i); 24

25 --commit;

27 end;

28 /

PL/SQL procedure successfully completed

上面例項就說明出10g上的限制因素，就是forall中不能使用記錄record複合陣列元素。筆者認為其中的原因可能是陣列複合元素可能需要pl/sql引擎進行處理，切換到SQL引擎後，不宜於進行處理。

但是，在11g下，這種限制已經被打破。

--轉換到11g下

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> declare

2 type t_list is table of t%rowtype index by binary_integer;

3 vc_sql varchar2(1000);

4 t_infos t_list;

5 i number;

6 begin

7 --Geting Data

8 select *

9 bulk collect into t_infos

10 from dba_objects

11 where rownum<10000;

12

13 --ForAll Insert

14 forall i in 1..t_infos.count

15 execute immediate 'insert into t (owner, object_id, object_type) values (:1,:2,:3)'

16 using t_infos(i).owner,

17 t_infos(i).object_id,

18 t_infos(i).object_type;

19

20 --commit;

21

22 end;

23 /

PL/SQL procedure successfully completed

在使用rowtype作為forall迴圈體物件時，Oracle沒有報錯，語句正常執行。

結論：Forall語法可以實現批次資料SQL語句提交執行。通常情況下，forall會帶來一定的效能提升。在11g下，陣列變數限制被解除，開發人員不需要再去寫長長的陣列定義了。

2、tnsnames中本地NET服務名格式規則以及影響

Tnsnames.ora是每個初學Oracle者難以逃避的一關。在tnsnames.ora檔案中，儲存著從本地訪問遠端資料伺服器Server的配置資訊。在該檔案中，連線資訊以巢狀括號的方式進行儲存。

WILSON =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.39.41)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = wilson)

)

很多朋友在配置本地命名的時候，選擇直接修改tnsnames.ora檔案，或者直接替換掉檔案方法。而不去使用Oracle提供的Net Configuration Assistant工具進行配置。這在很多時候，都會帶來一些問題：主要是命名配置的格式錯誤。

在Oracel解析tnsname.ora檔案中的命名資訊時，是要求其滿足一些基本規則的。從目前我們探索的情況看，兩個規則是必須的：

ü 本地命名必須頂格書寫在tnsname.ora中定義的本地連線名稱（如：wilson），要求一定是頂行書寫的；

ü 巢狀括號非頂格規則：在各個巢狀括號中，只有最外層的括號是允許頂行書寫，其他都不允許書寫；

那麼，如果違背了兩個規則，會有什麼現象呢？我們進行如下的系列實驗，下面是實驗範本tnsname.ora。

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

ORA11G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.39.41)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ora11g)

)

使用tnsping連線。

C:\Users\Liuziyu>tnsping ora11g

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-11月-

2011 13:19:02

已使用的引數檔案:

C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 介面卡來解析別名

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = 10.1.39.41)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora11g)))

OK (70 毫秒)

ü 本地名稱非頂格書寫

我們修改ora11g的格式，將其書寫為非頂行書寫。

ORA11G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.39.41)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ora11g)

)

之後，使用tnsping語句進行測試解析情況，測試失敗。

C:\Users\Liuziyu>tnsping ora11g

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-11月-

2011 13:17:42

已使用的引數檔案:

C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

TNS-03505: 無法解析名稱

說明：當服務名在tnsname.ora中非頂格書寫時，Oracle不能正確解析該名稱。

ü 最外層括號頂行頭

如果使用NETCA進行配置，tnsnames.ora中括號都是不頂行頭的。如果我們頂行頭書寫，看看結果如何：

ORA11G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.39.41)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ora11g)

)

連線無問題：

C:\Users\Liuziyu>tnsping ora11g

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-11月-

2011 13:31:35

已使用的引數檔案:

C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 介面卡來解析別名

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = 10.1.39.41)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora11g)))

OK (80 毫秒)

但是注意，這裡只是能夠連線解析。此時我們啟動Java編寫的Net Configuration Assistant，就會發現無法進入配置本地服務名配置的模組。也就是說：最外層括號頂行寫，只是能夠做到服務名解析，而不能被客戶端工具識別！

如果非外層括號頂行書寫，會有什麼影響呢？

ORA11G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.39.41)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ora11g)

)

進行解析測試：

C:\Users\Liuziyu>tnsping ora11g

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 21-11月-

2011 13:35:33

已使用的引數檔案:

C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 介面卡來解析別名

Attempting to contact (DESCRIPTION =

TNS-12533: TNS: 非法的 ADDRESS 引數

結論：當非外層括號頂行書寫時，解析會出現問題，報錯。

綜合上述：配置本地服務名稱時，還是建議使用Net Configuration Assistant比較穩妥。

3、結論

Oracle知識浩如煙海，但是又有脈可循。一點點的積累，一點點成長，大有裨益。

Oracle知識拾漏兩則

相關文章