salida - ¿La mejor manera de encapsular la lógica compleja del cursor PL/SQL de Oracle como una vista?

procedimientos y funciones oracle pl/sql

Question

Escribí el código PL / SQL para desnormalizar una tabla en una forma mucho más fácil de consultar. El código usa una tabla temporal para hacer parte de su trabajo, combinando algunas filas de la tabla original.

La lógica se escribe como una función de tabla canalizada , siguiendo el patrón del artículo vinculado. La función de tabla utiliza una declaración PRAGMA AUTONOMOUS_TRANSACTION para permitir la manipulación de la tabla temporal, y también acepta un parámetro de entrada del cursor para restringir la desnormalización a ciertos valores de ID.

Luego creé una vista para consultar la función de tabla, pasando todos los posibles valores de ID como un cursor (otros usos de la función serán más restrictivos).

Mi pregunta: ¿esto es realmente necesario? ¿Me he perdido completamente una manera mucho más simple de lograr lo mismo?

Cada vez que toco PL / SQL tengo la impresión de que estoy escribiendo demasiado.

Actualización: Agregaré un boceto de la tabla con la que estoy tratando para darles a todos una idea de la desnormalización de la que estoy hablando. La tabla almacena un historial de trabajos de empleados, cada uno con una fila de activación y (posiblemente) una fila de terminación. Es posible que un empleado tenga múltiples trabajos simultáneos, así como el mismo trabajo una y otra vez en intervalos de fechas no contiguas. Por ejemplo:

| EMP_ID | JOB_ID | STATUS | EFF_DATE    | other columns...
|      1 |     10 | A      | 10-JAN-2008 |
|      2 |     11 | A      | 13-JAN-2008 |
|      1 |     12 | A      | 20-JAN-2008 |
|      2 |     11 | T      | 01-FEB-2008 |
|      1 |     10 | T      | 02-FEB-2008 |
|      2 |     11 | A      | 20-FEB-2008 |

Preguntar eso para descubrir quién está trabajando en qué trabajo no es trivial. Por lo tanto, mi función de desnormalización rellena la tabla temporal con solo los rangos de fechas para cada trabajo, para cualquier EMP_ID pase a través del cursor. Pasar en EMP_ID s 1 y 2 produciría lo siguiente:

| EMP_ID | JOB_ID | START_DATE  | END_DATE    |
|      1 |     10 | 10-JAN-2008 | 02-FEB-2008 |
|      2 |     11 | 13-JAN-2008 | 01-FEB-2008 |
|      1 |     12 | 20-JAN-2008 |             |
|      2 |     11 | 20-FEB-2008 |             |

( END_DATE permite END_DATE NULL para trabajos que no tienen una fecha de finalización predeterminada).

Como se puede imaginar, esta forma desnormalizada es mucho, mucho más fácil de consultar, pero crearla (hasta donde puedo ver) requiere una tabla temporal para almacenar los resultados intermedios (por ejemplo, registros de trabajo para los cuales se ha activado la fila de activación). encontrado, pero no la terminación ... todavía). Usar la función de tabla canalizada para rellenar la tabla temporal y luego devolver sus filas es la única forma en que he descubierto cómo hacerlo.

Answer 1

En lugar de tener el parámetro de entrada como cursor, tendría una variable de tabla (no sé si Oracle tiene tal cosa, soy TSQL) o rellenar otra tabla temporal con los valores de ID y unirme a ella en la vista / función o donde sea que lo necesite.

El único momento para los cursores en mi honesta opinión es cuando tienes que repetir. Y cuando tienes que repetir el ciclo siempre recomiendo hacerlo fuera de la base de datos en la lógica de la aplicación.

Answer 2

No podría estar más de acuerdo contigo, HollyStyles. También solía ser un TSQL, y encuentro algunas de las idiosincrasias de Oracle más que un poco desconcertantes. Desafortunadamente, las tablas temporales no son tan prácticas en Oracle, y en este caso, otra lógica SQL existente espera consultar directamente una tabla, por lo que le doy esta vista. Realmente no existe una lógica de aplicación que exista fuera de la base de datos en este sistema.

Los desarrolladores de Oracle parecen usar los cursores mucho más ansiosamente de lo que hubiera pensado. Dada la naturaleza bondage & discipline de PL / SQL, eso es aún más sorprendente.

Answer 3

Creo que una forma de abordar esto es usar funciones analíticas ...

Configuré tu caso de prueba usando:

create table employee_job (
    emp_id integer,
    job_id integer,
    status varchar2(1 char),
    eff_date date
    );  

insert into employee_job values (1,10,''A'',to_date(''10-JAN-2008'',''DD-MON-YYYY''));
insert into employee_job values (2,11,''A'',to_date(''13-JAN-2008'',''DD-MON-YYYY''));
insert into employee_job values (1,12,''A'',to_date(''20-JAN-2008'',''DD-MON-YYYY''));
insert into employee_job values (2,11,''T'',to_date(''01-FEB-2008'',''DD-MON-YYYY''));
insert into employee_job values (1,10,''T'',to_date(''02-FEB-2008'',''DD-MON-YYYY''));
insert into employee_job values (2,11,''A'',to_date(''20-FEB-2008'',''DD-MON-YYYY''));

commit;

He usado la función principal para obtener la próxima fecha y luego la envolví todo como una sub consulta solo para obtener los registros "A" y agregar la fecha de finalización si la hay.

select
    emp_id,
    job_id,
    eff_date start_date,
    decode(next_status,''T'',next_eff_date,null) end_date
from
    (
    select
        emp_id,
        job_id,
        eff_date,
        status,
        lead(eff_date,1,null) over (partition by emp_id, job_id order by eff_date, status) next_eff_date,
        lead(status,1,null) over (partition by emp_id, job_id order by eff_date, status) next_status
    from
        employee_job
    )
where
    status = ''A''
order by
    start_date,
    emp_id,
    job_id

Estoy seguro de que hay algunos casos de uso que me he perdido, pero entiendes la idea. Las funciones analíticas son tu amiga :)

EMP_ID   JOB_ID     START_DATE     END_DATE            
  1        10       10-JAN-2008    02-FEB-2008         
  2        11       13-JAN-2008    01-FEB-2008         
  2        11       20-FEB-2008                              
  1        12       20-JAN-2008                              

Answer 4

La solución más simple es:

1. Cree una tabla temporal global que contenga los ID que necesita:

   CREAR TABLA TEMPORAL GLOBAL tab_ids (ID INTEGER)
   EN COMMIT DELETE ROWS;

2. Complete la tabla temporal con los ID que necesita.

3. Use la operación EXISTS en su procedimiento para seleccionar las filas que están solo en la tabla IDs:

   SELECCIONE yt.col1, yt.col2 FROM your_table yt
   DONDE EXISTE (
   SELECCIONE ''X'' DE tab_ids ti
   DONDE ti.id = yt.id
   )

También puede pasar una cadena de identificadores separados por comas como un parámetro de función y analizarlo en una tabla. Esto se realiza con un único SELECCIONAR. ¿Quieres saber más? Pregúntame cómo :-) Pero tiene que ser una pregunta separada.

Answer 5

Parece que está regalando algo de consistencia de lectura aquí, es decir: será posible que el contenido de su tabla temporal no esté sincronizado con los datos de origen, si tiene modificaciones de datos de modificación concurrentes.

Sin conocer los requisitos, ni la complejidad de lo que quiere lograr. Yo intentaría

1. para definir una vista que contenga lógica (posiblemente compleja) en SQL, de lo contrario agregaría un poco de PL / SQL a la mezcla;
2. Una función de tabla canalizada, pero con un tipo de colección SQL (en lugar de la tabla temporal). Un ejemplo simple es aquí: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4447489221109

El número 2 te da menos partes móviles y resuelve tu problema de consistencia.

Mathew Butler

Answer 6

El verdadero problema aquí es el diseño de la mesa "de solo escritura", es decir, es fácil insertar datos en él, pero es complicado e ineficiente para obtener información útil. Su tabla "temporal" tiene la estructura que la tabla "permanente" debería haber tenido en primer lugar.

¿Podrías hacer esto?

• Crea una mesa permanente con la mejor estructura
• Rellenarlo para que coincida con los datos en la primera tabla
• Defina un activador de base de datos en la tabla original para mantener la nueva tabla sincronizada a partir de ahora

Luego puede seleccionar de la nueva tabla para realizar su informe.