Procedimientos almacenados

Los procedimientos son muy similares a las funciones, ya que deben tener un nombre, pueden recibir parámetros y pueden contener una o más sentencias en su cuerpo.

Pero, a diferencia de las funciones, los procedimientos almacenados no deben devolver un valor. Por este motivo, los procedimientos no contienen la cláusula RETURNS ni la sentencia RETURN en su cuerpo.

Además, los procedimientos almacenados pueden contener sentencias Transaccionales, como COMMIT, las funciones no.

Por otro lado, los procedimientos no se pueden invocar en una expresión, como las funciones, sino que deben ejecutarse en una sentencia independiente. Esta es la sentencia CALL, que se utiliza del siguiente modo:

1
CALL <nombre_proc>[()];

Los paréntesis son opcionales si el procedimiento no requiere parámetros.

Ventajas

Los procedimientos son usados generalmente para unificar algún proceso de la lógica del negocio sobre la base de datos.

Al almacenarse en el servidor, y ejecutarse en una sola llamada, se aprovecha el poder de procesamiento del servidor y se reduce el tráfico de la red.

Un procedimiento es creado y compilado una sola vez, y luego ejecutado a solicitud, por lo tanto, su uso es rápido y eficiente.

Una vez validado, un conjunto de procedimientos puede ser utilizado por muchos clientes (aplicaciones). Si la definición de algún procedimiento cambia, solo se debe modificar en un solo lugar, en vez de modificar las aplicaciones que lo invocan.

Creación

Para crear un procedimiento debemos ejecutar la sentencia CREATE PROCEDURE como se muestra a continuación:

1
CREATE PROCEDURE <nombre>([<parametros>])
2
<cuerpo del procedimiento>

Donde <cuerpo del procedimiento> cumple las mismas condiciones que para una función: se puede utilizar una sentencia compuesta BEGIN ... END si hay más de una sentencia y se pueden crear variables.

Y <parametros>, puede omitirse si el procedimiento no usa parámetros, pero, de existir, debe ser una lista de parámetros, con sus correspondientes tipos de dato, separados por comas, de la forma:

1
[IN | OUT | INOUT] nombre_param <tipo>

Parámetros

Los parámetros pueden ser de tres tipos:

De entrada, IN: idénticos a los de las funciones, permiten pasar valores hacia el procedimiento para usarlos en su cuerpo.
De salida, OUT: permiten pasar valores desde el procedimiento hacia afuera. Inicialmente tienen el valor NULL, pero dentro del procedimiento se les puede asignar un valor específico. Al finalizar el procedimiento, su invocador puede ver/usar el valor.
De entrada-salida, INOUT: son parámetros inicializados afuera del procedimiento, pasan un valor hacia adentro y pueden ser modificados en el cuerpo del procedimiento. Cualquier cambio podrá ser visto afuera.

Si se especifica un parámetro sin indicar si es de entrada (IN), salida (OUT) o entrada-salida (INOUT), se asignará por defecto el tipo IN.

Ejemplo de uso

Vamos a volver a un ejemplo visto en el tema de Transacciones, donde realizábamos una transferencia de fondos entre dos clientes de un banco.

Ya que un procedimiento puede incluir Transacciones, vamos a crear uno que implemente la funcionalidad mencionada.

En esencia, vamos a utilizar la misma Transacción mostrada en el tema anterior, pero en lugar de escribir los valores literales, vamos a crear parámetros para ellos.

Además, vamos a utilizar un parámetro de salida para informar al invocador el id de la transferencia realizada.

1
DELIMITER $$
2

3
CREATE PROCEDURE transferencia_fondos(
4
    IN origen INT,
5
    IN destino INT,
6
    IN monto DECIMAL,
7
    OUT id_transferencia INT
8
)
9
BEGIN
10
    START TRANSACTION;
11

12
    IF (SELECT saldo FROM cuentas WHERE id_cuenta = origen) >= monto THEN
13
        UPDATE cuentas
14
        SET saldo = saldo - monto
15
        WHERE id_cuenta = origen;
16

17
        UPDATE cuentas
18
        SET saldo = saldo + monto
19
        WHERE id_cuenta = destino;
20

21
        INSERT INTO transferencias (
22
            id_cuenta_origen,
23
            id_cuenta_destino,
24
            monto,
25
            fecha
26
        )
27
        VALUES (origen, destino, monto, NOW());
28

29
        SELECT LAST_INSERT_ID() INTO id_transferencia;
30
        COMMIT;
31
    ELSE
32
        ROLLBACK;
33
    END IF;
34

35
END$$
36

37
DELIMITER ;

En este ejemplo, agregamos la lógica de consultar si la cuenta origen tiene saldo suficiente para realizar la transferencia en la condición de una sentencia IF. Si tiene saldo suficiente, se procede con la transferencia y se realiza un COMMIT, de lo contrario, ejecutamos un ROLLBACK para finalizar la Transacción.

La última sentencia antes del COMMIT obtiene el id del último registro insertado (en la sesión actual) y lo asigna (con SELECT ... INTO) al parámetro de salida id_transferencia.

Después de ejecutar la creación del procedimiento, en Workbench, podemos ver que se agregó el procedimiento en la sección “Stored Procedures” de la base de datos.

Posteriormente, podemos llamar al procedimiento de la siguiente forma:

1
SET @id_transferencia = NULL;
2
CALL transferencia_fondos(1246542576, 1945387487, 5000, @id_transferencia);
3
SELECT @id_transferencia; -- mostrar el valor del id

En este ejemplo, utilizamos el parámetro de salida id_transferencia, para que el procedimiento pueda devolver el id de la transferencia realizada.

Para recuperar el valor devuelto, debemos utilizar una variable desde donde invocaremos al procedimiento. Para crear una variable fuera de una función o procedimiento, utilizamos la sentencia SET seguida del nombre de la variable, con un carácter arroba @ como sufijo, y un valor asignado. En este caso NULL. Luego pasamos la variable al llamar al procedimiento, en la posición del parámetro de salida.

El procedimiento ejecutará la Transacción, y si resulta exitosa almacenará el id de la transferencia en la variable de salida, que luego podrá ser accedida desde afuera.

Connector/Python

Desde una aplicación Python podemos ejecutar un procedimiento almacenado utilizando el método callproc() de un objeto cursor, de la siguiente forma:

1
args = (1246542576, 1945387487, 5000, 0)
2
resultado = cursor.callproc("transferencia_fondos", args)
3
print(resultado[3])   # sera el valor devuelto

El método callproc() ejecuta el procedimiento almacenado y devuelve una copia modificada de los argumentos pasados. Los parámetros de entrada no se modifican, los de salida (o entrada-salida) pueden ser reemplazados por un valor nuevo. Todos los parámetros serán devueltos en el mismo orden de entrada.

En nuestro caso, el 4º parámetro es de salida. Por lo tanto, desde Python, pasamos un 0 como entrada (este valor no importará para el procedimiento). Y asignamos la salida de la ejecución del procedimiento a una variable.

La variable resultado, será una tupla con los valores de los parámetros. En su 4º posición (índice 3) tendrá el valor devuelto por el parámetro de salida del procedimiento. En las otras posiciones, tendrá los valores originales pasados como parámetros de entrada.