Oracle

Procedimientos almacenados en PL/SQL

Por : Jhons_1101
2018-11-06
Tags :

Los procedimientos almacenados en PL/SQL nos permite escribir la lógica transaccional de negocio.

Tenemos dos opciones..
Guardarlo en un fichero ".sql" para su posterior uso, o bien guardarlo en base de datos para ejecutarlo cuando se requiera ya sea internamente o por cualquier aplicación externa (API).

Un procedimiento es un subprograma que puede tomar parámetros (opcional) y ser llamado desde cualquier bloque, consulta..etc. En general, utilizamos un procedimiento para realizar una acción (transaccion).


Un procedimiento almacenado consta de dos partes: la especificación y el cuerpo.
La especificación comienza con la palabra clave PROCEDURE y termina con el nombre del procedimiento o una lista de parámetros. Las declaraciones de parámetros que son opcionales y se les debe definir su tipo de dato.

Los procedimientos que no toman parámetros se escriben sin paréntesis.
El cuerpo del procedimiento comienza con la palabra clave IS (o AS) y termina con la palabra clave END seguido del nombre del procedimiento.
compartir en facebook compartir en Google compartir en Twitter compartir en Blogger compartir como código embebido compartir la url
Para cada parámetro, se debe de especificar
  • un nombre.
  • un modo de parámetro (IN, OUT, o IN OUT).
  • Su tipo de dato.
  • Se especifica solo el tipo, no las restricciones de longitud o precisión.
  • Opcionalmente, su valor por defecto.

  • Un procedimiento se puede usar en un select, pero no en una clausula where.

    La sintaxis de un procedimiento almacenado es la siguiente:
    
    CREATE [OR REPLACE]
     PROCEDURE <nombre_del_procedimiento> [(
        <parametro1> [IN|OUT|IN OUT] <type>,
        <parametro2> [IN|OUT|IN OUT] <type>, ...
     )]
     IS
        -- Declaracion de variables locales
     BEGIN
        -- Sentencias
        [EXCEPTION]
        -- Sentencias control de excepción
     END [<nombre_del_procedimiento>];
    
    


    El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el procedimiento existe, se producirá, un error. /* Ver imagen adjunta */

    La sintáxis es muy parecida a la de un bloque anónimo, salvo porque se reemplaza la sección DECLARE por la secuencia PROCEDURE ... IS en la especificación del procedimiento.

    Error replace procedure en PL/SQL

    Veamos un ejemplo básico de un procedimiento y de cómo se hace el llamado

    
    ------------------------------------------------------------------------------------
    -- Procedimiento que inserta un cliente (Ejemplo muy sencillo)
    ------------------------------------------------------------------------------------
    CREATE PROCEDURE pr_addCliente(
        pty_codCliente   tbl_clientes.cod_cliente%TYPE,
        pty_nomCliente   tbl_clientes.nom_cliente%TYPE
    ) AS
    BEGIN
        INSERT INTO tbl_clientes(cod_cliente, nom_cliente)
        VALUES(pty_codCliente, pty_nomCliente);
    END pr_addCliente;
    
    ----------------------------------
    -- Ejemplo de cómo usarlo...
    ----------------------------------
    BEGIN
        pr_addCliente();
        COMMIT;
    END;
    
    

    En la parte ejecutable de un procedimiento debe aparecer al menos una instrucción/sentencia a ejecutar. Puede utilizar la instrucción NULL para definir que el procedimiento no hace nada.

    El no hacerlo generaría un error como el siguiente...

    Error procedimiento vacío en PL/SQL

    
    ------------------------------------------------------------------------------------
    -- Ejemplo de ERROR procedimiento vacío
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE 
    PROCEDURE pr_clienteError() IS
    BEGIN
    
    END pr_clienteError;
    --------------------------------
    -- Ejemplo de cómo usarlo...
    --------------------------------
    BEGIN
        pr_clienteError();
    END;
    
    





    Casos prácticos de uso de procedimientos almacenados


    
    ------------------------------------------------------------------------------------
    -- Ejemplo...
    -- Se define procedimiento para actualizar el salario del empleado
    -- directamente con un valor fijo.
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE 
    PROCEDURE pr_actualizaSalario(
        pty_cod_empleado    TBL_EMPLEADOS.COD_EMPLEADO%TYPE,
        pty_nuevo_salario   TBL_EMPLEADOS.VLR_SALARIO%TYPE
    )
    IS
        -- Declaracion de variables locales
    BEGIN
        -- Sentencias
        UPDATE tbl_empleados
        SET 
            VLR_SALARIO    = pty_nuevo_salario,
            FEC_CREACION   = SYSDATE
        WHERE COD_EMPLEADO = pty_cod_empleado;
    
    END pr_actualizaSalario;
    --------------------------------
    -- Ejemplo de cómo usarlo...
    --------------------------------
    BEGIN
        pr_actualizaSalario(1, 1200000);
        COMMIT;
    END;
    
    


    
    ------------------------------------------------------------------------------------
    -- Ejemplo...
    -- Se define procedimiento para actualizar el salario del empleado
    -- mediante un porcentaje de aumento
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE 
    PROCEDURE pr_aumentarSalario (
        pty_cod_empleado    TBL_EMPLEADOS.COD_EMPLEADO%TYPE,
        pty_aumentoSalario  NUMBER --Porcentaje de aumento de salario
    ) IS
    BEGIN
    
        UPDATE tbl_empleados 
        SET VLR_SALARIO = VLR_SALARIO + ((VLR_SALARIO * pty_aumentoSalario)/100) 
        WHERE COD_EMPLEADO = pty_cod_empleado;
    
    END pr_aumentarSalario;
    ---------------------------------
    -- Ejemplo de cómo usarlo...
    ---------------------------------
    -- En este caso lo hacemos en un bloque anónimo con variables definidas en el DECLARE
    DECLARE
        pty_cod_empleado     TBL_EMPLEADOS.COD_EMPLEADO%TYPE := 1;
        pty_aumentoSalario   NUMBER := 8;
    BEGIN
        pr_aumentarSalario (pty_cod_empleado, pty_aumentoSalario);
        COMMIT;
    END;
    
    


    También podemos asignar un valor por defecto a los parámetros de un procedimiento utilizando la clausula DEFAULT o usando el operador de asiganción (:=).


    
    ------------------------------------------------------------------------------------
    -- Ejemplo...
    -- Se define procedimiento para reducir el salario del empleado
    -- mediante un parámetro por defecto (DEFAULT)
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE 
    PROCEDURE pr_reducirSalario (
        pty_cod_empleado       TBL_EMPLEADOS.COD_EMPLEADO%TYPE,
        pty_porcReducSalario   NUMBER DEFAULT 5 --Porcentaje de aumento de salario
    ) IS
    --
    BEGIN
    
        UPDATE tbl_empleados 
        SET VLR_SALARIO = VLR_SALARIO - ((VLR_SALARIO * pty_porcReducSalario)/100) 
        WHERE COD_EMPLEADO = pty_cod_empleado;
    
    END pr_reducirSalario;
    ---------------------------------
    -- Ejemplo de cómo usarlo...
    ---------------------------------
    --  Ejemplo con notación posicional: Se pasan los valores de los parámetros en 
    -- el mismo orden en que el procedure los define
    BEGIN
        pr_reducirSalario(1);
        COMMIT;
    END; 
    
    -- Ejemplo con notación nominal: Se pasan los valores en cualquier orden nombrando
    -- explicitamente el parámetro.
    -- Recordemos que en notación nominal, no importa el orden de los parámetros, 
    -- siempre y cuando se especifiquen los parámetros obligatorios.
    -- De los contrario, mostrará este error...
    BEGIN
        pr_reducirSalario(pty_porcReducSalario => 10, pty_cod_empleado => 1);
        COMMIT;
    END; 
    
    

    Error notacion nominal faltan parametros en PL/SQL




    
    ------------------------------------------------------------------------------------
    -- Ejemplo...
    -- Se define procedimiento para insertar una factura y devuelve código de factura
    -- insertado para poder guardar el detalle de la factura con esa FK
    -- mediante los parámetros de salida tipo OUT
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE 
    PROCEDURE pr_insertaFactura (
        pty_num_factura IN  TBL_FACTURAS.NUM_FACTURA%TYPE,
        pty_vlr_factura IN  TBL_FACTURAS.VLR_FACTURA%TYPE,
        pty_codfactura OUT  TBL_FACTURAS.COD_FACTURA%TYPE
    ) IS
        lnu_cod_factura TBL_FACTURAS.COD_FACTURA%TYPE;
    BEGIN
    
        -- OBTENEMOS LA LLAVE PRIMARIA (COD_FACTURA) DESDE UNA SECUENCIA AUTOMÁTICA
        lnu_cod_factura := TBL_FACTURAS_SEQ.NEXTVAL;
        
        INSERT INTO TBL_FACTURAS
        VALUES (lnu_cod_factura, pty_num_factura, pty_vlr_factura);
        
        pty_codfactura := lnu_cod_factura;
    
    END pr_insertaFactura;
    ---------------------------------
    -- Ejemplo de cómo usarlo...
    ---------------------------------
    DECLARE
        pty_codfactura TBL_FACTURAS.COD_FACTURA%TYPE;
    BEGIN
        pr_insertaFactura ('FACT001', 100000, pty_codfactura);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(pty_codfactura);
    END; 
    
    

    procedimiento con parametro OUT en PL/SQL

    
    ------------------------------------------------------------------------------------
    -- Ejemplo...
    -- Se define procedimiento para insertar el detalle de la factura con el código de factura que
    -- nos retornó el procedimiento pr_insertaFactura(); el cual consta de un parametro por
    -- defecto con la semántica (:=)
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE 
    PROCEDURE pr_insertaDetalleFactura (
        pty_cod_factura    IN TBL_DETALLE_FACTURAS.COD_FACTURA%TYPE,
        pty_cod_producto   IN TBL_DETALLE_FACTURAS.COD_PRODUCTO%TYPE,
        pty_vlr_producto   IN TBL_DETALLE_FACTURAS.VLR_PRODUCTO%TYPE,
        pty_fec_creacion   IN TBL_DETALLE_FACTURAS.FEC_CREACION%TYPE := SYSDATE
    ) IS
        lnu_cod_detalle_fact TBL_DETALLE_FACTURAS.COD_FACTURA%TYPE;
    BEGIN
    
        -- OBTENEMOS LA LLAVE PRIMARIA (COD_DETALLE_FACTURA) DESDE UNA SECUENCIA AUTOMÁTICA
        lnu_cod_detalle_fact := TBL_DETALLE_FACTURA_SEQ.NEXTVAL;
        
        INSERT INTO TBL_DETALLE_FACTURAS
        VALUES (lnu_cod_detalle_fact, pty_cod_factura, pty_cod_producto, pty_vlr_producto, pty_fec_creacion);
    
    END pr_insertaDetalleFactura;
    ---------------------------------
    -- Ejemplo de cómo usarlo...
    ---------------------------------
    DECLARE
        pty_codfactura TBL_FACTURAS.COD_FACTURA%TYPE;
    BEGIN
        -- insertamos la factura
        pr_insertaFactura ('FACT001', 100000, pty_codfactura);
        COMMIT;
        
        -- Insertamos el detalle de la factura
        pr_insertaDetalleFactura (pty_codfactura, 10125, 120000);
        COMMIT;
    END; 
    
    

    procedimiento inserta detalle factura en PL/SQL


    Adicional, podemos crear procedimientos (stored procedures) que inicien y finalicen una transacción autónoma (PRAGMA AUTONOMOUS_TRANSACTION).

    
    ------------------------------------------------------------------------------------
    -- Ejemplo...
    -- PRAGMA AUTONOMOUS_TRANSACTION permite generar una nueva transacción hasta 
    -- encontrar un COMMIT y acentar los cambios que hasta el momento se hayan generado
    
    -- Es ideal para realizar logs de procesos en donde no importa si falla el
    -- proceso, siempre realizará el commit del bloque especifico. MIREMOS...
    ------------------------------------------------------------------------------------
    CREATE OR REPLACE
    PROCEDURE pr_logError(
        pty_txt_error  IN    TBL_ERRORLOG.TXT_MESSAGE%TYPE,
        pvc_object   IN TBL_ERRORLOG.OBJECTNAME%TYPE
    ) AS
            
            num_SessionID NUMBER(10);
            
            lvc_username VARCHAR2(1000);
            lvc_osuser VARCHAR2(1000);
            lvc_machine VARCHAR2(1000);
            lnuLogNumber NUMBER(15) := TBL_ERRORLOG_SEQ.NEXTVAL;
            
        PRAGMA autonomous_transaction;
    BEGIN
        
        -- -------------------------------------------------------------------------
        -- Recuperar los datos de la session
        -- -------------------------------------------------------------------------
        SELECT 
            username, osuser, machine
        INTO
            lvc_username, lvc_osuser, lvc_machine
        FROM V$session
        WHERE audsid = USERENV('SessionID');
        
        BEGIN
            -- -------------------------------------------------------------------------
            -- Inserta registro de ErrorLog
            -- -------------------------------------------------------------------------
            INSERT INTO TBL_ERRORLOG (
                COD_ERRORLOG,
                OBJECTNAME,
                USER_ORACLE,
                USER_SYSTEM,
                USER_MACHINE,
                DATE_ERRORLOG,
                TXT_MESSAGE
            ) VALUES (
                lnuLogNumber,
                NVL(pvc_object,'Out of Schema'),
                lvc_username,
                lvc_osuser,
                lvc_machine,
                SYSDATE,
                pty_txt_error
            );
    
    
            -- -------------------------------------------------------------------------
            -- Confirmar la transaccion
            -- -------------------------------------------------------------------------
            COMMIT WORK;
    
        EXCEPTION
            WHEN OTHERS THEN
                RAISE_APPLICATION_ERROR(-20600,'pr_logError() ' || '-' ||SQLERRM);
        END;
        
    END pr_logError;
    ---------------------------------
    -- Ejemplo de cómo usarlo...
    ---------------------------------
    BEGIN
        pr_logError('Falló el proceso de guardado', 'pr_logError()');
    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)

    WDzU3cT





    smithe931
    2017-03-06 02:23:18
    Thank you for the sensible critique. Me & my neighbor were just preparing to do a little research about this. We got a grab a book from our local library but I think I learned more from this post. I am very glad to see such excellent info being shared freely out there.
    smithd151
    2017-03-06 01:41:58
    Esta es la primera vez que frecuenté su página web y hasta ahora? Me sorprendió con la investigación que hizo para crear este particular publicar extraordinario. ¡Trabajo fantástico!
    Jhons_1101
    2016-06-09 08:29:16
    @SMITHE51, Por favor, no colocar span en los comentarios, esto es una comunidad web. tu comentario no es relevante para los demás.
    Please do not put spam in the comments, this is a web community. your comment is not relevant for others
    smithg614
    2016-05-29 11:10:16
    This kind of game gives a real experience of building visits

    Filas diferente color con css "tr:nth-child(odd)" y "tr:nth-child(even)


    nth child colores tr tablas



    Filas diferente color cada tres elementos "tr:nth-child(3n+0)


    nth child colores tr tablas



    Filas de diferente color, sólo los primeros tre elementos ":nth-child(-n+3)


    nth child colores tr tablas




    Unete al grupo de whatsApp +57 316 392 6456

    Sigue el grupo en facebook

    Siguenos.....