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 rotinas. 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 criar, 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();
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;