PL/SQL Cursores

-- CURSORES

--FETCH
DECLARE
  CURSOR cur IS SELECT dnombre, loc FROM depart;
  v_nombre VARCHAR2(14);
  v_localidad VARCHAR2(14);

BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_nombre, v_localidad;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (v_nombre || '*' || v_localidad);
  END LOOP;
    DBMS_OUTPUT.PUT_LINE ('Nº de departamentos: ' || cur%ROWCOUNT);
  CLOSE cur;
END;
/

-- FOR...LOOP
DECLARE
    CURSOR cur IS SELECT dnombre, loc FROM depart;

BEGIN
    FOR v_reg IN cur LOOP
        DBMS_OUTPUT.PUT_LINE(v_reg.dnombre || '*' || v_reg.loc);
    END LOOP;
END;
/


-- Visualizar los apellidos de los empleados pertenecientes al departamento 20 numerandolos secuencialmente.

-- FETCH
DECLARE
    CURSOR c1 IS SELECT apellido FROM emple WHERE dept_no=20;
        v_apellido VARCHAR2(10);
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO v_apellido;
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(c1%ROWCOUNT, '99.') || v_apellido);
        -- Al poner esta fila después del EXIT WHEN la última fila no la devolverá dos veces.
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
END;
/

-- FOR...LOOP

DECLARE
    CURSOR c1 IS SELECT apellido FROM emple WHERE dept_no=20;
BEGIN
    FOR v_reg IN c1 LOOP
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(c1%ROWCOUNT, '99.') || v_reg.apellido);
    END LOOP;
END;
/

-- VARIABLES DE ACOPLAMIENTO, necesarias cuando las variables implicadas en la condición WHERE se conocen en tiempo de ejecución.

-- En el siguiente ejemplo se visualizan lso empleados de un departamento cualquiera usando variables de acoplamineto.
    -- se declaran como cualquier otra.
    -- se utiliza en la clausula SELECT, como parte de la supresión.

-- FETCH
CREATE OR REPLACE PROCEDURE ver_emple_por_dept (dep emple.dept_no%TYPE)
AS
    v_dept NUMBER (2); -- variable de acoplamiento
    -- el propio parametro es una buena variable de acoplamiento.
    CURSOR c1 IS SELECT apellido FROM emple WHERE dept_no = v_dept;
    -- sin v. de acoplamiento seria dept_no = dep, usando el propio parametro.
    v_apellido VARCHAR2(10);
BEGIN
    v_dept := dep;
    OPEN c1;
    FETCH c1 INTO v_apellido;
    WHILE c1%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_apellido);
        FETCH c1 INTO v_apellido;
    END LOOP;
    CLOSE c1;
END ver_emple_por_dept;
/

-- FOR...LOOP
CREATE OR REPLACE PROCEDURE ver_emple_por_dept (dep emple.dept_no%TYPE)
AS
    CURSOR c1 IS SELECT apellido FROM emple WHERE dept_no = dep;
BEGIN
    FOR v_reg INTO c1 LOOP
        DBMS_OUTPUT.PUT_LINE(v_reg.apellido);
    END LOOP;
END ver_emple_por_dept;
/

EXECUTE ver_emple_por_dept(20);

-- OBJETIVO CURSORES FOR LOOP : simplificar, omitiendo la mayor parte de las tareas del cursor.
-- En el siguiente ejemplo, se visualizan el apellido, el oficio y la comision de los empleados cuya comision supera 500e utilizando CURSOR FOR... LOOP.

DECLARE
    CURSOR mi_cursor IS SELECT apellido, oficio, comision FROM emple WHERE comision > 500;
    num_filas NUMBER (3) DEFAULT 0;
BEGIN
-- desaparece la declaración de las variables que recogerán los datos del cursor.
-- desaparece la introducción de apertura del cursor.
-- las filas del cursor no se recuperan con FETCH.
-- el cursor se cierra utomaticamente.
    FOR v_reg IN mi_cursor LOOP
    -- v_reg es del tipo mi_curor%ROWTYPE y solo tiene validez dentro del bucle, no hace falta declararla.
        DBMS_OUTPUT.PUT_LINE(v_reg.apellido||'*'|| v_reg.oficio||'*'||TO_CHAR(v_reg.comision) );
        num_filas := mi_cursor%ROWTYPE;
    END LOOP;
        DBMS_OUTPUT.PUT_LINE ('Nº de departamentos: ' || num_filas);
END;
/

-- otro ejemplo FOR...LOOP

DECLARE
    CURSOR c_emple_f1 IS
        SELECT dept_no, COUNT(*) n_emp, SUM(salario+NVL(comision, 0)) suma
        FROM emple
        GROUP BY dept_no;
BEGIN
    FOR v_reg_emp IN c_emple_f1 LOOP
        DBMS_OUTPUT.PUT_LINE(v_reg_emp.dept_no || '*' || v_reg_emp.n_emp || '*' || v_reg_emp.suma);
    END LOOP;
END;
/

-- el mismo ejemplo con FETCH

DECLARE
    v_dept_no emple.dept_no%TYPE;
    v_n_emp NUMBER (5);
    v_suma NUMBER (10);
    CURSOR c_emple_f1 IS SELECT dept_no, COUNT(*) n_emp, SUM(salario+NVL(comision, 0)) suma
        FROM emple
        GROUP BY dept_no;
BEGIN
    OPEN c_emple_f1;
    LOOP
        FETCH c_emple_f1 INTO v_dept_no, v_n_emp, v_suma;
        EXIT WHEN c_emple_f1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (v_dept_no|| '*' || v_n_emp || '*' || v_suma);
    END LOOP;
    CLOSE c_emple_f1;
END;
/


-- CURSORES CON PARAMETROS (sin usar variables de acoplamiento)

-- CURSOR nombre (parametros) IS SELECT sentencia select sin parametros
-- Los parametros se declaran NOMBREPARAMETRO IN tipodato := DEFAULT valor
-- a) con FETCH

DECLARE
    v_apellido emple.apellido%TYPE;
    v_salario emple.salario%TYPE;
    v_dep emple.dept_no%TYPE;
    v_ofi emple.oficio%TYPE;

    CURSOR cur(v_departamento NUMBER, v_oficio VARCHAR2 DEFAULT 'ANALISTA')
    IS
        SELECT apellido, salario FROM emple
        WHERE dept_no = v_departamento AND oficio = v_oficio;
BEGIN
    v_dep := 20;
    v_ofi := 'ANALISTA';
    -- podemos no declarar las variables y al abrir el cursor poner OPEN cur(20) con solo un valor ya que el otro es DEFAULT
    OPEN cur(v_dep, v_ofi);

    FETCH cur INTO v_apellido, v_salario;
    WHILE cur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_apellido || '*' || v_salario);
        FETCH cur INTO v_apellido, v_salario;
    END LOOP;
END;
/
-- el anterior con FOR...LOOP

DECLARE
    CURSOR cur(v_departamento NUMBER, v_oficio VARCHAR2 DEFAULT 'ANALISTA')
        IS
            SELECT apellido, salario FROM emple
            WHERE dept_no = v_departamento AND oficio = v_oficio;
BEGIN
    FOR v_reg IN cur(20) LOOP
    DBMS_OUTPUT.PUT_LINE(v_reg.apellido || '*' || v_reg.salario);
    END LOOP;
END;
/