-- TIMER object to simplify routine task of capturing start and end times and
-- displaying formatted results, e.g:
-- mytimer.SHOW('Elapsed so far');
-- might give
-- +0 00:00:05.15 Elapsed so far
--
-- Elapsed time is displayed as a constrained INTERVAL DAY(1) TO SECOND(2), which Oracle displays as
-- +[days] [hh]:[mi]:[ss.xx]  (where  'xx' represents two decimal places)
--
-- Description is displayed by SHOW() and can be set at any point i.e. via initialisation,
-- RESTART(), STOP(), RESUME() or SHOW().
--
-- William Robertson 22 Feb 2004, www.williamrobertson.net
--
-- 2004 02 29  Added RESUME() method and DBMS_APPLICATION_INFO calls.

DEFINE day_precision = 1
DEFINE second_precision = 2

CREATE OR REPLACE TYPE timer AS OBJECT
( start_time        TIMESTAMP
, end_time          TIMESTAMP
, description       VARCHAR2(1000)
, status            VARCHAR2(7)
, last_resumed_time TIMESTAMP
, accumulated       INTERVAL DAY TO SECOND
, CONSTRUCTOR FUNCTION timer
	( p_description VARCHAR2 DEFAULT NULL )
	RETURN SELF AS RESULT
, MEMBER PROCEDURE restart  -- Reset start time to now
	( p_description VARCHAR2 DEFAULT NULL )
, MEMBER PROCEDURE stop     -- Set stop time to now
	( p_description VARCHAR2 DEFAULT NULL )
, MEMBER PROCEDURE resume   -- Reset status to STARTED: undoes STOP without erasing START_TIME
	( p_description VARCHAR2 DEFAULT NULL )
, MEMBER FUNCTION elapsed   -- Return elapsed time as INTERVAL value
	RETURN INTERVAL DAY TO SECOND
, MEMBER PROCEDURE show     -- Display elapsed time with message
	( p_description VARCHAR2 DEFAULT NULL )
, STATIC FUNCTION version   -- Code repository version
	RETURN VARCHAR2
)
NOT FINAL
/

SHOW ERRORS

GRANT EXECUTE ON timer TO PUBLIC;
CREATE OR REPLACE PUBLIC SYNONYM TIMER FOR TIMER;

CREATE OR REPLACE TYPE BODY timer
AS
	CONSTRUCTOR FUNCTION timer
		( p_description VARCHAR2 DEFAULT NULL )
		RETURN SELF AS RESULT
	IS
	BEGIN
		-- RESTART() method reinitializes the timer (see below):
		RESTART(p_description);
		RETURN;
	END;

		
	MEMBER PROCEDURE restart
		( p_description VARCHAR2 DEFAULT NULL )
	IS
	BEGIN
		SELF.RESUME(p_description);

		SELF.start_time := SYSTIMESTAMP;
		SELF.last_resumed_time := NULL;
		SELF.accumulated := NULL;
		SELF.status := 'RUNNING';

		IF p_description IS NOT NULL THEN
			SELF.description := p_description;
			DBMS_APPLICATION_INFO.SET_ACTION(p_description);
		END IF;
	END restart;


	-- Return the amount of time elapsed since either START_TIME or LAST_RESUMED_TIME.
	-- If there is an ACCUMULATED interval, add that as well (timing was stopped and later resumed:
	-- the elapsed time is stored as ACCUMULATED at the STOP() event to allow it for a subsequent RESUME()).
	MEMBER FUNCTION elapsed
		RETURN INTERVAL DAY TO SECOND
	IS
		-- Capture time-sensitive information first:
		k_end_time CONSTANT TIMESTAMP := SYSTIMESTAMP;

		k_start_time CONSTANT TIMESTAMP :=
			CASE WHEN SELF.last_resumed_time IS NOT NULL THEN
				GREATEST(SELF.last_resumed_time,SELF.start_time)
			ELSE
				SELF.start_time
			END;

		k_base_elapsed CONSTANT INTERVAL DAY TO SECOND := k_end_time - k_start_time;
	BEGIN
		IF SELF.status = 'STOPPED' THEN
			-- Clock has stopped: accumulated value will not change until timing is resumed:
			RETURN SELF.accumulated;
		ELSE
			RETURN (k_end_time - k_start_time) + NVL(SELF.accumulated, INTERVAL '0' SECOND);
		END IF;
	END elapsed;


	-- Stop timer and calculate accumulated time.
	-- Amend description, if specified.
	-- Timer can later be restarted, or non-destructively resumed.
	MEMBER PROCEDURE stop
		( p_description VARCHAR2 DEFAULT NULL )
	IS
		k_elapsed CONSTANT INTERVAL DAY TO SECOND := SELF.elapsed();
	BEGIN
		-- Check that timer has not already stopped:
		-- If so, ignore this call except to change description.
		IF SELF.status = 'STOPPED' THEN
			NULL;
		ELSE
			SELF.end_time := SYSTIMESTAMP; 
			SELF.accumulated := k_elapsed; 
			SELF.status := 'STOPPED';
		END IF;

		IF p_description IS NOT NULL THEN
			SELF.description := p_description;
		END IF;
	END stop;


	MEMBER PROCEDURE resume
		( p_description VARCHAR2 DEFAULT NULL )
	IS
	BEGIN
		SELF.last_resumed_time := SYSTIMESTAMP;
		SELF.status := 'RUNNING';

		IF p_description IS NOT NULL THEN
			SELF.description := p_description;
			DBMS_APPLICATION_INFO.SET_ACTION(p_description);
		END IF;
	END resume;


	MEMBER PROCEDURE show
		( p_description VARCHAR2 DEFAULT NULL )
	IS
		k_elapsed CONSTANT INTERVAL DAY(&&day_precision) TO SECOND(&&second_precision) := SELF.ELAPSED();
	BEGIN
		IF p_description IS NOT NULL THEN
			SELF.description := p_description;
		END IF;

		DBMS_OUTPUT.PUT_LINE(k_elapsed || ' ' || SELF.description);
	END show;


	STATIC FUNCTION version
		RETURN VARCHAR2
	IS
	BEGIN
		RETURN '1.1';
	END version;
END;
/

show errors

set doc off 

pro Demo using TIMER type:
set echo on serverout on size 1000000 format wrapped

DECLARE
	k_iterations CONSTANT PLS_INTEGER := 4;

	v_interval      INTERVAL DAY(&day_precision) TO SECOND(&second_precision);

	v_overall_timer TIMER := NEW TIMER('Overall time for demo');
	v_split_timer   TIMER := NEW TIMER('Add times for two events using STOP and RESUME methods'); 
	v_loop_timer    TIMER := NEW TIMER('Loop with ' || k_iterations || ' iterations');
	v_detail_timer  TIMER := NEW TIMER(); 
BEGIN
	DBMS_OUTPUT.PUT_LINE('...demo pausing 1 second...');
	DBMS_LOCK.SLEEP(1);

	v_split_timer.STOP();
	v_split_timer.SHOW('v_split_timer stopped');

	DBMS_OUTPUT.NEW_LINE();

	DBMS_LOCK.SLEEP(1);

	FOR i IN 1..k_iterations LOOP
		v_detail_timer.RESTART('Loop iteration #' || i);
		DBMS_LOCK.SLEEP(1);
		v_detail_timer.STOP();
		v_detail_timer.SHOW();  -- Can specify message on NEW, RESTART or SHOW
		v_overall_timer.SHOW('running time so far');
	END LOOP;

	DBMS_OUTPUT.NEW_LINE();

	v_loop_timer.SHOW();

	DBMS_OUTPUT.NEW_LINE();

	v_split_timer.RESUME();
	v_split_timer.SHOW('v_split_timer resumed after the loop.');

	DBMS_OUTPUT.NEW_LINE();
	DBMS_OUTPUT.PUT_LINE('...demo pausing 1 second...');
	DBMS_LOCK.SLEEP(1);

	v_interval := v_detail_timer.ELAPSED();
	DBMS_OUTPUT.PUT_LINE
	( CHR(10)|| 'Captured time of last loop iteration "' ||
	  v_detail_timer.description || '": ' || v_interval );

	DBMS_OUTPUT.NEW_LINE();
	DBMS_OUTPUT.PUT_LINE('...demo pausing 1 second...');

	DBMS_OUTPUT.NEW_LINE();
	v_split_timer.SHOW('v_split_timer resumed timing from where it left off.');

	v_overall_timer.SHOW('Entire demo');
END;
/

set echo off