PROCEDIMIENTOS ALMACENADOS DE BASE DE DATOS

Son un conjunto de comandos que pueden ser ejecutados directamente

en el servidor, es decir, será ejecutado por el servidor de Base de Datos y no

por el programa cliente que lo accede, permitiendo la ejecución de una acción

o conjunto de acciones específicas.

CARACTERISTICAS

Se almacenan en la propia Base de Datos y constituyen un objeto más

dentro de esta.

Tienden a mejorar el rendimiento de los sistemas producto a que reducen

en intercambio entre cliente y servidor.

Los procedimientos almacenados son reutilizables, de manera que los

usuarios mediante la aplicación cliente no necesitan relanzar los comandos

individuales, sino que pueden llamar el procedimiento para ejecutarlo en el

servidor tantas veces como sea necesario.

UTILIDADES

Los procedimientos almacenados son muy útiles sobre todo en

arquitecturas cliente/servidor donde hay un servidor muy potente el cual se

puede aprovechar para ejecutar procesos, consultas y actualizaciones

complejas en la base de datos

Posibles usos que pueden darse a estos objetos de la base de datos

Por ejemplo, si deseamos obtener un reporte complejo que incluya

instrucciones condicionales y cálculos complejos con datos obtenidos de

varias tablas, un procedimiento almacenado es nuestro mejor aliado. También

se pueden ejecutar complejos procesos que a veces tardan horas cuando son

ejecutados desde el cliente, ya que en tales casos la información debe pasar

del servidor al cliente y viceversa.

Casi siempre las computadoras servidores son poderosas máquinas con

mucha memoria, discos rápidos y uno o más procesadores también muy

rápidos. Por lo tanto, al ejecutar los procesos mediante procedimientos

almacenados estamos aprovechando toda esa capacidad de cómputo

disponible en el hardware del servidor.

Algunos casos en que pueden resultar particularmente útiles

Cuando múltiples aplicaciones cliente se escriben en distintos lenguajes

o funcionan en distintas plataformas, pero necesitan realizar la misma

operación en la base de datos. Cuando la seguridad es muy importante. Los

bancos, por ejemplo, usan procedimientos almacenados para todas las

operaciones comunes.

Esto proporciona un entorno seguro y consistente, y los procedimientos

pueden asegurar que cada operación se loguea apropiadamente. En tal

entorno, las aplicaciones y los usuarios no obtendrían ningún acceso directo a

las tablas de la base de datos, sólo pueden ejectuar algunos procedimientos

almacenados.

VENTAJAS

La ventaja de un procedimiento almacenado, en respuesta a una

petición de usuario, está directamente bajo el control del motor del gestor de

bases de datos, que corre generalmente en un servidor distinto del servidor

web, aumentando con ello la rapidez de procesamiento de las peticiones del

usuario. El servidor de la base de datos tiene acceso directo a los datos

necesarios para manipular y sólo necesita enviar el resultado final al usuario.

Los procedimientos almacenados pueden permitir que la lógica del negocio se

encuentre como un API en la base de datos, que pueden simplificar la gestión

de datos y reducir la necesidad de codificar la lógica en el resto de los

programas cliente. Esto puede reducir la probabilidad de que los datos se

corrompan por el uso de programas clientes defectuosos o erróneos. De este

modo, el motor de base de datos puede asegurar la integridad de los datos y

su consistencia con la ayuda de procedimientos almacenados.

Algunos afirman que las bases de datos deben ser utilizadas para el

almacenamiento de datos solamente, y que la lógica de negocio sólo debería

aplicarse en la capa de negocio de código, a través de aplicaciones cliente que

deban acceder a los datos. Sin embargo, el uso de procedimientos

almacenados no se opone a la utilización de una capa de negocio.

ELEMENTOS DE LOS PROCEDIMIENTOS ALMACENADOS

Los procedimientos almacenados están compuestos por algunos de estos

elementos:

Parámetros de entrada

Parámetros de salida

Declaración de variables

Cuerpo del procedimiento

Tanto los parámetros de entrada como los de salida son opcionales.

Podemos tener un procedimiento que no tenga parámetros de entrada, pero sí

de salida y a la inversa. Como todo buen programador sabe, los parámetros de

entrada sirven para enviarle datos al procedimiento que puede utilizar en sus

cálculos. También se pueden obtener datos de otras tablas dentro del

procedimiento mediante instrucciones SELECT de SQL.

Los parámetros de salida envían al programa cliente que ejecutó el

procedimiento el resultado del mismo. Lo interesante es que los parámetros de

salida pueden ser enviados en forma de tuplas, lo que nos permite acceder al

procedimiento almacenado mediante una instrucción SELECT de SQL y

devolviendo una especie de tabla "virtual". Un procedimiento almacenado

también se puede ejecutar mediante la instrucción EXECUTE PROCEDURE.

Esto lo veremos a detalle más adelante.

Ventajas de usar SP

Compilación: La primera vez que se invoca un SP, el motor lo compila y a

partir de ahí, se sigue usando la versión compilada del mismo, hasta que

se modifique. Esto significa que se tendrá un mejor rendimiento que las

consultas directas que usan cadenas con las instrucciones, que se

compilan cada vez que se invocan.

Automatización: si tenemos un conjunto de instrucciones SQL, las cuales

queremos ejecutar de manera ordenada, un SP es la mejor manera de hacerlo.

Ventajas de usar SP

Administración: cuando realizamos aplicaciones con un gran numero de

líneas de código, y queremos hacer cambios, solo implica modificar un SP

y no toda la aplicación, lo que significa solo cambiamos los SP en el

servidor y no tenemos que actualizar la aplicación en todos los equipos

cliente.

Seguridad: una parte importante es que a los usuarios de nuestra

aplicación, solo les proporcionamos los permisos para ejecutar los

procedimientos almacenados y no el acceso a todos los objetos de la base.

Programabilidad: Los SP admiten el uso de variables y estructuras de

control como IF, Bucles, Case, etc. Además del manejo de transacción y

permite controlar excepciones.

SINTAXIS

Para crear un procedimiento almacenado debemos emplear la sentencia 

CREATE OR REPLACE FUNCTION.

CREATE OR REPLACE FUNCTION <nombre_procedure> (param1

<tipo>, ...)

-- Sentencias del procedure

Ejemplo

El siguiente ejemplo muestra un procedimiento almacenado, denominado

spu_addCliente que inserta un registro en la tabla "CLIENTES"

CREATE OR REPLACE FUNCTION spu_addCliente(nombre varchar(100),

apellido1 varchar(100),apellido2 varchar(100),

nifCif varchar(20),fxNaciento timestamp)

RETURNS VOID AS $$

BEGIN

INSERT INTO CLIENTES

(nombre, apellido1, apellido2, nifcif, fxNaciento) VALUES

(nombre, apellido1, apellido2, nifCif, fxNaciento);

END;

$$ Language 'plpgsql';

Procedimientos almacenados (store procedure)

El siguiente ejemplo muestra la ejecución del procedimiento almacenado anterior.

SELECT spu_addCliente('Pedro', 'Herrarte', 'Sanchez',

'00000002323', ‘2011-02-02’)

Ejemplo

El siguiente ejemplo muestra un procedimiento almacenado que devuelve valores.

CREATE or replace FUNCTION spu_EstaEnNumerosRojos(numCuentavarchar(20))RETURNS numeric AS $SALDO$BEGINIF (SELECT state FROM expedientesWHERE code = numCuenta) = '4' thenRETURN 1;ELSERETURN 0;END IF;END;$SALDO$Language 'plpgsql';

Ejemplo

El siguiente ejemplo muestra como ejecutar el procedure y obtener el valor

devuelto.

select spu_EstaEnNumerosRojos('1');

TRIGGER

Un trigger en una Base de Datos, es un procedimiento que se ejecuta cuando

se cumple una condición establecida al realizar una operación. Los triggers

pueden ser de inserción (INSERT), actualización (UPDATE) o borrado

(DELETE).

Usos: Son usados para mejorar la administración de la Base de datos, sin necesidad

de contar con que el usuario ejecute la sentencia de SQL. Además, previene errores

de datos, sincroniza tablas, modifica valores de una vista, etc.

Ejemplo

Para instalar un trigger hay que hacer dos cosas:

Crear una función para el trigger

Instalar el trigger en la tabla

CREATE OR REPLACE FUNCTION cambiarstatus() RETURNS TRIGGER AS $$ BEGIN -- ¿Ha cambiado el estado? IF NEW.state != OLD.state THENINSERT INTO expStatusHistory (code, state) VALUES (OLD.code, OLD.state); END IF; RETURN NEW; END; $$ Language 'plpgsql';

CREATE TRIGGER StatusChangeDateTrigger BEFORE UPDATE ON expedientesFOR EACH ROWEXECUTE PROCEDURE cambiarstatus()