Acceso a colecciones Oracle como si fuesen tablas físicas

Oracle, pionero en BD relacionales.

Acceso a colecciones Oracle como si fuesen tablas físicas

Notapor Pere » Lun Jun 17, 2013 4:57 pm

En esta ocasión voy a exponer un recurso Oracle de gran potencia que descubrí al investigar como abordar una interface para una publicación web que se nutría de una BBDD Oracle.

El caso es que en los requisitos del proyecto se pedía que la información fuese accesible desde la web en tiempo real, por lo que quedaba descartado montar estructuras auxiliares para que, por ejemplo, un proceso batch las alimentase por la noche y tener la información preparada y calculada para que fuese visible desde la web. Por otro lado, la información que debía aparecer en la web no estaba en unas pocas tablas, ni era directa, sino que se necesitaba acceder a un gran número de tablas y requería bastante lógica para presentar la información del modo deseado.

El desarrollo web se programaría en Java y se accedería a la BBDD oracle mediante un webservice, usando Hibernate como herramienta de desarrollo de acceso a la BBDD Oracle.

La investigación que realicé analizando el proyecto me llevó a descubrir un gran recurso de Oracle que permite alimentar estructuras dinámicas, es decir, colecciones, y atacarlas como si de tablas físicas se tratara, haciéndolo totalmente compatible con Hibernate en particular y digamos que con cualquier estándar de acceso a las bases de datos como puede ser ODBC.

La idea pasa por programar un package donde las funciones públicas retornan colecciones, es decir, estructuras dinámicas, y los programadores que desarrollan la interface de usuario en Java, en lugar de realizar consultas sobre las tablas físicas de la BBDD y programar todo su tratamiento posterior, realizan llamadas a las funciones pero como si fuesen tablas, es decir a través de selects, donde la información ya viene tratada. De este modo toda la lógica de la capa de negocio queda encapsulada en el package, las funciones devuelven la información de las diferentes vistas de la aplicación web, y el desarrollador Java tan solo tiene que realizar las llamadas con los parámetros pertinentes. La información le vendrá en forma de filas y columnas ya que las funciones devuelven colecciones.

Para ilustrar este recurso vamos a programar una función de ejemplo que retorna una estructura dinámica con tantos registros como le indiquemos en el parámetro de la función, en este ejemplo los datos se los inventa la función, es decir, genera dinámicamente tantos registros como le indicamos en el parámetro y retorna el resultado.

Empecemos por definir la estructura de registro o tupla sobre la que se creará la colección, en este caso es un registro con tres campos:

Definición:
Código: Seleccionar todo

CREATE OR REPLACE type TEST_CLASS_TYPE as object 
(      
      id_record   number
(4),
      desc_record varchar2(20),
      date_record date,
      
      constructor function test_class_TYPE return self as result            
);


Implementación:
Código: Seleccionar todo

CREATE OR REPLACE type body TEST_CLASS_TYPE as
   constructor function test_class_TYPE return self as result is
   begin
      return
;
   end;
end;
/


Ahora creamos el tipo colección en base al tipo registro previamente definido.

Código: Seleccionar todo

CREATE OR REPLACE type TEST_TABLE_TYPE is table of test_class_TYPE
;


Creamos el package donde se alojará la función de ejemplo.

Definición:
Código: Seleccionar todo

CREATE OR REPLACE package TEST_COLLECTION is
   
   function  get_table
(p_max_records in number) return test_table_TYPE;
      
end
;


Implementación:
Código: Seleccionar todo

CREATE OR REPLACE package body test_collection is
   
   function get_table
(p_max_records in number) return test_table_TYPE is
      itab test_table_TYPE
;      
      
   begin
      
      itab 
:= new test_table_TYPE();
      if nvl(p_max_records,0) > 0 then
         for i in 1
..p_max_records loop
            itab
.extend;
            itab(itab.last) := new test_class_TYPE();            
            itab
(itab.last).id_record := i;
            itab(itab.last).desc_record := 'Registro '||i;
            itab(itab.last).date_record := trunc(sysdate + i - 1);
         end loop;
      end if;
      
      return itab
;
   end;
 
end
; 


Y ahora viene lo interesante, como acceder a esta colección como si de una tabla física se tratase:
Código: Seleccionar todo

select 
* from TABLE( test_collection.get_table(12) );


Resultado:
Código: Seleccionar todo

ID_RECORD DESC_RECORD          DATE_RECORD       
--------- -------------------- ------------------
        1 Registro 1           17/06/2013        
        2 Registro 2           18
/06/2013        
        3 Registro 3           19
/06/2013        
        4 Registro 4           20
/06/2013        
        5 Registro 5           21
/06/2013        
        6 Registro 6           22
/06/2013        
        7 Registro 7           23
/06/2013        
        8 Registro 8           24
/06/2013        
        9 Registro 9           25
/06/2013        
       10 Registro 10          26
/06/2013        
       11 Registro 11          27
/06/2013        
       12 Registro 12          28
/06/2013        
12 rows selected


Observe como con este recurso pueden realizar agrupación de datos, filtros en la cláusula WHERE... y en definitiva operar con la colección que devuelve la función como si de una tabla física se tratase:

Código: Seleccionar todo

select 
* from TABLE( test_collection.get_table(31) )
 where id_record > 20
 order by id_record desc


Resultado:
Código: Seleccionar todo

ID_RECORD DESC_RECORD          DATE_RECORD       
--------- -------------------- ------------------
       31 Registro 31          17/07/2013        
       30 Registro 30          16
/07/2013        
       29 Registro 29          15
/07/2013        
       28 Registro 28          14
/07/2013        
       27 Registro 27          13
/07/2013        
       26 Registro 26          12
/07/2013        
       25 Registro 25          11
/07/2013        
       24 Registro 24          10
/07/2013        
       23 Registro 23          09
/07/2013        
       22 Registro 22          08
/07/2013        
       21 Registro 21          07
/07/2013        
11 rows selected



En un caso real los parámetros de la función serían los filtros que el usuario web indica en los formulario de acceso a datos, y los datos retornados por la función mediante la colección los reuniría la lógica programada dentro de la función, donde internamente se puede realizar acceso a un gran número de tablas, realizar operaciones, conversiones, comparaciones, etc.. todo lo necesario para ir llenando la colección y retornar los datos que espera la aplicación Java para mostrarlos en la web.

Con ello conseguimos las siguientes ventajas destacables.

    1. - Toda la lógica para reunir los datos queda encapsulada en el núcleo de Oracle.
    2. - El desarrollador Java no es necesario que conozca la lógica de negocio, solo debe conocer la interfece (package Oracle)
    3. - Solo será necesario mapear en Hibernate las estructuras dinámicas, y no las múltiples tablas de la capa de negocio que forman la fuente de datos.
    4. - Todos los accesos a tablas físicas se realizan en PL/SQL siendo menos costoso su implementación que en la parte Java.
    5. - Permite dividir la implementación por capas, la interface la desarrolla el programador Oracle y la página web el desarrollador Java.
    6. - Tan solo es necesario otorgar privilegios de ejecución sobre el package que aloja las funciones, y no sobre las múltiples tablas que forman la fuente de datos.

El único inconveniente que le veo es que al ser lógica la que al fin y al cabo se acaba ejecutando, podemos tener problemas de rendimiento si no cuidamos la eficiencia del código que contienen las funciones.

Y esto es todo, como siempre espero lo podáis aplicar.
Pere
 
Mensajes: 74
Registrado: Mar Feb 02, 2010 9:44 pm

Volver a SGBD Oracle

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 1 invitado