Mit gespeicherten SQL-Prozeduren arbeiten

Eine gespeicherte Prozedur ist eine Sammlung von Anweisungen, die von anderen Abfragen oder gespeicherten Prozeduren aufgerufen werden können. Eine Prozedur kann Eingabeargumente annehmen und Werte als Ausgabe zurückgeben. Prozeduren werden in einem BigQuery-Dataset benannt und gespeichert. Eine gespeicherte Prozedur kann auf Daten in mehreren Datasets von mehreren Nutzern zugreifen oder diese ändern. Sie kann auch eine Abfrage mit mehreren Anweisungen enthalten.

Einige gespeicherte Prozeduren sind in BigQuery eingebunden und müssen nicht erstellt werden. Diese werden als Systemprozeduren bezeichnet. Weitere Informationen dazu finden Sie in der Referenz zu Systemprozeduren.

Gespeicherte Verfahren unterstützen prozedurale Sprachanweisungen, mit denen Sie Variablen definieren und Ablaufsteuerung implementieren können. Weitere Informationen zu prozeduralen Sprachanweisungen finden Sie in der Referenz zu prozeduralen Sprachen.

Gespeicherte Prozedur erstellen

Verwenden Sie zum Erstellen eines Datasets die Anweisung CREATE PROCEDURE.

Im folgenden konzeptionellen Beispiel stellt procedure_name die Prozedur dar und der Text der Prozedur wird zwischen den Anweisungen BEGIN und END angezeigt:

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

Das folgende Beispiel zeigt ein Verfahren mit einer Abfrage mit mehreren Anweisungen. Mit der Abfrage mit mehreren Anweisungen wird eine Variable festgelegt, eine INSERT-Anweisung ausgeführt und das Ergebnis als formatierter Textstring angezeigt.

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

Im vorherigen Beispiel lautet der Name der Prozedur mydataset.create_customer. Der Prozedurtext erscheint zwischen den Anweisungen BEGIN und END.

Verwenden Sie die Anweisung CALL, um die Prozedur aufzurufen:

CALL mydataset.create_customer();

Wert mit Eingabeparameter übergeben

Eine Prozedur kann Eingabeparameter haben. Ein Eingabeparameter ermöglicht die Eingabe einer Prozedur, lässt jedoch keine Ausgabe zu.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Wert mit Ausgabeparameter übergeben

Eine Prozedur kann Ausgabeparameter haben. Ein Ausgabeparameter gibt einen Wert aus der Prozedur zurück, lässt jedoch keine Eingabe für die Prozedur zu. Verwenden Sie zum Erstellen eines Ausgabeparameters das Schlüsselwort OUT vor dem Namen des Parameters.

Diese Version der Prozedur gibt beispielsweise die neue Kundennummer mit dem Parameter id zurück:

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Zum Aufrufen dieser Prozedur müssen Sie eine Variable verwenden, um den Ausgabewert zu erhalten:

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

Wert mit Eingabe-/Ausgabeparameter übergeben

Eine Prozedur kann auch Eingabe-/Ausgabeparameter haben. Ein Eingabe-/Ausgabeparameter gibt einen Wert der Prozedur zurück und akzeptiert auch eine Eingabe für die Prozedur. Verwenden Sie zum Erstellen eines Eingabe-/Ausgabeparameters das Keyword INOUT vor dem Namen des Parameters. Weitere Informationen finden Sie unter Argumentmodus.

Routinen autorisieren

Sie können gespeicherte Prozeduren als Routinen autorisieren. Mit autorisierten Routinen können Sie Abfrageergebnisse für bestimmte Nutzer oder Gruppen freigeben, ohne ihnen Zugriff auf die zugrunde liegenden Tabellen zu gewähren, mit denen die Ergebnisse generiert wurden. Beispielsweise kann eine autorisierte Routine eine Aggregation von Daten berechnen oder einen Tabellenwert abrufen und diesen Wert in einer Berechnung verwenden.

Autorisierte Routinen können Tabellen erstellen, löschen und Tabellen bearbeiten sowie Andere gespeicherte Prozeduren für die zugrunde liegende Tabelle aufrufen.

Weitere Informationen zu Autorisierte Routinen.

Gespeicherte Prozedur aufrufen

Verwenden Sie die Anweisung CALL, um eine gespeicherte Prozedur nach dem Erstellen aufzurufen. Mit der folgenden Anweisung wird beispielsweise die gespeicherte Prozedur create_customer aufgerufen:

CALL mydataset.create_customer();

Systemprozedur aufrufen

Mit der Anweisung CALL können Sie eine integrierte Systemprozedur aufrufen. Mit der folgenden Anweisung wird beispielsweise die Systemprozedur BQ.REFRESH_MATERIALIZED_VIEW aufgerufen:

CALL BQ.REFRESH_MATERIALIZED_VIEW;