M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T8
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;