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

[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.

[borwin]

Schaue mal auf die zweite Variante. Die ist ohne Exception.

Zitat:

declare
vlb_found boolean := false;
begin
for rec in (select * from tabelle where ID = parameter) loop
vlb_found := true;
exit;
end loop;
if not vlb_found then
-- neuen Datensatz einfügen
insert into tabelle .....
end if;
end;

Gruß Borwin

[sirius]

Ok, aber

1. Die eine Exception ist weg. Aber ich bleine noch bei meiner ursprünglichen.
Denn wie ist es denn gesichert, dass 2 User nicht gerade gleichzeitig diese SP ausführen?
Beide haben kurz hintereinander fesgestellt, dass dieser Datensatz noch nicht existiert (und zwar bevor ein User diesen einträgt). Jetzt versuchen beide User den Datensatz einzutragen. Einer wird eine Exception bekommen (dup_val_on_index), die es abzufangen gilt. Letztenendes bin ich dann wieder bei meiner Variante.
2. Dauert das nicht, alle Datensätze erst durchzugehen?

[borwin]

Zitat:

2. Dauert das nicht, alle Datensätze erst durchzugehen?

Nein. Das ist ggf. Schnelle als ein Count, da er nicht gleich alle Daten fetch. Das muss er für einen Count machen.
Nach dem ersten Datensatz geht er ja mit EXIT aus dem loop.

Gruß Borwin

[idefix2]

Ich verstehe nicht ganz, was Du eigentlich willst, bzw., wo das problem liegt.

Normalerweise ist der Primärschlüssel id doch ein willkürlicher Wert, der nur zur Identifikation des Datensatzes dient. Wenn Du diesen Wert mittels Trigger und Sequence erzeugst, ist er zwangsläufig eindeutig. Sobald Du den Sequencewert abrufst, ist er schon verbraucht, und wenn der nächste einen Datensatz in die Tabelle schreibt, bekommt er über den Trigger ohnedies eine neue Nummer. Es ist also vom System her gar nicht möglich, dass das Datenbanksysstem versucht, eine id ein zweites mal zu vergeben.

[mkinzler]

U.U sind aber Lücken nicht erwünscht

[idefix2]

Wenn man davon ausgeht, dass praktisch in allen Anwendungen gelegentlich Datensätze auch wieder gelöscht werden, sind Lücken nicht wirklich vermeidbar. Irgendeine Programmlogik auf lückenlos fortlaufende Nummern der Datensätze aufzubauen würde ich als Designfehler einstufen.

[HeZa]

@sirius

Irgendwie finde ich das ganze Konstrukt nicht so griffig.

Erzeuge doch alle Datensätze der Tabelle DBT_ZEIT im voraus. Das sind 876000 Datensätze einer sehr schmalen Tabelle für die nächsten 100 Jahre.

Damit sparst du dir

• das Insert
• die Sequenz
• den Trigger
• das abfangen der Exception dup_val_on_index

Das hat für die Robustheit und Performance der SP nur Vorteile und der Code wird auch übersichtlicher und damit auch verständlicher.

[Sherlock]

Wenn Du Lücken vermeiden willst (das scheint sich als Hintergrund herauszukristallisieren), nutze nicht Sequences und Trigger, sondern vergib selber die ID.

Sherlock

[sirius]

Danke Euch für die Antworten,

@HeHa, ja in dem Fall könnte man das vielleicht sogar so lösen (weis nicht, ob das so üblich ist). Aber ich sagte ja, dass dies nur ein Beispiel unter vielen ist. Ich schreib halt immer wieder die selbe Befehlssequenz die da besteht aus

• Ermitteln ob der Datanesatz schon vorhanden ist
• Wenn ja, dann PK holen
• Wenn nein dann Insert probieren
  • falls Insert fehl schlägt (Exception abfangen) dann PK von dem inzwischen eigentragenen Datensatz holen

Und das halt nicht nur bei dbt_Zeit, sondern auch für verschiedene andere Tabellen, wo das nicht so geht. (Ich wollt ja erst nicht ein Beispiel posten, weil ich schon erwartete, dass die Antworten dann zu konkret auf das Beispiel bezogen sind) Also nehmt das Beispiel nur als Beispiel. Ich wollt hier nur einmal wissen, wie andere Datenbanknutzer in ihren Projekten damit umgegangen sind (ich bin ja hoffentlich nicht der erste) und ob es zufällig sogar einen Befehl gibt in Oracle.

zu den Lücken: Lücken kann man nicht vermeiden, aber man muss ja nicht schon von vorn herein im Konzept ca. 10000 IDs überspringen.

@idefix2: Ja, der nächste Datensatz hätte einen neuen PK, aber alle anderen Werte wären gleich. Ich hätte somit einen überflüssigen Eintrag. Dies ist nicht gewünscht und wird meistens durch einen unique constraint verhindert.

@borwin: Danke für den Code. Behebt zwar nicht das urspüngliche Problem, aber beschleunigt meine bisherige Lösung.

Danke Euch allen für Eure Beiträge. Aber ich befürchte langsam, dass ich der einzige bin, der ständig über dieses Problem stolpert.

Edit: @Sherlock, Selber PK zu vergeben ist aber ziemlich riskant. Aber wie gesagt, ist nicht das eigentliche Problem.