PL/SQL AC 10

--ACTIVIDADES COMPLEMENTARIAS T.10

--1 Desarrolla un procedimiento que visualice el apellido y la fecha de alta de todos los empleados ordenados por apellido.
--FETCH
CREATE OR REPLACE PROCEDURE ver_emp AS
    CURSOR cur IS
        SELECT APELLIDO, FECHA_ALT
        FROM EMPLE
        ORDER BY APELLIDO;
    v_apellido VARCHAR2(10);
    v_fecha DATE;
BEGIN
    OPEN cur;
    FETCH cur INTO v_apellido, v_fecha;
    WHILE cur%FOUND LOOP
            DBMS_OUTPUT.PUT_LINE( v_apellido||' * '||v_fecha);
            FETCH cur INTO v_apellido,v_fecha;
    END LOOP;
    CLOSE cur;
END ver_emp;
/

-- 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 CUR 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 cur;
    FETCH cur INTO v_dnombre, v_num_emple;
    WHILE cur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_dnombre||' * '||v_num_emple);
        FETCH cur INTO v_dnombre,v_num_emple;
    END LOOP;
    CLOSE cur;
END ver_emple_depart;
/

-- 3 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_5
AS
    CURSOR cur IS
        SELECT apellido, salario FROM emple
        ORDER BY salario DESC;
    vr_emp cur%ROWTYPE;
    v_num NUMBER;
BEGIN
    v_num:=1;
    OPEN cur;
    FETCH cur INTO vr_emp;
    WHILE cur%FOUND AND v_num<=5 LOOP
        DBMS_OUTPUT.PUT_LINE(vr_emp.apellido ||' * '|| vr_emp.salario);
        FETCH cur INTO vr_emp;
        v_num:=v_num+1;
    END LOOP;
    CLOSE cur;
END emp_5;
/

-- 4 Codificar un programa que visualice los dos empleados que ganan menos de cada oficio.

CREATE OR REPLACE PROCEDURE emp_2
AS
    CURSOR cur IS
        SELECT apellido, oficio, salario FROM emple
        ORDER BY oficio, salario;
    vr_emp cur%ROWTYPE;
    oficio_ant EMPLE.OFICIO%TYPE;
    v_num NUMBER;
BEGIN
    OPEN c_emp;
    oficio_ant:='*';
    FETCH cur INTO vr_emp;
    WHILE cur%FOUND LOOP
        IF oficio_ant <> vr_emp.oficio THEN
            oficio_ant := vr_emp.oficio;
            v_num := 1;
        END IF;
        IF v_num <= 2 THEN
            DBMS_OUTPUT.PUT_LINE(vr_emp.oficio||' * ' ||vr_emp.apellido||' * ' ||vr_emp.salario);
        END IF;
        FETCH cur INTO vr_emp;
        v_num:=v_num+1;
    END LOOP;
    CLOSE cur;
END emp_2;
/

-- 5 Desarrollar un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones:
-- Se pasará al procedimiento el nombre del departamento y la localidad.
-- El procedimiento insertará la fila nueva asignando como número de departamento la decena siguiente al número mayor de la tabla.
-- Se incluirá gestión de posibles errores.

CREATE OR REPLACE PROCEDURE insertar_depart(
    nombre_dep VARCHAR2,
    loc VARCHAR2)
AS
    CURSOR cur IS SELECT dnombre
            FROM depart WHERE dnombre = nombre_dep;
    v_dnombreDEPART.DNOMBRE%TYPE DEFAULT NULL;
    v_ulti_num DEPART.DEPT_NO%TYPE;
    nombre_duplicado EXCEPTION;
BEGIN
-- comprobar que no esté duplicado
    OPEN cur;
    FETCH cur INTO v_dummy;
    CLOSE cur;
    IF v_dnombre IS NOT NULL THEN
        RAISE nombre_duplicado;
    END IF;

--  coger el último número y calcular el siguiente
    SELECT MAX(dept_no) INTO v_ulti_num FROM depart;

-- Inserción de la nueva fila
    INSERT INTO depart VALUES ((TRUNC(v_ulti_num, -1)+10), nombre_dep, loc);
    EXCEPTION WHEN nombre_duplicado THEN
        DBMS_OUTPUT.PUT_LINE('Err. departamento duplicado');
        RAISE;
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20005,'ERROR: no se puede realizar la operación');
END insertar_depart;
/

-- 6 Codificar un procedimiento reciba como parámetros un numero de departamento, un importe y un porcentaje; y suba el salario a
-- todos los empleados del departamento indicado en la llamada. La subida será el porcentaje o el importe indicado en la llamada
-- (el que sea más beneficioso para el empleado en cada caso empleado).

CREATE OR REPLACE PROCEDURE sub_sal(
    num_depar emple.dept_no%TYPE,
    importe NUMBER,
    porcentaje NUMBER)
AS
    CURSOR cur IS SELECT salario,ROWID
              FROM emple WHERE dept_no = num_depar;
    vr_sal cur%ROWTYPE;
    v_imp_por NUMBER(10);
BEGIN
    OPEN cur;
    FETCH cur INTO vr_sal;
    WHILE cur%FOUND LOOP

-- Guardar en v_imp_por el importe mayor
        v_imp_por := GREATEST((vr_sal.salario/100)*porcentaje, v_imp_por);

-- Actualizar
        UPDATE EMPLE SET SALARIO=SALARIO + v_imp_por WHERE ROWID = vr_sal.ROWID;

        FETCH cur INTO vr_sal;
    END LOOP;
    CLOSE cur;
-- Excepción
    EXCEPTION WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: No se actualiza ninguna fila');
END sub_sal;
/