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

Revisió del 16:42, 6 maig 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 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;

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