Capítulo 7 - Funciones y Funciones Almacenadas

Las funciones guardan mucha similitud con los procedimientos, la diferencia es que una función debe devolver un valor propio de la función, la cual puede ser asignada a una variable o función. Debido a  este mecanismo las funciones pueden ser utilizadas dentro de sentencias SQL, además de poder ser usadas en bloques anónimos, procedimientos u otras funciones.

DECLARACIÓN DE FUNCIONES

Antes de mostrar un ejemplo de función, veamos primero la sintaxis de una:

FUNCTION <NOMBRE_FUNCION> (<PARAMETROS>)
RETURN <TIPO_DATO_RETORNO>
IS
  <VARIABLES>
BEGIN
  <CODIGO PL/SQL>
END;

Dónde:

• <NOMBRE_FUNCION>: Es el nombre con el que se podrá identificar a la función
• <PARAMETROS>: Los parámetros que usará la función como datos de entrada.
• <TIPO_DATO_RETORNO>: Se debe indicar el tipo de dato que va a devolver la función.
• <VARIABLES>: Variables que necesitemos para realizar operaciones en la función. Éstas variables serán de uso esclusivo de la función.
• <CODIGO PL/SQL>: Código PL/SQL que ejecutará la función cada vez que sea llamada.

Veámos un ejemplo:

DECLARE
  --Declaramos las variables de nuestro bloque anónimo.
  V_VAR_SUMA        NUMBER;
 
  --Declaramos la función SUMAR_NUMEROS
  FUNCTION SUMAR_NUMEROS(P_NUMEROA   NUMBER,
                         P_NUMEROB   NUMBER)
  --El tipo de dato que devolverá la función es NUMBER
  RETURN NUMBER
  IS
    --Creamos una variable para almacenar el valor de la operación.
    V_RESULTADO   NUMBER;
  BEGIN
    -- Operamos
    V_RESULTADO := P_NUMEROA + P_NUMEROB;
    
    -- Y el valor de V_RESULTADO será devuelto como el valor de la función.
    RETURN V_RESULTADO;
  END;

BEGIN
  -- Usamos la función directamente.
  DBMS_OUTPUT.PUT_LINE('La suma es: ' || SUMAR_NUMEROS(10,80) );
 
  --Almacenamos el valor de la función en una variable.
  --para imprimirlo posteriormente.
  V_VAR_SUMA := SUMAR_NUMEROS(100,50);
  DBMS_OUTPUT.PUT_LINE('La suma es: ' || V_VAR_SUMA);
END;

La suma es: 90
La suma es: 150

Como se puede observar, la función SUMAR_NUMEROS es pasada como un parámetro para la función DBMS_OUTPUT.PUT_LINE o se puede guardar el valor de retorno de la función en una variable. Esto es posible debido a que la función SUMAR_NUMEROS devuelve un valor del tipo NUMBER.

FUNCIONES ALMACENADAS

Al igual que en los procedimientos, las funciones también pueden ser almacenadas en la base de datos. Para almacenar una función en la base de datos sólo debemos adicionar la palabra CREATE a la declaración de la función.

--Declaración de la función
CREATE OR REPLACE FUNCTION HR.SUMAR_NUMEROS(P_NUMEROA   NUMBER,
                       P_NUMEROB   NUMBER)
--El tipo de dato que devolverá la función es NUMBER
RETURN NUMBER
IS
  --Creamos una variable para almacenar el valor de la operación.
  V_RESULTADO   NUMBER;
BEGIN
  -- Operamos
  V_RESULTADO := P_NUMEROA + P_NUMEROB;
  
  -- Y el valor de V_RESULTADO será devuelto como el valor de la función.
  RETURN V_RESULTADO;
END;

Al crear una función almacenada, SQL Developer nos mostrará el siguiente mensaje:

plsql_c7_funcion_compilado.png

Las funciones almacenadas pueden ser utilizadas en bloques anónimos:

DECLARE
 V_VARIABLE  NUMBER;
BEGIN
  --Almacenamos el valor de la función almacenada
  --en una variable para imprimirlo posteriormente.
  V_VARIABLE := HR.SUMAR_NUMEROS(300,50);
  
  --Mostramos el resultado de la operación
  DBMS_OUTPUT.PUT_LINE('La suma es: ' || V_VARIABLE);
END;

La suma es: 350

Adicionalmente, las funciones pueden ser usadas en sentencias SQL, veamos un ejemplo para sumar los campos min_salary y max_salary de la tabla hr.jobs:

-- Usamos la función HR.SUMAR_NUMEROS, para realizar
-- una suma con los campos min_salary y max_salary
-- de la tabla hr.jobs
SELECT job_id,
       job_title,
       min_salary,
       max_salary,
       HR.SUMAR_NUMEROS(min_salary,max_salary) as SUMA
  FROM hr.jobs;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY       SUMA
---------- ----------------------------------- ---------- ---------- ----------
AD_PRES    President                                20080      40000      60080
AD_VP      Administration Vice President            15000      30000      45000
AD_ASST    Administration Assistant                  3000       6000       9000
FI_MGR     Finance Manager                           8200      16000      24200
FI_ACCOUNT Accountant                                4200       9000      13200
AC_MGR     Accounting Manager                        8200      16000      24200
AC_ACCOUNT Public Accountant                         4200       9000      13200
SA_MAN     Sales Manager                            10000      20080      30080
SA_REP     Sales Representative                      6000      12008      18008
PU_MAN     Purchasing Manager                        8000      15000      23000
PU_CLERK   Purchasing Clerk                          2500       5500       8000

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY       SUMA
---------- ----------------------------------- ---------- ---------- ----------
ST_MAN     Stock Manager                             5500       8500      14000
ST_CLERK   Stock Clerk                               2008       5000       7008
SH_CLERK   Shipping Clerk                            2500       5500       8000
IT_PROG    Programmer                                4000      10000      14000
MK_MAN     Marketing Manager                         9000      15000      24000
MK_REP     Marketing Representative                  4000       9000      13000
HR_REP     Human Resources Representative            4000       9000      13000
PR_REP     Public Relations Representative           4500      10500      15000