Procedimientos almacenados, INSERT SELECT , UPDATE y MERGE con Oracle

Esto es un ejemplo de un procedimiento almacenado de Oracle con operaciones genéricas de creación del procedimiento almacenado, un INSERT SELECT, que consiste en un INSERT que se alimenta de un SELECT de otra tabla o tablas normalmente unidas por INNER JOIN.

A continuación hay un UPDATE normal y corriente con sus actualizaciones de campos y las condiciones de actualización.

También se ha incluido un MERGE que es como un UPDATE pero algo más complejo.

El MERGE utiliza también una SELECT que también puede ser compleja y al final se hace una comparación de la SELECT con la tabla a actualizar campo a campo con un on similar a los de un INNER JOIN con la condición de que estos campos deben ser Primary Key de sus tablas respectivas. Donde se produzca la coincidencia se actualiza el campo indicado de la tabla con el valor tomado de la SELECT interior.

Finalmente las instrucciones de cierre de procedimiento almacenado, también se muestran al comienzo y al final las instrucciones  DBMS_OUTPUT.put_line que muestran por pantalla o en un log el resultado de las acciones por la que va pasando, en este caso muestra la  fecha, hora y un comentario con las actividades que se están realizando.

Este es el Procedimiento almacenado.

--COMIENZO SP

create or replace PROCEDURE                                                                INSERT_NOMBRE AS 

BEGIN

DBMS_OUTPUT.put_line(TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH24:MI:SS'));

DBMS_OUTPUT.put_line('Ejemplos INSERT SELECT y UPDATE-MERGE');

--INSERT SELECT SP_TABLA1

INSERT INTO PROPIETARIO.NOMBRE_TABLA1

  (

    ID_A,

    CAMPO2_B,

    CAMPO3_C,

    CAMPO4_D

  )

SELECT 

TB4.ID1_A, TB3.CAMPO1_B, TB3.CAMPO2_C, TB2.CAMPO1_D from PROPIETARIO.TABLA2 TB2 

INNER JOIN PROPIETARIO.TABLA3 TB3 ON TB2.ID = TB3.ID

INNER JOIN PROPIETARIO.TABLA4 TB4 ON TB4.ID2 = TB3.ID2;

--UPDATE NORMAL 

UPDATE PROPIETARIO.TABLA1 TB1

SET CAMPO1 = expression1,

    CAMPO2 = expression2,

    ...

    CAMPO_N = expression_n

WHERE condiciones;

--MERGE

Los campos del on ( t2.ID = t1.ID) deben ser primary key de la tabla.

merge into PROPIETARIO.TABLA1 t1

using (SELECT A.COD_OBJT , B.DES_VALEXT  

from  PROPIETARIO.TABLA2 A inner join  PROPIETARIO.TABLA3 B ON A.ID = B.ID         

where B.CAMPO3 = 'Valor' ) t2

on ( t2.ID = t1.ID)

when matched then update set t1.CAMPO1 = t2.CAMPO1 ;

--FIN SP

----------------------------------------------------------------

DBMS_OUTPUT.put_line(TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH24:MI:SS'));

DBMS_OUTPUT.put_line('FIN PROCESO');

COMMIT;

--END SP_TABLA1;

Un merge más genérico.

merge into PROPIETARIO.TABLA1 t1
using (Select COD_CAMPO1,DES_CAMPO2 from  PROPIETARIO.TABLA2) t2
on ( t2.COD_CODIGO = t1.COD_CODIGO)

when matched then update set t1.DES_CAMPO_T1 = t2.DES_CAMPO_T2;

Y otro tipo de update.

UPDATE  
        (SELECT A.DES_ORIGEN AS old_value,
                E.DES_DESTINO AS new_value 
        FROM PROPIETARIO.TABLA1 A INNER JOIN PROPIETARIO.TABLA2 E ON A.COD_OBJT = E.COD_OBJT
        where E.CAMPO_X = 'Condición' )

        SET old_value = new_value; 

El procedimiento almacenado se crea en el entorno Oracle SQL developer.

Exists

SELECT Campo1

  FROM Tabla1 d

  WHERE EXISTS

  (SELECT * FROM Tabla2 e

    WHERE d.Campo1

    = e.Campo1);

Llamada desde Visual Basic

Si queremos llamar a un SP desde VB hacemos lo siguiente:

Const Tabla = "Tabla"

 Sub CargaTabla()

        Dim srSQL As StreamReader
        Dim strConsulta As String

        Try

            strStartupPath = My.Application.Info.DirectoryPath
            srSQL = New System.IO.StreamReader(strStartupPath & "\Ejecuta_SP.sql")
            strConsulta = srSQL.ReadToEnd
            cmdPD = New OleDb.OleDbCommand(strConsulta, cnxPD)
            cmdPD.CommandType = CommandType.StoredProcedure
            cmdPD.ExecuteNonQuery()
            bs.DataSource = ds.Tables(Tabla)

        Catch
            MsgBox("Se ha producido un error insertando los datos en TABLA: " & Err.Description)
        End Try

    End Sub


EN EJECUTA_SP va


NOMBRE_SP