sql - new - Inserción en Oracle y recuperación de la ID de secuencia generada

rownum oracle

Question

Tengo un puñado de consultas SQL sin procesar para SQL Server que usan SCOPE_IDENTITY para recuperar el ID generado para un INSERT específico inmediatamente después de que INSERT ocurre todo en una ejecución ...

INSERT into Batch(
BatchName,
BatchType,
Source,
Area
) Values (
@strBatchName,
@strType,
@strSource,
@intArea
);

SELECT SCOPE_IDENTITY() BatchID;

La pregunta es:

¿Cuál es la mejor manera de hacer eso para una base de datos Oracle?

¿Se puede hacer esto en Oracle a través de SQL estándar o tengo que cambiar esto para usar un procedimiento almacenado y colocar algo similar en el cuerpo del proceso almacenado?

Si debe ser un proceso almacenado, ¿cuál es la forma estándar de facto para recuperar el último número de secuencia generado, teniendo en cuenta que probablemente habrá ejecuciones superpuestas en varios hilos, por lo que este mecanismo tendrá que recuperar el ID y el ID generados correctamente no necesariamente la última identificación generada.

Si dos se ejecutan simultáneamente, cada uno debe devolver la ID generada correcta de cada llamada respectiva. Tenga en cuenta que no estoy usando "@@ IDENTITY" de SQL Server debido a la naturaleza multiproceso de las llamadas.

Preferiría mantenerlo como SQL sin procesar, si es posible, ya que es mucho más fácil para mí administrarlo en todas las plataformas (archivo único que contiene el bloque SQL de cada plataforma separado por las etiquetas de identificación DBMS). Los procesos almacenados me resultan un poco más difíciles de manejar, pero puedo hacerlo de la única manera posible.

Answer 1

Ampliando un poco las respuestas de @Guru y @Ronnis, puede ocultar la secuencia y hacer que se vea más como un autoincremento usando un desencadenador, y tener un procedimiento que haga la inserción por usted y devuelva la ID generada como una salida parámetro.

create table batch(batchid number,
    batchname varchar2(30),
    batchtype char(1),
    source char(1),
    intarea number)
/

create sequence batch_seq start with 1
/

create trigger batch_bi
before insert on batch
for each row
begin
    select batch_seq.nextval into :new.batchid from dual;
end;
/

create procedure insert_batch(v_batchname batch.batchname%TYPE,
    v_batchtype batch.batchtype%TYPE,
    v_source batch.source%TYPE,
    v_intarea batch.intarea%TYPE,
    v_batchid out batch.batchid%TYPE)
as
begin
    insert into batch(batchname, batchtype, source, intarea)
    values(v_batchname, v_batchtype, v_source, v_intarea)
    returning batchid into v_batchid;
end;
/

A continuación, puede llamar al procedimiento en lugar de hacer una inserción simple, por ejemplo, desde un bloque anónimo:

declare
    l_batchid batch.batchid%TYPE;
begin
    insert_batch(v_batchname => ''Batch 1'',
        v_batchtype => ''A'',
        v_source => ''Z'',
        v_intarea => 1,
        v_batchid => l_batchid);
    dbms_output.put_line(''Generated id: '' || l_batchid);

    insert_batch(v_batchname => ''Batch 99'',
        v_batchtype => ''B'',
        v_source => ''Y'',
        v_intarea => 9,
        v_batchid => l_batchid);
    dbms_output.put_line(''Generated id: '' || l_batchid);
end;
/

Generated id: 1
Generated id: 2

Puede realizar la llamada sin un bloqueo anónimo explícito, por ejemplo, desde SQL * Plus:

variable l_batchid number;
exec insert_batch(''Batch 21'', ''C'', ''X'', 7, :l_batchid);

... y use la variable de enlace :l_batchid para referirse al valor generado después:

print l_batchid;
insert into some_table values(:l_batch_id, ...);

Answer 2

Hacerlo como un procedimiento almacenado tiene muchas ventajas. Puede obtener la secuencia que se inserta en la tabla utilizando la insert into table_name values returning sintaxis insert into table_name values returning tabla que insert into table_name values returning .

Me gusta:

declare
some_seq_val  number;
lv_seq        number;
begin
some_seq_val := your_seq.nextval;
insert into your_tab (col1, col2, col3) 
values (some_seq_val, val2, val3) returning some_seq_val into lv_seq;

dbms_output.put_line(''The inserted sequence is: ''||to_char(lv_seq));
end;
/

O simplemente devuelve some_seq_val . En caso de que no esté haciendo uso de SEQUENCE, y al llegar la secuencia en algún cálculo, puede hacer uso de returning into efectivamente.

Answer 3

No hay funciones de incremento automático en Oracle para una columna. Necesita crear un objeto SEQUENCE. Puedes usar la secuencia como:

insert into table(batch_id, ...) values(my_sequence.nextval, ...)

... para devolver el siguiente número. Para saber la última secuencia creada nr (en su sesión), usaría:

my_sequence.currval

Este sitio tiene varios ejemplos completos sobre cómo usar secuencias.

Answer 4

Puede hacer esto con una declaración única, suponiendo que la está llamando desde un conector similar a JDBC con funcionalidad de parámetros de entrada / salida:

insert into batch(batchid, batchname) 
values (batch_seq.nextval, ''new batch'')
returning batchid into :l_batchid;

o, como un script pl-sql:

variable l_batchid number;

insert into batch(batchid, batchname) 
values (batch_seq.nextval, ''new batch'')
returning batchid into :l_batchid;

select :l_batchid from dual;

Answer 5

Puede usar la declaración a continuación para obtener el ID insertado a una variable similar.

INSERT INTO  YOUR_TABLE(ID) VALUES (''10'') returning ID into :Inserted_Value;

Ahora puede recuperar el valor usando la declaración siguiente

SELECT :Inserted_Value FROM DUAL;