Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T8»

Revisió del 18:58, 28 abr 2014

T8- Cursors explícits

• Exercici 1

Previ: buida la taula TOP_DOGS.

SQL> DELETE

2 FROM TOP_DOGS;

Crea un bloc pl/sql que determini els n empleats amb sous més alts (suposeu que no hi ha dos empleats o més amb el mateix sou):

a. obtenir un numero "n" entrat per teclat per l'usuari amb un paràmetre de substitució.

b. en un bucle, obtenir els cognoms i sous d'aquests "n" empleats de la taula EMP

c. guarda aquestes dades en la taula TOP_DOGS.

d. Suposa que no hi ha dos empleats amb al mateix sou.

e. comprova varis casos: n=0 i n més gran que el número màxim d'empleats.

SET SERVEROUTPUT ON;

SET VERIFY OFF;

ACCEPT p_num PROMPT 'Introdueixi el número de empleats amb sou més alt: '

DECLARE

v_num NUMBER(3) :=&p_num;

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

--declarem un cursor que guardarà totes les dades de la taula empleats

CURSOR emp_cursor IS

SELECT ename, sal

FROM emp

WHERE sal IS NOT NULL

ORDER BY sal DESC;

BEGIN

OPEN emp_cursor; --obrim el cursor, aquest es posiciona apuntant el primer registre del cursor

FETCH emp_cursor INTO v_ename, v_sal; --carreguem el valor de la primera fila en les variables v_ename i v_sal, el cursor se situa

--en el seguent registre

WHILE emp.cursor%ROWCOUNT <= v_num AND emp_curosr%FOUND LOOP -- mentre recuperem menys registres dels demanats i quedin registres

INSERT INTO top_dogs VALUES (v_ename,v_sal); --inserim una nova fila amb el valors carregats

FETCH emp_cursor INTO v_ename, v_sal; --fem que el cursor carregui de nou i passi al següent registre

END LOOP;

CLOSE emp_cursor;--tanquem el cursor, alliberem memòria

COMMIT;

END;

/

SQL> SELECT *

2 FROM TOP_DOGS;

• Exercici 2

Considere el caso de que varios empleados tengan el mismo salario. Al igual que el ejercicio anterior cree un bloque plsql que determine los n empleados con sueldos mas altos pero en este caso si se muestra una persona con el mismo sueldo se tendran que mostrar todos los que tengan este sueldo.

a) Si el usuario introduce n=2 deben aparecer tres registros (king, ford i scott, estos últimos tienen el mismo sueldo.

b) Si el usuario introduce n=3 deben aparecer 4 registros (king, ford i scott i jones)

c) vacía todas la filas de la tabla TOP_DOGS antes de probar el ejercicio.

SET SERVEROUTPUT ON; SET VERIFY OFF; ACCEPT num_emp PROMPT 'Introdueixi el número de empleats amb sou més alt: ' DECLARE

  v_numemp number(3) :=&num_emp;    
  v_sal emp.sal%TYPE :=-1;

  CURSOR c_emp IS SELECT * FROM emp ORDER BY NVL(sal,0) DESC;

BEGIN

  DELETE FROM top_dogs; --buidem la taula top_dogs
  FOR v_emp IN c_emp LOOP
       DBMS_OUTPUT.PUT_LINE(v_numemp);    
       EXIT WHEN  (c_emp%ROWCOUNT > v_numemp AND v_sal<>v_emp.sal) OR (v_numemp=0);
       INSERT INTO top_dogs VALUES (v_emp.ename,v_emp.sal);    
         IF (v_sal=v_emp.sal) THEN v_numemp:=v_numemp+1;
            ELSE  v_sal:=v_emp.sal;
         END IF;
    END LOOP;
END;

/

--Versión 2 de este ejericio

ACCEPT p_num PROMPT 'introduce el máximo numero de sueldos distintos: '

DECLARE

  v_num  NUMBER(3):= &p_num;

  v_ename  emp.ename%TYPE;

  v_salari_actual  emp.sal%TYPE;

  v_ultimo_salario  emp.sal%TYPE:= 1;

  CURSOR  emp_cursor IS  SELECT ename, sal FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC;

BEGIN

  open emp_cursor;

  FETCH emp_cursor INTO v_ename, v_salari_actual;

  WHILE (emp_cursor%ROWCOUNT <= v_num OR v_salari_actual=v_ultimo_salario) AND emp_cursor%FOUND LOOP

             INSERT INTO top_dogs(name, salary)

             VALUES (v_ename,v_salari_actual);

              v_ultimo_salario:=v_salari_actual;

             FETCH emp_cursor INTO v_ename, v_salari_actual;

  END LOOP;

  close emp_cursor;

COMMIT;

END;

SET SERVEROUTPUT ON; SET VERIFY OFF; ACCEPT num_emp PROMPT 'Introdueixi el número de empleats amb sou més alt: ' DECLARE

  v_numemp number(3) :=&num_emp;    

  --declarem un cursor que guardarà totes les dades de la taula empleats
  CURSOR c_emp IS SELECT * FROM emp ORDER BY sal DESC;

  --declarem una variable registre capaç de guardar totes les dades de una fila del cursor anterior

  v_emp c_emp%ROWTYPE;

BEGIN

  OPEN c_emp; --obrim el cursor, aquest es posiciona apuntant el primer registre del cursor
  FETCH c_emp INTO v_emp; --carreguem el valor de la primera fila en la variablle c_emp, el cursor se situa

                          --en el seguent registre
   
   FOR i IN 1..v_numemp LOOP --usem un bucle per repetir la càrrega tantes vegades com v_numemp
       IF c_emp%NOTFOUND THEN EXIT; --condició de sortida del for, si c_emp està buida, el fetch no

                                    --retorna cap valor
       END IF;
  
       INSERT INTO top_dogs VALUES (v_emp.ename,v_emp.sal); --inserim una nova fila amb el valors carregats
       FETCH c_emp INTO v_emp; --fem que el cursor carregui de nou i passi al següent registre
    END LOOP;
   
   CLOSE c_emp;--tanquem el cursor, alliberem memòria

END;

• Exercici 2

Considere el caso de que varios empleados tengan el mismo salario. Al igual que el ejercicio anterior cree un bloque plsql que determine los n empleados con sueldos mas altos pero en este caso si se muestra una persona con el mismo sueldo se tendran que mostrar todos los que tengan este sueldo.

a) Si el usuario introduce n=2 deben aparecer tres registros (king, ford i scott, estos últimos tienen el mismo sueldo.

b) Si el usuario introduce n=3 deben aparecer 4 registros (king, ford i scott i jones)

c) vacía todas la filas de la tabla TOP_DOGS antes de probar el ejercicio.

SET SERVEROUTPUT ON; SET VERIFY OFF; ACCEPT num_emp PROMPT 'Introdueixi el número de empleats amb sou més alt: ' DECLARE

  v_numemp number(3) :=&num_emp;    
  v_sal emp.sal%TYPE :=-1;

  CURSOR c_emp IS SELECT * FROM emp ORDER BY NVL(sal,0) DESC;

BEGIN

  DELETE FROM top_dogs; --buidem la taula top_dogs
  FOR v_emp IN c_emp LOOP
       DBMS_OUTPUT.PUT_LINE(v_numemp);    
       EXIT WHEN  (c_emp%ROWCOUNT > v_numemp AND v_sal<>v_emp.sal) OR (v_numemp=0);
       INSERT INTO top_dogs VALUES (v_emp.ename,v_emp.sal);    
         IF (v_sal=v_emp.sal) THEN v_numemp:=v_numemp+1;
            ELSE  v_sal:=v_emp.sal;
         END IF;
    END LOOP;
END;

/

--Versión 2 de este ejericio

ACCEPT p_num PROMPT 'introduce el máximo numero de sueldos distintos: '

DECLARE

  v_num  NUMBER(3):= &p_num;

  v_ename  emp.ename%TYPE;

  v_salari_actual  emp.sal%TYPE;

  v_ultimo_salario  emp.sal%TYPE:= 1;

  CURSOR  emp_cursor IS  SELECT ename, sal FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC;

BEGIN

  open emp_cursor;

  FETCH emp_cursor INTO v_ename, v_salari_actual;

  WHILE (emp_cursor%ROWCOUNT <= v_num OR v_salari_actual=v_ultimo_salario) AND emp_cursor%FOUND LOOP

             INSERT INTO top_dogs(name, salary)

             VALUES (v_ename,v_salari_actual);

              v_ultimo_salario:=v_salari_actual;

             FETCH emp_cursor INTO v_ename, v_salari_actual;

  END LOOP;

  close emp_cursor;

COMMIT;

END;