codemonth.dk

One project every month - making stuff better ...

Topological sorting in PLSQL

For my testdata_ninja library, I recently created the field auto reference feature, where a value from one field can be used as an input to a generator for another field. Part of this feature involves parsing all columns and building a dependency list (Directed Acyclic Graph) sorting all columns correctly. Since everything in the library is build purely in plsql, I don't store anything in tables. So if my data is not in a table, I cannot use sql as one would normally do it. I couldn't find any code out there that did what I wanted, so like much of the other stuff I do, I had to build it myself.

So after a few attempts to get started I got around to actually doing some code that worked. For now it works for number values in the dependency list, but the next version will work on string values also. For a quick look at how it works, you can see the below example:


declare

  my_dependencies   topological_ninja.topo_dependency_list;
  my_order          topological_ninja.topo_number_list;

begin

  my_dependencies('1') := '7';
  my_dependencies('4') := '2';
  my_dependencies('8') := '1';
  my_dependencies('7') := '3';

  my_order := topological_ninja.f_s(dependency_list => my_dependencies);

  for i in 1..my_order.count loop
    dbms_output.put_line('#' || i || ': ' || my_order(i));
  end loop;

end;
/

#1: 3
#2: 2
#3: 7
#4: 1
#5: 4
#6: 8

PL/SQL procedure successfully completed.

Tagged in : PL/SQL