在PL/SQL开发中正确使用数据类型来提高性能

1、确保比较运算中的数据类型相同
  
下面例子比较了采用不同数据类型的代价，也就是在if语句中将一个数字类型的数据类型和
字符值进行比较。

程序1：
create or replace procedure test_if(p_condition_num number) as
   lv_temp_num number := 0;
   lv_temp_cond_num number := p_condition_num;
begin
   for lv_cont_num in 1..100000 loop
       if lv_temp_cond_num = '1' then
          lv_temp_num := lv_temp_num + 1;
       elsif lv_temp_cond_num='2' then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num='3' then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num='4' then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num='5' then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num='6' then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num='7' then
          lv_temp_num :=lv_temp_num+1;
       else
          lv_temp_num :=lv_temp_num+1;
       end if;
   end loop;

end;

SQL> exec test_if(8);

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.43

将上面过程中参与比较的数值采用相同的数据类型，执行速度明显加快

程序2：

create or replace procedure test_if(p_condition_num number) as
   lv_temp_num number := 0;
   lv_temp_cond_num number := p_condition_num;
begin
   for lv_cont_num in 1..10000000 loop
       if lv_temp_cond_num = 1 then
          lv_temp_num := lv_temp_num + 1;
       elsif lv_temp_cond_num=2 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=3 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=4 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=5 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=6 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=7 then
          lv_temp_num :=lv_temp_num+1;
       else
          lv_temp_num :=lv_temp_num+1;
       end if;
   end loop;

end;

SQL> exec test_if(8);

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.77

如果确保同属于一类数值类型的比较值具有相同的子数据类型，因此，在程序2中if语句中的
比较量和1、2、3等进行比较，也就是将number类型与pls_integer类型进行比较。这仍然
将导致Oracle类型转化的开销，要消除这种开销，应当将1、2、3等数改成1.0、2.0、3.0
等值，这样速度就能进一步提高：

程序3：

create or replace procedure test_if(p_condition_num number) as
   lv_temp_num number := 0;
   lv_temp_cond_num number := p_condition_num;
begin
   for lv_cont_num in 1..10000000 loop
       if lv_temp_cond_num = 1.0 then
          lv_temp_num := lv_temp_num + 1;
       elsif lv_temp_cond_num=2.0 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=3.0 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=4.0 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=5.0 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=6.0 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=7.0 then
          lv_temp_num :=lv_temp_num+1;
       else
          lv_temp_num :=lv_temp_num+1;
       end if;
   end loop;

end;

SQL> exec test_if(8);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.07

2、使用pls_integer数据类型进行整数运算

声明数值类型的通用标准是使用number类型，在pl/sql 2.2版本中，oracle引入了pls_integer
数据类型。这种数据类型可以用于代替各种数值数据系列类型的声明，只要变量的值是一个整数，
并且在-2147483647到+2147483647的范围内。pls_integer可以使用更少的内部命令来处理，所以
使用这种数据类型就可以提高性能。
下面将前面的例子中的number数据类型改为pls_integer

程序4：

create or replace procedure test_if(p_condition_num pls_integer) as
   lv_temp_num pls_integer := 0;
   lv_temp_cond_num pls_integer := p_condition_num;
begin
   for lv_cont_num in 1..10000000 loop
       if lv_temp_cond_num = 1 then
          lv_temp_num := lv_temp_num + 1;
       elsif lv_temp_cond_num=2 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=3 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=4 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=5 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=6 then
          lv_temp_num :=lv_temp_num+1;
       elsif lv_temp_cond_num=7 then
          lv_temp_num :=lv_temp_num+1;
       else
          lv_temp_num :=lv_temp_num+1;
       end if;
   end loop;

end;

SQL> exec test_if(8);

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.61

附注：如果将一个带有小数的数字赋值给一个pls_integer变量，那么这个值将取整为一个
     整数，就像对这个数字运行了round取整函数一样。