-- Oracle 11g Simple_Integer -- The variable declared as SIMPLE_INTEGER has to initialized with -- non-null value. It is a predefined subtype of PLS_INTEGER with -- the same range of (-2,147,483,648 to 2,147,483,647) with a -- built-in NOT NULL constraint. It performs better than -- PLS_INTEGER as shown in the example below. -- If declared without a value (NULL), it results in PLS-00218 -- error (refer output - PLS_00218 Error) DECLARE v_num NUMBER := 0; v_max NUMBER := 10000000; v_pls PLS_INTEGER := 0; v_start PLS_INTEGER; -- cannot declare as SIMPLE_INTEGER v_end PLS_INTEGER; v_si SIMPLE_INTEGER := 0; BEGIN v_start := dbms_utility.get_time; dbms_output.put_line('-- NUMBER -----------'); dbms_output.put('Start Time -> ' ||TO_CHAR(v_start)); FOR idx IN 1..v_max LOOP v_num := v_num+idx; END LOOP; v_end := dbms_utility.get_time; dbms_output.put(' End Time -> ' ||TO_CHAR(v_end)); dbms_output.put_line(' Time Lapsed -> ' ||TO_CHAR(v_end-v_start)); dbms_output.put_line('-- PLS_INTEGER ------'); v_start := dbms_utility.get_time; dbms_output.put('Start Time -> ' ||TO_CHAR(v_start)); FOR idx IN 1..v_max LOOP v_pls := v_pls+1; END LOOP; v_end := dbms_utility.get_time; dbms_output.put(' End Time -> ' ||TO_CHAR(v_end)); dbms_output.put_line(' Time Lapsed -> ' ||TO_CHAR(v_end-v_start)); dbms_output.put_line('-- SIMPLE_INTEGER ---'); v_start := dbms_utility.get_time; dbms_output.put('Start Time -> ' ||TO_CHAR(v_start)); FOR idx IN 1..v_max LOOP v_si := v_si+1; END LOOP; v_end := dbms_utility.get_time; dbms_output.put(' End Time -> ' ||TO_CHAR(v_end)); dbms_output.put_line(' Time Lapsed -> ' ||TO_CHAR(v_end-v_start)); END; / Number, PLS_Integer, Simple_Integer Analysis
24082