Table FUNctions: select a graph in SQL

Lucas Jellema 2
0 0
Read Time:3 Minute, 35 Second

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

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts on “Table FUNctions: select a graph in SQL

  1. […] Lucas Jellema von amis technology corner beschreibt, wie man in SQL*Plus den Output ASCII-graphisch darstellen lassen kann. Absolut verrückt! Ich denke da an unsere alten Sharp PC-1403H zurück, auf deren Pixeldisplay die ganz Verwegenen auch Graphen ausgeben konnten. […]

Comments are closed.

Next Post

Oracle Open Source : SQuirreL

Not too long ago my colleague Zeger Hendrikse gave this presentation on Open Source Databases. Part of this presentation are the Tools available. One of the Tools he mentioned also has an Oracle Plugin available. So, continuing the story on Oracle and Open Source, may I present to you: SQuirreL […]
%d bloggers like this: