Procedimiento Almacenado con MySQL y PHP


El tema que hoy nos trae a este artículo ha sido tratado innumerables veces en la red y aunque no es “programación avanzada” si requiere quizás de cierto nivel de know-how por parte de los desarrolladores o administradores de base de datos. Aunque no cubriremos totalmente todos los aspectos de esta temática si los guiaremos en los aspectos básicos de como se configura en MySQL. Pueden documentarse mejor en los siguientes vínculos que les ponemos a continuación:

Primero, un Procedimiento Almacenado (stored procedure) es un procedimiento como bien lo dice su traducción al español el cual reside físicamente en la base de datos. Su implementación varía de gestor en gestor (MySQL-SQL Server-PostgreSQL) y su principal ventaja es el ahorro que supone en ancho de banda (Sistemas Web a través de la Red), en transacciones cliente-servidor y en performance pues usualmente los servidores de base de datos se encuentran independientes al servidor web que recibe las peticiones de los usuarios o clientes y un mejor control de acceso a datos.

Es ejecutado en respuesta a una primera petición del cliente que desencadena una secuencia de transacciones o consultas en la base de datos. Se puede decir que es un puente entre el usuario y las tablas de la base de datos.

A continuación ejemplaricemos lo antes expuesto creando un procedimiento almacenado. Recomendado es crear el procedimiento almacenado directamente en el servidor de base de datos, aunque también existe la posibilidad de su creación mediante PHP. Por supuesto podemos valernos de utilidades como MySQL Workbench o PHPMyAdmin entre otras.

Supongamos la siguiente tabla:

  CREATE TABLE `salario` (
    `empleadoid` int(11) NOT NULL,
    `salario` int(11) DEFAULT NULL,
    PRIMARY KEY (`empleadoid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Una vez creada la tabla garantizaremos acceso y permisos de EXECUTE al usuario que utilizará nuestro sistema para ejecutar las transacciones en la base de datos.

  // Usuario website y sus credenciales en este caso desde el servidor local.
  CREATE USER 'website'@'localhost' IDENTIFIED BY 'password123'; 
  // A el usuario creado se le establece permiso de EXECUTE en el esquema sistema_contabilidad que contiene la tabla salario.
  GRANT EXECUTE ON sistema_contabilidad.*  to website@`%`; 

Y ahora creamos el procedimiento de almacenado.

  DELIMITER $$

  CREATE PROCEDURE `avg_salario`(out avg_salario decimal)
  BEGIN
      select avg(salario) into avg_salario from salario;

  END

El procedimiento básicamente calcula y devuelve el promedio total de los registros de la tabla salario con números decimales.

Ejecutando un procedimiento de almacenado desde PHP

  $dbms = 'mysql';
  $host = '192.168.1.1'; 
  $db = 'sistema_contabilidad';
  $user = 'website';
  $pass = 'password123';
  $dsn = "$dbms:host=$host;dbname=$db";

  $cn = new PDO( $dsn, $user, $pass );

  $q = $cn->exec( 'call avg_salario(@out)' );
  $res = $cn->query( 'select @out' )->fetchAll();
  print_r( $res );

En el código anterior nos conectamos a la base de datos utilizando PDO (antes descrito en uno de nuestros artículos anteriores) y ejecutamos la sentencia call avg_salario(@out) seguida por select @out las cuales en ese orden ejecutan el procedimiento de almacenado y su valor de retorno es mostrado por print_r($res). No es estrictamente obligatorio utilizar PDO, también podemos usar las funciones mysqli o algún ORM (Doctrine mi favorito) no importa.

La utilidad de los PA supera los límites de nuestra imaginación. Nos permiten aplicar controles de acceso a ciertos datos sensibles o no de nuestras bases de datos. Lo principal es definir que datos queremos proteger y que funcionalidades nos es conveniente ejecutar directamente en nuestros servidores de base de datos.

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.

Como siempre los alentamos a experimentar y probar que es la única manera de aprender.

Latest posts by Leroy Ley (see all)