Oracle PL/SQL Tutorial/PL SQL Data Types/PLS INTEGER

PLS_INTEGER

The PLS_INTEGER datatype is used for declaring signed integer variables.

The PLS_INTEGER datatype stores values in the range -2,147,483,647 through 2,147,483,647.

The PLS_INTEGER datatype uses the native machine instructions for performing computations.

PLS_INTEGER calculations are much faster than BINARY_INTEGER calculations.

The Syntax for the PLS_INTEGER Datatype

   <source lang="sql">

variable_name PLS_INTEGER;</source>

PLS_INTEGER in action

   <source lang="sql">

SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2     v_count PLS_INTEGER := 1;
 3  BEGIN
 4     LOOP
 5        DBMS_OUTPUT.PUT_LINE("Ah -- Much better");
 6        v_count := v_count + 1;
 7        EXIT WHEN v_count = 20;
 8     END LOOP;
 9  END;
10  /

Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better Ah -- Much better PL/SQL procedure successfully completed. SQL></source>

Use PLS_INTEGER as the loop counter

   <source lang="sql">

SQL> SQL> SQL> DECLARE

 2     lv_counter_num       PLS_INTEGER := 0;
 3     lv_total_counter_num PLS_INTEGER := 0;
 4  BEGIN
 5     LOOP
 6        lv_counter_num       := lv_counter_num + 1;
 7        IF lv_counter_num >= 100000 THEN
 8           DBMS_OUTPUT.PUT_LINE("Processed 100,000 Records. Total " ||"Processed " || lv_total_counter_num);
 9           lv_total_counter_num := lv_total_counter_num +
10              lv_counter_num;
11           lv_counter_num := 0;
12           EXIT WHEN lv_total_counter_num >= 1000000;
13        END IF;
14     END LOOP;
15  END;
16  /

Processed 100,000 Records. Total Processed 0 Processed 100,000 Records. Total Processed 100000 Processed 100,000 Records. Total Processed 200000 Processed 100,000 Records. Total Processed 300000 Processed 100,000 Records. Total Processed 400000 Processed 100,000 Records. Total Processed 500000 Processed 100,000 Records. Total Processed 600000 Processed 100,000 Records. Total Processed 700000 Processed 100,000 Records. Total Processed 800000 Processed 100,000 Records. Total Processed 900000 PL/SQL procedure successfully completed. SQL></source>