Timer package : Utility Package « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Package

SQL>
SQL> CREATE OR REPLACE PACKAGE timer
  2  IS
  3     PROCEDURE capture;
  4     PROCEDURE show_elapsed;
  5  END timer;
  6  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY timer
  2  IS
  3     last_timing INTEGER := NULL;
  4
  5     PROCEDURE capture IS
  6     BEGIN
  7       last_timing := DBMS_UTILITY.GET_TIME;
  8     END;
  9
 10     PROCEDURE show_elapsed IS
 11     BEGIN
 12        DBMS_OUTPUT.PUT_LINE ('Elapsed time: '||(DBMS_UTILITY.GET_TIME - last_timing)/100);
 13     END;
 14  END timer;
 15  /

Package body created.

SQL>
SQL> DECLARE
  2     myname VARCHAR2(100);
  3  BEGIN
  4     timer.capture;
  5     FOR i IN 1 .. 1000
  6     loop
  7        myname := USER;
  8     end loop;
  9     timer.show_elapsed;
 10     end;
 11  /
Elapsed time: .09

PL/SQL procedure successfully completed.

SQL>