Oracle知识拾漏两则

在学习和研究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进行执行。这样，上下文切换动作的损耗就能得到节省。

但是，在Oracle11g之前，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 Database10gEnterpriseEdition Release10.2.0.1.0 - Prod

PL/SQL Release10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version10.2.0.1.0 - Production

NLSRTL Version10.2.0.1.0 – Production

构建如下的代码片段：

declare

type t_list is table oft%rowtypeindex 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                        usingt_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 Database11gEnterpriseEdition Release11.2.0.1.0 - Production

PL/SQL Release11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version11.2.0.1.0 - Production

NLSRTL Version11.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: Version10.2.0.1.0 - Production on 21-11月-

2011 13:19:02

Copyright (c) 1997, 2005, Oracle. All rights reserved.

已使用的参数文件:

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: Version10.2.0.1.0 - Production on 21-11月-

2011 13:17:42

Copyright (c) 1997, 2005, Oracle. All rights reserved.

已使用的参数文件:

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: Version10.2.0.1.0 - Production on 21-11月-

2011 13:31:35

Copyright (c) 1997, 2005, Oracle. All rights reserved.

已使用的参数文件:

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: Version10.2.0.1.0 - Production on 21-11月-

2011 13:35:33

Copyright (c) 1997, 2005, Oracle. All rights reserved.

已使用的参数文件:

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