Como escrever procedimentos armazenados no Script Snowflake¶

Este tópico explica como escrever um procedimento armazenado em SQL usando o 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 Como usar o Script Snowflake no SnowSQL e na Classic Console.

É possível capturar dados de registro e rastreamento à medida que o código do manipulador é executado. Para obter mais informações, consulte Visão geral do registro e do rastreamento.

Observe o seguinte:

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  RETURN message;
END;
$$
;

Copy

A seguir, um exemplo de como chamar o procedimento armazenado:

CALL output_message('Hello World');

Copy

A seguir 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');

Copy

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. Consulte as próximas seções para obter mais detalhes:

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

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

A seguir, um exemplo de como chamar o procedimento armazenado:

CALL return_greater(2, 3);

Copy

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. (Consulte Como usar uma variável em uma instrução SQL (vinculação).)

Por exemplo, o seguinte procedimento armazenado utiliza 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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

A seguir, um exemplo de como chamar o procedimento armazenado:

CALL find_invoice_by_id('2');

Copy

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

A seguir, um exemplo de como chamar o procedimento armazenado:

CALL get_row_count('invoices');

Copy

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;

Copy

Retorno de dados tabulares¶

Se você precisar retornar dados tabulares (por exemplo, dados de um RESULTSET) de seu procedimento armazenado, especifique RETURNSTABLE(…) 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)
...

Copy

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 ()
...

Copy

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)
  ...

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();

Copy

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

Copy

Para contornar isso, você pode omitir os argumentos da coluna e digitar RETURNS TABLE().

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE()
  ...

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();

Copy

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

A seguir, um exemplo de como chamar o procedimento armazenado:

CALL get_top_sales();

Copy

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

Copy

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

Por fim, chame o segundo procedimento armazenado:

CALL insert_two_values(4, 5);

Copy

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;

Copy

Nota: se você estiver usando SnowSQL ou Classic Console, use este exemplo (consulte Como usar o Script Snowflake no SnowSQL e na Classic Console):

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

Copy

A seguir, um exemplo de como chamar o procedimento armazenado:

CALL count_greater_than('invoices', 3);

Copy

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

Copy