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) |
|||
(Hi ha 10 revisions intermèdies del mateix usuari que no es mostren) | |||
Línia 1: | Línia 1: | ||
− | + | '''1)''' Cree un bloque PL/SQL que realice las siguientes acciones: | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | 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). | :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). | ||
Línia 19: | Línia 13: | ||
WHERE department_id = v_deptno; | WHERE department_id = v_deptno; | ||
</source> | </source> | ||
− | :c) En la | + | :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" |
<source lang="SQL"> | <source lang="SQL"> | ||
BEGIN | BEGIN | ||
Línia 65: | Línia 59: | ||
FOR emp_record IN c_emp_cursor | FOR emp_record IN c_emp_cursor | ||
LOOP | LOOP | ||
− | IF emp_record.salary < 5000 AND (emp_record.manager_id= | + | 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'); | DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise'); | ||
ELSE | ELSE | ||
Línia 74: | Línia 68: | ||
</source> | </source> | ||
− | 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. | ||
Línia 130: | Línia 124: | ||
CLOSE c_emp_cursor; | CLOSE c_emp_cursor; | ||
</source> | </source> | ||
+ | |||
: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"> | <source lang="SQL"> | ||
Línia 136: | Línia 131: | ||
END; | END; | ||
</source> | </source> | ||
+ | |||
:g) Ejecute el script. La salida de ejemplo es la siguiente: | :g) Ejecute el script. La salida de ejemplo es la siguiente: | ||
Línia 177: | Línia 173: | ||
</source> | </source> | ||
− | + | :* '''Utilitzant bucles for cursor''': | |
− | + | <source lang="SQL"> | |
+ | 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; | ||
+ | </source> | ||
− | + | '''3)''' Crea un bloque PL/SQL que utiliza un cursor explícito para determinar los n salarios más altos 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. | + | :a) Ejecute el script lab_07-2.sql para crear una nueva tabla top_salaries para almacenar los salarios de los empleados. |
+ | <source lang="SQL"> | ||
+ | CREATE TABLE top_salaries( | ||
+ | salary NUMBER(7,2)); | ||
+ | </source> | ||
+ | :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. | 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. | Recuerde que los salarios no deben estar duplicados. | ||
Línia 191: | Línia 214: | ||
v_sal employees.salary%TYPE; | v_sal employees.salary%TYPE; | ||
CURSOR c_emp_cursor IS | CURSOR c_emp_cursor IS | ||
− | SELECT salary | + | SELECT DISTINCT salary |
FROM employees | FROM employees | ||
ORDER BY salary DESC; | ORDER BY salary DESC; | ||
</source> | </source> | ||
− | : | + | :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. | :'''Nota:''' asegúrese de agregar una condición de salida para evitar un bucle infinito. | ||
Línia 211: | Línia 234: | ||
END; | END; | ||
</source> | </source> | ||
− | + | :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. | |
<source lang="SQL"> | <source lang="SQL"> | ||
/ | / | ||
Línia 220: | Línia 243: | ||
[[Imatge:M2UF3_SOL_Cursors_2.png |300px|center| Solucions Cursors]] | [[Imatge:M2UF3_SOL_Cursors_2.png |300px|center| 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. | |
<source lang="SQL"> | <source lang="SQL"> | ||
Línia 243: | Línia 266: | ||
CLOSE c_emp_cursor; | CLOSE c_emp_cursor; | ||
END; | END; | ||
+ | |||
/ | / | ||
SELECT * FROM top_salaries; | SELECT * FROM top_salaries; | ||
+ | </source> | ||
+ | |||
+ | |||
+ | :* Amb un for simple: | ||
+ | |||
+ | <source lang="SQL"> | ||
+ | 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; | ||
</source> | </source> |
Revisió de 20:28, 16 març 2023
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:
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:
- 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;