Capítulo 11: Procedimientos almacenados

Enlaces

• Procedimientos almacenados en la documentación de MySQL.
• Un interesante artículo también sobre procedimientos almacenados.

Versión reducida de la base de datos de ejemplo

Creación de la base de datos:

DROP DATABASE IF EXISTS videoteca;
CREATE DATABASE videoteca;

Tabla de intérpretes:

USE videoteca;

DROP TABLE IF EXISTS interprete;
CREATE TABLE interprete
(
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  imdb VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
)
ENGINE = InnoDB;

Registro de prueba:

USE videoteca;

INSERT INTO interprete
(
  nombre,
  apellidos,
  imdb
)
VALUES
(
  'Harrison',
  'Ford',
  'nm0000148'
);

INSERT INTO interprete
(
  nombre,
  apellidos,
  imdb
)
VALUES
(
  'Russell',
  'Crowe',
  'nm0000128'
);

Lista de intérpretes

En su versión más simple:

USE videoteca;

CREATE PROCEDURE pa_interpretes_lista()

SELECT
  *
FROM
  interprete;

Para ejecutar el procedimiento almacenado:

USE videoteca;

CALL pa_interpretes_lista();

Número de registros en la tabla de intérpretes

USE videoteca;

CREATE PROCEDURE pa_interpretes_cantidad()

SELECT
  COUNT(*)
FROM
  interprete;

Delimitadores

Número de intérpretes, usando variables y delimitadores:

USE videoteca;

DELIMITER //

CREATE PROCEDURE pa_interpretes_cantidad2()
BEGIN

DECLARE interpretes INT;

SELECT
  COUNT(*)
FROM
  interprete
INTO
  interpretes;

SELECT interpretes;

END

//
DELIMITER ;

Parámetros

Lista de intérpretes cuyo nombre comienza con una determinada letra:

USE videoteca;

CREATE PROCEDURE pa_interpretes_buscar(letra CHAR(2))

SELECT
  *
FROM
  interprete
WHERE
  nombre LIKE letra;

Interpretes cuyo nombre comienza con la letra h:

USE videoteca;

CALL pa_interpretes_buscar('h%');

Ahora, también se necesita el número de intérpretes localizados:

USE videoteca;

DELIMITER //

CREATE PROCEDURE pa_interpretes_buscar2(
  IN letra CHAR(2),
  OUT interpretes INT
)
BEGIN

SELECT
  *
FROM
  interprete
WHERE
  nombre LIKE letra;

SELECT
  COUNT(*)
INTO
  interpretes
FROM
  interprete
WHERE
  nombre LIKE letra;

END

//
DELIMITER ;

Llamada a este procedimiento:

USE videoteca;

CALL pa_interpretes_buscar2('h%', @cantidad);

Obtener el número de intérpretes:

SELECT @cantidad;

Funciones almacenadas

Número de intérpretes:

USE videoteca;

DELIMITER //

CREATE FUNCTION fa_interpretes_cantidad()
RETURNS INT
BEGIN

DECLARE interpretes INT;

SELECT
  COUNT(*)
INTO
  interpretes
FROM
  interprete;

RETURN interpretes;

END

//
DELIMITER ;

Llamada a la función almacenada:

USE videoteca;

SELECT fa_interpretes_cantidad();

Eliminación

Eliminar un procedimiento almacenado y crearlo de nuevo:

USE videoteca;

DROP PROCEDURE IF EXISTS pa_interpretes_lista;

CREATE PROCEDURE pa_interpretes_lista()

SELECT
  *
FROM
  interprete
ORDER BY
  apellidos,
  nombre;

Detalles

Obtención del código de un procedimiento almacenado existente:

USE videoteca;

SHOW CREATE PROCEDURE pa_interpretes_lista;

Respuesta más ordenada:

USE videoteca;

SHOW CREATE PROCEDURE pa_interpretes_listaG

Detalles de un procedimiento almacenado:

USE videoteca;

SHOW PROCEDURE STATUS LIKE 'pa_interpretes_lista'G

Detalles de todos los procedimientos almacenados:

USE videoteca;

SHOW PROCEDURE STATUSG

Procedimientos almacenados activos

Inserción de un nuevo intérprete:

USE videoteca;

CREATE PROCEDURE pa_interprete_insertar
(
  nuevo_nombre VARCHAR(64),
  nuevo_apellidos VARCHAR(64),
  nuevo_imdb VARCHAR(32)
)

INSERT INTO interprete
(
  nombre,
  apellidos,
  imdb
)
VALUES
(
  nuevo_nombre,
  nuevo_apellidos,
  nuevo_imdb
);

Una inserción:

USE videoteca;

CALL pa_interprete_insertar
(
  'Tim',
  'Robbins',
  'nm0000209'
);

Lista de intérpretes:

USE videoteca;

CALL pa_interpretes_lista();