Como escrever procedimentos armazenados no Script Snowflake¶
Este tópico fornece uma introdução à escrita de um procedimento armazenado em SQL usando o Snowflake Scripting. Para obter mais informações sobre o Snowflake Scripting, consulte Guia do Desenvolvedor do Script Snowflake.
Neste tópico:
Introdução¶
Para escrever um procedimento armazenado que utiliza o Script Snowflake:
Use o comando CREATE PROCEDURE ou WITH … CALL … com LANGUAGE SQL.
No corpo do procedimento armazenado (a cláusula AS), use um bloco Script Snowflake.
Nota
Se você estiver criando um procedimento de Script Snowflake no SnowSQL ou na Classic Console, você deve usar delimitadores de literais de cadeia de caracteres (
'
ou$$
) ao redor do corpo do procedimento armazenado.Para obter mais detalhes, consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector.
O Snowflake limita o tamanho máximo do código-fonte no corpo de um procedimento armazenado Script Snowflake. O Snowflake recomenda limitar o tamanho a 100 KB. (O código é armazenado de forma compactada, e o limite exato depende da capacidade de compactação do código).
É possível capturar dados de registro e rastreamento à medida que o código do manipulador é executado. Para obter mais informações, consulte Registro, rastreamento e métricas.
Nota
As mesmas regras relacionadas a direitos do chamador vs. direitos do proprietário aplicam-se a estes procedimentos armazenados.
As mesmas considerações e diretrizes em Como trabalhar com procedimentos armazenados se aplicam aos procedimentos armazenados do Script Snowflake.
O seguinte é um exemplo de um simples procedimento armazenado que retorna o valor do argumento que é passado:
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
RETURN message;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL output_message('Hello World');
A seguir está um exemplo de criação e chamada de um procedimento armazenado anônimo usando o comando WITH … CALL …:
WITH anonymous_output_message AS PROCEDURE (message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$
CALL anonymous_output_message('Hello World');
Observe que em um procedimento armazenado anônimo, você deve usar delimitadores de literal de cadeias de caracteres ('
ou $$
) ao redor do corpo do procedimento.
Uso de argumentos passados a um procedimento armazenado¶
Se você passar qualquer argumento para seu procedimento armazenado, você pode se referir a esses argumentos pelo nome em qualquer expressão do Script Snowflake. Os procedimentos armazenados do Script Snowflake oferecem suporte a argumentos de entrada (IN) e saída (OUT).
Quando você especifica um argumento de saída na definição de um procedimento armazenado do Script Snowflake, o procedimento armazenado pode retornar o valor atual do argumento de saída para um programa de chamada, como um bloco anônimo ou um procedimento armazenado diferente. O procedimento armazenado assume um valor inicial para o argumento de saída, salva o valor em uma variável no corpo do procedimento e, opcionalmente, executa operações para alterar o valor da variável antes de retornar o valor atualizado ao programa de chamada.
Por exemplo, o identificador de usuário de um vendedor e um trimestre de vendas podem ser passados para um procedimento armazenado denominado emp_quarter_calling_sp_demo
. Esse procedimento armazenado chama um procedimento armazenado diferente denominado sales_total_out_sp_demo
. O procedimento armazenado sales_total_out_sp_demo
tem um argumento de saída que executa operações para retornar o total de vendas do vendedor no trimestre para o procedimento armazenado de chamada emp_quarter_calling_sp_demo
. Para um exemplo desse cenário, consulte Como usar um argumento de saída para retornar o total de vendas de um funcionário em um trimestre.
Quando há uma incompatibilidade entre o tipo de dados do valor que está sendo passado e o tipo de dados do argumento de saída, as coerções suportadas são executadas automaticamente. Para obter um exemplo, consulte Como usar um argumento de saída com um tipo de dados diferente do valor de entrada de um procedimento de chamada. Para obter informações sobre quais coerções o Snowflake pode executar automaticamente, consulte Tipos de dados que podem ser convertidos.
A função GET_DDL e o comando SHOW PROCEDURES mostram o tipo (ou IN
ou OUT
) dos argumentos de um procedimento armazenado na saída. Outros comandos e exibições que mostram metadados sobre procedimentos armazenados não mostram o tipo dos argumentos, como o comando DESCRIBE PROCEDURE, o Information Schema Exibição PROCEDURES e a exibição exibição </sql-reference/account-usage/procedures>` do Account Usage :doc:`PROCEDURES.
Um procedimento armazenado não pode ser sobrecarregado especificando diferentes tipos de argumento na assinatura. Por exemplo, suponha que um procedimento armazenado tenha esta assinatura:
CREATE PROCEDURE test_overloading(a IN NUMBER)
O seguinte comando CREATE PROCEDURE falha com um erro informando que o procedimento já existe, porque tenta criar um novo procedimento armazenado que difere do exemplo anterior apenas no tipo de argumento:
CREATE PROCEDURE test_overloading(a OUT NUMBER)
Sintaxe¶
Use a seguinte sintaxe para especificar um argumento em uma definição de procedimento armazenado de Script Snowflake:
<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>
Onde:
arg_name
O nome do argumento. O nome deve seguir as regras de nomenclatura para Identificadores de objetos.
{ IN | INPUT | OUT | OUTPUT }
Palavra-chave opcional que especifica se o argumento é um argumento de entrada ou um argumento de saída.
IN
ouINPUT
- o argumento é inicializado com o valor fornecido, e esse valor é atribuído a uma variável de procedimento armazenado. A variável pode ser modificada no corpo do procedimento armazenado, mas seu valor final não pode ser passado para um programa de chamada.IN
eINPUT
são sinônimos.OUT
ouOUTPUT
- o argumento é inicializado com o valor fornecido, e esse valor é atribuído a uma variável de procedimento armazenado. A variável pode ser modificada no corpo do procedimento armazenado e seu valor final pode ser passado para um programa de chamada. No corpo de um procedimento armazenado, a atribuição de valores aos argumentos de saída é feita somente por variáveis.Também é possível passar variáveis não inicializadas aos argumentos de saída. Quando a variável associada não é atribuída, o argumento de saída retorna NULL.
OUT
eOUTPUT
são sinônimos.
Padrão:
IN
arg_data_type
Limitações¶
Os argumentos de saída devem ser especificados na definição de um procedimento armazenado.
Os argumentos de saída não podem ser especificados como argumentos opcionais. Ou seja, os argumentos de saída não podem ser especificados usando a palavra-chave DEFAULT.
No corpo de um procedimento armazenado, as variáveis devem ser usadas para atribuir valores aos argumentos de saída.
A mesma variável não pode ser usada para vários argumentos de saída.
As variáveis de sessão não podem ser passadas para argumentos de saída.
Funções definidas pelo usuário (UDFs) não oferecem suporte a argumentos de saída.
Procedimentos armazenados escritos em outras linguagens além de SQL não oferecem suporte a argumentos de saída.
Os argumentos de saída não podem ser usados em trabalhos secundários assíncronos.
Os procedimentos armazenados são limitados a 500 argumentos, incluindo argumentos de entrada e saída.
Exemplos¶
Exemplo simples de uso de argumentos passados a um procedimento armazenado
Uso de um argumento ao construir uma cadeia de caracteres para uma instrução SQL
Como usar um argumento de saída para retornar um único valor
Como usar um argumento de saída para retornar o total de vendas de um funcionário em um trimestre
Exemplo simples de uso de argumentos passados a um procedimento armazenado¶
O procedimento armazenado a seguir utiliza os valores dos argumentos nas instruções IF e RETURN.
CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL return_greater(2, 3);
Uso de um argumento em uma instrução SQL (vinculação)¶
Como é o caso das variáveis do Script Snowflake, se você precisar usar um argumento em uma instrução SQL, coloque dois pontos (:
) na frente do nome do argumento. Para obter mais informações, consulte Como usar uma variável em uma instrução SQL (vinculação).
As seções a seguir contêm exemplos que usam variáveis de vinculação em procedimentos armazenados:
Exemplo que usa uma variável de vinculação em uma cláusula WHERE
Exemplo de uso de uma variável de vinculação para definir o valor de uma propriedade
Exemplo que usa variáveis de vinculação para definir parâmetros em um comando
Exemplos que usam uma variável de vinculação para uma matriz
Exemplo que usa uma variável de vinculação em uma cláusula WHERE¶
O procedimento armazenado a seguir usa o argumento id
na cláusula WHERE de uma instrução SELECT. Na cláusula WHERE, o argumento é especificado como :id
.
CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
RETURN TABLE(res);
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
RETURN TABLE(res);
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL find_invoice_by_id('2');
Além disso, a função TO_QUERY fornece uma sintaxe simples para aceitar uma cadeia de caracteres SQL diretamente na cláusula FROM de uma instrução SELECT. Para uma comparação da função TO_QUERY com SQL dinâmico, consulte Construção de SQL no tempo de execução.
Exemplo de uso de uma variável de vinculação para definir o valor de uma propriedade¶
O procedimento armazenado a seguir usa o argumento comment
para adicionar um comentário a uma tabela em uma instrução CREATE TABLE. Na instrução, o argumento é especificado como :comment
.
CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL test_bind_comment('My Test Table');
Veja o comentário da tabela consultando a exibição TABLES no INFORMATION_SCHEMA:
SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';
+---------------+
| COMMENT |
|---------------|
| My Test Table |
+---------------+
Você também pode visualizar o comentário executando o comando SHOW TABLES.
Exemplo que usa variáveis de vinculação para definir parâmetros em um comando¶
Suponha que você tenha um estágio chamado st
com arquivos CSV:
CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;
Você deseja carregar os dados dos arquivos CSV em uma tabela chamada test_bind_stage_and_load
:
CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);
O procedimento armazenado a seguir usa os parâmetros FROM, ON_ERROR e VALIDATION_MODE em uma instrução COPY INTO <tabela>. Na instrução, os valores dos parâmetros são especificados como :my_stage_name
, :on_error
e :valid_mode
, respectivamente.
CREATE OR REPLACE PROCEDURE test_copy_files_validate(
my_stage_name VARCHAR,
on_error VARCHAR,
valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
COPY INTO test_bind_stage_and_load
FROM :my_stage_name
ON_ERROR=:on_error
FILE_FORMAT=(type='csv')
VALIDATION_MODE=:valid_mode;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE test_copy_files_validate(
my_stage_name VARCHAR,
on_error VARCHAR,
valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
COPY INTO test_bind_stage_and_load
FROM :my_stage_name
ON_ERROR=:on_error
FILE_FORMAT=(type='csv')
VALIDATION_MODE=:valid_mode;
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');
Exemplos que usam uma variável de vinculação para uma matriz¶
Você pode expandir uma variável de vinculação que representa uma matriz em uma lista de valores individuais usando o operador de expansão (**
). Para obter mais informações e exemplos, consulte Operadores de expansão.
Uso de um argumento como identificador de objeto¶
Se você precisar usar um argumento para se referir a um objeto (por exemplo, um nome de tabela na cláusula FROM de uma instrução SELECT), use a palavra-chave IDENTIFIER para indicar que o argumento representa um identificador de objeto. Por exemplo:
CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL get_row_count('invoices');
Este exemplo executa uma instrução CREATE TABLE… AS SELECT (CTAS) em um procedimento armazenado com base nos nomes de tabela fornecidos nos argumentos.
CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
RETURNS TEXT
LANGUAGE SQL
AS
BEGIN
CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
SELECT * FROM IDENTIFIER(:existing_table);
RETURN 'Table created';
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
RETURNS TEXT
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
SELECT * FROM IDENTIFIER(:existing_table);
RETURN 'Table created';
END;
$$
;
Antes de chamar o procedimento, crie uma tabela simples e insira dados:
CREATE OR REPLACE TABLE test_table_for_ctas_sp (
id NUMBER(2),
v VARCHAR(2))
AS SELECT
column1,
column2,
FROM
VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
Chame o procedimento armazenado para criar uma nova tabela baseada nessa tabela:
CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');
Uso de um argumento ao construir uma cadeia de caracteres para uma instrução SQL¶
Observe que se você estiver construindo uma instrução SQL como uma cadeia de caracteres a ser passada para EXECUTE IMMEDIATE (consulte Atribuição de uma consulta a um RESULTSET declarado), não use dois pontos antes do argumento. Por exemplo:
CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
select_statement VARCHAR;
res RESULTSET;
BEGIN
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);
RETURN TABLE(res);
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
select_statement VARCHAR;
res RESULTSET;
BEGIN
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);
RETURN TABLE(res);
END;
$$
;
Como usar um argumento de saída para retornar um único valor¶
O exemplo a seguir cria o procedimento armazenado simple_out_sp_demo
com o argumento de saída xout
em sua definição. O procedimento armazenado define o valor de xout
como 2
.
CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
xout := 2;
RETURN 'Done';
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
xout := 2;
RETURN 'Done';
END;
$$
;
O seguinte bloco anônimo define o valor da variável x
como 1
. Em seguida, ele chama o procedimento armazenado simple_out_sp_demo
e especifica a variável como o argumento.
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x);
RETURN x;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
EXECUTE IMMEDIATE
$$
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x);
RETURN x;
END;
$$
;
A saída mostra que o procedimento armazenado simple_out_sp_demo
executou uma operação para definir o valor do argumento de saída como 2
e depois retornou esse valor ao bloco anônimo.
+-----------------+
| anonymous block |
|-----------------|
| 2 |
+-----------------+
O seguinte bloco anônimo chama o procedimento armazenado simple_out_sp_demo
e retorna um erro, porque tenta atribuir um valor ao argumento de saída usando uma expressão em vez de uma variável.
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x + 2);
RETURN x;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
EXECUTE IMMEDIATE
$$
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x + 2);
RETURN x;
END;
$$
;
Como usar argumentos de saída para retornar vários valores para diversas chamadas a um procedimento armazenado¶
O exemplo a seguir demonstra o seguinte comportamento relacionado a procedimentos armazenados e argumentos de entrada e saída:
Um procedimento armazenado pode ter vários argumentos de entrada e saída em sua definição.
Um programa pode chamar um procedimento armazenado com argumentos de saída várias vezes, e os valores dos argumentos de saída são preservados após cada chamada.
Argumentos de entrada não retornam valores ao programa de chamada.
Crie o procedimento armazenado multiple_out_sp_demo
com vários argumentos de entrada e saída em sua definição. O procedimento armazenado executa as mesmas operações nos argumentos de entrada e saída equivalentes. Por exemplo, o procedimento armazenado adiciona 1
ao argumento de entrada p1_in
e ao argumento de saída p1_out
.
CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
p1_in NUMBER,
p1_out OUT NUMBER,
p2_in VARCHAR(100),
p2_out OUT VARCHAR(100),
p3_in BOOLEAN,
p3_out OUT BOOLEAN)
RETURNS NUMBER
LANGUAGE SQL
AS
BEGIN
p1_in := p1_in + 1;
p1_out := p1_out + 1;
p2_in := p2_in || ' hi ';
p2_out := p2_out || ' hi ';
p3_in := (NOT p3_in);
p3_out := (NOT p3_out);
RETURN 1;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
p1_in NUMBER,
p1_out OUT NUMBER,
p2_in VARCHAR(100),
p2_out OUT VARCHAR(100),
p3_in BOOLEAN,
p3_out OUT BOOLEAN)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
BEGIN
p1_in := p1_in + 1;
p1_out := p1_out + 1;
p2_in := p2_in || ' hi ';
p2_out := p2_out || ' hi ';
p3_in := (NOT p3_in);
p3_out := (NOT p3_out);
RETURN 1;
END;
$$
;
O seguinte bloco anônimo atribui valores às variáveis que correspondem aos argumentos do procedimento armazenado multiple_out_sp_demo
e depois chama o procedimento armazenado várias vezes. A primeira chamada usa os valores de variável especificados no bloco anônimo, mas cada chamada subsequente usa os valores retornados pelos argumentos de saída no procedimento armazenado multiple_out_sp_demo
.
BEGIN
LET x_in INT := 1;
LET x_out INT := 1;
LET y_in VARCHAR(100) := 'hello';
LET y_out VARCHAR(100) := 'hello';
LET z_in BOOLEAN := true;
LET z_out BOOLEAN := true;
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
EXECUTE IMMEDIATE
$$
BEGIN
LET x_in INT := 1;
LET x_out INT := 1;
LET y_in VARCHAR(100) := 'hello';
LET y_out VARCHAR(100) := 'hello';
LET z_in BOOLEAN := true;
LET z_out BOOLEAN := true;
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;
+------------------------+
| anonymous block |
|------------------------|
| [ |
| 1, |
| 4, |
| "hello", |
| "hello hi hi hi ", |
| true, |
| false |
| ] |
+------------------------+
Como usar um argumento de saída com um tipo de dados diferente do valor de entrada de um procedimento de chamada¶
Para alguns casos de uso, pode haver uma incompatibilidade entre o tipo de dados do valor que está sendo passado para um procedimento armazenado e o tipo de dados do argumento de saída do procedimento. Nesses casos, coerções com suporte são realizadas automaticamente.
Nota
Embora a coerção tenha suporte em alguns casos, ela não é recomendada.
Este exemplo demonstra a conversão automática de um valor FLOAT que é passado para um argumento de saída com um tipo de dados NUMBER. O valor FLOAT é convertido automaticamente em um valor NUMBER e depois passado de volta para o bloco anônimo de chamada.
Criar o procedimento armazenado sp_out_coercion
, que recebe um argumento de saída do tipo NUMBER:
CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
x := x * 2;
RETURN 'Done';
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
x := x * 2;
RETURN 'Done';
END;
$$
;
Executar um bloco anônimo que passa um valor FLOAT para o procedimento armazenado sp_out_coercion
:
BEGIN
LET a FLOAT := 500.662;
CALL sp_out_coercion(:a);
RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
EXECUTE IMMEDIATE
$$
BEGIN
LET a FLOAT := 500.662;
CALL sp_out_coercion(:a);
RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;
A saída mostra o valor retornado e o respectivo tipo de dados, chamando a função SYSTEM$TYPEOF. Observe que o valor é imposto a partir de um valor NUMBER de volta para um valor FLOAT após ser retornado do procedimento armazenado:
+---------------------------+
| anonymous block |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+
Como usar um argumento de saída para retornar o total de vendas de um funcionário em um trimestre¶
Este exemplo usa a seguinte tabela quarterly_sales
:
CREATE OR REPLACE TABLE quarterly_sales(
empid INT,
amount INT,
quarter TEXT)
AS SELECT * FROM VALUES
(1, 10000, '2023_Q1'),
(1, 400, '2023_Q1'),
(2, 4500, '2023_Q1'),
(2, 35000, '2023_Q1'),
(1, 5000, '2023_Q2'),
(1, 3000, '2023_Q2'),
(2, 200, '2023_Q2'),
(2, 90500, '2023_Q2'),
(1, 6000, '2023_Q3'),
(1, 5000, '2023_Q3'),
(2, 2500, '2023_Q3'),
(2, 9500, '2023_Q3'),
(3, 2700, '2023_Q3'),
(1, 8000, '2023_Q4'),
(1, 10000, '2023_Q4'),
(2, 800, '2023_Q4'),
(2, 4500, '2023_Q4'),
(3, 2700, '2023_Q4'),
(3, 16000, '2023_Q4'),
(3, 10200, '2023_Q4');
Criar o procedimento armazenado sales_total_out_sp_demo
que usa dois argumentos de entrada para o identificador do funcionário e trimestre, e um argumento de saída para calcular o total de vendas para o funcionário e trimestre fornecidos.
CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
id INT,
quarter VARCHAR(20),
total_sales OUT NUMBER(38,0))
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
SELECT SUM(amount) INTO total_sales FROM quarterly_sales
WHERE empid = :id AND
quarter = :quarter;
RETURN 'Done';
END;
$$
;
Criar o procedimento armazenado emp_quarter_calling_sp_demo
que chama o procedimento armazenado sales_total_out_sp_demo
. Este procedimento armazenado também recebe dois argumentos de entrada para o identificador do funcionário e trimestre.
CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
id INT,
quarter VARCHAR(20))
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
LET x NUMBER(38,0);
CALL sales_total_out_sp_demo(:id, :quarter, :x);
RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
id INT,
quarter VARCHAR(20))
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
LET x NUMBER(38,0);
CALL sales_total_out_sp_demo(:id, :quarter, :x);
RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;
Chamar emp_quarter_calling_sp_demo
com os argumentos 2
(para o identificador do funcionário) e '2023_Q4'
(para o trimestre).
CALL emp_quarter_calling_sp_demo(2, '2023_Q4');
+-----------------------------------------------------+
| emp_quarter_calling_sp_demo |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+
Retorno de dados tabulares¶
Se você precisar retornar dados tabulares (por exemplo, dados de um RESULTSET) de seu procedimento armazenado, especifique RETURNS TABLE(…) em sua instrução CREATE PROCEDURE.
Se você conhecer os tipos de dados Snowflake das colunas na tabela retornada, especifique os nomes e tipos de colunas em RETURNS TABLE().
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...
Caso contrário (por exemplo, se você estiver determinando os tipos de coluna durante o tempo de execução), você pode omitir os nomes e tipos de coluna:
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...
Nota
Atualmente, na cláusula RETURNS TABLE(...)
, você não pode especificar GEOGRAPHY como um tipo de coluna. Isso se aplica se você estiver criando um procedimento armazenado ou anônimo.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE(g GEOGRAPHY)
...
CALL test_return_geography_table_1();
Se você tentar especificar GEOGRAPHY como um tipo de coluna, chamar o procedimento armazenado resultará no erro:
Stored procedure execution error: data type of returned table does not match expected returned table type
Para contornar este problema, você pode omitir os argumentos da coluna e digitar RETURNS TABLE()
.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE()
...
CALL test_return_geography_table_1();
Se você precisar retornar os dados em um RESULTSET, use TABLE() em sua instrução RETURN.
Por exemplo:
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL get_top_sales();
Chamada de um procedimento armazenado a partir de outro procedimento armazenado¶
Em um procedimento armazenado, se você precisar chamar outro procedimento armazenado, use uma das seguintes abordagens:
Chamada de um procedimento armazenado sem usar o valor retornado
Uso do valor retornado a partir de uma chamada de procedimento armazenado
Chamada de um procedimento armazenado sem usar o valor retornado¶
Use uma instrução CALL para chamar o procedimento armazenado (como você normalmente faria).
Se você precisar passar qualquer variável ou argumento como argumento de entrada na instrução CALL, lembre-se de usar dois pontos (:
) na frente do nome da variável. (Consulte Como usar uma variável em uma instrução SQL (vinculação).)
A seguir, um exemplo de um procedimento armazenado que chama outro procedimento armazenado, mas não depende do valor de retorno.
Primeiro, crie uma tabela para uso no exemplo:
-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);
Então, crie o procedimento armazenado que você chamará de outro procedimento armazenado:
-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;
Em seguida, crie um segundo procedimento armazenado que chama o primeiro procedimento armazenado:
CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
$$
;
Por fim, chame o segundo procedimento armazenado:
CALL insert_two_values(4, 5);
Uso do valor retornado a partir de uma chamada de procedimento armazenado¶
Se estiver chamando um procedimento armazenado que retorna um valor escalar e você precisar acessar esse valor, use a cláusula INTO :snowflake_scripting_variable
na instrução CALL para capturar o valor em uma variável do Script Snowflake.
O exemplo a seguir chama o procedimento armazenado get_row_count
que foi definido em Uso de um argumento como identificador de objeto.
CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
DECLARE
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL count_greater_than('invoices', 3);
Se o procedimento armazenado retornar uma tabela, você pode capturar o valor de retorno definindo um RESULTSET para uma cadeia de caracteres contendo a instrução CALL. (Consulte Atribuição de uma consulta a um RESULTSET declarado.)
Para recuperar o valor de retorno da chamada, você pode usar um CURSOR para o RESULTSET. Por exemplo:
DECLARE
res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
IF (row_variable.col1 > 0) THEN
...;
ELSE
...;
END IF;
END FOR;
...
Como passar valores de argumento de saída de um procedimento armazenado para um procedimento armazenado de chamada¶
Quando um argumento de saída é especificado na definição de um procedimento armazenado do Script Snowflake, o procedimento armazenado pode retornar o valor atual do argumento de saída para um procedimento armazenado de chamada. O procedimento armazenado assume um valor inicial para o argumento de saída, salva o valor em uma variável no corpo do procedimento e, opcionalmente, executa operações para alterar o valor da variável. Em seguida, o procedimento armazenado retorna o valor atualizado para o procedimento armazenado de chamada.
Para obter um exemplo, consulte Como usar um argumento de saída para retornar o total de vendas de um funcionário em um trimestre.
Como usar procedimentos armazenados aninhados¶
Um procedimento armazenado aninhado é definido no escopo de um bloco anônimo ou um bloco em outro procedimento armazenado (o procedimento armazenado pai).
Você declara um procedimento armazenado aninhado na seção DECLARE de um bloco, que pode ser parte de uma instrução CREATE PROCEDURE. O exemplo a seguir mostra uma declaração de procedimento armazenado aninhado:
DECLARE
<nested_stored_procedure_name> PROCEDURE (<arguments>)
RETURNS <data_type>
AS
BEGIN
<nested_procedure_procedure_statements>
END;
BEGIN
<statements>
END;
Para obter informações sobre a sintaxe da declaração de um procedimento armazenado aninhado, consulte Sintaxe da instrução do procedimento armazenado aninhado.
Um procedimento armazenado aninhado só existe no escopo de seu bloco. Pode ser chamado de qualquer seção de seu bloco (DECLARE, BEGIN … END e EXCEPTION). Um único bloco pode conter vários procedimentos armazenados aninhados, e um procedimento armazenado aninhado pode chamar outro procedimento armazenado aninhado no mesmo bloco. Um procedimento aninhado não pode ser chamado ou acessado de fora de seu bloco.
Um procedimento armazenado aninhado opera no mesmo contexto de segurança que o bloco que o define. Quando um procedimento armazenado aninhado é definido em um procedimento armazenado pai, ele é executado automaticamente com os mesmos privilégios que o procedimento armazenado pai.
Nota
Tanto uma declaração de procedimento armazenado aninhado quanto o comando CALL WITH criam um procedimento armazenado temporário com escopo limitado. Eles diferem das seguintes maneiras:
Uma instrução CALL WITH pode aparecer em qualquer lugar em que uma instrução SQL pode, inclusive dentro de um procedimento armazenado, mas uma instrução de procedimento armazenado aninhado deve estar em um bloco do Script Snowflake.
Um procedimento armazenado CALL WITH só existe no escopo de sua instrução, mas um procedimento armazenado aninhado existe no escopo de seu bloco do Script Snowflake.
Benefícios dos procedimentos armazenados aninhados¶
Os procedimentos armazenados aninhados proporcionam os seguintes benefícios:
Eles podem aprimorar e simplificar a segurança encapsulando a lógica dentro de um bloco anônimo ou procedimento armazenado pai, o que impede o acesso a ele de fora do bloco ou pai.
Eles mantêm o código modular dividindo-o logicamente em partes menores, o que pode facilitar a manutenção e a depuração.
Eles melhoram a manutenção reduzindo a necessidade de variáveis globais ou argumentos adicionais, porque um procedimento armazenado aninhado pode acessar diretamente as variáveis locais de seu bloco.
Notas de uso para chamar procedimentos armazenados aninhados¶
As seguintes notas de uso se aplicam à chamada de um procedimento armazenado aninhado:
Para passar argumentos para um procedimento armazenado aninhado, um bloco pode usar valores constantes, variáveis do Script Snowflake, variáveis de vinculação, variáveis SQL (sessão) e chamadas a funções :doc:` definidas pelo usuário </developer-guide/udf/udf-overview>`.
Quando há uma incompatibilidade entre o tipo de dados do valor que está sendo passado e o tipo de dados de um argumento, o Snowflake executa automaticamente as coerções suportadas. Para obter informações sobre quais coerções o Snowflake pode executar automaticamente, consulte Conversão do tipo de dados.
Notas de uso para variáveis em um procedimento armazenado aninhado¶
As seguintes notas de uso se aplicam às variáveis em um procedimento armazenado aninhado:
Um procedimento armazenado aninhado pode referenciar variáveis de seu bloco que foram declaradas antes da declaração do procedimento armazenado aninhado na seção DECLARE de seu bloco. Ele não pode fazer referência a variáveis declaradas depois dele na seção DECLARE.
Um procedimento armazenado aninhado não pode acessar variáveis declaradas em uma instrução LET na seção BEGIN … END de um bloco.
O valor de uma variável referenciada reflete seu valor no momento em que o procedimento armazenado aninhado é chamado.
Um procedimento armazenado aninhado pode modificar um valor de variável referenciado, e o valor modificado persiste no bloco e em várias invocações do mesmo procedimento aninhado em uma única execução de seu bloco anônimo ou em uma única chamada a seu procedimento armazenado pai.
O valor de uma variável que foi declarada antes de uma chamada de procedimento armazenado aninhado pode ser passado como um argumento para o procedimento armazenado aninhado. O valor da variável pode ser passado como um argumento em uma chamada, mesmo que a variável tenha sido declarada após a declaração do procedimento armazenado aninhado ou em uma instrução LET.
Por exemplo, o seguinte procedimento armazenado declara várias variáveis:
CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
var_before_nested_proc NUMBER DEFAULT 1;
test_nested_variables PROCEDURE(arg1 NUMBER)
-- <nested_sp_logic>
var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
LET var_let_before_call NUMBER DEFAULT 3;
LET result := CALL nested_proc(:<var_name>);
LET var_let_after_call NUMBER DEFAULT 3;
RETURN result;
END;
$$;
Neste exemplo, apenas var_before_nested_proc
pode ser referenciado em nested_sp_logic
.
Na chamada do procedimento armazenado aninhado, o valor de qualquer uma das seguintes variáveis pode ser passado para o procedimento armazenado aninhado como um argumento em var_name
:
var_before_nested_proc
var_after_nested_proc
var_let_before_call
O valor var_let_after_call
não pode ser passado para o procedimento armazenado aninhado como um argumento.
Limitações de procedimentos armazenados aninhados¶
As seguintes limitações se aplicam à definição de procedimentos armazenados aninhados:
Eles não podem ser definidos dentro de outros procedimentos armazenados aninhados ou dentro de estruturas de controle, como loops FOR ou WHILE.
Cada procedimento armazenado aninhado deve ter um nome exclusivo em seu bloco. Ou seja, os procedimentos armazenados aninhados não podem ser sobrecarregados.
Eles não oferecem suporte a argumentos de saída (OUT).
Eles não oferecem suporte a argumentos opcionais com valores padrão.
As seguintes limitações se aplicam à chamada de procedimentos armazenados aninhados:
Eles não podem ser chamados em uma instrução EXECUTE IMMEDIATE.
Eles não podem ser chamados em trabalhos secundários assíncronos.
Eles não oferecem suporte a argumentos de entrada nomeados (
arg_name => arg
). Os argumentos devem ser especificados por posição. Para obter mais informações, consulte CALL.
Exemplos de procedimentos armazenados aninhados¶
Os exemplos a seguir usam procedimentos armazenados aninhados:
Definição de um procedimento armazenado aninhado que retorna dados tabulares
Definir de um procedimento armazenado aninhado que retorna um valor escalar
Definir um procedimento armazenado aninhado em um bloco anônimo
Definir de um procedimento armazenado aninhado que recebe argumentos
Definir um procedimento armazenado aninhado que chama outro procedimento armazenado aninhado
Definição de um procedimento armazenado aninhado que retorna dados tabulares¶
O exemplo a seguir define um procedimento armazenado aninhado que retorna um dado tabular. O exemplo cria um procedimento armazenado pai denominado nested_procedure_example_table
com um procedimento armazenado aninhado denominado nested_return_table
. O código inclui a seguinte lógica:
Declara uma variável denominada
res
do tipo RESULTSET.Inclui a seguinte lógica no procedimento armazenado aninhado:
Declara uma variável denominada
res2
.Insere valores em uma tabela denominada
nested_table
.Define a variável
res2
aos resultados de um SELECT na tabela.Retorna os dados tabulares no conjunto de resultados.
Cria a tabela
nested_table
no procedimento armazenado pai.Chama o procedimento armazenado aninhado
nested_return_table
e define a variávelres
para os resultados da chamada ao procedimento armazenado aninhado.Retorna os resultados tabulares na variável
res
.
CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET;
nested_return_table PROCEDURE()
RETURNS TABLE()
AS
DECLARE
res2 RESULTSET;
BEGIN
INSERT INTO nested_table VALUES(1);
INSERT INTO nested_table VALUES(2);
res2 := (SELECT * FROM nested_table);
RETURN TABLE(res2);
END;
BEGIN
CREATE OR REPLACE TABLE nested_table(col1 INT);
res := (CALL nested_return_table());
RETURN TABLE(res);
END;
$$;
Chame o procedimento armazenado:
CALL nested_procedure_example_table();
+------+
| COL1 |
|------|
| 1 |
| 2 |
+------+
Definir de um procedimento armazenado aninhado que retorna um valor escalar¶
O exemplo a seguir define um procedimento armazenado aninhado que retorna um valor escalar. O exemplo cria um procedimento armazenado pai denominado nested_procedure_example_scalar
com um procedimento armazenado aninhado denominado simple_counter
. O código inclui a seguinte lógica:
Declara uma variável denominada
counter
do tipo NUMBER e define o valor dessa variável como0
.Especifica que o procedimento armazenado aninhado adiciona
1
ao valor atual da variávelcounter
.Chama o procedimento armazenado aninhado três vezes no procedimento armazenado pai. O valor da variável
counter
é transferida entre invocações do procedimento armazenado aninhado.Retorna o valor da variável
counter
, que é3
.
CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
counter NUMBER := 0;
simple_counter PROCEDURE()
RETURNS VARCHAR
AS
BEGIN
counter := counter + 1;
RETURN counter;
END;
BEGIN
CALL simple_counter();
CALL simple_counter();
CALL simple_counter();
RETURN counter;
END;
$$;
Chame o procedimento armazenado:
CALL nested_procedure_example_scalar();
+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3 |
+---------------------------------+
Definir um procedimento armazenado aninhado em um bloco anônimo¶
O exemplo a seguir é o mesmo que o exemplo em Definir de um procedimento armazenado aninhado que retorna um valor escalar, exceto que ele define um procedimento armazenado aninhado em um bloco anônimo em vez de um procedimento armazenado:
EXECUTE IMMEDIATE $$
DECLARE
counter NUMBER := 0;
simple_counter PROCEDURE()
RETURNS VARCHAR
AS
BEGIN
counter := counter + 1;
RETURN counter;
END;
BEGIN
CALL simple_counter();
CALL simple_counter();
CALL simple_counter();
RETURN counter;
END;
$$;
+-----------------+
| anonymous block |
|-----------------|
| 3 |
+-----------------+
Definir de um procedimento armazenado aninhado que recebe argumentos¶
O exemplo a seguir define um procedimento armazenado aninhado ao qual são passados argumentos. No exemplo, o procedimento armazenado aninhado insere valores na tabela a seguir:
CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);
O exemplo cria um procedimento armazenado pai denominado nested_procedure_example_arguments
com um procedimento armazenado aninhado denominado log_and_multiply_numbers
. O procedimento armazenado aninhado recebe dois argumentos do tipo NUMBER. O código inclui a seguinte lógica:
Declara variáveis
a
,b
ex
do tipo NUMBER.Inclui um procedimento armazenado aninhado que executa as seguintes ações:
Insere os dois valores numéricos passados a ele pelo procedimento armazenado pai na tabela
log_nested_values
usando variáveis de vinculação.Define o valor da variável
x
ao resultado da multiplicação dos dois valores de argumento.Retorna o valor
x
ao procedimento armazenado pai.
Define o valor da variável
a
como5
e a variávelb
como10
.Chama o procedimento armazenado aninhado.
Retorna o valor da variável
x
, que foi definida no procedimento armazenado aninhado.
CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
a NUMBER;
b NUMBER;
x NUMBER;
log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
RETURNS NUMBER
AS
BEGIN
INSERT INTO log_nested_values VALUES(:num1, :num2);
x := :num1 * :num2;
RETURN x;
END;
BEGIN
a := 5;
b := 10;
CALL log_and_multiply_numbers(:a, :b);
RETURN x;
END;
$$;
Chame o procedimento armazenado:
CALL nested_procedure_example_arguments();
+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
| 50 |
+------------------------------------+
Consultar a tabela log_nested_values
para confirmar que o procedimento armazenado aninhado inseriu os valores passados a ele:
SELECT * FROM log_nested_values;
+------+------+
| COL1 | COL2 |
|------+------|
| 5 | 10 |
+------+------+
Definir um procedimento armazenado aninhado que chama outro procedimento armazenado aninhado¶
O exemplo a seguir define um procedimento armazenado aninhado que chama outro procedimento armazenado aninhado. O exemplo cria um procedimento armazenado pai denominado nested_procedure_example_call_from_nested
com dois procedimentos armazenados aninhados denominados counter_nested_proc
e call_counter_nested_proc
. O código inclui a seguinte lógica:
Declara uma variável denominada
counter
do tipo NUMBER e define o valor dessa variável como0
.Inclui o procedimento armazenado aninhado
counter_nested_proc
que adiciona10
ao valor decounter
.Inclui o procedimento armazenado aninhado
call_counter_nested_proc
que adiciona15
ao valor decounter
e também chamadascounter_nested_proc
(que adiciona outro10
ao valor decounter
).Chama ambos os procedimentos armazenados aninhados no procedimento armazenado pai.
Retorna o valor da variável
counter
, que é35
.
CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
counter NUMBER := 0;
counter_nested_proc PROCEDURE()
RETURNS NUMBER
AS
DECLARE
var1 NUMBER := 10;
BEGIN
counter := counter + var1;
END;
call_counter_nested_proc PROCEDURE()
RETURNS NUMBER
AS
DECLARE
var2 NUMBER := 15;
BEGIN
counter := counter + var2;
CALL counter_nested_proc();
END;
BEGIN
counter := 0;
CALL counter_nested_proc();
CALL call_counter_nested_proc();
RETURN counter;
END;
$$;
Chame o procedimento armazenado:
CALL nested_procedure_example_call_from_nested();
+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
| 35 |
+-------------------------------------------+
Uso e configuração de variáveis SQL em um procedimento armazenado¶
Por padrão, os procedimentos armazenados do Snowflake Scripting são executados com direitos de proprietário. Quando um procedimento armazenado é executado com direitos de proprietário, ele não pode acessar as variáveis SQL (ou de sessão).
No entanto, um procedimento armazenado de direitos do chamador pode ler as variáveis de sessão do chamador e usá-las na lógica do procedimento armazenado. Por exemplo, um procedimento armazenado de direitos do chamador pode usar o valor de uma variável SQL em uma consulta. Para criar um procedimento armazenado que seja executado com os direitos do chamador, especifique o parâmetro EXECUTE AS CALLER
na instrução CREATE PROCEDURE.
Esses exemplos ilustram essa diferença fundamental entre os procedimentos armazenados de direitos do chamador e de direitos do proprietário. Eles tentam usar as variáveis do SQL de duas maneiras:
Defina uma variável SQL antes de chamar o procedimento armazenado e, em seguida, use a variável SQL dentro do procedimento armazenado.
Defina uma variável SQL dentro do procedimento armazenado e, em seguida, use a variável SQL após retornar do procedimento armazenado.
Tanto o uso da variável SQL quanto a definição da variável SQL funcionam corretamente em um procedimento armazenado de direitos do chamador. Ambas falham ao utilizar um procedimento armazenado dos direitos do proprietário mesmo que o chamador seja o proprietário.
Para obter mais informações sobre os direitos do proprietário e os direitos do chamador, consulte Procedimentos armazenados com direitos do chamador e direitos do proprietário.
Como usar uma variável SQL em um procedimento armazenado¶
Este exemplo usa uma variável SQL em um procedimento armazenado.
Primeiro, defina uma variável SQL em uma sessão:
SET example_use_variable = 2;
Crie um procedimento armazenado simples que seja executado com os direitos do chamador e use essa variável SQL:
CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
sess_var_x_2 NUMBER;
BEGIN
sess_var_x_2 := 2 * $example_use_variable;
RETURN sess_var_x_2;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
sess_var_x_2 NUMBER;
BEGIN
sess_var_x_2 := 2 * $example_use_variable;
RETURN sess_var_x_2;
END;
$$
;
Chame o procedimento armazenado:
CALL use_sql_variable_proc();
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
| 4 |
+-----------------------+
Defina a variável SQL com um valor diferente:
SET example_use_variable = 9;
Chame o procedimento novamente para verificar se o valor retornado foi alterado:
CALL use_sql_variable_proc();
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
| 18 |
+-----------------------+
Como definir uma variável SQL em um procedimento armazenado¶
Você pode definir uma variável SQL em um procedimento armazenado que esteja sendo executado com os direitos do chamador. Para obter mais informações, incluindo diretrizes para o uso de variáveis SQL em procedimentos armazenados, consulte Procedimentos armazenados de direitos do chamador.
Nota
Embora você possa definir uma variável SQL dentro de um procedimento armazenado e deixá-la definida após o término do procedimento, a Snowflake não recomenda fazer isso.
Este exemplo define uma variável SQL em um procedimento armazenado.
Primeiro, defina uma variável SQL em uma sessão:
SET example_set_variable = 55;
Confirme o valor da variável SQL:
SHOW VARIABLES LIKE 'example_set_variable';
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55 | fixed | |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
Por exemplo, o procedimento armazenado a seguir define a variável SQL example_set_variable
como um novo valor e retorna o novo valor:
CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
SET example_set_variable = $example_set_variable - 3;
RETURN $example_set_variable;
END;
Observação: se você usar o Snowflake CLI, SnowSQL, o Classic Console, ou o método execute_stream
ou execute_string
no código Python Connector, use este exemplo (consulte Usar o Snowflake Scripting no Snowflake CLI, SnowSQL, Classic Console e Python Connector):
CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
SET example_set_variable = $example_set_variable - 3;
RETURN $example_set_variable;
END;
$$
;
Chame o procedimento armazenado:
CALL set_sql_variable_proc();
+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
| 52 |
+-----------------------+
Confirme o novo valor da variável SQL:
SHOW VARIABLES LIKE 'example_set_variable';
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52 | fixed | |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+