PL/SQL数据类型和效率分析

       在PL/SQL编程中，要注意对于的数据类型的使用。PL/SQL支持多种兼容的数据类型，选择适合的数据类型对于PL/SQL的性能有很大的影响。下面是一个简单的例子：

CREATE OR REPLACE PROCEDURE testplsql
IS
   anumber        NUMBER;
   ainteger       INTEGER;
   anumber_10     NUMBER (10);
   apls_integer   PLS_INTEGER;
   abinary        BINARY_INTEGER;
   err            NUMBER;
BEGIN
   anumber := 0;

   LOOP
      anumber := anumber + 1;
      ainteger := ainteger + 1;
      anumber_10 := anumber_10 + 1;
      apls_integer := apls_integer + 1;
      abinary := abinary + 1;
      EXIT WHEN anumber > 1500;
   END LOOP;
END;
/

 

这个例子中测试了NUMBER、INTEGER、NUMBER(N)、PLS_INTEGER、BINARY_INTEGER这几种整数类型的运行效率。
我们使用DBMS_PROFILER进行测试：

DECLARE
   err   NUMBER;
BEGIN
   err :=
      DBMS_PROFILER.start_profiler (TO_CHAR (SYSDATE, 'dd-Mon-YYYY hh:mi:ss'));
   testplsql;
   err := DBMS_PROFILER.stop_profiler;
END;
/

 

通过查询PROFILER表，结果如下：
模块                  执行时间      行号   语句
TESTPLSQL  .000350          9     anumber:=0;
TESTPLSQL  .358233         11     anumber:=anumber+1;
TESTPLSQL  .358399         12     ainteger:=ainteger+1;
TESTPLSQL  .363219         13     anumber_10:=anumber_10+1;
TESTPLSQL  .133521         14     apls_integer:=apls_integer+1;
TESTPLSQL  .498786         15     abinary:=abinary+1;
TESTPLSQL  .511632         16     exit when anumber>1500 ;
   从测试结果可以看出，PLS_INTEGER最快，其次是NUMBER和INTEGER类型，最慢的是BINARY_INTEGER。产生这种差异的原因何在呢？PLS_INTEGER是PL/SQL 2.2版本的标准整型类型，只要是整型变量，取值范围在-2147483647和+2147483647之间的整数都可以用PLS_INTEGER来表示。PLS_INTEGER是直接和操作系统C语言的类型对应的语言，在执行过程中没有类型转换的开销，因此最快。
  在PL/SQL中，使用和C语言类型对应的类型来定义变量可以大大提高PL/SQL的效率。以下是PL/SQL类型和C语言类型的对应关系：

C语言类型

PL/SQL 参数

PL/SQL 返回值

char

IN VARCHAR2

VARCHAR2

char *

IN OUT VARCHAR2

VARCHAR2

double

IN NUMBER 

NUMBER 

double *

IN OUT NUMBER 

NUMBER 

float

IN NUMBER 

NUMBER 

float *

IN OUT NUMBER

NUMBER 

int

PLS_INTEGER 

PLS_INTEGER 

int *

IN OUT PLS_INTEGER 

PLS_INTEGER 

long int

IN PLS_INTEGER

PLS_INTEGER 

long int *

IN OUT PLS_INTEGER 

PLS_INTEGER 

short

IN PLS_INTEGER 

PLS_INTEGER 

short *

IN OUT PLS_INTEGER 

PLS_INTEGER 

void *

IN OUT 

ORA_FFI.POINTERTYPE