Procedimientos almacenados (PHP+MySQL)

De wikipedia:

Un procedimiento almacenado (stored procedure) es un programa (o procedimiento) el cual es almacenado físicamente en una base de datos. Generalmente son escritos en un lenguaje de bases de datos propietario como PL/SQL para Oracle database o PL/PgSQL para PostgreSQL. La ventaja de un procedimiento almacenado es que al ser ejecutado, en respuesta a una petición de usuario, es ejecutado directamente en el motor de bases de datos, el cual usualmente corre en un servidor separado. Como tal, posee acceso directo a los datos que necesita manipular y solo necesita enviar sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes cantidades de datos salientes y entrantes.

Usos típicos para procedimientos almacenados incluyen la validación de datos siendo integrados a la estructura de base de datos (los procedimientos almacenados utilizados para este propósito a menudo son llamados disparadores; triggers en inglés), o encapsular un proceso grande y complejo. El último ejemplo generalmente ejecutará más rápido como un procedimiento almacenado que de haber sido implementado como, por ejemplo, un programa corriendo en el sistema cliente y comunicándose con la base de datos mediante el envío de consultas SQL y recibiendo sus resultados.

Los procedimientos pueden ser ventajosos: Cuando una base de datos es manipulada desde muchos programas externos. Al incluir la lógica de la aplicación en la base de datos utilizando procedimientos almacenados, la necesidad de embeber la misma lógica en todos los programas que acceden a los datos es reducida. Esto puede simplificar la creación y, particularmente, el mantenimiento de los programas involucrados.

Podemos ver un claro ejemplo de estos procedimientos cuando requerimos realizar una misma operación en un servidor dentro de algunas o todas las bases de datos y a la vez dentro de todas o algunas de las tablas de las bases de datos del mismo. Para ello podemos utilizar a los Procedimientos almacenados auto creables que es una forma de generar ciclos redundantes a través de los procedimientos almacenados.

——————————————————————————————————————————–

Apunte:

Ahora para MySQL, podemos en un Stored Procedure hacer uso de transacciones asegurando asi que realmente se llevará a cabo de forma segura el procedimiento y para esto hacemos uso de @@error_count, el cual siendo 0 significa que no han habido errores (usando un if bajo esta condición podriamos decidir cuando proceder al COMMIT).

En el manual de referencia viene como crear stored procedures, pero simplificando un poco pongon un ejemplo. Lo empezamos con CREATE PROCEDURE sp_nombreDelProcedimiento … luego abrimos un paréntesis para indicar las variables que pueden ser de IN, OUT o INOUT…

Una vez definido esto, definimos entre BEGIN y END las consultas que compondrán al stored procedure. Podemos declarar variables dentro de este stored procedure haciendo uso de DECLARE nombreVariable TipoVariable.

Ejemplo:

CREATE PROCEDURE sp_altaUsuario(IN nick VARCHAR(10) ,
IN correo VARCHAR(35),
IN contrasenia VARCHAR(32), IN estadox TINYINT(3),
IN permisox TINYINT(3))

BEGIN
DECLARE a INTEGER;
INSERT INTO usuario(nickUsuario, correoUsuario,
contraseniaUsuario, idEstado,
idPermiso)
VALUES(nick, correo, contrasenia, estadox,
permisox);
SET a=(SELECT @@error_count);
IF a = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END;

Ahora, si queremos llamar a un stored procedure, la consulta sería haciendo uso de CALL sp_nombreDelStoredProcedure(valoresDeEntrada). y en PHP podemos hacerlo de la siguiente manera:

$sql= “CALL sp_ejemplo(”.$parametro1.”);”;

$datos= mysqli_query($conexion, $sql);

Sobre @@error_count -> http://dev.mysql.com/doc/refman/5.0/es/show-errors.html