38. Procedimientos almacenados

Un Stored Procedure es un conjunto de instrucciones SQL que se almacenan en una base de datos como si fuera una función, por lo tanto, lo podemos llamar y ejecutar tantas veces como lo necesitemos. Pueden recibir parámetros de entrada y devolver parámetros de salida como si fuera una función en cualquier lenguaje de programación, y se utilizan para encapsular la lógica de negocio y reducir la complejidad de las aplicaciones a la hora de que están interactuando con la base de datos.

Podemos desarrollar y ejecutar procedimientos almacenados en nuestras bases de datos.

Ejercicio

Vamos a crear un ejercicio completo para entender su funcionamiento del uso de los procedimientos almacenados.

Ejm

CREATE TABLE subscripciones (
subscripcion_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
subscripcion VARCHAR(30) NOT NULL,
costo DECIMAL(5,2) NOT NULL
);

INSERT INTO subscripciones VALUES 
(0,'Bronce',199.99),
(0,'Plata',299.99),
(0,'Oro',399.99);

CREATE TABLE clientes (
cliente_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(30) NOT NULL,
email VARCHAR(50) UNIQUE
);

CREATE TABLE tarjetas (
tarjeta_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cliente INT UNSIGNED,
TARJETA BLOB,
FOREIGN KEY(cliente)
 REFERENCES clientes(cliente_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
);

CREATE TABLE servicios (
servicio_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cliente INT UNSIGNED,
tarjeta INT UNSIGNED,
subscripcion INT UNSIGNED,
FOREIGN KEY(cliente)
 REFERENCES clientes(cliente_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
FOREIGN KEY(tarjeta)
 REFERENCES tarjetas(tarjeta_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
FOREIGN KEY(subscripcion)
 REFERENCES subscripciones(subscripcion_id)
 ON DELETE RESTRICT
 ON UPDATE CASCADE
);

Sintaxis para poder crear un store procedure

Un stored procedure va a ser una especie de función. En un stored procedure vamos a ejecutar más de una linea de código, y hay que delimitarle y hacerle ver a SQL que este stored procedure va a tener más de una ejecución. Para ello tenemos una clausula denominada DELIMITER , como el punto y coma lo utilizamos para el término de cada instrucción, hay que establecer un delimitador para que entienda que de una linea de código a otra se van a estar ejecutando varias instrucciones, para que se tome la delimitación del stored procedure hasta después de la clausula DELIMITER de cierre, y como delimitador podemos usar cualquier carácter especial, aunque se suelen utilizar el símbolo de dólar ($) o dos diagonales (//). Veamos su sintaxis para entenderlo mejor.

Sintaxis (invocar un store procedure)

DELIMITER //

CREATE PROCEDURE sp_obtener_subscripciones()
 BEGIN
  -- Lineas que queramos ejecutar
 END //

DELIMITER ; (El punto y coma tiene que ir separado)

Veamos un ejm.

Ejm

-- Invocamos un store procedure
DELIMITER //

CREATE PROCEDURE sp_obtener_subscripciones()
 BEGIN
  SELECT * FROM subscripciones;
 END //

DELIMITER ;

-- Llamamos un store procedure con la sentencia CALL
CALL sp_obtener_subscripciones();

-- Mandar llamar a todos los store procedures
SHOW PROCEDURE STATUS WHERE db = 'base de datos';

-- Eliminar un store procedure
DROP PROCEDURE sp_obtener_subscripciones;

Parámetros de entrada y salida en PA

Vamos a utilizar los datos de las tablas que pusimos más arriba para hacer un ejercicio completo de stored procedure.

Ejm

DELIMITER //

CREATE PROCEDURE sp_asignar_servicio(
 IN i_subscripcion INT UNSIGNED,
 IN i_nombre VARCHAR(30).
 IN i_email VARCHAR(50),
 IN i_tarjeta VARCHAR(16),
 OUT o_respuesta VARCHAR(50)
)

 BEGIN
  DECLARE existe_correo INT DEFAULT 0; 
  DECLARE cliente_id INT DEFAULT 0;
  DECLARE tarjeta_id INTE DEFAULT 0;

  START TRANSACTION;
   SELECT COUNT(*) INTO existe_correo
    FROM clientes
    WHERE correo = i_correo;

   IF existe_correo <> 0 THEN
    SELECT 'Tu correo ya ha sido registrado' INTO o_respuesta;
   ELSE
    INSERT INTO clientes VALUES (0,i_nombre, i_correo);

    SELECT LAST_INSERT_ID() INTO cliente_id;
    INSERT INTO tarjetas 
     VALUES (0, cliente_id, AES_ENCRYPT(i_tarjeta, cliente_id));

   SELECT LAST_INSERT_ID() INTO tarjeta_id;

   INSERT INTO servicios VALUES (0, cliente_id, tarjeta_id, i_subscripcion);

   SELECT 'Servicio asignado con éxito' INTO o_respuesta;
   END IF;
  COMMIT;
 END //

DELIMITER ;

Ahora vamos ahora a mandar a llamar a nuestro stored procedure.

SELECT * FROM subscripciones;
SELECT * FROM clientes;
SELECT * FROM tarjetas;
SELECT * FROM servicios;

CALL sp_asignar_servicio(3,'Francisco', 'info@sutilweb.eu','1234567890123456', @res);
SELECT @res;
Scroll al inicio