Fundamentals PL/SQL: Chapter 06 Writing Explicit Cursors

Mayo 8, 2008 — Nelson Piedra

17 Responses to “Fundamentals PL/SQL: Chapter 06 Writing Explicit Cursors”

  1. Juan Pablo Angamarca Says:
    Mayo 12, 2008 at 7:14 pm

    Uso de cursores y la instrucción FETCH: Consultamos todos los datos de la tabla locations, los colocamos en un cursor. Luego con un bucle LOOP lo recorremos y con FETCH colocamos los dos valores de cada tupla del cursor en dos variables y las presentamos.


    SET SERVEROUTPUT ON
    DECLARE
    CURSOR my_locations IS
    SELECT location_id, street_address FROM locations;
    loc_id locations.location_id%TYPE;
    str_add locations.street_address%TYPE;
    counter NUMBER(1);
    BEGIN
    OPEN my_locations;
    LOOP
    FETCH my_locations INTO loc_id, str_add;
    EXIT WHEN my_locations%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Location ID: ' || loc_id || ', Street Address: ' || str_add);
    END LOOP;
    CLOSE my_locations;
    END;

    Es importante abrir y cerrar los cursores. Si no se abren no se puede recuperar datos de ellos. Se cierran para liberar recursos.

  2. Juan Pablo Angamarca Says:
    Mayo 12, 2008 at 7:26 pm

    La salida del programa es:

    Location ID: 1400, Street Address: 2014 Jabberwocky Rd
    Location ID: 1500, Street Address: 2011 Interiors Blvd
    Location ID: 1700, Street Address: 2004 Charade Rd
    Location ID: 1800, Street Address: 460 Bloor St. W.
    Location ID: 2500, Street Address: Magdalen Centre, The Oxford Science Park

  3. Diego Guamán Says:
    Mayo 12, 2008 at 7:44 pm

    En este ejemplo utilizamos la sentencia FETCH para volver los 10 primeros empleados uno por uno.

    SET SERVEROUTPUT ON
    DECLARE
    v_empno employees.employee_id%TYPE;
    v_ename employees.last_name%TYPE;
    CURSOR emp_cursor IS
    SELECT employee_id, last_name
    FROM employees;
    BEGIN
    OPEN emp_cursor;
    FOR i IN 1..10 LOOP
    FETCH emp_cursor INTO v_empno, v_ename;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR (v_empno)|| ‘ ‘|| v_ename);
    END LOOP;
    END;

  4. claucar Says:
    Mayo 12, 2008 at 7:55 pm

    Diego una forma de obtener los id y nombre de todos los empleados de la tabla employees, es utilizando una variable para obtener el número total de los empleados en esta tabla y luego usándola como contador, como se muestra a continuación:

    SET SERVEROUTPUT ON
    DECLARE
    v_empno employees.employee_id%TYPE;
    v_ename employees.last_name%TYPE;
    v_count_row NUMBER(3);

    CURSOR emp_cursor IS
    SELECT employee_id, last_name
    FROM employees;

    BEGIN
    SELECT COUNT(rownum) INTO v_count_row
    FROM employees;

    OPEN emp_cursor;
    FOR i IN 1..v_count_row LOOP
    FETCH emp_cursor INTO v_empno, v_ename;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno)||’ ‘||v_ename);
    END LOOP;
    END;
    /

    SALIDA

    100 King
    101 Kochhar
    102 De Haan
    103 Hunold
    104 Ernst
    107 Lorentz
    124 Mourgos
    141 Rajs
    142 Davies
    143 Matos
    144 Vargas
    149 Zlotkey
    174 Abel
    178 Grant
    200 Whalen
    201 Hartstein
    202 Fay
    205 Higgins
    206 Gietz
    207 Cores
    PL/SQL procedure successfully completed.

  5. Gabriela Puglla Says:
    Mayo 12, 2008 at 7:58 pm

    en esta sentencia utilizamos la informacion de la tabal locations en la cual utilizamos un cursor y la sentencia fecth el cual nos permitir verificar el numero de datos recuperados dependiendo del valor colocado en la sentencia for

    set serveroutput on
    declare
    v_locno locations.location_id%TYPE;
    v_street locations.street_address%TYPE;
    CURSOR emp_cursor IS
    select location_id, street_address
    from locations;
    begin
    open emp_cursor;
    for i IN 1..20 LOOP
    fetch emp_cursor into v_locno, v_street;
    dbms_output.put_line (to_char(v_locno)||’ ‘||v_street);
    end loop;
    end;

    la ejecucion nos muestra la informacion de los datos recuperados:
    1400 2014 Jabberwocky Rd
    1500 2011 Interiors Blvd
    1700 2004 Charade Rd
    1800 460 Bloor St. W.
    2500 Magdalen Centre, The Oxford Science Park
    1801
    1802
    1803
    1804
    1805
    1806
    1807
    1808
    1809
    1810
    1811
    1812
    1813
    1814
    1815
    PL/SQL procedure successfully completed.

  6. Luis Castillo Says:
    Mayo 12, 2008 at 8:01 pm

    SET SERVEROUTPUT ON
    DECLARE
    V_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE;
    V_ENAME EMPLOYEES.LAST_NAME%TYPE;
    I NUMBER(4,2);
    CURSOR EMP_CURSOR IS
    SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES;
    BEGIN
    OPEN EMP_CURSOR;
    I := 0;
    WHILE I <= 10 LOOP
    FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_EMPNO)||’ ‘|| V_ENAME);
    I := I + 1;
    END LOOP;
    CLOSE EMP_CURSOR;
    END;
    100 King
    101 Kochhar
    102 De Haan
    103 Hunold
    104 Ernst
    107 Lorentz
    124 Mourgos
    141 Rajs
    142 Davies
    143 Matos
    144 Vargas
    PL/SQL procedure successfully completed.
    Aquí les propongo el mismo ejercicio pero con el uso de un WHILE el cual en cierta manera reemplaza al FOR pero con la diferencia de que el declarar el Wile en este ejemplo se tiene que recurrir a declarar una variable mas y posteriormente realizar un incremento i := i +1 pero como sera con el uso de SQL%ROWCOUNT??

  7. Lorena Says:
    Mayo 12, 2008 at 8:03 pm

    Al utilizar la centencia FETCH con la tabla de departments se devuelven los 15 primeros departamentos con sus respectivos ID lo que he cambiado es el número de deparmntos que quiero ver en la sentencia repetitiva FOR

    SET SERVEROUTPUT ON
    DECLARE
    v_depno departments.department_id%TYPE;
    v_ename departments.department_name%TYPE;
    CURSOR dep_cursor IS
    SELECT department_id, department_name
    FROM departments;
    BEGIN
    OPEN dep_cursor;
    FOR i IN 1..15 LOOP
    FETCH dep_cursor INTO v_depno, v_ename;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR (v_depno)||”|| v_ename);
    END LOOP;
    END;

  8. imburguan Says:
    Mayo 12, 2008 at 8:04 pm

    A todos los compañeros le sugiero que cierren el cursor ya que los cursores constan de la seccion de declare, open, fetch y close; De manera de que se libre memoria, en estos ejercicios no lo podríamos percibir, pero no sucedería lo mismo si se tratase que el cursor recorriera grandes cantidades de información.

    Saludos Iliana

  9. claucar Says:
    Mayo 12, 2008 at 8:11 pm

    Las siguientes líneas de código permiten obtener el id y el nombre de todas las regiones existentes en la tabla regions, utilizando fetch y un contador que contiene el número total de filas que posee la tabla, también ya se utiliza la sentencia de cierre del cursor: CLOSE cursor_name.

    SET SERVEROUTPUT ON
    DECLARE
    v_region_id regions.region_id%TYPE;
    v_region_name regions.region_name%TYPE;
    v_count_row NUMBER(3);

    CURSOR reg_cursor IS
    SELECT region_id, region_name
    FROM regions;

    BEGIN
    SELECT COUNT(rownum) INTO v_count_row
    FROM regions;

    OPEN reg_cursor;
    FOR i IN 1..v_count_row LOOP
    FETCH reg_cursor INTO v_region_id, v_region_name;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_region_id)||’ ‘||v_region_name);
    END LOOP;
    CLOSE reg_cursor;
    END;
    /

    SALIDA

    1 Europe
    2 Americas
    3 Asia
    4 Middle East and Africa
    PL/SQL procedure successfully completed.

  10. Juan Pablo Angamarca Says:
    Mayo 12, 2008 at 8:15 pm

    Usando un cursor, recuperar los empleados ordenados por employee_id, presentar los empleados pares que están en la tabla. Se usa los atributos %NOTFOUND y %ROWCOUNT.


    SET SERVEROUTPUT ON
    DECLARE
    CURSOR empleados IS
    SELECT * FROM employees ORDER BY employee_id;
    TYPE reg_empleados IS TABLE OF
    empleados%ROWTYPE;
    lista empleados%ROWTYPE;
    BEGIN
    OPEN empleados;
    DBMS_OUTPUT.PUT_LINE('Empleados pares: ');
    LOOP
    FETCH empleados INTO lista;
    EXIT WHEN empleados%NOTFOUND;
    IF MOD(empleados%ROWCOUNT, 2) = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Empleado ' || empleados%ROWCOUNT || ': ' || lista.last_name || ', ' || lista.first_name);
    END IF;
    END LOOP;
    CLOSE empleados;
    END;

    Salida del programa:


    Empleados pares:
    Empleado 2: Kochhar, Neena
    Empleado 4: Hunold, Alexander
    Empleado 6: Lorentz, Diana
    Empleado 8: Rajs, Trenna
    Empleado 10: Matos, Randall
    Empleado 12: Zlotkey, Eleni
    Empleado 14: Taylor, Jonathon
    Empleado 16: Whalen, Jennifer
    Empleado 18: Fay, Pat
    Empleado 20: Gietz, William
    PL/SQL procedure successfully completed.

  11. Marcela Gomez Says:
    Mayo 12, 2008 at 8:25 pm

    Manipulamos la tabla DEPARTMENTS, Utilizamos la sentencia FETCH y mostarmos el ID y el nombre del empleado. Sale
    del ciclo cuando se a alcanzado mas de 10 filas o cuando no hay filas q recuperar

    DECLARE
    v_dep departments.department_id%TYPE;
    v_nombre departments.department_name%TYPE;
    CURSOR dep_cursor IS
    SELECT DEPARTMENT_ID , DEPARTMENT_NAME
    FROM DEPARTMENTS;
    BEGIN
    OPEN dep_cursor;
    LOOP
    FETCH dep_cursor INTO v_dep, v_nombre;
    EXIT WHEN dep_cursor%ROWCOUNT > 10 OR dep_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_dep)||’ ‘|| v_nombre);
    END LOOP;
    CLOSE dep_cursor;
    END;

    Los datos de salida son:
    10 Administration
    20 Marketing
    50 Shipping
    60 IT
    80 Sales
    90 Executive
    110 Accounting
    190 Contracting
    PL/SQL procedure successfully completed.

  12. Juan Pablo Angamarca Says:
    Mayo 12, 2008 at 8:40 pm

    Usar un cursor para recuperar números de localizaciones y direcciones de calle para poblar una tabla de la base de datos, TEMP_LIST, con esta información. El tipo de datos para crear la tabla temp_list se determinó usando DESCRIBE LOCATIONS;


    CREATE TABLE temp_list (
    locid NUMBER(4),
    locstreet VARCHAR2(40)
    );
    DECLARE
    CURSOR loc_cursor IS
    SELECT location_id, street_address
    FROM locations;
    loc_record loc_cursor%ROWTYPE;
    BEGIN
    OPEN loc_cursor;
    LOOP
    FETCH loc_cursor INTO loc_record;
    EXIT WHEN loc_cursor%NOTFOUND;
    INSERT INTO temp_list(locid, locstreet)
    VALUES(loc_record.location_id, loc_record.street_address);
    END LOOP;
    COMMIT;
    CLOSE loc_cursor;
    END;
    SELECT * FROM temp_list;


    Table created.
    PL/SQL procedure successfully completed.
    LOCID LOCSTREET
    1400 2014 Jabberwocky Rd
    1500 2011 Interiors Blvd
    1700 2004 Charade Rd
    1800 460 Bloor St. W.
    2500 Magdalen Centre, The Oxford Science Park

  13. martincx Says:
    Mayo 12, 2008 at 8:43 pm

    Un ejemplo de los Loop FOR para los cursores.
    En el cual obtenemos todos los paises almacenados en la tabla countries.

    Observamos que las operaciones de open y fetch ocurren de manera implicita en el cursor.

    SET SERVEROUTPUT ON
    DECLARE
    CURSOR countries_cursor IS
    SELECT COUNTRY_NAME, REGION_ID
    FROM COUNTRIES;

    BEGIN
    for countries_record IN countries_cursor LOOP
    DBMS_OUTPUT.PUT_LINE (countries_record.country_name);
    END LOOP;
    END;

    _________________________________________
    La salida es :

    Canada
    Germany
    United Kingdom
    United States of America
    PL/SQL procedure successfully completed.

  14. claucar Says:
    Mayo 12, 2008 at 8:47 pm

    A continuación un ejemplo usando %NOTFOUND, cursores y registros, que muestra el id y nombre de las regiones de la tabla regions.

    SET SERVEROUTPUT ON
    DECLARE
    CURSOR reg_cursor IS
    SELECT region_id, region_name
    FROM regions;

    reg_record reg_cursor%ROWTYPE;

    BEGIN
    OPEN reg_cursor;
    LOOP
    FETCH reg_cursor INTO reg_record;
    EXIT WHEN reg_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(reg_record.region_id||’ ‘||reg_record.region_name);
    END LOOP;
    CLOSE reg_cursor;
    END;
    /

    SALIDA

    1 Europe
    2 Americas
    3 Asia
    4 Middle East and Africa
    PL/SQL procedure successfully completed.

  15. imburguan Says:
    Mayo 12, 2008 at 8:50 pm

    A continuación les presento el uso de Cursor For Loops usando subqueries de los empleados que trabajan en el departamento de ‘Sales’

    SET SERVEROUTPUT ON
    DECLARE
    BEGIN
    FOR emp_record IN (SELECT last_name, department_id FROM employees)LOOP

    IF emp_record.department_id = 80 THEN

    DBMS_OUTPUT.PUT_LINE (’El Empleado ‘||’ ‘||emp_record.last_name || ‘ trabaja el departamento Sales.’);
    END IF;
    END LOOP;
    END;

  16. Luis Castillo Says:
    Mayo 12, 2008 at 8:50 pm

    Este cursor es una modificación del ejercicio de la pagina 6-23 del libro, en el que mando a busacar los nombres de los empleados y filtro aquellos que tienen trabajan en el departamento Executive
    DECLARE
    CURSOR EMPLEADO_DEPART IS
    SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E, departments D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
    BEGIN
    FOR EMP_DEP IN EMPLEADO_DEPART LOOP
    IF UPPER(EMP_DEP.DEPARTMENT_NAME) = UPPER(’Executive’ ;) THEN
    DBMS_OUTPUT.PUT_LINE(EMP_DEP.FIRST_NAME ||’ ‘|| EMP_DEP.LAST_NAME ||’ ,TRABAJA EN EL DEPARTAMENTO ‘||EMP_DEP.DEPARTMENT_NAME );
    END IF;
    END LOOP;
    END;
    /
    SALIDA
    Steven King ,TRABAJA EN EL DEPARTAMENTO Executive
    Neena Kochhar ,TRABAJA EN EL DEPARTAMENTO Executive
    Lex De Haan ,TRABAJA EN EL DEPARTAMENTO Executive
    PL/SQL procedure successfully completed.

  17. Marcela Gomez Says:
    Mayo 12, 2008 at 8:52 pm

    Imprime la dirección de las calles de la localización que se encuentra en la ciudad de Toronto

    SELECT * FROM locations;
    SET SERVEROUTPUT ON
    DECLARE
    CURSOR loc_cursor IS
    SELECT STREET_ADDRESS , CITY
    FROM LOCATIONS;
    BEGIN
    FOR loc_record IN loc_cursor LOOP
    IF loc_record.city = ‘Toronto’ THEN
    DBMS_OUTPUT.PUT_LINE (’La dirección ‘ || loc_record.STREET_ADDRESS ||’ pertenece a la ciudad de Toronto’);
    END IF;
    END LOOP;
    END;

    La dirección 460 Bloor St. W. pertenece a la ciudad de Toronto
    PL/SQL procedure successfully completed.

Leave a Reply