Funciones y procedimientos en PL/SQL

Los procedimientos almacenados y las funciones son objetos de base de datos que pueden compilarse y almacenarse en la base de datos, y quedan ahí listos para ser llamados y ejecutados cuando sea necesario.

Una vez compilado el procedimiento este queda como un objeto de esquema conocido como procedimiento almacenado o función almacenada, que puede ser referenciado o llamado cualquier número de veces por las aplicaciones conectadas a la base de datos.

Los procedimientos y las funciones pueden aceptar parámetros cuando son llamados. Para ejecutar un procedimiento o función almacenada, sólo es necesario incluir su nombre de objeto.

¿Dónde podemos crear funciones y procedimientos?

En el esquema que estemos trabajando: cuando se crea el procedimiento en el esquema es un subprograma totalmente independiente. La ventaja de esto es que lo podremos llamar cuando lo necesitemos y le podremos pasar parámetros de ser necesario. Para crearlo usamos la sentencia CREATE PROCEDURE o CREATE FUNCTION. Se almacena en la base de datos como un objeto más de ese esquema. Tenemos la posibilidad de eliminarlo con la sentencia DROP PROCEDURE o DROP FUNCTION.

Dentro de un bloque de código PL/SQL: tenemos la posibilidad de crear funciones y procedimientos en los bloques anónimos, pero solo estarán disponibles dentro de ese contexto.

Formando parte de un Package: un procedimiento o función que creemos dentro de un paquete es un subprograma empaquetado. En este caso también se almacena directamente en la base de datos, pero en el contexto del paquete. Lo podremos crear al crear el package y lo podremos eliminar usando la sentencia DROP PACKAGE.

Partes de las funciones y procedimientos

Sección de declaración: Es una parte opcional. Contiene declaraciones de tipos, cursores, constantes, variables, excepciones y subprogramas anidados.

Sección ejecutable: esta sección si es obligatoria y es donde está contenido la parte del programa que se ejecuta.

Manejo de errores: parte opcional en la que colocamos el manejo de los posibles errores que se presenten a tiempo de ejecución.

Vamos a crear un procedimiento en el que busquemos a un empleado por su campo EMPNO y se determine si el salario es menor que mil vamos a aplicar un aumento de salario que especifiquemos como parámetro.

CREATE OR REPLACE PROCEDURE AUMENTO_SALARIO (LV_EMPNO IN NUMBER, BONO IN NUMBER)
        AS
        EMP_COMM EMP.COMM%TYPE;
        EMP_SAL EMP.SAL%TYPE;
        FALTA_SALARIO EXCEPTION;
    BEGIN
        SELECT SAL, COMM INTO EMP_SAL, EMP_COMM FROM EMP
        WHERE EMPNO = LV_EMPNO;
        IF EMP_SAL IS NULL THEN
            RAISE FALTA_SALARIO;
        ELSIF EMP_SAL > 1000  THEN
            DBMS_OUTPUT.PUT_LINE('El empleado ' || LV_EMPNO || ' Gana más de salario mínimo, además ya tiene bono' ); 
        ELSIF EMP_SAL < 1000 AND EMP_COMM IS  NULL THEN
            DBMS_OUTPUT.PUT_LINE('El empleado ' || LV_EMPNO || ' Gana muy poco dinero y necesita un aumento');  
            UPDATE EMP 
            SET SAL = EMP_SAL + (EMP_SAL * BONO)
                WHERE EMPNO = LV_EMPNO;    
            SELECT SAL, COMM INTO EMP_SAL, EMP_COMM FROM EMP
            WHERE EMPNO = LV_EMPNO;
            DBMS_OUTPUT.PUT_LINE('El empleado ' || LV_EMPNO || 'ahora se le aumentó el sueldo ' || EMP_SAL);          
        END IF;
    EXCEPTION 
        WHEN FALTA_SALARIO THEN
            DBMS_OUTPUT.PUT_LINE('El empleado ' || LV_EMPNO || ' sin actualizacion');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Ha ocurrido un error');
    END AUMENTO_SALARIO;  

Resultado:

Procedure created.

0.01 seconds

Ahora podemos llamar ese procedimiento que ya se encuentra almacenado como un objeto de esquema de la base de datos. Usaremos el nombre del procedimiento y le pasaremos dos valores el primero es el número de empleado y el segundo será el aumento de salario que daremos si dicho empleado gana menos de 1000

BEGIN
        AUMENTO_SALARIO(7900, 0.10);
    END;

Resultado:

El empleado 7900 Gana muy poco dinero y necesita un aumento

El empleado 7900ahora se le aumentó el sueldo 1064.8

0.01 seconds

Creando una función almacenada

Ahora vamos a acrear una función almacenada muy sencilla. Lo único que vamos a hacer con la función es buscar a un empleado enviando su id, si la aplicación lo consigue vamos a mostrar su nombre en pantalla, por el contrario, si el empleado no existe vamos a manejar el error con una excepción

CREATE OR REPLACE FUNCTION buscar_empleado (empid NUMBER)
        RETURN VARCHAR2 IS
            lv_name emp.ename%TYPE; 
            lv_job emp.job%TYPE; 
    BEGIN
        SELECT ename, job INTO lv_name, lv_job FROM emp
        WHERE empno = empid;
        RETURN ( 'Employee: ' || empid || ' - ' || UPPER(lv_name) || ', ' || UPPER(lv_job) ); --este es el retorno de la función
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            RETURN('El empleado  no existe');--este es el retorno de la función
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Ha ocurrido un error');
    END buscar_empleado;

Resultado:

Function created.

0.01 seconds

Una vez creada la función lo que tenemos que hacer ahora es llamarla por su nombre y enviar el valor del id del empleado

BEGIN
        DBMS_OUTPUT.PUT_LINE(buscar_empleado(1) );
    END;

Resultado:

Employee: 7788 - SCOTT, ANALYST

0.01 seconds