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

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

  Copy

• 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;`;
  

  Copy

• 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;"
  

  Copy

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

Copy

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

Copy

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.

Délimiteurs JavaScript¶

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.

Surcharge de noms de procédures stockées¶

Pour plus d’informations sur la surcharge et les conventions de dénomination, voir Nommage et surcharge de procédures et d’UDFs.

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

   Copy

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

   Copy

3. Appelez la procédure stockée :

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

   Copy

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

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

   Copy

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

   Copy

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

   Copy

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

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

   Copy

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

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

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

   Copy

2. Appelez la procédure stockée :

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

   Copy

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

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

   Copy

Récupération des 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 */
    );

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

Interception d’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;
         $$
         ;
   

   Copy

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

   Copy

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

   Copy

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

   Copy

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¶

Vous pouvez capturer des données de journal et de trace à partir du code du gestionnaire JavaScript en utilisant l’objet snowflake dans l’API JavaScript. Dans ce cas, les messages du journal et les données de trace sont stockés dans une table d’événements que vous pouvez analyser à l’aide de requêtes.

Pour plus d’informations, reportez-vous à ce qui suit :

• Enregistrement des messages en JavaScript

• Émettre des événements de trace en JavaScript

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éer et charger la table :

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

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

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

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

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

Copy

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

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

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

Copy

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

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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