Triggers en PL/SQL

Triggers en PL/SQL

En muchos casos es necesario la utilización de triggers, uno de los casos mas comunes
es la validación de los datos antes de ser insertados en la base de datos, la utilidad
de esto es dejar dicha carga de validación a los servidores donde se encuentra la base
de datos, y liberar la lógica del sistema de estas tareas.

Declaración de un trigger

CREATE [OR REPLACE] TRIGGER <nombre_trigger>
{BEFORE|AFTER} 
               {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]
           [OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]}
ON <nombre_tabla>
[FOR EACH ROW [WHEN (<condicion>)]]
DECLARE
  -- variables locales
BEGIN
  -- Sentencias
[EXCEPTION]
  -- Sentencias control de excepcion  
END <nombre_trigger>;

Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de la operación.   El modificador BEFORE AFTER indica que el trigger se ejecutará antes o después de ejecutarse la sentencia SQL definida por DELETE INSERT  UPDATE. Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista.

El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.


INSERT, DELETE, UPDATE Define qué tipo de orden DML provoca la activación del disparador.
BEFORE , AFTER Define si el disparador se activa antes o después de que se ejecute la orden.
FOR EACH ROW Los disparadores con nivel de fila se activan una vez por cada fila afectada por la orden que provocó el disparo. Los disparadores con nivel de orden se activan sólo una vez, antes o después de la orden. Los disparadores con nivel de fila se identifican por la cláusula FOR EACH ROW en la definición del disparador.

Fuente: http://www.devjoker.com/contenidos/Tutorial-PLSQL/54/Triggers-en-PLSQL.aspx

Vistas PL/SQL

Vistas en PL/SQL

A continuación, veremos la creación de una vista en PL/SQL, vamos a suponer
que tenemos la tabla "CLIENTE" con los atributos Nombre, Identificacion,
FechaNac, Historial y Moroso,

CREATE VIEW CLIENTES_MOROSOS
AS
(SELECT Nombre, Identificacion
 FROM CLIENTE
 WHERE Moroso = 1;
);

Con este código crearemos una vista con los clientes morosos (Moroso = 1) en donde
se muestra su nombre y numero de identificación,

Ahora, si queremos eliminar la vista creada utilizaremos el siguiente código:

DROP VIEW CLIENTES_MOROSOS;

Excepciones PL/SQL

Excepciones en PL/SQL

Las excepciones en PL/SQL son muy útiles a la hora de canalizar errores
que pueden ocurrir en un bloque de código, a continuación se mostrara un
ejemplo:

DECLARE
 -- Declaraciones
BEGIN
  -- Ejecución
EXCEPTION
WHEN NO_DATA_FOUND THEN
  -- Se ejecuta cuando ocurre una excepción de tipo NO_DATA_FOUND
WHEN ZERO_DIVIDE THEN
  -- Se ejecuta cuando ocurre una excepción de tipo ZERO_DIVIDE

WHEN OTHERS THEN
  -- Se ejecuta cuando ocurre una excepción de un tipo no tratado
  -- en los bloques anteriores

END;  

En el ejemplo anterior podemos ver tres como utilizar tres tipos de excepciones, 
en un procedimiento o función, a parte de las mostradas anteriormente existen 
las siguientes excepciones:

ACCESS_INTO_NULL El programa intentó asignar valores a los atributos de un objeto no inicializado -6530
COLLECTION_IS_NULL El programa intentó asignar valores a una tabla anidada aún no inicializada -6531
CURSOR_ALREADY_OPEN El programa intentó abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar con la sentencia OPEN -6511
DUP_VAL_ON_INDEX El programa intentó almacenar valores duplicados en una columna que se mantiene con restricción de integridad de un índice único (unique index) -1
INVALID_CURSOR El programa intentó efectuar una operación no válida sobre un cursor -1001
INVALID_NUMBER En una sentencia SQL, la conversión de una cadena de caracteres hacia un número falla cuando esa cadena no representa un número válido -1722
LOGIN_DENIED El programa intentó conectarse a Oracle con un nombre de usuario o password inválido -1017
NO_DATA_FOUND Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento no inicializado en una tabla indexada 100
NOT_LOGGED_ON El programa efectuó una llamada a Oracle sin estar conectado -1012
PROGRAM_ERROR PL/SQL tiene un problema interno -6501
ROWTYPE_MISMATCH Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado -6504
SELF_IS_NULL El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo -30625
STORAGE_ERROR La memoria se terminó o está corrupta -6500
SUBSCRIPT_BEYOND_COUNT El programa está tratando de referenciar un elemento de un arreglo indexado que se encuentra en una posición más grande que el número real de elementos de la colección -6533
SUBSCRIPT_OUTSIDE_LIMIT El programa está referenciando un elemento de un arreglo utilizando un número fuera del rango permitido (por ejemplo, el elemento “-1”) -6532
SYS_INVALID_ROWID La conversión de una cadena de caracteres hacia un tipo rowid falló porque la cadena no representa un número -1410
TIMEOUT_ON_RESOURCE Se excedió el tiempo máximo de espera por un recurso en Oracle -51
TOO_MANY_ROWS Una sentencia SELECT INTO devuelve más de una fila -1422
VALUE_ERROR Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando se intenta calzar un valor muy grande dentro de una variable más pequeña -6502
ZERO_DIVIDE El programa intentó efectuar una división por cero -1476

Ahora veremos un ejemplo con la excepción NO_DATA_FOUND, tomaremos como ejemplo para realizar un SELECT la tabla "PRODUCTO" cuyos atributos son Nombre y Precio

PROCEDURE LISTAR_PRODUCTOS AS
DECLARE
 MI_PRODUCTO PRODUCTO%%ROWTYPE; 
BEGIN
 SELECT *
 INTO MI_PRODUCTO
 FROM PRODUCTO
 WHERE Precio = 200;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
  BEGIN
   --No Existe el producto con precio 200
  END;
END LISTAR_PRODUCTOS;
/

Cursores en PL/SQL

Cursores en ORACLE - PL/SQL

Cuando nos vemos en la necesidad de recorrer la información de un select una de las
mejores opciones es la utilización de un cursor, en el próximo ejemplo aprenderemos
de manera sencilla su utilización.

Vamos a tomar como ejemplo inicial la tabla "CLIENTE" cuyos atributos
son Nombre e NumIdentificacion,

A continuación se explicara el ejemplo mediante un procedimiento:

PROCEDURE LISTAR_CLIENTES AS
DECLARE
CURSOR CLIENTES IS
SELECT *
FROM CLIENTES;

NOMBRE_CLIENTE VARCHAR(100);
ID_CLIENTE   VARCHAR(15);

BEGIN
FOR MI_CLIENTE IN CLIENTES LOOP
NOMBRE_CLIENTE := MI_CLIENTE.Nombre;
ID_CLIENTE   := MI_CLIENTE.NumIdentificacion;
END LOOP;
END BUSCAR_CLIENTE;
/

En el código anterior podemos ver la declaración del cursor: CURSOR CLIENTES IS
y luego del BEGIN construimos un FOR para recorrer el cursor que contiene a todos
nuestros clientes, vemos que el for es el siguiente: FOR MI_CLIENTE IN CLIENTES LOOP
y nos podemos referir a los atributos dentro del cursor a través de MI_CLIENTE.<atributo>
en nuestro ejemplo tomamos los atributos Nombre y NumIdentificacion..

La utilización de los cursores puede variar según la necesidad y el estilo de programación,
en mi caso, el ejemplo mas practico es el presentado anteriormente... Para mas información buscar la documentación de ORACLE...

Crear un paquete (PACKAGE) en ORACLE PL/SQL

Crear un paquete(PACKAGE) en ORACLE

A veces es necesario distribuir nuestros procesos en paquetes
para dar una mejor estructura al proyecto en el que estamos trabajando, para
empezar un paquete tiene dos niveles (Declaración y Cuerpo), en la declaración
solo declaramos los procedimientos con sus parámetros, y en el cuerpo desarrollamos
el código de nuestros procedimientos. (Para mas información consultar documentación
de ORACLE)

Ahora vamos con nuestro ejemplo:

CREATE OR REPLACE PACKAGE "OPERACIONES_BASICAS" AS 


PROCEDURE SUMAR(A NUMBER,B NUMBER);


PROCEDURE RESTAR(A NUMBER,B NUMBER);

END OPERACIONES_BASICAS;
/

Con el código anterior construimos el nivel declarativo de nuestro paquete, solo falta crear nuestro
nivel cuerpo para completar nuestro paquete

 CREATE OR REPLACE PACKAGE BODY "OPERACIONES_BASICAS" AS 
PROCEDURE SUMAR(A NUMBER,B NUMBER) AS
RESULTADO NUMBER(10);
BEGIN
RESULTADO := A + B;
END SUMAR;
PROCEDURE RESTAR(A NUMBER,B NUMBER) AS
RESULTADO NUMBER(10);
BEGIN
RESULTADO := A - B;
END RESTAR;
END OPERACIONES_BASICAS;
/

Con el código anterior tenemos nuestro paquete creado, ahora veremos como llamar a los procedimientos
dentro de dicho paquete:

>>OPERACIONES_BASICAS.SUMAR(11,3);
>>OPERACIONES_BASICAS.RESTAR(11,3);

Creación de TYPE en ORACLE

Creación de TYPES en ORACLE

En muchas ocasiones es necesario la creación de un TYPE para luego utilizarlo
como un atributo en alguna tabla, para la creación de un TYPE se debe utilizar
el siguiente código:

>>create or replace type TELEFONO as object (
>>    CodArea             NUMBER(10),
>>    Numero              NUMBER(10)
>>);


>>create or replace type TELEFONOS as VARRAY(16) of TELEFONO;

En el código anterior creamos el TYPE TELEFONO, cuyos atributos son CodArea y
Numero, y creamos un arrecho llamado TELEFONOS al cual le asignamos el TYPE TELEFONO
ahora veremos como agregarlo como atributo a una tabla:

>>create table BAR(
>>   IdBar                   NUMBER(4)            NOT NULL,
>>   IdLugar                 NUMBER(4)            NOT NULL,
>>   Nombre                  VARCHAR2(50)         NOT NULL,
>>   Tlf               TELEFONOS            NOT NULL,
>>   Direccion               VARCHAR2(100)        NOT NULL,
>>   constraint PK_BAR primary key (IdBar)
>>);

En el código anterior creamos la tabla "BAR" cuyo atributo Telf. corresponde a un
arreglo del tipo TELEFONO.

Muchos se preguntaran, como insertar una nueva fila en la tabla BAR,

>>INSERT INTO BAR VALUES (IDBAR.NEXTVAL,2,'Bar do Chico',TELEFONOS(TELEFONO(092,3232161)),'Av. Leonardo Malcher, Rua Luiz Antony');

Ahora para eliminar los types creados podemos utilizar:

>>drop type TELEFONOS;
>>drop type TELEFONO;

Creación de Tablas en ORACLE

Creación de tablas en ORACLE

Para crear una nueva tabla en ORACLE, el código a utilizar es el siguiente:

>>CREATE TABLE LUGAR  (
>>   IdLugar            NUMBER(4) not null,
>>   Capital VARCHAR2(35) null,
>>   Moneda VARCHAR2(35) null,
>>   constraint PK_LUGAR primary key (IdLugar)
>>);

Con el código anterior creamos la tabla "LUGAR", cuyos atributos son
IdLugar, Capital y Moneda.  Para indicar el Primary Key o la clave
primaria de la tabla utilizamos el código: constraint PK_LUGAR primary key (IdLugar)

Ahora, en muchas ocasiones necesitamos que la clave primaria se incremente automáticamente,
para ello, debemos crear una secuencia (SEQUENCE) con el siguiente código:

>>CREATE SEQUENCE IdLugar INCREMENT BY 1 START WITH 1;

Con el código anterior indicamos que la clave primaria IdLugar se incremente automáticamente..

Si deseamos borrar la tabla creada utilizamos el siguiente código:

>>DROP TABLE LUGAR cascade constraint;

Ahora solo nos falta eliminar la secuencia creada:

>>DROP SEQUENCE IdLugar;


He tenido muchas dudas a la hora de insertar una fila en una tabla cuando su clave primaria
es declarada para incrementarse automáticamente, por ello, muestro un ejemplo de como insertar
en la tabla creada en el ejemplo anterior:

>>INSERT INTO LUGAR (IdLugar, Capital, Moneda) VALUES (IDLUGAR.NEXTVAL, 'España', Euro');

Con el código anterior indicamos que se inserte la clave primaria con el valor siguiente, por ejemplo
si la clave primaria de la ultima fila insertada es 5, la nueva fila tendrá el valor de 6