Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

oracle - What's the difference between pls_integer and 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.

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

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.

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

On 8i and 9i, PLS_INTEGER was noticeably faster than 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 -231+1 .. 231-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:

  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 -231+1 .. 231-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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...