Trabalhe com procedimentos armazenados de SQL

Um procedimento armazenado é uma coleção de declarações que podem ser chamadas a partir de outras consultas ou outros procedimentos armazenados. Um procedimento pode receber argumentos de entrada e devolver valores como saída. Dá um nome e armazena um procedimento num conjunto de dados do BigQuery. Um procedimento armazenado pode aceder ou modificar dados em vários conjuntos de dados por vários utilizadores. Também pode conter uma consulta com várias declarações.

Alguns procedimentos armazenados estão integrados no BigQuery e não precisam de ser criados. Estes são denominados procedimentos do sistema e pode saber mais acerca deles na referência de procedimentos do sistema.

Os procedimentos armazenados suportam declarações de linguagem processual, que lhe permitem fazer ações como definir variáveis e implementar o fluxo de controlo. Pode saber mais acerca das declarações de linguagem processual na referência de linguagem processual.

Crie um procedimento armazenado

Para criar um procedimento, use a declaração CREATE PROCEDURE.

No exemplo conceptual seguinte, procedure_name representa o procedimento e o corpo do procedimento aparece entre as declarações BEGIN e END:

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

O exemplo seguinte mostra um procedimento que contém uma consulta com várias declarações. A consulta com várias declarações define uma variável, executa uma declaração INSERT e apresenta o resultado como uma string de texto formatada.

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

No exemplo anterior, o nome do procedimento é mydataset.create_customer e o corpo do procedimento aparece entre as declarações BEGIN e END.

Para chamar o procedimento, use a declaração CALL:

CALL mydataset.create_customer();

Transmita um valor com um parâmetro de entrada

Um procedimento pode ter parâmetros de entrada. Um parâmetro de entrada permite a entrada para um procedimento, mas não permite a saída.

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

Transmita um valor com um parâmetro de saída

Um procedimento pode ter parâmetros de saída. Um parâmetro de saída devolve um valor do procedimento, mas não permite a entrada para o procedimento. Para criar um parâmetro de saída, use a palavra-chave OUT antes do nome do parâmetro.

Por exemplo, esta versão do procedimento devolve o ID do novo cliente através do parâmetro 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

Para chamar este procedimento, tem de usar uma variável para receber o valor de saída:

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

Transmita um valor com um parâmetro de entrada/saída

Um procedimento também pode ter parâmetros de entrada/saída. Um parâmetro de entrada/saída devolve um valor do procedimento e também aceita entradas para o procedimento. Para criar um parâmetro de entrada/saída, use a palavra-chave INOUT antes do nome do parâmetro. Para mais informações, consulte o artigo Modo de argumento.

Autorize rotinas

Pode autorizar procedimentos armazenados como rotinas. As rotinas autorizadas permitem-lhe partilhar resultados de consultas com utilizadores ou grupos específicos sem lhes conceder acesso às tabelas subjacentes que geraram os resultados. Por exemplo, uma rotina autorizada pode calcular uma agregação sobre dados ou procurar um valor de tabela e usar esse valor num cálculo.

As rotinas autorizadas podem criar, eliminar, e manipular tabelas, bem como invocar outros procedimentos armazenados na tabela subjacente.

Para mais informações, consulte o artigo Rotinas autorizadas.

Chame um procedimento armazenado

Para chamar um procedimento armazenado depois de criado, use a declaração CALL. Por exemplo, a seguinte declaração chama o procedimento armazenado create_customer:

CALL mydataset.create_customer();

Nota: chamar um procedimento armazenado em vez de incluir as declarações SQL do procedimento diretamente na sua consulta introduz uma pequena sobrecarga de desempenho.

Chame um procedimento do sistema

Para chamar um procedimento do sistema integrado, use a declaração CALL. Por exemplo, a seguinte declaração chama o procedimento do sistema BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;