M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T7

De wikiserver
Dreceres ràpides: navegació, cerca

1) Cree un bloque PL/SQL que realice las siguientes acciones:

a) En la sección de declaraciones, declare e inicialice una variable llamada v_deptno de tipo NUMBER. Asigne un valor de identificador de departamento válido (consulte los valores en la tabla del paso d).
DECLARE
  v_deptno NUMBER := 10;
b) Declare un cursor llamado c_emp_cursor, que recupere last_name, salary y manager_id de los empleados que trabajan en el departamento especificado en v_deptno.
CURSOR c_emp_cursor IS
  SELECT last_name, salary,manager_id
  FROM employees
  WHERE department_id = v_deptno;
c) En la secció executable, utilitza el bucle FOR de cursor per realitzar operacions en les dades recuperades. Si el salari de l'empleat es menor que 5000 i el identificador de su superior (manager_id) es 101 0 124, apareix el missatge "<<last_name>> Due for a raise". En cas contrari, apareix el missatge "<<last_name>> Not Due for a raise"
BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    IF emp_record.salary < 5000 AND (emp_record.manager_id=101OR emp_record.manager_id=124) THEN
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
    ELSE
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
    END IF;
   END LOOP;
END;

d) Pruebe el bloque PL/SQL para los siguientes casos: 

   Department ID      Message
    10                Whalen Due for a raise
    20                Hartstein Not Due for a raise
                      Fay Not Due for a raise
    50                Weiss Not Due for a raise
                      Fripp Not Due for a raise
                      Kaufling Not Due for a raise
                      Vollman Not Due for a raise. . .
                      . . .
                      OConnell Due for a raise
                      Grant Due for a raise
    80                Russell Not Due for a raise
                      Partners Not Due for a raise
                      Errazuriz Not Due for a raise
                      Cambrault Not Due for a raise
                      . . .
                      Livingston Not Due for a raise
                      Johnson Not Due for a raise

Solució:

DECLARE
  v_deptno NUMBER := 10;
  CURSOR c_emp_cursor IS
    SELECT last_name, salary,manager_id
    FROM employees
    WHERE department_id = v_deptno;
BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    IF emp_record.salary < 5000 AND (emp_record.manager_id=101 OR emp_record.manager_id=124) THEN
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
    ELSE
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
    END IF;
  END LOOP;
END;

2) A continuación, escriba un bloque PL/SQL que declare y utilice dos cursores: uno sin parámetro y otro con parámetro. El primer cursor recupera el número de departamento y el nombre del departamento de la tabla departments para todos los departamentos cuyos números de identificador sean menores que 100. El segundo cursor recibe el número de departamento como parámetro y recupera los detalles de los empleados que trabajan en ese departamento y cuyo employee_id sea menor que 120.

a) Declare un cursor c_dept_cursor para recuperar department_id y department_name para los departamentos con department_id menor que 100. Ordene por department_id.
DECLARE
  CURSOR c_dept_cursor IS
    SELECT department_id,department_name
    FROM departments
    WHERE department_id < 100
    ORDER BY department_id;
b) Declare otro cursor c_emp_cursor que tome el número de departamento como parámetro y recupere los siguientes datos de la tabla employees: last_name, job_id, hire_date y salary de los empleados que trabajan en ese departamento, con employee_id menor que 120.
CURSOR c_emp_cursor(v_deptno NUMBER) IS
  SELECT last_name,job_id,hire_date,salary
  FROM employees
  WHERE department_id = v_deptno AND employee_id < 120;
c) Declare las variables que contienen los valores recuperados de cada cursor.
Utilice el atributo %TYPE mientras declara las variables.
v_current_deptno departments.department_id%TYPE;
v_current_dname departments.department_name%TYPE;
v_ename employees.last_name%TYPE;
v_job employees.job_id%TYPE;
v_hiredate employees.hire_date%TYPE;
v_sal employees.salary%TYPE;
d) Abra c_dept_cursor, utilice un bucle simple y recupere los valores en las variables declaradas. Muestre el número y el nombre de departamento. Utilice el atributo de cursor adecuado para salir del bucle.
BEGIN
  OPEN c_dept_cursor;
  LOOP
    FETCH c_dept_cursor INTO v_current_deptno, v_current_dname;
    EXIT WHEN c_dept_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname);
e) Abra c_emp_cursor transfiriendo el número de departamento actual como parámetro. Inicie otro bucle y recupere los valores de emp_cursor en las variables e imprima todos los detalles recuperados de la tabla employees.
Nota
  • Compruebe si c_emp_cursor ya está abierto antes de abrirlo.
  • Utilice el atributo de cursor adecuado para la condición de salida.
  • Cuando termine el bucle, imprima una línea después de haber mostrado los detalles de cada departamento y cierre c_emp_cursor.
IF c_emp_cursor%ISOPEN THEN
  CLOSE c_emp_cursor;
END IF;
OPEN c_emp_cursor (v_current_deptno);
LOOP
  FETCH c_emp_cursor INTO v_ename,v_job,v_hiredate,v_sal;
  EXIT WHEN c_emp_cursor%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE (v_ename || ' ' || v_job || ' ' || v_hiredate || ' ' || v_sal);
END LOOP;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------');
CLOSE c_emp_cursor;
f) Termine el primer bucle y cierre c_dept_cursor. A continuación, termine la sección ejecutable.
END LOOP;
  CLOSE c_dept_cursor;
END;
g) Ejecute el script. La salida de ejemplo es la siguiente:
Solucions Cursors
Solució:

DECLARE
  CURSOR c_dept_cursor IS
    SELECT department_id,department_name
    FROM departments
    WHERE department_id < 100
    ORDER BY department_id;
  v_current_deptno departments.department_id%TYPE;
  v_current_dname departments.department_name%TYPE;
  v_ename employees.last_name%TYPE;
  v_job employees.job_id%TYPE;
  v_hiredate employees.hire_date%TYPE;
  v_sal employees.salary%TYPE;
BEGIN
  OPEN c_dept_cursor;
  LOOP
    FETCH c_dept_cursor INTO v_current_deptno, v_current_dname;
    EXIT WHEN c_dept_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname);
    IF c_emp_cursor%ISOPEN THEN
      CLOSE c_emp_cursor;
    END IF;
    OPEN c_emp_cursor (v_current_deptno);
    LOOP
      FETCH c_emp_cursor INTO v_ename,v_job,v_hiredate,v_sal;
      EXIT WHEN c_emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (v_ename || ' ' || v_job || ' ' || v_hiredate || ' ' || v_sal);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------');
    CLOSE c_emp_cursor;
  END LOOP;
  CLOSE c_dept_cursor;
END;
  • Utilitzant bucles for cursor:
DECLARE
  CURSOR c_dept_cursor IS
    SELECT department_id, department_name
    FROM departments
    WHERE department_id < 100
    ORDER BY department_id;
  CURSOR c_emp_cursor(v_deptno NUMBER) IS
    SELECT last_name,job_id,hire_date,salary
    FROM employees
    WHERE department_id = v_deptno AND employee_id < 120;
BEGIN
  FOR dept_record IN c_dept_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Department Number : ' || dept_record.department_id || ' Department Name : ' || dept_record.department_name);
    FOR emp_record IN c_emp_cursor(dept_record.department_id)
    LOOP
       DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' ' || emp_record.job_id || ' ' ||emp_record.hire_date || ' ' || emp_record.salary);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------');
  END LOOP;
END;

3) Crea un bloque PL/SQL que utiliza un cursor explícito para determinar los n salarios más altos de los empleados.

a) Ejecute el script lab_07-2.sql para crear una nueva tabla top_salaries para almacenar los salarios de los empleados.
CREATE TABLE top_salaries(
    salary NUMBER(7,2));
b) En la sección de declaraciones, declare la variable v_num del tipo NUMBER que contenga el número n que representa los n salarios más altos de la tabla employees. Por ejemplo, para ver los cinco salarios principales, introduzca 5.

Declare otra variable sal del tipo employees.salary. Declare un cursor, c_emp_cursor, que recupere los salarios de los empleados en orden descendente. Recuerde que los salarios no deben estar duplicados. 

DECLARE
  v_num NUMBER(3) := 5;
  v_sal employees.salary%TYPE;
  CURSOR c_emp_cursor IS
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC;
c) En la sección ejecutable, abra el bucle, recupere los n salarios principales e insértelos en la tabla top_salaries. Puede utilizar un bucle simple para realizar operaciones con los datos. Además, utilice los atributos %ROWCOUNT y %FOUND para la condición de salida.
Nota: asegúrese de agregar una condición de salida para evitar un bucle infinito.
BEGIN
  OPEN c_emp_cursor;
  FETCH c_emp_cursor INTO v_sal;
  WHILE c_emp_cursor%ROWCOUNT <= v_num AND c_emp_cursor%FOUND
  LOOP
    INSERT INTO top_salaries (salary)
    VALUES (v_sal);
     FETCH c_emp_cursor INTO v_sal;
  END LOOP;
  CLOSE c_emp_cursor;
END;
d) Después de insertarlos en la tabla top_salaries, muestre las filas con una sentencia SELECT. La salida que se muestra representa los cinco salarios más altos de la tabla employees.
/
SELECT * FROM top_salaries;
La salida de ejemplo es la siguiente:
Solucions Cursors
e) Pruebe distintos casos especiales, como v_num = 0 o donde v_num es mayor que el número de empleados de la tabla employees. Vacíe la tabla top_salaries después de cada prueba.
Solució:

DECLARE
  v_num NUMBER(3) := 5;
  v_sal employees.salary%TYPE;
  CURSOR c_emp_cursor IS
    SELECT salary
    FROM employees
    ORDER BY salary DESC;
BEGIN
  OPEN c_emp_cursor;
  FETCH c_emp_cursor INTO v_sal;
  WHILE c_emp_cursor%ROWCOUNT <= v_num AND c_emp_cursor%FOUND
  LOOP
    INSERT INTO top_salaries (salary)
    VALUES (v_sal);
    FETCH c_emp_cursor INTO v_sal;
  END LOOP;
  CLOSE c_emp_cursor;
END;

/
SELECT * FROM top_salaries;


  • Amb un for simple:
DECLARE
  v_num NUMBER(3) := 5;
  v_sal employees.salary%TYPE;
  CURSOR c_emp_cursor IS
    SELECT distinct salary
    FROM employees
    ORDER BY salary DESC;
BEGIN
  OPEN c_emp_cursor;
  LOOP
     FETCH c_emp_cursor INTO v_sal;
     EXIT WHEN c_emp_cursor%ROWCOUNT > v_num OR c_emp_cursor%NOTFOUND;
     INSERT INTO top_salaries (salary)
     VALUES (v_sal);
  END LOOP;
  CLOSE c_emp_cursor;
END;
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF3:_Llenguatges_SQL_/_Solucions_PL-SQL_T7&oldid=17197»