Rrc: /* Diferencias entre los stored procedures y las stored functions */

2016-11-10T18:14:30Z

‎Diferencias entre los stored procedures y las stored functions

Página nueva

__NOTOC__
* [[:#Stored Procedures | Stored Prodeedures]]
* [[:#Definición | |___ Definición]]
* [[:#Ventajas de los stored procedures de MariaDB | |______ Ventajas de los stored procedures de MariaDB]]
* [[:#Desventajas de los stored procedures de MariaDB | |______ Desventajas de los stored procedures de MariaDB]]
* [[:#Diferencias entre los stored procedures y las stored functions | |______ Diferencias entre los stored procedures y las stored functions]]
* [[:#Introducción a los parámetros de stored procedures de MariaDB | |___ Introducción a los parámetros de stored procedures de MariaDB]]
* [[:#Sintaxis de definir un parámetro en los stored procedures | |______ Sintaxis de definir un parámetro en los stored procedures]]
* [[:#Ejemplos de parámetros de stored procedures | |______ Ejemplos de parámetros de stored procedures]]
* [[:#Ejemplo del IN parámetro | |______ Ejemplo del IN parámetro]]
* [[:#Ejemplo del OUT parámetro | |______ Ejemplo del OUT parámetro]]
* [[:#Ejemplo del INOUT parámetro | |______ Ejemplo del INOUT parámetro]]
* [[:#Modificar un procedimiento almacenado | |___ Modificar un procedimiento almacenado]]
* [[:#Eliminar un stored procedure | |___ Eliminar un stored procedure]]
* [[:#Descargar el EjemploDB | |___ Descargar el EjemploDB]]

= 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 ===
<ul>
<li>
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.
</li>
<li>
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.
</li>
<li>
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.
</li>
<li>
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.
</li>
</ul>

=== Desventajas de los stored procedures de MariaDB ===

<ul>
<li>
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.
</li>
<li>
Las construcciones de stored procedures dificultan el desarrollo de
stored procedures que tienen una lógica de negocio complicada.
</li>
<li>
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.
</li>
<li>
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.
</li>
</ul>

=== Diferencias entre los stored procedures y las stored functions ===
<ul>
<li>
Los stored procedures no se pueden utilizar en operaciones Select.
</li>
<li>
Los stored procedures admiten la resolución de nombres diferidos.
</li>
<li>
Los stored procedures se utilizan generalmente para realizar la lógica de negocio.
</li>
<li>
Los stored procedures pueden devolver cualquier tipo de datos.
</li>
<li>
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.
</li>
<li>
Los stored procedures pueden ejecutar SQL dinámico.
</li>
<li>
Los stored procedures admiten el tratamiento de errores.
</li>
<li>
Funciones no deterministas se pueden utilizar en stored procedures.
</li>
</ul>
<hr>
<ul>
<li>
Las stored functions se pueden utilizar en las instrucciones Select.
</li>
<li>
Las stored functions no admiten la resolución de nombres diferidos.
</li>
<li>
Las stored functions se utilizan generalmente para cálculos.
</li>
<li>
Las stored functions deben devolver un valor.
</li>
<li>
Las stored functions no pueden devolver imágenes.
</li>
<li>
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.
</li>
<li>
Las tablas temporales no se pueden utilizar en stored functions.
</li>
<li>
Las stored functions no pueden ejecutar SQL dinámico.
</li>
<li>
Las stored functions no admiten el tratamiento de errores. RAISEERROR OR @@ ERROR no están permitidos en stored functions.
</li>
<li>
Las funciones no deterministas no pueden usarse en stored functions. Por ejemplo, GETDATE() no se puede utilizar en stored functions.
</li>
</ul>

== 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.
<ul>
<li>
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.
</li>
<li>
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.
</li>
<li>
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.
</li>
</ul>

=== Sintaxis de definir un parámetro en los stored procedures ===

MODE param_nombre param_tipo (param_tamaño)
<ul>
<li>
MODE - puede ser IN, OUT o INOUT, dependiendo del propósito del parámetro en el
stored procedure.
</li>
<li>
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.
</li>
<li>
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.
</li>
</ul>
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.

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">

DELIMITER //
CREATE PROCEDURE ObtenOficinasPorPais( IN PaisNombre VARCHAR(255) )
BEGIN
SELECT *
FROM Oficinas
WHERE Pais = PaisNombre;
END //
DELIMITER ;

</syntaxhighlight>

<pre>
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)

</pre>

<pre>

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)

</pre>

<pre>

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)

</pre>

<pre>

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)

</pre>

=== Ejemplo del OUT parámetro ===
<pre>
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.
</pre>

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">
DELIMITER $$
CREATE PROCEDURE ContarOrdenesPorEstatus(
IN OrdenEstatus VARCHAR(25),
OUT Total INT)
BEGIN
SELECT count(OrdenID)
INTO Total
FROM Ordenes
WHERE Estatus = OrdenEstatus;
END$$
DELIMITER ;
</syntaxhighlight>

<pre>
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;
</pre>

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">

MariaDB [classicmodels]> CALL ContarOrdenesPorEstatus('Shipped',@Total);
Query OK, 1 row affected (0.01 sec)

MariaDB [classicmodels]> SELECT @Total;
+--------+
| @Total |
+--------+
| 303 |
+--------+
1 row in set (0.00 sec)

</syntaxhighlight>

<pre>
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;
</pre>

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">

MariaDB [classicmodels]> CALL ContarOrdenesPorEstatus('in process',@Total);
Query OK, 1 row affected (0.01 sec)

MariaDB [classicmodels]> SELECT @Total AS total_in_process;
+------------------+
| total_in_process |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)

</syntaxhighlight>

=== Ejemplo del INOUT parámetro ===

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">

DELIMITER $$
CREATE PROCEDURE set_counter(INOUT cuenta INT(4),IN inc INT(4))
BEGIN
SET cuenta = cuenta + inc;
END$$
DELIMITER ;

Query OK, 0 rows affected (0.01 sec)

</syntaxhighlight>

<pre>
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)

</pre>

<syntaxhighlight lang="sql" line="GESHI_FANCY_LINE_NUMBERS">

MariaDB [classicmodels]> SELECT @counter;
+----------+
| @counter |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

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

MariaDB [classicmodels]> SELECT @counter;
+----------+
| @counter |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

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

MariaDB [classicmodels]> SELECT @counter;
+----------+
| @counter |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

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

MariaDB [classicmodels]> SELECT @counter;
+----------+
| @counter |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)

</syntaxhighlight>

== Modificar un stored procedure ==

<pre>
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.
</pre>

== Eliminar un stored procedure ==
<pre>
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.
</pre>

== Descargar el EjemploDB ==
[http://www.linuxcabal.org/MariaDBEjemplos.sql.xz Descargar el EjemploDB]

[[Category:MariaDB Desde la Consola]]
[[Category: CursoMariaDB]]

Curso de MariaDB Desde la Consola/StoredProceedures - Historial de revisiones

Rrc: /* Diferencias entre los stored procedures y las stored functions */