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;