oracle - incrementar - Uso de secuencia al agregar y borrar de una tabla

secuencias sql ejemplos

Question

Estoy creando una tabla donde agregaré nombre de archivo y muchos otros campos. Utilicé una columna fileid para representar los archivos en orden secuencial; es decir, el primer archivo que se debe cargar debe tener el campo ID 1, luego el siguiente archivo tendrá el archivo ID 2 y así sucesivamente. Usé una secuencia y desencadené:

create sequence create_file_id start with 1 increment by 1 nocache;

El disparador es:

before insert on add_files_details
for each row
begin
select create_file_id.nextval into :new.file_id from dual;
end;

Pero, si cualquier registro / registro se elimina de la tabla, la secuencia se mezcla. Entonces, estoy pensando en utilizar otra secuencia con un disparador para disminuir el valor de la secuencia anterior por el número de filas eliminadas. Pero estoy atascado en la implementación del disparador de esta secuencia.

Secuencia:

create sequence del_file_id increment by -1 nocache;

¿Alguna forma de lograr esto?

Answer 1

Lea AskTom sobre por qué no le gustaría probar y crear una secuencia sin espacios.

@Nicholas tiene un enfoque bastante bueno allí, nunca antes lo había pensado.

Sin embargo, un par de problemas.

1. Si usa rownum en su vista, también debería incluir la instrucción ORDER BY en TAB_ID.
2. Usando TAB_ID del ejemplo; en un sistema de RAC, no hay garantía de que obtendrá el próximo número disponible, por lo que ORDER BY puede no ser de ayuda.

Sin embargo, ampliando un poco el enfoque, tal vez agregue una columna de FECHA o TIMESTAMP en la tabla y luego use eso en ORDER BY. No he probado ese enfoque.

Volviendo al punto de AskTom, ¿hay alguna razón específica por la que quiera una secuencia sin espacios?

¿Qué sucede cuando una tubería de agua explota en la sala sobre la que están las cajas y 50 de las cajas están completamente y completamente dañadas más allá de su uso?

o alguien aplasta una caja por accidente.

o lo que sea

no es gapless en general. Y tampoco es un sistema asignado, secuencia libre de huecos ...

Answer 2

Puede dejar que la secuencia haga el trabajo de la clave principal y cree una vista de su tabla base, seleccionando

rownum como la columna en la que desea ver los números del 1 al N en orden secuencial:

SQL> create table your_table(
  2    tab_id number primary key,
  3    col    number
  4  )
  5  ;

Table created

SQL> create sequence gen_id;

Sequence created

SQL> create trigger TR_PK_your_table
  2  before insert on your_table
  3  for each row
  4  begin
  5    :new.tab_id := gen_id.nextval; -- This kind of assignment is allowed in 11g  
  6  end;                             -- and higher, in version prior to 11g 
  7  /                                -- conventional select statement is used

Trigger created

SQL> insert into your_table(col)
  2  select level 
  3    from dual
  4  connect by level <=7;

7 rows inserted

SQL> commit;

Commit complete

SQL> select *
  2    from your_table;

    TAB_ID        COL
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7

7 rows selected

SQL> create or replace view V_your_table
  2  as
  3  select tab_id
  4       , col
  5       , rownum as num
  6    from your_table
  7  ;

View created

SQL> select *
  2    from v_your_table;

    TAB_ID        COL        NUM
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5
         6          6          6
         7          7          7

7 rows selected

SQL> delete from your_table where tab_id in (3,5,6);

3 rows deleted

SQL> commit;

Commit complete

SQL> select *
  2    from your_table;

    TAB_ID        COL
---------- ----------
         1          1
         2          2
         4          4
         7          7

SQL> select *
  2    from v_your_table;

    TAB_ID        COL        NUM
---------- ---------- ----------
         1          1          1
         2          2          2
         4          4          3
         7          7          4

SQL>