Oracle知识拾漏两则

本文介绍了在 Oracle 11g 中 FORALL 语法的优化以及本地NET服务名书写规则,包括数组变量限制的解除和命名配置的正确格式。

在学习和研究Oracle过程中,我们会遇到很多零散的知识点。在日常工作中主动的去收集这些知识,并且进行适当的整理,能够做到积少成多。本篇就介绍两个发现的知识点,权作积累记录之用。

111g中对FORALL的语法优化

注:本知识点是一个朋友提供,特此感谢!

ForallOraclePL/SQL中提供的一种批量处理语法。它提供了比传统for loop更好的处理性能优势。两者的差异主要体现在处理引擎上下文切换上的性能损耗优势。

PL/SQL语句中出现SQL语句,PL/SQL引擎会将SQL语句传递转化给SQL引擎进行处理。SQL引擎处理后再将结果返回给PL/SQL引擎。这个过程我们称之为上下文切换(context switch)。在for语句运行的时候,伴随着频繁的上下文切换动作。使用forall,就可以避免出现这种情况。

在使用forall的时候,PL/SQLSQL引擎的交互只有一次。所有的语句参数都是一次性的传递给SQL进行执行。这样,上下文切换动作的损耗就能得到节省。

但是,在Oracle11g之前,FORALL语句使用时有很多的语句格式限制。主要体现在两个方面:

ü       Forall语句中只能放置一条SQL语句

for语句语法格式不同的是:forall没有loopend 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-BINDDML语句在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下,数组变量限制被解除,开发人员不需要再去写长长的数组定义了。

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值