[PLSQL] Gibt es ein "Select oder Insert"- Befehl?

[sirius]

Wenn man einen Datensatz updaten möchte, aber nicht sicher ist, ob er bereits existiert kann man das Kommando "MERGE INTO" nutzen. Welches in einem Abwasch bspw. eine "Select count.."-Abfrage macht und dann entscheidet, ob ein Insert oder ein update notwendig ist.

Ich brauche dies aber des öftern für einen anderen Konstrukt. Ich möchte eine Entscheidung ob ich ein "Select ID ..." oder ein "Insert ... returning ID ..." brauche in einem Befehl (ID ist bei mir immer der PK) Da ich vermute, dass ich damit nicht der einzige bin (ich im Netz aber nichts finde; bei "Select oder Insert" findet man zu viel), würde ich gern mal wissen, ob es dafür etwas gibt.
Bisher löse ich das folgendermaßen:

SQL-Code:

Select count(ID) into c from ...

if c=0 then
  begin
    insert into dbt_Channel ...
      returning ID into result;    
    commit;
  exception
    when dup_val_on_index then
      c:=1;  -- falls zwischenzeitlich ein zweiter User einen Datensatz eingefügt hat?!?
    when others then
      raise;
  end;  
end if;

if c>0 then
  Select ID into result ... 
end if;

return(result);

[himitsu]

Ob es bei PLSQL geht, weiß ich nicht, aber die SQL-Syntax kennt da ein "Insert or Update".

Es ist praktisch ein INSERT und falls es Probleme mit doppelten/vorhandenen Keys gibt, dann wird das UPDATE versucht.

SQL-Code:

INSERT INTO `table` ( 'field1', 'field2' ) VALUES ( 'value1', 'value2' )
ON DUPLICATE KEY UPDATE 'field2' = 'value2'

field1 ist hierbei z.B. ein Primary-/Unique-Key und deshalb hab ich ihn beim Update weggelassen, da er sich ja eh nicht ändert
und über diesen schon der zu ändernde Datensatz ausgewählt wurde.

[mkinzler]

Liefert dir MERGE die ID nicht zuück?

Unter FireBird könnte man
Update or insert ... matching ... returning ID; verwenden

[sirius]

Oh Sorry,

ich sehe, ich habe die Frage falsch erklärt. Eine "insert-update" Variante gibt es (nennt sich "Merge Into"). Was ich suche ist eine "Select - Insert returning" Variante (wie im Codebesipiel als umständliche Langform gezeigt).

Schonmal Danke für Eure Bemühungen.

Edit: Wobei mir deine Syntax, himi, auch neu ist (schön wenn man mal redet, da lernt man immer was), ich sie aber hier nicht brauche.

[HeZa]

Irgendwie hast du mehr Code als nötig.

SQL-Code:

begin
  insert into dbt_Channel ...
    returning ID into result;    
  commit;
  return(result);
exception
  when dup_val_on_index then
    return ID;  -- falls zwischenzeitlich ein zweiter User einen Datensatz eingefügt hat?!?
  when others then
    raise;
end;

[/QUOTE]

[sirius]

Hi HeZa,

jo, so kann man es auch machen. Es bestehen aber zwei Nachteile:
1. Exception, die man vorher abfangen kann sind doof (und normalerweise auch sehr performanceraubend, zumal in dem Fall bis auf den ersten Eintrag immer die Exception geworfen wird)
2. Da auf das Insert ein Trigger (inkl. sequence) angelegt ist, welcher die ID setzen soll (=AutoInc bei mySQL), läuft der auch hoch, obwohl das Insert schief gelaufen ist.

[shmia]

Das Grundproblem ist die ungeeignete Erzeugung des Wertes für den Primärschlüssel.
Select count(ID) into c from ... Wehe es wird ein Datensatz gelöscht - dann gerät das ganze System durcheinander.
Besser ist da:
Select Max(ID)+1 into c from ... Aber auch das ist nicht wasserdicht wenn mehr als ein Prozess/Thread das abarbeitet.
Was man hier wirklich bräuchte wäre ein Generator.
Dann würde man zuerst mit dem Generator eine "neue Nummer ziehen" und könnte dann sicher sein, dass es keine Kollisionen von anderen Prozessen gibt.

[himitsu]

Zitat von shmia:

und könnte dann sicher sein, dass es keine Kollisionen von anderen Prozessen gibt.

Kann man Tabellen nicht auch sperren?

[Mschmidt]

Zitat von himitsu:

Zitat von shmia:

und könnte dann sicher sein, dass es keine Kollisionen von anderen Prozessen gibt.

Kann man Tabellen nicht auch sperren?

jeap select ... for Update [nowait];

[sirius]

@shmia:
Das ist nicht das Problem. Mit count will ich ja nicht den Wert ermitteln um den neuen Primärschlüssel (ID) zu erzeugen, sondern nur, ob der Datensatz (mit meiner ID) schon vorhanden ist, oder nicht. Zur Erzeugung der ID nutze ich natürlich eine Sequence (was in Oracle wohl dasselbe ist wie ein Generator bei firebird). Und diese wird hier per Trigger (auf before Insert) zugeführt.

@himi: Ja, man kann die Tabelle sperren. Finde ich aber nicht als geeignetes Mittel für so eine kurze Sache.


Ich machs jetzt Mal an einem meiner zahlreichen konkreten Umsetzungen ausführlich

1. Tabelle

SQL-Code:

CREATE TABLE "DBT_ZEIT" 
   (   "ID_ZEIT" NUMBER NOT NULL ENABLE, 
   "DATUM" DATE NOT NULL ENABLE, 
   "STUNDE" NUMBER(2,0) NOT NULL ENABLE, 
    CONSTRAINT "DBT_ZEIT_PK" PRIMARY KEY ("ID_ZEIT") ENABLE, 
    CONSTRAINT "DBT_ZEIT_UK1" UNIQUE ("DATUM", "STUNDE") ENABLE
   ) ;

2. Sequence und Trigger

SQL-Code:

CREATE SEQUENCE  "SEQ_ZEIT"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER "TRG_ZEIT" 
BEFORE INSERT ON DBT_ZEIT 
FOR EACH ROW 
BEGIN
 SELECT SEQ_Zeit.NEXTVAL INTO :NEW.ID_Zeit FROM DUAL;
END;

Soweit alles ok. Die Sequence und Trigger entsprechen, wie gesagt, dem AutoInc bei mySQL. Erzeugt werden die beiden Sachen beim SQL Developer automatisch mit der Tabellenerzeugung, wenn man in dem Wizard entsprechend die Angaben setzt.

Jetzt kommt die SP mit besagtem unschönen Code:

SQL-Code:

create or replace
PROCEDURE "ADDTRAFFIC" 
(aZeit in Date,
 aSrcIP in VarChar2,
 aDestIP in VarChar2,
 aTraffic in Number
) is
nTraffic64 Number(10) := 0;
nDatum Date;
nStunde Number(2);
id Number;
cnt Integer;
begin
  if aTraffic<64 then
    nTraffic64:=64-aTraffic;
  end if;
  
  nDatum:=trunc(aZeit);
  nStunde:=Extract(Hour from to_timestamp(aZeit));
  
    
  -- ****** Hier beginnt der Problembereich ******************************
  select count(id_zeit) into cnt from dbt_Zeit where
        Datum=nDatum and
        Stunde=nStunde;
  if cnt=0 then
    begin
      insert into dbt_Zeit (Datum,Stunde) 
        Values (nDatum,nStunde)
        returning ID_Zeit into id;
    exception
      when dup_val_on_index then
        cnt:=1;
      when others then
        raise;
    end;
  end if;
  if cnt>0 then
    select id_zeit into id from dbt_Zeit where
        Datum=nDatum and
        Stunde=nStunde;  
  end if;
  -- *** Ende des Problembreiches *************************************
  
  --commit;
  
  
  -- Für einen Insert or update gibt es folgenden Konstrukt:
  merge into dbt_Traffic
    using
      (Select count(Traffic) as cnt from dbt_Traffic
       where ID_Zeit=id
       and (SrcIP=aSRCIP or (SrcIP is null and aSRCIP is null ))
       and (DestIP=aDestIP or (DestIP is null and aDestIP is null))) e
    on (e.cnt>0)   
  WHEN MATCHED THEN
    update set 
        Traffic=Traffic+aTraffic,
        Traffic64=Traffic64+nTraffic64,
        Count_=Count_+1
        where ID_Zeit=id 
        and (SrcIP=aSRCIP or (SrcIP is null and aSRCIP is null ))
        and (DestIP=aDestIP or (DestIP is null and aDestIP is null))
  WHEN NOT MATCHED THEN
    insert (ID_Zeit,SRCIP,DestIP,Traffic,Traffic64)
      Values(id,aSrcIP,aDestIP,aTraffic,nTraffic64);  
  
  
  
  
  dbms_alert.signal('NewTraffic',to_char(nDatum,'dd.mm.yyyy') || ' ' ||
    to_char(nStunde) || ' ' ||
    aSRCIP || ' ' ||
    aDestIP || ' ' ||
    aTraffic || ' ' ||
    nTraffic64 || ' ' ||
    '1');
  
  commit;
 
end;

Also mich interessiert der Block ab "Select count(ID_Zeit) ..." (Problembereich). Kann man den in einen einzigen Befehl fassen (und ohne Exceptions nutzen).

@Borwin (roter Kasten)
Das ist das, was ich derzeit mache (nur von der anderen Seite aus). Nur ich hätte es gern ohne Exceptions gelöst (also ohne, dass ich mich bewusst auf eine Exception verlasse, denn das ist nach meinem Wissen nicht der Sinn einer Exception=Ausnahme). In deinem Beispiel würde noch eine Exception beim Insert geworfen, die du abfangen müsstest, wenn zwischenzeitlich ein anderer User den Eintrag gemacht hat.