Como escrever procedimentos armazenados em JavaScript¶

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

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

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

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

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

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:

• Registro de mensagens em JavaScript

• Emissão de eventos de rastreamento em JavaScript

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