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

De wikiserver
Dreceres ràpides: navegació, cerca
Línia 78: Línia 78:
 
c. Buida totes les files de la taula TOP_DOGS abans de prova l'exercici.
 
c. Buida totes les files de la taula TOP_DOGS abans de prova l'exercici.
  
 +
::SET SERVEROUTPUT ON;
  
−
SET SERVEROUTPUT ON;
+
::SET VERIFY OFF;
−
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;
+
::ACCEPT p_num PROMPT 'Introdueixi el número de empleats amb sou més alt:  '
−
 
−
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;
 
−
/
 
  
−
--------------------------------------------------------------------------------------------------
+
::DECLARE
 +
 
 +
:::v_num NUMBER(3) :=&p_num;
  
−
--Versión 2 de este ejericio
+
:::v_ename emp.ename%TYPE;
  
−
ACCEPT p_num PROMPT 'introduce el máximo numero de sueldos distintos: '
+
:::v_current_sal emp.sal%TYPE;   
  
−
DECLARE
+
:::v_last_sal emp.sal%TYPE := -1;
  
−
  v_num  NUMBER(3):= &p_num;
+
:::CURSOR emp_cursor IS
−
 
+
::::SELECT ename, sal
−
  v_ename  emp.ename%TYPE;
+
::::FROM emp  
−
 
+
::::WHERE sal IS NOT NULL
−
  v_salari_actual  emp.sal%TYPE;
+
::::ORDER BY sal DESC;
 +
 
 +
::BEGIN
  
−
  v_ultimo_salario  emp.sal%TYPE:= 1;
+
:::OPEN emp_cursor;  
  
−
  CURSOR  emp_cursor IS  SELECT ename, sal FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC;
+
:::FETCH emp_cursor INTO v_ename, v_current_sal;  
  
−
BEGIN
+
:::WHILE (emp.cursor%ROWCOUNT <= v_num OR v_current_sal = v_last_sal) AND emp_curosr%FOUND LOOP  
−
 
 
−
  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
+
::::INSERT INTO top_dogs VALUES (v_ename,v_sal);  
−
        IF c_emp%NOTFOUND THEN EXIT; --condició de sortida del for, si c_emp està buida, el fetch no
+
          
−
 
+
::::FETCH emp_cursor INTO v_ename, v_current_sal;  
−
                                    --retorna cap valor
+
      
−
        END IF;
+
:::END LOOP;
−
 
 
−
        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
+
:::CLOSE emp_cursor;
−
 
 
−
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;
+
:::COMMIT;
  
−
  close emp_cursor;
+
::END;
  
−
COMMIT;
+
::/
  
−
END;
+
::SQL> SELECT *
 +
:: 2  FROM TOP_DOGS;:::OPEN emp_cursor; --obrim el cursor, aquest es posiciona apuntant el primer registre del cursor

Revisió del 19:15, 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 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_curosr%FOUND LOOP
INSERT INTO top_dogs VALUES (v_ename,v_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
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_Llenguatges_SQL_/_Solucions_PL-SQL_T8&oldid=1557»