E J E R C I C I O S Oracle

E J E R C I C I O S
CURSOR...IS...
1.- Ejemplos de creación de procedimientos con cursores.
1) Desarrollar un procedimiento que visualice el apellido y la fecha de alta de todos los empleados ordenados por apellido.
CREATE OR REPLACE PROCEDURE ver_emple
AS
CURSOR c_emple IS
SELECT APELLIDO, FECHA_ALT
FROM EMPLE
ORDER BY APELLIDO;
v_apellido VARCHAR2(10);
v_fecha DATE;
BEGIN
OPEN c_emple;
FETCHc_emple into v_apellido, v_fecha;
WHILE c_emple%FOUND LOOP
DBMS_OUTPUT.PUT_LINE( v_apellido||' * '||v_fecha);
FETCH c_emple into v_apellido,v_fecha;
END LOOP;
CLOSE c_emple;
END ver_emple;
2) Codificar un procedimiento que muestre el nombre de cada departamento y el número de empleados que tiene.

CREATE OR REPLACE PROCEDURE ver_emple_depart
AS
CURSOR c_emple IS
SELECT dnombre, COUNT(emp_no)FROM emple e, depart d
WHERE d.dept_no = e.dept_no(+)
GROUP BY dnombre;
v_dnombre depart.dnombre%TYPE;
v_num_emple BINARY_INTEGER;
BEGIN
OPEN c_emple;
FETCH c_emple into v_dnombre, v_num_emple;
WHILE c_emple%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_dnombre||' * '||v_num_emple);
FETCH c_emple into v_dnombre,v_num_emple;
END LOOP;
CLOSE c_emple;
END ver_emple_depart;
3) Escribir un procedimiento que recibauna cadena y visualice el apellido y el número de empleado de todos los empleados cuyo apellido contenga la cadena especificada. Al finalizar visualizar el número de empleados mostrados.
CREATE OR REPLACE PROCEDURE ver_emple_apell(
cadena VARCHAR2)
AS
cad VARCHAR2(10);
CURSOR c_emple IS
SELECT apellido, emp_no FROM emple
WHERE apellido LIKE cad;
vr_emple c_emple%ROWTYPE;
BEGIN
cad:='%'||cadena||'%';
OPEN c_emple;
FETCH c_emple INTO vr_emple;
WHILE (c_emple%FOUND) LOOP
DBMS_OUTPUT.PUT_LINE(vr_emple.emp_no||' * '
||vr_emple.apellido);
FETCH c_emple INTO vr_emple;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMERO DE EMPLEADOS: '
|| c_emple%ROWCOUNT);
CLOSE c_emple;
END ver_emple_apell;
4) Escribir un programa que visualice el apellido y el salario de los cinco empleados que tienen el salario más alto.CREATE OR REPLACE PROCEDURE emp_5maxsal
AS
CURSOR c_emp IS
SELECT apellido, salario FROM emple
ORDER BY salario DESC;
vr_emp c_emp%ROWTYPE;
i NUMBER;
BEGIN
i:=1;
OPEN c_emp;
FETCH c_emp INTO vr_emp;
WHILE c_emp%FOUND AND i<=5 LOOP
DBMS_OUTPUT.PUT_LINE(vr_emp.apellido ||
' * '|| vr_emp.salario);
FETCH c_emp INTO vr_emp;
i:=I+1;
END LOOP;
CLOSE c_emp;
END emp_5maxsal;
 

OPEN...FETCH...
2 .-Ejemplos de como como recorrer un cursor.
5) Codificar un programa que visualice los dos empleados que ganan menos de cada oficio.
CREATE OR REPLACE PROCEDURE emp_2minsal
AS
CURSOR c_emp IS
SELECT apellido, oficio, salario FROM emple
ORDER BY oficio, salario;
vr_emp c_emp%ROWTYPE;
oficio_ant EMPLE.OFICIO%TYPE;
i NUMBER;
BEGIN
OPEN c_emp;
oficio_ant:='*';
FETCH c_emp INTO vr_emp;
WHILE c_emp%FOUND LOOPIF oficio_ant <> vr_emp.oficio THEN
oficio_ant := vr_emp.oficio;
i := 1;
END IF;
IF i <= 2 THEN
DBMS_OUTPUT.PUT_LINE(vr_emp.oficio||' * '
||vr_emp.apellido||' * '
||vr_emp.salario);
END IF;
FETCH c_emp INTO vr_emp;
i:=I+1;
END LOOP;
CLOSE c_emp;
END emp_2minsal;
 
6) Escribir un programa que muestre, en formato similar a las rupturas de control o secuencia vistas en SQL*plus los siguientesdatos:
- Para cada empleado: apellido y salario.
- Para cada departamento: Número de empleados y suma de los salarios del departamento.
- Al final del listado: Número total de empleados y suma de todos los salarios.
CREATE OR REPLACE PROCEDURE listar_emple
AS
CURSOR c1 IS
SELECT apellido, salario, dept_no FROM emple
ORDER BY dept_no, apellido;
vr_emp c1%ROWTYPE;
dep_ant EMPLE.DEPT_NO%TYPE;
cont_empleNUMBER(4) DEFAULT 0;
sum_sal NUMBER(9) DEFAULT 0;
tot_emple NUMBER(4) DEFAULT 0;
tot_sal NUMBER(10) DEFAULT 0;
BEGIN
OPEN c1;
FETCH c1 INTO vr_emp;
IF c1%FOUND THEN
dep_ant := vr_emp.dept_no;
END IF;
WHILE c1%FOUND LOOP
/* Comprobación nuevo departamento y resumen */
IF dep_ant <> vr_emp.dept_no THEN
DBMS_OUTPUT.PUT_LINE('*** DEPTO: ' || dep_ant ||
' NUM. EMPLEADOS: '||cont_emple ||
' SUM....