Curso: 1º CFGS DAW

Módulo: Bases de datos

Tema 9: PL/SQL: PROGRAMACIÓN DE BD RELACIONALES

OBJETIVOS
• Describir las características de un lenguaje de programación de un SGBD.
• Manejar en los programas variables propias y del sistema.
• Identificar las estructuras de control de flujo y repetitivas.
• Conocer el funcionamiento de las funciones predefinidas.
• Definir cursores implícitos y explícitos.
• Conocer, diseñar e implementar subprogramas
• Desarrollar programas PL/SQL usando funciones y procedimientos de usuario
• Profundizar en el funcionamiento de los disparadores con varios casos de uso
• Poner en marcha el mecanismo de control de errores a través de excepciones.

CONTENIDOS QUE VAMOS A TRABAJAR

• El lenguaje PL/SQL: características, variables, tipos de datos y transformación de

tipos, asignar valores devueltos por una consulta, entrada/salida de datos.
• Estructuras de control de flujo: selectivas y repetitivas o bucles.
• Tipos propios: registros y colecciones.
• Cursores: manejo y declaración, atributos. Bucles con cursores y clausula FOR
UPDATE
• Subprogramas: procedimientos y funciones.
• Gestión de excepciones
• Disparadores o triggers
 Curso: 1º CFGS DAW
Módulo: Bases de datos

1. INTRODUCCIÓN

¿para qué queremos almacenar datos en una BD si no accedemos a ella para

gestionarlos? El acceso a una BD es fundamental para desarrollar aplicaciones que las
exploten.

Para acceder a una BD se necesitan unos controladores que permitan crear conexiones
a las BD y estos controladores dependen tanto del SGBD como del lenguaje de
programación que utilicemos para desarrollar aplicaciones. Esto es lo que vais a ver en
Java…..

Pero ocurre una cosa y es que no toda la gestión y tratamiento de los datos tiene que
hacerse a través de un programa que se desarrolla con un lenguaje de programación y
desde el que nos conectamos a la BD sino que los SGBD pueden implementar
mecanismos que potencian la automatización de los procesos de gestión de los datos,
es decir, que es el propio SGBD el encargado de las actualizaciones de los datos así
como de controlar la integridad y coherencia.
Los lenguajes con los que los SGBD pueden implementar estos mecanismos son PL/SQL
para Oracle, Transact-SQL para SQL-Server o PL/PGSQL para PostgreSQL.

PL/SQL que es el que vamos a estudiar, es un lenguaje integrado en el SGBD que hace
que la ejecución de los programas escritos con él sea más rápida y eficiente. Por
ejemplo, insertar un registro en la tabla producto si no existe, pero si existe no hay que
insertarlo.

PL/SQL (Procedure Language/SQL) entonces es:

a) Es un lenguaje integrado en el servidor de BD con lo que se procesa de forma más
rápida y eficiente
b) Amplía la funcionalidad de SQL incorporando estructuras que se encuentran en
cualquier lenguaje de programación como son:
• Variables y tipos de datos predefinidos y definidos por el usuario.
• Estructuras de control como los bucles y las sentencias IF/ELSE.
• Procedimientos y funciones.
• Tipos de objetos.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Ejemplo:
• Queremos cambiar el precio del producto llamado ‘Impresora’ y ponerle 1500€,
¿cómo lo hacemos? UPDATE producto SET precio=1500 WHERE nombre =
“Impresora”;
• Queremos cambiar el precio del producto llamado “Impresora” pero si el producto
no existe queremos añadirlo ¿cómo lo hacemos?

2. CARACTERÍSTICAS GENERALES

Con el lenguaje PL/SQL podemos embeber comandos SQL en el programa que

hagamos, pero no podemos usar comandos asociados al DDL y al DCL. Solo podremos
manipular, es decir, usar DML.

Los programas que desarrollamos con PL/SQL:

a) Se ejecutan en el servidor
b) Se almacenan junto con los datos, es decir, son un objeto más de la BD
c) No están orientados a la entrada por teclado ni a la salida por pantalla sino que más
bien leen datos de la BD o introducen y controlan la inserción de nuevos datos.

Como en cualqueir programa que desarrollemos es muy útil explicar que hace
nuestro código o para que sirve alguna estructura de datos que estmos utilizando y
esto lo hacemos introduciendo comentarios en nuestro código de la siguiente forma:

d) Cuando el comentario ocupa una línea se pone un guión

e) Cuando el comentario ocupa más de una línea se pone entre los caracteres /* */

La unidad básica de cualquier programa PL/SQL es el bloque y hay distintos tipos:

a) Bloque anónimo: se escriben de forma dinámica cuando se necesita y se ejecuta

solo una vez. Cómo no tiene nombre asignado no lo podemos llamar desde ningún
otro bloque.

b) Subprogramas que son los procedimientos y funciones almacenados en la BD. Se

pueden ejecutar tantas veces como sea necesario, basta con llamarlos desde
cualquier otro bloque.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

c) Disparadores o triggers: son bloques que se almacenan en la BD y se ejecutan

cuando se produce un evento. En este caso el evento que provoca la ejecución del
disparador es una instrucción DML (INSERT, UPDATE, DELETE)

La estructura de un bloque tiene las siguientes partes

Cualquier programa que hagamos con PL/SQL se puede ejecutar desde SQL
Developer haciendo click en el play o desde SQL Plus con el comando EXECUTE
nombreBloque. Pues bien, la / del final hay que ponerla siempre que ejecutemos el
script desde SQL-Plus.

Ejemplo de bloque ….

3. VARIABLES

Cuando un programa necesita datos para ejecutarse se tienen que llevar a la

memoria principal y ahí se le reserva un espacio y se le asigna un nombre para poder
acceder al valor que almacena. Este espacio de memoria que almacena un valor es lo
que se conoce como variable.

Cuando se declara una variable se especifica el tipo de dato que va a almacenar

y con esto estamos indicando:
a) Cuál es el rango de valores que se puede almacenar en esa variable, es decir, su
dominio.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

b) Las operaciones que se pueden realizar con los valores que almacena
c) El espacio que se tiene que reservar

A la hora de darle un nombre a una variable hay que tener en cuenta que:

• se usan letras, números y caracteres especiales $ y #

• no puede tener más de 30 caracteres
• no puede tener el nombre de un comando
• no puede coincidir con una palabra reservada o variables del sistema

La sintaxis para declarar una variable es:

nombre_variable [CONSTANT] tipo [NOT NULL] [DEFAULT valor][:= valor_inicial];

 tipo puede ser:

o tipo_dato: NUMBER | DATE | CHAR | VARCHAR | BOOLEAN
o indentificador%TYPE: declarar variables del mismo tipo que la columna de una
tabla o variable definida con anterioridad. Ejemplos:

vMiNombre v_nombre%TYPE; es una variable del mismo tipo que la variable

v_nombre.

vProducto [Link]%TYPE; vProducto es una variable del mismo tipo que

la columna nombre de la tabla producto.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

o indentificador%ROWTYPE: declarar una variable que contenga todas las

columnas de una tabla y que además son del mismo tipo. Es un registro y para
acceder a cada uno de los campos la sintaxis es [Link]. Ejemplo:
vDatosProducto producto%ROWTYPE; vDatosProducto es una variable de tipo
registro que contiene todos los campos de la tabla producto

 CONSTANT para definir una constante cuyo valor no puede ser modificado. Se debe
incluir la inicialización de la constante en su declaración.
 NOT NULL impide que a una variable se le asigne el valor nulo, y por tanto debe
inicializarse a un valor diferente de NULL.
 DEFAULT le asignamos un valor por defecto si no tiene ningún valor.
 Cuando a una variable no se inicializa, toma como valor inicial NULL.
 Una variable se puede inicializar utilizando cualquier expresión válida en el lenguaje
siempre que devuelva un valor del mismo tipo que la variable que se está
declarando. La asignación se hace con el operador := y puede ser ser:
o Un valor explicito  V_sueldoBase NUMBER (6,2) := 1200,65;
o Una expresión aritmética  V_sueldoMes NUMBER (8,2) := V_sueldoBase
*1.25;
o Una expresión lógica o boolean: se utilizan los operadores relacionales y
lógicos y devuelven TRUE o FALSE
V_SueldoAlto BOOLEAN := v_sueldoMes > 5000;
V_SueldoMedio BOOLEAN := v_sueldoMes > 2000 AND v_sueldoMes <=5000 ;
V_SueldoRaro BOOLEAN := v_sueldoMes <100 OR v_sueldoMes >500000 ;

Ejemplos:

• VSueldo NUMBER(6,2);
• VPos NUMBER (3,0):=1;
• VSgApellido VARCHAR(30) DEFAULT ‘DESCONOCIDO’
• vNumMaxAsig CONSTANT NUMBER (2,0):=15; ¿qué ventajas tiene esto?

4. TIPOS DE DATOS

Los tipos de datos que se utilizan en PL/SQL son los mismos que hemos aprendido
con SQL
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Vamos a ver algunos ejemplos de declaraciones y si están bien o mal y porqué…

TRANSFORMACIÓN DE TIPOS

Si deseamos convertir de número a carácter: TO_CHAR(20);

Si deseamos convertir de carácter a número: TO_NUMBER('20');
Si deseamos convertir de fecha a carácter: TO_CHAR( v_fecha, 'DDMMYYYY
HH24:MI:SS');
 Curso: 1º CFGS DAW
Módulo: Bases de datos

5. ENTRADA Y SALIDA DE DATOS

PL/SQL no es un lenguaje orientado a la entrada de datos a través del teclado ni

a la salida de datos a través de la pantalla, sino que es un lenguaje que permite al
programador trabajar con los datos almacenados en las tablas para analizarlos y
manipularlos.

Aun así, ofrece mecanismos sencillos para que podamos probar nuestro código
tomando datos desde el teclado y mostrando resultados por pantalla. Estos comandos
están orientados a la evaluación del código y no a realizar programas que interactúen
con el usuario y son:

a) Mostrar datos: se usa el procedimiento DBMS_OUTPUT.PUT_LINE (cadena que

queremos mostrar). Un operador muy usado es || que se utiliza para
concatenar cadenas. Ejemplo:

vUsuario VARCHAR2(30) := ‘usuario nuevo’;

vRes VARCHAR2(100) := ‘Hola’ || Vusuario || ‘, bienvenido al instituto ’
DBMS_OUTPUT.PUT_LINE(vRes);

b) Entrada de datos por teclado: se hace a través de las llamadas variables de

sustitución que son variables precedidas por el símbolo &. Ejemplo:
Select * from empleado where dni = ‘&vdni’. Si es una cadena lo que voy a leer
se pone entre comillas.

Por defecto el servidor no permite mostrar datos por pantalla y eso se

parametriza con la variable de entorno SERVEROUTPUT con la sintaxis SET
SERVEROUTPUT ON | OFF.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Ejemplo:
SET SERVEROUTPUT ON
DECLARE
V_MIVARIABLE VARCHAR2(20):='HOLA MUNDO';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_MIVARIABLE);
DBMS_OUTPUT.PUT_LINE('FIN DEL PROGRAMA');
END;

Ejemplo:

SET SERVEROUTPUT ON
DECLARE
V_NUM1 NUMBER(4,2):=10.2;
V_NUM2 NUMBER(4,2):=20.1;
BEGIN
DBMS_OUTPUT.PUT_LINE ('LA SUMA ES: '||TO_CHAR(V_NUM1+V_NUM2)) ;
END;

6. ASIGNACIÓN DE RESULTADOS A VARIABLES

Cuando hacemos una consulta SELECT obtenemos unos valores que los vamos a
utilizar para hacer operaciones con ellos y para eso tenemos que almacenarlos en
variables. La sintaxis de la sentencia que nos permite hacer esto es SELECT INTO
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Esta forma de asignar valores devueltos por una consulta a variables se utiliza
siempre que la consulta devuelva una sola fila. Cuando devuelva más de una fila
usaremos otras estructuras que veremos más adelante (cursores) y tendremos que
usar bucles para recorrerlas.

Ejemplos:

o Bloque que muestra el número de alumnos que se han matriculado en el año 2020
o Bloque que muestre el nombre y apellidos del alumno con DNI 90100200 y que
muestre el número de asignaturas en las que se ha matriculado.
o Bloque que muestre los datos de los alumnos bilingües mostrando el resultado así:
“El alumno Pepe Pérez ha obtenido el certificado el martes, 11 de abril de 2023“
o Mostrar por pantalla el alumno que más nota ha obtenido haciendo dos versiones.
Usando %ROWTYPE y otro utilizando %TYPE

7. ESTRUCTURAS DE CONTROL

Las estructuras de control son sentencias con las que se puede controlar el flujo
de ejecución de un programa. Si no existen estas estructuras, todos los comandos se
ejecutan de forma secuencial hasta que llegan al final del programa, pero si queremos
 Curso: 1º CFGS DAW
Módulo: Bases de datos

ejecutar un bloque de instrucciones varias veces o queremos ejecutar un bloque de

instrucciones en función de si se cumple o no una condición necesitamos romper la
secuencialidad con estas estructuras que son:
- Estructuras selectivas o condicionales
- Estructuras repetitivas o bucles
- Estructuras procedimentales: son estructuras que definen los
programadores a los que se les asigna un nombre y se pueden invocar desde
cualquier parte del programa.

7.1. Estructuras selectivas

Ejemplo: vamos a hacer un programa PL/SQL que cuenta en cuántas asignaturas

se ha matriculado el alumno con DNI 90100200 y en cuantas de ellas ha tenido un 5 o
más. Si son iguales mostrar el mensaje que diga “todo aprobado” y sino mostrará un
mensaje indicando el número de asignaturas que tiene pendientes. Este bloque
 Curso: 1º CFGS DAW
Módulo: Bases de datos

funcionará solo para ese DNI, pero recordad que ese DNI se puede obtener de un
formulario, de otra tabla o pidiéndoselo al usuario por teclado.

Existe otra versión de la estructura selectiva que es cuando tenemos que ir

comprobando condiciones si no se van cumpliendo. Esta estructura es:

IF condicion THEN
//instrucciones
ELSIF condicion THEN
//instrucciones
ELSIF condicion THEN
//instrucciones
[ELSE
//instrucciones
]
END IF;

Otra estructura selectiva es la que se conoce como estructura selectiva múltiple.

En este caso en lugar de evaluar una condición se evalúa una expresión y se comprueba
si coincide con algunas de los valores de la estructura.

CASE [ expresión ]
WHEN condicion_1 THEN resultado_1
WHEN condicion_2 THEN resultado_2
...
WHEN condicion_n THEN resultado_n
ELSE resultado
END

Ejemplo usándolo en una consulta:

SELECT apellido,
CASE nombre
WHEN 'Pepe' THEN 'se llama Pepe'
WHEN 'Juan' THEN 'se llama Juan'
ELSE 'no se llama ni Pepe, ni Juan'
END
FROM empleados;
 Curso: 1º CFGS DAW
Módulo: Bases de datos

7.2. Estructuras repetitivas

 Curso: 1º CFGS DAW
Módulo: Bases de datos

Para entender el funcionamiento de estas estructuras no hay nada mejor que

ponerlo en práctica con la relación de ejercicios entregada.

8. CURSORES

Los cursores se utilizan para gestionar las instrucciones SELECT. 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. Se usan cuando la consulta devuelve un único registro. Se

utiliza para operaciones SELECT …INTO. No hay que declararlos.
 Cursores explícitos. Son declarados y controlados por el programador. Se utilizan
cuando la consulta devuelve un conjunto de registros.

También se utilizan en consultas que devuelven un único registro por razones de

eficiencia. Son más rápidos.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

8.1. CURSORES EXPLICITOS

Para trabajar con un cursor explicito necesitamos realizar las siguientes tareas:

a) Declarar el cursor:
CURSOR nombre_cursor IS SELECT …..

b) Abrir el cursor con la instrucción OPEN.

Para abrir el cursor sin parámetros  OPEN nombre_cursor;
Una vez abierto el cursor se pueden extraer los datos de la consulta fila a fila e
introducirlos en un conjunto de variables asociadas a las columnas que se quieren
proetos a bien introducirlas en un registro declarado en la sección DECLARE

c) Leer los datos del cursor con la instrucción FETCH.

FETCH nombre_cursor INTO lista_variables;
FETCH nombre_cursor INTO registro_PL/SQL;

El comando FETCH recorre las filas que devuelve la consulta y se usa dentro de un
bucle para acceder a cada una de las filas, una a una.

d) Cerrar el cursor y liberar la memoria ocupada por él con la instrucción CLOSE

nombre_cursor;

e) Atributos de los cursores:

a. nombreCursor%FOUND: devuelve TRUE si el último FETCH contiene fila.
b. nombreCursor %NOTFOUND: devuelve TRUE si el último FETCH no devuelve
fila.
c. nombreCursor %ISOPEN: devuelve TRUE si cursor está abierto.
d. nombreCursor %ROWCOUNT: contiene el número de filas que tiene el
cursor.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Para trabajar con los datos que tenemos en el cursor debemos usar bucles y
vamos a ver varias formas:

• Bucle LOOP con una sentencia EXIT <condición>

DECLARE
CURSOR cpaises IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
vco_pais VARCHAR2(3);
vdescripcion VARCHAR2(50);
vcontinente VARCHAR2(25);

BEGIN
OPEN cpaises;
LOOP
FETCH cpaises INTO vco_pais, vdescripcion, vcontinente;
EXIT WHEN cpaises%NOTFOUND;
dbms_output.put_line(vdescripcion);
END LOOP;

CLOSE cpaises;
END;
 Curso: 1º CFGS DAW
Módulo: Bases de datos

• Bucle WHILE LOOP

DECLARE

CURSOR cpaises IS
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
FROM PAISES;
vco_pais VARCHAR2(3);
vdescripcion VARCHAR2(50);
vcontinente VARCHAR2(25);

BEGIN

OPEN cpaises;
FETCH cpaises INTO co_pais,descripcion,continente;
WHILE cpaises%FOUND LOOP
dbms_output.put_line(vdescripcion);
FETCH cpaises INTO co_pais,descripcion,continente;
END LOOP;
CLOSE cpaises;

END;

9. SUBPOGRAMAS: PROCEDIMIENTOS Y
FUNCIONES

Es un bloque como los que hemos trabajado. La única diferencia es que el

conjunto de instrucciones que forman parte del bloque se encapsulan y se les asigna
un nombre para que podamos ejecutarlas en cualquier parte del programa.

Ventaja: en lugar de tener que escribir todo el código cada vez que queramos
ejecutarlo lo único que tenemos que hacer es llamar al subprograma a través de us
nombre.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Un subprograma tiene dos partes:

• Especificación: se especifica el nombre del subprograma y se definen los

parámetros de entrada y de salida.
• Cuerpo: bloque PL/SQL con el conjunto de instrucciones necesarias para
llevar a cabo la tarea de la función o del procedimiento.

Los subprogramas pueden recibir parámetros que son los valores que los hacen
dinámicos y válidos para cualquier valor de entrada y pueden devolver valores. Si
un subprograma devuelve un valor después de ejecutarse se llama FUNCIÓN y sino
se llama PROCEDIMIENTO.

9.1. PROCEDIMIENTOS
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Ejemplo: vamos a hacer un procedimiento que muestre por pantalla una cadena
de caracteres, pero un carácter por cada línea. El bloque que tengo que hacer para
probarlo sería este:

DECLARE

BEGIN

ImprimirCadenaVertical(“Hola”);

ImprimirCadenaVertical(“Estamos en clase de BD”);

ImprimirCadenaVertical(“Me voy a casa”);

END

Vamos a hacer el procedimiento ….

Para ver la diferencia entre la forma de definir los parámetros de un subprograma

vamos a crear un procedimiento que recibe dos valores de entrada (nombre y apellido)
y construye una dirección de correo almacenándolo en el tercer parámetro. El formato
de a dirección tiene que ser: [Link]@[Link]

A la hora de llamar a un procedimiento las variables que se indican en la llamada

se denominan ARGUMENTOS. Estos se tienen que corresponder con los parámetros
definidos en el procedimiento y hay dos formas de hacerlo:

 Notación Posicional: los argumentos que se usan en la llamada se asocian a los

parámetros según la posición que ocupan en la declaración
 Notación nominal: se especifica en la llamada a qué parámetro corresponde el
argumento utilizando como símbolo una flecha =>. Aquí da igual la posición porque
con la fecha ya indicas con que parámetro se corresponde. Este se usa menos.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

9.2. FUNCIONES
 Curso: 1º CFGS DAW
Módulo: Bases de datos

10. TRATAMIENTO DE EXCEPCIONES

EXCEPTION

WHEN NOMBRE_EXCEPTION1 THEN

[Declaración de Sentencia]

WHEN NOMBRE_EXCEPTION2 THEN

[Declaración de Sentencia]

WHEN NOMBRE_EXCEPTION_N THEN

[Declaración de Sentencia]

WHEN OTHERS THEN

[Declaración de Sentencia]

A continuación, se muestra una tabla donde se recogen las excepciones que se

tratan en Oracle para que las tengáis a mano y os hagáis una idea del tipo de errores
que se pueden producir y controlar.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Nombre de excepción Códig Explicación

o de
error

DUP_VAL_ON_INDEX ORA- Este error se debe a que se ha intentado ejecutar

00001 un INSERT o un UPDATE que intenta crear una fila con
un valor duplicado en un campo restringido pon un
UNIQUE INDEX.

TIMEOUT_ON_RESOURCE ORA- Ha acabado el tiempo de espera destinado a la

00051 consecución de un recurso.

TRANSACTION_BACKED_ ORA- La parte remota de la transacción ha hecho rollback..

OUT 00061

INVALID_CURSOR ORA- Está tratando de utilizar un CURSOR que ya no existe.

01001

NOT_LOGGED_ON ORA- Está intentando ejecutar una llamada a Oracle antes de

01012 validarse.

LOGIN_DENIED ORA- Está tratando de validarse contra Oracle usando una

01017 combinación errónea de usuario/clave.

NO_DATA_FOUND ORA- Está sucediendo una de las siguientes cosas:

01403
1. Está ejecutando una sentencia SELECT INTO y
no hay filas que devolver.
2. Está haciendo referencia a una fila de un tabla
que no está inicializada.
3. Esta intentado leer pasado el fin de fichero con
el paquete UTL_FILE.

TOO_MANY_ROWS ORA- Está tratando de ejecutar una consulta SELECT INTO

01422 que devuelve más de una fila.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Nombre de excepción Códig Explicación

o de
error

ZERO_DIVIDE ORA- Está tratando de dividir por cero.

01476

INVALID_NUMBER ORA- Está tratando de ejecutar una sentencia SQL que trata
01722 de convertir una cadena en número, pero no ha
funcionado.

STORAGE_ERROR ORA- Se ha producido un desbordamiento de la memoria y la

06500 memoria esta corrompida.

PROGRAM_ERROR ORA- Respuesta genérica a un error interno de Oracle.

06501

VALUE_ERROR ORA- Se ha producido un error de conversión, truncamiento,

06502 o restricción (constraint) de un valor numérico o de
carácter.

CURSOR_ALREADY_OPEN ORA- Está intentando abrir un cursor que ya está abierto.

06511

11. TRIGGER O DISPARADORES

Un disparador es un bloque que es llamado por el sistema y no por los programadores. ¿Cuál
es el evento que provoca su ejecución? un comando DML.

Cuando se produce una inserción, actualización o borrado sobre una tabla de la BD

automáticamente salta el disparador asociado ella y se ejecutan los comandos que los
programadores han incluido en el trigger.

La finalidad de los triggers es controlar restricciones sobre los datos que no se han tenido
cuenta al crear la BD como por ejemplo controlar que los valores que se introduzcan en el campo
edad de la tabla alumno estén comprendidos entre 18 y 35 , o restricciones que han surgido después
 Curso: 1º CFGS DAW
Módulo: Bases de datos

estando ya la BD en explotación. Hay requisitos que se detectan al hacer el análisis de requerimientos

y el diseño conceptual que no se pueden reflejar en cada una de las fases, pero si tenemos que
respetarlos y para eso tenemos los trigger. Por ejemplo, un jefe solo puede tener 5 empleados a su
cargo, o antes de eliminar datos de una tabla de matrículas quiero guardar los que borre en una tabla
temporal.

Los trigger se pueden programar para que ejecuten una serie de acciones para cada una de las
filas afectadas por el comando INSERT, UPDATE O DELETE o bien para el comando en sí afecte a las
filas que afecte.

La sintaxis general de un disparador es:

Vamos a ver dos ejemplos para que veáis la diferencia entre un trigger programado para cada
fila o programado para el comando DML en sí:

a) Este trigger se ejecuta por comando independientemente del número de filas que se vean
afectadas. No vamos a hacer nada sobre las filas.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

CREATE OR REPLACE TRIGGER INSERTARALUMNOS

AFTER INSERT ON ALUMNO
FOR EACH STATEMENT
BEGIN
DBMS_OUTPUT.PUT_LINE ('SE HA INSERTADO UN ALUMNO
NUEVO....');
END;

DESCRIBE ALUMNO;

INSERT INTO ALUMNO VALUES ('2222222','NUEVO ALUMNO',

'NUEVO APELLIDO', 'NUEVO APELLIDO2', 'N');

b) Este trigger se ha programado para que cuando se borren asignaturas de la BD se guarden

temporalmente en una tabla que va a contener los datos de las asignaturas borradas por si
queremos consultarlas por algo o nos equivocamos. Antes de nada debe existir la tabla
asignaturasBorradas con los mismos campos que la tabla asignatura o con los campos que
queramos guardar. La programación sería:

CREATE OR REPLACE TRIGGER HistorialAsignaturas

BEFORE DELETE ON Asignatura
FOR EACH ROW

BEGIN
INSERT INTO asignaturasBorradas (codigo, nombre, nh, bil,
codcf) VALUES (:old.cod_asig, :[Link],
:[Link] , [Link], [Link]);
END;

La ejecución del trigger afecta a cada una de las filas que se borre y lo normal es usar los valores
que se están modificando para hacer algo, tanto los nuevos valores como los antiguos valores y
¿cómo podemos acceder a esos valores? con dos registros virtuales llamados :old y :new que se
explican a continuación:

- :new  este registro tiene todos los campos que estén participando en el insert o update
que ha desencadenado la ejecución del trigger
- :old  registro que contiene los valores viejos de la tabla.
 Curso: 1º CFGS DAW
Módulo: Bases de datos

Estas dos variables hay que tenerlas presentes siempre que se programe un trigger de tipo
FOR EACH ROW ya que vamos a tener que manipular los datos nuevos que se estén insertando y los
viejos.

c) Por ejemplo: vamos a crear un trigger que cambie las horas que vamos a insertar sumándole
100h mas.

CREATE OR REPLACE TRIGGER CAMBIARHORAS

BEFORE INSERT ON ASIGNATURA
FOR EACH ROW
BEGIN
:[Link] := :[Link] + 100;
END;

d) Otro trigger que se dispara cuando hacemos un UPDATE, cuando modificamos las joras de
una asignatura y queremos controlar que si se actualiza tiene que ser mayor que 100 y si
no lo es, sumamos 100 a las horas que tenga actualmente la asignatura

CREATE OR REPLACE TRIGGER CONTROL_MODIFICACION_HORAS

BEFORE UPDATE ON ASIGNATURA
FOR EACH ROW
BEGIN
IF :[Link] < 100 THEN
:[Link] := :[Link] +100;
END IF;
END;

Aspectos y cosas que aprendemos con estos ejemplos:

 Un trigger siempre se va a ejecutar cuando se procese el comando DML que se ha asociado a
dicho trigger. En el caso del UPDATE, aunque nosotros queremos que se ejecute solo cuando se
modifique el campo numhoras, se va a ejecutar siempre, aunque no haga nada. Es decir, con las
dos sentencias siguientes se ejecuta el trigger (ver script para que vean el mensaje que se
muestra)
o UPDATE ASIGNATURA SET bilingue = 'S' WHERE COD_ASIG=1;
o UPDATE ASIGNATURA SET numhoras = 50 WHERE COD_ASIG=1;

Para evitar esto, podemos ser más finos e indicar que queremos que se ejecute el trigger cuando
se actualice el campo numhoras de la tabla asignatura de la siguiente forma:
 Curso: 1º CFGS DAW
Módulo: Bases de datos

CREATE OR REPLACE TRIGGER CONTROL_MODIFICACION_HORAS

BEFORE UPDATE OF numhoras ON ASIGNATURA
FOR EACH ROW
BEGIN
IF :[Link] < 100 THEN
:[Link] := :[Link] +100;
END IF;
dbms_output.put_line('he entrado al disparador del update');
END;

 Cuando usar AFTER o BEFORE. Si queremos hacer modificaciones en los nuevos valores que
estemos insertando, no podemos usar AFTER porque ya que se ha ejecutado el comando DML
que ha provocado la ejecución del disparador y ya no podemos acceder a los valores del registro
:new. Verlo con el ejemplo en el que incrementamos en 100 las horas .

 Vemos que estamos programando varios trigger sobre la misma tabla solo que se disparan
por comandos DML distintos y esto lo podemos unificar en un solo trigger utilizando el operador
OR al declararlo y usando unos predicados condicionales con los que se determinará las
sentencias que se van a ejecutar dependiendo del tipo de operación. Estos son INSERTING,
UPDATING, DELETING que serán TRUE si el comando que lo ha disparado es un INSERT, UPDATE
O DELETE respectivamente.

CREATE OR REPLACE TRIGGER CONTROL_NUMERO_ALUMNOS

BEFORE INSERT OR UPDATE OR DELETE ON ALUMNO
FOR EACH ROW
BEGIN

IF INSERTING THEN
dbms_output.put_line('Disparador de alumnos por INSERTAR');

ELSIF UPDATING('sgapellido') THEN

dbms_output.put_line('Disparador por actualizar el segundo apellido');

ELSIF UPDATING THEN

dbms_output.put_line('Disparador de alumnos por ACTUALIZAR');

ELSIF DELETING THEN

dbms_output.put_line('Disparador de alumnos por BORRAR');

END IF;
END;