Table FUNctions: select a graph in SQL

You may seen some enthousiastic ramblings on Table Functions in earlier posts I have written. This is another one. With approximately 50 lines of code, you can select crude graphs for any query. For example, a graph that indicates the total salary sum for the three departments in the SCOTT schema:

The underlying query to get this ‘graph’ is the following. Graph is a Table Function that is invoked with a CURSOR expression. Any query will do, as long as it returns two columns: the first a varchar2 – that is the label that is printed along the horizontal axis; the second column must be a numer – that is the value positioned against the vertical axis.

Other parameters allow us to specify the range for the vertical axis (the maximum and minimum value to display), the legends for both axes, the width of the horizontal labels and the height of the graph (in number of rows).

Another example is the following graph that displays all weekdays and for each weekday the number of employees we hired on that day (taken from hiredate in the emp table):

Here again we see the query underpinning the graph, returning a label (the weekday) and the value (the count). We also specify the graph range (between 0 and 5), the legend: axis-titles , the height of the graph (=5) and the width of each cell (=10),

Of course we can make this function much more fancy – to display other markers than just *, to attempt to draw a line, to allow multiple values per label, to use a second vertical axis, etc. For now we have enough to demonstrate the concept and once again the power of table functions. The code for the function graph is listed below:

create type string_table is table of varchar2(2000)
/
create type values_tbl is table of number
/

create or replace function graph
( p_src in sys_refcursor
, p_title  in varchar2 default null
, p_labels_legend in varchar2 default null
, p_values_legend in varchar2 default null
, p_min in number default null
, p_max in number default null
, p_integer_values_only in varchar2 default 'N'
, p_graph_height in number default null
, p_label_width in number default null
) return string_table
is
   l_rows integer:=nvl(p_graph_height, 20);
   l_cell_width integer:=nvl(p_label_width, 5);
   l_graph_width integer:=200;
   retval string_table:= string_table();
   l_labels string_table;
   l_values values_tbl;
   l_min number;
   l_max number;
   l_row integer;
begin
   fetch p_src bulk collect into l_labels, l_values;
   -- get max and min
   select min(column_value)
   ,      max(column_value)
   into   l_min
   ,      l_max
   from   table( cast(l_values as values_tbl))
   ;
   l_min:= nvl(p_min, l_min);
   l_max:= nvl(p_max, l_max);
   retval.extend;
   retval(retval.last):= p_values_legend;
   for rows in 1..l_rows+1 loop
     retval.extend;
     if p_integer_values_only='N' or ((l_max - (rows-1)*(l_max-l_min)/l_rows) = round(l_max - (rows-1)*(l_max-l_min)/l_rows))
     then
       retval(retval.last):= rpad(to_char(l_max - (rows-1)*(l_max-l_min)/l_rows),10) ||rpad('  |',l_graph_width);
     else
       retval(retval.last):= rpad('. ',10) ||rpad('  |',l_graph_width);
     end if;
   end loop;
   -- calculate row for a value: l_rows - (value - l_min)/(l_max - l_min)*l_rows + 1     (e.g. max = 4500, min = 500, l_rows = 20; value = 1000 )
   for value in  1..l_values.count loop
     l_row:= l_rows - round((l_values(value) - l_min)/(l_max - l_min)*l_rows) + 2; -- the first rows holds the values legend

     retval(l_row):= rpad( substr(retval(l_row), 1,13 + l_cell_width* (value -1))||rpad('*',l_cell_width), l_graph_width);
   end loop;
   retval.extend;
   retval(retval.last):= rpad('-',10+l_graph_width,'-');
   retval.extend;
   retval(retval.last):= rpad(p_labels_legend,10+l_graph_width,' ');
   for label in  1..l_labels.count loop
     retval(retval.last):= substr(retval(retval.last), 1,13 + l_cell_width* (label -1))||rpad(substr(l_labels(label),1,l_cell_width),l_cell_width);
   end loop;

   return retval;
end;
/

Download the source for table function graph