Running totals in SQL*Plus: a 7.1 solution

The question of how to do running totals' in SQL*plus comes up from time to time on this group. and the normal (no pun intended) response requires tables to be joined to themselves and relatively expensive SQL.

With the advent of Oracle 7.1, and PL/SQL 2.1 here is an alternative that makes uses of a SQL-callable packaged function.

create or replace package accumulate as

    function get_running(new_value in number) return number;     pragma restrict_references(get_running, WNDS, RNDS);

    procedure reset_total;
end accumulate;
/

create or replace package body accumulate as running_total number;

function get_running(new_value in number) return number is begin

    running_total := running_total + new_value;     return running_total;
end get_running;

procedure reset_total is
begin

    running_total := 0;
end reset_total;

end accumulate;
/

create table t1 (n1 number);

insert into t1 values (4);
insert into t1 values (3);
insert into t1 values (2);
insert into t1 values (5);
insert into t1 values (2);
insert into t1 values (4);
insert into t1 values (1);

execute accumulate.reset_total;
select n1, accumulate.get_running(n1) from t1;

        N1 ACCUMULATE.GET_RUNNING(N1)                                           
---------- --------------------------                                           
         4                          4                                           
         3                          7                                           
         2                          9                                           
         5                         14                                           
         2                         16                                           
         4                         20                                           
         1                         21                                           

7 rows selected.

Note particularly the need to precede the query with a 'reset_total' to set the running total to zero. I had to do this becuase I could not think of a way of letting the function know that it was handling the first row returned. (NB ROWNUM does not give a general solution).

--
Jonathan Lewis
jonathan_at_jlcomp.demon.co.uk