Los procedimientos almacenados son conjuntos de sentencias SQL diseñados para realizar tareas específicas, almacenados en la base de datos. Después de la compilación inicial, no necesitan ser compilados nuevamente al ser invocados, permitiendo a los usuarios utilizarlos mediante el nombre del procedimiento y parámetros definidos. Aunque su uso no es muy extendido, presentan similitudes con los scripts.

Ventajas y Desventajas de los Procedimientos Almacenados

Ventajas:

1. Mejoran la funcionalidad y flexibilidad del lenguaje SQL: Los procedimientos pueden ser escritos con sentencias de control, ofreciendo gran flexibilidad para realizar operaciones complejas y cálculos.
2. Programación modular estándar: Una vez creados, los procedimientos pueden ser invocados múltiples veces sin necesidad de reescribir las sentencias SQL. Además, los profesionales de bases de datos pueden modificar los procedimientos sin afectar el código de la aplicación.
3. Mayor velocidad de ejecución: Para operaciones que contienen gran cantidad de código Transaction-SQL o que se ejecutan repetidamente, los procedimientos almacenados son significativamente más rápidos que el procesamiento por lotes, ya que están precompilados.
4. Reducción del tráfico de red: Cuando las operaciones sobre objetos de base de datos se organizan en procedimientos, solo se transmite la llamada al procedimiento en lugar de múltiples sentencias SQL, reduciendo el tráfico de red.
5. Mecanismo de seguridad: Al limitar los permisos para ejecutar procedimientos específicos, se puede controlar el acceso a datos, evitando que usuarios no autorizados accedan a información sensible.

Desventajas:

1. Baja portabilidad: Los procedimientos almacenados suelen ser específicos de un sistema de gestión de bases de datos.
2. Limited para sentencias SQL simples: Para operaciones simples, no ofrecen ventajas significativas.
3. No siempre reducen la transmisión de red: En algunos casos, no se logra la reducción esperada del tráfico de red.
4. Impacto mínimo en seguridad con un solo usuario: Si solo un usuario utiliza la base de datos, los procedimientos no aportan beneficios de seguridad adicionales.
5. Requieren estandarización en equipos: En desarrollo en equipo, es necesario establecer estándares previamente para evitar altos costos de mantenimiento.
6. No recomendados para alta concurrencia con operaciones complejas: En sistemas con gran cantidad de usuarios concurrentes, los procedimientos con operaciones intensivas pueden afectar el rendimiento.
7. Lógica compleja mejor en el frontend: Para lógica de negocio compleja, especialmente con operaciones sobre tablas grandes, es preferible simplificar primero en la interfaz.

Creación de Procedimientos Almacenados

Configuración del entorno de prueba

mysql> create database prueba_db;
Query OK, 1 row affected (0.00 sec)

mysql> use prueba_db;
Database changed
mysql> create table empleados_tb(id int,nombre varchar(20),departamento varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into empleados_tb values(1,'Juan','Ventas'),(2,'María','Contabilidad'),(3,'Carlos','Recursos Humanos');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

Sintaxis para crear un procedimiento almacenado

create procedure [nombre_procedimiento]([in|out|inout] [nombre_parametro] [tipo_parametro],...)
begin
    [conjunto_sentencias_SQL;]
end

Nota: Dado que el conjunto de sentencias SQL utiliza el punto y coma (;) como separador, es necesario redefinir el delimitador antes de crear el procedimiento usando delimiter [nuevo_delimitador];

En la definición de parámetros, in indica un parámetro de entrada (lee el valor de una variable externa), out indica un parámetro de salida (pasa el valor de una variable interna a una externa), y inout combina ambas funcionalidades. Si no se especifica, se asume in.

Ejemplos:

mysql> delimiter //
mysql> create procedure listar_empleados()
    -> begin
    -> select * from empleados_tb;
    -> end //
Query OK, 0 rows affected (0.05 sec)

mysql> create procedure buscar_empleado(inout id_empleado int)
    -> begin
    -> select * from empleados_tb where id=id_empleado;
    -> set id_empleado=2;
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

El conjunto de sentencias SQL también puede incluir comentarios de una línea (--) y comentarios de múltiples líneas (/* ... */).

Invocación de Procedimientos Almacenados

La invocación de procedimientos almacenados es sencilla mediante la sintaxis: call [nombre_procedimiento](@[nombre_parametro_externo],...)

Ejemplo:

mysql> call listar_empleados();　　//Invocación del procedimiento listar_empleados
+------+-----------+-------------------+
| id   | nombre    | departamento      |
+------+-----------+-------------------+
|    1 | Juan      | Ventas            |
|    2 | María     | Contabilidad      |
|    3 | Carlos    | Recursos Humanos  |
+------+-----------+-------------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
mysql> set @id_busqueda=1;　　//Establecer una variable local
Query OK, 0 rows affected (0.00 sec)

mysql> call buscar_empleado(@id_busqueda);　　//Invocación del procedimiento buscar_empleado
+------+-----------+-------------------+
| id   | nombre    | departamento      |
+------+-----------+-------------------+
|    1 | Juan      | Ventas            |
+------+-----------+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @id_busqueda;　　//El valor de @id_busqueda ha cambiado
+---------------+
| @id_busqueda |
+---------------+
| 2             |
+---------------+
1 row in set (0.00 sec)

Visualización de Procedimientos Almacenados

Ver todos los procedimientos almacenados:

mysql> select name from mysql.proc where type='PROCEDURE';
+-------------------------------------+
| name                                |
+-------------------------------------+
| create_synonym_db                   |
| diagnostics                         |
| execute_prepared_stmt               |
| ps_setup_disable_background_threads |
| ps_setup_disable_consumer           |
| ps_setup_disable_instrument         |
| ps_setup_disable_thread             |
| ps_setup_enable_background_threads  |
| ps_setup_enable_consumer            |
| ps_setup_enable_instrument          |
| ps_setup_enable_thread              |
| ps_setup_reload_saved               |
| ps_setup_reset_to_default           |
| ps_setup_save                       |
| ps_setup_show_disabled              |
| ps_setup_show_disabled_consumers    |
| ps_setup_show_disabled_instruments  |
| ps_setup_show_enabled               |
| ps_setup_show_enabled_consumers     |
| ps_setup_show_enabled_instruments   |
| ps_statement_avg_latency_histogram  |
| ps_trace_statement_digest           |
| ps_trace_thread                     |
| ps_truncate_all_tables              |
| statement_performance_analyzer      |
| table_exists                        |
| listar_empleados                    |
| buscar_empleado                     |
+-------------------------------------+
28 rows in set (0.00 sec)

También es posible especificar el nombre de la base de datos para reducir el resultado:

mysql> select name from mysql.proc where db='prueba_db' and type='PROCEDURE';               
+---------------+
| name          |
+---------------+
| listar_empleados|
| buscar_empleado|
+---------------+
2 rows in set (0.00 sec)

Ver el contenido específico de un procedimiento:

mysql> show create procedure prueba_db.listar_empleados\G
*************************** 1. row ***************************
           Procedure: listar_empleados
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `listar_empleados`()
begin
select * from empleados_tb;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Ver el estado de los procedimientos (no recomendado debido a la gran cantidad de salida):

mysql> show procedure status\G

Modificación de Procedimientos Almacenados

Para modificar un procedimiento almacenado, se debe eliminar y volver a crear, o utilizar herramientas de terceros.

Eliminación de Procedimientos Almacenados

Sintaxis: drop procedure [nombre_procedimiento];

mysql> drop procedure buscar_empleado; 
Query OK, 0 rows affected (0.00 sec)

mysql> select `name` from mysql.proc where db='prueba_db' and `type`='PROCEDURE';
+---------------+
| name          |
+---------------+
| listar_empleados|
+---------------+
1 row in set (0.00 sec)

Uso Avanzado del Conjunto de Sentencias SQL

1) Definición de variables internas en procedimientos

Sentencia para definir variables: declare [nombre_variable,...] [tipo_variable] [default valor_predeterminado];

Sentencia de asignación: set [nombre_variable]=[expresión];

Es posible definir múltiples variables del mismo tipo y establecer valores inicialles.

2) Anidamiento múltiple

Ejemplo de anidamiento:

mysql> delimiter //
mysql> create procedure mostrar_niveles()
    -> begin
    -> declare nivel1 varchar(20) default 'Nivel Principal';
    -> select nivel1;
    -> begin
    -> declare nivel2 varchar(20) default 'Nivel Anidado';
    -> select nivel2;
    -> end;
    -> end //
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> call mostrar_niveles();
+---------------+
| nivel1        |
+---------------+
| Nivel Principal|
+---------------+
1 row in set (0.00 sec)

+---------------+
| nivel2        |
+---------------+
| Nivel Anidado |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3) Sentencias condicionales

Sentencia if

if [condicion] then
[contenido_a_ejecutar]
end if;

if [condicion] then
[contenido_a_ejecutar]
else
[contenido_a_ejecutar]
end if;

Ejemplo de if:

mysql> delimiter //
mysql> create procedure condicional_if(in numero int)
    -> begin
    -> declare resultado int;
    -> set resultado=numero;
    -> if resultado>0 then
    -> set resultado=resultado+1;
    -> else
    -> set resultado=resultado+2;
    -> end if;
    -> select resultado;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call condicional_if(1)//
+----------+
| resultado |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call condicional_if(-2)//
+----------+
| resultado |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Sentencia case

case [variable]
when [valor] then
[contenido_a_ejecutar]
when [valor] then
[contenido_a_ejecutar]
...
else
[contenido_a_ejecutar]
end case;

Ejemplo de case:

mysql> create procedure condicional_case(in numero int)
    -> begin
    -> case numero
    -> when 0 then
    -> set numero=numero+1;
    -> when 1 then 
    -> set numero=numero-2;
    -> else 
    -> set numero=numero-3;
    -> end case;
    -> select numero;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call condicional_case(0)//
+--------+
| numero |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call condicional_case(1)//
+--------+
| numero |
+--------+
|     -1 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call condicional_case(9)//
+--------+
| numero |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

4) Sentencias de bucle

Sentencia while (evalúa antes de ejecutar)

while [condicion] do
[contenido_a_ejecutar]
end while;

Ejemplo de while:

mysql> create procedure bucle_while(in limite int)
    -> begin
    -> declare contador int;
    -> set contador=0;
    -> while contador<limite do=""> set contador=contador+1;
    -> end while;
    -> select contador;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call bucle_while(5)//
+----------+
| contador  |
+----------+
|         6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
</limite>

Sentencia repeat (ejecuta primero, evalúa después)

repeat
[contenido_a_ejecutar]
until [condicion]
end repeat;

Ejemplo de repeat:

mysql> create procedure bucle_repeat(in limite int)
    -> begin
    -> declare contador int;
    -> set contador=0;
    -> repeat
    -> set contador=contador+2;
    -> until contador>limite
    -> end repeat;
    -> select contador;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call bucle_repeat(5)//
+----------+
| contador  |
+----------+
|         6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Sentencia loop (ejecuta hasta encontrar leave)

[etiqueta]:loop
[contenido_a_ejecutar]
leave [etiqueta]
[contenido_a_ejecutar]
end loop;

Ejemplo de loop:

mysql> create procedure bucle_loop(in limite int)
    -> begin
    -> declare contador int;
    -> set contador=0;
    -> ciclo1:loop
    -> set contador=contador+2;
    -> if contador>limite then
    -> leave ciclo1;
    -> end if;
    -> end loop;
    -> select contador;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call bucle_loop(5)//
+----------+
| contador  |
+----------+
|         6 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)