How to make bubbles or homemade ORDER BY

In one project, a colleague worked on the "dictionary" function. This function was to retrieve records from the configuration table, sort them and put them into an associative array. It looked quite simple.

Just ordinary:

select * 
bulk collect into ATable
from table
ORDER by colum.

I waited a few days. Nothing. I've heard - "I'm working on it!". I wondered what the problem was that it took so long? And finally it is! Function ready for code review.

I sit down. I don't believe.

Instead of literally a few lines of code, I have dozens of them!

But why?

Well, after a while of analysis, I discovered that my colleague abandoned the good old ORDER BY and implemented his own sorting. Bubble ....

I appreciate the work and knowledge. However,hardly anything annoys me like complicating simple matters! Also bubble sorting came out with a bang and ORDER BY was restored.

But all in all I thought I have never written any sort algorithm. So I decided that I would try. A colleague spent a good week over it, so I prepared food, coffee, tea and a pillow.

And all for nothing. 15 minutes of fun, that's all the bubble sorting algorithm has to offer.

set serveroutput on
declare

type tabNum is table of number index by pls_integer;
tNum tabNum;

nTabCnt number;
nCurVal number;
nNextVal number;

begin

tnum(1) := 3;
tnum(2) := 2;
tnum(3) := 1;

nTabCnt := tNum.count;

for z in 1..nTabCnt-1 loop

    for i in 1..nTabCnt-1 loop
        nCurVal :=tnum(i);
        nNextVal := tNum(i+1);
        
        if nCurVal > nNextVal then
           tnum(i) :=   nNextVal;
           tnum(i+1) := nCurVal;
        end if;
    
    end loop;
    
end loop;

for z in 1..nTabCnt loop
    dbms_output.put_line(z || ' ' || tnum(z));
end loop;

end;

Select from HR.EMPLOYEES table

set serveroutput on
declare

type tabNum is table of number index by pls_integer;
tNum tabNum;

nTabCnt number;
nCurVal number;
nNextVal number;

begin

tnum(1) := 3;
tnum(2) := 2;
tnum(3) := 1;

nTabCnt := tNum.count;

for z in 1..nTabCnt-1 loop

    for i in 1..nTabCnt-1 loop
        nCurVal :=tnum(i);
        nNextVal := tNum(i+1);
        
        if nCurVal > nNextVal then
           tnum(i) :=   nNextVal;
           tnum(i+1) := nCurVal;
        end if;
    
    end loop;
    
end loop;

for z in 1..nTabCnt loop
    dbms_output.put_line(z || ' ' || tnum(z));
end loop;

end;

All in all fun. Only short: D