Erstellen von SQL zur Laufzeit¶

Snowflake unterstützt mehrere verschiedene Techniken, um Zeichenfolgen von SQL-Anweisungen dynamisch zur Laufzeit zu konstruieren. Mithilfe dieser Techniken können Sie allgemeinere und flexiblere SQL-Zeichenfolgen für Anwendungsfälle angeben, in denen der vollständige Text der SQL-Anweisungen bis zur Laufzeit unbekannt ist.

Eine gespeicherte Prozedur oder Anwendung kann Benutzereingaben akzeptieren und diese Eingaben dann in einer SQL-Anweisung verwenden. In einer Tabelle können zum Beispiel Informationen über Kundenaufträge gespeichert werden. Eine Anwendung oder eine gespeicherte Prozedur kann eine Bestell-ID als Eingabe akzeptieren und eine Abfrage ausführen, die nur die Ergebnisse für diese spezielle Bestellung liefert.

Ein Entwickler kann Code für gespeicherte Prozeduren oder Anwendungscode mit SQL-Anweisungen schreiben, die Platzhalter enthalten, und dann Variablen an diese Platzhalter im Code binden. Diese Platzhalter werden Bindungsvariablen genannt. Ein Entwickler kann auch Code schreiben, der SQL-Anweisungen aus einer Eingabefolge konstruiert (z. B. durch Verkettung von Zeichenfolgen, die einen SQL-Befehl, Parameter und Werte enthalten).

Die folgenden Techniken stehen zur Verfügung, um SQL-Anweisungen dynamisch zur Laufzeit zu erstellen:

Die Funktion TO_QUERY – Diese Funktion nimmt eine Zeichenfolge SQL mit optionalen Parametern als Eingabe.
Dynamisches SQL – Der Code in einer gespeicherten Prozedur oder Anwendung nimmt Eingaben entgegen und konstruiert anhand dieser Eingaben eine dynamische SQL-Anweisung. Der Code kann Teil einer gespeicherten Prozedur – Snowflake Scripting oder Javascript – oder eines anonymen Snowflake Scripting-Blocks sein. Sie können diese Technik auch in Ihrem Anwendungscode verwenden, der einen Snowflake-Treiber oder den Snowflake SQL-REST-API verwendet.

Bemerkung

Wenn Programme SQL-Anweisungen mit Benutzereingaben erstellen, gibt es potenzielle Sicherheitsrisiken, wie z. B. Einschleusung von SQL-Befehlen. Wenn Eingaben für SQL-Anweisungen aus externen Quellen stammen, stellen Sie sicher, dass diese validiert sind. Weitere Informationen dazu finden Sie unter Einschleusung von SQL-Befehlen.

Verwenden Sie die Funktion TO_QUERY¶

Sie können die Funktion TO_QUERY im Code für gespeicherte Prozeduren und Anwendungen verwenden, die SQL-Anweisungen dynamisch konstruieren. Diese Tabellenfunktion nimmt eine SQL-Zeichenfolge als Eingabe. Optional kann die Zeichenfolge SQL-Parameter enthalten, und Sie können die Argumente, die an die Parameter übergeben werden sollen, als Bindungsvariablen angeben.

Im Folgenden finden Sie ein einfaches Beispiel, das die Funktion aufruft:

SELECT COUNT(*) FROM TABLE(TO_QUERY('SELECT 1'));

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Das folgende Beispiel verwendet die Funktion TO_QUERY in einer gespeicherten Prozedur:

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;

Copy

Hinweis: Wenn Sie SnowSQL, die Classic Console oder die execute_stream- oder execute_string-Methode im Python-Konnektor-Code verwenden, benutzen Sie stattdessen das folgende Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL, in der Classic Console und im Python-Konnektor):

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

Rufen Sie die gespeicherte Prozedur auf:

CALL get_num_results_tq('SELECT 1');

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Dynamische SQL in gespeicherten Prozeduren und Anwendungen verwenden¶

Um SQL-Anweisungen zu erstellen, die Eingaben des Benutzers berücksichtigen, können Sie dynamische SQL in einer Snowflake Scripting oder Javascript gespeicherten Prozedur oder in einem anonymen Snowflake Scripting Block verwenden. Sie können auch dynamische SQL in Ihrem Anwendungscode verwenden, der einen Snowflake-Treiber oder die Snowflake SQL-REST-API verwendet.

In diesem Beispiel wird eine gespeicherte Prozedur mit Snowflake Scripting erstellt. Die Prozedur nimmt SQL-Text als Eingabe und konstruiert eine Zeichenfolge, die eine SQL-Anweisung enthält, indem sie den Text an diese anhängt. Die dynamische SQL wird dann mit dem Befehl EXECUTE IMMEDIATE ausgeführt.

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;

Copy

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
$$
;

Copy

Das folgende Beispiel ruft die Prozedur auf:

CALL get_num_results('SELECT 1');

Copy

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

Dynamic SQL unterstützt Bindungsvariablen Das folgende Snowflake Scripting-Beispiel verwendet Bindungsvariablen, die durch die Platzhalter ? dargestellt werden, um SQL-Anweisungen dynamisch zur Laufzeit zu konstruieren. Dieser Block wählt Daten aus der folgenden Tabelle invoices aus:

CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2));
INSERT INTO invoices (price) VALUES
  (11.11),
  (22.22);

Copy

Führen Sie den anonymen Block aus:

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

+-------+
| PRICE |
|-------|
| 22.22 |
+-------+

Vergleich der Techniken zur dynamischen Erstellung von SQL¶

Die folgende Tabelle beschreibt die Vor- und Nachteile der Techniken zur dynamischen Erstellung von SQL-Anweisungen.

Technik	Vorteile	Benachteiligungen
Funktion TO_QUERY	Einfache Syntax Eingebaute Fehlerbehandlung Spezifische Semantik für den Anwendungsfall der dynamischen Erstellung von SQL Automatisch ermitteltes Resultset	Abfragen können vor der Ausführung nicht beschrieben oder erklärt werden Nur gültig in der FROM-Klausel einer SELECT-Anweisung Snowflake-spezifisch
Dynamisch SQL	Allgemeiner und flexibler als die Funktion TO_QUERY Abfragen können vor der Ausführung beschrieben oder erklärt werden	Komplexer als die TO_QUERY-Funktion Manuelle Fehlerbehandlung