CURSORES,EXCEPCIONES Y CONTROL DE TRANSACCIONES PL/SQL

Cursores

Cursores explícitos

Se utilizan para trabajar con consultas que pueden devolver más de una fila. Hay 4 operaciones básicas para trabajar con un cursor explicito.

    1 .  Declaración del cursor: El cursor se declara en la zona de declaraciones con el siguiente formato:

   

CURSOR <nombrecursor> IS SELECT<sentencia select>;

    2.      Apertura del cursor : En la zona de instrucciones hay que abrir el cursor

OPEN <nombrecursor>

Al  hacerlo se ejecuta automáticamente la sentencia SELECT asociada y sus resultados se almacenan en las estructuras internas de memoria manejadas por el cursor.     

    3.       Recogida de información para recoger información almacenada en elcursor utilizaremos el siguiente formato :

FETCH <nombrecursor>INTO{<variable>|<listavariables> };

Después del into figurara una variable que recogerá la información de todas las columnas. En este caso la variable puede ser declarada de esta forma:

<variable><nombre cursor >%ROWTYPE

O una lista de variables . cada una recogerá la columna correspondiente de la clausula  SELECT                por tanto , serán del mismo tipo que las columna , cada fetch recupera una fila y el cursor avanza automáticamente a la fila que sigue.

   4.    Cierre del cursor: cuando el cursor no se va a utilizar hay que cerrarlo

CLOSE<nombrecursor>;

Atributos del  cursor .

Para conocer detalles respecto a la situación dl cursor hay cuatro atributos para consulta

%FOUND : devuelve verdadero si el ultimo FETCH ha recuperado algún valor; en caso contrario devuelve falso. Si el cursor no estaba abierto devuelve error y si  estaba abierto pero no se había ejecutado ningún FETCH devuelve NULL   . Se suele utilizar en bucles para recuperar información.

%NOTFOUND :Hace lo contrario que el atributo anterior se suele utilizar como condición de salida en bucles.

%ROWCOUNT : Devuelve el numero de filas recuperadas hasta el momento por el cursor (Numero de FETCH realizados satisfactoriamente

%ISOPEN: Devuelve verdadero si el cursor está abierto

La siguiente tabla muestra los valores de retorno de los atributos del cursor en diferentes situaciones.

		%FOUND	%ISOPEN	%NOTFOUND	%ROWCOUNT
OPEN		Invalid_cursor	F	Invalid_cursor	Invalid_cursor
		Null	T	NULL	0
PRIMER FETCH		Null	T	NULL	0
		T	T	F	1
SIGTE		T	T	F	1
		T	T	F	…
ULTIMO FETCH		T	T	F	N
		F	T	T	N
CLOSE		F	T	T	N
		Invalid_cursor	F	Invalid_cursor	Invalid_cursor

El siguiente ejemplo ilustra lo que hemos visto hasta ahora de cursores

Ej. 1. Podemos observar que a diferencia de lo que ocurre en los cursores implicitos, la sentencia SELECT en la declaración  del cursor no contiene la clausula INTO para indicar las variables que recibiran la información. Esta clausula into de especifica cada vez que se realiza un FETCH .
despues de FETCH debe comprobarse el resultado.

EJ. 3: El siguiente ejemplo muestra los empleados que estan en el departamento no.20 con el atributo ROWCOUNT que devuelve elnumero de empleados que hay.

Variables de acoplamiento en el manejo de cursores

En muchas ocasiones la clausula SELECT del cursor deberá seleccionar las filas de acuerdo con una condición. Cuando se trabaja con SQL interactivo se introducen los términos exactos de la condición .

SQL> SELECT apellido FROM emple

WHERE dept_no=20;

Cuando Se escribe un programa PL/SQL se suele utilizr un diseño más abierto, por lo que los términos exactos de esta condición solamente se conocen en tiempos de ejecución.

Ejemplo 4  En este ejemplo se visualizan los empleados de un departamento cualquiera

Podemos observar que en la clausula where se incluye una variable que se deberá haber declarad previamente. Este tipo de variables recibe el nombre de variables de acoplamiento. El programa la sustituirá por un valor en el momento en el que se abre el cursor  y se seleccionaran las filas según dicho valor.

Una vez creado el procedimiento, se puede ejecutar;

SQL>EXECUTE  ver_emple_por _dept(30);

Cursor FOR…LOOP

Como ya hemos visto en muchas ocasiones el trabajo de un cursor consiste en:

1. ·         Declarar el cursor
2. ·         Declarar una variable que recogerá los datos del cursor
3. ·         Abrir el cursor
4. ·         Recuperar con FETCH  una a una delas filas extraídas introduciendo los datos en la variable  procesándolos y comprobando también si se han recuperado los datos o no.
5. ·         Cerrar el cursor

Por eso PL/SQL proporciona la estructura cursor FOR…LOOP que simplifica estas tareas realizando todas ellas excepto la declaración del cursor de manera implícita.

El formato y el uso d esta estructura es :

Se declara la información cursor en la selección correspondiente (como cualquier otro cursor)

Se procesa el cursor utilizando el siguiente formato :

FORnombrevareg IN nombrecursor LOOP

END LOOP;

EJ.5 En el siguiente ejemplo se visualizara el apellido, el oficio y la comision de los empleados cuya comision supera los 50000 utilizando CURSOR::FOR::LOOP 

 Ejemplos de aplicacion 
    EJ.6 Escribir un bloque PL/SQL que visualize el apellido y la fecha de alta de todos los empleados de la empresa ordenados por fecha de alta mediante una  estructura FOR::LOOP

EJ7 Escribir el bloque anterior usando el bucle WHILE 

Al  entrar en e bucle se abre el cursor de manera automática se declara implícitamente la variable nombrevareg  de tipo nombrecursor%ROWTYPE y se ejecuta el primer  fetch .

Uso de alias en las columnas de selección del cursor

Ya hemos indicado que cuando utilizamos variables de registro declaradas del mismo tipo que el cursor o que la tabla, los campos tienen el mismo nombre que las columnas correspondiente cuando esas consultas son expresiones se puede presentar un problema:

CURSOR c1 IS

SELECT deptno, count (*) ,sum(sal+nvl (comisión,0))

FROM   emp

GROUP BY deptno;

En estos casos debemos indicar una alias en la columna .

CURSOR CON PARAMETROS

Un cursor puede tener parámetros, en este caso se aplicará el siguiente formato genérico 

CURSOR<nombrecursor[(parámetro1, parametro2,…)]

IS SELECT <sentencia select en la que intervendrán los parámetros>;

Los parámetros formales indicados después del nombre del cursor tienen l siguiente sintaxis :

Nombredevariable[IN] tipodedato [{:=|DEFAULT}valor ]

Todos los parámetros formales de un cursor son parámetros de entrada. El ámbito de estos parámetros es local al cursor por eso solamente puede ser referenciados dentro de la consulta.

DECLARE

 CURSOR  cur1

(v_dept  NUMBER, v_oficio VARCHAR2 DEFAULT ‘DIRECTOR’)

IS SELECT apellido,salario FROM emple

WHERE deptno=v_deptno AND oficio =v_oficio;

Para abrir un cursor pasándole parámetros los haremos así.

OPEN nombrecursor [(parametro1, parametro2,…)];

Donde parámetro1, paremetro2 son expresiones que contienen los valores que se pasara al cursor . No tienen por que ser los mismos valores de las variables indicadas como parámetros al declarar al cursor ; es más si lo fueran serian consideradas como variables distintas .

 Atributos en cursores implícitos

Oracle abre implícitamente un cursor cuando procesa un comando SQL que no esté asociado a un cursor explicito.

El valor de los atributos sql se refiere en cada momento a la ultima orden sql

·         SQL%NOTFOUND  dará TRUE si el ultimo insert, update, delete o select into han fallado.

·         SQL%FOUND  dará true si el ultimo insert, update, delete o into han afectado  a una  mas filas

·         SQL%ROWCOUNT  devuelve el numero de filas afectadas por el ultimo insert update, delete  o select into.

·         SQL%ISOPEN siempre devolverá falso, ya que Oracle cierra automáticamente el cursor  después de cada orden SQL.

Es preciso hacer algunas observaciones respecto al uso de atributos en cursores implícitos: el comportamiento de los atributos n los cursores implícitos es distinto al de los cursores explicitos.

·         A continuación se especifican algunas peculiaridades en el comportamiento y el uso de los atributos en cursores implicios.

·         Devolverán un valor relativo a la orden INSERT, UPDATE, DELETE O SELECT.. INTO aunque el cursor esté cerrado.

En el caso de que se trate deun  SELECT INTO  debemos tener en cuenta que ha d devolver una fila y solo una, pues de lo contrario se producirá un error y se levntara automáticamente una excepción.

o   _NO_DATA_FOUND  si la consulta no devuelve ninguna fila

o   _TOO_MANY_ROWS si la consulta devuelve más de una fila.

Se detendrá la ejecución normal del programa y bifurcara a la selección exception.

·         Lo indicado en el párrafo anterior no es aplicable en las ordenes  INSERT,UPDATE ,DELETE  ya que en estos casos no se levantan las excepciones correspondientes .

Excepciones

Las excepciones sirven para tratar errores en tiempo de ejecución así como errores y situaciones definidas por el usuario. Cuando se produce un error PL/SQL levanta una excepción y pasa el control a la sección EXCEPTION correspondiente del bloque PL, que actuará según lo establecido y dará por finalizada la ejecución del bloque actual.

…

EXCEPTION

WHEN<nombredeExcepcion1>THEN

<instrucciones1>;

WHEN<NombredeExcepcion2>THEN

<instrucciones2>;

…

[WHEN OTHERS THEN

<instrucciones>;]

END<nombredelprograma>;

Excepciones internas predefinidas

Estan predefinidas por oracle. Se disparan automáticamente al producirse un error .

CODIGO ERROR DE ORACLE	VALOR DE SQL CODE	EXCEPCIÓN	SE DISPARAN CUANDO
ORA-06530	-6530	ACCESS_INTO_NULL	Se intenta acceder a los atributos de un objeto no inicializado.
ORA-06531	-6531	COLLECTION_IS_NULL	Se intenta acceder a elementos de una colección que no ha sido inicializada
ORA-06511	-6511	CURSOR_ALREADY_OPEN	Intentamos abrir un cursor que ya s encuentra abierto
ORA-00001	-1	DUP_VAL_ON_INDEX	Se intenta almacenar un valor que crearía duplicados en la clave primaria o en una columna con la restricción UNIQUE
ORA-01001	-1001	INVALID_CURSOR	se intenta realizar una operación no permitida sobre un cursor
ORA-01722	-1722	INVALID_NUMBER	fallo al intentar convertir una cadena en un valor numérico
ORA-01017	-1017	LOGIN_DENIED	Se intenta conectar a oracle con un usuario o una clave no válidos
ORA-01012	-1012	NOT_LOGGED_ON	Se intenta acceder a la base datos d oracle sin estar conectado a el
ORA-01403	+100	NO_DATA_FOUND	Una sentencia SLECT INTO no devuelve ninguna fila
ORA-06501	-6501	PROGRAM_ERROR	Hay un problema interno en l ejecución del programa
ORA-06504	-6504	ROWTYPE_MISMATCH	La variable del cursor del host y la variable del cursor PL/SQL pertenecen a tipos incompatibles
ORA-06533	-6533	SUBSCRIPT_OUTSIDE_LIMIT	Se intenta acceder a una tabla anidada o un array con un valor del índice ilegal
ORA-06500	-6500	STORAGE_ERROR	El bloque PL/SQL se se ejecuta fuera de memoria
ORA-00051	-51	TIMEOUT_ON_RESOURCE	Se excede el tiempo de espera para un recurso
ORA- 01422	-1422	TOO_MANY_ROWS	Una sentencia SELECT INTO devuelve mas de un fila
ORA-06502	-6502	VALUE_ERROR	Un error de tipo aritmético de conversión, de truncamiento.
ORA-01476	-1476	ZERO_DIVIDE	Se intenta a división entre 0.

EJ8  El siguiente ejemplo ilustra las ecepxiones 

Excepciones definidas para el usuario

Para su utilización hay que dar tres pasos.

      1.      Se deben declarar en la selección DECLARE de la forma siguiente :
a.       <nombredeexcepcion>EXCEPTION;
2.       Se disparan en la selección ejecutable del programa con la orden RAISE.
a.       RAISE <nombreexcepcion>;
3.       Se trata en la selección                EXCEPTION según el formato ya conocido:
a.       WHEN<nombredeexcepcion>THEN<tratamiento>;

      ex9 El siguiente ejemplo ilustra una excepcion predifinida y otra definida por el programador 

      Otras excepciones

Existen otros errores internos en oracle, similares a los asociados a las excepciones internas pero no tienen asignada una excepción sino un código de error y un mensaje de error a los que se accede mediante las funciones SQLCODE SQLERRM.

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘Error:’||SQLCODE||SQLERRM);

END;

En este ejemplo se muestra al usuario l texto ERROR  con CODIGO DE ERROR y el mensaje de error utlizando las funciones correspondientes.

Estas dos funciones SQL CODE Y SQLERRM no son accesibles desde ordenes SQL*PLUS, pero pueden para a este entorno a través de soluciones como la siguiente

WHEN OTHERS THEN

:cod_err:=SQLCODE;

:msg_err:=SQLERRM;

ROLLBACK;

EXIT;

END;

Ejemplos de aplicación 

EX10 El siguiente ejemplo ilustra lo visto hasta ahora respecto ala gestion de excepciones 

EJ 11
Suponemos los siguiente bloques con las declaraciones de excepciones y los tratamientos especificados 

Propagación y ámbito delas aplicaciones

Las gestión de excepciones en PL/SQL  tiene unas reglas que se deben considerar en el diseño de aplicaciones:

·         Cuando se levanta una excepción , el programa bifurca a la sección EXCEPTION del bloque actual. Si no está definida en ella , la excepción se propaga al bloque que llamó al actual, pasando el control a la sección EXECPTION  de dicho bloque, y así hasta encontrar tratamiento para la excepción o devolver el control al programa HOST.

·         Una vez tratada la excepción en un bloque , se devuelve el control al bloque que llamo alqu trato la excepción con independencia  del que disparó.

·         Si, después de tratar una excepción queremos volver a la línea siguiente ala que se produjo no podemos hacerla directamente pero si es posible diseñar el programa para que funcione así. Esto se consigue encerrando el comando o comandos que peudn levantar la excecion en un subbloque junto el tratamiento para la excepción.

SELECT INTO.. àpuede levantar NO_DATA_FOUND

Para que el control del programa no salga del bloque actual cuando se produzca una excepción podemos encerrar l comando n un bloque y tratar la excepción en ese bloque

BEGIN

SELECT INTO --> puede levantar NO_DATA_FOUND

EXCEPTION

WHEN NO_DATA_FOUND THEN

;  --> tratamiento para la excepcion

END;

·         La clausula WHEN OTHERS tratará cualquier excepción que no aparezca en las cláusulas WHEN correspondientes, con independencia del tipo de excepción. De este modo se evita que el tipo de excepción   se propague a los bloques de nivel superior.

·         Si la excepción se levanta en la sección declarativa (por un fallo al inicializar una variable) automáticamente se propagará al bloque que llamo al actual sin comprobar si existe tratamiento para la excepción en el mismo bloque que se levantó.

·         También se puede levantar una excepción en la sección EXCEPTION de forma voluntaria o por un error que se produzca al tratar una excepción. En este caso, se propagará de forma automática al bloque que llamó al actual, sin comprobar que existe tratamiento para la excepción en el mismo bloque que se levantó.

·         En ocasiones peude resultar conveniente , después de tratar una excepción , volver a levantar la misma excepción para que se propague al bloque del nivel superior. Esto puede hacerse indicando al final de los comandos de manejo de la excepción el comando RAISE  sin parámetros:

WHEN TOO_MANY_ROWS

;

RAISE;

·         Las excepciones declaradas en un bloque son locales al bloque , por tanto no son conocidas en bloque pero si en distintos bloques. En este caso la excepción del subbloque prevalecerá sobre el bloque, auqnue esta ultima se puede levantar desde el ubbbloque utlizando la notación de punto (RAISE nombredelbloque.nombreexcepcion).

·         Las variablslocales , las globales , los atributos de un cursor etc, se pueden referenciar desde la sección EXCEPTION según las reglas de ámbito que rigen para los objetos del bloque.Pero si la excepción se ha disparado dentro de un bucle cursor FOR..LOOP , no se podrá acceder  los atributo de un cursor , ya que oracle cierra este cursor antes de disparar la excepción.

·         En la sección EXCEPTION  no se puede usar <GOTO etiqueta> para salir de esta sección o entrar en otra cláusula WHERE.

Utilización de RAISE_APPLICATION_ERROR

En el paquete DBMS_STANDARD se incluye un procedimiento muy útil llamado RAISE_APPLICATION_ERROR  que sirve para levantar errores y definir y enviar mensajes de error. Su formato es el siguiente

RAISE_APPLICATION_ERROR(numero_de_error, mensaje_de_error)

Donde Número_de_error es un numero comprendido entre -20000 y -20999 y mensaje_de_error es una cadena de hasta512 bytes.

Control de transacciones

Una transacción se puede definir como un conjunto de operacions que s realizan en la base de datos : una transacción , por tanto, no se circunscribe al ámbito de una orden SQL o al de un bloque PL/SQL   

 Oracle garantiza la consistencia delos datos en una transacción en términos de VALE TODO o NO VALE NADA, es decir, o se ejecutan todas las  operaciones que componen una transacción o no se ejecuta ninguna asi pues la base de datos tiene un estado antes de la transacción o un estado después de la transacción , pero no hay estados intermedios.

Una transacción comienza con la primera orden de SQL  de la sesión del usuario o con la primera orden de SQL posterior a la finalización de la transacción anterior.

La transacción finaliza cuando se ejecuta un comando de control de transacciones (COMMIT O ROLLBACK) una orden de definición de datos (DDL) o cuando finaliza la sesión.

BEGIN

UPDATE cuentas SET saldo =saldo –v_importe_transfer

WHERE num_cuenta =v_cta_origen;

UPDATE cuentas SET saldo =saldo+v_importe_transfer

WHERE num_cta=v_cta_destino;

COMMIT WORK;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK WORK;

END;

En el ejemplo anterior se garantiza la transferencia se llevara a cabo totalmente o que no se realizara ninguna operación , pero en ningún cas se quedará a medias.

·         COMMIT: Dar por concluida la transacción actual y hace definitivos los cambios efectuados.

·         ROLLBACK: Da por concluida la transacción actual y deshace los cambios que se pudiese haber producido en la misma.

·         ROLLBACK  implícitos: cuando un subprograma almacenado falla y no se controla la excepción que produjo el fallo, oracle automáticamente ejecuta un rollback sobre todo lo realizado por el subprograma, salvo que en el subprograma hubiese un COMMIT.

·         SAVEPOINT: Se utiliza para poner marcas  puntos de salvaguarda al procesar transacciones. Se usa conjunción con rollback to, esto permite deshacer parte de una transacción.

Podemos observar que ROLLBACK TO  <punto_de_salvaguarda> deshace el trabajo realizad sobre la base da datos después del punto indicado incluyendo posibles bloqueos .

El ámbito de los puntos de salvaguarda es el definido por la transacción desde que comienza hasta que termina por tanto trasciende de las reglas de ámbito y visibilidad de otros identificadores.

Por omisión el número de savepoints está limitado a 5 por sesión pero se puede cambiar el arámetro de savepoints del fichero de inicialización de de Oracle hasta 255.

·         SET TRANSACTION READ ONLY: Establece el comienzo de una transacción de  solo lectura. Se utiliza para garantizar la consistencia d los datos recuperados.

Uso de cursores para actualizar filas

CURSOR FOR UPDATE: Hasta el momento hemos venido utilizando los cursores solo para seleccionar datos , pero también se puede utilizar l nombre de un cursor que apunta a una fila para realizar una operación de actualización en esa fila. Cuando se prevea esa posibilhabra que añadirle a la declaracion del cursor FOR UPDATE al final:

  CURSOR nombrecursor<declaración del cursor > FOR UPDATE

FOR UPDATE indica que las filas seleccionadaspor el cursor van a ser actualizadas o borradas . todaslas flas seleccionadas van a ser bloqueadas tan pronto se abra el cursor y serán desbloqueadas al terminar las actualizaciones (al ejecutar COMMIT explicita o implícitamente.

Una vez declarado un cursor FOR UPDATE  se incluirá el especificador CURRENT OF  nombredelcursor  en la clausula WHERE para actualizar o borrar la ultima fila recuperada mediante la orden fetch.

Si la consulta del cursor hace referencia a múltiples tablas se deberá usar FOR UPDATE OF  nombre de columna, con lo que únicamente se bloquearan las filas correspondientes dela tabla que tenga la columna especificada.  

Uso de ROWID en lugar de FOR UPDATE

La utilización de la clausula FOR UPDATE plantea algunas cuestiones que algunas veces pueden ser problematicas :

-se bloquean todas las de la SELECT , no solo la que se esta utilizando en un momento dado.

- Si se ejecuta un COMMIT después ya no e puede ejecutar un FETCH

Para evitar estos problemas se puede utilizar el ROWID  que indicará la fila que se va a actualizar en lugar de FOR UPDATE .

EJ12 El siguiente ejemplo se realiza un procedimiento para subir el salario de los empleados  utilizando excepciones

EJ13
EL siguiente procedimiento subirá el salario de todos los empleados del departamento indicado en la llamada. la subida será el porcentaje indicado en la llamada.

 EJ 14
EL siguiente ejemplo se suben los salarios con el FOR UPDATE .