Como crear un Procedimiento almacenado de Oracle con un bucle

Queremos ejecutar una acción, por ejemplo llamar a un paquete de Oracle PACK.FUNCION(‘Valor’) Pero en valor queremos meter miles de valores diferentes.

Para ello creamos un Procedimiento almacenado para que el resultado de la select sea lo que se se mete como parámetro en la función del paquete Oracle.

 Sea la select del tipo: SELECT CAMPO FROM TABLA WHERE CAMPO = ‘CONDICION’

 Para crear un bucle en un SP de Oracle

create or replace NONEDITIONABLE PROCEDURE  PROCEDIMIENTO_ALMACENADO_CON_BUCLE IS

      CURSOR C1 IS SELECT CAMPO FROM TABLA WHERE CAMPO = ‘CONDICION’;

BEGIN

     FOR I IN C1 LOOP

         PACK.FUNCION (I);

     END LOOP;  

 END;

 Y luego se llama al SP ejecutando esto como si  fuera una query

BEGIN

PROCEDIMIENTO_ALMACENADO_CON_BUCLE;

END;

 

Como probar código en TOAD, (ejemplo: Buscar un valor en toda la base de datos)

Hay veces que tenemos código de Oracle en a base de datos (por ejemplo un PL) y queremos saber si funciona bien o no , o probarlo para ver que hace.

Para probar código con TOAD lo que tenemos que hacer es declarar primero las variables que vayamos a utilizar.

declare

  VARIABLE1    varchar2(1000);

  VARIABLE2    varchar2(1000);

  VNUMERICA1   number;

  VNUMERICA2   number;

   newparams varchar2(2000) := ‘valores por defecto a introducir en el código';

begin

Luego ponemos el   

  begin

        VARIABLE1 :=newparams;

        dbms_output.enable();

        dbms_output.put_line(VARIABLE1);

        dbms_output.put_line(VARIABLE2);

end;

Abrimos un Editor y pegamos nuestro código a probar

Se coloca en una pestaña SQL de TOAD

Se pulsa el triangulo verde para ejecutar.

 

Vemos el resultado en la pestaña DBMS Output

Activamos la salida pulsando sobre el circulo rojo (flecha roja) y se vuelve verde, entonces ponemos ver la salida en el botón de las dos flechas verdes (flecha azul)

 

El resultado sale en la pestaña inferior DBMS_Output porque lo hemos forzado con las variables:

        dbms_output.put_line(VARIABLE1);

Buscar un valor en toda la base de datos

En este caso el código que probamos sirve para buscar un valor en toda la base de datos: Los valores en rojo tienen que sustituirse por el propietario de nuestra base de datos y por el valor que deseemos buscar.

DECLARE

sql_str VARCHAR2(1000);

sql_del VARCHAR2(1000);

cadena_buscar VARCHAR(200);

total_val number;

--cursor c1 IS SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM dba_tab_columns WHERE owner='PROPIETARIO_BBDD' AND DATA_TYPE IN ('CHAR','VARCHAR2');

cursor c1 IS SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM all_tab_columns WHERE owner='PROPIETARIO_BBDD' AND DATA_TYPE IN ('CHAR','VARCHAR2');

BEGIN

    cadena_buscar:='VALOR_A_BUSCAR';

    FOR fila IN c1

        LOOP

            sql_str := 'SELECT COUNT(*) FROM '||fila.OWNER||'.'||fila.TABLE_NAME||' where '||fila.COLUMN_NAME||' like ''%'||cadena_buscar||'%''';

            sql_del := 'DELETE FROM '||fila.OWNER||'.'||fila.TABLE_NAME||' where '||fila.COLUMN_NAME||' like '''||cadena_buscar||'''';

            EXECUTE IMMEDIATE sql_str INTO total_val;

            IF total_val>0 then

                dbms_output.put_line(sql_str);

                dbms_output.put_line(fila.OWNER||'.'||fila.TABLE_NAME||'.'||fila.COLUMN_NAME||' Aciertos '||total_val);

                dbms_output.put_line(sql_del);

                dbms_output.put_line('---');

            end IF;

        END LOOP;

    END;

Otro código de búsqueda:

SET SERVEROUTPUT ON SIZE 100000 

 DECLARE 

 match_count INTEGER;

 BEGIN FOR t IN 

 (SELECT owner, table_name, column_name FROM all_tab_columns WHERE owner = ''PROPIETARIO_BBDD ' 

 and data_type LIKE '%CHAR%') LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name || 

 ' WHERE '||t.column_name||' = :1' INTO match_count USING 'VALOR_A_BUSCAR'; 

 IF match_count > 0 THEN dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count ); 

 END IF;

 END LOOP; 

 END;

Generar consultas complejas de Oracle con TOAD, a partir de un esquema visual

Hay veces que nos piden queries muy complejas y resulta difícil dar con la relación ideal entre tablas para sacar nuestra querie, pero existen recursos gráficos que nos permiten visualizar las tablas de las que queremos sacar la información y sacar sus relaciones entre ellas, (aquí con SQL Server) una vez establecidas estas relaciones la herramienta nos propone la query automáticamente.

En este caso utilizaremos TOAD de Oracle, en este caso desde Database elegimos Report y la opción ER Diagram.

 

Aparece una cruz azul que pulsamos para elegir que tablas queremos de la base de datos, si queremos todas las relacionadas con ella marcamos el check de arriba (auto select), pero si queremos elegirlas una a una lo desmarcamos.

 

Elegidas las tablas pulsamos ok, y nos muestra el esquema ER. 

 

Si queremos sacar la query correspondiente a nuestro esquema, pulsamos sobre el botón SQL

 Se nos abrirá una pestaña inferior con la query

 

También podemos abrir una pestaña nueva para tener un esquema diferente

Para elegir las columnas de la query marcamos la tabla del panel derecho y hacemos doble clic sobre ella, al marcar las columnas, se nos van escribiendo en la query

 

También podemos arrastrar y soltar la tabla desde el panel derecho y elegir las columnas (si tiene muchas las vamos a ver pequeñas) 

Para verlas grandes podemos pulsar sobre la lupa y elegir 100%

Si no encontramos las tablas del panel derecho, podemos sacarlas con View -> object palette.

Una vez tenemos abierto nuestro esquema ER podemos modificarlo y se irá cambiando la query, podemos pinchar y arrastrar desde un campo de una tabla hasta otro para crear relaciones INNER JOIN 

si pulsamos con el botón derecho del ratón sobre el panel podemos modificar nuestra Where, having, añadir objetos, etc.

Por ejemplo si pulsamos sobre Where conditions, se abrirá una pantalla bastante completa para modificar nuestra condición WHERE.

Depurar un paquete de Oracle con TOAD

Para depurar un paquete de base de datos con TOAD primero abrimos el buscador de objetos de la base de datos con Database-> Schema Browser

 

Elegimos paquetes (packages) 

 

Y elegimos el paquete y procedimiento a ejecutar

Situados sobre el paquete a depura, con el botón derecho del ratón pulsamos sobre el procedimiento y elegimos Compile-> Compile with Debug

Nos sale una pantalla en la que elegimos la primera opción Refresh Items One by One

Hecho esto nos colocamos sobre el procedimiento o función a depurar y hacemos doble click sobre él para abrirlo en el editor

Ahora  sobre el editor podemos colocarnos sobre una línea y con el botón derecho del ratón elegimos Debug-> Set Breakpoint para establecer sobre el código los puntos de ruptura que deseemos.

 

Para comenzar la depuración pulsamos sobre el botón con el triángulo verde que pone SQL

Y le decimos que si a los mensajes que nos salgan hasta llegar a la pantalla para rellenar las variables. Una vez en esta pantalla le ponemos a cada variable el valor con el que vayamos a depurar nuestro código.

 

Y pulsamos Ok,  ahora para depurar debemos ir pulsando sobre los botones Step Over, trace Into, etc según deseemos.

 

El código se irá ejecutando paso a paso.   Y podremos ver los valores que van tomando las variables y donde están nuestros posibles errores.  Podemos terminar la depuración con el botón Terminate Execution.

Generar Scripts de Base de Datos desde SQL Developer

Hay veces que necesitamos genera un script de Base de datos para hacer alguna tarea. Para ello no es necesario teclear todo el script, sobre todo si se trata de cientos o miles de líneas y además podemos cometer errores. Suele ser más efectivo que lo genere la propia base de datos desde SQL Developer. Para ello, basta con generar la query que necesitemos y una vez hecha, podemos generar un script con sus resultados. Por ejemplo en este caso vamos a genera cientos o miles de instrucciones insert a partir de un simple SELECT * FROM NOMBRE_TABLA.

Para generar scripts desde SQL Developer, sobre las tablas, pulsamos Botón derecho de ratón y Exportar

 

Definimos el archivo de destino y pulsamos siguiente.

 

Si deseamos sólo un número determinado de registros y no la tabla entera, podemos definir un WHERE

 

Pulsamos siguiente

 

Y pulsamos terminar

 

Al final se genera el DDL en pantalla

 

O si lo deseamos, también podemos descargarlo en un archivo

Importar datos de excel a una tabla oracle

Hay veces que queremos almacenar en una base de datos, los datos que nos pasan o tenemos almacenados en una hoja Excel. En Oracle SQL Developer hay un método para hacerlo automáticamente.

En éste ejemplo crearemos primero una tabla la cual utilizaremos para la importación de los datos.

CREATE TABLE "OWNER"."AUX_TABLA" 

   ( "ID" NUMBER, 

"CAMPO1" VARCHAR2(20 BYTE), 

"CAMPO2" VARCHAR2(20 BYTE), 

"CAMPO3" VARCHAR2(20 BYTE), 

"CAMPO4" VARCHAR2(20 BYTE), 

"CAMPO5" VARCHAR2(20 BYTE)

   ) ;

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

--  Constraints for Table AUX_TABLA

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

  ALTER TABLE "OWNER"."AUX_TABLA" MODIFY ("ID" NOT NULL ENABLE);

Tomamos la hoja de Excel con los datos que vamos a importar (hemos creado tantas columnas en la tabla como en el Excel, además añadimos una columna ID y le ponemos una función que sume de 1 en 1 para tener el ID de las filas.

En Sql Developer hacemos click derecho sobre la tabla  y luego en «Importar Datos…».

 

 

Seleccionamos el archivo de Excel a importar.

 

 Se mostrara el paso 1 del asistente de importación de datos, click en siguiente:

 

Hacemos click en siguiente.

 

Selección de las columnas a importar, por defecto se seleccionan todas, click en siguiente:

 

 

Definimos la relación de las columnas del origen de los datos (excel) y las columnas de la tabla a la que deseamos importar, por defecto se relacionan en el orden de las columnas del excel y la tabla, debajo podemos ver los datos para asegurarnos que es lo que deseamos importar.

 

 Podemos realizarla haciendo click en Terminar.

 

con este proceso hemos importado datos de una hoja de excel a una tabla de Oracle.

 

 Este proceso también puede realizarse utilizando un archivo CSV.

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

De CSV a DataGridView y de DatagridView a BBDD.

Leer un CSV

Se puede leer desde Visual Basic un archivo con formato separado por comas CSV, para que esto sea posible, el formato que debe cumplir un CSV debe cumplir estos requisitos:

Sea un fichero de texto plano cualquiera, eliminamos las cabeceras (suele ser la primera fila), y renombramos el fichero como .csv 

Si ahora lo abrimos (debemos tener en nuestro equipo un programa capaz de leer archivos .csv como LibreOffice por ejemplo).

Al intentar abrirlo nos muestra esta pantalla.