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 següent 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
Considera el cas de que varis empleats tinguin el mateix salari. Al igual que l'exercici anterior crea un bloc pl/sql que determini els n empleats amb sous més alts, però en aquest cas si se cal mostrar totes les persones que tinguin un mateix sou.
a. Si l'usuari introdueix n=2 han d'aparèixer tres registres (king, ford i scott, aquest últims tenen el mateix sou.
b. Si l'usuari introdueix n=3 han d'aparèixer 4 registres (king, ford i scott i jones).
c. Buida totes les files de la taula TOP_DOGS abans de prova l'exercici.
- 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_current_sal emp.sal%TYPE;
- v_last_sal emp.sal%TYPE := -1;
- CURSOR emp_cursor IS
- SELECT ename, sal
- FROM emp
- WHERE sal IS NOT NULL
- ORDER BY sal DESC;
- CURSOR emp_cursor IS
- BEGIN
- OPEN emp_cursor;
- FETCH emp_cursor INTO v_ename, v_current_sal;
- WHILE (emp_cursor%ROWCOUNT <= v_num OR v_current_sal = v_last_sal) AND emp_cursor%FOUND LOOP
- INSERT INTO top_dogs VALUES (v_ename,v_current_sal);
- v_last_sal := v_current_sal;
- FETCH emp_cursor INTO v_ename, v_current_sal;
- END LOOP;
- CLOSE emp_cursor;
- COMMIT;
- END;
- /
- SQL> SELECT *
- 2 FROM TOP_DOGS;:::OPEN emp_cursor; --obrim el cursor, aquest es posiciona apuntant el primer registre del cursor