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
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.
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.
Hola, tengo un pequeño problema que no se como iniciar:
Tengo una tabla que contiene información, deseo agregar mas informacion de la existente a una determinada columna. Como puedo agregar mas información sin afectar la existente.