Oracle PL/SQL - PLS_INTEGER Subtype: SIMPLE

Introduction

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER and has a NOT NULL constraint.

It differs from PLS_INTEGER in its overflow semantics.

If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER.

Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.

SIMPLE_INTEGER Overflow

If and only if all operands in an expression have the data type SIMPLE_INTEGER, PL/SQL uses two's complement arithmetic and ignores overflows.

Values can wrap from positive to negative or from negative to positive; for example:

Demo

SQL>
SQL> DECLARE-- from  w  w w.j av  a2s.com
  2   n SIMPLE_INTEGER := 2147483645;
  3  BEGIN
  4   FOR j IN 1..4 LOOP
  5      n := n + 1;
  6      DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
  7     END LOOP;
  8     FOR j IN 1..4 LOOP
  9      n := n - 1;
 10      DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
 11     END LOOP;
 12  END;
 13  /
+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645

PL/SQL procedure successfully completed.

SQL>

Oracle PL/SQL - PLS_INTEGER Subtype: SIMPLE_INTEGER

Introduction

SIMPLE_INTEGER Overflow

Demo

Related Topic