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;
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;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL output_message('Hello World');
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');
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 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;
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;
$$
;
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. (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;
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;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL find_invoice_by_id('2');
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;
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;
$$
;
A seguir, um exemplo de como chamar o procedimento armazenado:
CALL get_row_count('invoices');
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;
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)
...
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 isso, 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;
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;
$$
;
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;
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;
$$
;
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;
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;
$$
;
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;
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;
$$
;
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;
...