pls_integer和binary_integer有什么区别?

I've inherited some code which is going to be the base for some additional work. Looking at the stored procs, I see quite a lot of associative-arrays.

我继承了一些代码，这些代码将成为一些额外工作的基础。看看存储的procs，我看到了很多关联数组。

Some of these are indexed by binary_integers, some by pls_integers. Are there any differences between the two?

其中一些是由binary_integers索引的，有些是由pls_integer创建的。这两者之间有什么区别吗?

I had a look at the documentation, but apart from this line:

我看了一下文件，但除了这句话:

The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.

PL/SQL数据类型PLS_INTEGER和BINARY_INTEGER是相同的。为了简单起见，本文使用PLS_INTEGER来表示PLS_INTEGER和BINARY_INTEGER。

I couldn't find any difference between the two. So what's the difference? Are both around for historical/compatibility reasons?

我找不出这两者之间有什么区别。所以有什么区别呢?两者都是历史/兼容性的原因吗?

I'm using Oracle 10gR2

我用Oracle 10 gr2

3 个解决方案

#1

Historical reasons. They used to be different before 10g:

历史原因。在10g之前它们是不同的:

On 8i and 9i, PLS_INTEGER was noticeably faster than BINARY_INTEGER.

在8i和9i上，PLS_INTEGER的速度明显快于BINARY_INTEGER。

When it comes to declaring and manipulating integers, Oracle offers lots of options, including:

在声明和操作整数方面，Oracle提供了很多选项，包括:

INTEGER - defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which means that anything you do with NUMBER or INTEGER variables should work the same regardless of the hardware on which the database is installed.

INTEGER—在标准包中定义为NUMBER的子类型，这个数据类型以完全独立于平台的方式实现，这意味着无论数据库安装在哪个硬件上，对NUMBER或INTEGER变量所做的任何事情都应该是相同的。

BINARY_INTEGER - defined in the STANDARD package as a subtype of INTEGER, variables declared as BINARY_INTEGER can be assigned values between -231 .. 231, aka -2,147,483,647 to 2,147,483,647. Prior to Oracle9i Database Release 2, BINARY_INTEGER was the only indexing datatype allowed for associative arrays (aka, index-by tables), as in:

BINARY_INTEGER——在标准包中定义为整数的子类型，可以将变量声明为BINARY_INTEGER，并在-231之间分配值。231，即-2,147,483,647到2,147,483,647。在Oracle9i数据库发行版2之前，BINARY_INTEGER是唯一允许关联数组(也称为索引表)的索引数据类型，如:

  TYPE my_array_t IS TABLE OF VARCHAR2(100) 
  INDEX BY BINARY_INTEGER

PLS_INTEGER - defined in the STANDARD package as a subtype of BINARY_INTEGER, variables declared as PLS_INTEGER can be assigned values between -2³¹ .. 2³¹, aka -2,147,483,647 to 2,147,483,647. PLS_INTEGER operations use machine arithmetic, so they are generally faster than NUMBER and INTEGER operations. Also, prior to Oracle Database 10g, they are faster than BINARY_INTEGER. In Oracle Database 10g, however, BINARY_INTEGER and PLS_INTEGER are now identical and can be used interchangeably.

PLS_INTEGER——在标准包中定义为BINARY_INTEGER的子类型，声明为PLS_INTEGER的变量可以在-231之间分配值。231，即-2,147,483,647到2,147,483,647。PLS_INTEGER操作使用机器算法，因此它们通常比数字和整数操作更快。此外，在Oracle数据库10g之前，它们比BINARY_INTEGER要快。然而，在Oracle数据库10g中，BINARY_INTEGER和PLS_INTEGER现在是相同的，可以互换使用。

#2

binary_integer and pls_integer both are same. Both are PL/SQL datatypes with range -2,147,648,467 to 2,147,648,467.

binary_integer和pls_integer都是相同的。两者都是PL/SQL数据类型，范围在2,147,648,467到2,147,648,467之间。

Compared to integer and binary_integer pls_integer very fast in excution. Because pls_intger operates on machine arithmetic and binary_integer operes on library arithmetic.

与integer和binary_integer相比，pls_integer在excution中速度非常快。因为pls_intger对机器算法进行操作，而binary_integer操作对库算法进行操作。

pls_integer comes from oracle10g.

pls_integer来自oracle10g。

binary_integer allows indexing integer for assocative arrays prior to oracle9i.

binary_integer允许在oracle9i之前为一个事务数组创建索引整数。

Clear example:

明显的例子:

SET TIMING ON

declare
  num   integer := 0;
  incr  integer := 1;
  limit integer := 100000000;
begin
  while num < limit loop
    num := num + incr;
  end loop;
end;
PL/SQL procedure successfully completed.

Elapsed: 00:00:20.23
ex:2
declare
  num   binary_integer := 0;
  incr  binary_integer := 1;
  limit binary_integer := 100000000;
begin
  while num < limit loop
    num := num + incr;
  end loop;
end;
/ 

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.81
ex:3
declare
  num   pls_integer := 0;
  incr  pls_integer := 1;
  limit pls_integer := 100000000;
begin
  while num < limit loop
    num := num + incr;
  end loop;
end;
/

#3

Another difference between pls_integer and binary_integer is that when calculations involving a pls_integer overflow the PL/SQL engine will raise a run time exception. But, calculations involving a binary_integer will not raise an exception even if there is an overflow.

pls_integer和binary_integer之间的另一个区别是，当涉及pls_integer溢出的计算时，PL/SQL引擎将引发运行时异常。但是，涉及到binary_integer的计算即使存在溢出也不会引发异常。

#1