web analytics
Sign In | Register
Welcome, Guest

Using NUMBER, PLS_INTEGER and SIMPLE_INTEGER in PL/SQL code

 Options
Previous Topic Next Topic

codeling 1599 - 6654
@2019-06-12 08:58:18

Avoid declaring NUMBER variables or subtypes with no precision.

If you do not specify precision NUMBER is defaulted to 38 or the maximum supported by your system, whichever is less. You may well need all this precision, but if you know you do not, you should specify whatever matches your needs.
@2019-06-12 08:59:37

Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values (no decimal point).

PLS_INTEGER has a length of -2,147,484,648 to 2,147,484,648 , on a 32 bit system.

There are many reasons to use PLS_INTEGER instead of NUMBER:

  • PLS_INTEGER uses less memory
  • PLS_INTEGER uses machine arithmetic, which is up to three times faster than library arithmetic which is used by NUMBER.
@2019-06-12 09:01:31

With Oracle 11g and above, the new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER and covers the same range. The basic difference is that

  • SIMPLE_INTEGER is always NOT NULL when the value of the declared variable is never going to be NULL then you can declare it as SIMPLE_INTEGER.
  • Another major difference is that you will never face a numeric overflow using SIMPLE_INTEGER as this data type wraps around without giving any error.
  • Also SIMPLE_INTEGER data type gives major performance boost over PLS_INTEGER when code is compiled in ‘NATIVE’ mode, because arithmetic operations on SIMPLE_INTEGER type are performed directly at the hardware level.
Previous Topic Next Topic

Comments
You must Sign In to comment on this topic.


© 2024 Digcode.com