Today I was working on a data table, output in HTML generated by PL/SQL and I needed to implement a feature that would allow the user to sort the table based on the column header they clicked on.
Basically I have a procedure which takes a parameter (
p_sort_by) and establishes which column to sort by based on this parameter. It does this using the
DECODE function, which has similar functionality to
IF-THEN-ELSE statements. I could have alternatively used
CASE statements here, which are easier to understand, but they are not supported in older versions of Oracle. An example of this procedure is:
PROCEDURE display_table(p_sort_by IN NUMBER DEFAULT 1) IS CURSOR c_get_data IS SELECT name, city, dept, dob FROM person ORDER BY DECODE(p_sort_by, 1, name, 2, city, 3, dept, 4, to_char(dob, 'yyyymmdd') ); BEGIN FOR x IN c_get_data LOOP htp.p('<tr>'); htp.p('<td>'||x.name||'</td>'); htp.p('<td>'||x.city||'</td>'); htp.p('<td>'||x'dept||'</td>'); htp.p('<td>'||x.dob||'</td>'); htp.p('</tr>'); END LOOP; END display_table;
To sort the table by name, pass
1 as a parameter to the
display_table procedure. To sort by city, pass
2, and so on.