Oracle - Insert/Update desde un rango de tablas

Insert/Update desde un rango de tablas

Publicado por carlino70 (6 intervenciones) el 08/03/2013 16:03:12

Hola, espero que puedan ayudarme en esto.
Estoy trabajando en un procedure que realice los DML necesarios desde un rango de tablas (noa_ave ET y noa_ave_max EPE), sobre un tercer rango de tablas. Esto debería ser dinámico.

Las tablas origen son:

desc noa_ave ET

  UTCTIME      TIMESTAMP(6)                     NOT NULL,

  POINTNUMBER  INTEGER                          NOT NULL,

  VALUE        FLOAT(126)                       DEFAULT (0.0),

  TLQ          INTEGER                          DEFAULT (32)

desc noa_ave_max EPE

  UTCTIME      TIMESTAMP(6)                     NOT NULL,

  POINTNUMBER  INTEGER                          NOT NULL,

  VALUE        FLOAT(126)                       DEFAULT (0.0),

  TLQ          INTEGER                          DEFAULT (32),

  UTCTIMEMAX   DATE

Los rangos de las tablas los obtengo con:

--Lista tablas ET

SELECT TABLE_NAME

            FROM USER_TABLES

            WHERE TABLE_NAME NOT LIKE '%MAX%'

            AND TABLE_NAME NOT LIKE '%MIN%'

            AND TABLE_NAME LIKE 'NOA_%'

--Cursor

DECLARE

  CURSOR c_lista_tablas

IS

  SELECT TABLE_NAME

            FROM USER_TABLES

            WHERE TABLE_NAME NOT LIKE '%MAX%'

            AND TABLE_NAME NOT LIKE '%MIN%'

            AND TABLE_NAME LIKE 'NOA_%';

  registro c_lista_tablas%ROWTYPE;

BEGIN

  OPEN c_lista_tablas;

  FETCH c_lista_tablas INTO registro;

  CLOSE c_lista_tablas;

END;

-- Lista tablas EPE

SELECT TABLE_NAME

            FROM USER_TABLES

            WHERE table_name LIKE 'NOA_%_MAX'

            ORDER BY TABLE_NAME ASC;

-- Cursor

DECLARE

  CURSOR c_lista_tablas_max

IS

  SELECT TABLE_NAME

            FROM USER_TABLES

            WHERE table_name LIKE 'NOA_%_MAX'

            ORDER BY TABLE_NAME ASC;

  registro_max c_lista_tablas_max%ROWTYPE;

BEGIN

  OPEN c_lista_tablas_max;

  FETCH c_lista_tablas_max INTO registro_max;

  CLOSE c_lista_tablas_max;

END;

Hasta ahi obtengo el rango de tablas origen de los datos, y los guardo en registros. El select que me devuelve los datos a insertar/updatear es :

select a.utctime, a.pointnumber, a.value POT_ET, a.tlq, b.utctimemax, max(b.value) POT_EPE

						from noa_ave a, noa_ave_max b

						where a.utctime = b.utctimemax

						and a.utctime > sysdate -2

						group by a.utctime, a.pointnumber, a.value, a.tlq, b.utctimemax, b.value;

--Salida

UTCTIME,                   POINTNUMBER, POT_ET, TLQ, UTCTIMEMAX,             POT_EPE

03/06/2013 0:35:00, 118000,           10,         32,   03/06/2013 0:35:00, 3000,

Con esto obtengo los datos de las tablas ET, que se corresponden al mismo momento UTCTIMEMAX de las tablas EPE, junto con el max(value), por día (solo lo hago una vez por día, por tabla)

De esta manera armé un procedure que quedo así:

PROCEDURE UPDATE_ALL_POINTNUMBER(P_DATE               IN DATE,

                                     P_OWNER            IN VARCHAR2 DEFAULT NULL,

                                     P_COMMIT           IN BOOLEAN DEFAULT FALSE)

IS

       CURSOR c_lista_tablas IS

            SELECT TABLE_NAME

            FROM ALL_TABLES

            WHERE TABLE_NAME NOT LIKE '%MAX%'

            AND TABLE_NAME NOT LIKE '%MIN%'

            AND TABLE_NAME LIKE 'NOA_%'

            and owner = 'TEST'

            ORDER BY TABLE_NAME ASC;

    v_owner           all_tables.owner%type;

    v_table_name all_tables.table_name%type;

       CURSOR c_lista_tablas_max IS

            SELECT TABLE_NAME

            FROM ALL_TABLES

            WHERE table_name LIKE 'NOA_%_MAX'

            AND OWNER = 'TEST'

            ORDER BY TABLE_NAME ASC;

    v_owner_max           all_tables.owner%type;

    v_table_name_max all_tables.table_name%type;

    BEGIN

        NULL;

        OPEN c_lista_tablas;

        LOOP

           FETCH c_lista_tablas INTO  v_table_name;

           EXIT WHEN c_lista_tablas%NOTFOUND;

           BEGIN

                    update_max_x_table(v_table_name,  v_table_name || '_DIA' ,p_date, p_commit);

           EXCEPTION

                WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);

           END;

        END LOOP;

        CLOSE c_lista_tablas;

        OPEN c_lista_tablas_max;

        LOOP

           FETCH c_lista_tablas_max INTO  v_table_name_max;

           EXIT WHEN c_lista_tablas_max%NOTFOUND;

           BEGIN

                    update_max_x_table(v_table_name_max,  v_table_name_max || '_POT' ,p_date, p_commit);

           EXCEPTION

                WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);

           END;

        END LOOP;

        CLOSE c_lista_tablas_max;

    END;

La pregunta es: como armar un segundo procedure update_max_x_table, que contenga el select y que realice los update/insert desde las tablas de los rangos obtenidos en los registros, a nuevas tablas que también pertenencen a un nuevo rango NOA_AVE_POT

Estoy estancado aqui, y no cuento con demasiada experiencia en PL-SQL

Me gustaría recibir ayuda para resolver mi problema.

Agradecido a quien puediera darme una mano.

Saludos!

Carlino

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Insert/Update desde un rango de tablas

Publicado por luis (235 intervenciones) el 14/03/2013 16:40:13

Hola,

Para lo que quieres hacer te recomiendo usar bucles FOR LOOP para trabajar con los cursores sn mas óptimos y no tienes que codificar tanto.

Como trabajas con varios cursores tambien puedes usar cursores parametrizados, ejemplo:

declare
cursor1 datos1 is
select campo1, campo2
from tabla1
where campo1= valor1;

cursor2 datos2(param1, param2 ) is
select campo1, campo2, campo2
from tabla2
where campo1=param1
and campo2= param2 ;

begin

for x in datos1 loop
--Aqui pones tu codigo mientras barres cada registro del cursor1

for y datos2( param1, param2 )loop
/* param1 y param2 pueden ser campos del cursor1 que está
siendo leída y por cada registro de cursor1 se ejecuta el query del cursor 2
*/

end loop;

end loop;
end;

Otra recomendación, como estás haciendo updates e inserts puedes usar sql Dinámico, es decir construyes tu sentencia select o insert como una cadena de texto y luego se ejecuta como una instrucción normal. Revizar para esto PL/SQL dinamico en oracle y la sentencia Execute immediate. Hay muchos ejemplos en la web.

Saludos y suerte en este apasionante mundo de Oracle.
Y cualquier cosa consulta,
El conocimiento es para compartirlo y hacerlo mas grande!!!

Luis

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Insert/Update desde un rango de tablas

Publicado por carlino70 (6 intervenciones) el 15/03/2013 12:19:19

Muchas gracias Luis, te cuento como va quedando el cursor. Lo hice solo para una tabla, ahora me quedaría incluir que el cursor recorra el rango de tablas y haga select/inserts a una tercera tabla. Para esto quizás te moleste nuevamente.

--Para armar cursor de los datos

/*alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';*/

DECLARE

   TYPE c_datos IS REF CURSOR;

   datos_cv     c_datos;

   v_utctimemax_rtc    DATE;

   v_value_max_rtc     FLOAT;

   v_utctime_max_noa   DATE;

   v_value_max_noa     FLOAT;

   v_utctime_noa       DATE;

   v_value_noa         FLOAT;

   sql_datos    VARCHAR2 (2000);

BEGIN

   sql_datos :='SELECT a.utctimemax hora_max_rtc,

                    a.VALUE valor_max_rtc,

                    b.utctime hora_ave,

                    b.VALUE valor_ave,

                    c.utctime max_hora_rtc,

                    c.VALUE max_value_ave

        FROM rtc_estaciones a, noa_ave b, noa_ave c

       WHERE a.VALUE IN (SELECT MAX (VALUE)

                           FROM rtc_estaciones)

         AND c.VALUE IN (SELECT MAX (VALUE)

                           FROM noa_ave

                          WHERE utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2)

         AND a.utctimemax BETWEEN SYSDATE - 3 AND SYSDATE - 2

         AND b.utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2

         AND c.utctime BETWEEN SYSDATE - 3 AND SYSDATE - 2

         AND a.utctimemax = b.utctime

';

   OPEN datos_cv FOR sql_datos;

   LOOP

      FETCH datos_cv INTO v_utctimemax_rtc, v_value_max_rtc, v_utctime_max_noa, v_value_max_noa, v_utctime_noa, v_value_noa;

      EXIT WHEN datos_cv%NOTFOUND;

      /*dbms_output.put_line(v_utctimemax_rtc

                  || ' , ' ||v_value_max_rtc

                  || ' , ' ||v_utctime_max_noa

                  || ' , ' ||v_value_max_noa

                  || ' , ' ||v_utctime_noa

                  || ' , ' ||v_value_noa);*/

      insert into noa_ave_max_pot /*(HORA_MAX_RTC, VALOR_MAX_RTC, HORA_MAX_NOA, VALOR_MAX_NOA,  HORA_NOA,  VALOR_NOA)*/

                           values (v_utctimemax_rtc,v_value_max_rtc,v_utctime_max_noa,v_value_max_noa,v_utctime_noa,v_value_noa);

      commit;

   END LOOP;

   CLOSE datos_cv;

END;

Valora esta respuesta

Comentar

Insert/Update desde un rango de tablas

Publicado por carlino70 (6 intervenciones) el 20/03/2013 19:08:03

Muchas gracias por la ayuda. El procedure esta funcionando OK.

Valora esta respuesta

Comentar

No consigo configurar Webutil en Forms 11g, aunque en Forms 10g si lo consegui

SQL Statment ignored - Error