DECLARE
TYPE rec IS RECORD
(
ename emp.ename%TYPE,
sal emp.sal%TYPE
);
v_rec rec;
v_rec2 rec;
v_rec_temp rec;
TYPE assoc IS TABLE OF rec
INDEX BY PLS_INTEGER;
v_assoc assoc;
CURSOR c
IS
SELECT ename, sal FROM emp;
--elem emp.ename%TYPE;
v_sal NUMBER;
v_ename VARCHAR2 (100);
i NUMBER := 1;
BEGIN
DBMS_OUTPUT.put_line ('Origional List');
FOR d IN c
LOOP
v_rec.ename := d.ename;
v_rec.sal := d.sal;
v_assoc (i) := v_rec;
DBMS_OUTPUT.put_line (i || ': ' || v_rec.ename || ' ' || v_rec.sal);
i := i + 1;
END LOOP;
DBMS_OUTPUT.put_line ('Descending Order List');
FOR e IN 1 .. v_assoc.COUNT
LOOP
v_rec := v_assoc (e);
--DBMS_OUTPUT.put_line (e || '==>>' || v_rec.ename || ' -----'|| v_rec.sal );
FOR e2 IN e + 1 .. v_assoc.COUNT
LOOP
v_rec2 := v_assoc (e2);
--DBMS_OUTPUT.put_line (e || '====>>>>' || v_rec2.sal || '>' || v_sal);
IF v_rec2.sal > v_rec.sal
OR (v_rec2.sal = v_rec.sal AND v_rec2.ename < v_rec.ename)
THEN
v_rec_temp.ename := v_rec.ename;
v_rec_temp.sal := v_rec.sal;
v_assoc (e) := v_rec2;
v_assoc (e2) := v_rec_temp;
v_rec := v_assoc (e);
END IF;
END LOOP;
END LOOP;
FOR e IN 1 .. v_assoc.COUNT
LOOP
v_rec := v_assoc (e);
v_ename := v_rec.ename;
v_sal := v_rec.sal;
DBMS_OUTPUT.put_line (e || ' : ' || v_ename || ' ' || v_sal);
END LOOP;
END;
/*
Output:
Origional List
1: SMITH 800
2: ALLEN 1600
3: WARD 1250
4: JONES 2975
5: MARTIN 1250
6: BLAKE 2850
7: CLARK 2450
8: SCOTT 3000
9: KING 5000
10: TURNER 1500
11: ADAMS 1100
12: JAMES 950
13: FORD 3000
14: MILLER 1300
Descending Order List
1 : KING 5000
2 : FORD 3000
3 : SCOTT 3000
4 : JONES 2975
5 : BLAKE 2850
6 : CLARK 2450
7 : ALLEN 1600
8 : TURNER 1500
9 : MILLER 1300
10 : MARTIN 1250
11 : WARD 1250
12 : ADAMS 1100
13 : JAMES 950
14 : SMITH 800
*/
