UD6.

b
Programación de Bases de Datos
Manejo de errores. Excepciones
Cualquier situación de error es llamada excepción en PL/SQL.

Cuando se detecta un error, una excepción es lanzada, es decir, la ejecución

normal se para y el control se transfiere a la parte de manejo de excepciones.

La parte de manejo de excepciones es la parte etiquetada como EXCEPTION y

constará de sentencias para el manejo de dichas excepciones, llamadas
manejadores de excepciones.

La parte OTHERS captura cualquier excepción no capturada.

Las excepciones pueden estar definidas por el usuario o definidas

internamente.

Si las excepciones son capturadas cuando se produce un error en el programa,

se ejecutan las instrucciones del bloque de tratamiento de la excepción, y
continúa la ejecución del programa.

Si se produce un error que no es capturado, el programa acaba, aborta su

ejecución.
Excepciones
DECLARE

BEGIN

EXCEPTION
WHEN excepción1 [OR excepción2] THEN
--manejamos la excepción
WHEN excepción3 [OR excepción4] THEN
--manejamos la excepción
WHEN OTHERS THEN
--manejamos cualquier excepción que no
--corresponda con las anteriores

END;
/
Manejo de errores. Excepciones
Tipos de excepciones
Excepción Descripción Instrucciones de Manejo
Errores Predefinidos Errores que ocurren con No hay necesidad de declarar esta
de Oracle. frecuencia en PL/SQL. excepciones. Son predefinidas por
Oracle y se levantan de forma
implicita.

Errores No- Cualquier otro error Es necesario declararlos dentro de la

Predefinidos estándar del servidor de parte declarativa; el
de Oracle. Oracle. servidor Oracle levanta el error de
forma implícita, y luego puedes
capturarlo en el controlador de
excepciones.

Errores definidos por X condición que el Deben ser declarados y levantados de

e usuario. programador entiende forma explicita.
como anormal.

Nota: Algunas herramientas/aplicaciónes que interactuan con la Base de Dato Oracle (como Oracle
Developer Forms) tienen sus propias excepciones.
Excepciones implícitas o predefinidas
Las excepciones predefinidas se lanzarán automáticamente asociadas
a un error de Oracle. Las excepciones definidas por el usuario
deberán definirse y lanzarse explícitamente.

Cuando Oracle produce una

excepción, se debe capturar
antes de que llegue al usuario,
programando lo que se debe
hacer en cada caso, evitando la
terminación abrupta e
incorrecta del programa.
 Cuando manejamos una excepción no podemos
continuar por la siguiente sentencia a la que la lanzó.
DECLARE
BEGIN
insert into emple VALUES (10,’Cañaveras', 'vendedor',null,
TO_DATE('2018/05/01', 'yyyy/mm/dd'), null,null,10);

insert into emple VALUES (20, 'Bermejo', 'analista', null,

TO_DATE('2018/05/01', 'yyyy/mm/dd'), null,null,10);

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line('Clave duplicada');
END;
/

Pero sí podemos encerrar la sentencia dentro de un bloque,

y ahí capturar las posibles excepciones, para continuar con
las siguientes sentencias.
Pero podemos meter un bloque dentro de otro
para continuar después de la excepción
Ejercicio
Crea la tabla prueba con el campo id (numérico), y defínelo como PK
Inserta varios registros (1,2,…)
Ejercicio
Crea un bloque anónimo que permita insertar 50 nuevos registros en la tabla
declare
contador number:=0;
id number:=1;
Begin
loop
begin
insert into prueba values (id);
contador:=contador+1;
id:=id+1;
exit when contador=50;

Exception
when dup_val_on_index then
id:=id+1;
end;
end loop;
end;
/
Ejercicio
Crea un bloque anónimo que permita insertar 50 nuevos registros en la tabla
declare
valor number;
valorInicial number:=0;
begin
select max(id) into valorInicial from prueba;
valor:=valorInicial;
valor:=valor +1;
loop
begin
insert into prueba values (valor);
exit when valor=(valorInicial+50);
valor:=valor+1;
exception
when dup_val_on_index then
valor:=valor+1;
end;
end loop;
end;
/
Manejo de errores. Excepciones
El alcance de una excepción sigue las mismas reglas que el de una
variable, por lo que si nosotros redefinimos una excepción que ya es
global para el bloque, la definición local prevalecerá y no podremos
capturar esa excepción a menos que el bloque en la que estaba
definida esa excepción fuese un bloque nombrado, y podremos
capturarla usando la sintaxis:
nombre_bloque.nombre_excepcion

Las excepciones predefinidas están definidas globalmente. No

necesitamos (ni debemos) redefinir las excepciones predefinidas.
Control de Errores No-Predefinidos de Oracle

Asocia una excepción definida por el usuario con un error

de ORACLE

DECLARE
--Declaración de la excepción
nExcepcion EXCEPTION;
pragma exception_init(nExcepcion, codigoErrorNegativo);
BEGIN
…

EXCEPTION
WHEN nExcepcion THEN
…
END;
/
Control de Errores No-Predefinidos de Oracle
DECLARE
e_insert_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_insert_excep, -01400);
BEGIN
INSERT INTO depart (depart_no, dnombre)
VALUES (280, NULL);

EXCEPTION
WHEN e_insert_excep THEN
DBMS_OUTPUT.PUT_LINE('INSERCIÓN FALLIDA.');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/*El ejemplo intenta insertar el valor NULL en la columna de

department_name de la tabla departments. Sin embargo, la
operación no tiene éxito porque department_name es una columna
NOT NULL.*/
Control de Errores No-Predefinidos de Oracle
DECLARE
e_numero_invalido EXCEPTION;
v_entrada VARCHAR2(4);
v_numero NUMBER;
PRAGMA EXCEPTION_INIT(e_numero_invalido, -06502);
BEGIN
v_entrada := '&numero';
v_numero := v_entrada;
--
DBMS_OUTPUT.PUT_LINE('Tabla del '||v_numero);
FOR i IN 1..12 LOOP
DBMS_OUTPUT.PUT_LINE(v_numero||' X '||i||': '||i*v_numero);
END LOOP;

EXCEPTION
WHEN e_numero_invalido THEN
DBMS_OUTPUT.PUT_LINE('Entrado invalida: '||v_entrada);
DBMS_OUTPUT.PUT_LINE('Debe introducir un valor num‚rico.');
END;
/*El ejemplo pide al usuario introducir un valor numérico para luego mostrar la tabla de
multiplicación de dicho número*/
Excepciones lanzadas por el programador

En PL/SQL nosotros podemos definir nuestras propias

excepciones en la parte DECLARE de cualquier bloque.

Estas excepciones podemos lanzarlas explícitamente por

medio de la sentencia:
RAISE nombre_excepción
Excepciones lanzadas por el programador

DECLARE
--Declaración de la excepción
PUNTOS_NEGATIVOS EXCEPTION;
BEGIN
…
valor:=-10;
IF puntuación < 0 THEN
RAISE PUNTOS_NEGATIVOS
END IF

EXCEPTION
WHEN PUNTOS_NEGATIVOS THEN
dbms_output.put_line(‘No están permitidas
puntuaciones negativas’);
END;
/
Raise_application_error

Permite generar nuestras excepciones forzando a

que la ejecución aborte.
Este recurso es de gran ayuda dado que permite
interrumpir la ejecución de un proceso dotando a
la excepción de un código y un mensaje
personalizado.

RAISE_APPLICATION_ERROR(error_number,
message [, (TRUE|FALSE)]);
Información sobre el error

Estas funciones nos permiten obtener información

relativa al error que se ha producido,

SQLCODE devuelve el número o código de error,

SQLERRM devuelve el mensaje de error.

Nota: No es posible utilizar SQLCODE o SQLERRM directamente en una

instrucción SQL. En su lugar, debe asignar sus valores a variables locales, y luego usar las
variables en la sentencia SQL.

[Link]
Ejemplo

declare
p_valor integer;
begin
p_valor:=&DimeNumero;
if p_valor < 0 then
raise_application_error(-20000,'No es
posible calcular la raíz cuadrada de un número
negativo');
end if;
dbms_output.put_line(sqrt(p_valor));
end;
 Ejercicios

1. Para un número de empleado dado, incrementarle el salario en un 10%.

Controlar dos excepciones predefinidas de ORACLE: NO_DATA_FOUND y
TOO_MANY_ROWS.

2. Por medio de comandos SQL*Plus, aceptar tres valores que correspondan

al número, nombre y localidad de un departamento.

Realizar un bloque PL/SQL que inserte en la tabla DEPART los valores

aceptados. Controlar los siguientes errores:
* Si el departamento ya existe, insertar en la tabla TEMP un error.
* Si algún dato de los insertados es de mayor longitud que la especificada
en la tabla, insertar un error en la tabla TEMP.
* Si se producen otros errores, insertar en la tabla TEMP el número y el
mensaje del error producido.

Nota: El error ORACLE de longitud del dato demasiado grande es el -

Tipos de datos compuestos. Registros
Es un grupo de elementos relacionados, almacenados en campos
con sus propios nombres y tipos de datos.

TYPE nombreTipo IS RECORD (

campo1 TIPO_DATO,
campo2 TIPO_DATO
…
);

Para acceder a los campos:

mi_direcció[Link] :=‘ Avda estudiantes’;
Tipos de datos compuestos. Registros

Para asignar un registro a otro, deben ser del mismo

tipo.

No se pueden comparar registros, aunque sean del

mismo tipo.

Se puede hacer SELECT sobre registros, pero no INSERT

desde registros.
Ejercicio
Crea un tipo de dato registro llamado “persona”, que
permita almacenar:
• Nombre
• FechaNacimiento

Crea una tabla con esos mismos campos.

Crea un bloque anónimo que pida los datos de varias

personas y los introduzca en la tabla, mostrando la edad
de cada persona.
Ejercicio
Crea un bloque anónimo que muestre la edad de la
persona más y menos joven de la tabla, junto con sus
nombres.
%ROWTYPE
También podemos crear registros que mantengan los
mismos campos y tipos de datos que una tabla.

DECLARE
VbleRegistro tabla%rowtype;

Podremos acceder a vbleRegistro.campo1,…

DECLARE
TYPE departamento IS RECORD
( depart_no number,
dnombre VARCHAR2(20),
loc VARCHAR2(20));

TYPE departamentoAux IS RECORD

( depart_no number,
dnombre VARCHAR2(20),
loc VARCHAR2(20));

SUBTYPE departamentoSubtipo IS depart%ROWTYPE;

miDepart departamento;
miDepartAux departamentoAux;
miDepartSubtipo departamentoSubtipo;
BEGIN
miDepart := miDepartAux; --ilegal
miDepart := miDepartSubtipo; --legal
IF miDepart IS NULL THEN
dbms_output.put_line('esto es ilegal'); --ilegal
ENDIF;
IF miDepart = miDepartSubtipo THEN
dbms_output.put_line('esto es ilegal');
ENDIF; --ilegal
SELECT * INTO miDepart FROM depart
WHERE depart_no='10'; --legal
INSERT INTO depart VALUES (miDepartSubtipo); --ilegal

END;
Tipos de datos compuestos. Colecciones
Es un grupo ordenado de elementos, todos del
mismo tipo.

Cada elemento tiene un subíndice único que determina

su posición en la colección.

En PL/SQL las colecciones sólo pueden tener una

dimensión.

2 tipos de colecciones:
1. Arrays de longitud variable (VARRAYS)
2. Tablas anidadas
Tipos de datos compuestos.
Arrays de longitud variable (VARRAY)
Son como los arrays de cualquier otro lenguaje de
programación, pero en la declaración se indica su
tamaño máximo y el array podrá ir creciendo
dinámicamente hasta alcanzar ese tamaño.

Límite inferior → 1
Límite superior → tamaño máximo

TYPE nombre IS VARRAY (tamañoMaximo) OF tipo [NOT NULL]

Cuando definimos un VARRAY, éste es automáticamente

nulo, hay que inicializarlo para empezar a usarlo. Para
ello podemos usar un constructor.
Ejemplo de varray
DECLARE
TYPE numeros IS VARRAY(20) OF NUMBER;
tabla_numeros numeros := numeros(); --constructor sin valores
num NUMBER;
BEGIN
num := tabla_numeros.COUNT;
FOR i IN 1..10 LOOP
tabla_numeros.EXTEND;
tabla_numeros(i) := i;
DBMS_OUTPUT.PUT_LINE(tabla_numeros(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE(tabla_numeros.COUNT);
DBMS_OUTPUT.PUT_LINE(tabla_numeros.LIMIT);
DBMS_OUTPUT.PUT_LINE(tabla_numeros.FIRST);
DBMS_OUTPUT.PUT_LINE(tabla_numeros.LAST);
END;
/
¿Qué otros métodos tiene un
varray?
Ejemplo2 de varray

DECLARE

TYPE t_cadena IS VARRAY(5) OF VARCHAR2(50);

v_lista t_cadena:= t_cadena('Aitor', 'Alicia', 'Pedro','',‘’)

BEGIN

v_lista(4) := 'Tita’;
v_lista(5) := 'Ainhoa’;

END;
Subprogramas. Procedimientos y funciones

PL/SQL, como la mayoría de los lenguajes de

programación, tanto los procedimentales como los
orientados a objetos, permiten que unas determinadas
sentencias formen parte de lo que se llama
procedimiento o método (OOP)
y función.

Permitiendo la reutilización del código.

Además nos permite agrupar todas aquellas que

tengan relación en paquetes.
 Consulta a la BD

SELECT object_name, object_type

FROM USER_OBJECTS
WHERE object_type IN (‘PROCEDURE’ , ‘FUNCTION’)

SELECT text
FROM USER_SOURCE
WHERE type = ‘PROCEDURE’
AND name = ‘CONTRATAR_EMPLEADO’
Procedimientos

CREATE [OR REPLACE] PROCEDURE [esquema.] nomprocedimiento

(parámetro { IN | OUT| IN OUT} tipoDato,…)
{IS | AS}
Declaración vbles;
Declaración ctes;
Declaración cursores;
BEGIN
Instrucciones;
[EXCEPTION]
Tratamiento de excepciones;
END;
/
Ejemplo definición procedimiento y llamadas

--Declaración del procedimiento

CREATE OR REPLACE PROCEDURE listarCien AS
BEGIN
for i in 1 .. 100 loop
dbms_output.put_line(i);
end loop;
END;
/

--Ejemplo de llamada a procedimiento

declare SQL>CALL listarcien();
begin
listarcien();
end; SQL>EXECUTE listarcien();
/
Ver errores de compilación

select *

from SYS.USER_ERRORS

where NAME = 'NOMBREPROCEDIMIENTO’

and type = 'PROCEDURE'

Ejemplo de uso de parámetros
IN (entrada), OUT (solo salida), IN OUT (entrada/salida)
--Declaración del procedimiento
CREATE OR REPLACE PROCEDURE intercambio (p1 IN OUT NUMBER, p2 IN OUT
NUMBER) AS
aux NUMBER;
BEGIN
aux:=p1;
p1:=p2;
p2:=aux;
END;
/
set serveroutput on
Declare
n1 number :=3; [Link]
n2 number := 10;
begin
intercambio(n1,n2); --Llamada al procedimiento
dbms_output.put_line('n1 = ' ||n1 || chr(10) ||'n2 = '||n2);
end;
/
SQL> @d:\[Link]
Ejemplo de uso de parámetros
IN (entrada), OUT (solo salida), IN OUT (entrada/salida)
--Declaración del procedimiento
CREATE OR REPLACE PROCEDURE Tabla(n IN INT) AS
x int :=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(n ||' * ‘||x||’ = ‘ ||n*x);
x:=x+1;
EXIT WHEN x=11;
END LOOP;
END;
/

--Ejemplo de llamada a procedimiento

Declare SQL>CALL Tabla(4);
n1 int :=3;
begin
Tabla(n1); --Llamada al procedimiento
end; SQL>EXECUTE tabla(7);
/
Ejercicios

1. Diseña un procedimiento al que se le pase un

número y muestre todos los números desde 0 al
número pasado como parámetro.

2. Diseña un procedimiento al que le pasemos el

dividendo y el divisor y devuelva como parámetros
de salida el cociente y el resto.

Usa este procedimiento en un programa en el que se

divida 18 entre 4 y que muestre por pantalla el
dividendo, divisor, cociente y resto.
Funciones

CREATE [OR REPLACE] FUNCTION [esquema.] nombre

(parámetro { IN | OUT| IN OUT} tipoDato,…)
RETURN tipoDato
{IS | AS}
Declaración vbles;
Declaración ctes;
Declaración cursores;
BEGIN
Instrucciones;
--Tiene que haber al menos un RETURN valor;
[EXCEPTION]
Tratamiento de excepciones;
END; Al menos debe haber un RETURN
que devuelva un valor del mismo
/ tipo que fue declarado
Funciones. Ejemplo

Se pueden usar en expresiones con tan solo poner su

nombre y los parámetros, y será sustituida en la
expresión por el valor devuelto.
CREATE OR REPLACE FUNCTION factorial (a IN number)
RETURN number AS
f number :=1;
BEGIN
for i in 1..a loop
f:=f * i;
end loop;
return (f);
END;
/
Dentro de otro bloque: SQL>EXEC dbms_output.put_line(FACTORIAL(3));
a:= factorial(4)
Ejercicios

1. Diseña una función que se pasen como parámetros

dos números enteros y nos devuelva el mayor de
los dos.

2. Diseña una función a la que se le pasen como

parámetros dos números enteros y nos devuelva
verdadero si el primer parámetro es múltiplo del
segundo
Ejercicios
Crea una función llamada cuentaEmpleados que reciba
como parámetros un número de departamento y
devuelva el número de empleados que tiene ese
departamento.

Crea un procedimiento para insertar un registro de un

departamento nuevo dentro de la tabla DEPART, los
datos se le pasarán como parámetro y el código será el
máximo código de los departamentos existentes
sumándole 10.
Paquetes
Un paquete es un objeto que agrupa tipos,
elementos y subprogramas.

Formados por cabecera y cuerpo.

CREATE [OR REPLACE] PACKAGE nombre AS
[declaraciones públicas y especificación de subprogramas]
END [nombre];

CREATE [OR REPLACE] PACKAGE BODY nombre AS

[declaraciones privadas y cuerpo de los subprogramas
especificados]
[BEGIN sentencias de inicialización]
END [nombre];
Acceso a la BD. Cursores

Los cursores son áreas de memoria que almacenan las

filas de la BBDD devueltas por un SELECT, o por
manipulación de datos con instrucciones LMD.

2 tipos de cursores:

1. Implícitos: no necesitan ser declarados, están en

todas las sentencias LMD que devuelven una sola
fila. Si devuelve más de una fila, produciría un
error.

2. Explícitos: para cuando devuelve más de una fila

Cursores explícitos
Para las consultas que devuelven más de una fila, se deben
declarar explícitamente un cursor para procesar las filas
individualmente.

CURSOR nombre_cursor IS instrucción_SELECT

CURSOR nombre_cursor(param1 tipo1, ...,

paramN tipoN) IS instrucción_SELECT
 Cursores explícitos
Para trabajar en el bloque de código son necesarias las sgtes acciones:

1. Declaración del cursor

CURSOR nombre_cursor IS instrucción_SELECT
CURSOR nombre_cursor(param1 tipo1, ..., paramN tipoN) IS instrucción_SELECT

2. Apertura del cursor. En ese momento se ejecuta la sentencia

SELECT asociada y se almacena el resultado en el área de contexto
(estructura interna de memoria que maneja el cursor). Un puntero
señala a la primera fila
OPEN nombre_cursor;
OPEN nombre_cursor(valor1, valor2, ..., valorN);
Si no devuelve ninguna fila, no se produce ningún error, pero se debe controlar
mediante programación.
Una vez que el cursor está abierto, se podrá empezar a pedir los resultados al servidor.
 Cursores explícitos
3. Recuperación cada una de sus filas
Una vez que el cursor está abierto en el servidor se podrá hacer la petición
de recuperación de fila. En cada recuperación solo se accederá a una
única fila. Lee una fila, metiendo los datos en las vbles indicadas o
vbleRegistro.

FETCH nombre INTO {[var1, var2,…] | vbleRegistro}

Se debe recorrer el cursor hasta encontrar la información deseada o no
haya más filas (se necesita un bucle). Para saber cuándo no hay más filas
podemos consultar los siguientes atributos de un cursor:

Cada variable debe coincidir en tipo y orden con cada una de las columnas de la sentencia SELECT.
 Cursores explícitos
4. Cierre del cursor

CLOSE nombre;

Desactiva el cursor liberando la memoria que ocupaban los datos.

Se deben abrir y cerrar los cursores según se necesiten.
Hay un límite en el número de cursores que pueden estar abiertos a la vez
en la BD.
Una vez cerrado no puede recuperar datos, hasta que no se abra de
nuevo.
Si no cerrásemos el cursor, la tabla temporal quedaría en el servidor
almacenada con el nombre dado al cursor y la siguiente vez ejecutásemos
ese bloque de código, nos daría la excepción CURSOR_ALREADY_OPEN
(cursor ya abierto) cuando intentásemos abrir el cursor.
 Atención

Cuando un cursor está abierto y los

datos referenciados por la consulta
SELECT cambian, estos cambios no son
recogidos por el cursor.

El nombre del cursor es un identificador,

no una variable. Se utiliza para
identificar la consulta, por eso no se
puede utilizar en expresiones.
Ejemplo
DECLARE
CURSOR cEmple IS SELECT apellido, oficio, depart_no
FROM emple;
regEmple cEmple%ROWTYPE;
BEGIN
OPEN cEmple;
FETCH cEmple INTO regEmple; --lee primera fila
WHILE cEmple%FOUND LOOP
dbms_output.put_line([Link] ||’, ’|| [Link]
||’, ‘ || regEmple.depart_no || chr(10));
FETCH cEmple INTO regEmple;
END LOOP;
CLOSE cEmple;
END;
/
Formas de trabajar con cursores 1

OPEN nombre_cursor;
LOOP
FETCH nombre_cursor INTO variables;
EXIT WHEN nombre_cursor%NOTFOUND;
--procesar cada una de las filas
END LOOP;
Formas de trabajar con cursores 2
OPEN nombre_cursor;
FETCH nombre_cursor INTO lista_variables;
WHILE nombre_cursor%FOUND LOOP
/* Procesamiento de los registros recuperados */
FETCH nombre_cursor INTO lista_variables;
END LOOP;
CLOSE nombre_cursor;
 Formas de trabajar con
cursores. Bucle FOR de cursor
DECLARE
CURSOR cEmple2 IS SELECT apellido, oficio, depart_no
FROM emple;
BEGIN
FOR regEmple IN cEmple2 LOOP
dbms_output.put_line([Link] ||’, ’||
[Link] ||’, ‘ || regEmple.depart_no ||
chr(10));
END LOOP;
END;
• No es necesario abrir ni cerrar el cursor
• La vble registro se declara implícitamente
• En cada iteración del bucle se hace un FETCH implícitamente
• El bucle finaliza automáticamente cuando recorre todas las filas del cursor.
Ejemplo de cursores
parametrizados
DECLARE
CURSOR cemple (dep number) IS
SELECT apellido, oficio, depart_no
FROM emple
WHERE depart_no=dep;
BEGIN
FOR regEmple IN cemple(10) LOOP
dbms_output.put_line([Link] ||', '|| [Link]
||', ' || regEmple.depart_no || chr(10));
END LOOP;
END;
Ejemplo de cursores parametrizados
DECLARE
CURSOR cArt (cFml [Link]%TYPE)
IS SELECT cArtCdg, cArtDsc
FROM Articulos
WHERE cArtFml = cFml;
xCod [Link]%TYPE;
xDes [Link]%TYPE;
BEGIN
OPEN cArt('F1');
LOOP
FETCH cArt INTO xCod,xDes;
EXIT WHEN cArt%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (xDes);
END LOOP;
CLOSE cArt;
END;
Ejercicios cursores
1. Crea un procedimiento “muestraPedido”, al que le
pasemos el código de un pedido y nos muestre los datos del
cliente que realizó el pedido, así como la fecha del pedido y
código, y los detalles de los artículos facturados en ese
pedido, indicando al final el precio total, el IVA, y el precio
más IVA.
Gestiona las excepciones que se puedan dar.

2. Crea un procedimiento al que le pasemos el código de un

cliente y nos liste los datos de ese cliente:
código, nombre, ciudad y país, así como los pagos que
ha realizado, ordenados cronológicamente.
Para finalizar, que muestre la cantidad total pagada.
Implementa también el tratamiento de excepciones.
Ejemplo de fichero batch que crea un backup de la tabla
emple (.sql)
[Link] Se ejecutará
sqlplus /nolog @D:\[Link]
[Link]
CONNECT empledepart/xxxxxx
set serveroutput on
SET FEEDBACK OFF
SPOOL D:\[Link]
DECLARE
sentencia VARCHAR2(20000):= '';
BEGIN
sentencia:= 'DECLARE' || CHR (10);
sentencia:= sentencia || 'BEGIN' || CHR (10);
FOR xx IN ( SELECT APELLIDO, OFICIO FROM EMPLE) LOOP
sentencia := sentencia || 'Insert into emple(APELLIDOS, OFICIO) Values ';
sentencia := sentencia || '('''|| [Link] || ''',''' || [Link] || ''');'|| CHR (10);
END LOOP;
sentencia := sentencia || 'END;' || CHR (10);
DBMS_OUTPUT.put_line (sentencia);
END;
/
SPOOL OFF
Generará este archivo

DECLARE
BEGIN
Insert into emple(APELLIDOS, OFICIO) Values ('REY’, PRESIDENTE');
Insert into emple(APELLIDOS, OFICIO) Values ('JIMENEZ', 'DIRECTOR');
Insert into emple(APELLIDOS, OFICIO) Values ('NEGRO', 'DIRECTOR');
Insert into emple(APELLIDOS, OFICIO) Values ('CEREZO', 'DIRECTOR');
Insert into emple(APELLIDOS, OFICIO) Values ('ARROYO', 'VENDEDOR');
Insert into emple(APELLIDOS, OFICIO) Values ('GIL', ANALISTA');
Insert into emple(APELLIDOS, OFICIO) Values ('TOVAR', 'VENDEDOR');
Insert into emple(APELLIDOS, OFICIO) Values ('ALONSO', 'EMPLEADO');
Insert into emple(APELLIDOS, OFICIO) Values ('JIMENO', 'EMPLEADO');
Insert into emple(APELLIDOS, OFICIO) Values ('FERNANDEZ', 'ANALISTA');
Insert into emple(APELLIDOS, OFICIO) Values ('MUNOZ', 'EMPLEADO');
Insert into emple(APELLIDOS, OFICIO) Values ('SANCHEZ', 'EMPLEADO');
Insert into emple(APELLIDOS, OFICIO) Values ('SALA', 'VENDEDOR');
Insert into emple(APELLIDOS, OFICIO) Values ('MARTIN', ‘VENDEDOR');
END;
 Atributos en cursores implícitos
Los cursores implícitos no se pueden manipular por el usuario, pero
Oracle sí permite el uso de sus atributos.
Las sentencia a través de las que podemos obtener información de
estos atributos son: SELECT ... INTO, [INSERT, UPDATE, DELETE]
En este caso, se debe anteponer al nombre del atributo el prefijo SQL, en lugar del
nombre del cursor.
• SQL%NOTFOUND devuelve TRUE cuando la última sentencia SELECT no recuperó
ninguna fila, o cuando INSERT, DELETE o UPDATE no afectan a ninguna fila
• SQL%FOUND devuelve TRUE cuando la última sentencia SELECT devuelve alguna
fila, o cuando INSERT, DELETE o UPDATE afectan a alguna fila
• SQL%ROWCOUNT devuelve el número de filas afectadas por INSERT, DELETE o
UPDATE o las filas devueltas por una sentencia SELECT
• SQL%ISOPEN siempre devuelve FALSE, porque Oracle cierra automáticamente el
cursor implícito cuando termina la ejecución de la sentencia SELECT
Ejemplo
BEGIN
update artículos set cArtDsc = `Pantalla LCD’
where cCodArt = ‘LCD’;
if SQL%NOTFOUND then -- Otra opción : SQL%ROWCOUNT = 0

insert into articulos (cCodArt,cDesArt)

values (‘LCD’,’Pantalla LCD’);
end if;
END;
 Cursores de actualización
CURSOR nombre_cursor IS instrucción_SELECT
FOR UPDATE
las filas que devuelva el cursor van a ser los registros a ser actualizados o
borrados
UPDATE <nombre_tabla> SET <campo_1> = <valor_1>[,<campo_2> = <valor_2>]
WHERE CURRENT OF <cursor_name>

DELETE FROM table_name

WHERE CURRENT OF cursor_name;

Cuando trabajamos con cursores de actualización debemos tener en cuenta

que la sentencia UPDATE genera bloqueos en la base de datos ( transacciones,
disparadores,etc).
Ejemplo de actualización
DECLARE
CURSOR cpaises IS
select CO_PAIS, DESCRIPCION, CONTINENTE from paises
FOR UPDATE;
co_pais VARCHAR2(3);
descripcion VARCHAR2(50);
continente VARCHAR2(25);
BEGIN
OPEN cpaises;
FETCH cpaises INTO co_pais,descripcion,continente;
WHILE cpaises%found
LOOP
UPDATE PAISES SET CONTINENTE = CONTINENTE || '.'
WHERE CURRENT OF cpaises;
FETCH cpaises INTO co_pais,descripcion,continente;
END LOOP;
CLOSE cpaises;
COMMIT;
END;
Ejemplo de actualización
CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1 IS SELECT course_number
FROM courses_tbl
WHERE course_name = name_in
FOR UPDATE of instructor;
BEGIN
OPEN c1;
FETCH c1 INTO cnumber;
if c1%notfound then
cnumber := 9999;
else
UPDATE courses_tbl SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
end if;
CLOSE c1;
RETURN cnumber;
END;
Ejemplo de borrado
CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1 IS SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;
COMMIT;
end if;
close c1;
RETURN cnumber;
END;