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

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

 

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

 

PL/SQL的数值型数据类型 

BINARY_DOUBLE，BINARY_FLOAT，PLS_INTEGER，BINARY_INTEGER，NUMBER

 

	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_INTEGER，PLS_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中对out，in 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;

 

-----当指定nocopy后n3的值没有赋值给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是个很好用的关键字，在参数长度越大的时候，效果越明显。）

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

转载于:http://blog.itpub.net/23754390/viewspace-697421/