Construção de SQL no tempo de execução¶

O Snowflake aceita diversas técnicas diferentes para construir cadeias de caracteres de instruções SQL dinamicamente no tempo de execução. Ao usar essas técnicas, você pode especificar cadeias de caracteres SQL mais gerais e flexíveis para casos de uso em que o texto completo das instruções SQL é desconhecido até o tempo de execução.

Um procedimento armazenado ou aplicativo pode aceitar a entrada do usuário e então usar essa entrada em uma instrução SQL. Por exemplo, uma tabela pode armazenar informações sobre pedidos de vendas. Um aplicativo ou procedimento armazenado pode aceitar um ID de pedido como entrada e executar uma consulta que retorna apenas os resultados para aquele pedido específico.

Um desenvolvedor pode escrever um código de procedimento armazenado ou código de aplicativo com instruções SQL que contenham espaços reservados e, então, vincular variáveis a esses espaços reservados no código. Esses espaços reservados são chamados de variáveis de vinculação. Um desenvolvedor também pode escrever um código que constrói instruções SQL a partir de uma cadeia de caracteres de entrada (por exemplo, concatenando cadeias de caracteres que contêm um comando SQL, parâmetros e valores).

As seguintes técnicas estão disponíveis para construir instruções SQL dinamicamente no tempo de execução:

A função TO_QUERY - Esta função recebe uma cadeia de caracteres SQL com parâmetros opcionais como entrada.
SQL dinâmico - O código em um procedimento armazenado ou aplicativo recebe uma entrada e constrói uma instrução SQL dinâmica usando essa entrada. O código pode ser parte de um procedimento armazenado do Snowflake Scripting ou Javascript, ou de um bloco anônimo do Snowflake Scripting. Você também pode usar essa técnica no código do seu aplicativo que usa um driver Snowflake ou o REST API SQL Snowflake.

Nota

Quando programas constroem instruções SQL com a entrada do usuário, há riscos potenciais de segurança, como injeção SQL. Se as entradas nas instruções SQL vierem de fontes externas, certifique-se de que elas sejam validadas. Para obter mais informações, consulte Injeção de SQL.

Usar a função TO_QUERY¶

Você pode usar a função TO_QUERY no código para procedimentos armazenados e aplicativos que constroem instruções SQL dinamicamente. Essa função de tabela recebe uma cadeia de caracteres SQL como entrada. Opcionalmente, a cadeia de caracteres SQL pode conter parâmetros, e você pode especificar os argumentos a serem passados aos parâmetros como variáveis de vinculação.

A seguir está um exemplo simples que chama a função:

SELECT COUNT(*) FROM TABLE(TO_QUERY('SELECT 1'));

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

O exemplo a seguir usa a função TO_QUERY em um procedimento armazenado:

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;

Copy

Observação: se você estiver usando o método SnowSQL, o Classic Console, execute_stream ou execute_string no código Python Connector, use este exemplo (consulte Uso do Script Snowflake em SnowSQL, Classic Console e conector Python):

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

Chame o procedimento armazenado:

CALL get_num_results_tq('SELECT 1');

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Usar SQL dinâmico em procedimentos armazenados e aplicativos¶

Para construir instruções SQL que aceitam entrada do usuário, você pode usar SQL dinâmico em um procedimento armazenado do Snowflake Scripting ou Javascript, ou em um bloco anônimo do Snowflake Scripting. Você também pode usar SQL dinâmico no código do seu aplicativo que usa um driver Snowflake ou o REST API SQL Snowflake.

Esse exemplo cria um procedimento armazenado com o Snowflake Scripting. O procedimento armazenado recebe texto SQL como entrada e constrói uma cadeia de caracteres que contém uma instrução SQL anexando o texto a ela. O SQL dinâmico é então executado usando o comando EXECUTE IMMEDIATE.

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;

Copy

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
$$
;

Copy

O exemplo a seguir chama o procedimento:

CALL get_num_results('SELECT 1');

Copy

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

O SQL dinâmico aceita variáveis de vinculação. O exemplo de Snowflake Scripting a seguir usa variáveis de vinculação representadas pelos espaços reservados ? para construir instruções SQL dinamicamente no tempo de execução. Esse bloco seleciona dados da seguinte tabela invoices:

CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2));
INSERT INTO invoices (price) VALUES
  (11.11),
  (22.22);

Copy

Execute o bloco anônimo:

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

+-------+
| PRICE |
|-------|
| 22.22 |
+-------+

Comparação das técnicas para construir SQL dinamicamente¶

A tabela a seguir descreve as vantagens e desvantagens das técnicas de construção dinâmica do SQL.

Técnica	Vantagens	Desvantagens
Função TO_QUERY	Sintaxe simples Tratamento de erros integrado Semântica específica para o caso de uso de construção dinâmica de SQL Conjunto de resultados determinado automaticamente	As consultas não podem ser descritas ou explicadas antes da execução Válido somente na cláusula FROM de uma instrução SELECT Específico para Snowflake
SQL dinâmico	Mais geral e flexível que a função TO_QUERY As consultas podem ser descritas ou explicadas antes da execução	Mais complexo que a função TO_QUERY Tratamento manual de erros