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 numbernot 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是个很好用的关键字，在参数长度越大的时候，效果越明显。）

四，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_curbulk collect

intoemp_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 intoemp_tbl

from emp;

end;

/

Returning中也可以用：比如

insert into table1(col1, col2)

select seq1.nextval, col8 from table2

returning col1bulk collect intocol1_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;

foralli in emp_tbl.first

.. emp_tbl.last

update emp set sal = sal + 100

where empno = emp_tbl(i);

end;

/

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