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.

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

If you are creating a Snowflake Scripting procedure in SnowSQL or Snowsight, you must use string literal delimiters (' or $$) around the body of the stored procedure.

Para obter mais detalhes, consulte Using Snowflake Scripting in Snowflake CLI, SnowSQL, and 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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and 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 ou INPUT - 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 e INPUT são sinônimos.
OUT ou OUTPUT - 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 e OUTPUT são sinônimos.

Padrão: IN

arg_data_type

Um tipo de dados SQL.

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 em uma instrução SQL (vinculação)
Uso de um argumento como identificador de objeto
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 argumentos de saída para retornar vários valores para diversas chamadas a um procedimento armazenado
Como usar um argumento de saída com um tipo de dados diferente do valor de entrada de um procedimento de chamada
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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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
Como passar valores de argumento de saída de um procedimento armazenado para um procedimento armazenado de chamada

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;

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

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¶

If you are calling a stored procedure that returns a scalar value, and you need to access that value, use the INTO :snowflake_scripting_variable clause in the CALL statement to capture the value in a Snowflake Scripting variable.

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;

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ável res 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 como 0.
Especifica que o procedimento armazenado aninhado adiciona 1 ao valor atual da variável counter.
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 e x 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 como 5 e a variável b como 10.
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 como 0.
Inclui o procedimento armazenado aninhado counter_nested_proc que adiciona 10 ao valor de counter.
Inclui o procedimento armazenado aninhado call_counter_nested_proc que adiciona 15 ao valor de counter e também chamadas counter_nested_proc (que adiciona outro 10 ao valor de counter).
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;

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;

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