Procedimientos Almacenados y Disparadores en la base de datos

¿Qué son?

Los procedimientos almacenados y los disparadores o triggers son pequeños programas desarrollados en código SQL. En este tutorial añadiremos funciones y trigger o disparadores con ejemplos prácticos.

Unas herramientas que brinda el motor de base de datos MySQL son los procedimientos almacenados, funciones y trigger, que se utilizan para realizar transacciones u operaciones como insertar o modificar registros.
 
Los procedimientos almacenados son pequeños programas desarrollados en código SQL. Un procedimiento almacenado es un conjunto de comandos SQL que se almacenan junto con la base de datos.

La ventaja de un procedimiento almacenado es que podemos crearlo en cualquier editor de texto e incluso en el servidor, es ejecutado por el motor de bases de datos y no es accesible a los usuarios sino solo al administrador.

 

Un procedimiento almacenado envía sus resultados a una aplicación para que esta los muestre en pantalla evitando sobrecargar el servidor, en el tutorial:

Creación, Consultas e inserciones de datos

Un procedimiento almacenado (Store Procedure), es un pequeño algoritmo en lenguaje SQL que se almacena junto a la base de datos y permite realizar tareas sobre estos datos.
Las Ventajas de los procedimientos almacenados son:
- Se pueden acceder desde distintos programas externos, si la necesidad de hacer publica la estructura de la base de datos.
- Se pueden reutilizar por lo tanto ganaremos tiempo al estar ya programado y testeado.

Utilizar procedimientos mysql almacenados, con phpmyadmin

Iniciaremos el trabajo de crear y consultar procedimientos almacenados con la herramienta phpmyadmin, pero puede utilizarse cualquiera que soporte consultas SQL a partir de MYSQL 5.0
En este caso tomaremos la base de datos de una agencia de autos o vehículos.

1) Ingresamos a phpmyadmin y de allí a la base de datos

Existen 2 tipo motores que manejan datos en Mysql

• MyISAM: motor por defecto, muy rápido para consultas, no provee integridad de datos, ni protección referencial. Ideal sistemas con muchas consultas

• InnoDB: provee protección referencial e integridad de datos además de bloqueo de registros, ideal si se va a insertar, editar o eliminar mucha información constantemente. Generalmente para procedimientos almacenados es mejor utilizar InnoDB.

En la pestaña SQL creamos nuestro primer procedimiento almacenado para consultar los tipos de vehículos. Escribiros en el campo de texto de sql.

CREATE PROCEDURE consultar_tipo_vehiculo() ---> nombre del procedimiento
SELECT * FROM tipo_vehiculo ---> SQL que debe resolver

Si ejecutamos la consulta SQL recibiremos un mensaje de éxito al crearse el procedimiento.

Para ver los procedimientos creados desde la pestaña SQL consultamos la orden SHOW PROCEDURE STATUS, que no mostrara todos los procedimientos almacenados.

Ahora mostraremos como ejecutar el procedimiento almacenado con el comando CALL nombre_procedimiento (cada lenguaje de programación tiene su propia librería para acceder a un procedimiento almacenado pero son todos similares).

Aquí podemos ver el resultado de ejecutar el procedimiento almacenado CALL pa_tipo_vehiculo, devolvió el resultado y nadie ve que comandos se han ejecutado.

En el siguiente ejemplo listaremos vehículos pero por marca, el procedimiento seria:
CREATE PROCEDURE pa_vehiculos_por_marca(marca varchar(50))
SELECT * FROM
vehiculos, marcas
WHERE vehiculos.marca = marcas.id
AND marcas.marca=marca

Al nombre del procedimiento le añadimos una variable para poder buscar ejemplo vehículos marca Honda
Para ejecutar el ejemplo llamamos al procedimiento almacenado en una pestaña de SQL

CALL pa_vehiculos_por_marca(“Honda”)
CALL pa_vehiculos_por_marca(“Ford”)

También podes utilizar los procedimientos almacenados para tareas de inserción ejemplo un procedimiento para grabar datos de un cliente

CREATE PROCEDURE pa_cliente_insertar(

vnombre VARCHAR(64),

vapellidos VARCHAR(64)

)

INSERT INTO cliente (nombre, apellidos) VALUES(vnombre, vapellidos);

Para utilizarlo lo llamamos de la siguiente forma

CALL pa_cliente_insertar('José','Gonzales');

Otro podría ser Consultar cantidad de provincias

CREATE PROCEDURE `pa_provincias_cantidad`()

SELECT COUNT(*) as provincias FROM provincias

Para eliminar cualquier procedimiento se utiliza DROP PROCEDURE nombre_procedimiento

Llamadas a procedimientos desde distintos lenguajes.

En PHP, suponiendo que los datos vienen de un formulario

$mysqli = new mysqli("localhost", "root", "root");

$mysqli->select_db("agencia_autos");

$mysqli->query("CALL pa_cliente_insertar('$nombre', '$apellido')");

Ahora ejecutamos el procedimeinto almacenado en Java (se ha recortado el código)

conn = ConexionMySQL.conectar("127,0,0,1", "root", "*******", "root");

CallableStatement Procedimiento = conn.prepareCall("{ CALL pa_cliente_insertar('$nombre', '$apellido')) }");

Procedimiento.setString("vnombre", $nombre);

Procedimiento.setString("vapellido", $apellido);

Procedimiento.execute();

connM.commit();

De esta forma se demuestra que se ha usado el mismo procedimientos en entornos y lenguajes diferentes, de forma transparente para el usuario. El tema procedimiento es extenso pero cualquiera con conocimientos de SQL podrá investigar y conseguir grandes logros con la utilización de procedimientos almacenados, pudiendo así optimizar sus proyectos en tiempo y seguridad de datos.