在学习和研究Oracle过程中,我们会遇到很多零散的知识点。在日常工作中主动的去收集这些知识,并且进行适当的整理,能够做到积少成多。本篇就介绍两个发现的知识点,权作积累记录之用。
注:本知识点是一个朋友提供,特此感谢!
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知识浩如烟海,但是又有脉可循。一点点的积累,一点点成长,大有裨益。