Crear, Ejecutar y eliminar una función en Oracle

Las funciones, como los procedimientos almacenados son bloques de código que permiten agrupar y organizar sentencias SQL que se ejecutan al invocar la función.

Para empezar este tutorial de Oracle explica cómo crear y colocar funciones en Oracle / PLSQL con sintaxis y ejemplos.

Paso 1.- Crear función

Al igual que en otros lenguajes de programación, puede crear sus propias funciones en Oracle.

Sintaxis

La sintaxis para crear una función en Oracle es:

CREATE [OR REPLACE] FUNCTION NOMBRE_FUNCION
[(param1 |IN|OUT|IN OUT| TIPO_DATO,
param2 |IN|OUT|IN OUT| TIPO_DATO ), … ]
RETURN TIPO_DATO
IS | AS
--Declaración de variables locales
BEGIN
--Declaración de Sentencia
RETURN DATO;
[EXCEPTION TIPO_EXCEPCION]
--Sentencias de control de excepción
END [NOMBRE_FUNCION];

CREATE [OR REPLACE] FUNCTION NOMBRE_FUNCION

[(param1 |IN|OUT|IN OUT| TIPO_DATO,

param2 |IN|OUT|IN OUT| TIPO_DATO ), … ]

RETURN TIPO_DATO

IS | AS

--Declaración de variables locales

BEGIN

--Declaración de Sentencia

RETURN DATO;

[EXCEPTION TIPO_EXCEPCION]

--Sentencias de control de excepción

END [NOMBRE_FUNCION];

Cuando crea un procedimiento o una función, puede definir parámetros. Además hay tres tipos de parámetros que se pueden declarar:
1. IN – En cuanto a al parámetro puede ser referenciado por el procedimiento o la función. El valor del parámetro no puede ser sobrescrito por el procedimiento o la función.
2. OUT – El parámetro no puede ser referenciado por el procedimiento o la función, pero el valor del parámetro puede ser sobrescrito por el procedimiento o la función.
3. IN OUT – El parámetro puede ser referenciado por el procedimiento o función y el valor del parámetro puede ser sobrescrito por el procedimiento o la función.

Ejemplo

Veamos un ejemplo de cómo crear una función en Oracle.

CREATE OR REPLACE FUNCTION F_OBT_NOMBRECURSO(P_CODIGOCURSO IN CURSO.CODIGOCURSO%TYPE)
  RETURN VARCHAR2 IS
  VNOMBRE CURSO.NOMBRE%TYPE;
BEGIN
  BEGIN
    SELECT NOMBRE
      INTO VNOMBRE
      FROM CURSO
     WHERE CODIGOCURSO = P_CODIGOCURSO;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'OCURRIO UN ERROR AL OBTENER EL NOMBRE DEL CURSO - ' ||
                              SQLCODE || ' -ERROR- ' || SQLERRM);
  END;
  RETURN VNOMBRE;
END F_OBT_NOMBRECURSO;

CREATE OR REPLACE FUNCTION F_OBT_NOMBRECURSO(P_CODIGOCURSO IN CURSO.CODIGOCURSO%TYPE)

RETURN VARCHAR2 IS

VNOMBRE CURSO.NOMBRE%TYPE;

BEGIN

SELECT NOMBRE

INTO VNOMBRE

FROM CURSO

WHERE CODIGOCURSO = P_CODIGOCURSO;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001,

'OCURRIO UN ERROR AL OBTENER EL NOMBRE DEL CURSO - ' ||

SQLCODE || ' -ERROR- ' || SQLERRM);

END;

RETURN VNOMBRE;

END F_OBT_NOMBRECURSO;

Esta función se llama F_OBT_NOMBRECURSO. Tiene un parámetro llamado P_CODIGO de tipo IN y devuelve una cadena. por lo tanto La función devolverá el nombre del curso según el código del curso. Por otro lado mostrara un mensaje de error.

Paso 2.- Utilizar una función.

Las funciones pueden ser utilizadas dentro de sentencias SQL, además de poder ser usadas en bloques anónimos, procedimientos u otras funciones

SELECT NUMEROCREDITO,
       F_OBT_NOMBRECURSO(CODIGOCURSO) AS NOMBRECURSO
  FROM MATRICULA
 WHERE CODIGOALUMNO = '0909141003';

SELECT NUMEROCREDITO,

F_OBT_NOMBRECURSO(CODIGOCURSO) AS NOMBRECURSO

FROM MATRICULA

WHERE CODIGOALUMNO = '0909141003';

Paso 3.- Eliminar una función

Una vez que haya creado su función en Oracle, es posible que deba eliminarla de la base de datos.

Sintaxis

La sintaxis para eliminar una función en Oracle es:

DROP FUNCTION NOMBRE_FUNCION;

1	DROP FUNCTION NOMBRE_FUNCION;

NOMBRE_FUNCION.– Es el nombre de la función que desea eliminar.

Ejemplo.

Veamos un ejemplo de cómo eliminar una función en Oracle.

DROP FUNCTION F_OBT_NOMBRECURSO;

1	DROP FUNCTION F_OBT_NOMBRECURSO;

En conclusión

Finalmente para concluir, 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.