registro - Oracle: cómo INSERTAR si una fila no existe

pivot oracle columns to rows

Question

¿Cuál es la forma más fácil de INSERTAR una fila si no existe, en PL / SQL (oráculo)?

Quiero algo como:

IF NOT EXISTS (SELECT * FROM table WHERE name = ''jonny'') THEN
  INSERT INTO table VALUES ("jonny", null);
END IF;

Pero no está funcionando.

Nota: esta tabla tiene 2 campos, por ejemplo, nombre y edad . Pero solo el nombre es PK.

Answer 1

Además de las respuestas perfectas y válidas dadas hasta ahora, también existe la sugerencia ignore_row_on_dupkey_index que quizás quieras usar:

create table tq84_a (
  name varchar2 (20) primary key,
  age  number
);

insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values (''Johnny'',   77);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values (''Pete''  ,   28);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values (''Sue''   ,   35);
insert /*+ ignore_row_on_dupkey_index(tq84_a(name)) */ into tq84_a values (''Johnny'', null);

select * from tq84_a;

La pista está descrita en Tahiti .

Answer 2

Encontré los ejemplos un poco difíciles de seguir para la situación en la que desea asegurarse de que exista una fila en la tabla de destino (especialmente cuando tiene dos columnas como clave principal), pero la clave principal puede no existir allí, por lo que no hay nada para seleccionar.

Esto es lo que funcionó para mí:

MERGE INTO table1 D
    USING (
        -- These are the row(s) you want to insert.
        SELECT 
        ''val1'' AS FIELD_A,
        ''val2'' AS FIELD_B
        FROM DUAL

    ) S ON (
        -- This is the criteria to find the above row(s) in the
        -- destination table.  S refers to the rows in the SELECT
        -- statement above, D refers to the destination table.
        D.FIELD_A = S.FIELD_A
        AND D.FIELD_B = S.FIELD_B
    )

    -- This is the INSERT statement to run for each row that
    -- doesn''t exist in the destination table.
    WHEN NOT MATCHED THEN INSERT (
        FIELD_A,
        FIELD_B,
        FIELD_C
    ) VALUES (
        S.FIELD_A,
        S.FIELD_B,
        ''val3''
    )

Los puntos clave son:

• La instrucción SELECT dentro del bloque USING siempre debe devolver filas. Si no se devuelven filas de esta consulta, no se insertarán ni actualizarán filas. Aquí selecciono desde DUAL por lo que siempre habrá exactamente una fila.
• La condición ON es lo que establece los criterios para las filas coincidentes. Si ON no tiene una coincidencia, se ejecuta la instrucción INSERT.
• También puede agregar una WHEN MATCHED THEN UPDATE si desea tener más control sobre las actualizaciones también.

Answer 3

Si el nombre es un PK, simplemente inserta y detecta el error. La razón para hacer esto en lugar de cualquier control es que funcionará incluso con múltiples clientes insertando al mismo tiempo. Si marca y luego inserta, debe mantener un bloqueo durante ese tiempo, o esperar el error de todos modos.

El código para esto sería algo así como

BEGIN
  INSERT INTO table( name, age )
    VALUES( ''johnny'', null );
EXCEPTION
  WHEN dup_val_on_index
  THEN
    NULL; -- Intentionally ignore duplicates
END;

Answer 4

Suponiendo que tiene 10g, también puede usar la instrucción MERGE. Esto le permite insertar la fila si no existe e ignorar la fila si existe. La gente tiende a pensar en MERGE cuando quieren hacer un "upsert" (INSERTAR si la fila no existe y ACTUALIZAR si la fila existe) pero la parte ACTUALIZAR ahora es opcional para que también se pueda usar aquí.

SQL> create table foo (
  2    name varchar2(10) primary key,
  3    age  number
  4  );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  merge into foo a
  2    using (select ''johnny'' name, null age from dual) b
  3       on (a.name = b.name)
  4   when not matched then
  5    insert( name, age)
  6*    values( b.name, b.age)
SQL> /

1 row merged.

SQL> /

0 rows merged.

SQL> select * from foo;

NAME              AGE
---------- ----------
johnny

Answer 5

Utilizando partes de @benoit answer, usaré esto:

DECLARE
    varTmp NUMBER:=0;
BEGIN
    -- checks
    SELECT nvl((SELECT 1 FROM table WHERE name = ''john''), 0) INTO varTmp FROM dual;

    -- insert
    IF (varTmp = 1) THEN
        INSERT INTO table (john, null)
    END IF;

END;

Lo siento, no utilizo ninguna respuesta dada, pero necesito verificación IF porque mi código es mucho más complejo que esta tabla de ejemplo con campos de nombre y edad. Necesito un código muy claro. ¡Bien, gracias, aprendí mucho! Aceptaré la respuesta de @benoit.

Answer 6

CTE y solo CTE :-)

solo tira cosas extra. Aquí hay una forma casi completa y detallada para todos los casos de la vida. Y puedes usar cualquier forma concisa.

INSERT INTO reports r
  (r.id, r.name, r.key, r.param)

-

  -- Invoke this script from "WITH" to the end (";")
  -- to debug and see prepared values.
  WITH

  -- Some new data to add.
  newData AS(
          SELECT ''Name 1'' name, ''key_new_1'' key FROM DUAL
    UNION SELECT ''Name 2'' NAME, ''key_new_2'' key FROM DUAL
    UNION SELECT ''Name 3'' NAME, ''key_new_3'' key FROM DUAL
    ),
  -- Any single row for copying with each new row from "newData",
  -- if you will of course.
  copyData AS(
      SELECT r.*
      FROM reports r
      WHERE r.key = ''key_existing''
        -- ! Prevent more than one row to return.
        AND FALSE -- do something here for than!
    ),
  -- Last used ID from the "reports" table (it depends on your case).
  -- (not going to work with concurrent transactions)
  maxId AS (SELECT MAX(id) AS id FROM reports),

-

  -- Some construction of all data for insertion.
  SELECT maxId.id + ROWNUM, newData.name, newData.key, copyData.param
  FROM copyData
    -- matrix multiplication :)
    -- (or a recursion if you''re imperative coder)
    CROSS JOIN newData
    CROSS JOIN maxId

-

  -- Let''s prevent re-insertion.
  WHERE NOT EXISTS (
      SELECT 1 FROM reports rs
      WHERE rs.name IN(
        SELECT name FROM newData
      ));

Lo llamo " SI NO EXISTE " con esteroides. Entonces, esto me ayuda a mí y principalmente lo hago.

Answer 7

puedes usar esta sintaxis:

INSERT INTO table_name ( name, age )
select  ''jonny'', 18 from dual
where not exists(select 1 from table_name where name = ''jonny'');

si abre una ventana emergente para preguntar como "ingresar variable de sustitución", utilícela antes de las consultas anteriores:

set define off;
INSERT INTO table_name ( name, age )
select  ''jonny'', 18 from dual
where not exists(select 1 from table_name where name = ''jonny'');

Answer 8

INSERT INTO table
SELECT ''jonny'', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = ''jonny''
                  )