FirebirdSQL logo
 COMMENTProzedurale SQL-Anweisungen (PSQL) 

Zusammengesetzte Pläne

Bei einem Join können Sie den Index angeben, der für den Abgleich verwendet werden soll.Sie müssen auch die JOIN-Direktive für die beiden Streams im Plan verwenden:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s natural, c index (pk_classes));

Erweiterter Plan:

Select Expression
  -> Nested Loop Join (inner)
    -> Table "STUDENTS" as "S" Full Scan
    -> Filter
      -> Table "CLASSES" as "C" Access By ID
        -> Bitmap
          -> Index "PK_CLASSES" Unique Scan

Dieselbe Verknüpfung, sortiert nach einer indizierten Spalte:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s order pk_students, c index (pk_classes))
  order by s.id;

Erweiterter Plan:

Select Expression
  -> Nested Loop Join (inner)
    -> Table "STUDENTS" as "S" Access By ID
      -> Index "PK_STUDENTS" Full Scan
    -> Filter
      -> Table "CLASSES" as "C" Access By ID
        -> Bitmap
          -> Index "PK_CLASSES" Unique Scan

Und für eine nicht indizierte Spalte:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan sort (join (s natural, c index (pk_classes)))
  order by s.name;

Erweiterter Plan:

Select Expression
  -> Sort (record length: 152, key length: 12)
    -> Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
        -> Table "CLASSES" as "C" Access By ID
          -> Bitmap
            -> Index "PK_CLASSES" Unique Scan

Mit einer hinzugefügten Suchbedingung:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  where s.class <= '2'
  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  order by s.name;

Erweiterter Plan:

Select Expression
  -> Sort (record length: 152, key length: 12)
    -> Nested Loop Join (inner)
      -> Filter
        -> Table "STUDENTS" as "S" Access By ID
          -> Bitmap
            -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
      -> Filter
        -> Table "CLASSES" as "C" Access By ID
          -> Bitmap
            -> Index "PK_CLASSES" Unique Scan

Als Left Outer Join:

select s.id, s.name, s.class, c.mentor
  from classes c
  left join students s on c.name = s.class
  where s.class <= '2'
  plan sort (join (c natural, s index (fk_student_class)))
  order by s.name;

Erweiterter Plan:

Select Expression
  -> Sort (record length: 192, key length: 56)
    -> Filter
      -> Nested Loop Join (outer)
        -> Table "CLASSES" as "C" Full Scan
        -> Filter
          -> Table "STUDENTS" as "S" Access By ID
            -> Bitmap
              -> Index "FK_STUDENT_CLASS" Range Scan (full match)

Wenn keine Indizes verfügbar sind, die der Join-Bedingung entsprechen (oder wenn Sie sie nicht verwenden möchten), können Sie die Streams mit der Methode HASH oder MERGE verbinden.

Um eine Verbindung mit der HASH-Methode im Plan herzustellen, wird die HASH-Direktive anstelle der JOIN-Direktive verwendet.In diesem Fall wird der kleinere (sekundäre) Strom vollständig in einem internen Puffer materialisiert.Beim Lesen dieses sekundären Streams wird eine Hash-Funktion angewendet und ein Paar {Hash, Zeiger auf Puffer} in eine Hash-Tabelle geschrieben.Dann wird der primäre Stream gelesen und sein Hash-Schlüssel wird gegen die Hash-Tabelle getestet.

select *
  from students s
  join classes c on c.cookie = s.cookie
  plan hash (c natural, s natural)

Erweiterter Plan:

Select Expression
  -> Filter
    -> Hash Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Record Buffer (record length: 145)
        -> Table "CLASSES" as "C" Full Scan

Für einen 'MERGE'-Join muss der Plan zuerst beide Streams in deren Join-Spalte(n) sortieren und dann zusammenführen.Dies wird mit der SORT-Direktive (die wir bereits gesehen haben) und MERGE statt JOIN erreicht:

select * from students s
  join classes c on c.cookie = s.cookie
  plan merge (sort (c natural), sort (s natural));

Das Hinzufügen einer ORDER BY-Klausel bedeutet, dass das Ergebnis der Zusammenführung ebenfalls sortiert werden muss:

select * from students s
  join classes c on c.cookie = s.cookie
  plan sort (merge (sort (c natural), sort (s natural)))
  order by c.name, s.id;

Schließlich fügen wir eine Suchbedingung für zwei indizierbare Spalten der Tabelle STUDENTS hinzu:

select * from students s
  join classes c on c.cookie = s.cookie
  where s.id < 10 and s.class <= '2'
  plan sort (merge (sort (c natural),
                    sort (s index (pk_students, fk_student_class))))
  order by c.name, s.id;

Wie aus der formalen Syntaxdefinition hervorgeht, können JOINs und MERGEs im Plan mehr als zwei Streams kombinieren.Außerdem kann jeder Planausdruck als Planelement in einem umfassenden Plan verwendet werden.Dies bedeutet, dass Pläne bestimmter komplizierter Abfragen verschiedene Verschachtelungsebenen haben können.

Schließlich können Sie statt MERGE auch SORT MERGE schreiben.Da dies absolut keinen Unterschied macht und zu Verwirrung mit “real” SORT-Direktiven führen kann (die einen Unterschied machen), ist es wahrscheinlich am besten, beim einfachen MERGE zu bleiben.

Neben dem Plan für die Hauptabfrage können Sie für jede Unterabfrage einen Plan angeben.Die folgende Abfrage mit mehreren Plänen funktioniert beispielsweise:

select *
from color
where exists (
  select *
  from hors
  where horse.code_color = color.code_color
  plan (horse index (fk_horse_color)))
plan (color natural)
Warning

Gelegentlich akzeptiert der Optimierer einen Plan und folgt ihm dann nicht, obwohl er ihn nicht als ungültig zurückweist.Ein solches Beispiel war

MERGE (unsorted stream, unsorted stream)

Es ist ratsam, einen solchen Plan als “veraltet” zu behandeln.

UNION

Die UNION-Klausel verkettet zwei oder mehr Datensätze und erhöht somit die Anzahl der Zeilen, aber nicht die Anzahl der Spalten.Datensätze, die an einer UNION teilnehmen, müssen die gleiche Anzahl von Spalten haben und die Spalten an den entsprechenden Positionen müssen vom gleichen Typ sein.Abgesehen davon können sie völlig unabhängig sein.

Standardmäßig unterdrückt eine Vereinigung doppelte Zeilen.UNION ALL zeigt alle Zeilen, einschließlich aller Duplikate.Das optionale Schlüsselwort DISTINCT macht das Standardverhalten explizit.

Syntax
<union> ::=
  <individual-select>
  UNION [{DISTINCT | ALL}]
  <individual-select>
  [
    [UNION [{DISTINCT | ALL}]
    <individual-select>
    ...
  ]
  [<union-wide-clauses>]

<individual-select> ::=
  SELECT
  [TRANSACTION name]
  [FIRST m] [SKIP n]
  [{DISTINCT | ALL}] <columns>
  [INTO <host-varlist>]
  FROM <source> [[AS] alias]
  [<joins>]
  [WHERE <condition>]
  [GROUP BY <grouping-list>
  [HAVING <aggregate-condition>]]
  [PLAN <plan-expr>]

<union-wide-clauses> ::=
  [ORDER BY <ordering-list>]
  [{ ROWS <m> [TO <n>]
   | [OFFSET n {ROW | ROWS}]
     [FETCH {FIRST | NEXT} [m] {ROW | ROWS} ONLY]
  }]
  [FOR UPDATE [OF <columns>]]
  [WITH LOCK]
  [INTO <PSQL-varlist>]

Unions beziehen ihre Spaltennamen aus der ersten Auswahlabfrage.Wenn Sie Union-Spalten mit einem Alias versehen möchten, tun Sie dies in der Spaltenliste des obersten SELECT.Aliase in anderen teilnehmenden Selects sind erlaubt und können sogar nützlich sein, werden aber nicht auf Gewerkschaftsebene verbreitet.

Wenn eine Union eine ORDER BY-Klausel hat, sind die einzigen zulässigen Sortierelemente Integer-Literale, die 1-basierte Spaltenpositionen angeben, optional gefolgt von einem ASC/DESC und/oder einem NULLS {FIRST | LAST}-Anweisung.Dies impliziert auch, dass Sie eine Union nicht nach etwas sortieren können, das keine Spalte in der Union ist.(Sie können es jedoch in eine abgeleitete Tabelle einschließen, die Ihnen alle üblichen Sortieroptionen zurückgibt.)

Unions sind in Unterabfragen jeglicher Art erlaubt und können selbst Unterabfragen enthalten.Sie können auch Joins enthalten und an einem Join teilnehmen, wenn sie in eine abgeleitete Tabelle eingeschlossen sind.

Beispiele

Diese Abfrage präsentiert Informationen aus verschiedenen Musiksammlungen in einem Datensatz unter Verwendung von Unions:

select id, title, artist, length, 'CD' as medium
  from cds
union
select id, title, artist, length, 'LP'
  from records
union
select id, title, artist, length, 'MC'
  from cassettes
order by 3, 2  -- artist, title;

Wenn id, title, artist und length die einzigen beteiligten Felder in den Tabellen sind, kann die Abfrage auch so geschrieben werden:

select c.*, 'CD' as medium
  from cds c
union
select r.*, 'LP'
  from records r
union
select c.*, 'MC'
  from cassettes c
order by 3, 2  -- artist, title;

Die Qualifizierung der “Sterne” ist hier notwendig, da sie nicht das einzige Element in der Spaltenliste sind.Beachten Sie, dass die Aliase “c” in der ersten und dritten Auswahl nicht miteinander in Konflikt geraten: Ihre Gültigkeitsbereiche sind nicht unionsweit, sondern gelten nur für ihre jeweiligen select-Abfragen.

Die nächste Abfrage ruft Namen und Telefonnummern von Übersetzern und Korrektoren ab.Übersetzer, die auch als Korrektoren tätig sind, erscheinen nur einmal in der Ergebnismenge, sofern ihre Telefonnummer in beiden Tabellen gleich ist.Das gleiche Ergebnis kann ohne DISTINCT erreicht werden.Mit ALL würden diese Personen zweimal erscheinen.

select name, phone from translators
  union distinct
select name, telephone from proofreaders;

Eine UNION innerhalb einer Unterabfrage:

select name, phone, hourly_rate from clowns
where hourly_rate < all
  (select hourly_rate from jugglers
     union
   select hourly_rate from acrobats)
order by hourly_rate;

docnext count = 34

INSERT

Verwendet für

Einfügen von Datenzeilen in eine Tabelle

Verfügbar in

DSQL, ESQL, PSQL

Syntax
INSERT INTO target
  { DEFAULT VALUES
  | [(<column_list>)] [<override_opt>] <value_source> }
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name [, col_name ...]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<value_source> ::= VALUES (<value_list>) | <select_stmt>

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> :: = <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Arguments for the INSERT-Anweisungsparameter
Argument Beschreibung

target

Der Name der Tabelle oder Ansicht, zu der eine neue Zeile oder ein Zeilenstapel hinzugefügt werden soll

col_name

Spalte in der Tabelle oder Ansicht

value_expression

Ein Ausdruck, dessen Wert zum Einfügen in die Tabelle oder zum Zurückgeben verwendet wird

return_expression

Der in der RETURNING-Klausel zurückzugebende Ausdruck

literal

Ein Literal

context-variable

Kontextvariable

varname

Name einer lokalen PSQL-Variablen

Die INSERT-Anweisung wird verwendet, um einer Tabelle oder einer oder mehreren Tabellen, die einer Ansicht zugrunde liegen, Zeilen hinzuzufügen:

  • Wenn die Spaltenwerte in einer VALUES-Klausel übergeben werden, wird genau eine Zeile eingefügt

  • Die Werte können stattdessen durch einen SELECT-Ausdruck bereitgestellt werden, in diesem Fall können null bis viele Zeilen eingefügt werden

  • Bei der DEFAULT VALUES-Klausel werden überhaupt keine Werte angegeben und genau eine Zeile eingefügt.

Note
Einschränkungen

  • Spalten, die an die Kontextvariablen NEW.column_name in Triggern zurückgegeben werden, sollten keinen Doppelpunkt (“:”) vor ihrem Namen haben

  • In der Spaltenliste darf keine Spalte mehr als einmal vorkommen.
Important
ALERT : BEFORE INSERT-Triggers

Achten Sie unabhängig von der zum Einfügen von Zeilen verwendeten Methode auf alle Spalten in der Zieltabelle oder -ansicht, die von BEFORE INSERT-Triggern gefüllt werden, wie z. B. Primärschlüssel und Suchspalten, bei denen die Groß-/Kleinschreibung nicht beachtet wird.Diese Spalten sollten sowohl aus der column_list als auch aus der VALUES-Liste ausgeschlossen werden, wenn die Trigger den NEW.column_name wie gewünscht auf NULL testen.

INSERT …​ VALUES

Die VALUES-Liste muss für jede Spalte in der Spaltenliste einen Wert in der gleichen Reihenfolge und vom richtigen Typ liefern.Die Spaltenliste muss nicht jede Spalte im Ziel angeben, aber wenn die Spaltenliste nicht vorhanden ist, benötigt die Engine einen Wert für jede Spalte in der Tabelle oder Ansicht (ohne berechnete Spalten).

Der Wert DEFAULT ermöglicht die Angabe einer Spalte in der Spaltenliste, weist Firebird jedoch an, den Standardwert zu verwenden (entweder NULL oder der in der DEFAULT-Klausel der Spaltendefinition angegebene Wert).Bei Identitätsspalten wird durch die Angabe von DEFAULT der Identitätswert generiert.Es ist möglich, berechnete Spalten in die Spaltenliste aufzunehmen und DEFAULT als Spaltenwert anzugeben.

Note

Einführungssyntax bietet eine Möglichkeit, den Zeichensatz eines Werts zu identifizieren, der eine Zeichenfolgenkonstante (Literal) ist.Die Introducer-Syntax funktioniert nur mit Literal-Strings: Sie kann nicht auf String-Variablen, Parameter, Spaltenreferenzen oder Werte angewendet werden, die Ausdrücke sind.
Beispiele
INSERT INTO cars (make, model, year)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

-- notice the '_' prefix (introducer syntax)
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

INSERT …​ SELECT

Bei dieser Einfügemethode müssen die Ausgabespalten der SELECT-Anweisung für jede Zielspalte in der Spaltenliste einen Wert in der gleichen Reihenfolge und vom richtigen Typ liefern.

Literale Werte, Kontextvariablen oder Ausdrücke kompatiblen Typs können für jede Spalte in der Quellzeile ersetzt werden.In diesem Fall werden eine Quellspaltenliste und eine entsprechende VALUES-Liste benötigt.

Wenn die Spaltenliste fehlt – wie bei der Verwendung von SELECT * für den Quellausdruck – muss die column_list die Namen jeder Spalte in der Zieltabelle oder Sicht enthalten (berechnete Spalten ausgeschlossen).

Beispiele
INSERT INTO cars (make, model, year)
  SELECT make, model, year
  FROM new_cars;

INSERT INTO cars
  SELECT * FROM new_cars;

INSERT INTO Members (number, name)
  SELECT number, name FROM NewMembers
    WHERE Accepted = 1
UNION ALL
  SELECT number, name FROM SuspendedMembers
    WHERE Vindicated = 1

INSERT INTO numbers(num)
  WITH RECURSIVE r(n) as (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 100
  )
SELECT n FROM r

Natürlich müssen die Spaltennamen in der Quelltabelle nicht mit denen in der Zieltabelle übereinstimmen.Jede Art von SELECT-Anweisung ist zulässig, solange ihre Ausgabespalten in Anzahl, Reihenfolge und Typ genau mit den Einfügespalten übereinstimmen.Typen müssen nicht exakt gleich sein, aber sie müssen zuweisungskompatibel sein.

Important

Bei der Verwendung von und INSERT …​ SELECT mit einer RETURNING-Klausel muss das SELECT höchstens eine Zeile produzieren, da RETURNING derzeit nur für Anweisungen funktioniert, die höchstens eine Zeile betreffen.

Dieses Verhalten kann sich in zukünftigen Firebird-Versionen ändern.

INSERT …​ DEFAULT VALUES

Die DEFAULT VALUES-Klausel ermöglicht das Einfügen eines Datensatzes, ohne irgendwelche Werte bereitzustellen, entweder direkt oder aus einer SELECT-Anweisung.Dies ist nur möglich, wenn jede NOT NULL- oder CHECK-Spalte in der Tabelle entweder einen gültigen Standardwert deklariert hat oder einen solchen Wert von einem BEFORE INSERT-Trigger erhält.Darüber hinaus dürfen Trigger, die erforderliche Feldwerte bereitstellen, nicht vom Vorhandensein von Eingabewerten abhängen.

Die Angabe von DEFAULT VALUES entspricht der Angabe einer Werteliste mit dem Wert DEFAULT für alle Spalten.

Beispiel
INSERT INTO journal
  DEFAULT VALUES
RETURNING entry_id;

OVERRIDING

Die Klausel OVERRIDING steuert das Verhalten einer Identitätsspalte nur für diese Anweisung.

OVERRIDING SYSTEM VALUE

Der vom Benutzer bereitgestellte Wert für die Identitätsspalte wird verwendet, und es wird kein Wert mit der Identität generiert.Mit anderen Worten, für diese Einfügung verhält sich die Identität so, als ob sie GENERATED BY DEFAULT wäre.Diese Option kann nur für Tabellen mit einer GENERATED ALWAYS AS IDENTITY-Spalte angegeben werden.

Dies kann beim Zusammenführen oder Importieren von Daten aus einer anderen Quelle nützlich sein.Nach einer solchen Einfügung kann es erforderlich sein, den nächsten Wert der Identitätssequenz mit ALTER TABLE zu ändern, um zu verhindern, dass nachfolgende Einfügungen kollidierende Identitätswerte erzeugen.

OVERRIDING USER VALUE

Der vom Benutzer bereitgestellte Wert für die Identitätsspalte wird ignoriert und der Spaltenwert wird mithilfe der Identität generiert.Mit anderen Worten, für diese Einfügung verhält sich die Identität so, als ob sie ALWAYS GENERATED wäre, während die Identitätsspalte in der Spaltenliste zugelassen wird.Diese Option kann nur für Tabellen mit einer Spalte GENERATED BY DEFAULT AS IDENTITY angegeben werden.

Es ist normalerweise einfacher, die Identitätsspalte wegzulassen, um den gleichen Effekt zu erzielen.

Beispiele für OVERRIDING
-- Für GENERATED ALWAYS AS IDENTITY
-- wird immer der Wert 11 verwendet
insert into objects_always (id, name)
  OVERRIDING SYSTEM VALUE values (11, 'Laptop');

-- Für GENERATED BY DEFAULT AS IDENTITY
-- wird der Wert 12 nicht verwendet
insert into objects_default (id, name)
  OVERRIDING USER VALUE values (12, 'Laptop');

Die RETURNING-Klausel

Eine INSERT-Anweisung, die höchstens eine Zeile hinzufügt, kann optional eine RETURNING-Klausel enthalten, um Werte aus der eingefügten Zeile zurückzugeben.Die Klausel muss, falls vorhanden, nicht alle Einfügespalten enthalten und kann auch andere Spalten oder Ausdrücke enthalten.Die zurückgegebenen Werte spiegeln alle Änderungen wider, die möglicherweise an BEFORE INSERT-Triggern vorgenommen wurden.

Der Benutzer, der die Anweisung ausführt, benötigt SELECT-Berechtigungen für die in der RETURNING-Klausel angegebenen Spalten.

Die Syntax der returning_list ähnelt der Spaltenliste einer SELECT-Klausel.Es ist möglich, alle Spalten mit * oder table_name.* zu referenzieren.

Die optionale Unterklausel INTO ist nur in PSQL gültig.

Important
Mehrfache INSERTs

In DSQL gibt eine Anweisung mit RETURNING immer nur eine Zeile zurück.Wenn die RETURNING-Klausel angegeben ist und mehr als eine Zeile durch die INSERT-Anweisung eingefügt wird, schlägt die Anweisung fehl und es wird eine Fehlermeldung zurückgegeben.Dieses Verhalten kann sich in zukünftigen Firebird-Versionen ändern.
Beispiele
INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES ('Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (firstname, lastname, iq)
  SELECT fname, lname, iq
FROM Friends
  ORDER BY iq ROWS 1
  RETURNING id, firstname, iq
INTO :id, :fname, :iq;
Note

  • RETURNING wird für VALUES- und DEFAULT VALUES-Inserts und Singleton-SELECT-Inserts unterstützt.

  • In DSQL gibt eine Anweisung mit einer RETURNING-Klausel immer genau eine Zeile zurück.Wenn tatsächlich kein Datensatz eingefügt wurde, sind die Felder in dieser Zeile alle NULL.Dieses Verhalten kann sich in einer späteren Version von Firebird ändern.Wenn in PSQL keine Zeile eingefügt wurde, wird nichts zurückgegeben und die Zielvariablen behalten ihre vorhandenen Werte bei.

Einfügen in 'BLOB'-Spalten

Das Einfügen in 'BLOB'-Spalten ist nur unter folgenden Umständen möglich:

  1. Die Client-Anwendung hat spezielle Vorkehrungen für solche Einfügungen getroffen, indem sie die Firebird-API verwendet.In diesem Fall ist der modus operandi anwendungsspezifisch und liegt außerhalb des Rahmens dieses Handbuchs.

  2. Der eingefügte Wert ist ein Zeichenfolgenliteral von nicht mehr als 65.533 Byte (64 KB - 3).

    Note

    Ein Grenzwert in Zeichen wird zur Laufzeit für Zeichenfolgen berechnet, die sich in Mehrbytezeichensätzen befinden, um ein Überschreiten des Bytegrenzwertes zu vermeiden.Für einen UTF8-String (max. 4 Byte/Zeichen) liegt die Laufzeitbegrenzung beispielsweise bei (floor(65533/4)) = 16383 Zeichen.

  3. Sie verwenden das Formular “INSERT …​ SELECT” und eine oder mehrere Spalten in der Ergebnismenge sind BLOBs.

UPDATE

Verwendet für

Zeilen in Tabellen und Ansichten ändern

Verfügbar in

DSQL, ESQL, PSQL

Syntax
UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Argumente für die UPDATE-Anweisungsparameter
Argument Beschreibung

target

Der Name der Tabelle oder Ansicht, in der die Datensätze aktualisiert werden

alias

Alias für die Tabelle oder Ansicht

col_name

Name oder Alias einer Spalte in der Tabelle oder Ansicht

value_expression

Ausdruck für den neuen Wert für eine Spalte, die in der Tabelle oder Ansicht durch die Anweisung aktualisiert werden soll, oder einen zurückzugebenden Wert

search-conditions

Eine Suchbedingung, die die Menge der zu aktualisierenden Zeilen einschränkt

cursorname

Der Name des Cursors, über den die zu aktualisierende(n) Zeile(n) positioniert werden

plan_items

Klauseln im Abfrageplan

sort_items

Spalten, die in einer ORDER BY-Klausel aufgeführt sind

m, n

Integer-Ausdrücke zum Begrenzen der Anzahl der zu aktualisierenden Zeilen

ret_expression

Ein in der RETURNING-Klausel zurückzugebender Wert

literal

Ein Literal

context-variable

Kontextvariable

varname

Name einer lokalen PSQL-Variablen

Die UPDATE-Anweisung ändert Werte in einer Tabelle oder in einer oder mehreren Tabellen, die einer Ansicht zugrunde liegen.Die betroffenen Spalten werden in der SET-Klausel angegeben.Die betroffenen Zeilen können durch die Klauseln WHERE und ROWS eingeschränkt werden.Wenn weder 'WHERE' noch 'ROWS' vorhanden sind, werden alle Datensätze in der Tabelle aktualisiert.

Alias verwenden

Wenn Sie einer Tabelle oder Sicht einen Alias zuweisen, muss der Alias bei der Angabe von Spalten und auch in allen Spaltenreferenzen in anderen Klauseln verwendet werden.

Beispiel

Korrekte Verwendung

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Nicht möglich:

update Fruit F set Fruit.soort = 'pisang' where ...

Die SET-Klausel

In der SET-Klausel werden die Zuweisungsphrasen, die die Spalten mit den zu setzenden Werten enthalten, durch Kommas getrennt.In einer Zuweisungsphrase befinden sich links die Spaltennamen und rechts die Werte oder Ausdrücke, die die Zuweisungswerte enthalten.Eine Spalte darf nur einmal in der SET-Klausel enthalten sein.

In Ausdrücken auf der rechten Seite kann ein Spaltenname verwendet werden.In diesen Werten auf der rechten Seite wird immer der alte Wert der Spalte verwendet, auch wenn der Spalte bereits früher in der SET-Klausel ein neuer Wert zugewiesen wurde.

Die Verwendung des Wertes DEFAULT setzt die Spalte auf ihren Standardwert (entweder NULL oder der in der DEFAULT-Klausel der Spaltendefinition angegebene Wert).Für eine Identitätsspalte wird durch die Angabe von DEFAULT ein neuer Identitätswert generiert.Es ist möglich, berechnete Spalten in der SET-Klausel zu “aktualisieren”, wenn und nur wenn der zugewiesene Wert DEFAULT ist.

Note

Es ist nicht möglich, DEFAULT als Parameterwert zuzuweisen.
Hier ist ein Beispiel

Daten in der TSET-Tabelle:

A B
---
1 0
2 0

Die Anweisung:

UPDATE tset SET a = 5, b = a;

ändert die Werte in:

A B
---
5 1
5 2

Beachten Sie, dass die alten Werte (1 und 2) verwendet werden, um die Spalte b zu aktualisieren, auch nachdem der Spalte ein neuer Wert zugewiesen wurde (5).

Note

Es war nicht immer so.Vor Version 2.5 erhielten Spalten ihre neuen Werte sofort bei der Zuweisung.Es war ein nicht standardmäßiges Verhalten, das in Version 2.5 behoben wurde.

Um die Kompatibilität mit Legacy-Code zu gewährleisten, enthält die Konfigurationsdatei firebird.conf den Parameter OldSetClauseSemantics, der auf True (1) gesetzt werden kann, um das alte, schlechte Verhalten wiederherzustellen.Es handelt sich um eine vorübergehende Maßnahme – der Parameter wird in Zukunft entfernt.

Die WHERE-Klausel

Die WHERE-Klausel legt die Bedingungen fest, die die Menge der Datensätze für ein searched update begrenzen.

Wenn in PSQL ein benannter Cursor zum Aktualisieren einer Menge verwendet wird, ist die Aktion mit der WHERE CURRENT OF-Klausel auf die Zeile beschränkt, in der sich der Cursor gerade befindet.Dies ist ein positioniertes Update.

Note

Um die WHERE CURRENT OF-Klausel in SQL verwenden zu können, muss der Cursorname auf das Anweisungshandle gesetzt werden, bevor die Anweisung ausgeführt wird.
Beispiele
UPDATE People
  SET firstname = 'Boris'
  WHERE lastname = 'Johnson';

UPDATE employee e
  SET salary = salary * 1.05
  WHERE EXISTS(
         SELECT *
           FROM employee_project ep
           WHERE e.emp_no = ep.emp_no);

UPDATE addresses
  SET city = 'Saint Petersburg', citycode = 'PET'
  WHERE city = 'Leningrad'

UPDATE employees
  SET salary = 2.5 * salary
  WHERE title = 'CEO'

Für String-Literale, bei denen der Parser Hilfe benötigt, um den Zeichensatz der Daten zu interpretieren, kann die Introducer-Syntax verwendet werden.Dem Zeichenfolgenliteral geht der Zeichensatzname voran, dem ein Unterstrich vorangestellt ist:

-- beachten Sie das '_'-Präfix

UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

Die Klauseln ORDER BY und ROWS

Die Klauseln ORDER BY und ROWS sind nur sinnvoll, wenn sie zusammen verwendet werden.Sie können jedoch separat verwendet werden.

Wenn ROWS ein Argument hat, m, werden die zu aktualisierenden Zeilen auf die ersten m Zeilen beschränkt.

Hinweise

  • Wenn m > die Anzahl der verarbeiteten Zeilen ist, wird der gesamte Zeilensatz aktualisiert

  • Wenn m = 0, werden keine Zeilen aktualisiert

  • Wenn m < 0, tritt ein Fehler auf und das Update schlägt fehl

Wenn zwei Argumente verwendet werden, m und n, begrenzt ROWS die Zeilen, die aktualisiert werden, auf Zeilen von m bis einschließlich n.Beide Argumente sind ganze Zahlen und beginnen bei 1.

Hinweise

  • Wenn m > die Anzahl der verarbeiteten Zeilen ist, werden keine Zeilen aktualisiert

  • Wenn n > die Anzahl der Zeilen, werden Zeilen von m bis zum Ende des Satzes aktualisiert

  • Wenn m < 1 oder n < 1 ist, tritt ein Fehler auf und das Update schlägt fehl

  • Wenn n = m - 1, werden keine Zeilen aktualisiert

  • Wenn n < m -1, tritt ein Fehler auf und das Update schlägt fehl

ROWS-Beispiel
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

Die RETURNING-Klausel

Eine UPDATE-Anweisung, die höchstens eine Zeile umfasst, kann RETURNING enthalten, um einige Werte aus der aktualisierten Zeile zurückzugeben."RETURNING" kann Daten aus einer beliebigen Spalte der Zeile enthalten, nicht unbedingt aus den Spalten, die gerade aktualisiert werden.Es kann Literale oder Ausdrücke enthalten, die nicht mit Spalten verknüpft sind, wenn dies erforderlich ist.

Der Benutzer, der die Anweisung ausführt, benötigt SELECT-Berechtigungen für die in der RETURNING-Klausel angegebenen Spalten.

Wenn das RETURNING-Set Daten aus der aktuellen Zeile enthält, melden die zurückgegebenen Werte Änderungen, die in den BEFORE UPDATE-Triggern vorgenommen wurden, aber nicht die in AFTER UPDATE-Triggern.

Als Spaltennamen können die Kontextvariablen OLD.fieldname und NEW.fieldname verwendet werden.Wenn OLD. oder NEW. nicht angegeben wird, sind die zurückgegebenen Spaltenwerte die NEW.-Werte.

Die Syntax der returning_list ähnelt der Spaltenliste einer SELECT-Klausel.Es ist möglich, alle Spalten mit * oder table_name.*, NEW.* und/oder OLD.* zu referenzieren.

In DSQL gibt eine Anweisung mit RETURNING immer eine einzelne Zeile zurück.Versuche, ein UPDATE …​ RETURNING …​ auszuführen, das mehrere Zeilen betrifft, führen zu dem Fehler “multiple rows in singleton select”.Wenn die Anweisung keine Datensätze aktualisiert, enthalten die zurückgegebenen Werte NULL.Dieses Verhalten kann sich in zukünftigen Firebird-Versionen ändern.

Die INTO-Unterklausel

In PSQL kann die INTO-Klausel verwendet werden, um die Rückgabewerte an lokale Variablen zu übergeben.Es ist in DSQL nicht verfügbar.Wenn keine Datensätze aktualisiert werden, wird nichts zurückgegeben und die in RETURNING angegebenen Variablen behalten ihre vorherigen Werte.

RETURNING-Beispiel (DSQL)

UPDATE Scholars
SET firstname = 'Hugh', lastname = 'Pickering'
WHERE firstname = 'Henry' and lastname = 'Higgins'
RETURNING id, old.lastname, new.lastname;

'BLOB'-Spalten aktualisieren

Das Aktualisieren einer BLOB-Spalte ersetzt immer den gesamten Inhalt.Sogar die BLOB ID, das “handle”, das direkt in der Spalte gespeichert wird, wird geändert.BLOBs können aktualisiert werden, wenn:

  1. Die Client-Anwendung hat für diesen Vorgang spezielle Vorkehrungen getroffen, indem sie die Firebird-API verwendet.In diesem Fall ist der modus operandi anwendungsspezifisch und liegt außerhalb des Rahmens dieses Handbuchs.

  2. Der neue Wert ist ein Zeichenfolgenliteral von nicht mehr als 65.533 Byte (64 KB - 3).

    Note

    Ein Grenzwert in Zeichen wird zur Laufzeit für Zeichenfolgen berechnet, die sich in Mehrbytezeichensätzen befinden, um ein Überschreiten des Bytegrenzwertes zu vermeiden.Für einen UTF8-String (max. 4 Byte/Zeichen) liegt die Laufzeitbegrenzung beispielsweise bei (floor(65533/4)) = 16383 Zeichen.

  3. Die Quelle ist selbst eine 'BLOB'-Spalte oder allgemeiner ein Ausdruck, der ein 'BLOB' zurückgibt.

  4. Sie verwenden die Anweisung INSERT CURSOR (nur ESQL).

UPDATE OR INSERT

Verwendet für

Aktualisieren eines bestehenden Datensatzes in einer Tabelle oder, falls er nicht existiert, einfügen

Verfügbar in

DSQL, PSQL

Syntax
UPDATE OR INSERT INTO
  target [(<column_list>)]
  [<override_opt>]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name  [, col_name ...]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> ::= <value> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Argumente für den UPDATE OR INSERT-Anweisungsparameter
Argument Beschreibung

target

Der Name der Tabelle oder Ansicht, in der der/die Datensatz(e) aktualisiert oder ein neuer Datensatz eingefügt werden soll

col_name

Name einer Spalte in der Tabelle oder Ansicht

value_expression

Ein Ausdruck, dessen Wert zum Einfügen oder Aktualisieren der Tabelle oder zum Zurückgeben eines Werts verwendet werden soll

ret_expression

Ein in der RETURNING-Klausel zurückgegebener Ausdruck

varname

Variablenname – nur PSQL

UPDATE OR INSERT fügt einen neuen Datensatz ein oder aktualisiert einen oder mehrere bestehende Datensätze.Die durchgeführte Aktion hängt von den Werten ab, die für die Spalten in der MATCHING-Klausel (oder, falls letztere fehlt, im Primärschlüssel) bereitgestellt werden.Wenn Datensätze gefunden werden, die diesen Werten entsprechen, werden sie aktualisiert.Wenn nicht, wird ein neuer Datensatz eingefügt.Eine Übereinstimmung zählt nur, wenn alle Werte in den MATCHING- oder Primärschlüsselspalten gleich sind.Der Abgleich erfolgt mit dem Operator IS NOT DISTINCT, sodass ein NULL mit einem anderen übereinstimmt.

Note
Einschränkungen

  • Wenn die Tabelle keinen Primärschlüssel hat, ist die MATCHING-Klausel obligatorisch.

  • In der MATCHING-Liste sowie in der Update/Insert-Spaltenliste darf jeder Spaltenname nur einmal vorkommen.

  • Die Unterklausel “INTO <variables>” ist nur in PSQL verfügbar.

  • Bei Rückgabe von Werten in die Kontextvariable NEW darf diesem Namen kein Doppelpunkt vorangestellt werden (“:”).

Die RETURNING-Klausel

Die optionale RETURNING-Klausel, falls vorhanden, muss nicht alle in der Anweisung erwähnten Spalten enthalten und kann auch andere Spalten oder Ausdrücke enthalten.Die zurückgegebenen Werte spiegeln alle Änderungen wider, die möglicherweise in BEFORE-Triggern vorgenommen wurden, aber nicht in AFTER-Triggern. OLD.fieldname und NEW.fieldname können beide in der Liste der zurückzugebenden Spalten verwendet werden;für Feldnamen, denen keiner von diesen vorangeht, wird der neue Wert zurückgegeben.

Der Benutzer, der die Anweisung ausführt, benötigt SELECT-Berechtigungen für die in der RETURNING-Klausel angegebenen Spalten.

Die Syntax der returning_list ähnelt der Spaltenliste einer SELECT-Klausel.Es ist möglich, alle Spalten mit * oder table_name.*, NEW.* und/oder OLD.* zu referenzieren.

In DSQL gibt eine Anweisung mit einer RETURNING-Klausel immer genau eine Zeile zurück.Wenn eine RETURNING-Klausel vorhanden ist und mehr als ein übereinstimmender Datensatz gefunden wird, wird ein Fehler “multiple rows in singleton select” ausgegeben.Dieses Verhalten kann sich in einer späteren Version von Firebird ändern.

Die optionale Unterklausel INTO ist nur in PSQL gültig.

Beispiel für UPDATE OR INSERT

Ändern von Daten in einer Tabelle mit UPDATE OR INSERT in einem PSQL-Modul.Der Rückgabewert wird an eine lokale Variable übergeben, deren Doppelpunkt-Präfix optional ist.

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING rec_id into :id;

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING old.*, new.*;

DELETE

Verwendet für

Zeilen aus einer Tabelle oder Ansicht löschen

Verfügbar in

DSQL, ESQL, PSQL

Syntax
DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [RETURNING <returning_list> [INTO <variables>]]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Argumente der DELETE-Anweisungsparameter
Argument Beschreibung

target

Der Name der Tabelle oder Ansicht, aus der die Datensätze gelöscht werden sollen

alias

Alias für die Zieltabelle oder -ansicht

search-conditions

Suchbedingung, die den Satz von Zeilen einschränkt, die gelöscht werden sollen

cursorname

Der Name des Cursors, in dem der aktuelle Datensatz zum Löschen positioniert ist

plan_items

Abfrageplanklausel

sort_items

ORDER BY-Klausel

m, n

Integer-Ausdrücke zum Begrenzen der Anzahl der zu löschenden Zeilen

ret_expression

Ein in der RETURNING-Klausel zurückzugebender Ausdruck

value_expression

Ein Ausdruck, dessen Wert für die Rückgabe verwendet wird

varname

Name einer PSQL-Variablen

DELETE entfernt Zeilen aus einer Datenbanktabelle oder aus einer oder mehreren Tabellen, die einer Ansicht zugrunde liegen.WHERE- und ROWS-Klauseln können die Anzahl der gelöschten Zeilen begrenzen.Wenn weder WHERE noch ROWS vorhanden sind, entfernt DELETE alle Zeilen in der Relation.

Aliases

Wenn für die Zieltabelle oder -sicht ein Alias angegeben wird, muss dieser verwendet werden, um alle Feldnamenreferenzen in der DELETE-Anweisung zu qualifizieren.

Beispiele

Unterstützte Nutzung:

delete from Cities where name starting 'Alex';

delete from Cities where Cities.name starting 'Alex';

delete from Cities C where name starting 'Alex';

delete from Cities C where C.name starting 'Alex';

Nicht möglich:

delete from Cities C where Cities.name starting 'Alex';

WHERE

Die WHERE-Klausel legt die Bedingungen fest, die die Menge der Datensätze für ein searched delete begrenzen.

Wenn in PSQL ein benannter Cursor zum Löschen einer Menge verwendet wird, ist die Aktion mit der Klausel WHERE CURRENT OF auf die Zeile beschränkt, in der sich der Cursor gerade befindet.Dies ist ein positioniertes Löschen.

Note

Um die WHERE CURRENT OF-Klausel in SQL verwenden zu können, muss der Cursorname auf das Anweisungshandle gesetzt werden, bevor die Anweisung ausgeführt wird.
Beispiele
DELETE FROM People
  WHERE firstname <> 'Boris' AND lastname <> 'Johnson';

DELETE FROM employee e
  WHERE NOT EXISTS(
    SELECT *
    FROM employee_project ep
     WHERE e.emp_no = ep.emp_no);

DELETE FROM Cities
  WHERE CURRENT OF Cur_Cities;  -- ESQL and PSQL only

PLAN

Eine PLAN-Klausel ermöglicht es dem Benutzer, die Operation manuell zu optimieren.

Beispiel
DELETE FROM Submissions
  WHERE date_entered < '1-Jan-2002'
  PLAN (Submissions INDEX ix_subm_date);

ORDER BY und ROWS

Die ORDER BY-Klausel ordnet die Menge, bevor das eigentliche Löschen stattfindet.Es macht nur in Kombination mit ROWS Sinn, ist aber auch ohne gültig.

Die ROWS-Klausel begrenzt die Anzahl der zu löschenden Zeilen.Für die Argumente m und n können ganzzahlige Literale oder beliebige ganzzahlige Ausdrücke verwendet werden.

Wenn ROWS ein Argument hat, m, werden die zu löschenden Zeilen auf die ersten m Zeilen beschränkt.

Hinweise

  • Wenn m > die Anzahl der verarbeiteten Zeilen ist, wird der gesamte Satz von Zeilen gelöscht

  • Bei m = 0 werden keine Zeilen gelöscht

  • Wenn m < 0, tritt ein Fehler auf und das Löschen schlägt fehl

Wenn zwei Argumente verwendet werden, m und n, begrenzt ROWS die zu löschenden Zeilen auf Zeilen von m bis einschließlich n.Beide Argumente sind ganze Zahlen und beginnen bei 1.

Hinweise

  • Wenn m > die Anzahl der verarbeiteten Zeilen ist, werden keine Zeilen gelöscht

  • Wenn m > 0 und <= die Anzahl der Zeilen im Set und n außerhalb dieser Werte liegt, werden Zeilen von m bis zum Ende des Sets gelöscht

  • Wenn m < 1 oder n < 1 ist, tritt ein Fehler auf und das Löschen schlägt fehl

  • Wenn n = m - 1, werden keine Zeilen gelöscht

  • Wenn n < m -1, tritt ein Fehler auf und das Löschen schlägt fehl

Beispiele

Löschen des ältesten Kaufs:

DELETE FROM Purchases
  ORDER BY date ROWS 1;

Löschen des/der höchsten Custno(s):

DELETE FROM Sales
  ORDER BY custno DESC ROWS 1 to 10;

Löschen aller Verkäufe, ORDER BY-Klausel sinnlos:

DELETE FROM Sales
  ORDER BY custno DESC;

Löschen eines Datensatzes am Ende beginnend, also ab Z…​:

DELETE FROM popgroups
  ORDER BY name DESC ROWS 1;

Löschen der fünf ältesten Gruppen:

DELETE FROM popgroups
  ORDER BY formed ROWS 5;

Da keine Sortierung (ORDER BY) angegeben ist, werden 8 gefundene Datensätze, beginnend mit dem fünften, gelöscht:

DELETE FROM popgroups
  ROWS 5 TO 12;

RETURNING

Eine DELETE-Anweisung, die höchstens eine Zeile entfernt, kann optional eine RETURNING-Klausel enthalten, um Werte aus der gelöschten Zeile zurückzugeben.Die Klausel, falls vorhanden, muss nicht alle Spalten der Relation enthalten und kann auch andere Spalten oder Ausdrücke enthalten.

Der Benutzer, der die Anweisung ausführt, benötigt SELECT-Berechtigungen für die in der RETURNING-Klausel angegebenen Spalten.

Die Syntax der returning_list ähnelt der Spaltenliste einer SELECT-Klausel.Es ist möglich, alle Spalten mit * oder table_name.* zu referenzieren.

Note

  • In DSQL gibt eine Anweisung mit RETURNING immer ein Singleton zurück, niemals ein Set mit mehreren Zeilen.Wenn eine RETURNING-Klausel vorhanden ist und mehr als ein übereinstimmender Datensatz gefunden wird, wird ein Fehler “multiple rows in singleton select” ausgegeben.Wenn keine Datensätze gelöscht werden, enthalten die zurückgegebenen Spalten NULL.Dieses Verhalten kann sich in zukünftigen Firebird-Versionen ändern

  • Die INTO-Klausel ist nur in PSQL verfügbar

    • Wenn die Zeile nicht gelöscht wird, wird nichts zurückgegeben und die Zielvariablen behalten ihre Werte
Beispiele
DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING lastname, fullname, id;

DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING *;

DELETE FROM Dumbbells
  ORDER BY iq DESC
  ROWS 1
  RETURNING lastname, iq into :lname, :iq;

MERGE

Verwendet für

Zusammenführen von Daten aus einem Quellsatz in eine Zielrelation

Verfügbar in

DSQL, PSQL

Syntax
MERGE INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join_condition>
  <merge_when> [<merge_when> ...]
  [RETURNING <returning_list> [INTO <variables>]]

<merge_when> ::=
    <merge_when_matched>
  | <merge_when_not_matched>

<merge_when_matched> ::=
  WHEN MATCHED [ AND <condition> ] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<merge_when_not_matched> ::=
  WHEN NOT MATCHED [ AND <condition> ] THEN
  INSERT [( <column_list> )] [<override_opt>]
  VALUES ( <value_list> )

<source> ::= tablename | (<select_stmt>)

<assignment_list ::=
  col_name = <m_value> [, <col_name> = <m_value> ...]]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<column_list> ::= colname [, colname ...]

<value_list> ::= <m_value> [, <m_value> ...]

<m_value> ::= <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Argumente für die MERGE-Anweisungsparameter
Argument Beschreibung

target

Name der Zielbeziehung (Tabelle oder aktualisierbare Sicht)

source

Datenquelle.Dies kann eine Tabelle, eine Ansicht, eine gespeicherte Prozedur oder eine abgeleitete Tabelle sein

target_alias

Alias für die Zielbeziehung (Tabelle oder aktualisierbare Ansicht)

source_alias

Alias für die Quellrelation oder Menge

join_conditions

Die (ON) Bedingung(en) zum Abgleichen der Quelldatensätze mit denen im Ziel

condition

Zusätzliche Testbedingung in der Klausel WHEN MATCHED oder WHEN NOT MATCHED

tablename

Tabellen- oder Ansichtsname

select_stmt

Select-Anweisung der abgeleiteten Tabelle

col_name

Name einer Spalte in der Zielrelation

value_expression

Der einer Spalte in der Zieltabelle zugewiesene Wert.Dieser Ausdruck kann ein Literalwert, eine PSQL-Variable, eine Spalte aus der Quelle oder eine kompatible Kontextvariable sein

return_expression

Der in der RETURNING-Klausel zurückzugebende AusdruckKann ein Spaltenverweis auf Quelle oder Ziel oder ein Spaltenverweis des NEW- oder OLD-Kontexts des Ziels oder ein Wert sein.

ret_alias

Alias für den Wertausdruck in der RETURNING-Klausel

varname

Name einer lokalen PSQL-Variablen

Die 'MERGE'-Anweisung führt Datensätze aus der Quelle in eine Zieltabelle oder eine aktualisierbare Sicht zusammen.Die Quelle kann eine Tabelle, ein View oder “alles, was mit SELECT abfragen” können.Jeder Quelldatensatz wird verwendet, um einen oder mehrere Zieldatensätze zu aktualisieren, einen neuen Datensatz in die Zieltabelle einzufügen, einen Datensatz aus der Zieltabelle zu löschen oder nichts zu tun.

Welche Aktion ausgeführt wird, hängt von der angegebenen Join-Bedingung, der/den WHEN-Klausel(n) und der - optionalen - Bedingung in der WHEN-Klausel ab.Die Join-Bedingung und die Bedingung im WHEN enthalten normalerweise einen Vergleich von Feldern in den Quell- und Zielbeziehungen.

Mehrere WHEN MATCHED- und WHEN NOT MATCHED-Klauseln sind zulässig.Für jede Zeile in der Quelle werden die WHEN-Klauseln in der Reihenfolge überprüft, in der sie in der Anweisung angegeben sind.Wenn die Bedingung in der WHEN-Klausel nicht als wahr ausgewertet wird, wird die Klausel übersprungen und die nächste Klausel wird geprüft.Dies wird getan, bis die Bedingung für eine WHEN-Klausel wahr ist oder eine WHEN-Klausel ohne Bedingung zutrifft oder es keine WHEN-Klauseln mehr gibt.Wenn eine übereinstimmende Klausel gefunden wird, wird die mit der Klausel verknüpfte Aktion ausgeführt.Für jede Zeile in der Quelle wird höchstens eine Aktion ausgeführt.Wenn die Klausel WHEN MATCHED vorhanden ist und mehrere Datensätze mit einem einzigen Datensatz in der Zieltabelle übereinstimmen, wird ein Fehler ausgegeben.

Warning

Mindestens eine WHEN-Klausel muss vorhanden sein.

WHEN NOT MATCHED wird aus der Quellsicht ausgewertet, dh der in USING angegebenen Tabelle oder Menge.Es muss so funktionieren, denn wenn der Quelldatensatz nicht mit einem Zieldatensatz übereinstimmt, wird INSERT ausgeführt.Wenn es einen Zieldatensatz gibt, der nicht mit einem Quelldatensatz übereinstimmt, wird natürlich nichts unternommen.

Derzeit gibt die Variable ROW_COUNT den Wert 1 zurück, auch wenn mehr als ein Datensatz geändert oder eingefügt wird.Einzelheiten und Fortschritte finden Sie unter firebird#4722.

Die RETURNING-Klausel

Eine MERGE-Anweisung, die höchstens eine Zeile betrifft, kann eine RETURNING-Klausel enthalten, um hinzugefügte, geänderte oder entfernte Werte zurückzugeben.Wenn eine RETURNING-Klausel vorhanden ist und mehr als ein übereinstimmender Datensatz gefunden wird, wird ein Fehler “multiple rows in singleton select” ausgegeben.Die RETURNING-Klausel kann beliebige Spalten aus der Zieltabelle (oder aktualisierbaren View) sowie andere Spalten (zB aus der Quelle) und Ausdrücke enthalten.

Der Benutzer, der die Anweisung ausführt, benötigt SELECT-Berechtigungen für die in der RETURNING-Klausel angegebenen Spalten.

Die optionale Unterklausel INTO ist nur in PSQL gültig.

Note

Die Einschränkung, dass RETURNING nur mit einer Anweisung verwendet werden kann, die höchstens eine Zeile betrifft, könnte in einer zukünftigen Version entfernt werden.

Spaltennamen können durch das Präfix "OLD" oder "NEW" qualifiziert werden, um genau zu definieren, welcher Wert zurückgegeben werden soll: vor oder nach der Änderung. Die zurückgegebenen Werte enthalten die Änderungen, die von BEFORE-Triggern vorgenommen wurden.

Die Syntax der returning_list ähnelt der Spaltenliste einer SELECT-Klausel.Es ist möglich, alle Spalten mit * oder table_name.*, NEW.* und/oder OLD.* zu referenzieren.

Für die Aktion UPDATE oder INSERT verhalten sich unqualifizierte Spaltennamen oder solche, die durch den Zieltabellennamen oder Alias qualifiziert sind, als ob sie durch NEW qualifiziert wären, während sie für die DELETE Aktion wie durch OLD qualifiziert wären.

Das folgende Beispiel modifiziert das vorherige Beispiel, um eine Zeile zu betreffen, und fügt eine RETURNING-Klausel hinzu, um die alte und neue Warenmenge sowie die Differenz zwischen diesen Werten zurückzugeben.

Verwendung von MERGE mit einer RETURNING-Klausel
MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  AND SL.ID_PRODUCT =: ID_PRODUCT
  GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO : OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

Beispiele für MERGE

  1. Aktualisieren Sie Bücher, wenn vorhanden, oder fügen Sie einen neuen Datensatz hinzu, wenn Sie abwesend sind

    MERGE INTO books b
  USING purchases p
  ON p.title = b.title and p.type = 'bk'
  WHEN MATCHED THEN
    UPDATE SET b.desc = b.desc || '; ' || p.desc
  WHEN NOT MATCHED THEN
    INSERT (title, desc, bought) values (p.title, p.desc, p.bought);

  2. Verwenden einer abgeleiteten Tabelle

    MERGE INTO customers c
  USING (SELECT * from customers_delta WHERE id > 10) cd
  ON (c.id = cd.id)
  WHEN MATCHED THEN
    UPDATE SET name = cd.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) values (cd.id, cd.name);

  3. Zusammen mit einem rekursiven CTE

    MERGE INTO numbers
  USING (
    WITH RECURSIVE r(n) AS (
      SELECT 1 FROM rdb$database
      UNION ALL
      SELECT n+1 FROM r WHERE n < 200
    )
    SELECT n FROM r
  ) t
  ON numbers.num = t.n
  WHEN NOT MATCHED THEN
    INSERT(num) VALUES(t.n);

  4. Verwenden der DELETE-Klausel

    MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE

  5. Im folgenden Beispiel wird die Tabelle "PRODUCT_INVENTORY" täglich basierend auf den in der Tabelle "SALES_ORDER_LINE" verarbeiteten Bestellungen aktualisiert.Wenn der Lagerbestand des Produkts auf null oder darunter sinken würde, wird die Zeile für dieses Produkt aus der Tabelle PRODUCT_INVENTORY entfernt.

    MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM (SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
Siehe auch

[fblangref40-dml-select-de], [fblangref40-dml-insert-de], [fblangref40-dml-update-de], [fblangref40-dml-update-or-insert-de], [fblangref40-dml-delete-de]

EXECUTE PROCEDURE

Verwendet für

Ausführen einer gespeicherten Prozedur

Verfügbar in

DSQL, ESQL, PSQL

Syntax
EXECUTE PROCEDURE procname
   [{ <inparam-list | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname
Table 1. Arguments for the EXECUTE PROCEDURE-Anweisungsparameter
Argument Beschreibung

procname

Name der gespeicherten Prozedur

inparam

Ein Ausdruck, der den deklarierten Datentyp eines Eingabeparameters auswertet

varname

Eine PSQL-Variable, um den Rückgabewert zu erhalten

Führt eine ausführbare gespeicherte Prozedur aus, nimmt eine Liste mit einem oder mehreren Eingabeparametern, falls diese für die Prozedur definiert sind, und gibt einen einzeiligen Satz von Ausgabewerten zurück, wenn sie für die Prozedur definiert sind.

“Executable” Stored Procedure

Die EXECUTE PROCEDURE-Anweisung wird am häufigsten verwendet, um den Stil gespeicherter Prozeduren aufzurufen, die geschrieben werden, um auf der Serverseite eine Aufgabe zur Datenänderung auszuführen – solche, die keine SUSPEND-Anweisungen in ihrem Code enthalten.Sie können so konzipiert sein, dass sie eine Ergebnismenge, die nur aus einer Zeile besteht, die normalerweise über einen Satz von RETURNING_VALUES()-Variablen an eine andere gespeicherte Prozedur übergeben wird, die sie aufruft, zurückgeben.Clientschnittstellen verfügen normalerweise über einen API-Wrapper, der die Ausgabewerte in einen Einzelzeilenpuffer abrufen kann, wenn EXECUTE PROCEDURE in DSQL aufgerufen wird.

Das Aufrufen des anderen Stils von Stored Procedures - einer “selectable” - ist mit EXECUTE PROCEDURE möglich, aber es gibt nur die erste Zeile eines Ausgabesatzes zurück, der mit ziemlicher Sicherheit mehrzeilig ist.Auswählbare gespeicherte Prozeduren sind so konzipiert, dass sie durch eine SELECT-Anweisung aufgerufen werden und eine Ausgabe erzeugen, die sich wie eine virtuelle Tabelle verhält.

Note

  • In PSQL und DSQL können Eingabeparameter jeder Ausdruck sein, der in den erwarteten Typ aufgelöst wird.

  • Obwohl nach dem Namen der gespeicherten Prozedur keine Klammern erforderlich sind, um die Eingabeparameter einzuschließen, wird ihre Verwendung aus Gründen der guten Verwaltung empfohlen.

  • Wenn in einer Prozedur Ausgabeparameter definiert wurden, kann die `RETURNING_VALUES'-Klausel in PSQL verwendet werden, um sie in eine Liste zuvor deklarierter Variablen abzurufen, die in Reihenfolge, Datentyp und Anzahl mit den definierten Ausgabeparametern übereinstimmt.

  • Die Liste der RETURNING_VALUES kann optional in Klammern eingeschlossen werden und ihre Verwendung wird empfohlen.

  • Wenn DSQL-Anwendungen EXECUTE PROCEDURE unter Verwendung der Firebird-API oder einer Form von Wrapper dafür aufrufen, wird ein Puffer zum Empfangen der Ausgabezeile vorbereitet und die RETURNING_VALUES-Klausel wird nicht verwendet.

Beispiele für EXECUTE PROCEDURE

  1. In PSQL mit optionalen Doppelpunkten und ohne optionale Klammern:

    EXECUTE PROCEDURE MakeFullName
  :FirstName, :MiddleName, :LastName
  RETURNING_VALUES :FullName;

  2. In Firebirds Befehlszeilen-Dienstprogramm isql, mit Literalparametern und optionalen Klammern:

    EXECUTE PROCEDURE MakeFullName ('J', 'Edgar', 'Hoover');
    Note

    In DSQL (zB in isql) wird RETURNING_VALUES nicht verwendet.Eventuelle Ausgabewerte werden von der Anwendung erfasst und automatisch angezeigt.

  3. Ein PSQL-Beispiel mit Ausdrucksparametern und optionalen Klammern:

    EXECUTE PROCEDURE MakeFullName
  ('Mr./Mrs. ' || FirstName, MiddleName, upper(LastName))
  RETURNING_VALUES (FullName);

EXECUTE BLOCK

Verwendet für

Erstellen eines "anonymen" Blocks von PSQL-Code in DSQL zur sofortigen Ausführung

Verfügbar in

DSQL

Syntax
EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::=  <param_decl> = ? [, <inparams> ]

<outparams> ::=  <param_decl> [, <outparams>]

<param_decl> ::=
  paramname <domain_or_non_array_type> [NOT NULL] [COLLATE collation]

<domain_or_non_array_type> ::=
  !! Siehe auch Skalardatentypen-Syntax !!

<psql-module-body> ::=
  !! Siehe auch Syntax für Modul-Bodys !!
Table 1. Argumente für die EXECUTE BLOCK-Anweisungsparameter
Argument Beschreibung

param_decl

Name und Beschreibung eines Eingabe- oder Ausgabeparameters

paramname

Der Name eines Eingangs- oder Ausgangsparameters des Verfahrensblocks, bis zu 63 Zeichen lang.Der Name muss unter Ein- und Ausgabeparametern und lokalen Variablen im Block eindeutig sein

collation

Sortierreihenfolge

Führt einen Block von PSQL-Code wie eine gespeicherte Prozedur aus, optional mit Eingabe- und Ausgabeparametern und Variablendeklarationen.Dies ermöglicht dem Benutzer, PSQL "on-the-fly" in einem DSQL-Kontext auszuführen.

Beispiele

  1. In diesem Beispiel werden die Zahlen 0 bis 127 und die entsprechenden ASCII-Zeichen in die Tabelle ASCIITABLE eingefügt:

    EXECUTE BLOCK
AS
declare i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

  2. Das nächste Beispiel berechnet das geometrische Mittel zweier Zahlen und gibt es an den Benutzer zurück:

    EXECUTE BLOCK (x DOUBLE PRECISION = ?, y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = SQRT(x*y);
  SUSPEND;
END

    Da dieser Block Eingangsparameter hat, muss er zuerst vorbereitet werden.Anschließend können die Parameter eingestellt und der Block ausgeführt werden.Es hängt von der Client-Software ab, wie dies zu tun ist und ob es überhaupt möglich ist – siehe die Hinweise unten.

  3. Unser letztes Beispiel nimmt zwei ganzzahlige Werte an, kleinste und größte.Für alle Zahlen im Bereich kleinste…​größte gibt der Block die Zahl selbst, ihr Quadrat, ihren Kubus und ihre vierte Potenz aus.

    EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (number INT, square BIGINT, cube BIGINT, fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube   = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

    Auch hier hängt es von der Client-Software ab, ob und wie Sie die Parameterwerte einstellen können.

Eingabe- und Ausgabeparameter

Die Ausführung eines Blocks ohne Eingabeparameter sollte mit jedem Firebird-Client möglich sein, der es dem Benutzer erlaubt, eigene DSQL-Anweisungen einzugeben.Wenn es Eingabeparameter gibt, wird es schwieriger: Diese Parameter müssen ihre Werte erhalten, nachdem die Anweisung vorbereitet wurde, aber bevor sie ausgeführt wird.Dies erfordert besondere Vorkehrungen, die nicht jede Client-Anwendung bietet.(Firebirds eigenes isql zum Beispiel nicht.)

Der Server akzeptiert nur Fragezeichen (“?”) als Platzhalter für die Eingabewerte, nicht “:a”, “:MyParam” etc., oder wörtliche Werte.Client-Software unterstützt jedoch möglicherweise das Formular “:xxx” und wird es vorverarbeiten, bevor es an den Server gesendet wird.

Wenn der Block Ausgangsparameter hat, muss Sie SUSPEND verwenden, sonst wird nichts zurückgegeben.

Die Ausgabe wird immer in Form einer Ergebnismenge zurückgegeben, genau wie bei einer SELECT-Anweisung.Sie können RETURNING_VALUES nicht verwenden oder den Block INTO einige Variablen ausführen, selbst wenn es nur eine Ergebniszeile gibt.

PSQL-Links

Weitere Informationen zum Schreiben von PSQL finden Sie in Kapitel Procedural SQL (PSQL)-Anweisungen.

Statement-Terminatoren

Einige Editoren für SQL-Anweisungen – insbesondere das Dienstprogramm isql, das mit Firebird geliefert wird, und möglicherweise einige Editoren von Drittanbietern – verwenden eine interne Konvention, die erfordert, dass alle Anweisungen mit einem Semikolon abgeschlossen werden.Dies führt beim Codieren in diesen Umgebungen zu einem Konflikt mit der PSQL-Syntax.Wenn Sie dieses Problem und seine Lösung nicht kennen, lesen Sie bitte die Details im PSQL-Kapitel im Abschnitt Terminator in isql umschalten.