Need to sort a list of random numbers in a stored procedure

Question

I am in need of help in creating a stored procedure that allows a user to input a list of random numbers and then sort them using the bubble sort algorithm. I am very new to programming and as well as PL/SQL. Any help would be much appreciated.

Below are the lines of code that I have so far:

CREATE OR REPLACE PROCEDURE test_BubbleSort (i_number IN number) AS

type l_array_type IS TABLE OF NUMBER(10);

l_temp  NUMBER;

l_array l_array_type := l_array_type();

BEGIN

  --Loop through numbers and re-arrange their order using bubble sort---

  FOR i in 1 .. l_array.Count - 1 LOOP

    FOR j IN 2 .. l_array.Count LOOP
      IF l_array(j) > l_array(j - 1) THEN
        l_temp := l_array(j - 1);
        l_array(j - 1) := l_array(j);
        l_array(j) := l_temp;
      END IF;
    END LOOP;
  END LOOP;

  --Print the newly sorted numbers user inputs 

  FOR i in REVERSE 1 .. l_array.COUNT LOOP

    dbms_output.put_line('The new sorted numbers are: ' || l_array(i));
  END LOOP;

END;

Answer 1

I don't think this is really what you want, but if you do actually want to generate random numbers yourself and just want the length of the list to be supplied by the user (as i_number) then you can loop to do that:

...
BEGIN

  --Generate some random numbers
  for i in 1..i_number loop
    l_array.extend;
    l_array(i) := dbms_random.value(1, 100);
  end loop;

  --Loop through numbers and re-arrange their order using bubble sort---

  FOR i in 1 .. l_array.Count - 1 LOOP
  ...

When called with an i_number parameter value of 5 that might give:

The new sorted numbers are: 10
The new sorted numbers are: 55
The new sorted numbers are: 60
The new sorted numbers are: 74
The new sorted numbers are: 87

The parameters to the dbms_random.value() call are restricting the range of 'random' numbers that are generated.