COMMENTProzedurale SQL-Anweisungen (PSQL)

Wie die Engine mit `WITH LOCK` umgeht

Wenn eine UPDATE-Anweisung versucht, auf einen Datensatz zuzugreifen, der durch eine andere Transaktion gesperrt ist, löst sie je nach TPB-Modus entweder eine Aktualisierungskonfliktausnahme aus oder wartet auf den Abschluss der Sperrtransaktion.Das Engine-Verhalten ist hier dasselbe, als ob dieser Datensatz bereits durch die Sperrtransaktion geändert worden wäre.

Bei Konflikten mit pessimistischen Sperren werden keine speziellen gdscodes zurückgegeben.

Die Engine garantiert, dass alle von einer expliziten Sperranweisung zurückgegebenen Datensätze tatsächlich gesperrt sind und ob die in der WHERE-Klausel angegebenen Suchbedingungen erfüllen, solange die Suchbedingungen nicht von anderen Tabellen abhängen, über Joins, Unterabfragen usw.Es garantiert auch, dass Zeilen, die die Suchbedingungen nicht erfüllen, nicht durch die Anweisung gesperrt werden.Es kann nicht garantieren, dass es keine Zeilen gibt, die zwar die Suchbedingungen erfüllen, aber nicht gesperrt sind.

Note	Diese Situation kann eintreten, wenn andere, parallele Transaktionen ihre Änderungen während der Ausführung der Sperranweisung festschreiben.

Die Engine sperrt Zeilen zum Abrufzeitpunkt.Dies hat wichtige Konsequenzen, wenn Sie mehrere Zeilen gleichzeitig sperren.Viele Zugriffsmethoden für Firebird-Datenbanken holen standardmäßig die Ausgabe in Paketen von einigen hundert Zeilen (“buffered fetches”).Die meisten Datenzugriffskomponenten können Ihnen die im zuletzt abgerufenen Paket enthaltenen Zeilen nicht liefern, wenn ein Fehler aufgetreten ist.

Fallstricke bei der Verwendung von `WITH LOCK`

Das Zurücksetzen eines impliziten oder expliziten Sicherungspunkts gibt Datensatzsperren frei, die unter diesem Sicherungspunkt vorgenommen wurden, benachrichtigt jedoch keine wartenden Transaktionen.Anwendungen sollten nicht von diesem Verhalten abhängen, da es in Zukunft geändert werden kann.
Während explizite Sperren verwendet werden können, um ungewöhnliche Aktualisierungskonfliktfehler zu verhindern und/oder zu behandeln, wird die Anzahl von Deadlock-Fehlern zunehmen, wenn Sie Ihre Sperrstrategie nicht sorgfältig entwerfen und sie rigoros kontrollieren.
Die meisten Anwendungen benötigen überhaupt keine expliziten Sperren.Die Hauptzwecke von expliziten Sperren sind:
1. um eine teure Behandlung von Updatekonfliktfehlern in stark belasteten Anwendungen zu vermeiden, und
2. um die Integrität von Objekten aufrechtzuerhalten, die einer relationalen Datenbank in einer Clusterumgebung zugeordnet sind.
Wenn Ihre Verwendung der expliziten Sperrung nicht in eine dieser beiden Kategorien fällt, ist dies der falsche Weg, die Aufgabe in Firebird zu erledigen.
Explizites Sperren ist eine erweiterte Funktion;missbrauche es nicht!Während Lösungen für diese Art von Problemen für Websites mit Tausenden von gleichzeitigen Autoren oder für ERP/CRM-Systeme, die in großen Unternehmen betrieben werden, sehr wichtig sein können, müssen die meisten Anwendungsprogramme unter solchen Bedingungen nicht funktionieren.

docnext count = 71

Beispiele für die explizite Sperrung

Einfach:

SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK;

Mehrere Zeilen, Verarbeitung nacheinander mit SQL-Cursor:

SELECT * FROM DOCUMENT WHERE PARENT_ID=?
  FOR UPDATE WITH LOCK;

`INTO`

Verwendet für

SELECT-Ausgabe an Variablen übergeben

Verfügbar in

PSQL

Syntax

In PSQL wird die INTO-Klausel ganz am Ende der SELECT-Anweisung platziert.

SELECT [...] <column-list>
FROM ...
[...]
[INTO <variable-list>]

<variable-list> ::= [:]psqlvar [, [:]psqlvar ...]

Note	Der Doppelpunkt-Präfix vor lokalen Variablennamen in PSQL ist in der `INTO`-Klausel optional.

In PSQL-Code (Trigger, Stored Procedures und ausführbare Blöcke) können die Ergebnisse einer SELECT-Anweisung zeilenweise in lokale Variablen geladen werden.Dies ist oft die einzige Möglichkeit, überhaupt etwas mit den zurückgegebenen Werten zu tun, es sei denn, es wird ein expliziter oder impliziter Cursorname angegeben.Anzahl, Reihenfolge und Typen der Variablen müssen mit den Spalten in der Ausgabezeile übereinstimmen.

Eine “plain” SELECT-Anweisung kann in PSQL nur verwendet werden, wenn sie höchstens eine Zeile zurückgibt, d.h. wenn es sich um eine singleton select handelt.Für mehrzeilige Selects bietet PSQL das Schleifenkonstrukt FOR SELECT, das später im PSQL-Kapitel besprochen wird.PSQL unterstützt auch die DECLARE CURSOR-Anweisung, die einen benannten Cursor an eine SELECT-Anweisung bindet.Der Cursor kann dann verwendet werden, um die Ergebnismenge zu durchlaufen.

Beispiele

Auswahl einiger aggregierter Werte und Übergabe an die zuvor deklarierten Variablen min_amt, avg_amt und max_amt:

select min(amount), avg(cast(amount as float)), max(amount)
  from orders
  where artno = 372218
  into min_amt, avg_amt, max_amt;

Note	Der `CAST` dient dazu, den Durchschnitt zu einer reellen Zahl zu machen;andernfalls, da `Betrag` vermutlich ein ganzzahliges Feld ist, würden SQL-Regeln es auf die nächste niedrigere ganze Zahl kürzen.

Ein PSQL-Trigger, der zwei Werte als 'BLOB'-Feld abruft (unter Verwendung der 'LIST()'-Funktion) und ihm 'INTO' ein drittes Feld zuweist:
```
select list(name, ', ')
  from persons p
  where p.id in (new.father, new.mother)
  into new.parentnames;
```

Common Table Expressions (“`WITH … AS … SELECT`”)

Verfügbar in

DSQL, PSQL

Syntax

<cte-construct> ::=
  <cte-defs>
  <main-query>

<cte-defs> ::= WITH [RECURSIVE] <cte> [, <cte> ...]

<cte> ::= name [(<column-list>)] AS (<cte-stmt>)

<column-list> ::= column-alias [, column-alias ...]

Table 1. Argumente für Common Table Expressions
Argument	Beschreibung
cte-stmt	Jede `SELECT`-Anweisung, einschließlich `UNION`
main-query	Die `SELECT`-Hauptanweisung, die sich auf die in der Präambel definierten CTEs beziehen kann
name	Alias für einen Tabellenausdruck
column-alias	Alias für eine Spalte in einem Tabellenausdruck

Ein allgemeiner Tabellenausdruck oder CTE kann als virtuelle Tabelle oder Ansicht beschrieben werden, die in einer Präambel einer Hauptabfrage definiert ist und nach der Ausführung der Hauptabfrage den Gültigkeitsbereich verlässt.Die Hauptabfrage kann auf alle CTEs verweisen, die in der Präambel definiert sind, als wären es reguläre Tabellen oder Ansichten.CTEs können rekursiv, d.h. selbstreferenzierend, aber nicht verschachtelt sein.

CTE-Hinweise

Eine CTE-Definition kann jede zulässige SELECT-Anweisung enthalten, solange sie keine eigene “WITH…”-Präambel hat (keine Verschachtelung).
CTEs, die für dieselbe Hauptabfrage definiert sind, können aufeinander verweisen, aber es sollte darauf geachtet werden, Schleifen zu vermeiden.
CTEs kann von überall in der Hauptabfrage referenziert werden.
Jeder CTE kann in der Hauptabfrage mehrfach referenziert werden, ggf. mit unterschiedlichen Aliasnamen.
In Klammern eingeschlossen können CTE-Konstrukte als Unterabfragen in SELECT-Anweisungen, aber auch in UPDATEs, MERGEs usw. verwendet werden.

In PSQL werden CTEs auch in FOR-Schleifenheadern unterstützt:

for
  with my_rivers as (select * from rivers where owner = 'me')
    select name, length from my_rivers into :rname, :rlen
do
begin
  ..
end

Beispiele

with dept_year_budget as (
  select fiscal_year,
         dept_no,
         sum(projected_budget) as budget
  from proj_dept_budget
  group by fiscal_year, dept_no
)
select d.dept_no,
       d.department,
       dyb_2008.budget as budget_08,
       dyb_2009.budget as budget_09
from department d
     left join dept_year_budget dyb_2008
       on d.dept_no = dyb_2008.dept_no
       and dyb_2008.fiscal_year = 2008
     left join dept_year_budget dyb_2009
       on d.dept_no = dyb_2009.dept_no
       and dyb_2009.fiscal_year = 2009
where exists (
  select * from proj_dept_budget b
  where d.dept_no = b.dept_no
);

Rekursive CTEs

Ein rekursiver (selbstreferenzierender) CTE ist eine UNION, die mindestens ein nicht-rekursives Element namens anchor haben muss.Das/die nicht-rekursive(n) Element(e) muss/müssen vor dem/den rekursiven Element(en) platziert werden.Rekursive Elemente sind miteinander und mit ihrem nicht-rekursiven Nachbarn durch UNION ALL-Operatoren verknüpft.Die Vereinigungen zwischen nicht-rekursiven Mitgliedern können von jedem Typ sein.

Rekursive CTEs erfordern, dass das Schlüsselwort RECURSIVE direkt nach WITH vorhanden ist.Jedes rekursive Unionsmitglied darf nur einmal auf sich selbst verweisen, und zwar in einer FROM-Klausel.

Ein großer Vorteil rekursiver CTEs besteht darin, dass sie weit weniger Speicher und CPU-Zyklen benötigen als eine entsprechende rekursive gespeicherte Prozedur.

Ausführungsmuster

Das Ausführungsmuster eines rekursiven CTE sieht wie folgt aus:

Die Engine beginnt mit der Ausführung von einem nicht-rekursiven Member.
Für jede ausgewertete Zeile beginnt es, jedes rekursive Element nacheinander auszuführen, wobei die aktuellen Werte aus der äußeren Zeile als Parameter verwendet werden.
Wenn die aktuell ausgeführte Instanz eines rekursiven Members keine Zeilen erzeugt, führt die Ausführung eine Schleife zurück und ruft die nächste Zeile aus der äußeren Ergebnismenge ab.

Beispiel für rekursive CTEs

WITH RECURSIVE DEPT_YEAR_BUDGET AS (
  SELECT
      FISCAL_YEAR,
      DEPT_NO,
      SUM(PROJECTED_BUDGET) BUDGET
  FROM PROJ_DEPT_BUDGET
  GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS (
  SELECT
      DEPT_NO,
      HEAD_DEPT,
      DEPARTMENT,
      CAST('' AS VARCHAR(255)) AS INDENT
  FROM DEPARTMENT
  WHERE HEAD_DEPT IS NULL
  UNION ALL
  SELECT
      D.DEPT_NO,
      D.HEAD_DEPT,
      D.DEPARTMENT,
      H.INDENT || ' '
  FROM DEPARTMENT D
    JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
)
SELECT
    D.DEPT_NO,
    D.INDENT || D.DEPARTMENT DEPARTMENT,
    DYB_2008.BUDGET AS BUDGET_08,
    DYB_2009.BUDGET AS BUDGET_09
FROM DEPT_TREE D
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
      (D.DEPT_NO = DYB_2008.DEPT_NO) AND
      (DYB_2008.FISCAL_YEAR = 2008)
    LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
      (D.DEPT_NO = DYB_2009.DEPT_NO) AND
      (DYB_2009.FISCAL_YEAR = 2009);

Das nächste Beispiel gibt den Stammbaum eines Pferdes zurück.Der Hauptunterschied besteht darin, dass die Rekursion in zwei Zweigen des Stammbaums gleichzeitig auftritt.

WITH RECURSIVE PEDIGREE (
  CODE_HORSE,
  CODE_FATHER,
  CODE_MOTHER,
  NAME,
  MARK,
  DEPTH)
AS (SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      CAST('' AS VARCHAR(80)),
      0
    FROM
      HORSE
    WHERE
      HORSE.CODE_HORSE = :CODE_HORSE
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'F' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
    WHERE
      PEDIGREE.DEPTH < :MAX_DEPTH
    UNION ALL
    SELECT
      HORSE.CODE_HORSE,
      HORSE.CODE_FATHER,
      HORSE.CODE_MOTHER,
      HORSE.NAME,
      'M' || PEDIGREE.MARK,
      PEDIGREE.DEPTH + 1
    FROM
      HORSE
      JOIN PEDIGREE
        ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
    WHERE
      PEDIGREE.DEPTH < :MAX_DEPTH
)
SELECT
  CODE_HORSE,
  NAME,
  MARK,
  DEPTH
FROM
  PEDIGREE

Hinweise zu rekursiven CTEs

Aggregate (DISTINCT, GROUP BY, HAVING) und Aggregatfunktionen (SUM, COUNT, MAX usw.) sind in rekursiven Unionselementen nicht erlaubt.
Eine rekursive Referenz kann nicht an einem Outer Join teilnehmen.
Die maximale Rekursionstiefe beträgt 1024.

Die `SELECT`-Spaltenliste

Die Spaltenliste enthält einen oder mehrere durch Kommas getrennte Wertausdrücke.Jeder Ausdruck stellt einen Wert für eine Ausgabespalte bereit.Alternativ kann * (“Hole Sternchen” oder “Hole alle”) verwendet werden, um für alle Spalten in einer Relation (d.h. einer Tabelle, View oder auswählbaren Stored Procedure) zu stehen.

Syntax

SELECT
  [...]
  [{DISTINCT | ALL}] <select_list>
  [...]
  FROM ...

<select_list> ::= * | <output_column> [, <output_column> ...]

<output_column> ::=
    <qualifier>.*
  | <value_expression> [COLLATE collation] [[AS] alias]

<value_expression> ::=
    [<qualifier>.]col_name
  | [<qualifier>.]selectable_SP_outparm
  | <literal>
  | <context-variable>
  | <function-call>
  | <single-value-subselect>
  | <CASE-construct>
  | any other expression returning a single
    value of a Firebird data type or NULL

<qualifier> ::= a relation name or alias

<function-call> ::=
    <normal_function>
  | <aggregate_function>
  | <window_function>

<normal_function> ::=
  !! Siehe auch Eingebaute Skalarfunktionen !!

<aggregate_function> ::=
  !! Siehe auch Aggregatfunktionen !!

<window_function> ::=
  !! Siehe auch Window-Funktionen !!

Table 1. Argumente für die `SELECT`-Spaltenliste
Argument	Beschreibung
qualifier	Name der Relation (View, Stored Procedure, abgeleitete Tabelle);oder ein Alias dafür
collation	Nur für zeichenartige Spalten: ein vorhandener und für den Zeichensatz der Daten gültiger Kollatierungsname
alias	Spalten- oder Feldalias
col_name	Name einer Tabellen- oder Ansichtsspalte
selectable_SP_outparm	Deklarierter Name eines Ausgabeparameters einer auswählbaren gespeicherten Prozedur
literal	Ein Literal
context-variable	Kontextvariable
function-call	Skalar-, Aggregat- oder Fensterfunktionsausdruck
single-value-subselect	Eine Unterabfrage, die einen Skalarwert zurückgibt (Singleton)
CASE-construct	CASE-Konstrukt, das Bedingungen für einen Rückgabewert setzt

Es ist immer gültig, einen Spaltennamen (oder “*”) mit dem Namen oder Alias der Tabelle, Ansicht oder auswählbaren SP, zu der er gehört, zu qualifizieren, gefolgt von einem Punkt (‘.’).Beispiel: Beziehungsname.Spaltenname, Beziehungsname.*, Alias.Spaltenname, Alias.*.Qualifizierend ist erforderlich, wenn der Spaltenname in mehr als einer Relation vorkommt, die an einem Join teilnimmt.Das Qualifizieren von “*” ist immer obligatorisch, wenn es nicht das einzige Element in der Spaltenliste ist.

Important

Aliase verbergen den ursprünglichen Beziehungsnamen: Sobald eine Tabelle, Ansicht oder Prozedur mit einem Alias versehen wurde, kann nur der Alias als Qualifizierer während der gesamten Abfrage verwendet werden.Der Beziehungsname selbst wird nicht mehr verfügbar.

Der Spaltenliste kann optional eines der Schlüsselwörter DISTINCT oder ALL vorangestellt werden:

DISTINCT filtert alle doppelten Zeilen heraus.Das heißt, wenn zwei oder mehr Zeilen in jeder entsprechenden Spalte die gleichen Werte haben, wird nur eine davon in die Ergebnismenge aufgenommen
ALL ist die Vorgabe: es gibt alle Zeilen zurück, einschließlich der Duplikate.ALL wird selten verwendet;es wird zur Einhaltung des SQL-Standards unterstützt.

Eine COLLATE-Klausel ändert das Aussehen der Spalte als solche nicht.Wenn die angegebene Sortierung jedoch die Groß-/Kleinschreibung oder die Akzentempfindlichkeit der Spalte ändert, kann dies Folgendes beeinflussen:

Die Reihenfolge, wenn auch eine ORDER BY-Klausel vorhanden ist und diese Spalte betrifft
Gruppierung, wenn die Spalte Teil einer GROUP BY-Klausel ist
Die abgerufenen Zeilen (und damit die Gesamtzahl der Zeilen in der Ergebnismenge), wenn DISTINCT verwendet wird

Beispiele für `SELECT`-Abfragen mit verschiedenen Arten von Spaltenlisten

Ein einfaches SELECT, das nur Spaltennamen verwendet:

select cust_id, cust_name, phone
  from customers
  where city = 'London'

Eine Abfrage mit einem Verkettungsausdruck und einem Funktionsaufruf in der Spaltenliste:

select 'Mr./Mrs. ' || lastname, street, zip, upper(city)
  from contacts
  where date_last_purchase(id) = current_date

Eine Abfrage mit zwei Unterauswahlen:

select p.fullname,
  (select name from classes c where c.id = p.class) as class,
  (select name from mentors m where m.id = p.mentor) as mentor
from pupils p

Die folgende Abfrage bewirkt dasselbe wie die vorherige, indem Joins anstelle von Subselects verwendet werden:

select p.fullname,
  c.name as class,
  m.name as mentor
  join classes c on c.id = p.class
from pupils p
  join mentors m on m.id = p.mentor

Diese Abfrage verwendet ein CASE-Konstrukt, um den richtigen Titel zu ermitteln, z.B.beim Senden von E-Mails an eine Person:

select case upper(sex)
    when 'F' then 'Mrs.'
    when 'M' then 'Mr.'
    else ''
  end as title,
  lastname,
  address
from employees

Abfrage über eine Fensterfunktion.Sortiert Mitarbeiter nach Gehalt.

SELECT
  id,
  salary,
  name ,
  DENSE_RANK() OVER (ORDER BY salary) AS EMP_RANK
FROM employees
ORDER BY salary;

Abfrage einer auswählbaren gespeicherten Prozedur:

select * from interesting_transactions(2010, 3, 'S')
  order by amount

Auswählen aus Spalten einer abgeleiteten Tabelle.Eine abgeleitete Tabelle ist eine SELECT-Anweisung in Klammern, deren Ergebnismenge in einer einschließenden Abfrage verwendet wird, als wäre es eine reguläre Tabelle oder Ansicht.Die abgeleitete Tabelle ist hier fett gedruckt:

select fieldcount,
  count(relation) as num_tables
from (select r.rdb$relation_name as relation,
        count(*) as fieldcount
      from rdb$relations r
        join rdb$relation_fields rf
          on rf.rdb$relation_name = r.rdb$relation_name
      group by relation)
group by fieldcount

Abfrage der Uhrzeit über eine Kontextvariable (CURRENT_TIME):

select current_time from rdb$database

Für diejenigen, die mit RDB$DATABASE nicht vertraut sind: Dies ist eine Systemtabelle, die in allen Firebird-Datenbanken vorhanden ist und garantiert genau eine Zeile enthält.Obwohl es nicht für diesen Zweck erstellt wurde, ist es unter Firebird-Programmierern zur Standardpraxis geworden, aus dieser Tabelle auszuwählen, wenn Sie “from Nothing” auswählen möchten, dh wenn Sie Daten benötigen, die nicht an eine Tabelle oder Ansicht gebunden sind, kann aber allein aus den Ausdrücken in den Ausgabespalten abgeleitet werden.Ein anderes Beispiel ist:

select power(12, 2) as twelve_squared, power(12, 3) as twelve_cubed
  from rdb$database

Schließlich ein Beispiel, in dem Sie einige aussagekräftige Informationen aus RDB$DATABASE selbst auswählen:

select rdb$character_set_name from rdb$database

Wie Sie vielleicht erraten haben, erhalten Sie dadurch den Standardzeichensatz der Datenbank.

Siehe auch

Funktionen, Aggregatfunktionen, Window-Funktionen>, <<fblangref40-contextvars-de,Kontextvariablen, CASE, Unterabfragen

Die `FROM`-Klausel

Die FROM-Klausel gibt die Quelle(n) an, aus der die Daten abgerufen werden sollen.In seiner einfachsten Form ist dies nur eine einzelne Tabelle oder Ansicht.Die Quelle kann jedoch auch eine auswählbare gespeicherte Prozedur, eine abgeleitete Tabelle oder ein allgemeiner Tabellenausdruck sein.Mehrere Quellen können mit verschiedenen Arten von Joins kombiniert werden.

Dieser Abschnitt konzentriert sich auf Single-Source-Selects.Joins werden in einem der folgenden Abschnitte behandelt.

Syntax

SELECT
  ...
  FROM <source>
  [<joins>]
  [...]

<source> ::=
  { table
  | view
  | selectable-stored-procedure [(<args>)]
  | <derived-table>
  | LATERAL <derived-table>
  | <common-table-expression>
  } [[AS] alias]

<derived-table> ::=
  (<select-statement>) [[AS] alias] [(<column-aliases>)]

<common-table-expression> ::=
  WITH [RECURSIVE] <cte-def> [, <cte-def> ...]
  <select-statement>

<cte-def> ::= name [(<column-aliases>)] AS (<select-statement>)

<column-aliases> ::= column-alias [, column-alias ...]

Table 1. Argumente für die `FROM`-Klausel
Argument	Beschreibung
table	Name einer Tabelle
view	Name einer Ansicht
selectable-stored-procedure	Name einer auswählbaren Stored Procedure
args	Selektierbare Argumente für gespeicherte Prozeduren
derived-table	Abgeleiteter Tabellenabfrageausdruck
cte-def	Common Table Expression (CTE)-Definition, einschließlich eines “ad hoc”-Namens
select-statement	Beliebige SELECT-Anweisung
column-aliases	Alias für eine Spalte in einer Beziehung, CTE oder abgeleiteten Tabelle
name	Der “ad hoc”-Name für einen CTE
alias	Der Alias einer Datenquelle (Tabelle, Sicht, Prozedur, CTE, abgeleitete Tabelle)

Auswählen mit `FROM` in einer Tabelle oder Ansicht

Bei der Auswahl aus einer einzelnen Tabelle oder Ansicht erfordert die FROM-Klausel nur den Namen.Ein Alias kann nützlich oder sogar notwendig sein, wenn es Unterabfragen gibt, die sich auf die Haupt-select-Anweisung beziehen (wie sie es oft tun — Unterabfragen wie diese werden als korrelierte Unterabfragen bezeichnet).

Beispiele

select id, name, sex, age from actors
where state = 'Ohio'

select * from birds
where type = 'flightless'
order by family, genus, species

select firstname,
  middlename,
  lastname,
  date_of_birth,
  (select name from schools s where p.school = s.id) schoolname
from pupils p
where year_started = '2012'
order by schoolname, date_of_birth

Important

Mischen Sie niemals Spaltennamen mit Spaltenaliasen!

Wenn Sie einen Alias für eine Tabelle oder einen View angeben, müssen Sie diesen Alias immer anstelle des Tabellennamens verwenden, wenn Sie die Spalten der Relation abfragen (und überall dort, wo Sie sonst auf Spalten verweisen, z. GROUP BY- und WHERE-Klauseln).

Richtige Verwendung:

SELECT PEARS
FROM FRUIT;

SELECT FRUIT.PEARS
FROM FRUIT;

SELECT PEARS
FROM FRUIT F;

SELECT F.PEARS
FROM FRUIT F;

Falsche Verwendung:

SELECT FRUIT.PEARS
FROM FRUIT F;

Auswählen von `FROM` einer gespeicherten Prozedur

Eine auswählbare gespeicherte Prozedur ist eine Prozedur, die:

enthält mindestens einen Ausgabeparameter und
verwendet das Schlüsselwort SUSPEND, damit der Aufrufer die Ausgabezeilen einzeln abrufen kann, genau wie bei der Auswahl aus einer Tabelle oder Ansicht.

Die Ausgabeparameter einer auswählbaren gespeicherten Prozedur entsprechen den Spalten einer regulären Tabelle.

Die Auswahl aus einer gespeicherten Prozedur ohne Eingabeparameter entspricht der Auswahl aus einer Tabelle oder Ansicht:

select * from suspicious_transactions
  where assignee = 'John'

Alle erforderlichen Eingabeparameter müssen nach dem Prozedurnamen in Klammern angegeben werden:

select name, az, alt from visible_stars('Brugge', current_date, '22:30')
  where alt >= 20
  order by az, alt

Werte für optionale Parameter (dh Parameter, für die Standardwerte definiert wurden) können weggelassen oder bereitgestellt werden.Wenn Sie sie jedoch nur teilweise bereitstellen, müssen sich die weggelassenen Parameter alle am Ende befinden.

Angenommen, die Prozedur visible_stars aus dem vorherigen Beispiel hat zwei optionale Parameter: min_magn (numeric(3,1)) und spectral_class (varchar(12)), sind die folgenden Abfragen gültig:

select name, az, alt
from visible_stars('Brugge', current_date, '22:30');

select name, az, alt
from visible_stars('Brugge', current_date, '22:30', 4.0);

select name, az, alt
from visible_stars('Brugge', current_date, '22:30', 4.0, 'G');

Dies ist jedoch nicht der Fall, da die Parameterliste ein “hole” enthält:

select name, az, alt
from visible_stars('Brugge', current_date, '22:30', 'G');

Ein Alias für eine auswählbare gespeicherte Prozedur wird nach der Parameterliste angegeben:

select
  number,
  (select name from contestants c where c.number = gw.number)
from get_winners('#34517', 'AMS') gw

Wenn Sie auf einen Ausgabeparameter (“column”) verweisen, indem Sie ihn mit dem vollständigen Prozedurnamen qualifizieren, sollte der Prozeduralias weggelassen werden:

select
  number,
  (select name from contestants c where c.number = get_winners.number)
from get_winners('#34517', 'AMS')

Siehe auch

Stored Procedures, CREATE PROCEDURE

Abfragen einer abgeleiteten Tabelle mittels `FROM`

Eine abgeleitete Tabelle ist eine gültige SELECT-Anweisung in Klammern, optional gefolgt von einem Tabellenalias und/oder Spaltenaliasen.Die Ergebnismenge der Anweisung fungiert als virtuelle Tabelle, die die einschließende Anweisung abfragen kann.

Syntax

(<select-query>)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]

Der von diesem “SELECT FROM (SELECT FROM..)”-Stil der Anweisung zurückgegebene Datensatz ist eine virtuelle Tabelle, die innerhalb der einschließenden Anweisung abgefragt werden kann, als wäre es eine reguläre Tabelle oder Ansicht.

`LATERAL` abgeleitete Tabellen

Das Schlüsselwort "LATERAL" kennzeichnet eine Tabelle als eine lateral abgeleitete Tabelle.Lateral abgeleitete Tabellen können auf Tabellen (einschließlich abgeleiteter Tabellen) verweisen, die früher in der FROM-Klausel vorkommen.Weitere Informationen finden Sie unter [fblangref40-dml-select-joins-lateral-de].

Beispiel für die Verwendung einer abgeleiteten Tabelle

Die abgeleitete Tabelle in der folgenden Abfrage gibt die Liste der Tabellennamen in der Datenbank und die Anzahl der Spalten in jeder Tabelle zurück.Eine “Drill-Down”-Abfrage für die abgeleitete Tabelle gibt die Anzahl der Felder und die Anzahl der Tabellen mit jeder Feldanzahl zurück:

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
        ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
        GROUP BY RELATION)
GROUP BY FIELDCOUNT

Ein triviales Beispiel, das zeigt, wie der Alias einer abgeleiteten Tabelle und die Liste der Spaltenaliase (beide optional) verwendet werden können:

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO
        (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

Note

Mehr über abgeleitete Tabellen

Abgeleitete Tabellen können

verschachtelt sein
Gewerkschaften sein und in Gewerkschaften verwendet werden können
enthalten Aggregatfunktionen, Unterabfragen und Joins
in Aggregatfunktionen, Unterabfragen und Joins verwendet werden
Aufrufe an auswählbare gespeicherte Prozeduren oder Abfragen an diese sein
haben WHERE, ORDER BY und GROUP BY Klauseln, FIRST/SKIP oder ROWS Direktiven, et al.

Außerdem,

Jede Spalte in einer abgeleiteten Tabelle muss einen Namen haben.Wenn es keinen Namen hat, z. B. wenn es sich um eine Konstante oder einen Laufzeitausdruck handelt, sollte ihm ein Alias zugewiesen werden, entweder auf reguläre Weise oder durch Einfügen in die Liste der Spaltenaliase in der Spezifikation der abgeleiteten Tabelle.
- Die Liste der Spaltenaliase ist optional, aber falls vorhanden, muss sie für jede Spalte in der abgeleiteten Tabelle einen Alias enthalten
Der Optimierer kann abgeleitete Tabellen sehr effektiv verarbeiten.Wenn jedoch eine abgeleitete Tabelle in einen Inner Join eingeschlossen ist und eine Unterabfrage enthält, kann der Optimierer keine Join-Reihenfolge verwenden.

Ein nützlicheres Beispiel

Angenommen, wir haben eine Tabelle COEFFS, die die Koeffizienten einer Reihe von quadratischen Gleichungen enthält, die wir lösen müssen.Es wurde wie folgt definiert:

create table coeffs (
  a double precision not null,
  b double precision not null,
  c double precision not null,
  constraint chk_a_not_zero check (a <> 0)
)

Abhängig von den Werten von 'a', 'b' und 'c' kann jede Gleichung null, eine oder zwei Lösungen haben.Es ist möglich, diese Lösungen mit einer einstufigen Abfrage der Tabelle COEFFS zu finden, aber der Code sieht ziemlich unordentlich aus und mehrere Werte (wie die Diskriminante) müssen mehrmals pro Zeile berechnet werden.Eine abgeleitete Tabelle kann hier helfen, die Dinge sauber zu halten:

select
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select b, b*b - 4*a*c, 2*a from coeffs) (b, D, denom)

Wenn wir die Koeffizienten neben den Lösungen anzeigen möchten (was möglicherweise keine schlechte Idee ist), können wir die Abfrage wie folgt ändern:

select
  a, b, c,
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select a, b, c, b*b - 4*a*c as D, 2*a as denom
     from coeffs)

Beachten Sie, dass, während die erste Abfrage eine Spaltenaliasliste für die abgeleitete Tabelle verwendet, die zweite bei Bedarf intern Aliase hinzufügt.Beide Methoden funktionieren, solange jede Spalte garantiert einen Namen hat.

Important

Alle Spalten in der abgeleiteten Tabelle werden so oft ausgewertet, wie sie in der Hauptabfrage angegeben sind.Dies ist wichtig, da es bei der Verwendung nichtdeterministischer Funktionen zu unerwarteten Ergebnissen führen kann.Das Folgende zeigt ein Beispiel dafür.

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

Das Ergebnis, wenn diese Abfrage drei verschiedene Werte erzeugt:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3  EB176C10-F754-4689-8B84-64B666381154

Um ein einzelnes Ergebnis der Funktion GEN_UUID sicherzustellen, können Sie die folgende Methode verwenden:

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1 = 0) T;

Diese Abfrage erzeugt ein einzelnes Ergebnis für alle drei Spalten:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

Eine alternative Lösung besteht darin, die Abfrage 'GEN_UUID' in eine Unterabfrage einzuschließen:

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT
        (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

Dies ist ein Artefakt der aktuellen Implementierung.Dieses Verhalten kann sich in einer zukünftigen Firebird-Version ändern.

Abfragen einer Common Table Expression (CTE) mittels `FROM`

Ein allgemeiner Tabellenausdruck – oder CTE – ist eine komplexere Variante der abgeleiteten Tabelle, aber auch leistungsfähiger.Eine Präambel, die mit dem Schlüsselwort WITH beginnt, definiert eine oder mehrere benannte CTEs, jede mit einer optionalen Spalten-Alias-Liste.Die Hauptabfrage, die der Präambel folgt, kann dann auf diese CTEs zugreifen, als wären es reguläre Tabellen oder Ansichten.Die CTEs verlassen den Gültigkeitsbereich, sobald die Hauptabfrage vollständig ausgeführt wurde.

Eine vollständige Diskussion der CTEs finden Sie im Abschnitt [fblangref40-dml-select-cte-de].

Das Folgende ist eine Umschreibung unseres abgeleiteten Tabellenbeispiels als CTE:

with vars (b, D, denom) as (
  select b, b*b - 4*a*c, 2*a from coeffs
)
select
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
from vars

Abgesehen davon, dass die Berechnungen, die zuerst durchgeführt werden müssen, jetzt am Anfang stehen, ist dies keine große Verbesserung gegenüber der abgeleiteten Tabellenversion.Allerdings können wir jetzt auch die doppelte Berechnung von sqrt(D) für jede Zeile eliminieren:

with vars (b, D, denom) as (
  select b, b*b - 4*a*c, 2*a from coeffs
),
vars2 (b, D, denom, sqrtD) as (
  select b, D, denom, iif (D >= 0, sqrt(D), null) from vars
)
select
  iif (D >= 0, (-b - sqrtD) / denom, null) sol_1,
  iif (D >  0, (-b + sqrtD) / denom, null) sol_2
from vars2

Der Code ist jetzt etwas komplizierter, kann aber effizienter ausgeführt werden (je nachdem, was länger dauert: Ausführen der SQRT-Funktion oder Übergabe der Werte von b, D und denom durch einen zusätzlichen CTE) .Übrigens hätten wir das auch mit abgeleiteten Tabellen machen können, aber das würde eine Verschachtelung erfordern.

Important

Alle Spalten im CTE werden so oft ausgewertet, wie sie in der Hauptabfrage angegeben sind.Dies ist wichtig, da es bei der Verwendung nichtdeterministischer Funktionen zu unerwarteten Ergebnissen führen kann.Das Folgende zeigt ein Beispiel dafür.

WITH T (X) AS (
  SELECT GEN_UUID()
  FROM RDB$DATABASE)
SELECT
  UUID_TO_CHAR(X) as c1,
  UUID_TO_CHAR(X) as c2,
  UUID_TO_CHAR(X) as c3
FROM T

Das Ergebnis, wenn diese Abfrage drei verschiedene Werte erzeugt:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3  EB176C10-F754-4689-8B84-64B666381154

Um ein einzelnes Ergebnis der Funktion GEN_UUID sicherzustellen, können Sie die folgende Methode verwenden:

WITH T (X) AS (
  SELECT GEN_UUID()
  FROM RDB$DATABASE
  UNION ALL
  SELECT NULL FROM RDB$DATABASE WHERE 1 = 0)
SELECT
  UUID_TO_CHAR(X) as c1,
  UUID_TO_CHAR(X) as c2,
  UUID_TO_CHAR(X) as c3
FROM T;

Diese Abfrage erzeugt ein einzelnes Ergebnis für alle drei Spalten:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

Eine alternative Lösung besteht darin, die Abfrage 'GEN_UUID' in eine Unterabfrage einzuschließen:

WITH T (X) AS (
  SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
  FROM RDB$DATABASE)
SELECT
  UUID_TO_CHAR(X) as c1,
  UUID_TO_CHAR(X) as c2,
  UUID_TO_CHAR(X) as c3
FROM T;

Dies ist ein Artefakt der aktuellen Implementierung.Dieses Verhalten kann sich in einer zukünftigen Firebird-Version ändern.

Siehe auch

[fblangref40-dml-select-cte-de].

Joins

Joins kombinieren Daten aus zwei Quellen zu einem einzigen Satz.Dies erfolgt zeilenweise und beinhaltet normalerweise die Überprüfung einer Join-Bedingung, um zu bestimmen, welche Zeilen zusammengeführt und im resultierenden Dataset erscheinen sollen.Es gibt verschiedene Typen (INNER, OUTER) und Klassen (qualifiziert, natürlich usw.) von Joins, jede mit ihrer eigenen Syntax und eigenen Regeln.

Da Joins verkettet werden können, können die an einem Join beteiligten Datasets selbst verbundene Sets sein.

Syntax

SELECT
   ...
   FROM <source>
   [<joins>]
   [...]

<source> ::=
  { table
  | view
  | selectable-stored-procedure [(<args>)]
  | <derived-table>
  | <common-table-expression>
  | LATERAL <derived-table>
  } [[AS] alias]

<joins> ::= <join> [<join> ...]

<join> ::=
    [<join-type>] JOIN <source> <join-condition>
  | NATURAL [<join-type>] JOIN <source>
  | {CROSS JOIN | ,} <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition> ::= ON <condition> | USING (<column-list>)

Table 1. Argumente für die `JOIN`-Klausel
Argument	Beschreibung
table	Name einer Tabelle
view	Name einer Ansicht
selectable-stored-procedure	Name einer auswählbaren Stored Procedure
args	Wählbare Eingabeparameter für gespeicherte Prozeduren
derived-table	Verweis, namentlich, auf eine abgeleitete Tabelle
common-table-expression	Verweis nach Name auf einen allgemeinen Tabellenausdruck (CTE)
alias	Ein Alias für eine Datenquelle (Tabelle, Sicht, Prozedur, CTE, abgeleitete Tabelle)
condition	Join-Bedingung (Kriterium)
column-list	Die Liste der Spalten, die für einen Equi-Join verwendet werden

Inner vs. Outer Joins

Ein Join kombiniert immer Datenzeilen aus zwei Sätzen (normalerweise als linker Satz und rechter Satz bezeichnet).Standardmäßig gelangen nur Zeilen in die Ergebnismenge, die die Join-Bedingung erfüllen (d. h. die mindestens einer Zeile in der anderen Menge entsprechen, wenn die Join-Bedingung angewendet wird).Dieser Standard-Join-Typ wird als inner join bezeichnet.Angenommen, wir haben die folgenden zwei Tabellen:

Tabelle A
ID	S
87	Just some text
235	Silence

Tabelle B
CODE	X
-23	56.7735
87	416.0

Wenn wir diese Tabellen wie folgt verbinden:

select *
  from A
  join B on A.id = B.code;

dann ist die Ergebnismenge:

CODE

Just some text

416.0

Die erste Reihe von A wurde mit der zweiten Reihe von B verbunden, weil sie zusammen die Bedingung “A.id = B.code” erfüllten.Die anderen Zeilen aus den Quelltabellen haben keine Übereinstimmung in der entgegengesetzten Menge und werden daher nicht in den Join aufgenommen.Denken Sie daran, dies ist ein INNER-Join.Wir können diese Tatsache explizit machen, indem wir schreiben:

select *
  from A
  inner join B on A.id = B.code;

Da jedoch INNER die Vorgabe ist, wird es normalerweise weggelassen.

Es ist durchaus möglich, dass eine Reihe im linken Satz mit mehreren Reihen im rechten Satz übereinstimmt oder umgekehrt.In diesem Fall sind alle diese Kombinationen enthalten, und wir können Ergebnisse erhalten wie:

ID	S	CODE	X
87	Just some text	87	416.0
87	Just some text	87	-1.0
-23	Don’t know	-23	56.7735
-23	Still don’t know	-23	56.7735
-23	I give up	-23	56.7735

Manchmal möchten (oder müssen) wir alle Zeilen einer oder beider Quellen in der verbundenen Menge erscheinen, unabhängig davon, ob sie mit einem Datensatz in der anderen Quelle übereinstimmen.Hier kommen Outer Joins ins Spiel.Ein 'LEFT' Outer Join enthält alle Datensätze aus dem linken Satz, aber nur übereinstimmende Datensätze aus dem rechten Satz.Bei einem RIGHT Outer Join ist es umgekehrt.FULL Outer Joins beinhalten alle Datensätze aus beiden Sets.In allen Outer Joins werden die "Löcher" (die Stellen, an denen ein eingeschlossener Quelldatensatz keine Übereinstimmung im anderen Satz hat) mit NULL aufgefüllt.

Um einen Outer Join zu erstellen, müssen Sie LEFT, RIGHT oder FULL angeben, optional gefolgt vom Schlüsselwort OUTER.

Unten sind die Ergebnisse der verschiedenen Outer Joins, wenn sie auf unsere ursprünglichen Tabellen A und B angewendet werden:

select *
  from A
  left [outer] join B on A.id = B.code;

ID	S	CODE	X
87	Just some text	87	416.0
235	Silence	<null>	<null>

select *
  from A
  right [outer] join B on A.id = B.code

ID	S	CODE	X
<null>	<null>	-23	56.7735
87	Just some text	87	416.0

select *
  from A
  full [outer] join B on A.id = B.code

ID	S	CODE	X
<null>	<null>	-23	56.7735
87	Just some text	87	416.0
235	Silence	<null>	<null>

Qualifizierte joins

Qualifizierte Joins geben Bedingungen für das Kombinieren von Zeilen an.Dies geschieht entweder explizit in einer ON-Klausel oder implizit in einer USING-Klausel.

Syntax

<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

<join-condition> ::= ON <condition> | USING (<column-list>)

Joins mit expliziter Bedingung

Die meisten qualifizierten Joins haben eine ON-Klausel mit einer expliziten Bedingung, die jeder gültige boolesche Ausdruck sein kann, aber normalerweise einen Vergleich zwischen den beiden beteiligten Quellen beinhaltet.

Sehr oft ist die Bedingung ein Gleichheitstest (oder eine Reihe von AND-verknüpften Gleichheitstests) mit dem Operator “=”.Joins wie diese heißen equi-joins.(Die Beispiele im Abschnitt über innere und äußere Verknüpfungen waren alle Gleichverknüpfungen.)

Beispiele für Joins mit einer expliziten Bedingung:

/* Wählen Sie alle Detroit-Kunden aus, die einen Kauf getätigt haben
    2013, zusammen mit den Kaufdetails: */
select * from customers c
  join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013;

/* Wie oben, aber auch nicht kaufende Kunden: */
select * from customers c
  left join sales s on s.cust_id = c.id
  where c.city = 'Detroit' and s.year = 2013;

/* Wählen Sie für jeden Mann die Frauen aus, die größer sind als er.
    Männer, für die es keine solche Frau gibt, werden nicht berücksichtigt. */
select m.fullname as man, f.fullname as woman
  from males m
  join females f on f.height > m.height;

/* Wählen Sie alle Schüler mit ihrer Klasse und ihrem Mentor aus.
    Auch Schüler ohne Mentor werden einbezogen.
    Schüler ohne Klasse werden nicht berücksichtigt. */
select p.firstname, p.middlename, p.lastname,
       c.name, m.name
  from pupils p
  join classes c on c.id = p.class
  left join mentors m on m.id = p.mentor;

Joins mit benannten Spalten

Equi-Joins vergleichen häufig Spalten mit dem gleichen Namen in beiden Tabellen.Wenn dies der Fall ist, können wir auch den zweiten Typ eines qualifizierten Joins verwenden: den benannten Spalten join.

Note	Benannte Spalten-Joins werden in Dialekt-1-Datenbanken nicht unterstützt.

Benannte Spalten-Joins haben eine USING-Klausel, die nur die Spaltennamen angibt.Also stattdessen:

select * from flotsam f
  join jetsam j
  on f.sea = j.sea
  and f.ship = j.ship;

wir können auch schreiben:

select * from flotsam
  join jetsam using (sea, ship)

was deutlich kürzer ist.Die Ergebnismenge ist jedoch etwas anders — zumindest bei Verwendung von “SELECT *”:

Der Join mit expliziter Bedingung — mit der ON-Klausel — enthält jede der Spalten SEA und SHIP zweimal: einmal aus der Tabelle FLOTSAM und einmal aus der Tabelle JETSAM.Offensichtlich haben sie die gleichen Werte.
Der Join mit benannten Spalten – mit der USING-Klausel – enthält diese Spalten nur einmal.

Wenn Sie alle Spalten in der Ergebnismenge der benannten Spalten verknüpfen möchten, richten Sie Ihre Abfrage wie folgt ein:

select f.*, j.*
  from flotsam f
  join jetsam j using (sea, ship);

Dadurch erhalten Sie genau die gleiche Ergebnismenge wie beim Join mit expliziter Bedingung.

Für einen OUTER benannten Spalten-Join gibt es eine zusätzliche Wendung, wenn “SELECT *” oder ein nicht qualifizierter Spaltenname aus der USING-Liste verwendet wird:

Wenn eine Zeile aus einem Quellsatz keine Übereinstimmung im anderen hat, aber aufgrund der Direktiven LEFT, RIGHT oder FULL trotzdem eingeschlossen werden muss, erhält die zusammengeführte Spalte in der verbundenen Menge das Nicht- NULL-Wert.Das ist fair genug, aber jetzt können Sie nicht sagen, ob dieser Wert aus dem linken Satz, dem rechten Satz oder beiden stammt.Dies kann besonders täuschen, wenn der Wert aus dem rechten Satz stammt, da “*” immer kombinierte Spalten im linken Teil anzeigt — auch bei einem RIGHT-Join.

Ob dies ein Problem ist oder nicht, hängt von der Situation ab.Wenn dies der Fall ist, verwenden Sie den oben gezeigten Ansatz “a.*, b.*”, wobei a und b die Namen oder Aliase der beiden Quellen sind.Oder noch besser, vermeiden Sie “*” in Ihren ernsthaften Abfragen und qualifizieren Sie alle Spaltennamen in verbundenen Mengen.Dies hat den zusätzlichen Vorteil, dass Sie sich überlegen müssen, welche Daten Sie woher abrufen möchten.

Es liegt in Ihrer Verantwortung, sicherzustellen, dass die Spaltennamen in der USING-Liste von kompatiblen Typen zwischen den beiden Quellen sind.Wenn die Typen kompatibel, aber nicht gleich sind, konvertiert die Engine sie in den Typ mit dem breitesten Wertebereich, bevor die Werte verglichen werden.Dies ist auch der Datentyp der zusammengeführten Spalte, der in der Ergebnismenge angezeigt wird, wenn “SELECT *” oder der nicht qualifizierte Spaltenname verwendet wird.Qualifizierte Spalten hingegen behalten immer ihren ursprünglichen Datentyp.

Tip

Wenn Sie beim Zusammenführen nach benannten Spalten eine Join-Spalte in der WHERE-Klausel verwenden, verwenden Sie immer den qualifizierten Spaltennamen, andernfalls wird kein Index für diese Spalte verwendet.

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE x = 0;

-- PLAN JOIN (A NATURAL , B INDEX (RDB$2))

Jedoch:

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE a.x = 0;
-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE b.x = 0;
-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Tatsache ist, dass die nicht spezifizierte Spalte in diesem Fall implizit durch `COALESCE(a.x, b.x) ersetzt wird.Dieser clevere Trick wird verwendet, um Spaltennamen eindeutig zu machen, stört aber auch die Verwendung des Indexes.

Natural Joins

Um die Idee des benannten Spalten-Joins noch einen Schritt weiter zu gehen, führt ein natural join einen automatischen Equi-Join für alle Spalten mit dem gleichen Namen in der linken und rechten Tabelle durch.Die Datentypen dieser Spalten müssen kompatibel sein.

Note	Natural-Joins werden in Dialekt-1-Datenbanken nicht unterstützt.

Syntax

<natural-join> ::= NATURAL [<join-type>] JOIN <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Gegeben seien diese beiden Tabellen:

create table TA (
  a bigint,
  s varchar(12),
  ins_date date
);

create table TB (
  a bigint,
  descr varchar(12),
  x float,
  ins_date date
);

Ein natürlicher Join von TA und TB würde die Spalten a und ins_date beinhalten, und die folgenden beiden Anweisungen hätten den gleichen Effekt:

select * from TA
  natural join TB;

select * from TA
  join TB using (a, ins_date);

Wie alle Joins sind natürliche Joins standardmäßig innere Joins, aber Sie können sie in äußere Joins umwandeln, indem Sie LEFT, RIGHT oder FULL vor dem JOIN-Schlüsselwort angeben.

Caution

Gibt es in den beiden Quellbeziehungen keine gleichnamigen Spalten, wird ein CROSS JOIN ausgeführt.Wir kommen in einer Minute zu dieser Art von Join.

Cross Joins

Ein Cross-Join erzeugt das Full-Set-Produkt der beiden Datenquellen.Dies bedeutet, dass jede Zeile in der linken Quelle erfolgreich mit jeder Zeile in der rechten Quelle abgeglichen wird.

Syntax

<cross-join> ::= {CROSS JOIN | ,} <source>

Bitte beachten Sie, dass die Kommasyntax veraltet ist!Es wird nur unterstützt, um die Funktionsfähigkeit des Legacy-Codes aufrechtzuerhalten, und kann in einer zukünftigen Version verschwinden.

Das Kreuzverknüpfen zweier Mengen ist äquivalent dazu, sie auf einer Tautologie zu verbinden (eine Bedingung, die immer wahr ist).Die folgenden beiden Aussagen haben die gleiche Wirkung:

select * from TA
  cross join TB;

select * from TA
  join TB on 1 = 1;

Cross-Joins sind Inner-Joins, da sie nur übereinstimmende Datensätze enthalten – es kommt einfach vor, dass jeder Datensatz übereinstimmt!Ein Outer-Cross-Join, falls vorhanden, würde dem Ergebnis nichts hinzufügen, da die hinzugefügten Outer-Joins nicht übereinstimmende Datensätze sind und diese in Cross-Joins nicht vorhanden sind.

Cross-Joins sind selten sinnvoll, außer wenn Sie alle möglichen Kombinationen von zwei oder mehr Variablen auflisten möchten.Angenommen, Sie verkaufen ein Produkt in verschiedenen Größen, Farben und Materialien.Wenn diese Variablen jeweils in einer eigenen Tabelle aufgeführt sind, würde diese Abfrage alle Kombinationen zurückgeben:

select m.name, s.size, c.name
  from materials m
  cross join sizes s
  cross join colors c;

Implizite Joins

Im SQL:89-Standard wurden die an einem Join beteiligten Tabellen als durch Kommas getrennte Liste in der FROM-Klausel angegeben (mit anderen Worten, ein Cross Join ).Die Join-Bedingungen wurden dann neben anderen Suchbegriffen in der WHERE-Klausel angegeben.Diese Art von Join wird als impliziter Join bezeichnet.

Ein Beispiel für einen impliziten Join:

/*
 * Eine Auswahl aller Detroit-Kunden, die
 * einen Einkauf getätigt haben
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'

Important

Die implizite Join-Syntax ist veraltet und wird möglicherweise in einer zukünftigen Version entfernt.Wir empfehlen, die zuvor gezeigte explizite Join-Syntax zu verwenden.

Explizite und implizite Verknüpfungen mischen

Das Mischen von expliziten und impliziten Joins wird nicht empfohlen, ist jedoch zulässig.Einige Arten des Mischens werden jedoch von Firebird nicht unterstützt.

Die folgende Abfrage gibt beispielsweise den Fehler “Spalte gehört nicht zur referenzierten Tabelle” aus.

SELECT *
FROM TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Das liegt daran, dass der explizite Join die Tabelle TA nicht sehen kann.Die nächste Abfrage wird jedoch ohne Fehler abgeschlossen, da die Einschränkung nicht verletzt wird.

SELECT *
FROM TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Ein Hinweis zu Gleichheit

Important

Dieser Hinweis zu Gleichheits- und Ungleichheitsoperatoren gilt überall in Firebirds SQL-Sprache, nicht nur in JOIN-Bedingungen.

Der Operator “=”, der explizit in vielen bedingten Joins und implizit in benannten Spalten-Joins und natürlichen Joins verwendet wird, gleicht nur Werte mit Werten ab.Nach dem SQL-Standard ist NULL kein Wert und daher sind zwei NULL weder gleich noch ungleich.Wenn NULLs in einem Join miteinander übereinstimmen müssen, verwenden Sie den IS NOT DISTINCT FROM-Operator.Dieser Operator gibt true zurück, wenn die Operanden den gleichen Wert oder haben, wenn beide NULL sind.

select *
  from A join B
  on A.id is not distinct from B.code;

Ebenso in den — extrem seltenen — Fällen, in denen Sie bei inequality beitreten möchten, verwenden Sie IS DISTINCT FROM, nicht “<>”, wenn NULL als anders betrachtet werden soll Wert und zwei NULLs als gleich betrachtet:

select *
  from A join B
  on A.id is distinct from B.code;

Mehrdeutige Feldnamen in Joins

Firebird weist nicht qualifizierte Feldnamen in einer Abfrage zurück, wenn diese Feldnamen in mehr als einem an einem Join beteiligten Dataset vorhanden sind.Dies gilt sogar für innere Equi-Joins, bei denen der Feldname in der ON-Klausel wie folgt vorkommt:

select a, b, c
  from TA
  join TB on TA.a = TB.a;

Von dieser Regel gibt es eine Ausnahme: Bei Named-Column-Joins und Natural-Joins darf der unqualifizierte Feldname einer am Matching-Prozess beteiligten Spalte legal verwendet werden und bezieht sich auf die gleichnamige zusammengeführte Spalte.Bei Joins mit benannten Spalten sind dies die Spalten, die in der USING-Klausel aufgelistet sind.Bei natürlichen Verknüpfungen sind dies die Spalten, die in beiden Beziehungen denselben Namen haben.Beachten Sie aber bitte noch einmal, dass, insbesondere bei Outer-Joins, ein einfacher colname nicht immer gleich links.colname oder right.colname ist.Typen können unterschiedlich sein und eine der qualifizierten Spalten kann NULL sein, während die andere nicht ist.In diesem Fall kann der Wert in der zusammengeführten, nicht qualifizierten Spalte die Tatsache maskieren, dass einer der Quellwerte fehlt.

Joins mit gespeicherten Prozeduren

Wenn ein Join mit einer Stored Procedure durchgeführt wird, die nicht über Eingabeparameter mit anderen Datenströmen korreliert ist, gibt es keine Merkwürdigkeiten.Wenn Korrelation im Spiel ist, offenbart sich eine unangenehme Eigenart.Das Problem ist, dass sich der Optimierer jede Möglichkeit verweigert, die Zusammenhänge der Eingabeparameter der Prozedur aus den Feldern in den anderen Streams zu ermitteln:

SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1;

Hier wird die Prozedur ausgeführt, bevor ein einzelner Datensatz aus der Tabelle MY_TAB abgerufen wurde.Der Fehler isc_no_cur_rec error (no current record for fetch operation) wird ausgelöst und unterbricht die Ausführung.

Die Lösung besteht darin, eine Syntax zu verwenden, die die Join-Reihenfolge explizit angibt:

SELECT *
FROM MY_TAB
LEFT JOIN MY_PROC(MY_TAB.F) ON 1 = 1;

Dies erzwingt, dass die Tabelle vor dem Vorgang gelesen wird und alles funktioniert ordnungsgemäß.

Tip	Diese Eigenart wurde im Optimierer als Fehler erkannt und wird in der nächsten Version von Firebird behoben.

Joins mit `LATERAL` abgeleiteten Tabellen

Eine abgeleitete Tabelle, die mit dem Schlüsselwort LATERAL definiert ist, wird als seitlich abgeleitete Tabelle bezeichnet.Wenn eine abgeleitete Tabelle als lateral definiert ist, darf sie auf andere Tabellen in derselben FROM-Klausel verweisen, jedoch nur auf die, die in der FROM-Klausel davor deklariert wurden.

Beispiele für seitliche abgeleitete Tabellen

/* select customers with their last order date and number */
select c.name, ox.order_date as last_order, ox.number
from customer c
  left join LATERAL (
    select first 1 o.order_date, o.number
    from orders o
    where o.id_customer = c.id
    order by o.ORDER_DATE desc
  ) as ox on true
--
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c
  cross join LATERAL (
    select first 1 city_name, population
    from cities
    where cities.country_name = c.country_name
    order by population desc
  ) AS dt;
--
select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
  LATERAL ( select max(amount) as amount
            from all_sales
            where all_sales.salesperson_id = salespeople.id
  ) as max_sale,
  LATERAL ( select customer_name
            from all_sales
            where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
  ) as customer_of_max_sale;

Die `WHERE`-Klausel

Die WHERE-Klausel dient dazu, die zurückgegebenen Zeilen auf diejenigen zu beschränken, die den Aufrufer interessieren.Die Bedingung, die dem Schlüsselwort WHERE folgt, kann eine einfache Prüfung wie “AMOUNT = 3” sein oder ein vielschichtiger, verschachtelter Ausdruck mit Unterauswahlen, Prädikaten, Funktionsaufrufen, mathematischen und logischen Operatoren, Kontexvariablen und mehr.

Die Bedingung in der WHERE-Klausel wird oft als Suchbedingung, als Suchausdruck oder einfach als Suche bezeichnet.

In DSQL und ESQL kann der Suchausdruck Parameter enthalten.Dies ist sinnvoll, wenn eine Abfrage mit unterschiedlichen Eingabewerten mehrmals wiederholt werden muss.In der SQL-Zeichenfolge, die an den Server übergeben wird, werden Fragezeichen als Platzhalter für die Parameter verwendet.Sie werden positionale Parameter genannt, weil sie nur durch ihre Position im String unterschieden werden können.Konnektivitätsbibliotheken unterstützen oft named parameters der Form :id, :amount, :a usw.Diese sind benutzerfreundlicher;die Bibliothek kümmert sich um die Übersetzung der benannten Parameter in Positionsparameter, bevor die Anweisung an den Server übergeben wird.

Die Suchbedingung kann auch lokale (PSQL) oder Host- (ESQL) Variablennamen enthalten, denen ein Doppelpunkt vorangestellt ist.

Syntax

SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]

Table 1. `WHERE`-Argumente
Parameter	Beschreibung
search-condition	Ein boolescher Ausdruck, der TRUE, FALSE oder möglicherweise UNKNOWN (NULL) zurückgibt.

Nur die Zeilen, für die die Suchbedingung 'TRUE' ergibt, werden in die Ergebnismenge aufgenommen.Seien Sie vorsichtig mit möglichen NULL-Ergebnissen: Wenn Sie einen NULL-Ausdruck mit NOT negieren, ist das Ergebnis immer noch NULL und die Zeile wird nicht passieren.Dies wird in einem der folgenden Beispiele demonstriert.

Beispiele

select genus, species from mammals
  where family = 'Felidae'
  order by genus;

select * from persons
  where birthyear in (1880, 1881)
     or birthyear between 1891 and 1898;

select name, street, borough, phone
  from schools s
  where exists (select * from pupils p where p.school = s.id)
  order by borough, street;

select * from employees
  where salary >= 10000 and position <> 'Manager';

select name from wrestlers
  where region = 'Europe'
    and weight > all (select weight from shot_putters
                      where region = 'Africa');

select id, name from players
  where team_id = (select id from teams where name = 'Buffaloes');

select sum (population) from towns
  where name like '%dam'
  and province containing 'land';

select password from usertable
  where username = current_user;

Das folgende Beispiel zeigt, was passieren kann, wenn die Suchbedingung NULL ergibt.

Angenommen, Sie haben eine Tabelle mit den Namen einiger Kinder und der Anzahl der Murmeln (engl. marbles), die sie besitzen.Zu einem bestimmten Zeitpunkt enthält die Tabelle diese Daten:

CHILD	MARBLES
Anita	23
Bob E.	12
Chris	<null>
Deirdre	1
Eve	17
Fritz	0
Gerry	21
Hadassah	<null>
Isaac	6

CHILD

MARBLES

Anita

Bob E.

Chris

<null>

Deirdre

Eve

Fritz

Gerry

Hadassah

<null>

Isaac

Beachten Sie zunächst den Unterschied zwischen NULL und 0: Fritz hat bekannt überhaupt keine Murmeln, Chris' und Hadassah’s Murmeln sind unbekannt.

Wenn Sie nun diese SQL-Anweisung ausgeben:

select list(child) from marbletable where marbles > 10;

Sie erhalten die Namen Anita, Bob E., Eve und Gerry.Diese Kinder haben alle mehr als 10 Murmeln.

Wenn Sie den Ausdruck negieren:

select list(child) from marbletable where not marbles > 10

Deirdre, Fritz und Isaac sind an der Reihe, die Liste zu füllen.Chris und Hadassah sind nicht enthalten, da sie nicht bekannt haben, dass sie zehn Murmeln oder weniger haben.Sollten Sie diese letzte Abfrage ändern in:

select list(child) from marbletable where marbles <= 10;

das Ergebnis bleibt gleich, da der Ausdruck NULL <= 10 UNKNOWN ergibt.Dies ist nicht dasselbe wie TRUE, daher werden Chris und Hadassah nicht aufgeführt.Wenn Sie möchten, dass sie mit den “armen”-Kindern aufgelistet werden, ändern Sie die Abfrage in:

select list(child) from marbletable
where marbles <= 10 or marbles is null;

Jetzt wird die Suchbedingung für Chris und Hadassah wahr, da “marbles is null” in ihrem Fall offensichtlich TRUE zurückgibt.Tatsächlich kann die Suchbedingung jetzt für niemanden NULL sein.

Zuletzt zwei Beispiele für SELECT-Abfragen mit Parametern in der Suche.Es hängt von der Anwendung ab, wie Sie Abfrageparameter definieren sollten und ob dies überhaupt möglich ist.Beachten Sie, dass Abfragen wie diese nicht sofort ausgeführt werden können: Sie müssen zuerst vorbereitet werden.Nachdem eine parametrisierte Abfrage erstellt wurde, kann der Benutzer (oder der aufrufende Code) Werte für die Parameter bereitstellen und mehrmals ausführen lassen, wobei vor jedem Aufruf neue Werte eingegeben werden.Wie die Werte eingegeben und die Ausführung gestartet wird, bleibt der Anwendung überlassen.In einer GUI-Umgebung gibt der Benutzer typischerweise die Parameterwerte in ein oder mehrere Textfelder ein und klickt dann auf eine Schaltfläche "Ausführen", "Ausführen" oder "Aktualisieren".

select name, address, phone frome stores
  where city = ? and class = ?;

select * from pants
  where model = :model and size = :size and color = :col;

Die letzte Abfrage kann nicht direkt an die Engine übergeben werden; die Anwendung muss es zuerst in das andere Format konvertieren und benannte Parameter Positionsparametern zuordnen.

Die `GROUP BY`-Klausel

GROUP BY führt Ausgabezeilen, die dieselbe Kombination von Werten in ihrer Elementliste haben, zu einer einzigen Zeile zusammen.Aggregatfunktionen in der Auswahlliste werden auf jede Gruppe einzeln und nicht auf den gesamten Datensatz angewendet.

Wenn die Auswahlliste nur Aggregatspalten enthält oder allgemeiner Spalten, deren Werte nicht von einzelnen Zeilen in der zugrunde liegenden Menge abhängen, ist GROUP BY optional.Wenn es weggelassen wird, besteht die endgültige Ergebnismenge von aus einer einzelnen Zeile (vorausgesetzt, dass mindestens eine aggregierte Spalte vorhanden ist).

Wenn die Auswahlliste sowohl Aggregatspalten als auch Spalten enthält, deren Werte pro Zeile variieren können, wird die GROUP BY-Klausel obligatorisch.

Syntax

SELECT ... FROM ...
  GROUP BY <grouping-item> [, <grouping-item> ...]
  [HAVING <grouped-row-condition>]
  ...

<grouping-item> ::=
    <non-aggr-select-item>
  | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position

Table 1. Argumente für die `GROUP BY`-Klausel
Argument	Beschreibung
non-aggr-expression	Jeder nicht aggregierende Ausdruck, der nicht in der `SELECT`-Liste enthalten ist, d. h. nicht ausgewählte Spalten aus dem Quellsatz oder Ausdrücke, die überhaupt nicht von den Daten im Satz abhängen
column-copy	Eine wörtliche Kopie aus der `SELECT`-Liste eines Ausdrucks, der keine Aggregatfunktion enthält
column-alias	Der Alias aus der `SELECT`-Liste eines Ausdrucks (Spalte), der keine Aggregatfunktion enthält
column-position	Die Positionsnummer in der `SELECT`-Liste eines Ausdrucks (Spalte), der keine Aggregatfunktion enthält

Als allgemeine Faustregel gilt, dass jedes nicht aggregierte Element in der SELECT-Liste auch in der GROUP BY-Liste enthalten sein muss.Sie können dies auf drei Arten tun:

Durch wörtliches Kopieren des Artikels aus der Auswahlliste, z.B. “class” oder “'D:' || upper(doccode)”.
Durch Angabe des Spaltenalias, falls vorhanden.
Durch Angabe der Spaltenposition als Ganzzahl literal zwischen 1 und der Anzahl der Spalten.Ganzzahlwerte, die aus Ausdrücken oder Parameterersetzungen resultieren, sind einfach unveränderlich und werden als solche in der Gruppierung verwendet.Sie haben jedoch keine Auswirkung, da ihr Wert für jede Zeile gleich ist.

Note

Wenn Sie nach einer Spaltenposition gruppieren, wird der Ausdruck an dieser Position intern aus der Auswahlliste kopiert.Wenn es sich um eine Unterabfrage handelt, wird diese Unterabfrage in der Gruppierungsphase erneut ausgeführt.Das heißt, das Gruppieren nach der Spaltenposition, anstatt den Unterabfrageausdruck in der Gruppierungsklausel zu duplizieren, spart Tastenanschläge und Bytes, aber es ist keine Möglichkeit, Verarbeitungszyklen zu sparen!

Zusätzlich zu den erforderlichen Elementen kann die Gruppierungsliste auch Folgendes enthalten:

Spalten aus der Quelltabelle, die nicht in der Auswahlliste enthalten sind, oder nicht aggregierte Ausdrücke, die auf solchen Spalten basieren.Das Hinzufügen solcher Spalten kann die Gruppen weiter unterteilen.Da sich diese Spalten jedoch nicht in der Auswahlliste befinden, können Sie nicht erkennen, welche aggregierte Zeile welchem Wert in der Spalte entspricht.Wenn Sie also an diesen Informationen interessiert sind, nehmen Sie im Allgemeinen auch die Spalte oder den Ausdruck in die Auswahlliste auf — was Sie zu der Regel zurückbringt: “Jede nicht aggregierte Spalte in der Auswahlliste muss auch in der Gruppierungsliste”.
Ausdrücke, die nicht von den Daten in der zugrunde liegenden Menge abhängig sind, z. Konstanten, Kontextvariablen, einwertige nicht korrelierte Unterauswahlen usw.Dies wird nur der Vollständigkeit halber erwähnt, da das Hinzufügen solcher Elemente völlig sinnlos ist: Sie beeinflussen die Gruppierung überhaupt nicht.“Harmlose aber nutzlose” Elemente wie diese können auch in der Auswahlliste vorkommen, ohne in die Gruppierungsliste kopiert zu werden.

Beispiele

Wenn die Auswahlliste nur aggregierte Spalten enthält, ist GROUP BY nicht obligatorisch:

select count(*), avg(age) from students
  where sex = 'M';

Dadurch wird eine einzelne Zeile zurückgegeben, die die Anzahl der männlichen Studenten und ihr Durchschnittsalter auflistet.Das Hinzufügen von Ausdrücken, die nicht von Werten in einzelnen Zeilen der Tabelle STUDENTS abhängen, ändert daran nichts:

select count(*), avg(age), current_date from students
  where sex = 'M';

Die Zeile enthält jetzt eine zusätzliche Spalte mit dem aktuellen Datum, aber ansonsten hat sich nichts Wesentliches geändert.Eine GROUP BY-Klausel ist weiterhin nicht erforderlich.

In beiden obigen Beispielen ist es jedoch erlaubt.Das ist vollkommen gültig:

select count(*), avg(age) from students
  where sex = 'M'
  group by class;

Dadurch wird für jede Klasse mit Jungen eine Zeile zurückgegeben, in der die Anzahl der Jungen und ihr Durchschnittsalter in dieser bestimmten Klasse aufgeführt sind.(Wenn Sie auch das Feld current_date belassen, wird dieser Wert in jeder Zeile wiederholt, was nicht sehr aufregend ist.)

Die obige Abfrage hat jedoch einen großen Nachteil: Sie gibt Ihnen Informationen über die verschiedenen Klassen, aber sie sagt Ihnen nicht, welche Zeile für welche Klasse gilt.Um diese zusätzlichen Informationen zu erhalten, muss die nicht aggregierte Spalte "CLASS" zur Auswahlliste hinzugefügt werden:

select class, count(*), avg(age) from students
  where sex = 'M'
  group by class;

Jetzt haben wir eine nützliche Abfrage.Beachten Sie, dass das Hinzufügen der Spalte CLASS auch die GROUP BY-Klausel obligatorisch macht.Wir können diese Klausel nicht mehr löschen, es sei denn, wir entfernen auch CLASS aus der Spaltenliste.

Die Ausgabe unserer letzten Abfrage kann etwa so aussehen:

CLASS	COUNT	AVG
2A	12	13.5
2B	9	13.9
3A	11	14.6
3B	12	14.4
…	…	…

Die Überschriften “COUNT” und “AVG” sind wenig aussagekräftig.In einem einfachen Fall wie diesem kommen Sie vielleicht damit durch, aber im Allgemeinen sollten Sie Aggregatspalten einen aussagekräftigen Namen geben, indem Sie sie mit einem Alias versehen:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class;

Wie Sie sich vielleicht an der formalen Syntax der Spaltenliste erinnern, ist das Schlüsselwort AS optional.

Das Hinzufügen weiterer nicht-aggregierter (oder besser: zeilenabhängiger) Spalten erfordert auch das Hinzufügen dieser zur GROUP BY-Klausel.Zum Beispiel möchten Sie vielleicht die oben genannten Informationen auch für Mädchen sehen;und vielleicht möchten Sie auch zwischen Internats- und Tagesschülern unterscheiden:

select class,
       sex,
       boarding_type,
       count(*) as number,
       avg(age) as avg_age
  from students
  group by class, sex, boarding_type;

Dies kann zu folgendem Ergebnis führen:

CLASS	SEX	BOARDING_TYPE	NUMBER	AVG_AGE
2A	F	BOARDING	9	13.3
2A	F	DAY	6	13.5
2A	M	BOARDING	7	13.6
2A	M	DAY	5	13.4
2B	F	BOARDING	11	13.7
2B	F	DAY	5	13.7
2B	M	BOARDING	6	13.8
…	…	…	…	…

CLASS

SEX

BOARDING_TYPE

NUMBER

AVG_AGE

BOARDING

13.3

DAY

13.5

BOARDING

13.6

DAY

13.4

BOARDING

13.7

DAY

13.7

BOARDING

13.8

…

Jede Zeile in der Ergebnismenge entspricht einer bestimmten Kombination der Spalten CLASS, SEX und BOARDING_TYPE.Die aggregierten Ergebnisse – Anzahl und Durchschnittsalter – werden für jede dieser eher spezifischen Gruppen einzeln angegeben.In einer Abfrage wie dieser sehen Sie keine Gesamtsumme für Jungen als Ganzes oder Tagesschüler als Ganzes.Das ist der Kompromiss: Je mehr nicht aggregierte Spalten Sie hinzufügen, desto mehr können Sie sehr spezifische Gruppen lokalisieren, aber desto mehr verlieren Sie auch den Überblick.Natürlich können Sie die “gröberen” Aggregate weiterhin durch separate Abfragen erhalten.

`HAVING`

So wie eine 'WHERE'-Klausel die Zeilen in einem Datensatz auf diejenigen beschränkt, die die Suchbedingung erfüllen, so erlegt die 'HAVING'-Unterklausel Beschränkungen für die aggregierten Zeilen in einer gruppierten Menge auf.HAVING ist optional und kann nur in Verbindung mit GROUP BY verwendet werden.

Die Bedingung(en) in der HAVING-Klausel können sich beziehen auf:

Jede aggregierte Spalte in der Auswahlliste.Dies ist der am häufigsten verwendete Fall.
Jeder aggregierte Ausdruck, der nicht in der Auswahlliste enthalten ist, aber im Kontext der Abfrage zulässig ist.Dies ist manchmal auch nützlich.
Jede Spalte in der GROUP BY-Liste.Obwohl es legal ist, ist es effizienter, diese nicht aggregierten Daten zu einem früheren Zeitpunkt zu filtern: in der WHERE-Klausel.
Jeder Ausdruck, dessen Wert nicht vom Inhalt des Datasets abhängt (wie eine Konstante oder eine Kontextvariable).Dies ist gültig, aber völlig sinnlos, da es entweder die gesamte Menge unterdrückt oder unberührt lässt, basierend auf Bedingungen, die nichts mit der Menge selbst zu tun haben.

Eine HAVING-Klausel kann nicht enthalten:

Nicht aggregierte Spaltenausdrücke, die nicht in der GROUP BY-Liste enthalten sind.
Spaltenpositionen.Eine ganze Zahl in der HAVING-Klausel ist nur eine ganze Zahl.
Spaltenaliase – nicht einmal, wenn sie in der GROUP BY-Klausel vorkommen!

Beispiele

Aufbauend auf unseren früheren Beispielen könnte dies verwendet werden, um kleine Schülergruppen zu überspringen:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having count(*) >= 5;

So wählen Sie nur Gruppen mit einer Mindestaltersspanne aus:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having max(age) - min(age) > 1.2;

Beachten Sie, dass Sie, wenn Sie wirklich an diesen Informationen interessiert sind, normalerweise min(age) und max(age) einschließen würden – oder den Ausdruck „`max(age) - min(age) `" – auch in der Auswahlliste!

Um nur 3. Klassen einzubeziehen:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M'
  group by class
  having class starting with '3';

Besser wäre es, diese Bedingung in die WHERE-Klausel zu verschieben:

select class,
       count(*) as num_boys,
       avg(age) as boys_avg_age
  from students
  where sex = 'M' and class starting with '3'
  group by class;

Die `WINDOW`-Klausel

Die WINDOW-Klausel definiert ein oder mehrere benannte Fenster, auf die von Fensterfunktionen in der aktuellen Abfragespezifikation verwiesen werden kann.

Syntax

<query_spec> ::=
  SELECT
    [<limit_clause>]
    [<distinct_clause>]
    <select_list>
    <from_clause>
    [<where_clause>]
    [<group_clause>]
    [<having_clause>]
    [<named_windows_clause>]
    [<plan_clause>]

<named_windows_clause> ::=
  WINDOW <window_definition> [, <window_definition> ...]

<window definition> ::=
  new_window_name AS <window_specification>

<window_specification> ::=
  !! Siehe auch Window-Funktionen !!

In einer Abfrage mit mehreren SELECT- und WINDOW-Klauseln (zB mit Unterabfragen) ist der Geltungsbereich von `new_window_name_ auf seinen Abfragekontext beschränkt.Das bedeutet, dass ein Fenstername aus einem inneren Kontext nicht in einem äußeren Kontext verwendet werden kann und umgekehrt.Derselbe Fenstername kann jedoch unabhängig in verschiedenen Kontexten verwendet werden, obwohl es besser sein könnte, dies zu vermeiden, um Verwirrung zu vermeiden.

Weitere Informationen finden Sie unter [fblangref40-windowfuncs-de].

Beispiel mit Named Windows

select
  id,
  department,
  salary,
  count(*) over w1,
  first_value(salary) over w2,
  last_value(salary) over w2
from employee
window w1 as (partition by department),
       w2 as (w1 order by salary)
order by department, salary;

Die `PLAN`-Klausel

Die PLAN-Klausel ermöglicht es dem Benutzer, einen Datenabrufplan zu übermitteln und damit den Plan zu überschreiben, den der Optimierer automatisch generiert hätte.

Syntax

PLAN <plan-expr>

<plan-expr> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-item>

<sorted-item> ::= SORT (<plan-item>)

<joined-item> ::=
  JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<hash-item> ::=
  HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expr>

<basic-item> ::=
  <relation> { NATURAL
             | INDEX (<indexlist>)
             | ORDER index [INDEX (<indexlist>)] }

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]

Table 1. Argumente für die `PLAN`-Klausel
Argument	Beschreibung
table	Tabellenname oder sein Alias
view	Ansichtsname
index	Indexname

Jedes Mal, wenn ein Benutzer eine Abfrage an die Firebird-Engine sendet, berechnet der Optimierer eine Datenabrufstrategie.Die meisten Firebird-Clients können diesen Abrufplan für den Benutzer sichtbar machen.In Firebirds eigenem Dienstprogramm isql geschieht dies mit dem Befehl SET PLAN ON.Wenn Sie Abfragepläne untersuchen, anstatt Abfragen auszuführen, zeigt SET PLANONLY ON den Plan an, ohne die Abfrage auszuführen.Verwenden Sie SET PLANONLY OFF, um die Abfrage auszuführen und den Plan anzuzeigen.

Note

Einen detaillierteren Plan erhalten Sie, wenn Sie einen erweiterten Plan aktivieren.In isql kann dies mit SET EXPLAIN ON erfolgen.Der erweiterte Plan zeigt detailliertere Informationen über die vom Optimierer verwendeten Zugriffsmethoden an, kann jedoch nicht in die PLAN-Klausel einer Anweisung aufgenommen werden.Die Beschreibung des erweiterten Plans geht über den Rahmen dieser Sprachreferenz hinaus.

In den meisten Situationen können Sie darauf vertrauen, dass Firebird den optimalen Abfrageplan für Sie auswählt.Wenn Sie jedoch komplizierte Abfragen haben, deren Leistung nicht ausreicht, kann es sich durchaus lohnen, den Plan zu prüfen und zu prüfen, ob Sie ihn verbessern können.

Einfache Pläne

Die einfachsten Pläne bestehen nur aus einem Relationsnamen gefolgt von einer Abrufmethode.Zum Beispiel für eine unsortierte Einzeltabellenauswahl ohne WHERE-Klausel:

select * from students
  plan (students natural);

Erweiterter Plan:

Select Expression
  -> Table "STUDENTS" Full Scan

Wenn es eine WHERE- oder eine HAVING-Klausel gibt, können Sie den Index angeben, der für die Suche nach Übereinstimmungen verwendet werden soll:

select * from students
  where class = '3C'
  plan (students index (ix_stud_class));

Erweiterter Plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Bitmap
        -> Index "IX_STUD_CLASS" Range Scan (full match)

Die Direktive INDEX wird auch für Join-Bedingungen verwendet (wird etwas später besprochen).Es kann eine durch Kommas getrennte Liste von Indizes enthalten.

ORDER gibt den Index zum Sortieren der Menge an, wenn eine ORDER BY- oder GROUP BY-Klausel vorhanden ist:

select * from students
  plan (students order pk_students)
  order by id;

Erweiterter plan:

Select Expression
  -> Table "STUDENTS" Access By ID
    -> Index "PK_STUDENTS" Full Scan

ORDER und INDEX können kombiniert werden:

select * from students
  where class >= '3'
  plan (students order pk_students index (ix_stud_class))
  order by id;

Erweiterter Plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Es ist vollkommen in Ordnung, wenn ORDER und INDEX denselben Index angeben:

select * from students
  where class >= '3'
  plan (students order ix_stud_class index (ix_stud_class))
  order by class;

Erweiterter Plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Um Sets zu sortieren, wenn kein verwendbarer Index verfügbar ist (oder wenn Sie seine Verwendung unterdrücken möchten), lassen Sie ORDER weg und stellen Sie dem Planausdruck SORT voran:

select * from students
  plan sort (students natural)
  order by name;

Erweiterter Plan:

Select Expression
  -> Sort (record length: 128, key length: 56)
    -> Table "STUDENTS" Full Scan

Oder wenn ein Index für die Suche verwendet wird:

select * from students
  where class >= '3'
  plan sort (students index (ix_stud_class))
  order by name;

Erweiterter Plan:

elect Expression
  -> Sort (record length: 136, key length: 56)
    -> Filter
      -> Table "STUDENTS" Access By ID
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Beachten Sie, dass SORT im Gegensatz zu ORDER außerhalb der Klammern steht.Dies spiegelt die Tatsache wider, dass die Datenzeilen ungeordnet abgerufen und anschließend von der Engine sortiert werden.

Geben Sie bei der Auswahl aus einer Ansicht die Ansicht und die betreffende Tabelle an.Wenn Sie beispielsweise eine Ansicht FRESHMEN haben, die nur die Erstsemester auswählt:

select * from freshmen
  plan (freshmen students natural);

Erweiterter Plan:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Oder zum Beispiel:

select * from freshmen
  where id > 10
  plan sort (freshmen students index (pk_students))
  order by name desc;

Erweiterter Plan:

Select Expression
  -> Sort (record length: 144, key length: 24)
    -> Filter
      -> Table "STUDENTS" as "FRESHMEN" Access By ID
        -> Bitmap
          -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Important

Wenn eine Tabelle oder Ansicht mit einem Alias versehen wurde, muss der Alias, nicht der ursprüngliche Name, in der PLAN-Klausel verwendet werden.

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;

`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:

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.

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.

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:

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.

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.

Die Quelle ist selbst eine 'BLOB'-Spalte oder allgemeiner ein Ausdruck, der ein 'BLOB' zurückgibt.
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`

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);

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);

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);

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

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`

In PSQL mit optionalen Doppelpunkten und ohne optionale Klammern:

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

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.

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

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

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.

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.

Wie Die Engine Mit WITH LOCK Umgeht

Wie die Engine mit WITH LOCK umgeht

Fallstricke bei der Verwendung von WITH LOCK

docnext count = 71

Beispiele für die explizite Sperrung

INTO

Beispiele

Common Table Expressions (“WITH …​ AS …​ SELECT”)

CTE-Hinweise

Beispiele

Rekursive CTEs

Ausführungsmuster

Beispiel für rekursive CTEs

Die SELECT-Spaltenliste

Beispiele für SELECT-Abfragen mit verschiedenen Arten von Spaltenlisten

Die FROM-Klausel

Auswählen mit FROM in einer Tabelle oder Ansicht

Beispiele

Auswählen von FROM einer gespeicherten Prozedur

Abfragen einer abgeleiteten Tabelle mittels FROM

LATERAL abgeleitete Tabellen

Beispiel für die Verwendung einer abgeleiteten Tabelle

Ein nützlicheres Beispiel

Abfragen einer Common Table Expression (CTE) mittels FROM

Joins

Inner vs. Outer Joins

Qualifizierte joins

Joins mit expliziter Bedingung

Joins mit benannten Spalten

Natural Joins

Cross Joins

Implizite Joins

Explizite und implizite Verknüpfungen mischen

Ein Hinweis zu Gleichheit

Mehrdeutige Feldnamen in Joins

Joins mit gespeicherten Prozeduren

Joins mit LATERAL abgeleiteten Tabellen

Die WHERE-Klausel

Beispiele

Die GROUP BY-Klausel

Beispiele

HAVING

Beispiele

Die WINDOW-Klausel

Beispiel mit Named Windows

Die PLAN-Klausel

Einfache Pläne

Zusammengesetzte Pläne

UNION

Beispiele

INSERT

INSERT …​ VALUES

INSERT …​ SELECT

INSERT …​ DEFAULT VALUES

OVERRIDING

Die RETURNING-Klausel

Einfügen in 'BLOB'-Spalten

UPDATE

Alias verwenden

Beispiel

Die SET-Klausel

Die WHERE-Klausel

Die Klauseln ORDER BY und ROWS

Die RETURNING-Klausel

Die INTO-Unterklausel

RETURNING-Beispiel (DSQL)

'BLOB'-Spalten aktualisieren

UPDATE OR INSERT

Die RETURNING-Klausel

Beispiel für UPDATE OR INSERT

DELETE

Aliases

Beispiele

WHERE

Beispiele

PLAN

ORDER BY und ROWS

Beispiele

RETURNING

Beispiele

Wie die Engine mit `WITH LOCK` umgeht

Fallstricke bei der Verwendung von `WITH LOCK`

`INTO`

Common Table Expressions (“`WITH … AS … SELECT`”)

Die `SELECT`-Spaltenliste

Beispiele für `SELECT`-Abfragen mit verschiedenen Arten von Spaltenlisten

Die `FROM`-Klausel

Auswählen mit `FROM` in einer Tabelle oder Ansicht

Auswählen von `FROM` einer gespeicherten Prozedur

Abfragen einer abgeleiteten Tabelle mittels `FROM`

`LATERAL` abgeleitete Tabellen

Abfragen einer Common Table Expression (CTE) mittels `FROM`

Joins mit `LATERAL` abgeleiteten Tabellen

Die `WHERE`-Klausel

Die `GROUP BY`-Klausel

`HAVING`

Die `WINDOW`-Klausel

Die `PLAN`-Klausel

`UNION`

`INSERT`

`INSERT … VALUES`

`INSERT … SELECT`

`INSERT … DEFAULT VALUES`

`OVERRIDING`

Die `RETURNING`-Klausel

`UPDATE`

Die `SET`-Klausel

Die `WHERE`-Klausel

Die Klauseln `ORDER BY` und `ROWS`

Die `RETURNING`-Klausel

Die `INTO`-Unterklausel

`UPDATE OR INSERT`

Die `RETURNING`-Klausel

Beispiel für `UPDATE OR INSERT`

`DELETE`

`WHERE`

`PLAN`

`ORDER BY` und `ROWS`

`RETURNING`

`MERGE`

Die `RETURNING`-Klausel

Beispiele für `MERGE`

`EXECUTE PROCEDURE`

Beispiele für `EXECUTE PROCEDURE`

`EXECUTE BLOCK`