Utilizzo delle stored procedure SQL

Una stored procedure è una raccolta di istruzioni che possono essere richiamate da altre query o altre stored procedure. Una procedura può utilizzare argomenti di input e restituire valori come output. Puoi assegnare un nome e archiviare una procedura in un set di dati BigQuery. Una stored procedure 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. Queste sono chiamate procedure di sistema e puoi scoprire di più in merito nella documentazione di riferimento sulle procedure di sistema.

Le stored procedure supportano le istruzioni di linguaggio procedurale, che ti consentono di eseguire operazioni come la definizione delle variabili e l'implementazione del flusso di controllo. Puoi scoprire di più sulle dichiarazioni linguistiche procedurali nel Riferimento 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 è riportato 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 di una procedura, ma non 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

Trasmettere 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 della procedura. Per creare un parametro di output, utilizza la parola chiave OUT prima del nome del parametro.

Ad esempio, questa versione della procedura restituisce il nuovo ID 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ò anche avere 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 Modalità argomento.

Autorizza routine

Puoi autorizzare le stored procedure come routine. Le routine autorizzate ti consentono di condividere i risultati delle 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 sui dati o cercare un valore di tabella e utilizzarlo in un calcolo.

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

Per ulteriori informazioni, consulta Routine autorizzate.

Chiamare una stored procedure

Per chiamare una stored procedure dopo la creazione, utilizza l'istruzione CALL. Ad esempio, l'istruzione seguente 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, l'istruzione seguente chiama la procedura di sistema BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;