Stored Procedure en MySQL (parte 1) [inicio]

• Stored Procedure en MySQL (parte 1) [inicio]
• Stored Procedure en MySQL (parte 2) [iterate, cursores y handlers]
• Stored procedure en MySQL (parte 3) [respaldo y recuperación]
• Stored Procedure en MySQL (parte 4) [validaciones]

Cuando desarrollamos una aplicación tenemos (por seguridad) que estar atentos con las validaciones, evitar que el usuario tenga que introducir la menor cantidad de datos posibles. Es necesario tratar de reducir la carga del servidor, dividir el trabajo, conseguir la manera mas eficaz de llevar a cabo los procedimientos, etc.

Podemos reducir el tiempo de desarrollo, el mantenimiento de los sistemas, evitar hacer peticiones de grandes cantidades de datos, si la lógica más relevante la dejamos del lado de los gestores de base de datos, específicamente, usando los stored procedure (procedimientos almacenados).

Lo procedimientos almacenados son soportados en MySQL desde la versión cinco (5), un stored procedure es una rutina que puede ser llamada en cualquier momento y que se encuentra almacenada dentro de la base de datos, pudiendo validar datos de entrada, utilizar cualquier valor de la base de datos, devolver solo lo que la misma permita y además, ser usado por cualquier lenguaje de programación; lo que significa que si tienes un sistema desarrollado en PHP y que ejecuta cierto procedimiento almacenado, no estas atado a que solo lo puedas llamar desde ese lenguaje, si no que puedes usar otros como, JAVA, ASP.NET, etc .

Comenzando con los stored procedure

Antes de todo hay que seleccionar la base de datos que almacenará los procedimientos:

USE nombre_base_de_datos

En MySQL las sentencias se ejecutan luego de escribir el signo punto y coma (;), es por eso que cuando introducimos una sentencia el prompt sigue apareciendo:

mysql> show databases
    -> 
    ->

hasta que no coloquemos el punto y coma (;) [delimitador por defecto] seguirá esperando que introduzcas nuevos datos, como dentro de los procedimientos almacenados necesitamos de ese carácter especial (punto y coma), hay que indicarle a MySQL que temporalmente NO lo use como carácter delimitador, eso lo haremos usando la sentencia DELIMITER:

DELIMITER nuevo_caracter

ejemplo:

mysql> DELIMITER <
mysql> show databases <
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbprueba           |
| mysql              |
+--------------------+
4 rows in set (0.00 sec)

nota

Luego de programado el procedimiento, lo recomendable es regresar al carácter punto y coma (;) su función de DELIMITADOR y eso lo haces de la misma manera:
DELIMITER ;

---

Quienes han programado en Pascal notaran cierta semejanza con ese lenguaje a la hora de armar el store procedure.

Dentro del procedimiento podras hacer uso de los bloques de control de flujo de datos que nos permitirán determinar el orden en el que se ejecutarán las instrucciones, bloques (IF, While, Repeat - Until, Labe Loop, CASE, etc.).

Las sentencias que podemos usar dentro de los procedimientos son: INSERT, UPDATE, SELECT, DROP, CREATE, REPLACE.

Por otro lado, no podemos usar:
- CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE
- CREATE FUNCTION, DROP FUNCTION
- CREATE TRIGGER, DROP TRIGGER 

Existen dos tipos de  procedimientos almacenados:
- procedure: se limita a ejecutar las instrucciones cuando es llamada, no devuelve un valor directamente, pero se pueden establecer valores en variables que puedan ser accedidas luego del que el procedimiento haya culminado su ejecución (ver variables de usuarios).
- function: se usa para que cuando además de ejecutar determinadas instrucciones, retorne un valor (solo uno) que pueda ser usado luego en alguna expresión.

Para crear un procedimiento la sintaxis es la siguiente:

mysql> create procedure nombre_del_procedimiento ()
    -> begin /* indica el inicio de la rutina */
    ->    /* aqui va el contenido */
    -> end /* indica el fin de la rutina */;
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter <
mysql> create function saludo () returns char(5)
    -> begin
    ->    return 'hola';
    -> end <

Para llamar a un procedimiento usamos la sentencia call:

mysql> call nombre_del_procedimiento();

y para llamar a una función, lo hacemos con select:

mysql> select saludo();
+------------+
| + saludo() |
+------------+
| hola       |
+------------+

Antes de crear un procedure es recomendable verificar si existe algún procedimiento con los mismo nombre y de ser así, eliminarlo, eso lo hacemos usando DROP:

DROP PROCEDURE IF EXISTS Nombre_procedimiento

Los comentarios dentro de los procedimientos pueden hacerse de la siguiente manera:

# comentario con almohadilla (solo una linea)
-- comentario con 2 guiones (solo una linea)
/*
   esto es
   un comentario
   multilinea
*/

Para declarar una variable, de la siguiente manera:

declare Variable tipo_de_dato; -- tipo_de_dato -> int, char, varchar ..

Asignando valores a las variables:

set variable = VALOR ; -- valor de acuerdo al tipo de dato 

Estructuras de control de flujo:

1.- Bloque IF

IF expresión THEN
   -- que hacer en caso correcto
ELSE
   -- que hacer en caso contrario
END IF; -- necesario para cerrar el bloque

podemos usar el ELSIF, ejemplo:

IF (Num = 1) THEN
      -- ..
ELSEIF (Num = 2) THEN
      -- ..
ELSEIF (Num = 3) THEN
      -- ..
ELSE
      -- ..
END IF;

2.- Ciclos/loop (While)

WHILE expresión DO
   -- contenido del bucle
END WHILE;

3.- Ciclos/loop (Repeat - Until)

REPEAT
   -- CODE DEL BUCLE
UNTIL variable >= 1
END REPEAT;

4.- Ciclos/loop (etiqueta : Loop)

ETIQUETA : LOOP
   -- code del bucle
   IF variable >= VALOR THEN
      LEAVE ETIQUETA; -- se ejecuta hasta encontrar esto
   END IF;
END LOOP;

Donde ETIQUETA es una cadena de caracteres cualquiera.
Para el caso de este bucle, su código se ejecutara hasta que se encuentre la sentencia LEAVE ETIQUETA cuyo objetivo es similar a la función que cumple BREAK en algunos lenguajes de programación.

Funciones de control de flujo

1.- función IF (if en una sola línea): es similar al IFF de visual basic6.
- sintaxis: IF(expresión1,expr1,expr3) --> devuelve expr2 si se cumple la expresión1, sino, devuelve expr3, ejemplo:

SET @valor =IF (5<5,1,0); 
SELECT @valor; -- devuelve 0 porque 5 no puede ser menor que 5 ..

SELECT IF(1>2,'uno ES mayor que dos','uno NO es mayor que dos') as Resultado;
+-------------------------+
| Resultado               |
+-------------------------+
| uno No es mayor que dos |
+-------------------------+

2.- CASE (similar al SWITCH o SELEC CASE de algunos lenguajes de programación)

CASE variable
   WHEN 1 THEN -- que hacer en caso de que variable = 1
   WHEN 2 THEN -- que hacer en caso de que variable = 2
   WHEN x THEN -- que hacer en caso de que variable = x Valor
   ELSE -- que hacer en caso de que no se presente alguno de los casos anteriores
END CASE;

3.- IFNULL
- sintaxis: IFNULL(expr1,expr2) --> si la primera expresión (expr1) es NULL, devuelve expr1, sino, devuelve expr2.

SELECT IFNULL(NULL,6); -- devuelve 6
SELECT IFNULL(1/0,10); --> devuelve 10

4.- NULLIF
- sintaxis: NULLIF(expresión1,expresión2) --> devuelve NULL si las dos expresiones son iguales, sino, devuelve la expresión1.

SELECT NULLIF(1,1); -- devuelve NULL
SELECT NULLIF(1,2); -- devuelve 1

Los parámetros
La lista de parámetros es lo que está entre paréntesis inmediatamente después del nombre del procedimiento almacenado y pueden ser IN, OUT y INOUT.

- IN: es opcional colocarlo, puedes no indicar que tipo de argumento usaras y por defecto se tomara como IN (entrada). Los valores que tomen las variables de este tipo no se conservaran una vez termine de ejecutarse el procedimiento.

DROP PROCEDURE IF EXISTS ejemplo;
DELIMITER //
CREATE PROCEDURE ejemplo(IN id INT)
BEGIN
  -- el parametro es IN (solo de entrada), no se podra acceder a su valor luego
  -- de que termine el procedimiento.

  -- se concatenan dos campos y se muestra el resultado  
  SELECT CONCAT(apellido,' ',nombre) as Nombre_Completo
  FROM usuarios WHERE idusuario=id;

END//
DELIMITER ;

-- mostrara el registro de id 2
CALL ejemplo(2);

- OUT: los valores de este tipo de argumentos se establecen dentro del procedimiento y pueden ser accedidos mas adelante, aun cuando la ejecución del procedimiento haya terminado.

DROP PROCEDURE IF EXISTS ejemplo;
DELIMITER //
CREATE PROCEDURE ejemplo
(
 -- recibe la variable de usuario donde se almacenara el resultado
 OUT total INT
)
BEGIN
   -- calculamos el numero de registros y los almacenamos en la variable de usuario
   SELECT COUNT(*) INTO total FROM usuarios;
END//
DELIMITER ;

-- llamamos al procedimiento indicandole donde almacenara el resultado (@mi_variable)
call ejemplo(@mi_variable);

-- ahora podemos acceder a @mi_variable cuando lo deseemos
SELECT @mi_variable;

En este último ejemplo hicimos uso de la sentencia SELECT .. INTO .., que permite almacenar en determinadas variables, el resultado de determinadas columnas.

- INOUT: una combinación de las dos anteriores, la variable puede recibir valores de entrada y puede ser accedida mas adelante .

DROP PROCEDURE IF EXISTS ejemplo;
DELIMITER //
CREATE PROCEDURE ejemplo
(
 -- recibe la variable de usuario donde se almacenara el resultado
 -- puede trabajar con el valor recibido por parámetro
 -- el valor actualizado de la variable puede ser accedida en cualquier momento
 INOUT var INT
)
BEGIN
  SET var :=  var + 6;
END//
DELIMITER ;

-- inicializamos la variable
SET @mi_variable := 4;

-- llamamos al procedimiento indicandole donde almacenara el resultado (@mi_variable)
call ejemplo(@mi_variable);

-- ahora podemos acceder a @mi_variable cuando lo deseemos
-- devuelve 10 ya que se actualizo su valor dentro del procedure
SELECT @mi_variable;

---

Ejemplo práctico:

Clic aquí para mostrar/ocultar el código

mysql> CREATE TABLE usuarios 
    -> (
    -> idusuario INT AUTO_INCREMENT PRIMARY KEY,
    -> nombre VARCHAR(15) NOT NULL,
    -> apellido VARCHAR(15) NOT NULL,
    -> fecha_nacimiento DATE NOT NULL
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO usuarios (nombre,apellido,fecha_nacimiento) 
    -> VALUES ('pedro','martinez','1988-02-01'),('Maria','Lopez','1991-05-03'),
    -> ('Luis','Briceño','1990-10-23'),('Javier','Medina','1987-09-11'),
    -> ('Ana','De Armas','1992-12-17'),('Sofia','Romero','1970-03-06');
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM usuarios;
+-----------+--------+----------+------------------+
| idusuario | nombre | apellido | fecha_nacimiento |
+-----------+--------+----------+------------------+
|         1 | Pedro  | Martines | 1988-02-01       |
|         2 | Maria  | Lopez    | 1991-05-03       |
|         3 | Luis   | Briceño  | 1990-10-23       |
|         4 | Javier | Medina   | 1987-09-11       |
|         5 | Ana    | De Armas | 1992-12-17       |
|         6 | Sofia  | Romero   | 1970-03-06       |
+-----------+--------+----------+------------------+
6 rows in set (0.03 sec)

mysql> DROP PROCEDURE IF EXISTS ejemplo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE ejemplo(IN id INT)
    -> BEGIN
    -> -- se concatenan dos campos y se muestra el resultado   
    -> SELECT CONCAT(apellido,' ',nombre) as Nombre_Completo 
    -> FROM usuarios WHERE idusuario=id;
    -> END//
Query OK, 0 rows affected (0.02 sec)

mysql> call ejemplo(2)//
+------------------+
| Nombre_Completo  |
+------------------+
| Lopez Maria      |
+------------------+
1 row in set (0.00 sec)

mysql> call ejemplo (5)//
+------------------+
| Nombre_Completo  |
+------------------+
| De Armas Ana     |
+------------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION ejemplo2(Num INT) RETURNS INT
    -> BEGIN
    -> 
    ->    IF (Num = 1) THEN
    ->       RETURN (1+3);
    ->    ELSEIF (Num = 2) THEN
    ->       RETURN (2+4);
    ->    ELSEIF (Num = 3) THEN
    ->       RETURN (3+5);
    ->    ELSE
    ->       RETURN 0;
    ->    END IF;
    -> END//
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;
mysql> 
mysql> SELECT ejemplo2(2);
+------------+
|ejemplo2(2) |
+------------+
|          6 |
+------------+

---

Fuentes:
conclase.net
mysql.com
wikipedia.org