Pages

Thursday, September 22, 2011

Write a pl/sql block to display names of employees with sal in descending order without using order by

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
*/

No comments:

Post a Comment