Procedimientos almacenados

Un procedimiento almacenado es un bloque de código con una o varias sentencias que se guarda dentro de la base de datos, con nombre propio, y que se ejecuta cuando alguien lo llama. No vive en la aplicación ni en un fichero de la máquina del programador: es un objeto más del esquema, como una tabla o una vista, y se crea, se modifica y se borra con CREATE, ALTER y DROP.

Lo que contiene es lógica de negocio escrita con la extensión procedural del lenguaje, SQL/PSM: sentencias SQL normales mezcladas con variables, condicionales y bucles. Esa es la diferencia con una consulta suelta. Una consulta describe un resultado; un procedimiento describe un procedimiento, valga la redundancia, y por eso puede encadenar varias operaciones y decidir entre ellas.

El procedimiento almacenado traslada lógica desde la aplicación hasta el servidor de base de datos. Se guarda una vez y se ejecuta donde están los datos, no donde está el programa.

Un procedimiento acepta parámetros, y cada uno se declara con la dirección en la que viajan los datos. Es una de las preguntas más repetidas del apartado.

La distinción que hay que tener clara es la que separa un procedimiento de una función. Una función devuelve un valor de retorno y por eso se puede usar dentro de una expresión, por ejemplo en la lista de columnas de un SELECT o dentro de un WHERE. Un procedimiento no devuelve valor de retorno: se invoca como una sentencia por sí misma y, si tiene que comunicar algo, lo hace por sus parámetros de salida.

La función se llama desde dentro de una consulta porque produce un valor; el procedimiento se llama en lugar de una consulta porque produce un efecto.

La sentencia estándar para ejecutar un procedimiento es CALL, seguida de su nombre y de la lista de parámetros entre paréntesis, separados por comas. En Transact-SQL se usa además EXEC o EXECUTE, con los parámetros separados por comas y sin paréntesis obligatorios.

-- Estándar
CALL registrar_intento(1, 7, 18, @resultado);

-- Transact-SQL
EXEC registrar_intento 1, 7, 18, @resultado OUTPUT;

Para poder ejecutarlo hace falta el privilegio EXECUTE sobre el procedimiento, que se concede con GRANT igual que cualquier otro. La clasificación de CALL da lugar a preguntas ambiguas: hay fabricantes que la cuentan como DML y otros que la sitúan en el DCL.

• Menos tráfico de red: se envía una sola llamada en lugar de una decena de sentencias, y el resultado intermedio no viaja de ida y vuelta.
• Rendimiento: el gestor guarda el procedimiento ya analizado, de modo que se ahorra el análisis y buena parte de la planificación en cada ejecución.
• Reutilización y consistencia: la misma regla de negocio la aplican todas las aplicaciones que atacan a la base, sin depender de que cada una la reimplemente igual.
• Seguridad: se puede conceder EXECUTE sobre el procedimiento sin conceder ningún permiso sobre las tablas que toca, de manera que el usuario solo puede hacer con los datos lo que el procedimiento le deje hacer.
• Defensa frente a la inyección de SQL, siempre que los datos entren como parámetros y no se concatenen para formar sentencias dentro del propio procedimiento.

El precio principal es la portabilidad. El SQL declarativo es razonablemente común entre gestores, pero la extensión procedural no: un cuerpo escrito en PL/SQL no se lleva a Transact-SQL sin reescribirlo. A eso se añade que la lógica repartida entre la aplicación y la base es más difícil de versionar, de probar y de depurar que la que vive en un solo sitio.

En la creación se declaran los parámetros con su modo y su tipo, el lenguaje en que está escrito el cuerpo y, entre delimitadores, el bloque BEGIN y END con las sentencias. Dentro se pueden usar consultas, asignaciones a variables y estructuras de control como IF o CASE, y confirmar la transacción al final.

CREATE PROCEDURE registrar_intento (
  IN  p_alumno    INTEGER,
  IN  p_tema      INTEGER,
  IN  p_aciertos  SMALLINT,
  OUT p_resultado VARCHAR(20)
)
LANGUAGE SQL
AS $$
BEGIN
  INSERT INTO intento (id_alumno, id_tema, aciertos, fallos, realizado)
  VALUES (p_alumno, p_tema, p_aciertos, 20 - p_aciertos, CURRENT_TIMESTAMP);

  IF p_aciertos >= 12 THEN
    SET p_resultado = 'superado';
  ELSE
    SET p_resultado = 'pendiente';
  END IF;

  COMMIT;
END;
$$;

La sintaxis exacta cambia bastante de un gestor a otro: los delimitadores del cuerpo, la forma de asignar a una variable y hasta la cláusula del lenguaje son distintos en Oracle, en SQL Server y en PostgreSQL. Lo que no cambia es el esqueleto: cabecera con parámetros, cuerpo entre BEGIN y END, y control de flujo dentro.

Un cursor permite recorrer fila a fila lo que devuelve una consulta, en lugar de tratarlo como un conjunto entero. Es el puente entre el mundo declarativo del SELECT y el mundo procedural del cuerpo del procedimiento, donde a veces hace falta hacer algo distinto con cada fila.

DECLARE c_flojos CURSOR FOR
  SELECT id_alumno, id_tema
  FROM   intento
  WHERE  aciertos < 10
  FOR UPDATE;

DECLARE v_fila c_flojos%ROWTYPE;

OPEN c_flojos;
  FETCH c_flojos INTO v_fila;
  -- ... tratamiento de v_fila, y siguiente FETCH
CLOSE c_flojos;

1. 1DECLARE asocia el cursor a una consulta. Si lleva FOR UPDATE, el gestor bloquea las filas recorridas para poder modificarlas sin que nadie se interponga.
2. 2OPEN ejecuta la consulta y deja el cursor situado antes de la primera fila.
3. 3FETCH avanza una fila y vuelca sus columnas en variables. La declaración con %ROWTYPE crea una variable con la misma estructura que devuelve el cursor, y evita tener que enumerar las columnas.
4. 4CLOSE libera el cursor y los recursos asociados.

El concepto no es exclusivo del interior del gestor: el ResultSet de JDBC y el DataReader de .NET son cursores por debajo, y se manejan con la misma secuencia de abrir, avanzar y cerrar. Conviene recordar además que recorrer fila a fila lo que se podría resolver con una sola sentencia de conjunto suele salir mucho más caro.