Cursor

Die direkte Cursorunterstützung ist neu in PL / pgSQL Version 7.2. Die Verarbeitung einer Ergebnismenge mit einem Cursor ähnelt der Verarbeitung einer Ergebnismenge mit einer FOR-Schleife, aber Cursor bieten einige deutliche Vorteile, die Sie gleich sehen werden.

Sie können sich einen Cursor als Namen für eine Ergebnismenge vorstellen. Sie müssen eine Cursorvariable genauso deklarieren wie jede andere Variable. Das folgende Code-Snippet zeigt, wie Sie eine Cursorvariable deklarieren können:

...DECLARE rental_cursor CURSOR FOR SELECT * FROM rentals;...

rental_cursor wird als Cursor für die Ergebnismenge der Abfrage SELECT * FROM rentals deklariert. Wenn Sie eine Variable vom Typ CURSOR deklarieren, müssen Sie eine Abfrage einschließen. Die Cursorvariable soll an diese Abfrage gebunden sein, und die Variable ist eine gebundene Cursorvariable.

Bevor Sie einen gebundenen Cursor verwenden können, müssen Sie den Cursor mit der OPEN-Anweisung öffnen:

...DECLARE rental_cursor CURSOR FOR SELECT * FROM rentals;BEGIN OPEN rental_cursor;...

Wenn Sie versuchen, einen bereits geöffneten Cursor zu öffnen, erhalten Sie eine Fehlermeldung (Cursor „Name“ bereits verwendet). Wenn Sie versuchen, von einem nicht geöffneten Cursor abzurufen (siehe folgenden Abschnitt), erhalten Sie eine Fehlermeldung (Cursor „name“ ist ungültig). Wenn Sie einen Cursor verwenden, DEKLARIEREN Sie ihn zuerst, öffnen ihn dann, RUFEN ihn ab und schließen ihn schließlich in dieser Reihenfolge. Sie können den Zyklus ÖFFNEN, ABRUFEN, SCHLIEßEN wiederholen, wenn Sie die Cursorergebnisse erneut verarbeiten möchten.

FETCH

Nachdem ein gebundener Cursor geöffnet wurde, können Sie die Ergebnismenge (jeweils eine Zeile) mit der FETCH-Anweisung abrufen. Wenn Sie eine Zeile von einem Cursor abrufen, müssen Sie eine oder mehrere Zielvariablen angeben, in die PL / pgSQL die Ergebnisse einfügen kann. Die Syntax für die FETCH-Anweisung lautet

FETCH cursor-name INTO destination ];

Das Ziel (oder die Ziele) müssen mit der Form einer vom Cursor zurückgegebenen Zeile übereinstimmen. Wenn der Cursor beispielsweise eine Zeile aus der Vermietungstabelle auswählt, gibt es drei mögliche Ziele:

  • Eine Variable vom Typ%ROWTYPE

  • Drei Variablen: eine vom Typ rentals.tape_id%TYPE, einer vom Typ 0.customer_id%TYPE und der letzte vom Typ %.rental_date%TYP

  • Eine Variable vom Typ RECORD

Schauen wir uns jeden dieser Zieltypen genauer an.

Wenn Sie in eine Variable eines %ROWTYPE , können Sie mit der üblichen Variablen auf die einzelnen Spalten verweisen.spaltennotation. Zum Beispiel:

Als nächstes kann ich in eine durch Kommas getrennte Liste von Variablen abrufen. Im vorherigen Beispiel gibt der Cursor rental_cursor Zeilen zurück, die jeweils drei Spalten enthalten. Anstatt in eine %ROWTYPE Variable %ROWTYPE , kann ich drei separate Variablen (der entsprechenden Typen) deklarieren und stattdessen in diese abrufen:

Sie müssen keine mit %TYPE deklarierten Variablen verwenden, aber dies ist der perfekte Ort dafür. Wenn Sie Variablen mit %TYPE deklarieren, werden Ihre Funktionen in Fällen, in denen sich die referenzierten Spaltentypen ändern können, viel weniger anfällig.

Zusammengesetzte Variablen und skalare Variablen können nicht in derselben FETCH-Anweisung kombiniert werden:

Dies scheint mir ein Fehler zu sein. Möglicherweise können Sie in einer zukünftigen Version zusammengesetzte und skalare Variablen kombinieren.

Der dritte Zieltyp, den Sie mit einer FETCH-Anweisung verwenden können, ist eine Variable vom Typ RECORD. Sie erinnern sich vielleicht an früher in diesem Kapitel, dass eine Datensatzvariable so etwas wie ein Chamäleon ist?es passt sich an jede Art von Daten an, die Sie eingeben. Das folgende Snippet verwendet beispielsweise dieselbe Datensatzvariable, um zwei unterschiedlich geformte Zeilen zu speichern:

Woher wissen Sie, ob eine Zeile tatsächlich abgerufen wurde, nachdem Sie eine FETCH-Anweisung ausgeführt haben? Wenn Sie nach dem Abrufen des gesamten Ergebnisses abrufen, tritt kein Fehler auf. Stattdessen hat jede PL / pgSQL-Funktion eine automatisch deklarierte Variable namens FOUND . FOUND ist eine BOOLESCHE Variable, die vom PL / pgSQL-Interpreter gesetzt wird, um verschiedene Arten von Statusinformationen anzuzeigen. Tabelle 7.1 listet die Zeitpunkte auf, zu denen PL/pgSQL die GEFUNDENE Variable und die entsprechenden Werte setzt.

Tabelle 7.1. GEFUNDENE Ereignisse und Werte

Veranstaltung

Wert

Beginn jeder Funktion

FALSCH

Beginn einer Integer-FOR-Schleife

FALSCH

Innerhalb einer Integer-FOR-Schleife

WAHR

Beginn eines FOR…Schleife AUSWÄHLEN

FALSCH

Innerhalb eines FÜR…Schleife AUSWÄHLEN

WAHR

Vor SELECT INTO Anweisung

FALSCH

Nach SELECT INTO Anweisung

TRUE (wenn Zeilen zurückgegeben werden)

Vor FETCH-Anweisung

FALSCH

Nach FETCH-Anweisung

TRUE (wenn eine Zeile zurückgegeben wird)

Sie können also sehen, dass FOUND auf TRUE gesetzt ist, wenn eine FETCH-Anweisung eine Zeile zurückgibt. Lassen Sie uns sehen, wie Sie alle cursorbezogenen Anweisungen in einer einzigen PL / pgSQL-Funktion zusammenfassen:

Das erste, was Sie in diesem Codeausschnitt tun, ist, den Cursor zu öffnen. Als nächstes geben Sie eine SCHLEIFE ein, die jede vom Cursor zurückgegebene Zeile verarbeitet. Innerhalb der SCHLEIFE rufen Sie einen einzelnen Datensatz ab, verlassen die Schleife, wenn der Cursor erschöpft ist, und rufen eine andere Funktion (process_rental()) auf, wenn nicht. Nachdem die Schleife beendet ist, schließen Sie den Cursor mit der CLOSE-Anweisung.

Bisher sieht es so aus, als wäre eine Cursorschleife so ziemlich die gleiche wie eine FOR-IN-SELECT Schleife. Was kann man sonst noch mit einem Cursor machen?

Parametrisierte Cursor

Sie haben gesehen, dass Sie eine SELECT-Anweisung bereitstellen müssen, wenn Sie einen CURSOR deklarieren. Sehr oft werden Sie feststellen, dass Sie die genauen Werte der Abfrage zum Zeitpunkt des Schreibens einer Funktion nicht kennen. Sie können einen parametrisierten Cursor deklarieren, um dieses Problem zu lösen.

Ein parametrisierter Cursor ähnelt im Konzept einer parametrisierten Funktion. Wenn Sie eine Funktion definieren, können Sie eine Reihe von Parametern deklarieren (diese werden als formale Parameter oder formale Argumente bezeichnet); diese Parameter können innerhalb der Funktion verwendet werden, um die Ergebnisse der Funktion zu ändern. Wenn Sie eine Funktion ohne Parameter definieren, gibt die Funktion immer die gleichen Ergebnisse zurück (es sei denn, sie wird durch globale, externe Daten beeinflusst). Jede Sprache legt fest, wo Sie einen Parameter innerhalb einer Funktion verwenden können. Im Allgemeinen können Funktionsparameter überall dort verwendet werden, wo ein wertgebender Ausdruck verwendet werden kann. Wenn Sie eine parametrisierte Funktion aufrufen, geben Sie für jeden Parameter einen Wert an: Die von Ihnen angegebenen Werte (diese werden als tatsächliche Parameter oder tatsächliche Argumente bezeichnet) werden innerhalb der Funktion überall dort ersetzt, wo die formalen Parameter angezeigt werden.

Wenn Sie einen Cursor definieren, können Sie einen Satz formaler Parameter deklarieren; diese Parameter können mit dem Cursor verwendet werden, um die Ergebnismenge der Abfrage zu ändern. Wenn Sie einen Cursor ohne Parameter definieren, gibt die Abfrage immer dieselbe Ergebnismenge zurück, es sei denn, sie wird durch externe Daten beeinflusst. PL / pgSQL schränkt die Orte ein, an denen Sie einen Parameter innerhalb einer Cursordefinition verwenden können. Ein Cursorparameter kann überall dort verwendet werden, wo ein wertgebender Ausdruck verwendet werden kann. Wenn Sie einen Cursor öffnen, müssen Sie für jeden formalen Parameter Werte angeben. Die tatsächlichen Parameter werden innerhalb des Cursors ersetzt, wo immer die formalen Parameter erscheinen.

Sehen wir uns ein Beispiel an:

Die Zeilen 3, 4 und 5 deklarieren einen parametrisierten Cursor. Dieser Cursor hat einen einzigen formalen Parameter; eine Ganzzahl namens ID. Beachten Sie (am Ende von Zeile 5), dass ich den formalen Parameter innerhalb der Cursordefinition verwendet habe. Wenn ich diesen Cursor öffne, gebe ich einen GANZZAHLIGEN Wert für den ID-Parameter an. Der tatsächliche Parameter, den ich zur Verfügung stelle, wird überall dort in die Abfrage eingefügt, wo der formale Parameter verwendet wird. Wenn also target_customer beispielsweise 42 ist, liest der in Zeile 10 geöffnete Cursor:

SELECT * FROM customers WHERE customer_id = 42;

Die vollständige Syntax für eine Cursordeklaration lautet

variable-name CURSOR ) ] FOR select-query;

Die vollständige Syntax für eine OPEN-Anweisung lautet

OPEN cursor-name ) ];

Sie würden einen Cursor aus den gleichen Gründen parametrisieren, aus denen Sie eine Funktion parametrisieren würden: Sie möchten, dass die Ergebnisse von den tatsächlichen Argumenten abhängen. Wenn Sie einen Cursor parametrisieren, wird der Cursor auch wiederverwendbarer. Zum Beispiel möchte ich vielleicht alle Bänder in meinem Inventar verarbeiten, aber ich möchte die Bänder einzeln verarbeiten. Wenn ich keinen parametrisierten Cursor verwende, muss ich für jeden meiner Verteiler einen Cursor deklarieren (und ich muss die Menge der Verteiler zum Zeitpunkt des Schreibens der Funktion kennen). Mit einem parametrisierten Cursor kann ich den Cursor einmal deklarieren und bei jedem Öffnen des Cursors unterschiedliche tatsächliche Argumente angeben:

Beachten Sie, dass Sie einen Cursor so oft öffnen und schließen können, wie Sie möchten. Ein Cursor muss geschlossen werden, bevor er geöffnet werden kann. Jedes Mal, wenn Sie einen parametrisierten Cursor öffnen, können Sie neue aktuelle Parameter angeben.

Cursorreferenzen

Wenden wir uns nun einem anderen Aspekt der Cursorunterstützung in PL / pgSQL zu?cursor-Referenzen.

Wenn Sie eine Cursorvariable deklarieren, geben Sie eine SELECT-Anweisung an, die an den Cursor gebunden ist. Sie können den Text der Abfrage nicht ändern, nachdem der Cursor deklariert wurde. Natürlich können Sie die Abfrage parametrisieren, um die Ergebnisse zu ändern, aber die Form der Abfrage bleibt gleich: Wenn die Abfrage Zeilen aus der Tapes-Tabelle zurückgibt, werden immer Zeilen aus der Tapes-Tabelle zurückgegeben.

Anstatt einen CURSOR zu deklarieren, können Sie eine Variable vom Typ REFCURSOR deklarieren. Ein REFCURSOR ist eigentlich kein Cursor, sondern ein Verweis auf einen Cursor. Die Syntax zum Deklarieren eines REFCURSORS lautet

DECLARE ref-name REFCURSOR; ...

Beachten Sie, dass Sie beim Erstellen eines REFCURSORS keine Abfrage angeben. Stattdessen ist ein Cursor zur Laufzeit an einen REFCURSOR gebunden. Hier ist ein einfaches Beispiel:

In diesem Block habe ich zwei Cursor und eine Cursorreferenz deklariert. Einer der Cursor gibt Zeilen aus der rentals-Tabelle und der andere Zeilen aus der Tapes-Tabelle zurück.

In Zeile 9 öffnet sich der Cursor next_rental. In Zeile 10 gebe ich der next_row Cursorreferenz einen Wert. Wir haben jetzt zwei Möglichkeiten, auf den next_rental Cursor zuzugreifen: über die next_rental Cursorvariable und über die next_row Cursorreferenz. An diesem Punkt bezieht sich next_row auf den next_rental Cursor. Sie können (in den Zeilen 11 und 12) sehen, dass Sie eine Zeile mit einer der beiden Variablen abrufen können. Beide FETCH-Anweisungen geben eine Zeile aus derselben Tabelle zurück.

In Zeile 14 zeigt die next_row-Cursorreferenz auf einen anderen Cursor. Wenn Sie nun von next_row abrufen, erhalten Sie eine Zeile aus der folgenden Tabelle. Beachten Sie, dass Sie next_row auf einen Cursor zeigen können, der noch nicht geöffnet wurde. Sie können einen Cursor mithilfe einer Cursorreferenz SCHLIEßEN, einen Cursor jedoch nicht mithilfe einer Cursorreferenz ÖFFNEN.

Tatsächlich können Sie einen Cursor mit einem REFCURSOR öffnen. Wenn Sie eine Cursorvariable deklarieren, erstellen Sie tatsächlich einen PostgreSQL-Cursor, dessen Name mit dem Namen der Variablen übereinstimmt. Im vorherigen Beispiel haben Sie einen Cursor (nicht nur eine Cursorvariable) mit dem Namen next_rental und einen Cursor mit dem Namen next_tape . Mit PL / pgSQL können Sie anonyme Cursor mit REFCURSOR-Variablen erstellen. Ein anonymer Cursor ist ein Cursor, der keinen Namen hat. Sie erstellen einen anonymen Cursor mit der OPEN-Anweisung, einem REFCURSOR und einer SELECT-Anweisung:

Ein anonymer Cursor hat tatsächlich einen Namen, aber PostgreSQL konstruiert den Namen und ist nicht sehr leserfreundlich. Ein anonymer Cursor hat einen Namen wie <unbenannter Cursor 42>.

1 ...2 DECLARE3 next_row REFCURSOR;4 BEGIN5 OPEN next_row FOR SELECT * FROM customers;6 ...

In Zeile 5 erstellen Sie einen anonymen Cursor und binden ihn an die next_row Cursorreferenz. Nachdem ein anonymer Cursor geöffnet wurde, können Sie ihn wie jeden anderen Cursor behandeln. Sie können es abrufen, SCHLIEßEN und verlieren. Dieser letzte Teil klingt vielleicht ein wenig fischig, also lass es mich weiter erklären. Schauen Sie sich das folgende Codefragment genau an:

Diese Funktion enthält zwei Schleifen: eine äußere Schleife, die die Kundentabelle durchliest, und eine innere Schleife, die jede Tabelle für einen bestimmten Kunden liest. Der next_customer Cursor wird geöffnet (in Zeile 10), bevor die äußere Schleife beginnt. Der next_rental Cursor wird gebunden und geöffnet (in den Zeilen 15, 16 und 17), kurz bevor die innere Schleife beginnt. Nachdem die innere Schleife abgeschlossen ist, setze ich die next_rental Cursorreferenz auf NULL und fahre mit der äußeren Schleife fort. Was passiert mit dem Cursor, der an next_rental gebunden war? Ich habe den Cursor nicht explizit geschlossen, daher muss er offen bleiben. Nachdem ich die Zuweisungsanweisung in Zeile 29 ausgeführt habe, kann ich nicht mehr auf den Cursor zugreifen??denken Sie daran, es ist ein anonymer Cursor, daher kann ich nicht namentlich darauf verweisen. Diese Situation wird als Ressourcenleck bezeichnet. Ein Ressourcenleck tritt auf, wenn Sie ein Objekt (in diesem Fall einen Cursor) erstellen und dann alle Verweise auf dieses Objekt verlieren. Wenn Sie das Objekt nicht wiederfinden, können Sie die Ressource nicht freigeben. Vermeiden Sie Ressourcenlecks; Sie sind böse und können Leistungsprobleme verursachen. Ressourcenlecks führen auch dazu, dass Ihr Code fehlschlägt, wenn Ihnen eine Ressource (z. B. Speicherplatz) ausgeht. Wir können das in diesem Beispiel gezeigte Ressourcenleck vermeiden, indem wir next_rental schließen, bevor wir es auf NULL setzen.

Sie haben gesehen, was mit einer Cursorreferenz nicht zu tun ist, aber lassen Sie uns sehen, wofür Cursorreferenzen wirklich gut sind. Das Schöne an einer Cursorreferenz ist, dass Sie die Referenz an eine andere Funktion übergeben oder eine Referenz an den Aufrufer zurückgeben können. Dies sind leistungsstarke Funktionen. Indem Sie Cursorreferenzen zwischen Funktionen freigeben, können Sie Ihren PL / pgSQL-Code in wiederverwendbare Teile zerlegen.

Eine der effektiveren Möglichkeiten, Cursorreferenzen zu verwenden, besteht darin, den Code, der einen Cursor verarbeitet, von dem Code zu trennen, der den Cursor erstellt. Sie können beispielsweise feststellen, dass wir eine Funktion benötigen, um den Gesamtbetrag zu berechnen, den wir über einen bestimmten Zeitraum von einem bestimmten Kunden erhalten haben. Ich könnte damit beginnen, eine einzelne Funktion zu erstellen, die einen Cursor konstruiert und jede Zeile in diesem Cursor verarbeitet:

Dies ist ein guter Anfang, aber es funktioniert nur für einen einzigen Satz von Bedingungen: einen bestimmten Kunden und ein bestimmtes Datumspaar. Stattdessen können Sie diese eine Funktion in drei separate Funktionen faktorisieren.

Die erste Funktion erstellt einen Cursor, der beim Öffnen alle Vermietungsdatensätze für einen bestimmten Kunden innerhalb eines bestimmten Zeitraums zurückgibt; Der Cursor wird an den Aufrufer zurückgegeben:

Die zweite Funktion berechnet bei einem Cursor, der Vermietungsdatensätze zurückgibt, den Gesamtwert der über diesen Cursor zugänglichen Vermietungen:

Die letzte Funktion ruft die ersten beiden auf:

Der Vorteil dieses Ansatzes besteht darin, dass Sie einen cursor mit verschiedenen Auswahlkriterien und rufen compute_total_value(). Sie können beispielsweise die Gesamtwerte aller Ausleihungen eines bestimmten Bandes berechnen:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.