Trabalhar com procedimentos armazenados em SQL

Um procedimento armazenado é um conjunto de instruções que podem ser chamadas a partir de outras consultas ou outros procedimentos armazenados. Um procedimento pode pegar argumentos de entrada e retornar valores como saída. Nomeie e armazene um procedimento em um conjunto de dados do BigQuery. Um procedimento armazenado pode acessar ou modificar dados em vários conjuntos de dados por vários usuários. Ela também pode conter uma consulta de várias instruções.

Alguns procedimentos armazenados são integrados ao BigQuery e não precisam ser criados. Eles são chamados de procedimentos do sistema, e é possível saber mais sobre eles na Referência de procedimentos do sistema.

Os procedimentos armazenados são compatíveis com instruções de linguagem processual, que permitem definir variáveis e implementar o fluxo de controle. Saiba mais sobre declarações de linguagem processual na Referência de linguagem processual.

Criar um procedimento armazenado

Para criar um conjunto de dados, use a instrução CREATE PROCEDURE.

No exemplo conceitual a seguir, procedure_name representa o procedimento. O corpo do procedimento aparece entre as instruções BEGIN e END:

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

O exemplo a seguir mostra um procedimento que contém uma consulta de várias instruções. A consulta de várias instruções define uma variável, executa uma instrução INSERT e exibe 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 instruções de BEGIN e END.

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

CALL mydataset.create_customer();

Transmitir um valor com um parâmetro de entrada

Um procedimento pode ter parâmetros de entrada. Um parâmetro de entrada permite 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

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

Um procedimento pode ter parâmetros de saída. Um parâmetro de saída retorna um valor do procedimento, mas não permite a entrada do 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 retorna o novo ID de cliente pelo 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, você precisa 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;

transmitir um valor de entrada e saída 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 retorna um valor do procedimento e também aceita a entrada do procedimento. Para criar um parâmetro de entrada/saída, use a palavra-chave INOUT antes do nome do parâmetro. Para conferir mais informações, consulte Modo de argumento.

Autorizar rotinas

É possível autorizar procedimentos armazenados como routines. As rotinas autorizadas permitem compartilhar resultados de consultas com usuários ou grupos específicos sem conceder acesso às tabelas subjacentes que geraram os resultados. Por exemplo, uma rotina autorizada pode calcular uma agregação sobre os dados ou procurar um valor de tabela e usá-lo em um cálculo.

As rotinas autorizadas podem create, eliminar manipular tabelas, além de invocar outros procedimentos armazenados na tabela.

Para mais informações, consulte Rotinas autorizadas.

Chamar um procedimento armazenado

Para chamar um procedimento armazenado depois de criá-lo, use a instrução CALL. Por exemplo, a instrução a seguir chama o procedimento armazenado create_customer:

CALL mydataset.create_customer();

Observação: chamar um procedimento armazenado em vez de incluir as instruções SQL do procedimento diretamente na consulta gera um pequeno overhead de desempenho.

Chamar um procedimento do sistema

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

CALL BQ.REFRESH_MATERIALIZED_VIEW;