pl/sql bubble sort

okay, I am beating myself up over this. I am need to load a array in people last name stored in a table. Then sort the last names and print them out in alphabetical order. This must be done using the bubble sort algorithm.

here is what I have so far

CREATE OR REPLACE PROCEDURE TEAM_TABLE_SORT AS
  TYPE player_Name_type IS TABLE OF databasename.team.player%type
  INDEX BY PLS_INTEGER ;
  player_name player_Name_type;
  i integer := 1;
  temp integer;

BEGIN

  FOR player_names IN (SELECT * FROM marshall.team )
  LOOP
    player_name(i) := player_names.player;
    DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) || player_name(i) ) ;
    i := i + 1 ;
  END LOOP

All this really does is print out the names. I cannot get it to sort. I am not try thing this

TYPE player_Name_type IS TABLE OF  %type INDEX BY varchar2(20) ;
aux player_Name_type;
i integer := 1;
v_current is开发者_开发知识库 table of aux
swapped BOOLEAN := TRUE;

BEGIN

  FOR aux IN (SELECT * FROM )
  LOOP
    DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) || aux.player);
    i := i + 1 ;
  END LOOP;

  v_current := aux.first;
  WHILE(swapped)
  LOOP
    swapped := FALSE;

    FOR I IN 1..(aux.count-2) LOOP
      IF aux(i) > aux(I+1) THEN
         v_current := aux(i+1);
         aux(I+1) := aux(i);
         aux(i) :=  v_current;
      END IF;
      swapped := TRUE;

    END LOOP;

  END LOOP;

FOR aux IN (SELECT * FROM    LOOP

  DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) ||aux.player);
  i := i + 1 ;
END LOOP;

This should be what you are looking for. Note that it is better to type the variables/collections off of the tables like you have in your example. I just used generic versions since I don't have your tables to work with. If you don't understand how this is working, feel free to ask. I am guessing this is homework (who else would bubble sort in Oracle), so the point of the assignment is for you to understand it, not just to get it right. :)

DECLARE
  coll    DBMS_SQL.VARCHAR2A;
  swapped BOOLEAN;
  tmp     VARCHAR2(10);
BEGIN
  /*
    Generate 10 random strings and collect them into our collection
    Note: you would replace this with your query on marshall.team
  */
  select dbms_random.string('l',10) rand_string
  BULK COLLECT INTO coll
  from dual
  connect by level <= 10;


  /*
    At this point, all of the rows we need are in our collection
    so there is no need to go back to the table anymore.  Now onto the...

    Bubble sort.. walk through the collection swapping elements until
    we make a pass where no swapping takes place
  */
  LOOP

   swapped := false;

   FOR i IN 2 .. coll.LAST
   LOOP

     IF coll(i-1) > coll(i)
     THEN
       -- swap records
       tmp := coll(i);
       coll(i) := coll(i-1);
       coll(i-1) := tmp;

       /*
         Mark that swap has taken place.  note we mark as true only inside
         the if block, meaning a swap really did take place
       */ 
       swapped := true;

      END IF;

   END LOOP;

   -- If we passed through table without swapping we are done, so exit
   EXIT WHEN NOT swapped;

  END LOOP; 

  /*
    Now print out records to make sure they are in order.  Again notice
    how we are just referencing the (now sorted) collection and not going
    back to the table again
  */
  FOR i in coll.FIRST .. coll.LAST
  LOOP

    dbms_output.put_line(coll(i));

  END LOOP;

END;
/

You generally want to use an ORDER BY in the source query.

You can use a VARCHAR2 index-by table to do sorting as well though.

DECLARE
  CURSOR c_1 is
     SELECT table_name, num_rows FROM user_tables order by num_rows;
  TYPE typ_tab IS TABLE OF c_1%rowtype INDEX BY user_tables.table_name%type;
  t_tab typ_tab;
  v_str user_tables.table_name%type;
BEGIN
  FOR c_rec IN c_1 LOOP
    t_tab(c_rec.table_name) := c_rec;
  END LOOP;
  v_str := t_tab.first;
  WHILE v_str IS NOT NULL LOOP
    dbms_output.put_line(t_tab(v_str).table_name||':'||t_tab(v_str).num_rows);
    v_str := t_tab.next(v_str);
  END LOOP;
END;
/

The second chunk of code you have posted looks like a valid implementation of the bubble sort algorithm. The reason it appears not to work is because of that final loop. Instead of printing out the sorted array you are repopulating it with randomly-ordered data from your table.

So, just change the final loop:

FOR i IN 1..aux.count()
LOOP    
    DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) ||aux(i).player);
END LOOP; 

You can also use the example of the Bubble Sort in PL / SQL on this website:

http://www.oratechinfo.co.uk/oo.html#bubble_sort