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

Cette rubrique explique comment écrire du code JavaScript pour une procédure stockée.

Note

Pour créer et appeler une procédure anonyme, utilisez CALL (avec procédure anonyme). La création et l’appel d’une procédure anonyme ne nécessitent pas un rôle avec des privilèges de schéma CREATE PROCEDURE.

Vous pouvez capturer des données d’enregistrement et de trace pendant l’exécution du code de votre gestionnaire. Pour plus d’informations, reportez-vous à Journalisation, traçage et métriques.

Comprendre l’API JavaScript¶

Cette API JavaScript est similaire aux APIs des connecteurs et des pilotes Snowflake (Node.js, JDBC, Python, etc.), sans être identique.

L’API vous permet d’effectuer des opérations telles que :

Exécuter une instruction SQL.
Récupérer les résultats d’une requête (c’est-à-dire un jeu de résultats).
Récupérer les métadonnées relatives au jeu de résultats (nombre de colonnes, types de données des colonnes, etc.).

Ces opérations sont effectuées en appelant des méthodes sur les objets suivants :

snowflake, qui dispose de méthodes pour créer un objet Statement et exécuter une commande SQL.
Statement qui vous aide à exécuter des instructions préparées et à accéder aux métadonnées de ces instructions préparées, et vous permet de récupérer un objet ResultSet.
ResultSet, qui contient les résultats d’une requête (par exemple, les lignes de données extraites pour une instruction SELECT).
SfDate, qui est une extension de JavaScript Date (avec des méthodes supplémentaires) et sert de type de retour pour les types de données SQL de Snowflake TIMESTAMP_LTZ, TIMESTAMP_NTZ et TIMESTAMP_TZ.

Ces objets sont décrits en détail dans API de procédures stockées JavaScript.

Une procédure stockée typique contient un code similaire au pseudo-code suivant :

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

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

Ce code utilise un objet nommé snowflake, qui est un objet spécial qui existe sans être déclaré. L’objet est fourni dans le contexte de chaque procédure stockée et expose l’API pour vous permettre d’interagir avec le serveur.

Les autres variables (par exemple statement1) sont créées avec des instructions JavaScript var. Par exemple :

var statement1 = ...;

Comme illustré dans l’exemple de code ci-dessus, l’objet snowflake vous permet de créer un objet Statement en appelant l’une des méthodes dans l’API.

Voici un exemple qui récupère un ResultSet et le répète à travers celui-ci :

CREATE OR REPLACE PROCEDURE read_result_set()
  RETURNS FLOAT NOT NULL
  LANGUAGE JAVASCRIPT
  AS     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

La section Exemples (à la fin de cette rubrique) fournit des exemples supplémentaires permettant d’exercer chacun des objets, ainsi que de nombreuses méthodes, dans l’API JavaScript de procédure stockée.

Mappage de type de données SQL et JavaScript¶

Lorsque vous appelez, utilisez et récupérez des valeurs à partir de procédures stockées, vous devez souvent convertir un type de données SQL Snowflake en un type de données JavaScript, ou inversement.

La conversion SQL > JavaScript peut avoir lieu dans les situations suivantes :

Appel d’une procédure stockée avec un argument. L’argument est un type de données SQL ; lorsqu’il est stocké dans une variable JavaScript de la procédure stockée, il doit être converti.
Lors de la récupération d’une valeur d’un objet ResultSet dans une variable JavaScript. Le ResultSet contient la valeur sous la forme d’un type de données SQL et la variable JavaScript doit stocker la valeur sous l’un des types de données JavaScript.

La conversion JavaScript > SQL peut avoir lieu dans les situations suivantes :

Renvoi d’une valeur depuis la procédure stockée. L’instruction return contient généralement une variable JavaScript qui doit être convertie en un type de données SQL.
Lors de la construction dynamique d’une instruction SQL utilisant une valeur dans une variable JavaScript.
Lors de la liaison de la valeur d’une variable JavaScript à une instruction préparée.

Pour plus d’informations sur la façon dont Snowflake fait correspondre les types de données JavaScript et SQL, voir Mappages de type de données SQL-JavaScript.

Conseils généraux¶

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

Considérations relatives aux procédures stockées JavaScript¶

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.

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

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¶

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 :

Créez une table :

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

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

Appelez la procédure stockée :

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

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 :

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

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

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 :

Note

Comme indiqué dans injection SQL (dans ce chapitre), veillez à vous protéger contre les attaques lorsque vous utilisez du SQL dynamique.

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

Appelez la procédure stockée :

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

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

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

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

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

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 :

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

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 :

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

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¶

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 :

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

L’exemple suivant vous montre comment utiliser la fonction RESULT_SCAN pour récupérer et traiter le résultat d’une instruction CALL :

Créer et charger la table :

CREATE TABLE western_provinces(ID INT, province VARCHAR);

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

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

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.

Astuce

Vous pouvez également utiliser l”opérateur de canal (->>) au lieu de la fonction RESULT_SCAN pour exécuter une instruction CALL et traiter son jeu de résultats avec une seule commande.

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

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

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

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 :

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

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

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

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

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

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

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

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 should be deleted.
        // ...
        result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
        }
    return result;
    $$
    ;

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

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

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

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 avec droits de l’appelant¶

L’exemple suivant illustre une procédure stockée avec droits de l’appelant.

Créer et charger une table :

create table sv_table (f float);
insert into sv_table (f) values (49), (51);

Définir une variable de session :
set SESSION_VAR1 = 50;

Créer une procédure stockée de droits d’un appelant qui utilise une variable de session et en définit une autre :

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

Appeler la procédure :

CALL session_var_user();
+------------------+
| SESSION_VAR_USER |
|------------------|
|               51 |
+------------------+

Afficher la valeur de la variable de session définie dans la procédure stockée :

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

Note

Bien que vous puissiez définir une variable de session dans une procédure stockée et la conserver après la fin de la procédure, Snowflake déconseille d’agir ainsi.

Procédure stockée avec droits du propriétaire¶

L’exemple suivant montre la procédure stockée avec droits du propriétaire.

Créez une procédure stockée avec droits du propriétaire qui utilise une variable de session :

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

Appelez la procédure (elle devrait échouer) :
CALL cannot_use_session_vars();

Créez une procédure stockée de droits du propriétaire qui tente de définir une variable de session :

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

Appelez la procédure (elle devrait échouer) :
CALL cannot_set_session_vars();

Résolution des problèmes¶

Une technique de dépannage générale consiste à utiliser un bloc Try/Catch JavaScript pour intercepter l’erreur et afficher les informations sur l’erreur. L’objet d’erreur contient :

Code d’erreur.
Message d’erreur.
État d’erreur.
Trace de pile au point d’échec.

Pour plus d’informations, y compris un exemple d’utilisation de ces informations, voir Interception d’une erreur en utilisant Try/Catch (dans cette rubrique).

Les sections suivantes fournissent des suggestions supplémentaires pour aider à résoudre des problèmes spécifiques.

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

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

Comprendre l’API JavaScript¶

Mappage de type de données SQL et JavaScript¶

Conseils généraux¶

Continuation de la facturation¶

Considérations relatives aux procédures stockées JavaScript¶

Plage de nombres JavaScript¶

Traitement des erreurs JavaScript¶

Restrictions sur les procédures stockées¶

Sensibilité à la casse dans les arguments JavaScript¶

Délimiteurs JavaScript¶

Surcharge de noms de procédures stockées¶

Variables de liaison¶

Exigences en matière de code¶

Taille du code¶

Erreurs d’exécution¶

Prise en charge de SQL dynamique¶

API synchrone¶

Exemples¶

Exemples de base¶

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

Création dynamique d’une instruction SQL¶

Récupération des métadonnées du jeu de résultats¶

Interception d’une erreur en utilisant Try/Catch¶

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

Consignation d’une erreur¶

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

Renvoi d’un tableau de messages d’erreur¶

Renvoi d’un jeu de résultats¶

Protection de la vie privée¶

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

Procédure stockée avec droits de l’appelant¶

Procédure stockée avec droits du propriétaire¶

Résolution des problèmes¶

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

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

Erreur : Failed: empty argument passed¶

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

Erreur : `Failed: empty argument passed`¶

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