Permalink
Find file
145716d Dec 29, 2015
180 lines (161 sloc) 5.56 KB
-- ---------------------------------------------------------------------------------------------------
--
-- Script: counter.sql
--
-- Author: Adrian Billington
-- www.oracle-developer.net
--
-- Description: A deliberately simple package to keep a counter running. Useful for investigations
-- and instrumentation.
--
-- Note the PUBLIC synonym and grant at the end of the source code.
--
-- Usage: Usage is very simple.
--
-- a) Initialise a counter
-- --------------------
--
-- BEGIN
-- counter.initialise(); --<-- starts at 0
-- counter.initialise(p_counter => 100); --<-- starts at 100
-- END;
-- /
--
-- b) Increment a counter
-- -------------------
--
-- BEGIN
-- counter.increment(); --<-- increment by 1
-- counter.increment(p_increment => 50); --<-- increment by 50
-- END;
-- /
--
-- c) Increment a counter and retrieve the latest counter value in SQL
-- ----------------------------------------------------------------
--
-- SELECT counter.incrementf() AS counter
-- FROM some_table
-- WHERE ...
--
-- Note that this function is called INCREMENTF because the INCREMENT
-- identifier used for the corresponding procedure is a reserved word
-- and can't be used in SQL unless in double-quotes (i.e.
-- counter.increment doesn't work but counter."INCREMENT" does).
--
-- d) Increment a counter and retrieve the latest counter value in PL/SQL
-- -------------------------------------------------------------------
--
-- DECLARE
-- v_counter PLS_INTEGER;
-- BEGIN
-- DBMS_OUTPUT.PUT_LINE(counter.incrementf()); --<-- direct usage
-- v_counter := counter.incrementf(); --<-- assignment
-- END;
-- /
--
-- e) Show the counter with a message in PL/SQL
-- -----------------------------------------
-- BEGIN
-- counter.show(p_msg => 'My counter is'); --<-- resets the counter
-- counter.show(p_msg => 'My counter is', p_reset => FALSE); --<-- retains the counter
-- END;
-- /
--
-- f) Retrieve the counter into a variable
-- ------------------------------------
-- DECLARE
-- n PLS_INTEGER;
-- BEGIN
-- ...
-- n := counter.show(); --<-- resets the counter
-- n := counter.show(p_reset => FALSE); --<-- retains the counter
-- ...
-- END;
-- /
--
-- g) Reset the counter
-- -----------------
-- BEGIN
-- counter.reset();
-- END;
-- /
--
-- ---------------------------------------------------------------------------------------------------
CREATE PACKAGE counter AS
PROCEDURE initialise(
p_counter IN PLS_INTEGER DEFAULT 0
);
PROCEDURE increment(
p_increment IN PLS_INTEGER DEFAULT 1
);
FUNCTION incrementf(
p_increment IN PLS_INTEGER DEFAULT 1
) RETURN PLS_INTEGER;
PROCEDURE show(
p_msg IN VARCHAR2 DEFAULT NULL,
p_reset IN BOOLEAN DEFAULT TRUE
);
FUNCTION show(
p_reset IN BOOLEAN DEFAULT TRUE
) RETURN PLS_INTEGER;
PROCEDURE reset;
END counter;
/
CREATE PACKAGE BODY counter AS
g_counter PLS_INTEGER := 0;
--------------------------------------------------------
PROCEDURE initialise(
p_counter IN PLS_INTEGER DEFAULT 0
) IS
BEGIN
g_counter := NVL(p_counter,0);
END initialise;
--------------------------------------------------------
PROCEDURE increment(
p_increment IN PLS_INTEGER DEFAULT 1
) IS
BEGIN
g_counter := g_counter + NVL(p_increment,1);
END increment;
--------------------------------------------------------
FUNCTION incrementf(
p_increment IN PLS_INTEGER DEFAULT 1
) RETURN PLS_INTEGER IS
BEGIN
increment(p_increment);
RETURN show(FALSE);
END incrementf;
--------------------------------------------------------
FUNCTION show(
p_reset IN BOOLEAN DEFAULT TRUE
) RETURN PLS_INTEGER IS
v_counter PLS_INTEGER := g_counter;
BEGIN
IF p_reset THEN
counter.reset();
END IF;
RETURN v_counter;
END show;
--------------------------------------------------------
PROCEDURE show(
p_msg IN VARCHAR2 DEFAULT NULL,
p_reset IN BOOLEAN DEFAULT TRUE
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
CASE
WHEN p_msg IS NOT NULL
THEN CHR(10) || p_msg || ': '
END ||
show(p_reset)
);
END show;
--------------------------------------------------------
PROCEDURE reset IS
BEGIN
g_counter := 0;
END reset;
END counter;
/
CREATE OR REPLACE PUBLIC SYNONYM counter FOR counter;
GRANT EXECUTE ON counter TO PUBLIC;