BLOQUES ANONIMOS PARTE III
OBJETIVOS:
Trabajar con registros PL/SQL: RECORD.
Construir bloques anónimos en los cuales se utilizan cursores.
RECOMENDACIONES
Cuando un bloque PL/SQL contiene una instrucción DBMS_OUTPUT.PUT_LINE antes de ejecutarlo
se debe activar la salida en pantalla, usando la instrucción: SET SERVEROUT ON. Para desactivar la
salida en pantalla: SET SERVEROUT OFF
CURSORES
Los programas PL/SQL tratados previamente emplean órdenes SQL cuyo retorno es una única fila o
ninguna fila, no obstante cuando se generan múltiples filas se hace necesario el uso de cursores que
permiten al programa tomar explícitamente el control del procesamiento de las órdenes SQL.
Por lo tanto un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente
los cursores son fragmentos de memoria que reservados para procesar los resultados de una
consulta SELECT.
Podemos distinguir dos tipos de cursores: Cursores implícitos, Cursores explícitos.
CURSORES IMPLÍCITOS
Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta
devuelve un único registro. Son los que trabajamos en la clase pasada donde se tenía que utilizar la
palabra clave INTO.
Ejemplo:
SET SERVEROUTPUT ON;
DECLARE
nom_empleado [Link]%TYPE;
BEGIN
SELECT nombre
INTO nom_empleado from empleado
WHERE cargo ='VENDEDOR CAT 2';
DBMS_OUTPUT.PUT_LINE( ''El nombre es'||nom_empleado);
END;
Los cursores implícitos sólo pueden devolver una fila, por lo que pueden producirse determinadas
excepciones. Las más comunes que se pueden encontrar Son:
Excepcion Explicacion
NO_DATA_FOUND Se produce cuando una sentencia SELECT intenta recuperar datos pero ninguna fila
satisface sus condiciones. Es decir, cuando "no hay datos"
TOO_MANY_ROWS Dado que cada cursor implicito sólo es capaz de recuperar una fila , esta excepcion
detecta la existencia de más de una fila.
EJEMPLO:
SET SERVEROUTPUT ON;
SET VERIFY OFF;
DECLARE
nom_empleado [Link]%TYPE;
BEGIN
SELECT nombre
INTO nom_empleado from empleado
WHERE cargo ='VENDEDOR CAT 5';
DBMS_OUTPUT.PUT_LINE( 'El nombre es'||nom_empleado);
EXCEPTION
-- WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('El nombre ' ||nom_empleado ||'no existe');--
END;
REALICE LE MISMO EJERCICIO PERO PARA VENDEDOR CAT Y UTILICE LA EXCEPCION DE
TOO_MANY_ROWS
PARA LAS EXCEPCION CREADA POR EL USUARIO
Insertar registros en la tabla mediante un contador que se va
incrementando y toma valores desde 1 hasta 10.
DECLARE
V_contador number(6) :=1;
BEGIN
LOOP
INSERT INTO prueba(Valor) VALUES (V_contador);
V_contador:=V_contador +1;
EXIT WHEN V_contador =10;
END LOOP;
END;
2
CURSORES EXPLÍCITOS
Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la
consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que
devuelven un único registro por razones de eficiencia. Son más rápidos.
En resumen los cursores explícitos se emplean para realizar consultas SELECT que pueden devolver
cero filas, o más de una fila.
Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:
Declarar el cursor. DECLARE
Abrir el cursor con la instrucción OPEN.
Leer los datos del cursor con la instrucción FETCH.
Cerrar el cursor y liberar los recursos con la instrucción CLOSE.
Para declarar un cursor debemos emplear la siguiente SINTAXIS:
Declarar el cursor. DECLARE
CURSOR nombre_cursor IS
instrucción_SELECT
EJEMPLO:
DECLARE
CURSOR C1 IS
SELECT cod_empleado, nombre
FROM empleado
WHERE cargo = ‘VENDEDOR CAT 1’;
Tenga en cuenta:
No incluya la cláusula INTO en la declaración del cursor.
Si es necesario procesar filas en algún orden, incluya la cláusula ORDER BY.
Abrir el cursor con la instrucción OPEN.
OPEN nombre_cursor;
EJEMPLO:
OPEN C1;
Leer los datos del cursor con la instrucción FETCH
FETCH nombre_cursor INTO lista_variables;
-- o bien ...
FETCH nombre_cursor INTO registro_PL/SQL;
EJEMPLO:
3
FETCH C1 INTO c_empleado, nom_empleado
Tenga en cuenta:
Incluya, en el FETCH, el mismo número de variables que las definidas en el cursor, y
en el mismo orden.
Compruebe si el cursor tiene filas.
Cerrar el cursor y liberar los recursos con la instrucción CLOSE
CLOSE nombre_cursor;
VER ARCHIVO DE ATRIBUTOS PARA LOS CURSORES
EJEMPLO:
CLOSE C1;
EJEMPLO:
DECLARE
CURSOR C1 IS
SELECT cod_empleado, nombre
FROM empleado
WHERE cargo ='VENDEDOR CAT 2';
c_empleado empleado.cod_empleado%TYPE;
nom_empleado [Link]%TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO c_empleado, nom_empleado;
DBMS_OUTPUT.PUT_LINE('Nombre del empleado es: '||c_empleado ||' - '|| nom_empleado);
CLOSE C1;
END;
CORRER EL MISMO BLOQUE PERO DONDE LE CAMBIEMOS LA CONDICION YA NO PARA
LA CATEGORIA 'VENDEDOR CAT 1';
¿QUE OBSERVAMOS CUANDO CORREMOS ESTE BLOQUE?
4
DECLARE
CURSOR C1 IS
SELECT cod_empleado, nombre
FROM empleado
WHERE cargo ='VENDEDOR CAT 1';
c_empleado empleado.cod_empleado%TYPE;
nom_empleado [Link]%TYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO c_empleado, nom_empleado;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nombre del empleado es: '||c_empleado
||' - '|| nom_empleado);
END LOOP;
CLOSE C1;
END;
CORRER EL MISMO BLOQUE PERO CON WHILE
DECLARE
CURSOR C1 IS
SELECT cod_empleado, nombre
FROM empleado
WHERE cargo ='VENDEDOR CAT 1';
c_empleado empleado.cod_empleado%TYPE;
nom_empleado [Link]%TYPE;
BEGIN
OPEN C1;
FETCH C1 INTO c_empleado, nom_empleado;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('Nombre del empleado es: '||c_empleado ||' - '||
nom_empleado);
FETCH C1 INTO c_empleado, nom_empleado;
END LOOP;
CLOSE C1;
END;
5
CORRER EL MISMO BLOQUE PERO CON FOR
DECLARE
CURSOR C1 IS
SELECT *
FROM empleado
WHERE cargo ='VENDEDOR CAT 1';
Registro empleado%ROWTYPE;
BEGIN
FOR REGISTRO IN C1 LOOP
DBMS_OUTPUT.PUT_LINE('Nombre del empleado es: '||
registro.cod_empleado ||' - '||[Link]);
END LOOP;
END;
MIRAR EL EJERCICIO PARA EL ATRIBUTO ISOPEN
DECLARE
CURSOR C1 IS
SELECT *
FROM empleado
WHERE cargo ='VENDEDOR CAT 1';
Registro empleado%ROWTYPE;
BEGIN
open c1;
if c1%isopen then
DBMS_OUTPUT.PUT_LINE('No puede ejecutar nada');
else
FOR REGISTRO IN C1 LOOP
DBMS_OUTPUT.PUT_LINE('Nombre del empleado es: '||
registro.cod_empleado ||' - '||[Link]);
END LOOP;
end if;
END;
MIRAR EL EJERCICIO PARA EL ATRIBUTO ROWCOUNT
DECLARE
CURSOR C1 IS
SELECT *
FROM empleado
WHERE cargo ='VENDEDOR CAT 1';
Registro empleado%ROWTYPE;
resulta number(10);
BEGIN
FOR REGISTRO IN C1 LOOP
DBMS_OUTPUT.PUT_LINE('Nombre del empleado es: '||registro.cod_empleado
||' - '||[Link]);
resulta := c1%ROWCOUNT;
6
DBMS_OUTPUT.PUT_LINE (resulta||'Filas contada');
END LOOP;
END;
EJERCICIO
Mostrar un bloque anónimo que permite simular una interfaz de usuario a través de la cual se
ingresa un rango de fechas y se muestra la información cliente: nombre del cliente, fecha de la
venta y el valor de la misma. Este ejercicio debe realizarse con cursores
DECLARE
v_fecha1 [Link]%TYPE;
v_fecha2 [Link]%TYPE;
--Declaramos el cursor--
CURSOR c_ventas IS
SELECT [Link], valor,[Link]
FROM clientes, ventas
WHERE codigo = cod_clientes
AND [Link] BETWEEN v_fecha1 AND v_fecha2;
BEGIN
v_fecha1 := '&FechaInicial';
v_fecha2 := '&FechaFinal';
--Ahora es cuando empieza el bucle, se realiza OPEN implícito de
C_ventas--
FOR v_reg_ventas IN c_ventas LOOP
--El FETCH lo ejecuta de forma implícita--
DBMS_OUTPUT.PUT_LINE('La fecha de la venta es : '||v_reg_ventas.fecha ||' -
'||'Corresponde al valor de: '||' - '|| v_reg_ventas.valor ||' Cliente: '|| v_reg_ventas. nombre);
END LOOP;
--Ahora se cierra el cursor--
END;
7
EJERCICIO
1. Realizar un CURSOR QUE TRAIGA LA LISTA DE CLIENTES donde muestre sus
nombres, teléfono y fecha BAJO LAS SIGUIENTES CONDICIONES:
2. El mismo cursor con las siguientes especificaciones
[Link] DE CLARACION DE VARIABLES %TYPE;
[Link] LOS REGISTROS CON EL MISMO FORMATO QUE LAS FILAS DE LAS
TABLAS (ROWTYPE)
c. LA DECLARACION Tipo rgistro
[Link] WHILE, CON FOR
