oracle pls_integer 和 binary_integer 有什么区别？

What's the difference between pls_integer and binary_integer?

提问by Sathyajith Bhat

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.

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

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

其中一些由 binary_integers 索引，一些由 pls_integers 索引。两者之间有什么区别吗？

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

我查看了文档，但除了这一行：

The PL/SQL data types PLS_INTEGERand BINARY_INTEGERare identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGERand 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 10gR2

回答by Thilo

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:

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.

BINARY_INTEGER - defined in the STANDARD package as a subtype of INTEGER. Variables declared as BINARY_INTEGER can be assigned values between -2³¹+1 .. 2³¹-1, 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:

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

INTEGER - 在 STANDARD 包中定义为 NUMBER 的子类型，此数据类型以完全独立于平台的方式实现，这意味着您对 NUMBER 或 INTEGER 变量所做的任何事情都应该相同，无论安装数据库的硬件如何.

BINARY_INTEGER - 在 STANDARD 包中定义为 INTEGER 的子类型。声明为 BINARY_INTEGER 的变量可以赋值 -2 ³¹+1 .. 2 ³¹-1，也就是 -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³¹+1 .. 2³¹-1, 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 - 在 STANDARD 包中定义为 BINARY_INTEGER 的子类型。声明为 PLS_INTEGER 的变量可以被赋值为 -2 ³¹+1 .. 2 ³¹-1，也就是 -2,147,483,647 到 2,147,483,647。PLS_INTEGER 操作使用机器算术，因此它们通常比 NUMBER 和 INTEGER 操作快。此外，在 Oracle 数据库 10g 之前，它们比 BINARY_INTEGER 快。但是，在 Oracle 数据库 10g 中，BINARY_INTEGER 和 PLS_INTEGER 现在完全相同，可以互换使用。

回答by nagu

binary_integerand pls_integerboth 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 integerand binary_integerpls_integervery fast in excution. Because pls_intgeroperates on machine arithmetic and binary_integeroperes on library arithmetic.

相比于integer和binary_integerpls_integer非常快excution。因为pls_intger对机器算术进行操作，binary_integer对库算术进行操作。

pls_integercomes from oracle10g.

pls_integer来自oracle10g。

binary_integerallows 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;
/ 

回答by Gebru Welay

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 的计算也不会引发异常。