Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL T7»
(→Solución 7-2: Uso de Cursores Explícitos) |
(→Solución 7-1: Uso de Cursores Explícitos) |
||
Línia 8: | Línia 8: | ||
: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). | :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). | ||
− | < | + | <source lang="SQL"> |
DECLARE | DECLARE | ||
v_deptno NUMBER := 10; | v_deptno NUMBER := 10; | ||
− | </ | + | </source> |
: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. | :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. | ||
− | < | + | <source lang="SQL"> |
CURSOR c_emp_cursor IS | CURSOR c_emp_cursor IS | ||
SELECT | SELECT | ||
Línia 19: | Línia 19: | ||
FROM employees | FROM employees | ||
WHERE department_id = v_deptno; | WHERE department_id = v_deptno; | ||
− | </ | + | </source> |
: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”. | :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”. | ||
− | < | + | <source lang="SQL"> |
BEGIN | BEGIN | ||
FOR emp_record IN c_emp_cursor | FOR emp_record IN c_emp_cursor | ||
Línia 32: | Línia 32: | ||
END LOOP; | END LOOP; | ||
END; | END; | ||
− | </ | + | </source> |
d) Pruebe el bloque PL/SQL para los siguientes casos: | d) Pruebe el bloque PL/SQL para los siguientes casos: | ||
<pre> | <pre> | ||
Línia 53: | Línia 53: | ||
Livingston Not Due for a raise | Livingston Not Due for a raise | ||
Johnson Not Due for a raise | Johnson Not Due for a raise | ||
− | </ | + | </source> |
+ | 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=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; | ||
+ | <source lang="SQL"> | ||
+ | |||
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. | 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. | :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. | ||
− | < | + | <source lang="SQL"> |
DECLARE | DECLARE | ||
CURSOR c_dept_cursor IS | CURSOR c_dept_cursor IS | ||
Línia 64: | Línia 84: | ||
WHERE department_id < 100 | WHERE department_id < 100 | ||
ORDER BY department_id; | ORDER BY department_id; | ||
− | </ | + | </source> |
: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. | :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. | ||
− | < | + | <source lang="SQL"> |
CURSOR c_emp_cursor(v_deptno NUMBER) IS | CURSOR c_emp_cursor(v_deptno NUMBER) IS | ||
SELECT last_name,job_id,hire_date,salary | SELECT last_name,job_id,hire_date,salary | ||
FROM employees | FROM employees | ||
WHERE department_id = v_deptno AND employee_id < 120; | WHERE department_id = v_deptno AND employee_id < 120; | ||
− | </ | + | </source> |
:c) Declare las variables que contienen los valores recuperados de cada cursor. | :c) Declare las variables que contienen los valores recuperados de cada cursor. | ||
:Utilice el atributo %TYPE mientras declara las variables. | :Utilice el atributo %TYPE mientras declara las variables. | ||
− | < | + | <source lang="SQL"> |
v_current_deptno departments.department_id%TYPE; | v_current_deptno departments.department_id%TYPE; | ||
v_current_dname departments.department_name%TYPE; | v_current_dname departments.department_name%TYPE; | ||
Línia 81: | Línia 101: | ||
v_hiredate employees.hire_date%TYPE; | v_hiredate employees.hire_date%TYPE; | ||
v_sal employees.salary%TYPE; | v_sal employees.salary%TYPE; | ||
− | </ | + | </source> |
: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. | :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. | ||
− | < | + | <source lang="SQL"> |
BEGIN | BEGIN | ||
OPEN c_dept_cursor; | OPEN c_dept_cursor; | ||
Línia 90: | Línia 110: | ||
EXIT WHEN c_dept_cursor%NOTFOUND; | EXIT WHEN c_dept_cursor%NOTFOUND; | ||
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); | ||
− | </ | + | </source> |
: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. | :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. | ||
Línia 97: | Línia 117: | ||
:* Utilice el atributo de cursor adecuado para la condición de salida. | :* 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. | :* Cuando termine el bucle, imprima una línea después de haber mostrado los detalles de cada departamento y cierre c_emp_cursor. | ||
− | < | + | <source lang="SQL"> |
IF c_emp_cursor%ISOPEN THEN | IF c_emp_cursor%ISOPEN THEN | ||
CLOSE c_emp_cursor; | CLOSE c_emp_cursor; | ||
Línia 111: | Línia 131: | ||
</pre> | </pre> | ||
:f) Termine el primer bucle y cierre c_dept_cursor. A continuación, termine la sección ejecutable. | :f) Termine el primer bucle y cierre c_dept_cursor. A continuación, termine la sección ejecutable. | ||
− | < | + | <source lang="SQL"> |
END LOOP; | END LOOP; | ||
CLOSE c_dept_cursor; | CLOSE c_dept_cursor; | ||
END; | END; | ||
− | </ | + | </source> |
:g) Ejecute el script. La salida de ejemplo es la siguiente: | :g) Ejecute el script. La salida de ejemplo es la siguiente: | ||
[[Imatge:M2UF3_SOL_Cursos_1.png |500px|center| Solucions Cursors]] | [[Imatge:M2UF3_SOL_Cursos_1.png |500px|center| Solucions Cursors]] | ||
+ | |||
+ | </source> | ||
+ | Solució: | ||
+ | |||
+ | <source lang="SQL"> | ||
==Solución 7-2: Uso de Cursores Explícitos== | ==Solución 7-2: Uso de Cursores Explícitos== |
Revisió del 13:10, 15 març 2018
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 </source> 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=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; <source lang="SQL"> 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. <source lang="SQL"> DECLARE CURSOR c_dept_cursor IS SELECT department_id,department_name FROM departments WHERE department_id < 100 ORDER BY department_id; </source> :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. <source lang="SQL"> 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; </source> :c) Declare las variables que contienen los valores recuperados de cada cursor. :Utilice el atributo %TYPE mientras declara las variables. <source lang="SQL"> 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; </source> :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. <source lang="SQL"> 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); </source> :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. <source lang="SQL"> 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:
</source> Solució:
<source lang="SQL">
Solución 7-2: Uso de Cursores Explícitos
Si tiene tiempo, realice el siguiente ejercicio opcional. Creará un bloque PL/SQL que utiliza un cursor explícito para determinar los n salarios más altos de los empleados.
1) Ejecute el script lab_07-2.sql para crear una nueva tabla top_salaries para almacenar los salarios de los empleados.
2) 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 salary FROM employees ORDER BY salary DESC;
- 3) 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;
4) 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:
5) 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.