Écriture de procédures stockées en JavaScript¶

Conversion de SQL > JavaScript¶

Le tableau suivant indique les types de données SQL Snowflake et les types de données JavaScript correspondants :

Par exemple, si votre instruction SQL sélectionne une colonne VARIANT dans une table Snowflake, lorsque vous copiez la valeur de ResultSet dans une variable JavaScript, la variable JavaScript doit être de type JSON.

Tous les types de données SQL de Snowflake ont un type de données JavaScript correspondant. Par exemple, JavaScript ne prend pas directement en charge les types de données INTEGER ou NUMBER. Dans ces cas, vous devez convertir le type de données SQL en un type de données alternatif approprié. Par exemple, vous pouvez convertir un SQL INTEGER en SQL FLOAT, qui peut ensuite être converti en une valeur JavaScript de type de données number.

Le tableau ci-dessous indique les conversions appropriées pour les types de données SQL incompatibles :

Conversion de JavaScript > SQL¶

Informations supplémentaires sur la conversion du type de données¶

Vous pouvez également trouver les sujets suivants utiles :

• Types de données JavaScript

• Arguments JavaScript et valeurs retournées

Ces informations sur les types de données, la capitalisation des noms d’arguments et les valeurs NULL s’appliquent aux procédures stockées, ainsi qu’aux fonctions définies par l’utilisateur (UDFs).

Pour plus d’informations sur les types de données SQL de Snowflake , voir Résumé des types de données.

Continuation de la facturation¶

Les instructions SQL peuvent être assez longues et il n’est pas toujours pratique de les insérer sur une seule ligne. JavaScript traite une nouvelle ligne comme la fin d’une instruction. Si vous souhaitez fractionner une instruction longue SQL sur plusieurs lignes, vous pouvez utiliser les techniques habituelles JavaScript pour gérer les chaînes longues, notamment :

• Mettre une barre oblique inverse (caractère de continuation de ligne) immédiatement avant la fin de la ligne. Par exemple :

  var sql_command = "SELECT * \
                         FROM table1;";
  

• Utiliser des guillemets simples (guillemets simples) plutôt que des guillemets doubles autour de la chaîne. Par exemple :

  var sql_command = `SELECT *
                         FROM table1;`;
  

• Accumuler la chaîne. Par exemple :

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

Plage de nombres JavaScript¶

La plage pour les nombres dont la précision est intacte est de

-(2^53 -1)

dans

(2^53 -1)

La plage de valeurs valides dans les types de données Snowflake NUMBER(p, s) et DOUBLE est plus grande. La récupération d’une valeur de Snowflake et son stockage dans une variable numérique JavaScript peut entraîner une perte de précision. Par exemple :

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Les deux premières colonnes doivent correspondre, et la troisième doit contenir 0.0.

Le problème s’applique aux fonctions JavaScript définies par l’utilisateur (UDFs) et aux procédures stockées.

Si vous rencontrez le problème dans les procédures stockées avec getColumnValue(), vous pouvez l’éviter en récupérant une valeur sous forme de chaîne, par exemple avec :

getColumnValueAsString()

Vous pouvez ensuite renvoyer la chaîne à partir de la procédure stockée et la convertir en un type de données numérique dans SQL.

Traitement des erreurs JavaScript¶

Dans la mesure où une procédure stockée est écrite en JavaScript, elle peut utiliser la syntaxe try/catch de JavaScript.

La procédure stockée peut générer une exception prédéfinie ou une exception personnalisée. Un exemple simple de levée d’une exception personnalisée est disponible ici.

Vous pouvez exécuter vos instructions SQL dans un bloc d’essai. Si une erreur se produit, votre bloc de saisie peut alors annuler toutes les instructions (si vous les mettez dans une transaction). La section Exemples contient un exemple d”annulation d’une transaction dans une procédure stockée.

Restrictions sur les procédures stockées¶

Les procédures stockées présentent les restrictions suivantes :

• Le code JavaScript ne peut pas appeler la fonction JavaScript eval() .

• Les procédures stockées JavaScript acceptent l’accès à la bibliothèque JavaScript standard. Notez que ceci exclut de nombreux objets et méthodes généralement fournis par les navigateurs. Il n’existe aucun mécanisme pour importer, inclure ou appeler des bibliothèques supplémentaires. Autoriser les bibliothèques tierces pourrait créer des failles de sécurité.

• Le code JavaScript est exécuté dans un moteur restreint, empêchant les appels système du contexte JavaScript (par exemple, pas d’accès au réseau et au disque) et limitant les ressources système disponibles pour le moteur, en particulier la mémoire.

Sensibilité à la casse dans les arguments JavaScript¶

Les noms d’arguments ne sont pas sensibles à la casse dans la partie SQL du code de procédure stockée, mais le sont dans la partie JavaScript.

Pour les procédures stockées (et les UDFs) qui utilisent JavaScript, les identificateurs (tels que les noms d’arguments) dans la partie SQL de l’instruction sont automatiquement convertis en majuscules (sauf si vous délimitez l’identificateur par des guillemets), tandis que les noms d’arguments dans la partie JavaScript seront laissés dans leur casse d’origine. Cela peut entraîner l’échec de votre procédure stockée sans retourner de message d’erreur explicite, car les arguments ne sont pas vus.

Voici un exemple de procédure stockée dans laquelle le nom d’un argument dans le code JavaScript ne correspond pas à celui de l’argument dans le code SQL simplement parce que la casse sera différente :

Dans l’exemple ci-dessous, la première instruction d’affectation est incorrecte, car le nom argument1 est en minuscule.

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

L’utilisation d’identificateurs en majuscules (en particulier de noms d’arguments) de manière cohérente dans vos instructions SQL et votre code JavaScript tend à réduire les erreurs silencieuses.

JavaScript Délimiteurs¶

La partie JavaScript du code de procédure stockée doit être encadrée par des guillemets simples ' ou par des signes à deux dollars $$ .

L’utilisation de $$ facilite la gestion du code JavaScript contenant des guillemets simples sans « échapper » ces guillemets.

Variables de liaison¶

La liaison d’une variable à une instruction SQL vous permet d’utiliser la valeur de la variable dans l’instruction.

Vous pouvez lier des valeurs NULL ainsi que des valeurs non NULL.

Le type de données de la variable doit convenir à l’utilisation de la valeur dans l’instruction SQL . Actuellement, seules les variables JavaScript de type nombre, chaîne et SfDate peuvent être liées. (Pour plus d’informations sur le mappage entre les types de données SQL et les types de données JavaScript, voir Mappage de type de données SQL et JavaScript .)

Voici un bref exemple de liaison :

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

Voici un exemple plus complet. Cet exemple lie des informations TIMESTAMP (horodatage). La liaison directe des données SQL TIMESTAMP n’étant pas prise en charge, cet exemple transmet l’horodatage sous la forme d’un VARCHAR, puis la lie à l’instruction. Notez que l’instruction SQL elle-même convertit le VARCHAR en TIMESTAMP en appelant la fonction TO_TIMESTAMP() :

Cette fonction simple renvoie TRUE si l’horodatage spécifié est antérieur à maintenant et FALSE dans le cas contraire.

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

CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+

Cela montre comment lier un VARCHAR, un TIMESTAMP_LTZ et d’autres types de données à une instruction INSERT . Le TIMESTAMP_LTZ lie une variable SfDate créée dans la procédure stockée.

Créez une table.

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

Créez une procédure stockée. Cette procédure accepte un VARCHAR et convertit le VARCHAR en TIMESTAMP_LTZ à l’aide de SQL. La procédure récupère ensuite la valeur convertie d’un ResultSet. La valeur est stockée dans une variable JavaScript de type SfDate. La procédure stockée lie ensuite le VARCHAR d’origine et le TIMESTAMP_LTZ à une instruction INSERT. Cela illustre également la liaison de données numériques 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; 
$$ ; 

Appelez la procédure.

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

Vérifiez que la ligne a été insérée.

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

Pour des exemples supplémentaires de liaison de données dans JavaScript, voir Paramètres d’instruction de liaison.

Exigences en matière de code¶

Le code JavaScript doit définir un seul objet JavaScript littéral pour que la procédure stockée soit valide.

Si le code JavaScript ne répond pas à cette exigence, la procédure stockée sera créée. Cependant, elle échouera à l’appel.

Taille du code¶

Snowflake limite la taille maximale du code source JavaScript dans le corps d’une procédure stockée JavaScript. Snowflake recommande de limiter la taille à 100 KB. (Le code est stocké sous une forme compressée et la limite exacte dépend de la capacité de compression du code).

Erreurs d’exécution¶

La plupart des erreurs dans les procédures stockées s’affichent au moment de l’exécution, car le code JavaScript est interprété au moment de l’exécution de la procédure stockée plutôt qu’à la création de la procédure stockée.

Prise en charge de SQL dynamique¶

Les procédures stockées peuvent être utilisées pour construire dynamiquement des instructions SQL. Par exemple, vous pouvez créer une chaîne de commande SQL contenant un mélange d’entrées SQLpréconfigurées et d’utilisateurs (par exemple, le numéro de compte de l’utilisateur).

Pour des exemples, voir Création dynamique d’une instruction SQL et la section Exemples.

API synchrone¶

L’API pour les procédures stockées Snowflake est synchrone. Dans une procédure stockée, vous ne pouvez exécuter qu’un seul thread à la fois.

Notez que cela diffère de la règle pour JavaScript qui s’exécute avec le connecteur Node.js, qui vous permet d’exécuter des threads asynchrones.

Exemples de base¶

L’exemple suivant montre la syntaxe de base de la création et de l’appel d’une procédure stockée. Il n’exécute aucun code SQL ni code de procédure. Cependant, il fournit un point de départ pour des exemples plus réalistes plus tard :

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

Notez que le délimiteur $$ marque le début et la fin du code JavaScript.

Appelez maintenant la procédure que vous venez de créer :

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

L’exemple suivant montre comment exécuter une instruction SQL dans une procédure stockée :

1. Créez une table :

   CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));
   

2. Créez une procédure stockée. Cela insère une ligne dans une table existante nommée stproc_test_table1 et renvoie la valeur « Réussie ». La valeur renvoyée n’est pas particulièrement utile du point de vue SQL, mais elle vous permet de renvoyer des informations sur le statut (par exemple, « Réussie » ou « Échouée ») à l’utilisateur.

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

3. Appelez la procédure stockée :

   call stproc1(5.14::FLOAT);
   +------------+
   | STPROC1    |
   |------------|
   | Succeeded. |
   +------------+
   

4. Confirmez que la procédure stockée a inséré la ligne :

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

L’exemple suivant récupère un résultat :

1. Créez une procédure pour compter le nombre de lignes dans une table (équivalent à 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;
     $$
     ;
   

2. Demandez à la procédure stockée combien de lignes se trouvent dans la table :

   call get_row_count('stproc_test_table1');
   +---------------+
   | GET_ROW_COUNT |
   |---------------|
   |             3 |
   +---------------+
   

3. Vérifiez indépendamment que vous avez le bon numéro :

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

Exemple de procédure stockée récursive¶

L’exemple suivant illustre une procédure stockée récursive de base, mais pas particulièrement réaliste :

create or replace table stproc_test_table2 (col1 FLOAT);

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

call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+

SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+

Création dynamique d’une instruction SQL¶

L’exemple suivant montre comment créer dynamiquement une instruction SQL :

1. Créez la procédure stockée. Cette procédure vous permet de transmettre le nom d’une table et d’obtenir le nombre de lignes de cette table (équivalent à 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;
       $$
       ;
   

2. Appelez la procédure stockée :

   call get_row_count('stproc_test_table1');
   +---------------+
   | GET_ROW_COUNT |
   |---------------|
   |             3 |
   +---------------+
   

3. Affichez les résultats de select count(*) pour la même table :

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

Récupérer les métadonnées du jeu de résultats¶

Cet exemple illustre la récupération d’une petite quantité de métadonnées à partir d’un ensemble de résultats :

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

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

call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+

call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Intercepter une erreur en utilisant Try/Catch¶

Cet exemple montre comment utiliser un bloc Try/Catch JavaScript pour intercepter une erreur dans une procédure stockée :

1. Créez la procédure stockée :

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

2. Appelez la procédure stockée. cela devrait renvoyer une erreur indiquant le numéro de l’erreur et d’autres informations :

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

L’exemple suivant montre la levée d’une exception personnalisée :

1. Créez la procédure stockée :

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

2. Appelez la procédure stockée avec des valeurs valides et non valides :

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

Utilisation des transactions dans les procédures stockées¶

L’exemple suivant englobe plusieurs instructions liées dans une transaction et utilise la fonction « try/catch » pour valider ou annuler. Le paramètre force_failure permet à l’appelant de choisir entre une exécution réussie et une erreur délibérée.

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

Consignation d’une erreur¶

L’exemple suivant enregistre les erreurs dans une table. Dans un exemple plus réaliste, vous devriez probablement également inclure l’horodatage de l’heure à laquelle l’erreur s’est produite (à l’aide de la fonction CURRENT_TIMESTAMP).

CREATE OR REPLACE TABLE error_log (error_code number, error_state string, error_message string, stack_trace string);

CREATE OR REPLACE PROCEDURE broken() 
RETURNS varchar 
NOT NULL 
LANGUAGE javascript 
AS $$
var result;
try {
    snowflake.execute({ sqlText: "Invalid Command!;" });
    result = "Succeeded";
} catch (err) {
    result = "Failed";
    snowflake.execute({
      sqlText: `insert into error_log VALUES (?,?,?,?)`
      ,binds: [err.code, err.state, err.message, err.stackTraceTxt]
      });
}
return result;
$$;

call broken();
+--------+
| BROKEN |
|--------|
| Failed |
+--------+

select * from error_log;
+------------+-------------+---------------------------------------------------------+---------------------------------------+
| ERROR_CODE | ERROR_STATE | ERROR_MESSAGE                                           | STACK_TRACE                           |
|------------+-------------+---------------------------------------------------------+---------------------------------------|
|       1003 | 42000       | SQL compilation error:                                  | Snowflake.execute, line 4 position 14 |
|            |             | syntax error line 1 at position 0 unexpected 'Invalid'. |                                       |
+------------+-------------+---------------------------------------------------------+---------------------------------------+

Consignation d’une erreur (version 2)¶

Cela montre comment utiliser une procédure stockée pour consigner des messages dans une table temporaire. Cet exemple permet à l’appelant de spécifier le nom de la table de journal et de créer la table si elle n’existe pas déjà. Cet exemple permet également à l’appelant d’activer et de désactiver facilement la journalisation.

Notez également que l’une de ces procédures stockées crée une petite fonction JavaScript qu’elle peut réutiliser. Dans les longues procédures stockées avec code répétitif, la création de fonctions JavaScript dans la procédure stockée peut être pratique.

Créez les procédures :

CREATE or replace PROCEDURE do_log(MSG STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
 
    // See if we should log - checks for session variable do_log = true.
    try {
       var stmt = snowflake.createStatement( { sqlText: `select $do_log` } ).execute();
    } catch (ERROR){
       return ''; //swallow the error, variable not set so don't log
    }
    stmt.next();
    if (stmt.getColumnValue(1)==true){ //if the value is anything other than true, don't log
       try {
           snowflake.createStatement( { sqlText: `create temp table identifier ($log_table) if not exists (ts number, msg string)`} ).execute();
           snowflake.createStatement( { sqlText: `insert into identifier ($log_table) values (:1, :2)`, binds:[Date.now(), MSG] } ).execute();
       } catch (ERROR){
           throw ERROR;
       }
    }
 $$
;

CREATE or replace PROCEDURE my_test()
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$

    var accumulated_log_messages = '';

    // Define the SP call as a function - it's cleaner this way.
    // Add this function to your stored procs.
    function log(msg) {
        snowflake.createStatement( { sqlText: `call do_log(:1)`, binds:[msg] } ).execute();
        }

    try {
        accumulated_log_messages += 'log this message\n';
        var x = 10/10;
        accumulated_log_messages += 'x = ' + x.toString() + '\n';
        //do some stuff here
        x = no_such_function();   // Force an error so that we catch it.
    } catch(ERROR) {
        log(accumulated_log_messages);
        log(ERROR.message); //we can even catch/log the error messages
        return ERROR.message;
    }

    if (accumulated_log_messages != '') {
        log(accumulated_log_messages)
        }

    return 'No error detected.';

    $$
;

Activez la journalisation :

set do_log = true; --true to enable logging, false (or undefined) to disable
set log_table = 'my_log_table';  -- The name of the temp table where log messages go.

Appelez la procédure :

CALL my_test();
+---------------------------------+
| MY_TEST                         |
|---------------------------------|
| no_such_function is not defined |
+---------------------------------+

Vérifiez que la table a été créée et que les messages ont été consignés :

select msg 
    from my_log_table 
    order by 1;
+---------------------------------+
| MSG                             |
|---------------------------------|
| log this message                |
| x = 1                           |
|                                 |
| no_such_function is not defined |
+---------------------------------+

Surcharge de noms de procédures stockées¶

Comme décrit dans Conventions de dénomination pour les procédures stockées (dans ce chapitre), vous pouvez surcharger les noms de procédure stockée. Par exemple :

1. Commencez par créer deux procédures stockées portant les mêmes noms, mais avec un nombre d’arguments différent.

   create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
       returns string
       language javascript
       strict
       as
       $$
       return FLOAT_PARAM1;
       $$
       ;
   

   create or replace procedure stproc1(FLOAT_PARAM1 FLOAT, FLOAT_PARAM2 FLOAT)
       returns string
       language javascript
       strict
       as
       $$
       return FLOAT_PARAM1 * FLOAT_PARAM2;
       $$
       ;
   

2. Ensuite, appelez les deux procédures :

   call stproc1(5.14::FLOAT);
   +---------+
   | STPROC1 |
   |---------|
   | 5.14    |
   +---------+
   

   call stproc1(5.14::FLOAT, 2.00::FLOAT);
   +---------+
   | STPROC1 |
   |---------|
   | 10.28   |
   +---------+
   

L’exemple suivant de surcharge montre comment la combinaison de la surcharge et de la conversion de type automatique peut favoriser l’obtention de résultats inattendus :

Créez une procédure stockée qui prend un paramètre FLOAT :

CREATE PROCEDURE add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + 3.1415926;
    $$;

Appelez la procédure stockée deux fois. La première fois, transmettez un FLOAT. La deuxième fois, transmettez un VARCHAR. Le VARCHAR est converti en un FLOAT et la sortie de chaque appel est identique :

CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+

Maintenant, créez une procédure stockée surchargée qui prend un paramètre VARCHAR :

CREATE PROCEDURE add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + '3.1415926';
    $$;

Utilisez maintenant exactement les mêmes CALLs qu’avant. Notez la différence de sortie entre ces deux CALLs et les deux CALLs précédents.

CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+--------------+
| ADD_PI       |
|--------------|
| 1.03.1415926 |
+--------------+

Utilisation de RESULT_SCAN pour récupérer le résultat d’une procédure stockée¶

Cet exemple montre comment utiliser la fonction RESULT_SCAN pour récupérer le résultat d’une instruction CALL :

1. Créez et chargez la table :

   CREATE TABLE western_provinces(ID INT, province VARCHAR);
   

   INSERT INTO western_provinces(ID, province) VALUES
       (1, 'Alberta'),
       (2, 'British Columbia'),
       (3, 'Manitoba')
       ;
   

2. Créez la procédure stockée. Cette procédure retourne une chaîne bien formatée qui ressemble à un jeu de résultats de trois lignes, mais qui est en réalité une seule chaîne :

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

3. Appelez la procédure stockée, puis récupérez les résultats en utilisant 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            |
   +------------------------+
   

Vous pouvez effectuer des opérations plus complexes sur la valeur renvoyée par la fonction RESULT_SCAN. Dans ce cas, étant donné que la valeur renvoyée est une chaîne unique, vous pouvez extraire les « lignes » individuelles qui semblent être contenues dans cette chaîne et stocker ces lignes dans une autre table.

L’exemple suivant, qui est la suite de l’exemple précédent, illustre une façon de procéder :

1. Créez une table pour le stockage à long terme. Cette table contient le nom de la province et l’ID de province après les avoir extraits de la chaîne renvoyée par la commande CALL :

   CREATE TABLE all_provinces(ID INT, province VARCHAR);
   

2. Appelez la procédure stockée, puis récupérez le résultat à l’aide de RESULT_SCAN, puis extrayez les trois lignes de la chaîne et placez-les dans la table :

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

3. Vérifiez que cela a fonctionné en affichant les lignes dans la table :

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

Voici à peu près le même code, mais par petites étapes :

1. Créez une table nommée one_string. Cette table stocke temporairement le résultat de la commande CALL. Le résultat de CALL est une chaîne unique, donc cette table ne stocke qu’une seule valeur VARCHAR.

   CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
   

2. Appelez la procédure stockée, puis récupérez le résultat (une chaîne) en utilisant RESULT_SCAN, puis enregistrez-le dans la table intermédiaire nommée 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 |
   +-------------------------+
   

   Cela montre la nouvelle ligne de la table one_string. Rappelez-vous que bien que ceci soit formaté pour ressembler à trois lignes, il s’agit en fait d’une seule chaîne :

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

   Les commandes suivantes montrent comment extraire plusieurs lignes de la chaîne :

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

3. Ensuite, créez une table nommée three_strings. Cette table contiendra le résultat après l’avoir divisé en lignes/chaînes individuelles :

   CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
   

4. Convertissez maintenant cette chaîne de la table one_string en trois chaînes distinctes et montrez qu’il s’agit désormais de trois chaînes :

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

5. Convertissez maintenant les trois chaînes en trois lignes dans notre table à long terme nommée 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 |
   +-------------------------+
   

6. Affichez les trois lignes de la table à long terme :

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

Renvoi d’un tableau de messages d’erreur¶

Votre procédure stockée peut exécuter plusieurs instructions SQL et vous pouvez renvoyer un message d’état/d’erreur pour chaque instruction SQL. Cependant, une procédure stockée retourne une seule ligne ; elle n’est pas conçue pour renvoyer plusieurs lignes.

Si tous vos messages entrent dans une seule valeur de type ARRAY, vous pouvez obtenir tous les messages d’une procédure stockée avec un effort supplémentaire.

L’exemple suivant montre une façon de procéder (les messages d’erreur affichés ne sont pas réels, mais vous pouvez étendre ce code pour qu’il fonctionne avec vos instructions SQL réelles) :

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

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." |
+-------+---------------------------------------------+

Rappelez-vous que ceci n’est pas une solution à usage général. La taille maximale des types de données ARRAY est limitée et l’intégralité de votre jeu de résultats doit correspondre à un seul ARRAY.

Renvoi d’un jeu de résultats¶

Cette section prolonge l’exemple précédent décrit dans Renvoi d’un tableau de messages d’erreur. Cet exemple est plus général et vous permet de renvoyer un jeu de résultats à partir d’une requête.

Une procédure stockée renvoie une seule ligne contenant une seule colonne. Elle n’est pas conçue pour renvoyer un jeu de résultats. Toutefois, si votre jeu de résultats est suffisamment petit pour tenir dans une valeur unique de type VARIANT ou ARRAY, vous pouvez renvoyer un jeu de résultats à partir d’une procédure stockée avec du code supplémentaire :

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

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

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" |
+-------+-----------+

Cela montre comment combiner les deux lignes précédentes en une seule ligne :

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" |
+-------+-----------+

Pour plus de commodité, vous pouvez insérer la ligne précédente dans une vue. Cette vue convertit également la chaîne “null” en une vraie valeur NULL. Vous devez seulement créer la vue une fois. Toutefois, vous devez appeler la procédure stockée immédiatement avant d’effectuer des sélections dans cette vue à chaque utilisation de la vue. N’oubliez pas que l’appel à RESULT_SCAN dans la vue est extrait de l’instruction la plus récente, qui doit être 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);

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

Vous pouvez même l’utiliser comme une vue vraie (c.-à-d. en sélectionnant un sous-ensemble) :

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

Rappelez-vous que ceci n’est pas une solution à usage général. La taille maximale des types de données VARIANT et ARRAY est limitée et l’intégralité de votre jeu de résultats doit correspondre à une seule VARIANT ou ARRAY.

Protection de la vie privée¶

Cet exemple montre une procédure stockée utile pour un détaillant en ligne. Cette procédure stockée respecte la vie privée des clients, tout en protégeant les intérêts légitimes du détaillant et du client. Si un client demande au détaillant de supprimer ses données pour des raisons de confidentialité, cette procédure stockée supprime la plupart des données du client, mais conserve l’historique des achats du client si l’une des conditions suivantes est vraie :

• Tout article acheté a une garantie qui n’a pas encore expiré.

• Le client doit encore de l’argent (ou le client doit être remboursé).

Une version plus réaliste de ce système supprimerait les lignes individuelles pour lesquelles le paiement a été effectué et la garantie a expiré.

1. Commencez par créer les tables et les charger :

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

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

2. Créez la procédure stockée :

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

3. Affichez les données dans les tables avant de supprimer l’une de ces données :

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

4. Le client #1 a une garantie qui est toujours en vigueur. La procédure stockée supprime les commentaires de révision qu’il a publiés, mais conserve son enregistrement d’achat en raison de la garantie :

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

5. Le client #2 doit encore de l’argent. La procédure stockée supprime ses commentaires de révision, mais conserve son enregistrement d’achat :

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

6. Le client #3 ne doit pas d’argent (et ne lui doit pas d’argent). Sa garantie a expiré, donc la procédure stockée supprime à la fois les commentaires de révision et les enregistrements d’achat :

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

Utilisation de variables de session avec les procédures stockées des droits de l’appelant et des droits du propriétaire¶

Ces exemples illustrent l’une des principales différences entre les procédures stockées relatives aux droits de l’appelant et aux droits du propriétaire. Ils tentent d’utiliser les variables de session de deux manières :

• Définissez une variable de session avant d’appeler la procédure stockée, puis utilisez la variable de session à l’intérieur de la procédure stockée.

• Définissez une variable de session dans la procédure stockée, puis utilisez-la après un renvoi depuis les procédures stockées.

L’utilisation de la variable de session et la définition de la variable de session fonctionnent correctement dans la procédure stockée des droits de l’appelant. Les deux échouent lors de l’utilisation d’une procédure stockée avec les droits du propriétaire, même si l’appelant est le propriétaire.

Procédure stockée ou UDF renvoie NULL de manière inattendue¶

Cause

Votre procédure stockée/UDF a un paramètre. À l’intérieur de la procédure/UDF, le paramètre est désigné par son nom en minuscule, mais Snowflake a automatiquement converti le nom en majuscule.

Solution

Deux possibilités :

• Utilisez la majuscule pour le nom de la variable dans le code JavaScript, ou

• Placez le nom de la variable entre guillemets dans le code SQL.

Pour plus de détails, voir Arguments JavaScript et valeurs retournées.

La procédure stockée ne termine jamais son exécution¶

Cause

Vous pourriez avoir une boucle infinie dans votre code JavaScript.

Solution

Recherchez cela et corrigez les boucles infinies.

Erreur : Failed: empty argument passed¶

Cause

Votre procédure stockée peut contenir « sqltext » alors qu’elle devrait avoir « sqlText » (le premier est en minuscule ; le second est en casse mixte).

Solution

Utilisez « sqlText ».

Erreur : JavaScript out of memory error: UDF thread memory limit exceeded¶

Cause

Vous pourriez avoir une boucle infinie dans votre code JavaScript.

Solution

Recherchez cela et corrigez les boucles infinies. En particulier, veillez à ne plus appeler la prochaine ligne lorsque l’ensemble de résultats est épuisé (c’est-à-dire lorsque resultSet.next() renvoie false).