Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T7»
De wikiserver
(→Solución 7-1: Uso de Cursores Explícitos) |
(→Solución 7-1: Uso de Cursores Explícitos) |
||
Línia 91: | Línia 91: | ||
DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname); | DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname); | ||
</pre> | </pre> | ||
+ | :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. | ||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | :f) Termine el primer bucle y cierre c_dept_cursor. A continuación, termine la sección ejecutable. | ||
+ | <pre> | ||
+ | END LOOP; | ||
+ | CLOSE c_dept_cursor; | ||
+ | END; | ||
+ | </pre> | ||
+ | :g) Ejecute el script. La salida de ejemplo es la siguiente: |
Revisió del 17:11, 25 abr 2016
Solución 7-1: Uso de Cursores Explícitos
En esta lección, realizará dos ejercicios:
- Primero, utilizará un cursor explícito para procesar un número de filas de una tabla y rellenar otra tabla con los resultados mediante un bucle FOR de cursor.
- Después, escribirá un bloque PL/SQL que procese la información con dos cursores, incluido uno que utilice un parámetro.
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ón ejecutable, utilice el bucle FOR de cursor para realizar operaciones en los datos recuperados. Si el salario del empleado es menor que 5.000 y si el identificador de superior es 101 o 124, aparece el mensaje “<<last_name>> Due for a raise”. De lo contrario, aparece el mensaje “<<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
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: