Procedimientos y Funciones en PLSQL

Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Funciones y procedimientos PL/SQL no son muy diferentes. Un procedimiento o función está constituido por un conjunto de sentencias SQL y PL/SQL lógicamente agrupados para realizar una tarea específica. Los procedimientos y funciones almacenados constituyen un bloque de código PLSQL que ha sido compilado y almacenado en las tablas del sistema de la base de datos Oracle.

Los procedimientos o funciones PLSQL son dinámicos ya que admiten parámetros que les pueden ser pasados antes de su ejecución. Por lo tanto, un procedimiento o función puede realizar diferentes tareas dependiendo de los parámetros que le hayan pasado.

Los procedimientos y funciones Oracle están compuestos por una parte en la que se definen de variables y cursores, otra parte ejecutable compuesta por sentencias SQL y PLSQL, y otra parte opcional enfocada a manejar las excepciones y errores ocurridos durante la ejecución.

Los pasos que sigue Oracle para ejecutar un procedimiento o función son los siguientes:

• Verificar si el usuario tiene permiso de ejecución.
• Verificar la validez del procedimiento o función.
• Y finalmente ejecutarlo.

Las ventajas de usar procedimientos y funciones en lugar de scripts SQL son:

• Facilidad para gestionar la seguridad.
• Mejor rendimiento al estar compilados y almacenados en la base de datos.
• Mejor gestión de la memoria.
• Mayor productividad e integridad.

La diferencia más importante entre los procedimientos y las funciones es que una función, al final de su ejecución, devuelve un valor al bloque PL/SQL que la llamó. Sin embargo, en los procedimientos esto no es posible, aunque si que podemos definir múltiples parámetros de salida que se devolverían al bloque PL/SQL desde el que se ejecutó el procedimiento (esto último también es posible en las funciones).

Sintaxis de un procedimiento PLSQL

   CREATE OR REPLACE PROCEDURE [esquema].nombre-procedim
      (nombre-parámetro {IN, OUT, IN OUT} tipo de dato, ..) {IS, AS}
         Declaración de variables;
         Declaración de constantes;
         Declaración de cursores;
         BEGIN
            Cuerpo del subprograma PL/SQL;
            EXCEPTION
            Bloque de excepciones PL/SQL;
         END;

Sintaxis de una función PLSQL

   CREATE OR REPLACE FUNCTION [esquema].nombre-funcion
      (nombre-parámetro {IN, OUT, IN OUT} tipo-de-dato, ..)
      RETURN tipo-de-dato {IS, AS}
         Declaración de variables;
         Declaración de constantes;
         Declaración de cursores;
         BEGIN
            Cuerpo del subprograma PL/SQL;
            EXCEPTION
            Bloque de excepciones PL/SQL;
         END;

Aclaraciones sobre la sintaxis

Nombre-parámetro: es el nombre que nosotros queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos podemos omitir los paréntesis.

IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.

OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.

IN OUT: Son parámetros de entrada y salida a la vez.

Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

Ejemplos prácticos

Ejemplo de creación de un procedimiento PL/SQL:

   CREATE OR REPLACE PROCEDURE
      procedimiento1 (a IN NUMBER, b IN OUT NUMBER) IS
         vmax NUMBER;
         BEGIN
            SELECT salario, maximo
            INTO b, vmax
            FROM empleados
            WHERE empleado_id=a;
            IF b < vmax THEN
               b:=b+100;
            END IF;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
               b:=-1;
               RETURN;
            WHEN OTHERS THEN
               RAISE;
         END;

Ejemplo de SQL script que llama a un procedimiento PLSQL:

   DECLARE
      vsalario NUMBER;
   BEGIN
      procedimiento1 (3213, vsalario)
      dbms_output.put_line
         ('El salario del empleado 3213 es ', vsalario);
   END;

Ejemplo de ejecución desde SQL de un procedimiento PL/SQL que sólo utiliza parámetros de entrada:

   sql> exec proc_solo_parametros_entrada (2000, 2, 'Pepe');