在学习和研究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知识浩如烟海，但是又有脉可循。一点点的积累，一点点成长，大有裨益。

ITPub博客

Oracle知识拾漏两则

最新文章