Fundamentals PL/SQL: Chapter 06 Writing Explicit Cursors
Mayo 8, 2008 — Nelson Piedra18 comentarios para “Fundamentals PL/SQL: Chapter 06 Writing Explicit Cursors”
Escribe un comentario
-
100 Zapatitos
Loxa Bloggers 2007
-
CC
-
Comentarios recientes
Convenio de derechos… en Ciencia y Desarrollo en Améric… marta en Loja Ecuador, Conozca mi … vane en Loja Ecuador, Conozca mi … -
My site is worth $2,053.
How much is yours worth? -
Flickr Photos
More Photos 
Programa en Java y disfruta de un café lojano-
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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
Mayo 12, 2008 a las 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;
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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??
Mayo 12, 2008 a las 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;
Mayo 12, 2008 a las 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
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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;
Mayo 12, 2008 a las 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.
Mayo 12, 2008 a las 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.
Junio 26, 2008 a las 10:59 am
Hola a todos:
Tengo una duda sobre cursores y necesito ayuda.
Tengo por ejemplo:
open c1 for
select *
from empleados
Estoy trabajando con REF CURSOR y necesito 2 cosas:
1. recorrer el cursor (c1) para obtener ciertos conjunto de datos
(todos los departementos asociados al conjunto de empleados)
2. enviar c1 de vuelta a un procedimiento almacenado.
Asi las soluciones posibles son:
A: duplicarlo antes de comenzar a recorrer c1:
c2 := c1,
Pero una vez recorrido c1, el cursor C2 queda tb sin registros y por tanto no lo puedo enviar de vuelta para hacer el paso 2.
B: una vez recorrido c1, volverlo al punto de inicio (se puede hacer eso con el REF CURSOR?)
Estaria muy agradecida con su ayuda,
desde ya muchas gracias
Saludos