Oracle - Insert/Update desde un rango de tablas

   
Vista:

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
--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 :

1
2
3
4
5
6
7
8
9
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í:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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 claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Insert/Update desde un rango de tablas

Publicado por luis (232 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 claraNo me gusta: Está respuesta no esta clara o no es útil
0
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
--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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar