Curso de MariaDB Desde la Consola/StoredProceedures

De WikiCabal
Ir a la navegación Ir a la búsqueda

Stored Prodedures

Definición

Un stored procedure es un segmento de declaraciones SQL declarativas almacenadas dentro del catálogo de bases de datos. Un stored procedure puede ser invocado por triggers, otros stored procedures y aplicaciones como Java, Python, PHP, etc.

Un stored proceedure que se llama a sí mismo se conoce como un stored procedure recursivo. La mayoría de los sistemas de administración de bases de datos admiten stored procedures recursivos. Sin embargo, MariaDB no lo soporta muy bien. Usted debe comprobar su versión de la base de datos MariaDB antes de implementar stored procedures recursivos.

Ventajas de los stored procedures de MariaDB

  • Los stored procedures típicamente ayudan a aumentar el rendimiento de las aplicaciones. Una vez creados, los stored procedures se compilan y almacenan en la base de datos. Sin embargo, MariaDB implementa los stored procedures ligeramente diferentes. Los stored procedures de MariaDB se compilan bajo demanda. Después de compilar un stored procedure, MariaDB lo coloca en un caché. Y MariaDB mantiene su propio caché de stored procedures para cada conexión. Si una aplicación utiliza un stored procedure varias veces en una sola conexión, se utiliza la versión compilada, de lo contrario, el stored procedure funciona como una consulta.
  • Los stored procedures ayudan a reducir el tráfico entre la aplicación y el servidor de bases de datos, ya que en lugar de enviar varias sentencias SQL largas, la aplicación sólo tiene que enviar nombre y parámetros del stored procedure.
  • Los stored procedures son reutilizables y transparentes para cualquier aplicación. Los stored procedures exponen la interfaz de la base de datos a todas las aplicaciones para que los desarrolladores no tengan que desarrollar funciones que ya están soportadas en stored procedure.
  • Los stored proceedures son seguros. El administrador de bases de datos puede conceder permisos adecuados a las aplicaciones que acceden a stored procedures en la base de datos sin conceder permisos sobre las tablas de base de datos subyacentes.

Desventajas de los stored procedures de MariaDB

  • Si utiliza una gran cantidad de stored procedures, el uso de memoria de cada conexión que utiliza estos stored procedures aumentará sustancialmente. Además, si sobreutiliza un gran número de operaciones lógicas dentro de stored procedure, el uso de CPU también aumentará porque el servidor de base de datos no está bien diseñado para operaciones lógicas.
  • Las construcciones de stored procedures dificultan el desarrollo de stored procedures que tienen una lógica de negocio complicada.
  • Es difícil depurar stored procedures. Sólo unos pocos sistemas de gestión de bases de datos permiten depurar stored procedures. Desafortunadamente, MariaDB no proporciona facilidades para depurar stored procedures.
  • No es fácil desarrollar y mantener stored procedures. Desarrollar y mantener stored procedures a menudo se requiere un conjunto de habilidades especializadas que no todos los desarrolladores de aplicaciones poseen. Esto puede dar lugar a problemas tanto en el desarrollo de aplicaciones como en las fases de mantenimiento.

Diferencias entre los stored procedures y las stored functions

  • Los stored procedures no se pueden utilizar en operaciones Select.
  • Los stored procedures admiten la resolución de nombres diferidos.
  • Los stored procedures se utilizan generalmente para realizar la lógica de negocio.
  • Los stored procedures pueden devolver cualquier tipo de datos.
  • Los stored procedures pueden aceptar un mayor número de parámetros de entrada que las stored functions. Los stored procedures pueden tener hasta 21.000 parámetros de entrada.
  • Los stored procedures pueden ejecutar SQL dinámico.
  • Los stored procedures admiten el tratamiento de errores.
  • Funciones no deterministas se pueden utilizar en stored procedures.
  • Las stored functions se pueden utilizar en las instrucciones Select.
  • Las stored functions no admiten la resolución de nombres diferidos.
  • Las stored functions se utilizan generalmente para cálculos.
  • Las stored functions deben devolver un valor.
  • Las stored functions no pueden devolver imágenes.
  • Las stored functions aceptan números más pequeños de parámetros de entrada que los stored procedures. Los stored functions pueden tener hasta 1.023 parámetros de entrada.
  • Las tablas temporales no se pueden utilizar en stored functions.
  • Las stored functions no pueden ejecutar SQL dinámico.
  • Las stored functions no admiten el tratamiento de errores. RAISEERROR OR @@ ERROR no están permitidos en stored functions.
  • Las funciones no deterministas no pueden usarse en stored functions. Por ejemplo, GETDATE() no se puede utilizar en stored functions.

Introducción a los parámetros de stored procedures de MariaDB

Casi todos de los stored procedures que se desarrollan requieren parámetros. Los parámetros hacen que el stored procedure sea más flexible y útil. En MariaDB, un parámetro tiene uno de los tres modos: IN, OUT o INOUT.

  • IN - es el modo predeterminado. Cuando define un parámetro IN en un stored procedure, el programa llamante tiene que pasar un argumento al stored procedure. Además, el valor de un parámetro IN se protege. Esto significa que incluso el valor del parámetro IN se cambia dentro del stored procedure, su valor original se conserva después de que finaliza el procedimiento almacenado. En otras palabras, el stored procedure sólo funciona en la copia del parámetro IN.
  • OUT - el valor de un parámetro OUT puede cambiarse dentro del stored procedure y su nuevo valor se devuelve al programa llamante. Observe que el stored procedure no puede acceder al valor inicial del parámetro OUT cuando se inicia.
  • INOUT - un parámetro INOUT es la combinación de los parámetros IN y OUT. Esto significa que el programa llamante puede pasar el argumento, y el stored procedure puede modificar el parámetro INOUT y pasar el nuevo valor al programa llamante.

Sintaxis de definir un parámetro en los stored procedures

MODE param_nombre param_tipo (param_tamaño)

  • MODE - puede ser IN, OUT o INOUT, dependiendo del propósito del parámetro en el stored procedure.
  • param_nombre - es el nombre del parámetro. El nombre del parámetro debe seguir las reglas de nomenclatura del nombre de la columna en MariaDB.
  • param_tipo y tamaño - de datos. Al igual que una variable, el tipo de datos del parámetro puede ser cualquier tipo de datos válido de MariaDB.

Cada parámetro está separado por una coma (,) si el stored procedure tiene más de un parámetro

Ejemplos de parámetros de stored procedures

Ejemplo del IN parámetro

En el ejemplo siguiente se muestra cómo utilizar el parámetro IN en el stored procedure ObtenOficinasPorPais que selecciona las oficinas ubicadas en un país en particular. 

1 DELIMITER //
2 CREATE PROCEDURE ObtenOficinasPorPais( IN PaisNombre VARCHAR(255) )
3  BEGIN
4    SELECT * 
5    FROM Oficinas
6    WHERE Pais = PaisNombre;
7  END //
8 DELIMITER ;

MariaDB [classicmodels]> show procedure status \G;
*************************** 1. row ***************************
                  Db: classicmodels
                Name: ObtenOficinasPorPais
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2016-11-04 15:51:45
             Created: 2016-11-04 15:51:45
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)



MariaDB [classicmodels]> show create procedure ObtenOficinasPorPais \G;
*************************** 1. row ***************************
           Procedure: ObtenOficinasPorPais
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `ObtenOficinasPorPais`( IN PaisNombre VARCHAR(255) )
BEGIN
   SELECT * 
   FROM Oficinas
   WHERE Pais = PaisNombre;
 END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)



MariaDB [classicmodels]> CALL ObtenOficinasPorPais( 'USA' ); 
+-----------+---------------+-----------------+----------------------+------------+--------+------+-------+------------+
| OficinaID | Ciudad        | Telefono        | Direccion1           | Direccion2 | Estado | Pais | CP    | Territorio |
+-----------+---------------+-----------------+----------------------+------------+--------+------+-------+------------+
| 1         | San Francisco | +1 650 219 4782 | 100 Market Street    | Suite 300  | CA     | USA  | 94080 | NA         |
| 2         | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102  | MA     | USA  | 02107 | NA         |
| 3         | NYC           | +1 212 555 3000 | 523 East 53rd Street | apt. 5A    | NY     | USA  | 10022 | NA         |
+-----------+---------------+-----------------+----------------------+------------+--------+------+-------+------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)



MariaDB [classicmodels]> CALL ObtenOficinasPorPais( 'France' ); 
+-----------+--------+-----------------+--------------------------+------------+--------+--------+-------+------------+
| OficinaID | Ciudad | Telefono        | Direccion1               | Direccion2 | Estado | Pais   | CP    | Territorio |
+-----------+--------+-----------------+--------------------------+------------+--------+--------+-------+------------+
| 4         | Paris  | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | NULL       | NULL   | France | 75017 | EMEA       |
+-----------+--------+-----------------+--------------------------+------------+--------+--------+-------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Ejemplo del OUT parámetro

El siguiente stored procedure devuelve el número de pedidos por
estado de pedido. Tiene dos parámetros:

     OrdenEstatus: el parámetro IN que es el estado del pedido que
                   queremos contar los pedidos.
     Total:        el parámetro OUT que almacena el número de pedidos
                   para un estado de pedido específico.

El siguiente es el código fuente del stored procedure ContarOrdenesPorEstatus.

 1 DELIMITER $$
 2 CREATE PROCEDURE ContarOrdenesPorEstatus(
 3  IN OrdenEstatus VARCHAR(25),
 4  OUT Total INT)
 5 BEGIN
 6  SELECT count(OrdenID)
 7  INTO Total
 8  FROM Ordenes
 9  WHERE Estatus = OrdenEstatus;
10 END$$
11 DELIMITER ;

Para obtener el número de pedidos enviados, llamams al 
stored procedure ContarOrdenesPorEstatus y pasamos el estado
del pedido como Shipped y también pasamos un argumento (@Total)
para obtener el valor devuelto.

CALL ContarOrdenesPorEstatus('Shipped',@Total);
SELECT @Total;

 1 MariaDB [classicmodels]> CALL ContarOrdenesPorEstatus('Shipped',@Total);
 2 Query OK, 1 row affected (0.01 sec)
 3 
 4 MariaDB [classicmodels]> SELECT @Total;
 5 +--------+
 6 | @Total |
 7 +--------+
 8 |    303 |
 9 +--------+
10 1 row in set (0.00 sec)

Para obtener el número de órdenes que están en proceso, 
llamamos al stored procedure ContarOrdenesPorEstatus de
la siguiente manera:

CALL ContarOrdenesPorEstatus('in process',@Total);
SELECT @Total AS  total_in_process;

 1 MariaDB [classicmodels]> CALL ContarOrdenesPorEstatus('in process',@Total);
 2 Query OK, 1 row affected (0.01 sec)
 3 
 4 MariaDB [classicmodels]> SELECT @Total AS  total_in_process;
 5 +------------------+
 6 | total_in_process |
 7 +------------------+
 8 |                6 |
 9 +------------------+
10 1 row in set (0.00 sec)

Ejemplo del INOUT parámetro

1 DELIMITER $$
2 CREATE PROCEDURE set_counter(INOUT cuenta INT(4),IN inc INT(4))
3   BEGIN
4     SET cuenta = cuenta + inc;
5    END$$
6 DELIMITER ;
7 
8 Query OK, 0 rows affected (0.01 sec)

El stored procedure set_counter acepta un parámetro INOUT (cuenta)
y un parámetro IN (inc).

Dentro del stored proceedure, aumentamos el contador (cuenta) por
el valor del parámetro inc.

Vea cómo llamamos al stored procedure set_counter:

SET @counter = 1;
SELECT @counter;
CALL set_counter(@counter,1);
SELECT @counter;
CALL set_counter(@counter,1);
SELECT @counter;
CALL set_counter(@counter,5);
SELECT @counter;
MariaDB [classicmodels]> SET @counter = 1;
Query OK, 0 rows affected (0.00 sec)


 1 MariaDB [classicmodels]> SELECT @counter;
 2 +----------+
 3 | @counter |
 4 +----------+
 5 |        1 |
 6 +----------+
 7 1 row in set (0.00 sec)
 8 
 9 MariaDB [classicmodels]> CALL set_counter(@counter,1);
10 Query OK, 0 rows affected (0.00 sec)
11 
12 MariaDB [classicmodels]> SELECT @counter;
13 +----------+
14 | @counter |
15 +----------+
16 |        2 |
17 +----------+
18 1 row in set (0.00 sec)
19 
20 MariaDB [classicmodels]> CALL set_counter(@counter,1);
21 Query OK, 0 rows affected (0.00 sec)
22 
23 MariaDB [classicmodels]> SELECT @counter;
24 +----------+
25 | @counter |
26 +----------+
27 |        3 |
28 +----------+
29 1 row in set (0.00 sec)
30 
31 MariaDB [classicmodels]> CALL set_counter(@counter,5);
32 Query OK, 0 rows affected (0.00 sec)
33 
34 MariaDB [classicmodels]> SELECT @counter;
35 +----------+
36 | @counter |
37 +----------+
38 |        8 |
39 +----------+
40 1 row in set (0.00 sec)

Modificar un stored procedure

MariaDB proporciona una instrucción ALTER PROCEDURE para modificar una rutina, pero sólo permite
la posibilidad de cambiar ciertas características. Si necesita modificar el cuerpo o los parámetros,
debe eliminar y volver a crear el stored procedure.

Eliminar un stored procedure

DROP PROCEDURE [IF EXISTS] Nombre de Procedure;

Este es un comando simple. La cláusula IF EXISTS opcional previene un error
en caso de que el procedure no exista.

Descargar el EjemploDB

Descargar el EjemploDB

Obtenido de «http://wiki.cabal.mx/index.php?title=Curso_de_MariaDB_Desde_la_Consola/StoredProceedures&oldid=122323»