ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 高性能PL/SQL编程经验谈一

高性能PL/SQL编程经验谈一

原创 Linux操作系统 作者:木呼 时间:2011-06-08 16:46:20 0 删除 编辑

一直都想整理一份PL/SQL高效编程的技术经验,迟迟都没时间。废话不多说,希望以下的文章能给大家带来一些帮助。

 

一,PL/SQL数值型数据的选择

 

PL/SQL的数值型数据类型 

BINARY_DOUBLEBINARY_FLOATPLS_INTEGERBINARY_INTEGERNUMBER

 

 

NUMBER

BINARY_INTEGER

PLS_INTEGER

BINARY_FLOAT

BINARY_DOUBLE

表数据类型

Pl/sql数据类型

取值范围

 

-231---231

-231---231

单精度32bit

双精度64bit

数值类型

小数,浮点小鼠

整数

整数

浮点

浮点

我们在PL/SQL中使用整数时,推荐使用

BINARY_INTEGER

PLS_INTEGER

这两种数据类型:

declare

wk_cnt 数据类型:= 0;

begin

for cnt in 0..100000000 loop

wk_cnt := wk_cnt + 1;

end loop;

end;

/

 

以上的PL/SQL块中,我们选择BINARY_INTEGERPLS_INTEGER 循环的执行效率大约会是NUMBER性的3倍(别问我怎么看过程的效率,自己trace去)。个人习惯使用pls/integer

 

二,变量的申明及NOT NULL限制

这里我们对比以下两个过程

 

过程一:

declare

value1 number not null := 0;

begin

for cnt in 0 .. 100000000 loop

value1 := 1;

end loop;

end;

/

过程二:

declare

value1 number;

begin

for cnt in 0 .. 100000000 loop

value1 := 1;

end loop;

if value1 is not null then

null;

end if;

end;

/

在过程一中,当我们每次执行循环的时候都会在 value1 := 1; 时都会对是否null做一次检查。而在过程二中,我们只在循环之后对null检查了一次。

 

三, NOCOPY

首先我们先解释一下nocopy,

pl/sql中,参数模式分为三种,in ,out ,inout 这三种的区别就不用介绍了

PL/SQL中对outin out参数使用的?默认形参会复制一份实参的副本,然后在内部传递,修改等,发生异常,不会赋值给实参,控制权交还调用环境,而实参值不变,还是调用前的值。而使用了NOCOPY后,形参将获得一个指向实参的指针,然后在内部传递,赋值都直接修改实参了,此时如果异常发生,控制权交还调用环境,但是实参已经被修改了。无法还原成调用前的值。

对参数IN使用NOCOPY将会产生编译错误,这是因为参数IN总是按引用传递.NOCOPY的主要优点是可以提高程序的效率。当我们传递大型PL/SQL表时,其优越性特别显著.使用NOCOPY的限制在某些情况下,NOCOPY将被编译器忽略,这时的参数仍将按值传递。这时,编译器不会报告编译错误。由于NOCOPY是一个提示项(Hint),编译器可以决定是否执行该项。

大家可以对比以下四个过程,自己体悟下就能理解了。

过程一:

DECLARE

n NUMBER := 10;

PROCEDURE do_something (

n1 IN NUMBER,

n2 IN OUT NUMBER,

n3 IN OUT NOCOPY NUMBER) IS

BEGIN

n2 := 20;

dbms_output.put_line(n); -- prints 10<<1>>

n3 := 30;

dbms_output.put_line(n); -- prints 30 <<2>>

END;

BEGIN

do_something(n, n, n);

dbms_output.put_line(n); -- prints 20 <<3>>

END;

 

-----当指定nocopyn3的值没有赋值给n

 

 

过程二:

 

DECLARE

n NUMBER := 10;

PROCEDURE do_something (

n1 IN NUMBER) IS

BEGIN

dbms_output.put_line(n1); -- prints 10

--n1:=20; --illegal assignment.

END;

BEGIN

do_something(n);    ----------------   10

do_something(20);   --------------- 20

END;

过程三:

DECLARE

n NUMBER := 10;

PROCEDURE do_something (

n1 OUT nocopy NUMBER) IS

BEGIN

dbms_output.put_line('before assign: ' || n); -- prints none <<1>>

n1:=20;

dbms_output.put_line('before return: ' || n); -- prints 20 <<2>>

END;

BEGIN

do_something(n);

dbms_output.put_line('after return: ' || n); -- prints 20

END;

过程四:

DECLARE

n NUMBER := 10;

PROCEDURE do_something (

n1 OUT NUMBER) IS

BEGIN

dbms_output.put_line('before assign: ' || n1); -- prints none <<1>>

n1:=20;

dbms_output.put_line('before return: ' || n); -- prints 10 <<2>>

dbms_output.put_line('before return: ' || n1); -- prints 20 <<2>>

END;

BEGIN

do_something(n);

dbms_output.put_line('after return: ' || n); -- prints 20

END;

 

NOCOPY的主要优点是可以提高程序的效率。当我们传递大型PL/SQL表时,其优越性特别显著.使用NOCOPY的限制在某些情况下,NOCOPY将被编译器忽略,这时的参数仍将按值传递。这时,编译器不会报告编译错误。由于NOCOPY是一个提示项(Hint),编译器可以决定是否执行该项。在下列情况下,编译器将忽略NOCOPY项:

1.实参是索引表(index-by table)的成员时。如果该实参是全表,则该限制不起作用。

2.实参被强制指定精度,比例或NOT NULL时。该限制将不适用按最大长度强制的字符串参数。

3.实参和形参都是记录类型,二者是以隐含方式或使用了%ROWTYPE类型声明时,作用在对应字段的强制说明不一致。

4.传递实参需要隐式类型转换时。

5.子程序涉及到远程过程调用(PRC)。远程过程调用就是跨越数据库对远程服务器的过程调用。

(这里除了过程,文字很多都是copy的,整理起来挺麻烦,反正NOCOPY是个很好用的关键字,在参数长度越大的时候,效果越明显。)

四,returning

文字介绍确实很难写,我们直接看下面的程序块。

过程一:

declare

seq number;

begin

insert into table1 values

(seq1.nextval, 'AAA', 'BBB');

select seq1.currval into seq from dual;

dbms_output.put_line

(seq || ‘的值已经取得');

end;

/

过程二:

declare

seq number;

begin

insert into table1 values

(seq1.nextval, 'AAA', 'BBB')

returning col1 into seq;

dbms_output.put_line

(seq || '的值已经取得');

end;

 

这两个过程的目的都是在执行Insert之后获取当前的seq值,在第一个过程中,我们执行了两次sql,在第二个过程中我们使用了returning 只执行了一次sql,代码性能不言而喻吧。

五,execute immediate中的绑定变量

我们看下面两条语句吧

一:

execute immediate 'insert into temp values’||emp_no||’,’||emp_name||’,’||emp_sal||’)' ;

二:

execute immediate 'insert into temp values (:empno, :ename, :sal)'

using emp_no, emp_name, emp_sal;

 

首先我们先说说什么是绑定变量,为什么要用绑定变量

绑定变量就是在动态sql的一个占位符,它告诉oracle现在需要生产一个执行计划,随后会为这个占位符提供一个值。

     Sql分为动态部分和静态部分。静态部分是不变的,动态部分是不固定的。很明显整个数据库中的对象数量是有限的,而其中的数据是无限的。占位符体态的就是动态部分。动态部分在一般情况下,对执行计划的生成影响是微乎其微的。不同的动态部分产生的执行计划都是相同的。使用绑定变量可以史游标共享,避免多次硬解析。

也许你们已经明白这两条语句的优劣了,在语句二中我们使用了绑定变量。很少有朋友们写代码会注意到这些,在动态语句中并不会默认的将变量转换成绑定变量。在pl/sql中动态语句是不会自动将变量转换成绑定变量的,不知道后来改没改过来。

 

六,bulk处理

Pl/sql确实很简单,可是要写好却是有些麻烦,就拿bulk处理来说吧,也许很多同行们都不大会注意到这些。

什么是bulk处理,bulk处理就是把大量数据一次性处理。比如:一次性的进行多行的fetch,insert. 通过数组等数据类型进行处理,PL/SQL表,嵌套表等。我们先看下面两个过程。

过程一:

declare

type emp_tbl_type is table of

emp%rowtype index by pls_integer;

emp_tbl emp_tbl_type;

cursor emp_cur is select * from emp;

begin

open emp_cur;

loop

fetch emp_cur

into emp_tbl(emp_cur%rowcount);

exit when emp_cur%notfound;

end loop;

close emp_cur;

end;

/

过程二:

declare

type emp_tbl_type is table of

emp%rowtype index by pls_integer;

emp_tbl emp_tbl_type;

cursor emp_cur is select * from emp;

begin

open emp_cur;

fetch emp_cur bulk collect

into emp_tbl;

close emp_cur;

end;

/

这两个过程实现的是同一个功能,想pl/sql表中里fetch 数据,大家可以尝试下在大量数据下(10W条吧),第二种写法那是绝对的比第一种牛掰的。

 

Bulk collect也可以直接用在select into语句中

如下:

declare

type emp_tbl_type is table of

emp%rowtype index by pls_integer;

emp_tbl emp_tbl_type;

begin

select * bulk collect into emp_tbl

from emp;

end;

/

Returning中也可以用:比如

insert into table1(col1, col2)

select seq1.nextval, col8 from table2

returning col1 bulk collect into col1_tbl;

 

 bulk forall

pl/sql中,for 大家应该都用过了,forall肯定是用的很少了,废话不多说,咱们还是从程序中学习,看下面两段程序

程序一:

declare

type emp_tbl_type is table of

emp.empno%type index by pls_integer;

emp_tbl emp_tbl_type;

begin

select empno bulk collect into emp_tbl

from emp;

for i in emp_tbl.first .. emp_tbl.last

loop

update emp set sal = sal + 100

where empno = emp_tbl(i);

end loop;

end;

/

程序二:

declare

type emp_tbl_type is table of

emp.empno%type index by pls_integer;

emp_tbl emp_tbl_type;

begin

select empno bulk collect into emp_tbl

from emp;

forall i in emp_tbl.first

.. emp_tbl.last

update emp set sal = sal + 100

where empno = emp_tbl(i);

end;

/

看明白没,应该能看明白的,使用forall的效率,应该是第一段的两倍左右。

 

今天就先写到这里,以后有时间继续。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23754390/viewspace-697422/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-19

  • 博文量
    93
  • 访问量
    152564