Oracle

Subprogramas en PL/SQL

Por : Jhons_1101
2018-08-15
Tags :
Oracle, PL/SQL nos permite crear conjuntos de declaraciones en subprogramas reutilizables. Los subprogramas son como bloques de construcción para aplicaciones modulares y mantenibles. Denominados bloques PL/SQL que pueden llamar con un conjunto de parámetros o no.

PL/SQL cuenta con dos tipos de subprogramas: Los procedimientos y las funciones. Generalmente, se utiliza un procedimiento para realizar una acción de transacción (INSERT, UPDATE, DELETE) y una función para calcular un valor o similar (SELECTS).

compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url

Similar a los bloques anónimos, los subprogramas tienen:

* Una parte declarativa, con declaraciones de tipos de dato, cursores, constantes, variables, excepciones y subprogramas anidados. Estos elementos son locales y dejan de existir cuando finaliza el subprograma.

* Una parte ejecutable, con sentencias que asignan valores, calculan datos, controlan la ejecución y/o manipulan los datos de la base de datos Oracle.

* Una parte opcional de manejo de excepciones, que se encarga de las condiciones de error en tiempo de ejecución del subprograma.


-- Ejemplo Procedimiento PL/SQL INSERT
DECLARE

    lnu_cod_pais      PAIS.COD_PAIS%TYPE       := 1;
    lvc_nom_pais      PAIS.NOM_PAIS%TYPE       := 'Estados Unidos';
    lnu_codContinente PAIS.COD_CONTINENTE%TYPE := 1;

    PROCEDURE pr_insertPais (
        pty_cod_pais      PAIS.COD_PAIS%TYPE,
        pty_nom_pais      PAIS.NOM_PAIS%TYPE,
        pty_codContinente PAIS.COD_CONTINENTE%TYPE
    ) AS
    BEGIN
        BEGIN
            INSERT INTO PAIS VALUES (
                pty_cod_pais,
                UPPER(pty_nom_pais),
                pty_codContinente
            );
            COMMIT;
        EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error al insertar el PAIS.' || SQLERRM);
        END;
    END;
BEGIN
    pr_insertPais(lnu_cod_pais, lvc_nom_pais,lnu_codContinente);
END;


-- Ejemplo Función PL/SQL cursor implícito

DECLARE

    FUNCTION paisPorCiudad(
        pty_codCiudad CIUDAD.COD_CIUDAD%TYPE
    )
    RETURN NUMBER AS 
        lnu_cod_pais PAIS.COD_PAIS%TYPE;
    BEGIN
        SELECT COD_PAIS INTO lnu_cod_pais 
        FROM CIUDAD 
        WHERE COD_CIUDAD = pty_codCiudad;
      RETURN lnu_cod_pais;
    END;

BEGIN
    DBMS_OUTPUT.PUT_LINE(paisPorCiudad(76001));
END;

/*
    ----------------------------------- 
    -- SALIDA 
    ----------------------------------- 
        57
*/


ejemplo cursor dinámico en PL/SQL


Ventajas


* Los subprogramas le permiten extender el lenguaje PL/SQL. Los procedimientos actúan como nuevas declaraciones. Las funciones actúan como nuevas expresiones y operadores.

* Los subprogramas le permiten dividir un programa en módulos manejables y bien definidos.

* Los subprogramas promueven la reutilización. Una vez probado, un subprograma se puede reutilizar en cualquier número de aplicaciones.

* Se puede llamar los subprogramas PL/SQL desde muchos entornos diferentes.

* Los subprogramas promueven la mantenibilidad. Se puede cambiar las partes internas de un subprograma sin cambiar otros subprogramas que lo llaman o consuman.

* Cuando utiliza subprogramas de PL/SQL se puede hacer que su código sea aún más reutilizable y mantenible agrupando los subprogramas en un paquete de PL/SQL, Esto lo veremos un poco más adelante..

Los subprogramas desempeñan un papel importante en otras funciones de mantenimiento, como paquetes y tipos de objetos.


Subprogramas anidados PL/SQL


* Puede declarar subprogramas en cualquier bloque, subprograma o paquete PL/SQL. Los subprogramas deben ir al final de la sección declarativa, después de todos los demás elementos.

* Debe declarar un subprograma antes de llamarlo. Este requisito puede dificultar la declaración de varios subprogramas anidados que se llaman entre sí.

* Puede declarar subprogramas anidados interrelacionados mediante una declaración de reenvío: una especificación de subprograma terminada por un punto y coma, sin cuerpo (body).

* Aunque la lista de parámetros formal aparece en la declaración directa, también debe aparecer en el cuerpo/body del subprograma.
Puede colocar el cuerpo del subprograma en cualquier lugar después de la declaración, pero deben aparecer en la misma unidad de programa.


------------------------------------------------------------------------------------
-- Declaración directa para un subprograma anidado
------------------------------------------------------------------------------------
DECLARE

    PROCEDURE proc1(number1 NUMBER);  -- forward declaration
    
    PROCEDURE proc2(number2 NUMBER) IS
    BEGIN
        proc1(number2);  -- calls proc1
    END; 

    PROCEDURE proc1(number1 NUMBER) IS 
    BEGIN
        proc2 (number1);  -- calls proc2
    END;
BEGIN
    NULL;
END;


Los subprogramas al igual que los paquetes PL/SQL pueden recibir parámetros, ya sean de tipo columna o de tipo especifico.


Parámetros en subprogramas PL/SQL


Existen cuatro (4) maneras de pasar parámetros a los subprogramas, los cuales permite hacer dinámico el subprograma para que se pueda reutilizar y facilitar el mantenimiento.

  • Parámetros formales Vs reales
  • Uso de la notación posicional, nombrada o mixta para los parámetros del subprograma
  • Modos de Parámetro de Subprograma
  • Uso de valores predeterminados (Por defecto) para parámetros de subprograma


  • Parámetros formales Vs reales


    Los subprogramas pasan información utilizando parámetros:
  • Las variables declaradas en una especificación de subprograma y referenciadas en el cuerpo del subprograma son parámetros formales.
  • Las variables o expresiones pasadas desde el subprograma llamante son parámetros reales.


  • Es una buena práctica usar diferentes nombres y definir una estructura o convención para los parámetros reales y formales.

    Cuando se llama a un procedimiento, los parámetros reales se evalúan y los resultados se asignan a los parámetros formales correspondientes. Si es necesario, antes de asignar un valor a un parámetro real a un parámetro formal, PL/SQL convierte el tipo de dato.

    Por ejemplo, si pasa un número cuando el procedimiento espera una cadena, PL/SQL convierte este parámetro para que el procedimiento reciba una cadena.

    El parámetro real y su parámetro formal correspondiente deben tener tipos de datos compatibles.

    Por ejemplo, PL/SQL no puede convertir entre los tipos de datos DATEy NUMBER, o convertir una cadena en un número si la cadena contiene caracteres adicionales, como signos de dólar.

    
    ------------------------------------------------------------------------------------
    -- Este procedimiento (pr_aumentarSalario) declara dos parámetros formales de entrada
    -- con nombre (codEmpleado, AumentoSalario) y el llamado procedimiento especifica 
    -- parámetros reales (cod_empleado y aumento_sal)
    ------------------------------------------------------------------------------------
    DECLARE
        cod_empleado NUMBER(6) := 120;
        aumento_sal  NUMBER(2) := 7;
        bonificación NUMBER(2) := 1;
        
        PROCEDURE pr_aumentarSalario (
            codEmpleado    NUMBER, 
            AumentoSalario NUMBER
        ) IS
        BEGIN
            UPDATE tbl_empleados 
            SET salario = salario + ((salario * AumentoSalario)/100) 
            WHERE cod_empleado = codEmpleado;
            COMMIT;
    
        END pr_aumentarSalario;
    BEGIN
        ------------------------------------------------------------------------------------
        -- Hacemos el llamado al procedimiento.
        ------------------------------------------------------------------------------------
        -- llamado al procedimeinto con parámetros reales
        pr_aumentarSalario(cod_empleado, aumento_sal);
    
        -- Se pueden usar expresiones en el paso de parámetros, siempre y cuando
        -- estén bien estructurados.
        pr_aumentarSalario(cod_empleado, (bonificación + aumento_sal));
    END;
    
    



    Parámetros con notacion posicional, nombrada o mixta


    Al llamar a un subprograma, puede escribir los parámetros reales utilizando:

  • Notación posicional. Especifique los mismos parámetros en el mismo orden en que se declaran en el procedimiento.
  • Esta notación es compacta, pero si mandan los parámetros en el orden incorrecto, el error puede ser difícil de detectar. Debe cambiar su código si cambia la lista de parámetros del procedimiento. (No es recomendable)

  • Notación nombrada. Especifique el nombre de cada parámetro junto con su valor. Una flecha (=>) sirve como operador de la asociación. El orden de los parámetros no es importante.
  • Esta notación es más detallada, pero hace que su código sea más fácil de leer y mantener.
    En ocasiones, puede evitar cambiar su código si cambia la lista de parámetros del procedimiento, por ejemplo, si los parámetros se reordenan o si se agrega un nuevo parámetro opcional.


    La notación con nombre es una buena práctica para usar con cualquier código que llame a la API de otra persona, o que defina una API para que otra persona la use.


  • Notación mixta. Se debe de especificar los primeros parámetros con notación posicional (obligatorios), luego cambia a notación nombrada para los últimos parámetros (opcionales).

  • 
    DECLARE
        cod_empleado NUMBER(6) := 120;
        aumento_sal  NUMBER(2) := 7;
        bonificación NUMBER(2) := 1;
        
        PROCEDURE pr_aumentarSalario (
            codEmpleado    NUMBER, 
            AumentoSalario NUMBER
        ) IS
        BEGIN
            UPDATE tbl_empleados 
            SET salario = salario + ((salario * AumentoSalario)/100) 
            WHERE cod_empleado = codEmpleado;
            COMMIT;
    
        END pr_aumentarSalario;
    BEGIN
        ------------------------------------------------------------------------------------
        -- Hacemos el llamado al procedimiento.
        ------------------------------------------------------------------------------------
        -- llamado al procedimiento con parámetros reales y notación posicional
        pr_aumentarSalario(cod_empleado, aumento_sal);
    
        -- llamado al procedimiento con parámetros reales y notación nombrada
        pr_aumentarSalario(aumento_sal => aumento_sal, cod_empleado => cod_empleado);
    
        -- llamado al procedimiento con parámetros reales y notación mixta
        pr_aumentarSalario(cod_empleado, aumento_sal => aumento_sal);
    END;
    
    


    Modos de Parámetro de Subprograma


    Se debe definir el comportamiento de los parámetros formales. Los tres modos de parámetros son IN (el predeterminado) OUT, e IN OUT.

    Se puede usar cualquier modo de parámetro en los subprograma, o una combinación de ellos.

    Evite utilizar los modos OUT e IN OUT con funciones (FUNCTION). Hacer que una función devuelva varios valores es una mala práctica de programación.



  • Usando el modo/tipo IN
  • Un parámetro IN permite pasar valores al subprograma al que se llama. Dentro del subprograma, un parámetro IN actúa como una constante. No se le puede asignar un valor.

    Se le puede pasar una constante, un literal, un variable inicializada o unaexpresión como un parámetro de entrada.

    los parámetros IN pueden inicializarse a los valores predeterminados (por defecto).



  • Usando el modo/tipo OUT
  • Un parámetro OUT devuelve un valor a la persona/recurso que llama al subprograma.

    un parámetro OUT actúa como una variable. Puede cambiar su valor y hacer referencia al valor después de asignarlo.

    En este caso, se le debe pasar una variable, no una constante o una expresión.
    Al igual que las variables, los parámetros OUT formales se inicializan en NULL.

    Antes de salir de un subprograma, asigne valores a todos los parámetros OUT formales. De lo contrario, los parámetros reales correspondientes serán nulos.



  • Usando el modo/tipo IN OUT
  • Un parámetro IN OUT pasa los valores iniciales a un subprograma y devuelve los valores actualizados a la persona/recurso que lo llama.

    Se le puede asignar un valor y su valor se puede leer. Normalmente, un parámetro IN OUT es un búfer de cadena o un acumulador numérico, que se lee dentro del subprograma y luego se actualiza en el tiempo.

    Un parámetro IN OUT debe ser una variable; no puede ser una constante o una expresión.


    
    DECLARE
        cod_empleado  NUMBER(6) := 120;
        aumento_sal   NUMBER(2) := 7;
        bonificación  NUMBER(2) := 1;
        nuevo_salario OUT TBL_EMPLEADOS.SALARIO%TYPE;
        
        PROCEDURE pr_aumentarSalario (
            codEmpleado    IN  NUMBER, 
            AumentoSalario IN  NUMBER,
            salAumentado   OUT MUMBER
        ) IS
        lnu_sal_aumentado NUMBER (20);
        lnu_salario_emp   TBL_EMPLEADOS.SALARIO%TYPE;
        BEGIN
    
            SELECT SALARIO INTO lnu_salario_emp
            FROM tbl_empleados WHERE COD_EMPLEADO = codEmpleado;
    
            lnu_sal_aumentado := lnu_salario_emp + ((lnu_salario_emp * AumentoSalario)/100) ;
    
            UPDATE tbl_empleados  SET salario = lnu_sal_aumentado
            WHERE cod_empleado = codEmpleado;
            COMMIT;
    
            -- Retornamos el nuevo salario
            salAumentado := lnu_sal_aumentado;
    
        END pr_aumentarSalario;
    BEGIN
        pr_aumentarSalario(cod_empleado, aumento_sal, nuevo_salario); 
        DBMS_OUTPUT.PUT_LINE('El nuevo salario del empleado es : ' || nuevo_salario);
    END;
    
    



    Uso de valores predeterminados (por Defecto)


    Se pueden inicializar los parámetros IN con valores predeterminados, estos, puede pasar diferentes números de parámetros reales a un subprograma, aceptando los valores predeterminados cada tipo de dato.

    Si se omite un parámetro en el llamado, se utilizará el valor predeterminado correspondiente.

    No puede omitir un parámetro formal. Para omitir el primer parámetro y especificar el segundo, use la el modo "notación nombrada".

    Esto es importante para poder construir API's o conectarse a recursos externos.

    
    DECLARE
        cod_empleado NUMBER(6) := 120;
        aumento_sal  NUMBER(2) := 7;
        
        PROCEDURE pr_aumentarSalario (
            codEmpleado    NUMBER, 
            AumentoSalario NUMBER DEFAULT 5
        ) IS
        BEGIN
            UPDATE tbl_empleados 
            SET salario = salario + ((salario * AumentoSalario)/100) 
            WHERE cod_empleado = codEmpleado;
            COMMIT;
    
        END pr_aumentarSalario;
    BEGIN
        -- Recibiría los siguientes parámetros  pr_aumentarSalario(120, 5)
        pr_aumentarSalario(cod_empleado);
    
        -- Recibiría los siguientes parámetros  pr_aumentarSalario(120, 7)
        pr_aumentarSalario(cod_empleado, AumentoSalario => 7);
    END;
    
    





    Si este contenido te fue útil, no olvides compartirlo en redes sociales, Considéralo. Puede ser la manera de agradecer!


    Agrega tu comentario...

    Puedes utilizar etiquetas <pre></pre>, <p></p>, <div></div>, + (Nombre usuario, para responderle a alguien)

    iKwI5eJ





    Este post no tiene comentarios, sé el primero en hacerlo
    Esta entrada no cuenta con imágenes adjuntas

    Unete al grupo de whatsApp +57 316 392 6456

    Sigue el grupo en facebook

    Siguenos.....