Utilizzare le stored procedure SQL

Una stored procedure è una raccolta di istruzioni che possono essere richiamate da altre query o altre stored procedure. Una procedura può accettare argomenti di input e restituire valori come output. Assegni un nome e memorizzi una procedura in un set di dati BigQuery. Una procedura memorizzata può accedere o modificare i dati di più set di dati da parte di più utenti. Può anche contenere una query con più istruzioni.

Alcune stored procedure sono integrate in BigQuery e non devono essere create. Si tratta delle procedure di sistema e puoi scoprire di più su di esse nel riferimento alle procedure di sistema.

Le stored procedure supportano le istruzioni del linguaggio procedurale, che ti consentono di eseguire operazioni come definire variabili e implementare il flusso di controllo. Puoi scoprire di più sulle dichiarazioni del linguaggio procedurale nel riferimento al linguaggio procedurale.

Crea una stored procedure

Per creare una procedura, utilizza l'istruzione CREATE PROCEDURE.

Nel seguente esempio concettuale, procedure_name rappresenta la procedura e il corpo della procedura viene visualizzato tra le istruzioni BEGIN e END:

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

L'esempio seguente mostra una procedura che contiene una query con più istruzioni. La query con più istruzioni imposta una variabile, esegue un'istruzione INSERT e visualizza il risultato come stringa di testo formattata.

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

Nell'esempio precedente, il nome della procedura è mydataset.create_customer e il corpo della procedura viene visualizzato tra le istruzioni BEGIN e END.

Per chiamare la procedura, utilizza l'istruzione CALL:

CALL mydataset.create_customer();

Passare un valore con un parametro di input

Una procedura può avere parametri di input. Un parametro di input consente l'input per una procedura, ma non consente l'output.

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

Passare un valore con un parametro di output

Una procedura può avere parametri di output. Un parametro di output restituisce un valore dalla procedura, ma non consente l'input per la procedura. Per creare un parametro di output, utilizza la parola chiave OUT prima del nome del parametro.

Ad esempio, questa versione della procedura restituisce l'ID del nuovo cliente tramite il parametro id:

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

Per chiamare questa procedura, devi utilizzare una variabile per ricevere il valore di output:

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

Passare un valore in entrata e in uscita con un parametro di input/output

Una procedura può avere anche parametri di input/output. Un parametro di input/output restituisce un valore dalla procedura e accetta anche input per la procedura. Per creare un parametro di input/output, utilizza la parola chiave INOUT prima del nome del parametro. Per ulteriori informazioni, consulta la sezione Modalità Argomento.

Autorizzare le routine

Puoi autorizzare le stored procedure come routine. Le routine autorizzate ti consentono di condividere i risultati di una query con utenti o gruppi specifici senza concedere loro l'accesso alle tabelle sottostanti che hanno generato i risultati. Ad esempio, una routine autorizzata può calcolare un'aggregazione su dati o cercare un valore di tabella e utilizzarlo in un calcolo.

Le routine autorizzate possono creare, eliminare, e manipolare le tabelle, nonché richiamare altre stored procedure sulla tabella sottostante.

Per ulteriori informazioni, vedi Routine autorizzate.

Chiama una stored procedure

Per chiamare una stored procedure dopo la sua creazione, utilizza l'istruzione CALL. Ad esempio, la seguente dichiarazione chiama la stored procedure create_customer:

CALL mydataset.create_customer();

Chiama una procedura di sistema

Per chiamare una procedura di sistema integrata, utilizza l'istruzione CALL. Ad esempio, la seguente dichiarazione chiama la procedura di sistema BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;