Procedimientos almacenados
Un procedimiento almacenado es una secuencia de instrucciones SQL que se almacenan en el servidor de la base de datos y pueden ser ejecutados en cualquier momento.
Definición de procedimientos almacenados
La sintaxis básica para la creación de procedimientos almacenados en MySQL es la siguiente
CREATE
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
SQL routineLa sintaxis básica para la creación de procedimientos almacenados en PostgreSQL es la siguiente
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
| sql_body
} ...Parámetros
Los procedimientos almacenados pueden definir dos tipos de parámetros:
De entrada (IN): cuando se invoca el procedimiento se debe proveer un valor para este parámetro, dicho valor se utilizará dentro del procedimiento.
De salida (OUT): cuando se invoca el procedimiento no se pasa un valor, sino una referencia a una variable. Al finalizar la invocación, dicha variable contendrá un valor calculado durante la ejecución del procedimiento
De entrada/salida (INOUT): es una mezcla de las dos anteriores. Cuando se invoca el procedimiento se debe pasar una variable con un valor asignado. Durante la ejecución del procedimiento, dicho valor puede cambiar y se verá reflejado en la variable.
Ejemplo
Si se desea crear un procedimiento almacenado que muestra las películas de una categoría dada:
En MySQL
CREATE PROCEDURE list_films(
IN category VARCHAR(25),
OUT category_films INT UNSIGNED
)
BEGIN
SET category_films = (SELECT COUNT(fc.film_id)
FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
WHERE c.name = category);
END;Veamos en detalle:
El parámetro de salida category_films se carga con el SET
En PostgreSQL
CREATE OR REPLACE PROCEDURE sakila.list_films(
IN category VARCHAR(25),
OUT category_films INT
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(fc.film_id) INTO category_films
FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
WHERE c.name = category ;
END;$$Veamos en detalle:
El parámetro de salida category_films se carga en la query usando INTO
Se debe cambiar el valor del delimiter en la línea 6
Este procedimiento:
Se llama list_films
Tiene un parámetro de entrada llamado category
Tiene un parámetro de salida llamado category_films
Cuando se invoca, calcula el número de películas de la categoría y guarda el resultado en el parámetro de salida category_films
DELIMITER
Debido a que dentro de la definición de un proceso almacenado hay varias sentencias, es necesario modificar el delimitador de sentencia durante su definición en un script, para así poder distinguir entre el final de una sentencia dentro del procedimiento almacenado y el final de la declaración del procedimiento almacenado.
Normalmente el delimitador que se utiliza durante la declaración del procedimiento / función / trigger es $$
-- Esta sentencia elimina el procedimiento si ya existía
DROP PROCEDURE IF EXISTS list_films;
-- Empieza la declaración
CREATE PROCEDURE list_films(
IN category VARCHAR(50),
OUT category_films INT
)
BEGIN
SET category_films = (SELECT COUNT(fc.film_id)
FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
WHERE c.name = category);
END
Invocación de un procedimiento almacenado
Una vez definido un procedimiento, es posible invocarlo de la siguiente manera
En MySQL usamos la palabra reservada CALL. Los parámetros de salida deben tener un nombre que empieza por @, así MySQL sabe que se está declarando esa variable y que el procedimiento va a cargar valores dentro.
-- Se invoca el procedimiento list_films con el parámetro 'Horror'
CALL list_films('Horror', @category_films);
SELECT @category_films;Ten en cuenta que aquí volvemos a usar el delimitador ;.Si se ejecuta esto justo después de la definición del procedimiento, será necesario cambiar el delimitador antes de la llamada.
DELIMITER ;
-- Se invoca el procedimiento list_films con el parámetro 'Horror'
CALL list_films('Horror', @category_films);
SELECT @category_films;En PosgtreSQL usamos la palabra reservada CALL. Los parámetros de salida se debe pasar null y obtenemos el resultado en pantalla al ejecutar el CALL.
CALL list_films('Horror', null);Casos de uso
Transacciones complejas
Los procedimientos almacenados son útiles para coordinar y controlar transacciones complejas que involucran múltiples operaciones de base de datos. Esto garantiza la integridad de los datos y la consistencia de la base de datos, al tiempo que simplifica la lógica de la aplicación.
Al ejecutar múltiples instrucciones SQL dentro de un procedimiento almacenado, se reduce el tráfico de red entre la aplicación y el servidor de base de datos. Esto puede mejorar significativamente el rendimiento, especialmente para operaciones que involucran consultas complejas o procesamiento de datos intensivo.
Ejecución programada
Los procedimientos almacenados pueden ser programados para ejecutarse en momentos específicos utilizando un programador de tareas del sistema operativo o un planificador de eventos del servidor de base de datos. Esto es útil para tareas de mantenimiento programadas, generación de informes, etc.
Last updated