Home » SQL & PL/SQL » SQL & PL/SQL » pls_integer
pls_integer [message #217873] Mon, 05 February 2007 23:08 Go to next message
Messages: 2
Registered: February 2007
Junior Member
what is the use of pls_integer?can anyone expand pls?
Re: pls_integer [message #217930 is a reply to message #217873] Tue, 06 February 2007 02:48 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have always understood that calculations involving pls_integer datatypes will be performed using the server's own Os level maths libraries, rather than Oracles, and that they will be quicker as a result.

I can't guarantee why, but some empiracle testing suggests that calculations involving pls_integer as opposed to Number types are faster by the following ratios

Operation     Pls_integer time as % of Number time
Addition            65%
Addition            65%

Multiplication      65%
Division            150% (hey - it's an integer. not much call for divison there
POWER               110%

Re: pls_integer [message #217937 is a reply to message #217873] Tue, 06 February 2007 02:56 Go to previous message
Messages: 11
Registered: January 2007
Junior Member
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 of PLS_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.

Variables declared as pls_integer and binary_integer have the same range, but they are treated differently. When a calculation involving pls_integer overflows, pl/sql raises an exception. However, similar overflow involving binary_integers will not raise an exception if the result is being assigned to a number variable.

Previous Topic: How to use LIKE inside Where & When clauses?
Next Topic: Help required in using IN clause
Goto Forum:

Current Time: Wed Aug 23 08:53:53 CDT 2017

Total time taken to generate the page: 0.10587 seconds