Como escrever procedimentos armazenados em JavaScript

Este tópico explica como escrever o código JavaScript para um procedimento armazenado.

Nota

Para criar e chamar um procedimento anônimo, use CALL (com procedimento anônimo). Criar e chamar um procedimento anônimo não exige uma função com privilégios de esquema CREATE PROCEDURE.

Neste tópico:

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

Compreensão da API JavaScript

A API JavaScript para procedimentos armazenados é semelhante, mas não idêntica, às APIs nos conectores e drivers do Snowflake (Node.js, JDBC, Python etc.).

A API permite realizar operações como:

  • Execute uma instrução SQL.

  • Recuperar os resultados de uma consulta (ou seja, um conjunto de resultados).

  • Recuperar metadados sobre o conjunto de resultados (número de colunas, tipos de dados das colunas etc.).

Essas operações são realizadas por meio de métodos de chamada para os seguintes objetos:

  • snowflake, que tem métodos para criar um objeto Statement e executar um comando SQL.

  • Statement, que ajuda a executar instruções preparadas e acessar metadados para essas instruções preparadas, além de permitir que você receba de volta um objeto ResultSet.

  • ResultSet, que contém os resultados de uma consulta (por exemplo, as linhas de dados obtidos para uma instrução SELECT).

  • SfDate, que é uma extensão de JavaScript Date (com métodos adicionais) e serve como um tipo de retorno para os tipos de dados SQL do Snowflake TIMESTAMP_LTZ, TIMESTAMP_NTZ e TIMESTAMP_TZ.

Esses objetos são descritos em detalhes em API de procedimentos armazenados em JavaScript.

Um procedimento armazenado típico contém um código semelhante ao seguinte pseudocódigo:

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();
Copy

Este código utiliza um objeto chamado snowflake, que é um objeto especial que existe sem ser declarado. O objeto é fornecido dentro do contexto de cada procedimento armazenado e expõe a API para permitir que você interaja com o servidor.

As outras variáveis (por exemplo, statement1) são criadas com instruções JavaScript var. Por exemplo:

var statement1 = ...;
Copy

Como mostrado na amostra de código acima, o objeto snowflake permite criar um objeto Statement chamando um dos métodos na API.

Aqui está um exemplo que obtém um ResultSet e faz iterações por meio dele:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;
Copy

A seção Exemplos (no final deste tópico) fornece exemplos adicionais que exercem cada um dos objetos e muitos dos métodos na API JavaScript do procedimento armazenado.

Mapeamentos de tipos de dados SQL e JavaScript

Ao chamar, usar e obter valores de procedimentos armazenados, muitas vezes é necessário converter de um tipo de dados SQL do Snowflake em um tipo de dados JavaScript ou vice-versa.

A conversão de SQL em JavaScript pode ocorrer ao:

  • Chamar um procedimento armazenado com um argumento. O argumento é um tipo de dados SQL; quando é armazenado dentro de uma variável JavaScript dentro do procedimento armazenado, ele deve ser convertido.

  • Obter um valor de um objeto ResultSet para uma variável JavaScript. ResultSet mantém o valor como um tipo de dados SQL, e a variável JavaScript deve armazenar o valor como um dos tipos de dados JavaScript.

A conversão de JavaScript em SQL pode ocorrer ao:

  • Retornar um valor do procedimento armazenado. A instrução return normalmente contém uma variável JavaScript que deve ser convertida em um tipo de dados SQL.

  • Construir dinamicamente uma instrução SQL que usa um valor em uma variável JavaScript.

  • Vincular o valor de uma variável JavaScript a uma instrução preparada.

Para obter mais informações sobre como o Snowflake mapeia os tipos de dados JavaScript e SQL, consulte Mapeamentos de tipos de dados SQL-JavaScript.

Dicas gerais

Continuação de linha

As instruções SQL podem ser bastante longas, e nem sempre é prático encaixá-las em uma única linha. O JavaScript trata um caractere de nova linha como o final de uma instrução. Se você quiser dividir uma instrução SQL longa em várias linhas, você pode usar as técnicas habituais do JavaScript para tratar cadeias de caracteres longas, inclusive:

  • Colocar uma barra invertida (caracteres de continuação de linha) imediatamente antes do final da linha. Por exemplo:

    var sql_command = "SELECT * \
                           FROM table1;";
    
    Copy
  • Usar aspas simples de abertura em vez de aspas duplas delimitando a cadeia de caracteres. Por exemplo:

    var sql_command = `SELECT *
                           FROM table1;`;
    
    Copy
  • Acumular a cadeia de caracteres. Por exemplo:

    var sql_command = "SELECT col1, col2"
    sql_command += "     FROM table1"
    sql_command += "     WHERE col1 >= 100"
    sql_command += "     ORDER BY col2;"
    
    Copy

Considerações sobre procedimentos armazenados em JavaScript

Intervalo de números do JavaScript

A faixa para números com precisão intacta é de

-(2^53 -1)

a

(2^53 -1)

O intervalo de valores válidos nos tipos de dados NUMBER(p, s) e DOUBLE do Snowflake é maior. Recuperar um valor do Snowflake e armazená-lo em uma variável numérica de JavaScript pode resultar em perda de precisão. Por exemplo:

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;
Copy
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+
Copy

As duas primeiras colunas devem coincidir, e a terceira deve conter 0.0.

O problema se aplica a funções definidas pelo usuário (UDFs) e procedimentos armazenados de JavaScript.

Se você tiver esse problema em procedimentos armazenados ao usar getColumnValue(), você pode conseguir evitar o problema recuperando um valor como uma cadeia de caracteres, por exemplo com:

getColumnValueAsString()
Copy

Você pode então retornar a cadeia de caracteres do procedimento armazenado e converter a cadeia em um tipo de dados numéricos em SQL.

Tratamento de erros JavaScript

Como um procedimento armazenado é escrito em JavaScript, ele pode usar a sintaxe try/catch do JavaScript.

O procedimento armazenado pode lançar uma exceção pré-definida ou uma exceção personalizada. Um exemplo simples de geração de uma exceção personalizada está aqui.

Você pode executar suas instruções SQL dentro de um bloco de tentativas. Se ocorrer um erro, seu bloco catch pode reverter todas as instruções (se você colocar as instruções em uma transação). A seção Exemplos contém um exemplo de reversão de uma transação em um procedimento armazenado.

Restrições aos procedimentos armazenados

Os procedimentos armazenados têm as seguintes restrições:

  • O código JavaScript não pode chamar a função JavaScript eval().

  • Procedimentos armazenados JavaScript oferecem suporte ao acesso à biblioteca padrão JavaScript. Observe que isto exclui muitos objetos e métodos normalmente fornecidos por navegadores. Não há nenhum mecanismo para importar, incluir ou chamar bibliotecas adicionais. Permitir bibliotecas de terceiros pode criar falhas de segurança.

  • O código JavaScript é executado dentro de um mecanismo restrito, impedindo chamadas de sistema a partir do contexto JavaScript (por exemplo, sem rede e acesso a disco) e restringindo os recursos do sistema disponíveis para o mecanismo, especificamente a memória.

Diferenciação entre maiúsculas e minúsculas em argumentos JavaScript

Os nomes dos argumentos não diferenciam maiúsculas e minúsculas na parte SQL do código de procedimento armazenado, mas diferenciam maiúsculas e minúsculas na parte JavaScript.

Para procedimentos armazenados (e UDFs) que usam JavaScript, os identificadores (como nomes de argumentos) na parte SQL da instrução são convertidos em maiúsculas automaticamente (a menos que você delimite o identificador com aspas duplas), enquanto os nomes de argumentos na parte JavaScript serão deixados na forma original. Isso pode fazer com que seu procedimento armazenado falhe sem retornar uma mensagem de erro explícita, pois os argumentos não são vistos.

Aqui está um exemplo de procedimento armazenado no qual o nome de um argumento no código JavaScript não corresponde ao nome do argumento no código SQL simplesmente porque as letras maiúsculas/minúsculas serão diferentes:

No exemplo abaixo, a primeira instrução de atribuição está incorreta porque o nome argument1 está em minúsculas.

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;
Copy

O uso de identificadores de maiúsculas (especialmente nomes de argumentos) de forma consistente em suas instruções SQL e código JavaScript tende a reduzir erros silenciosos.

Diferenciação entre maiúsculas e minúsculas em argumentos JavaScript

Os nomes dos argumentos não diferenciam maiúsculas e minúsculas na parte SQL do código de procedimento armazenado, mas diferenciam maiúsculas e minúsculas na parte JavaScript.

Delimitadores JavaScript

A parte JavaScript do código de procedimento armazenado deve estar contida dentro de aspas simples ' ou de cifrões duplos $$.

O uso de $$ facilita o tratamento do código JavaScript que contém aspas simples sem “escapar” dessas aspas.

Sobrecarga de nomes de procedimentos armazenados

Para obter mais informações sobre sobrecarga e convenções de nomenclatura, consulte Como nomear e sobrecarregar procedimentos e UDFs.

Variáveis de vinculação

Vincular uma variável a uma instrução SQL permite usar o valor da variável na instrução.

Você pode vincular valores NULL assim como valores não NULL.

O tipo de dados da variável deve ser apropriado para o uso do valor na instrução SQL. Atualmente, apenas variáveis JavaScript do tipo número, cadeia de caracteres e SfDate podem ser vinculadas. (Para obter mais detalhes sobre o mapeamento entre tipos de dados SQL e tipos de dados JavaScript, consulte Mapeamentos de tipos de dados SQL e JavaScript).

Aqui está um exemplo curto de vinculação:

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);
Copy

Aqui está um exemplo mais completo. Este exemplo vincula informações TIMESTAMP. Como a vinculação direta de dados SQL TIMESTAMP não é aceita, este exemplo passa o timestamp (carimbo de data/hora) como um VARCHAR e depois o vincula à instrução. Note que a própria instrução SQL converte o VARCHAR em TIMESTAMP chamando a função TO_TIMESTAMP():

Esta função simples retorna TRUE se o carimbo de data/hora especificado for anterior a agora, e FALSE caso contrário.

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;
Copy
CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+
Copy

Isso mostra como vincular um VARCHAR, um TIMESTAMP_LTZ e outros tipos de dados a uma instrução INSERT. O TIMESTAMP_LTZ vincula uma variável SfDate que é criada dentro do procedimento armazenado.

Crie uma tabela.

CREATE TABLE table1 (v VARCHAR,
                     ts1 TIMESTAMP_LTZ(9), 
                     int1 INTEGER,
                     float1 FLOAT,
                     numeric1 NUMERIC(10,9),
                     ts_ntz1 TIMESTAMP_NTZ,
                     date1 DATE,
                     time1 TIME
                     );
Copy

Crie um procedimento armazenado. Este procedimento aceita um VARCHAR e converte o VARCHAR em um TIMESTAMP_LTZ usando SQL. O procedimento obtém então o valor convertido a partir de um ResultSet. O valor é armazenado em uma variável JavaScript do tipo SfDate. O procedimento armazenado então vincula tanto o VARCHAR original como o TIMESTAMP_LTZ a uma instrução INSERT. Isso também demonstra a vinculação de dados numéricos JavaScript.

CREATE OR REPLACE PROCEDURE string_to_timestamp_ltz(TSV VARCHAR) 
RETURNS TIMESTAMP_LTZ 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Convert the input varchar to a TIMESTAMP_LTZ.
    var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_LTZ;"; 
    var stmt = snowflake.createStatement( {sqlText: sql_command} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    // Retrieve the TIMESTAMP_LTZ and store it in an SfDate variable.
    var my_sfDate = resultSet.getColumnValue(1); 

    f = 3.1415926;

    // Specify that we'd like position-based binding.
    sql_command = `INSERT INTO table1 VALUES(:1, :2, :3, :4, :5, :6, :7, :8);` 
    // Bind a VARCHAR, a TIMESTAMP_LTZ, a numeric to our INSERT statement.
    result = snowflake.execute(
        { 
        sqlText: sql_command, 
        binds: [TSV, my_sfDate, f, f, f, my_sfDate, my_sfDate, '12:30:00.123' ] 
        }
        ); 

    return my_sfDate; 
$$ ; 
Copy

Chame o procedimento.

CALL string_to_timestamp_ltz('2008-11-18 16:00:00');
+-------------------------------+
| STRING_TO_TIMESTAMP_LTZ       |
|-------------------------------|
| 2008-11-18 16:00:00.000 -0800 |
+-------------------------------+
Copy

Verifique se a linha foi inserida.

SELECT * FROM table1;
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
| V                   | TS1                           | INT1 |   FLOAT1 |    NUMERIC1 | TS_NTZ1                 | DATE1      | TIME1    |
|---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------|
| 2008-11-18 16:00:00 | 2008-11-18 16:00:00.000 -0800 |    3 | 3.141593 | 3.141593000 | 2008-11-18 16:00:00.000 | 2008-11-18 | 12:30:00 |
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
Copy

Para exemplos adicionais de vinculação de dados em JavaScript, consulte Vinculação de parâmetros de instrução.

Requisitos do código

O código JavaScript deve definir um único objeto literal JavaScript para que o procedimento armazenado seja válido.

Se o código JavaScript não atender a este requisito, o procedimento armazenado será criado; no entanto, falhará quando for chamado.

Tamanho do código

O Snowflake limita o tamanho máximo do código-fonte JavaScript no corpo de um procedimento armazenado JavaScript. 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).

Erros de tempo de execução

A maioria dos erros nos procedimentos armazenados aparece no momento da execução porque o código JavaScript é interpretado no momento em que o procedimento armazenado é executado, e não quando o procedimento armazenado é criado.

Suporte a SQL dinâmico

Os procedimentos armazenados podem ser usados para construir instruções SQL dinamicamente. Por exemplo, você pode construir uma cadeia de caracteres de comando SQL que contém uma mistura de SQL pré-configurado e entradas do usuário (por exemplo, o número de conta de um usuário).

Para exemplos, consulte Criação dinâmica de uma instrução SQL e a seção Exemplos.

API síncrona

A API para os procedimentos armazenados do Snowflake é síncrona. Dentro de um procedimento armazenado, você pode executar apenas um thread de cada vez.

Note que isso é diferente da regra para a execução JavaScript com o conector Node.js, que permite executar threads assíncronos.

Exemplos

Exemplos básicos

O exemplo a seguir mostra a sintaxe básica de criar e chamar um procedimento armazenado. Ele não executa um SQL ou código de procedimento. No entanto, ele fornece um ponto de partida para exemplos mais realistas posteriores:

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;
Copy

Observe que o delimitador $$ marca o início e o fim do código JavaScript.

Agora chame o procedimento que você acabou de criar:

CALL sp_pi();
+-----------+
|     SP_PI |
|-----------|
| 3.1415926 |
+-----------+
Copy

O exemplo a seguir ilustra como executar uma instrução SQL dentro de um procedimento armazenado:

  1. Crie uma tabela:

    CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));
    
    Copy
  2. Crie um procedimento armazenado. Isto insere uma linha em uma tabela existente chamada stproc_test_table1 e retorna o valor “Succeeded.” (bem-sucedido). O valor retornado não é particularmente útil de uma perspectiva SQL, mas permite que você retorne informações de status (por exemplo “Succeeded.”, para bem-sucedido, ou “Failed.”, para falha) para o usuário.

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
        returns string
        language javascript
        strict
        execute as owner
        as
        $$
        var sql_command = 
         "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
        try {
            snowflake.execute (
                {sqlText: sql_command}
                );
            return "Succeeded.";   // Return a success/error indicator.
            }
        catch (err)  {
            return "Failed: " + err;   // Return a success/error indicator.
            }
        $$
        ;
    
    Copy
  3. Chame o procedimento armazenado:

    call stproc1(5.14::FLOAT);
    +------------+
    | STPROC1    |
    |------------|
    | Succeeded. |
    +------------+
    
    Copy
  4. Confirme que o procedimento armazenado inseriu a linha:

    select * from stproc_test_table1;
    +-----------+
    |  NUM_COL1 |
    |-----------|
    | 5.1400000 |
    +-----------+
    
    Copy

O exemplo a seguir obtém um resultado:

  1. Crie um procedimento para contar o número de linhas em uma tabela (equivalente a select count(*) from table):

    create or replace procedure get_row_count(table_name VARCHAR)
      returns float not null
      language javascript
      as
      $$
      var row_count = 0;
      // Dynamically compose the SQL statement to execute.
      var sql_command = "select count(*) from " + TABLE_NAME;
      // Run the statement.
      var stmt = snowflake.createStatement(
             {
             sqlText: sql_command
             }
          );
      var res = stmt.execute();
      // Get back the row count. Specifically, ...
      // ... get the first (and in this case only) row from the result set ...
      res.next();
      // ... and then get the returned value, which in this case is the number of
      // rows in the table.
      row_count = res.getColumnValue(1);
      return row_count;
      $$
      ;
    
    Copy
  2. Pergunte ao procedimento armazenado quantas linhas estão na tabela:

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
    Copy
  3. Verifique independentemente se você conseguiu o número certo:

    select count(*) from stproc_test_table1;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    
    Copy

Exemplo de procedimento armazenado recursivo

O exemplo a seguir mostra um procedimento básico, mas não particularmente realista, de um procedimento armazenado recursivo:

create or replace table stproc_test_table2 (col1 FLOAT);
Copy
create or replace procedure recursive_stproc(counter FLOAT)
    returns varchar not null
    language javascript
    as
    -- "$$" is the delimiter that shows the beginning and end of the stored proc.
    $$
    var counter1 = COUNTER;
    var returned_value = "";
    var accumulator = "";
    var stmt = snowflake.createStatement(
        {
        sqlText: "INSERT INTO stproc_test_table2 (col1) VALUES (?);",
        binds:[counter1]
        }
        );
    var res = stmt.execute();
    if (COUNTER > 0)
        {
        stmt = snowflake.createStatement(
            {
            sqlText: "call recursive_stproc (?);",
            binds:[counter1 - 1]
            }
            );
        res = stmt.execute();
        res.next();
        returned_value = res.getColumnValue(1);
        }
    accumulator = accumulator + counter1 + ":" + returned_value;
    return accumulator;
    $$
    ;
Copy
call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+
Copy
SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
Copy

Criação dinâmica de uma instrução SQL

O exemplo a seguir mostra como criar dinamicamente uma instrução SQL:

Nota

Como mencionado em Injeção de SQL (neste tópico), tome cuidado para evitar ataques ao usar SQL dinâmico.

  1. Crie o procedimento armazenado. Este procedimento permite passar o nome de uma tabela e obter o número de linhas naquela tabela (equivalente a select count(*) from table_name):

    create or replace procedure get_row_count(table_name VARCHAR)
        returns float 
        not null
        language javascript
        as
        $$
        var row_count = 0;
        // Dynamically compose the SQL statement to execute.
        // Note that we uppercased the input parameter name.
        var sql_command = "select count(*) from " + TABLE_NAME;
        // Run the statement.
        var stmt = snowflake.createStatement(
               {
               sqlText: sql_command
               }
            );
        var res = stmt.execute();
        // Get back the row count. Specifically, ...
        // ... first, get the first (and in this case only) row from the
        //  result set ...
        res.next();
        // ... then extract the returned value (which in this case is the
        // number of rows in the table).
        row_count = res.getColumnValue(1);
        return row_count;
        $$
        ;
    
    Copy
  2. Chame o procedimento armazenado:

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
    Copy
  3. Mostre os resultados de select count(*) para a mesma tabela:

    SELECT COUNT(*) FROM stproc_test_table1;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    
    Copy

Obtenção de metadados do conjunto de resultados

Este exemplo demonstra a obtenção de uma pequena quantidade de metadados de um conjunto de resultados:

create or replace table stproc_test_table3 (
    n10 numeric(10,0),     /* precision = 10, scale = 0 */
    n12 numeric(12,4),     /* precision = 12, scale = 4 */
    v1 varchar(19)         /* scale = 0 */
    );
Copy
create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select n10, n12, v1 from stproc_test_table3;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;
Copy
call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
Copy
call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+
Copy
call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
Copy

Captura de um erro usando Try/Catch

Este exemplo demonstra o uso de um bloco try/catch JavaScript para capturar um erro dentro de um procedimento armazenado:

  1. Crie o procedimento armazenado:

        create procedure broken()
          returns varchar not null
          language javascript
          as
          $$
          var result = "";
          try {
              snowflake.execute( {sqlText: "Invalid Command!;"} );
              result = "Succeeded";
              }
          catch (err)  {
              result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
              result += "\n  Message: " + err.message;
              result += "\nStack Trace:\n" + err.stackTraceTxt; 
              }
          return result;
          $$
          ;
    
    Copy
  2. Chame o procedimento armazenado. Isso deve retornar um erro mostrando o número do erro e outras informações:

        -- This is expected to fail.
        call broken();
    +---------------------------------------------------------+
    | BROKEN                                                  |
    |---------------------------------------------------------|
    | Failed: Code: 1003                                      |
    |   State: 42000                                          |
    |   Message: SQL compilation error:                       |
    | syntax error line 1 at position 0 unexpected 'Invalid'. |
    | Stack Trace:                                            |
    | Snowflake.execute, line 4 position 20                   |
    +---------------------------------------------------------+
    
    Copy

O exemplo a seguir demonstra o lançamento de uma exceção personalizada:

  1. Crie o procedimento armazenado:

    CREATE OR REPLACE PROCEDURE validate_age (age float)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
        try {
            if (AGE < 0) {
                throw "Age cannot be negative!";
            } else {
                return "Age validated.";
            }
        } catch (err) {
            return "Error: " + err;
        }
    $$;
    
    Copy
  2. Chame o procedimento armazenado com valores válidos e inválidos:

    CALL validate_age(50);
    +----------------+
    | VALIDATE_AGE   |
    |----------------|
    | Age validated. |
    +----------------+
    CALL validate_age(-2);
    +--------------------------------+
    | VALIDATE_AGE                   |
    |--------------------------------|
    | Error: Age cannot be negative! |
    +--------------------------------+
    
    Copy

Uso de transações em procedimentos armazenados

O exemplo a seguir delimita várias instruções relacionadas em uma transação e usa try/catch para confirmar ou reverter. O parâmetro force_failure permite que o chamador escolha entre execução bem-sucedida e erro deliberado.

-- Create the procedure
create or replace procedure cleanup(force_failure varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "BEGIN WORK;"} );
  try {
      snowflake.execute( {sqlText: "DELETE FROM child;"} );
      snowflake.execute( {sqlText: "DELETE FROM parent;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
          }
      snowflake.execute( {sqlText: "COMMIT WORK;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

call cleanup('fail');

call cleanup('do not fail');
Copy

Registro de um erro

É possível capturar dados de log e rastreamento do código do manipulador JavaScript usando o objeto snowflake na API JavaScript. Ao fazer isso, as mensagens de log e os dados de rastreamento são armazenados em uma tabela de eventos que pode ser analisada com consultas.

Para obter mais informações, consulte o seguinte:

Uso de RESULT_SCAN para recuperar o resultado de procedimento armazenado

Este exemplo mostra como usar a função RESULT_SCAN para recuperar o resultado a partir de uma instrução CALL:

  1. Criar e carregar a tabela:

    CREATE TABLE western_provinces(ID INT, province VARCHAR);
    
    Copy
    INSERT INTO western_provinces(ID, province) VALUES
        (1, 'Alberta'),
        (2, 'British Columbia'),
        (3, 'Manitoba')
        ;
    
    Copy
  2. Crie o procedimento armazenado. Este procedimento retorna uma cadeia de caracteres bem formatada que parece um conjunto de resultados de três linhas, mas na verdade é uma única cadeia de caracteres:

    CREATE OR REPLACE PROCEDURE read_western_provinces()
      RETURNS VARCHAR NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      var return_value = "";
      try {
          var command = "SELECT * FROM western_provinces ORDER BY province;"
          var stmt = snowflake.createStatement( {sqlText: command } );
          var rs = stmt.execute();
          if (rs.next())  {
              return_value += rs.getColumnValue(1);
              return_value += ", " + rs.getColumnValue(2);
              }
          while (rs.next())  {
              return_value += "\n";
              return_value += rs.getColumnValue(1);
              return_value += ", " + rs.getColumnValue(2);
              }
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt;
          }
      return return_value;
      $$
      ;
    
    Copy
  3. Chame o procedimento armazenado e depois recupere os resultados usando RESULT_SCAN:

    CALL read_western_provinces();
    +------------------------+
    | READ_WESTERN_PROVINCES |
    |------------------------|
    | 1, Alberta             |
    | 2, British Columbia    |
    | 3, Manitoba            |
    +------------------------+
    SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    +------------------------+
    | READ_WESTERN_PROVINCES |
    |------------------------|
    | 1, Alberta             |
    | 2, British Columbia    |
    | 3, Manitoba            |
    +------------------------+
    
    Copy

Você pode realizar operações mais complexas sobre o valor retornado pela função RESULT_SCAN. Neste caso, como o valor retornado é uma única linha, você pode extrair as “linhas” individuais que parecem estar contidas dentro dessa cadeia de caracteres e armazenar essas linhas em outra tabela.

O exemplo a seguir, que é uma continuação do exemplo anterior, ilustra uma maneira de fazer isso:

  1. Crie uma tabela para armazenamento a longo prazo. Esta tabela contém o nome da província e a ID da província depois de extraí-los da cadeia de caracteres retornada pelo comando CALL:

    CREATE TABLE all_provinces(ID INT, province VARCHAR);
    
    Copy
  2. Chame o procedimento armazenado, depois recupere o resultado usando RESULT_SCAN e depois extraia as três linhas da cadeia de caracteres e coloque essas linhas na tabela:

    INSERT INTO all_provinces
      WITH 
        one_string (string_col) AS
          (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
        three_strings (one_row) AS
          (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
      SELECT
             STRTOK(one_row, ',', 1) AS ID,
             STRTOK(one_row, ',', 2) AS province
        FROM three_strings
        WHERE NOT (ID IS NULL AND province IS NULL);
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    
    Copy
  3. Verifique se isso funcionou mostrando as linhas na tabela:

    SELECT ID, province 
        FROM all_provinces;
    +----+-------------------+
    | ID | PROVINCE          |
    |----+-------------------|
    |  1 |  Alberta          |
    |  2 |  British Columbia |
    |  3 |  Manitoba         |
    +----+-------------------+
    
    Copy

Aqui está aproximadamente o mesmo código, mas em etapas menores:

  1. Crie uma tabela com o nome one_string. Esta tabela armazena temporariamente o resultado do comando CALL. O resultado do CALL é uma única cadeia de caracteres, portanto essa tabela armazena apenas um único valor VARCHAR.

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
    
    Copy
  2. Chame o procedimento armazenado, depois recupere o resultado (uma cadeia de caracteres) usando RESULT_SCAN e então armazene isso na tabela intermediária chamada one_string:

    CALL read_western_provinces();
    +------------------------+
    | READ_WESTERN_PROVINCES |
    |------------------------|
    | 1, Alberta             |
    | 2, British Columbia    |
    | 3, Manitoba            |
    +------------------------+
    INSERT INTO one_string
        SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       1 |
    +-------------------------+
    
    Copy

    Isso mostra a nova linha na tabela one_string. Lembre-se de que embora isso seja formatado para parecer três linhas, na verdade é uma única cadeia de caracteres:

    SELECT string_col FROM one_string;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    -- Show that it's one string, not three rows:
    SELECT '>>>' || string_col || '<<<' AS string_col 
        FROM one_string;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | >>>1, Alberta       |
    | 2, British Columbia |
    | 3, Manitoba<<<      |
    +---------------------+
    SELECT COUNT(*) FROM one_string;
    +----------+
    | COUNT(*) |
    |----------|
    |        1 |
    +----------+
    
    Copy

    Os comandos a seguir mostram como extrair várias linhas da cadeia de caracteres:

    SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +---------------------+-----+-------+---------------------+
    | STRING_COL          | SEQ | INDEX | VALUE               |
    |---------------------+-----+-------+---------------------|
    | 1, Alberta          |   1 |     1 | 1, Alberta          |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    | 1, Alberta          |   1 |     2 | 2, British Columbia |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    | 1, Alberta          |   1 |     3 | 3, Manitoba         |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    +---------------------+-----+-------+---------------------+
    SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +---------------------+
    | VALUE               |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    
    Copy
  3. Em seguida, crie uma tabela chamada three_strings. Esta tabela manterá o resultado depois que você dividi-lo em linhas/cadeias de caracteres individuais:

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
    
    Copy
  4. Agora, converta aquela cadeia de caracteres na tabela one_string em três cadeias separadas e mostre que agora são realmente três cadeias:

    INSERT INTO three_strings
      SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    SELECT string_col 
        FROM three_strings;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    SELECT COUNT(*) 
        FROM three_strings;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    
    Copy
  5. Agora converta as três cadeias de caracteres em três linhas em nossa tabela de longo prazo chamada all_provinces:

    INSERT INTO all_provinces
      SELECT 
             STRTOK(string_col, ',', 1) AS ID, 
             STRTOK(string_col, ',', 2) AS province 
        FROM three_strings
        WHERE NOT (ID IS NULL AND province IS NULL);
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    
    Copy
  6. Mostre as três linhas na tabela de longo prazo:

    SELECT ID, province 
        FROM all_provinces;
    +----+-------------------+
    | ID | PROVINCE          |
    |----+-------------------|
    |  1 |  Alberta          |
    |  2 |  British Columbia |
    |  3 |  Manitoba         |
    +----+-------------------+
    SELECT COUNT(*) 
        FROM all_provinces;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    
    Copy

Retorno de uma matriz de mensagens de erro

Seu procedimento armazenado pode executar mais de uma instrução SQL, e você pode retornar uma mensagem de status/erro para cada instrução SQL. Entretanto, um procedimento armazenado retorna uma única linha; ele não é projetado para retornar várias linhas.

Se todas as suas mensagens couberem em um único valor do tipo ARRAY, você pode obter todas as mensagens de um procedimento armazenado com um esforço adicional.

O exemplo seguinte mostra uma maneira de fazer isso (as mensagens de erro mostradas não são reais, mas você pode estender esse código para trabalhar com suas instruções SQL reais):

CREATE OR REPLACE PROCEDURE sp_return_array()
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This array will contain one error message (or an empty string) 
      // for each SQL command that we executed.
      var array_of_rows = [];

      // Artificially fake the error messages.
      array_of_rows.push("ERROR: The foo was barred.")
      array_of_rows.push("WARNING: A Carrington Event is predicted.")

      return array_of_rows;
      $$
      ;
Copy
CALL sp_return_array();
+-----------------------------------------------+
| SP_RETURN_ARRAY                               |
|-----------------------------------------------|
| [                                             |
|   "ERROR: The foo was barred.",               |
|   "WARNING: A Carrington Event is predicted." |
| ]                                             |
+-----------------------------------------------+
-- Now get the individual error messages, in order.
SELECT INDEX, VALUE 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(INPUT => res.$1)
    ORDER BY index
    ;
+-------+---------------------------------------------+
| INDEX | VALUE                                       |
|-------+---------------------------------------------|
|     0 | "ERROR: The foo was barred."                |
|     1 | "WARNING: A Carrington Event is predicted." |
+-------+---------------------------------------------+
Copy

Lembre-se, esta não é uma solução de finalidade geral. Há um limite para o tamanho máximo do tipo de dados ARRAY, e todo seu conjunto de resultados deve caber em um único ARRAY.

Retorno de um conjunto de resultados

Esta seção estende o exemplo anterior descrito em Retorno de uma matriz de mensagens de erro. Este exemplo é mais geral e permite que você retorne um conjunto de resultados definido a partir de uma consulta.

Um procedimento armazenado retorna uma única linha que contém uma única coluna; ele não é projetado para retornar um conjunto de resultados. Entretanto, se seu conjunto de resultados for pequeno o suficiente para caber em um único valor do tipo VARIANT ou ARRAY, você pode retornar um conjunto de resultados de um procedimento armazenado com um código adicional:

CREATE TABLE return_to_me(col_i INT, col_v VARCHAR);
INSERT INTO return_to_me (col_i, col_v) VALUES
    (1, 'Ariel'),
    (2, 'October'),
    (3, NULL),
    (NULL, 'Project');
Copy
-- Create the stored procedure that retrieves a result set and returns it.
CREATE OR REPLACE PROCEDURE sp_return_table(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This variable will hold a JSON data structure that holds ONE row.
      var row_as_json = {};
      // This array will contain all the rows.
      var array_of_rows = [];
      // This variable will hold a JSON data structure that we can return as
      // a VARIANT.
      // This will contain ALL the rows in a single "value".
      var table_as_json = {};

      // Run SQL statement(s) and get a resultSet.
      var command = "SELECT * FROM " + TABLE_NAME;
      var cmd1_dict = {sqlText: command};
      var stmt = snowflake.createStatement(cmd1_dict);
      var rs = stmt.execute();

      // Read each row and add it to the array we will return.
      var row_num = 1;
      while (rs.next())  {
        // Put each row in a variable of type JSON.
        row_as_json = {};
        // For each column in the row...
        for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
          var col_name = COL_NAMES[col_num];
          row_as_json[col_name] = rs.getColumnValue(col_num + 1);
          }
        // Add the row to the array of rows.
        array_of_rows.push(row_as_json);
        ++row_num;
        }
      // Put the array in a JSON variable (so it looks like a VARIANT to
      // Snowflake).  The key is "key1", and the value is the array that has
      // the rows we want.
      table_as_json = { "key1" : array_of_rows };

      // Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
      return table_as_json;
      $$
      ;
Copy
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
-- Use "ResultScan" to get the data from the stored procedure that
-- "did not return a result set".
-- Use "$1:key1" to get the value corresponding to the JSON key named "key1".
SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| $1:KEY1                |
|------------------------|
| [                      |
|   {                    |
|     "COL_I": 1,        |
|     "COL_V": "Ariel"   |
|   },                   |
|   {                    |
|     "COL_I": 2,        |
|     "COL_V": "October" |
|   },                   |
|   {                    |
|     "COL_I": 3,        |
|     "COL_V": null      |
|   },                   |
|   {                    |
|     "COL_I": null,     |
|     "COL_V": "Project" |
|   }                    |
| ]                      |
+------------------------+
-- Now get what we really want.
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(input => res.$1)
  ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+
Copy

Isto mostra como combinar as duas linhas anteriores em uma única linha:

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM (SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res,
            LATERAL FLATTEN(input => res.$1)
       ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+
Copy

Por conveniência, você pode delimitar a linha anterior em uma exibição. Essa exibição também converte a cadeia de caracteres “null” em um verdadeiro NULL. Você só precisa criar a exibição uma vez. Entretanto, você deve chamar o procedimento armazenado imediatamente antes de selecionar a partir dessa exibição sempre que utilizar a exibição. Lembre-se, a chamada para RESULT_SCAN na exibição está sendo obtida da instrução mais recente, que deve ser a CALL:

CREATE VIEW stproc_view (col_i, col_v) AS 
  SELECT NULLIF(VALUE:COL_I::VARCHAR, 'null'::VARCHAR), 
         NULLIF(value:COL_V::VARCHAR, 'null'::VARCHAR)
    FROM (SELECT $1:key1 AS tbl FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res, 
         LATERAL FLATTEN(input => res.tbl);
Copy
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT * 
    FROM stproc_view
    ORDER BY COL_I;
+-------+---------+
| COL_I | COL_V   |
|-------+---------|
| 1     | Ariel   |
| 2     | October |
| 3     | NULL    |
| NULL  | Project |
+-------+---------+
Copy

Você pode até usá-la como uma exibição verdadeira (ou seja, selecionar um subconjunto dela):

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT COL_V 
    FROM stproc_view
    WHERE COL_V IS NOT NULL
    ORDER BY COL_V;
+---------+
| COL_V   |
|---------|
| Ariel   |
| October |
| Project |
+---------+
Copy

Lembre-se, esta não é uma solução de finalidade geral. Há um limite para o tamanho máximo do tipo de dados VARIANT e ARRAY, e todo seu conjunto de resultados deve caber em um único VARIANT ou ARRAY.

Proteção da privacidade

Este exemplo mostra um procedimento armazenado que é útil para um varejista on-line. Esse procedimento armazenado respeita a privacidade dos clientes e, ao mesmo tempo, protege os interesses legítimos tanto do varejista quanto do cliente. Se um cliente pede ao varejista para apagar os dados do cliente por razões de privacidade, então este procedimento armazenado apaga a maioria dos dados do cliente, mas deixa o histórico de compras do cliente se uma das seguintes opções for verdadeira:

  • Qualquer item comprado tem uma garantia que ainda não expirou.

  • O cliente ainda deve dinheiro (ou o cliente está aguardando um reembolso).

Uma versão mais real disto excluiria linhas individuais para as quais o pagamento foi feito e a garantia expirou.

  1. Comece criando as tabelas e carregando-as:

    create table reviews (customer_ID VARCHAR, review VARCHAR);
    create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                                   product_ID VARCHAR, purchase_date DATE);
    
    Copy
    insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
        (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
        (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
        (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);
    
    insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
    insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');
    
    Copy
  2. Crie o procedimento armazenado:

    create or replace procedure delete_nonessential_customer_data(customer_ID varchar)
        returns varchar not null
        language javascript
        as
        $$
    
        // If the customer posted reviews of products, delete those reviews.
        var sql_cmd = "DELETE FROM reviews WHERE customer_ID = " + CUSTOMER_ID;
        snowflake.execute( {sqlText: sql_cmd} );
    
        // Delete any other records not needed for warranty or payment info.
        // ...
    
        var result = "Deleted non-financial, non-warranty data for customer " + CUSTOMER_ID;
    
        // Find out if the customer has any net unpaid balance (or surplus/prepayment).
        sql_cmd = "SELECT SUM(price) - SUM(paid) FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
        var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
        var rs = stmt.execute();
        // There should be only one row, so should not need to iterate.
        rs.next();
        var net_amount_owed = rs.getColumnValue(1);
    
        // Look up the number of purchases still under warranty...
        var number_purchases_under_warranty = 0;
        // Assuming a 1-year warranty...
        sql_cmd = "SELECT COUNT(*) FROM purchase_history ";
        sql_cmd += "WHERE customer_ID = " + CUSTOMER_ID;
        // Can't use CURRENT_DATE() because that changes. So assume that today is 
        // always June 15, 2019.
        sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, '2019-06-15'::DATE)";
        var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
        var rs = stmt.execute();
        // There should be only one row, so should not need to iterate.
        rs.next();
        number_purchases_under_warranty = rs.getColumnValue(1);
    
        // Check whether need to keep some purchase history data; if not, then delete the data.
        if (net_amount_owed == 0.0 && number_purchases_under_warranty == 0)  {
            // Delete the purchase history of this customer ...
            sql_cmd = "DELETE FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
            snowflake.execute( {sqlText: sql_cmd} );
            // ... and delete anything else that that should be deleted.
            // ...
            result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
            }
        return result;
        $$
        ;
    
    Copy
  3. Mostre os dados nas tabelas antes de excluir qualquer um desses dados:

    SELECT * FROM reviews;
    +-------------+---------------------------+
    | CUSTOMER_ID | REVIEW                    |
    |-------------+---------------------------|
    | 1           | Loved the milk chocolate! |
    | 2           | Loved the dark chocolate! |
    +-------------+---------------------------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
    Copy
  4. O cliente n.º 1 tem uma garantia que ainda está em vigor. O procedimento armazenado exclui os comentários de avaliação que ele publicou, mas mantém seu registro de compra por causa da garantia:

    call delete_nonessential_customer_data(1);
    +---------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
    |---------------------------------------------------------|
    | Deleted non-financial, non-warranty data for customer 1 |
    +---------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+---------------------------+
    | CUSTOMER_ID | REVIEW                    |
    |-------------+---------------------------|
    | 2           | Loved the dark chocolate! |
    +-------------+---------------------------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
    Copy
  5. O cliente n.º 2 ainda deve dinheiro. O procedimento armazenado exclui os comentários de avaliação, mas mantém seu registro de compra:

    call delete_nonessential_customer_data(2);
    +---------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
    |---------------------------------------------------------|
    | Deleted non-financial, non-warranty data for customer 2 |
    +---------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+--------+
    | CUSTOMER_ID | REVIEW |
    |-------------+--------|
    +-------------+--------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
    Copy
  6. O cliente n.º 3 não deve dinheiro (e não precisa receber dinheiro). Sua garantia expirou, portanto o procedimento armazenado exclui tanto os comentários de avaliação quanto os registros de compra:

    call delete_nonessential_customer_data(3);
    +-------------------------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                                       |
    |-------------------------------------------------------------------------|
    | Deleted all data, including financial and warranty data, for customer 3 |
    +-------------------------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+--------+
    | CUSTOMER_ID | REVIEW |
    |-------------+--------|
    +-------------+--------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    +-------------+-------+-------+------------+---------------+
    
    Copy

Uso de variáveis de sessão com procedimentos armazenados dos direitos do chamador e direitos do proprietário

Estes exemplos ilustram uma das principais diferenças entre os procedimentos armazenados dos direitos do chamador e dos direitos do proprietário. Eles tentam usar as variáveis de sessão de duas maneiras:

  • Definir uma variável de sessão antes de chamar o procedimento armazenado, depois usar a variável de sessão dentro do procedimento armazenado.

  • Definir uma variável de sessão dentro do procedimento armazenado, depois usar a variável de sessão após retornar dos procedimentos armazenados.

Tanto usar a variável de sessão quanto definir a variável de sessão funciona corretamente em um procedimento armazenado dos direitos do chamador. Ambas falham ao utilizar um procedimento armazenado dos direitos do proprietário mesmo que o chamador seja o proprietário.

Procedimento armazenado dos direitos do chamador

O exemplo a seguir demonstra o procedimento armazenado dos direitos de um chamador.

  1. Criar e carregar uma tabela:

    create table sv_table (f float);
    insert into sv_table (f) values (49), (51);
    
    Copy
  2. Defina uma variável de sessão:

    set SESSION_VAR1 = 50;
    
    Copy
  3. Crie um procedimento armazenado dos direitos do chamador que utilize uma variável de sessão e defina outra:

    create procedure session_var_user()
      returns float
      language javascript
      EXECUTE AS CALLER
      as
      $$
      // Set the second session variable
      var stmt = snowflake.createStatement(
          {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
          );
      var rs = stmt.execute();  // we ignore the result in this case
      // Run a query using the first session variable
      stmt = snowflake.createStatement(
          {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
          );
      rs = stmt.execute();
      rs.next();
      var output = rs.getColumnValue(1);
      return output;
      $$
      ;
    
    Copy
  4. Chame o procedimento:

    CALL session_var_user();
    +------------------+
    | SESSION_VAR_USER |
    |------------------|
    |               51 |
    +------------------+
    
    Copy
  5. Exiba o valor da variável de sessão definida dentro do procedimento armazenado:

    SELECT $SESSION_VAR2;
    +------------------------------+
    | $SESSION_VAR2                |
    |------------------------------|
    | I was set inside the StProc. |
    +------------------------------+
    
    Copy

Nota

Embora você possa definir uma variável de sessão dentro de um procedimento armazenado e deixá-la definida após o final do procedimento, o Snowflake não recomenda fazer isso.

Procedimento armazenado dos direitos do proprietário

O exemplo a seguir demonstra o procedimento armazenado dos direitos de um proprietário.

  1. Crie um procedimento armazenado dos direitos do proprietário que utilize uma variável de sessão:

    create procedure cannot_use_session_vars()
      returns float
      language javascript
      EXECUTE AS OWNER
      as
      $$
      // Run a query using the first session variable
      var stmt = snowflake.createStatement(
          {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
          );
      var rs = stmt.execute();
      rs.next();
      var output = rs.getColumnValue(1);
      return output;
      $$
      ;
    
    Copy
  2. Chame o procedimento (ele deve falhar):

    CALL cannot_use_session_vars();
    
    Copy
  3. Crie um procedimento armazenado dos direitos do proprietário que tente definir uma variável de sessão:

    create procedure cannot_set_session_vars()
      returns float
      language javascript
      EXECUTE AS OWNER
      as
      $$
      // Set the second session variable
      var stmt = snowflake.createStatement(
          {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
          );
      var rs = stmt.execute();  // we ignore the result in this case
      return 3.0;   // dummy value.
      $$
      ;
    
    Copy
  4. Chame o procedimento (ele deve falhar):

    CALL cannot_set_session_vars();
    
    Copy

Solução de problemas

Uma técnica geral de solução de problemas é usar um bloco try/catch JavaScript para capturar o erro e exibir informações de erro. O objeto de erro contém:

  • Código de erro.

  • Mensagem de erro.

  • Estado de erro.

  • Rastreamento de pilha no ponto de falha.

Para obter mais informações, incluindo um exemplo, de como utilizar essas informações, consulte Captura de um erro usando Try/Catch (neste tópico).

As seções seguintes fornecem sugestões adicionais para ajudar na depuração de problemas específicos.

Procedimento armazenado ou UDF retorna NULL inesperadamente

Causa

Seu procedimento armazenado/UDF tem um parâmetro, e dentro do procedimento/UDF, o parâmetro é referido por seu nome em minúsculas, mas o Snowflake converteu automaticamente o nome em maiúsculas.

Solução

Uma das seguintes opções:

  • Usar maiúsculas para o nome da variável dentro do código JavaScript ou

  • Colocar o nome da variável entre aspas duplas no código SQL.

Para obter mais detalhes, consulte Argumentos e valores retornados de JavaScript.

Procedimento armazenado nunca termina a execução

Causa

Você pode ter um loop infinito em seu código JavaScript.

Solução

Verifique e conserte os loops infinitos.

Erro: Failed: empty argument passed

Causa

Seu procedimento armazenado pode conter “sqltext” quando deveria ter “sqlText” (o primeiro é somente em minúsculas; o segundo mistura maiúsculas e minúsculas).

Solução

Use “sqlText”.

Erro: JavaScript out of memory error: UDF thread memory limit exceeded

Causa

Você pode ter um loop infinito em seu código JavaScript.

Solução

Verifique e conserte os loops infinitos. Em especial, certifique-se de não chamar a próxima linha quando o conjunto de resultados se esgotar (isto é, quando resultSet.next() retornar false).