Si ya usamos bases de datos como Oracle, Interbase / Firebird, PostgreSQL, seguro escuchamos hablar de procedimientos almacenados. Y en MariaDB esto no es una novedad.
Ahora bien, ¿qué son en realidad los procedimientos almacenados? Luego de sumergirnos en este tema veremos que el nombre es plenamente identificatorio y casi explica lo que es un procedimiento almacenado.
Los procedimientos almacenados son un conjunto de instrucciones SQL más una serie de estructuras de control que nos permiten dotar de cierta lógica al procedimiento. Estos procedimientos están guardados en el servidor y pueden ser accedidos a través de llamadas, como veremos más adelante.
Para crear un procedimiento, MariaDB nos ofrece la directiva CREATE PROCEDURE. Al crearlo éste es ligado o relacionado con la base de datos que se está usando, tal como cuando creamos una tabla, por ejemplo.
Para llamar a un procedimiento lo hacemos mediante la instrucción CALL. Desde un procedimiento podemos invocar a su vez a otros procedimientos o funciones.
Un procedimiento almacenado, al igual cualquiera de los procedimientos que podamos programar en nuestras aplicaciones utilizando cualquier lenguaje, tiene:
- Un nombre.
- Puede tener una lista de parámetros.
- Tiene un contenido (sección también llamada definición del procedimiento: aquí se especifica qué es lo que va a hacer y cómo). Ese contenido puede estar compuesto por instrucciones sql, estructuras de control, declaración de variables locales, control de errores, etcétera.
MariaDB sigue la sintaxis SQL:2003 para procedimientos almacenados, que también usa IBM DB2.
En resumen, la sintaxis de un procedimiento almacenado es la siguiente:
CREATE PROCEDURE nombre (parámetro)
[características] definición
o para ser más técnicos:
CREATE
[OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MariaDB data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement
Puede haber más de un parámetro (se separan con comas) o puede no haber ninguno (en este caso deben seguir presentes los paréntesis, aunque no haya nada dentro).
Los parámetros tienen la siguiente estructura: modo nombre tipo
Donde:
- modo: es opcional y puede ser IN (el valor por defecto, son los parámetros que el procedimiento recibirá), OUT (son los parámetros que el procedimiento podrá modificar) INOUT (mezcla de los dos anteriores).
- nombre: es el nombre del parámetro.
- tipo: es cualquier tipo de dato de los provistos por MariaDB.
- Dentro de características es posible incluir comentarios o definir si el procedimiento obtendrá los mismos resultados ante entradas iguales, entre otras cosas.
- definición: es el cuerpo del procedimiento y está compuesto por el procedimiento en sí: aquí se define qué hace, cómo lo hace y bajo qué circunstancias lo hace.
Así como existen los procedimientos, también existen las funciones. Para crear una función, MariaDB nos ofrece la directiva CREATE FUNCTION.
La diferencia entre una función y un procedimiento es que la función devuelve valores. Estos valores pueden ser utilizados como argumentos para instrucciones SQL, tal como lo hacemos normalmente con otras funciones como son, por ejemplo, MAX() o COUNT().
Utilizar la cláusula RETURNS es obligatorio al momento de definir una función y sirve para especificar el tipo de dato que será devuelto (sólo el tipo de dato, no el dato).
Su sintaxis es:
CREATE FUNCTION nombre (parámetro)
RETURNS tipo
[características] definición
o para ser más técnicos:
CREATE [OR REPLACE]
[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]
RETURN func_body
func_parameter:
param_name type
type:
Any valid MariaDB data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
func_body:
Valid SQL procedure statement
Puede haber más de un parámetro (se separan con comas) o puede no haber ninguno (en este caso deben seguir presentes los paréntesis, aunque no haya nada dentro). Los parámetros tienen la siguiente estructura: nombre tipo
Donde:
- nombre: es el nombre del parámetro.
- tipo: es cualquier tipo de dato de los provistos por MariaDB.
- Dentro de características es posible incluir comentarios o definir si la función devolverá los mismos resultados ante entradas iguales, entre otras cosas.
- definición: es el cuerpo del procedimiento y está compuesto por el procedimiento en sí: aquí se define qué hace, cómo lo hace y cuándo lo hace.
Para llamar a una función lo hacemos simplemente invocando su nombre, como se hace en muchos lenguajes de programación.
Desde una función podemos invocar a su vez a otras funciones o procedimientos.
delimiter //
CREATE PROCEDURE procedimiento (IN cod INT)
BEGIN
SELECT * FROM tabla WHERE cod_t = cod;
END
//
Query OK, 0 rows affected (0.00 sec)
delimiter ;
CALL procedimento(4);
En el código anterior lo primero que hacemos es fijar un delimitador. Al utilizar la línea de comandos de MariaDB vimos que el delimitador por defecto es el punto y coma (;): en los procedimientos almacenados podemos definirlo nosotros.
Lo interesante de esto es que podemos escribir el delimitador anterior; sin que el procedimiento termine. Más adelante, en este mismo código volveremos al delimitador clásico. Luego creamos el procedimiento con la sintaxis vista anteriormente y ubicamos el contenido entre las palabras reservadas BEGIN y END.
El procedimiento recibe un parámetro para luego trabajar con él, por eso ese parámetro es de tipo IN. Definimos el parámetro como OUT cuando en él se va aguardar la salida del procedimiento. Si el parámetro hubiera sido de entrada y salida a la vez, sería de tipo denominado INOUT.
El procedimiento termina y es llamado luego mediante la siguiente instrucción:
mysql> CALL procedimento(4);
Otro ejemplo:
CREATE PROCEDURE procedimiento2 (IN a INTEGER)
BEGIN
DECLARE variable CHAR(20);
IF a > 10 THEN
SET variable = ‘mayor a 10’;
ELSE
SET variable = ‘menor o igual a 10’;
END IF;
INSERT INTO tabla VALUES (variable);
END
- El procedimiento recibe un parámetro llamado a que es de tipo entero.
- Se declara una variable para uso interno que se llama variable y es de tipo char.
- Se implementa una estructura de control y si a es mayor a 10 se asigna a variable un valor. Si no lo es se le asigna otro.
- Se utiliza el valor final de variable en una instrucción SQL.
Recordemos que para implementar el ultimo ejemplo se deberán usar nuevos delimitadores, como se vio anteriormente.
Observemos ahora un ejemplo de funciones:
delimiter //
CREATE FUNCTION cuadrado (s SMALLINT) RETURNS SMALLINT
RETURN s*s;
//
Query OK, 0 rows affected (0.00 sec)
delimiter ;
SELECT cuadrado(2);
En definitiva hemos dado un recorrido por el mundo de la programación de procedimientos almacenados en MariaDB.
Dejo Links:
https://mariadb.com/kb/en/library/create-procedure/
https://mariadb.com/kb/en/library/stored-procedures/
