stop_watch : Utility Package « PL SQL « Oracle PL / SQL






stop_watch

 
SQL>
SQL> CREATE OR REPLACE PACKAGE stop_watch AS
  2     pv_start_time_num     PLS_INTEGER;
  3     pv_stop_time_num      PLS_INTEGER;
  4     pv_last_stop_time_num PLS_INTEGER;
  5  PROCEDURE start_timer;
  6  PROCEDURE stop_timer;
  7  END stop_watch;
  8  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY stop_watch AS
  2  PROCEDURE start_timer AS
  3  BEGIN
  4     pv_start_time_num     := DBMS_UTILITY.GET_TIME;
  5     pv_last_stop_time_num := pv_start_time_num;
  6  END start_timer;
  7
  8  PROCEDURE stop_timer AS
  9  BEGIN
 10     pv_stop_time_num := DBMS_UTILITY.GET_TIME;
 11     DBMS_OUTPUT.PUT_LINE('Total Time Elapsed: ' ||
 12        TO_CHAR((pv_stop_time_num - pv_start_time_num)/100,
 13        '999,999.99') || ' sec   Interval Time: ' ||
 14        TO_CHAR((pv_stop_time_num - pv_last_stop_time_num)/100,
 15        '99,999.99') || ' sec');
 16     pv_last_stop_time_num := pv_stop_time_num;
 17  END stop_timer;
 18  END;
 19  /

Package body created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_if (p_condition_num NUMBER) AS
  2     lv_temp_num         NUMBER := 0;
  3     lv_temp_cond_num    NUMBER := p_condition_num;
  4  BEGIN
  5     stop_watch.start_timer;
  6     FOR lv_count_num IN 1..1000 LOOP
  7        IF lv_temp_cond_num = 1 THEN
  8           lv_temp_num := lv_temp_num + 1;
  9        ELSIF lv_temp_cond_num = 2 THEN
 10           lv_temp_num := lv_temp_num + 1;
 11        ELSIF lv_temp_cond_num = 3 THEN
 12           lv_temp_num := lv_temp_num + 1;
 13        ELSIF lv_temp_cond_num = 4 THEN
 14           lv_temp_num := lv_temp_num + 1;
 15        ELSIF lv_temp_cond_num = 5 THEN
 16           lv_temp_num := lv_temp_num + 1;
 17        ELSIF lv_temp_cond_num = 6 THEN
 18           lv_temp_num := lv_temp_num + 1;
 19        ELSIF lv_temp_cond_num = 7 THEN
 20           lv_temp_num := lv_temp_num + 1;
 21        ELSE
 22           lv_temp_num := lv_temp_num + 1;
 23        END IF;
 24     END LOOP;
 25     stop_watch.stop_timer;
 26  END;
 27  /

Procedure created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>

 








Related examples in the same category

1.Debug package
2.Debug package based on UTL_FILE
3.This object type represents a point on a Cartesian grid.
4.Debug package: inserts into a test table
5.Debug package: uses DBMS_OUTPUT.
6.time the performance enhancements of native dynamic SQL.