CREATE PROCEDURE en MySQL: ¿Cómo crear procedimientos almacenados en MySQL?

¿Qué son los procedimientos almacenados en MySQL?

Bueno lo primero es comprender ¿Qué son los Stored Procedure (SP) o procedimientos almacenados en español? esto es fácil de responder y lo haré de la siguiente manera.

Los procedimientos almacenados (SP) son programas que se almacenan de forma física en una base de datos, que en nuestro caso es MySQL.

Cada motor de base de datos gestiona e implementa los SP según su sintaxis y paradigma, no obstante, en es muy similar ya que todas prácticamente utilizan el lenguaje SQL. Desde la versión 5.0 MySQL nos permite crear este tipo de archivos lo cual ha sido genial y le dio un gran impulso a este motor de base de datos.

Ventajas de los procedimientos almacenados

• La ventaja de utilizar los SP se ve reflejada cuando una base de datos recibe muchas conexiones desde programas o aplicaciones externas, ya que la lógica principal esta en el store procedure y las aplicaciones externas solo deben invocar dicho archivo y recibir los datos si es que este regresa algún parámetro.
• El uso de los SP también se lleva acabo por temas de seguridad ya que su uso crea un entorno seguro y consistente indicando que cada login se ha llevado acabo de manera correcta, dentro de este tema, los usuarios y aplicaciones no tendrían acceso directo a las tablas de la base de datos, si no que en vez de esto solo requieren ejecutar SPs.

Desventaja de los SP

La única desventaja que veo y algunos dirán realmente no es desventaja, pero bueno, lo dejo a tu criterio. Al usar SPs aumenta el estrés en el servidor de base de datos ya que todo el trabajo y ejecución se realiza en el y no del lado del cliente o aplicación que ha invocado el script.

¿Cómo hacer un procedimiento almacenado en MySQL?

En MySQL existen dos instrucciones en SQL que nos permiten crear rutinas que se almacenan en la base de datos:

• CREATE PROCEDURE
• CREATE FUNCTION

Y para invocar tus funciones o procedimientos puedes usar

• CALL

Los procedimientos almacenados y las funciones son muy similares, pero no son iguales y podemos diferenciarlos de la siguiente manera:

• Un procedimiento almacenado es una rutina que recibe parámetros y puede regresar, un resultado, varios resultados o ninguno.
• Las funciones sería mas para agregar una funcionalidad extra que no venga incluida en la base de datos, como el calculo de números, alguna formula personalizada, ya que solo regresan un valor.

Permisos de usuario para crear un procedimiento almacenado

Desde la versión 5.0 de MySQL se integró la tabla  proc  en la base de datos de nombre MySQL la cual es necesaria para el manejo de store procedure, te dejo un ejemplo de como verías esto en phpMyAdmin

Además, a partir de la versión 5.0.3 se requieren los siguientes permisos de usuario para el uso de SP:

• CREATE ROUTINE, permite la creación de procedimientos almacenados.
• ALTER ROUTINE, permite editar o borrar procedimientos almacenados, no obstante, si el usuario posee el permiso de crear, en automático se le asigna este permiso.
• EXECUTE, permite la ejecución de procedimientos almacenados y de igual forma se le da por de faul a los usuarios que tengan el permiso de creación

Bien, en este punto ya tenemos los elementos principales que debes de configurar primero para poder crear editar o eliminar porcimex almacenados a continuación veremos un ejemplo y la sintaxis qué debe tener o más bien la sintaxis con la que tú puedes crear hacer uso de los store procedure.

Sintaxis de procedimientos almacenados

La sintaxis general para la creación de un stored procedure y una function es la siguiente:

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

Parece bastante complejo ¿Cierto?, pero no lo es tanto, en el pedazo de código anterior esta incluida la creación de una función y un SP, por lo tanto hay muchas líneas que eliminamos si creamos uno o el otro.

Uso de DELIMITER en procedimientos almacenados MySQL

Antes de pasar a los ejemplos, me gustaría que conocieras a DELIMITER, esta instrucción es muy importante en la creación de procedimientos almacenados ya que, cuando creamos un stored procedure seguramente tendrá muchas sentencias las cuales deberás separar mediante ";" y lo que buscaos con un SP es compilarlo todo como una sola declaración compuesta, por lo tanto, debemos cambiar temporalmente el delimitador ";" por otro carácter y así pode compilarlo, esta es la razón por la que te encontrarás SP con la siguiente forma:

DELIMITER $$ -- CAMBIANDO EL DELIMITADOR POR "$$"
DROP PROCEDURE IF EXISTS `SP_NOMBRE` $$
CREATE PROCEDURE `SP_NOMBRE`()
BEGIN
    -- MÚLTIPLES SENTENCIAS DEL PROCEDIMIENTO ALMACENADO SEPARADAS POR ";"
END$$
DELIMITER ; -- EL DELIMITADOR VUELVE A SER ";"

En el código anterior vemos que:

• El primer paso se cambiar el delimitador ";" por "$$"
• Ahora, en el cuerpo del SP debes utilizar ";" para finalizar cada sentencia o query
• Al finalizar el SP con la palabra END agregas "$$" indicando el cierre
• Por último cambia el delimitador "$$" por el predeterminado

Yo tarde un poco en comprender por que habían stored procedure con esta sintaxis, espero que esto te ayude ya que te vas ahorrar mucho tiempo en estar investigando.

Ejemplo de un procedimiento almacenado

Primero haremos un ejemplo creando un SP para que te quede mas claro el tema. Vamos a utilizar la tabla resumen_productos de la base de datos db_php_mysql que es con la que hemos estado trabajando en este curso de PHP con MySQL desde cero, así que si no has visto todos los vídeos te recomiendo que los veas para que entiendas mejor.

CREATE PROCEDURE

Bien, en el post donde te enseño a crear un dashboard con gráficas y todo en PHP y MySQL, vimos un par de select que usamos para extraer los datos, ahora utilicemos algunas de esas consultas para crear nuestro ejemplo, así que utilicemos el siguiente query.

-- Nos permite obtener el total de productos vendidos
SELECT SUM(cantidad_vendidos) as vendidos FROM resumen_productos

-- Nos regresa el total de productos en almacen
SELECT SUM(en_almacen) as enAlmacen FROM resumen_productos

-- Nos regresa los ingresos totales el 100000 es un valor de corrección
-- ya que son datos inventados
SELECT (SUM(precio) * SUM(cantidad_vendidos))/100000 as ingresos FROM resumen_productos

Ya que tenemos claro lo que haremos procedamos a crear nuestro procedimiento almacenado, el cual se debe ver algo así

¿Cómo crear un procedimiento almacenado en MySQL?

DELIMITER $$ -- CAMBIANDO EL DELIMITADOR POR "$$"
DROP PROCEDURE IF EXISTS `SP_INDICADORES` $$
CREATE PROCEDURE `SP_INDICADORES`() -- NO TENEMOS DATOS DE ENTRADA
BEGIN
    DECLARE _PRODUCTOS_TOTALES		FLOAT;
    DECLARE _PRODUCTOS_ALMACEN		FLOAT;
    DECLARE _INGRESOS_TOTALES		FLOAT;
    
    -- Nos permite obtener el total de productos vendidos
	SET _PRODUCTOS_TOTALES = (SELECT SUM(cantidad_vendidos) as vendidos FROM resumen_productos);
    
    -- Nos regresa el total de productos en almacen
    SET _PRODUCTOS_ALMACEN = (SELECT SUM(en_almacen) as enAlmacen FROM resumen_productos);
    
    -- Nos regresa los ingresos totales el 100000 es un valor de corrección
	-- ya que son datos inventados
    SET _INGRESOS_TOTALES = (SELECT (SUM(precio) * SUM(cantidad_vendidos))/100000 as ingresos FROM resumen_productos);
    
    -- bien ahora solo debemos retornar los 3 datos mediante otro SELECT
    SELECT _PRODUCTOS_TOTALES AS PT, _PRODUCTOS_ALMACEN AS PA, _INGRESOS_TOTALES AS IT;
    
END$$
DELIMITER ; -- EL DELIMITADOR VUELVE A SER ";"

El stored procedure no recibe ningún parámetro de entrada, realiza tres  select  con los cuales regresa:

• PRODUCTOS  TOTALES
• PRODUCTOS EN ALMACÉN
• INGRESOS TOTALES

Finalmente se utiliza un  select  más para retornar el resultado. En vídeos anteriores tienes el ¿Por qué? de las consultas utilizadas, puedes ver el curso gratis en YouTube y si deseas suscribirte a mi canal, me ayudarías mucho.

¿Cómo crear un procedimiento almacenado en MySQL con parámetros de entrada?

Ahora que ya tenemos la base de un procedimiento almacenado, ahora vamos a crear un ejemplo con un stored procedure que reciba parámetros de entrada y después los inserte en nuestra base de datos, aquí te dejo este ejemplo.

DELIMITER $$ -- CAMBIANDO EL DELIMITADOR POR "$$"
DROP PROCEDURE IF EXISTS `SP_INSERTAR_PRODUCTO` $$
CREATE PROCEDURE `SP_INSERTAR_PRODUCTO`(
`_nombre`				         VARCHAR(45),
`_categoria`					 VARCHAR(45),
`_precio`						 FLOAT,
`_cantidad_vendidos`			 INT,
`_en_almacen`					 VARCHAR(45),
`_fecha_alta`					 DATETIME		
)
BEGIN
   
   INSERT INTO resumen_productos
        (nombre,categoria,precio,cantidad_vendidos,en_almacen,fecha_alta)
    values
        (_nombre, _categoria, _precio, _cantidad_vendidos, _en_almacen, _fecha_alta);
        
   select LAST_INSERT_ID() as ultimo_id;
        
END$$
DELIMITER ; -- EL DELIMITADOR VUELVE A SER ";"

El SP anterior recibe 6 parámetros de entrada:

• nombre
• categoría
• precio
• cantidad vendidos
• en almacen
• fecha alta

Estos campos están definidos en nuestra tabla resumen_productos y en el cuerpo del SP realiza un insert into y con la instrucción:

•  LAST_INSERT_ID() , recuperamos el último ID insertado desde un stored procedure (procedimiento almacenado)
• Una vez que recuperamos el último id lo retornamos a través de un  select .

Sintaxis ALTER PROCEDURE y DROP PROCEDURE

Si deseas modificar o eliminar de la base de datos un procedimiento almacenado utilizas ALTER O DROP

•  ALTER PROCEDURE , permite modificar un stored procedure existente
•  DROP PROCEDURE , permite eliminar un stored procedure existente

Sintaxis general ALTER PROCEDURE

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

La sintaxis general los indica que al utilizar ALTER solo podemos modificar las características y/o parámetros de un procedimiento almacenado, pero no el cuerpo o contenido del SP, por tal razón realmente el ALTER no es muy usado en desarrollos con MySQL ya que con DROP en conjunto con IF te ahorras mucho.

¿Cómo modificar un procedimiento almacenado en MySQL con ALTER PROCEDURE?

Yo por ejemplo, cuando trabajo con MySQL suelo usar DROP para editar los SP, si te fijas en los ejemplos anteriores antes del CREATE tengo lo siguiente:

DROP PROCEDURE IF EXISTS `SP_INSERTAR_PRODUCTO` $$

¿Cómo ejecutar un procedimiento almacenado en MySQL desde PHP?

Ahora que ya sabemos como crear un SP vamos a llamarlos desde PHP, puedes acceder al código en el repositorio que es donde voy subiendo todo lo referente a este curso. Aquí te dejo el código con el que puedes invocar tu stored procedure desde PHP utilizando sintaxis PDO, en el vídeo te muestro las 3 sintaxis como siempre.

/**
     * Sintaxis PDO
     * Executando Procedimiento almacenado con parámetros
     */
    public function execSPParametrosPDO()
    {
        $nombre = "zapatos PDO";
        $categoria = "calzado";
        $precio = 500;
        $cantidad_vendidos = 20;
        $en_almacen = 30;
        $fecha_alta = "2020-01-30";
        try {
            if ($this->conBDPDO()) {
                $pQuery = $this->oConBD->prepare(" call SP_INSERTAR_PRODUCTO(:nombre,:categoria,:precio,:cantidad_vendidos,:en_almacen,:fecha_alta); ");
                $pQuery->bindParam(':nombre', $nombre);
                $pQuery->bindParam(':categoria', $categoria);
                $pQuery->bindParam(':precio', $precio);
                $pQuery->bindParam(':cantidad_vendidos', $cantidad_vendidos);
                $pQuery->bindParam(':en_almacen', $en_almacen);
                $pQuery->bindParam(':fecha_alta', $fecha_alta);
                $pQuery->execute();
                $pQuery->setFetchMode(PDO::FETCH_ASSOC);
                while ($indicador = $pQuery->fetch()) {
                    printf("Producto insertado con ID: %s \n", $indicador["ultimo_id"]);
                }
                $this->oConBD = null;
            }
        } catch (PDOException $e) {
            echo ("MysSQL.execSPParametrosPDO -- " . $e->getMessage() . "\n");
        }
    }

Conexión a base de datos función conBDPDO.

    /**
     * Conexión BD por PDO
     */
    public function conBDPDO()
    {
        try {
            $this->oConBD = new PDO("mysql:host=" . $this->ipBD . ";dbname=" . $this->nombreBD, $this->usuarioBD, $this->passBD);
            echo "Conexión exitosa..." . "\n";
            return true;
        } catch (PDOException $e) {
            echo "Error al conectar a la base de datos: " . $e->getMessage() . "\n";
            return false;
        }
    }

Esta expresión lo que hace es que si el SP  SP_INSERTAR_PRODUCTO  existe lo elimina y vuelve a crear con los cambios que hayas realizado, esta es una forma de editar un SP, eliminarlo y volverlo a crear y es la forma en que modificas el cuerpo o lógica de un stored procedure en MySQL.

Espero que te haya gustado y te sea útil este post, y si así fue te invito a que te suscribas a mi canal de YouTube que estoy seguro que todo lo que estoy subiendo te va a gustar o simplemente regálame un compartir en tus redes sociales, te lo agradecería mucho.