2011-08-22 16:49:06 heqiyu34 阅读数 13413更多

 

Benefits of Using
PLS_INTEGER Datatype in PL/SQL

If you have a whole-number counter, for example in a loop or record counter, consider using a datatype ofPLS_INTEGER instead of INTEGER or NUMBER. When declaring an integer variable,PLS_INTEGER
is the most efficient numeric datatype because its values require less
storage than INTEGER or NUMBER values, which are represented internally
as 22-byte Oracle numbers. Also,PLS_INTEGER operations
use machine arithmetic, so they are faster than BINARY_INTEGER, INTEGER, or NUMBER operations, which use library arithmetic.

来源：http://gaijing814.iteye.com/blog/271620

--------------------------------------------------------------------------

PLS_INTEGER Datatype
You use the PLS_INTEGER datatype to store signed integers. Its magnitude
range is -2147483648 to 2147483647, represented in 32 bits. PLS_INTEGER
values require less storage than NUMBER values and NUMBER subtypes.
Also, PLS_INTEGER operations use hardware arithmetic,
so they are faster than NUMBER operations, which use library
arithmetic. For efficiency, use PLS_INTEGER for all calculations that
fall within its magnitude range. For calculations outside the range of
PLS_INTEGER, you can use the INTEGER datatype.

Note:

The BINARY_INTEGER and PLS_INTEGER datatypes are identical. See "Change to the BINARY_INTEGER Datatype".

When a calculation with two PLS_INTEGER datatypes overflows the
magnitude range of PLS_INTEGER, an overflow exception is raised even if
the result is assigned to a NUMBER datatype.

tips:1）pls_integer类型也是数字类型，但和number类型不同，number可以存储实数，而pls_integer只能存储-2147483647到+2147483647之间的整数，如果使用pls_integer类型时发生溢出，系统将会报错。
 
  
   2）binary_integer与pls_integer类似，在9.2版本以前大量使用，从9.2以后，从Oracle内部一些组件可以看的出，大有被pls_integer取代之势(pls_integer比binary_integer具有更少的存储开销和更好的访问性能，所以Oracle从9.2以后推荐你尽量能使用pls_integer就使用pls_integer)。它也是只能存储-2147483647到+2147483647之间的整数。
 
      3）在oracle
11g中，又增加了一个新的类似的数据类型simple_integer,不过simple_integer不能包含空值，它的取值范围是[-2147483648..2147483647]。在11g中，simple_integer相对pls_integer在性能上又有所提高，如果在实际的pl/sql中既不需要overflow检查也不会包含null值，Oracle建议你使用simple_integer.