Travailler avec des procédures stockées

Les procédures stockées permettent aux utilisateurs de créer du code modulaire pouvant inclure une logique métier complexe en combinant plusieurs instructions SQL avec une logique procédurale.

Dans ce chapitre :

Création d’une procédure stockée

DDL de procédure stockée

Les procédures stockées sont des objets de base de données de première classe. Les commandes DDL suivantes s’appliquent aux procédures stockées :

En outre, Snowflake fournit la commande suivante pour exécuter des procédures stockées :

Mise en œuvre et API

Les procédures stockées Snowflake sont écrites en JavaScript, qui peuvent exécuter des instructions SQL en appelant une API JavaScript. Cette API 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 Procédures stockées - API.

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.

Les sections ci-dessous expliquent comment les données sont converties de SQL à JavaScript ou de JavaScript à SQL.

Conversion de SQL > JavaScript

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

Type de données SQL

Type de données JavaScript

Remarques

ARRAY

JSON

BOOLEAN

boolean

DATE

date

REAL, FLOAT, FLOAT8, FLOAT4, DOUBLE, DOUBLE PRECISION

number

TIME

string

TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ

date ou SfDate

Lorsqu’un horodatage est transmis en tant qu’argument à une procédure stockée, l’horodatage est converti en objet JavaScript date. Dans d’autres situations (par exemple, lors de la récupération de ResultSet), un horodatage est converti en un objet SfDate. Pour plus de détails sur le type de données SfDate, qui n’est pas un type de données standard JavaScript, reportez-vous à la section Procédures stockées - API.

VARCHAR, CHAR, CHARACTER, STRING, TEXT

string

VARIANT

JSON

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

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

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

Type de données SQL incompatible

Type de données SQL incompatible

INTEGER

FLOAT

NUMBER, NUMERIC, DECIMAL

FLOAT

BINARY

Uint8Array

OBJECT

Uint8Array

Conversion de JavaScript > SQL

Lors du renvoi de valeurs

Le type de retour d’une procédure stockée est déclaré dans la définition de procédure stockée. Si l’instruction return dans JavaScript renvoie un type de données différent du type de retour déclaré de la procédure stockée, la valeur JavaScript est, si possible, convertie en type de données SQL. Par exemple, si un nombre est renvoyé, mais que la procédure stockée est déclarée comme renvoyant une chaîne, le nombre est converti en chaîne au sein de JavaScript, puis copié dans la chaîne renvoyée dans l’instruction SQL. (Gardez à l’esprit que les erreurs de programmation JavaScript, comme le retour du type de données incorrect, peuvent être masquées par ce comportement.)

Si aucune conversion valide pour la conversion n’existe, une erreur se produit.

Lors de la liaison de valeurs

Lorsque vous liez des variables JavaScript à des instructions SQL, Snowflake convertit les types de données JavaScript en types de données SQL. Vous pouvez lier des variables des types de données JavaScript suivants :

  • nombre.

  • chaîne

  • SfDate. (Pour plus de détails sur le type de données SfDate, qui n’est pas un type de données standard JavaScript, reportez-vous à la section Procédures stockées - API.)

Pour plus d’informations sur la liaison, avec quelques exemples, voir Variables de liaison.

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

Vous pouvez également trouver les sujets suivants utiles :

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

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

Conventions de dénomination pour les procédures stockées

Les procédures stockées sont des objets de base de données, c’est-à-dire qu’ils sont créés dans une base de données et un schéma spécifiés. À ce titre, ils ont un nom complet défini par leur espace de noms, sous la forme bd.schéma.nom_procédure, par exemple :

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();

Lorsqu’elles sont appelées sans leur nom complet, les procédures stockées sont résolues en fonction de la base de données et du schéma utilisés pour la session.

Surcharge de noms

Snowflake prend en charge la surcharge des noms de procédure stockée. Plusieurs procédures stockées dans le même schéma peuvent porter le même nom, tant que leurs signatures diffèrent, soit par le nombre d’arguments soit par leur type. Lorsqu’une procédure stockée surchargée est appelée, Snowflake vérifie les arguments et appelle la procédure stockée correcte.

Un exemple est inclus dans Surcharge de noms de procédures stockées.

Soyez prudent lorsque vous utilisez la surcharge. La combinaison de la conversion de type automatique et de la surcharge peut favoriser la situation dans laquelle des erreurs utilisateurs mineures provoqueraient des résultats inattendus. Pour un exemple, voir Surcharge de noms de procédures stockées.

Conflits potentiels avec les fonctions définies par le système et les fonctions définies par l’utilisateur

Les procédures stockées et les fonctions définies par l’utilisateur peuvent porter les mêmes noms si leurs nombres ou types de données d’arguments sont différents.

Cependant, Snowflake ne permet pas de créer des procédures stockées avec le même nom que les fonctions définies par le système.

Gestion des transactions

Une procédure stockée peut être appelée entièrement dans une transaction ou en dehors de toute transaction. Toutefois, les commandes de contrôle de transaction (BEGIN, COMMIT, ROLLBACK) ne sont pas autorisées dans une procédure stockée.

Le pseudo-code suivant montre une procédure stockée appelée entièrement à l’intérieur d’une transaction :

BEGIN;
W;
CALL MyProcedure();   -- executes X and Y
Z;
COMMIT;

Si une procédure stockée est appelée en dehors d’une transaction explicite, chaque instruction de la procédure stockée s’exécute en tant que transaction distincte.

N’oubliez pas que les instructions DDL (CREATE TABLE, etc.) provoquent un COMMIT implicite. De telles instructions ne doivent pas être utilisées dans une procédure stockée si la procédure est appelée dans une transaction. Par exemple, le pseudo-code suivant montre ce qu’il ne faut pas faire :

CREATE PROCEDURE do_not_call_inside_transaction...
AS
$$
    ...
    var stmt = snowflake.createStatement(
        {sqlText: "CREATE TABLE not_a_good_idea_here..."}
        );
    var rs = stmt.execute();
    ...
$$;

BEGIN;
...
CALL do_not_call_inside_transaction();   -- Tries to do an implicit commit due to DDL.
...
COMMIT;

La modification du paramètre AUTOCOMMIT dans une procédure stockée est interdite.

Conseils généraux

Code symétrique

Si vous maîtrisez la programmation en langage assembleur, l’analogie suivante peut vous être utile. En langage assembleur, les fonctions créent et annulent souvent leurs environnements de manière symétrique. Par exemple :

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

Vous voudrez peut-être utiliser cette approche dans vos procédures stockées :

  • Si une procédure stockée apporte des modifications temporaires à votre session, alors cette procédure doit annuler ces modifications avant de les renvoyer.

  • Si une procédure stockée utilise la gestion des exceptions ou la création de branches, ou une autre logique pouvant avoir une incidence sur les instructions affectées, vous devez nettoyer ce que vous avez créé, quelles que soient les branches que vous prenez lors d’un appel particulier.

Par exemple, votre code pourrait ressembler au pseudo-code présenté ci-dessous :

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

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

Appel d’une procédure stockée

Pour exécuter une procédure stockée, utilisez une instruction CALL. Par exemple :

call stproc1(5.14::FLOAT);

Chaque argument d’une procédure stockée peut être une expression générale :

CALL stproc1(2 * 5.14::FLOAT);

Un argument peut être une sous-requête :

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

Vous ne pouvez appeler qu’une procédure stockée par instruction CALL. Par exemple, l’instruction suivante échoue :

call proc1(1), proc2(2);                          -- Not allowed

En outre, vous ne pouvez pas utiliser une procédure stockée CALL dans le cadre d’une expression. Par exemple, toutes les instructions suivantes échouent :

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

Toutefois, dans une procédure stockée, la procédure stockée peut appeler une autre procédure stockée ou s’appeler elle-même de manière récursive. Un exemple est présenté dans la section Exemples (dans ce chapitre).

Prudence

Les appels imbriqués peuvent dépasser la profondeur de pile maximale autorisée. Soyez donc prudent lors de l’imbrication des appels, en particulier lors de l’utilisation de la récursivité.

Privilèges

Les procédures stockées utilisent deux types de privilèges :

  • Privilèges directement sur la procédure stockée elle-même.

  • Privilèges sur les objets de base de données (par exemple, les tables) auxquels la procédure stockée accède.

Privilèges sur les procédures stockées

Semblables à d’autres objets de base de données (tables, vues, UDFs, etc.), les procédures stockées appartiennent à un rôle et disposent de privilèges pouvant être accordés à d’autres rôles.

Actuellement, les privilèges suivants s’appliquent aux procédures stockées :

  • USAGE

  • OWNERSHIP

Pour qu’un rôle utilise une procédure stockée, le rôle doit être le propriétaire ou avoir obtenu le privilège USAGE sur la procédure stockée.

Privilèges sur les objets de base de données accessibles par la procédure stockée

Ce sujet est traité dans Présentation des procédures stockées des droits de l’appelant et des droits du propriétaire.

Considérations relatives aux procédures stockées

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.

Restrictions sur les procédures stockées

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

  • Actuellement, les commandes de contrôle des transactions (BEGIN, COMMIT, ROLLBACK) ne sont pas autorisées dans une procédure stockée. Cela signifie qu’une procédure stockée est exécutée entièrement dans une seule transaction (explicitement ou implicitement).

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

  • Les commandes JavaScript d’une procédure stockée ne peuvent pas importer de bibliothèques tierces. Autoriser les bibliothèques tierces pourrait créer des failles de sécurité.

  • Bien que les procédures stockées autorisent l’imbrication et la récursivité, la profondeur maximale actuelle de la pile est environ de 8 et peut être inférieure si des procédures stockées individuelles dans la chaîne d’appels consomment de grandes quantités de ressources.

  • Dans de rares cas, appeler trop de procédures stockées en même temps peut provoquer un blocage.

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
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

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

JavaScript Délimiteurs

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

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

Variables de liaison

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

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

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

Voici un bref exemple de liaison :

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

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

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

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

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

Créez une table.

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

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

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

    f = 3.1415926;

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

    return my_sfDate; 
$$ ; 

Appelez la procédure.

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

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

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

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

Exigences en matière de code

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

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

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.

Injection SQL

Les procédures stockées peuvent créer dynamiquement une instruction SQL et l’exécuter. Toutefois, cela peut permettre des attaques par injection SQL, en particulier si vous créez l’instruction SQL à l’aide d’une entrée provenant d’une source publique ou non approuvée.

Vous pouvez minimiser le risque d’attaques par injection SQL en liant des paramètres plutôt qu’en concaténant du texte. Pour un exemple de liaison de variables, voir Variables de liaison.

Si vous choisissez d’utiliser la concaténation, vérifiez soigneusement les entrées lors de la construction dynamique de SQL à l’aide des entrées de sources publiques. Vous pouvez également prendre d’autres précautions, telles que l’interrogation à l’aide d’un rôle disposant de privilèges limités (par exemple, un accès en lecture seule ou un accès uniquement à certaines tables ou vues).

Pour plus d’informations sur les attaques par injection SQL, voir Injection SQL (sur Wikipédia).

Conseils de conception pour les procédures stockées

Voici quelques conseils pour concevoir une procédure stockée :

  • De quelles ressources (par exemple, les tables) cette procédure stockée a-t-elle besoin ?

  • Quels sont les privilèges nécessaires ?

    Réfléchissez aux objets de base de données auxquels vous aurez accès, aux rôles qui exécuteront votre procédure stockée et aux privilèges dont ces rôles auront besoin.

    Si la procédure doit être une procédure stockée avec droits de l’appelant, vous pouvez créer un rôle pour exécuter cette procédure spécifique ou l’une des procédures associées à un groupe. Vous pouvez ensuite accorder les privilèges requis à ce rôle, puis attribuer ce rôle aux utilisateurs appropriés.

  • La procédure stockée doit-elle être exécutée avec les droits de l’appelant ou les droits du propriétaire ? Pour plus d’informations sur ce sujet, voir Présentation des procédures stockées des droits de l’appelant et des droits du propriétaire .

  • Comment la procédure doit-elle gérer les erreurs, par exemple, que doit faire la procédure si une table requise est manquante ou si un argument n’est pas valide ?

  • La procédure stockée doit-elle consigner ses activités ou ses erreurs, par exemple en écrivant dans une table de journaux ?

  • Voir également la discussion pour savoir quand utiliser une procédure stockée et quand utiliser une UDF : Choisir de créer une procédure stockée ou une UDF.

Documentation des procédures stockées

Les procédures stockées sont généralement écrites pour être réutilisées et souvent partagées. La documentation des procédures stockées peut faciliter la gestion et l’utilisation des procédures stockées.

Vous trouverez ci-dessous des recommandations générales sur la documentation des procédures stockées.

En règle générale, au moins deux publics souhaitent avoir des informations sur une procédure stockée :

  • Utilisateurs/appelants.

  • Programmeurs/auteurs.

Pour les utilisateurs (et les programmeurs), documentez chacun des éléments suivants :

  • Nom de la procédure stockée

  • « Emplacement » de la procédure stockée (base de données et schéma).

  • Objectif de la procédure stockée.

  • Nom, type de données et signification de chaque paramètre d’entrée.

  • Nom, type de données et signification de la valeur de retour. Si la valeur de retour est un type complexe, tel qu’un VARIANT contenant des sous-champs, documentez ces sous-champs.

  • Si la procédure stockée s’appuie sur des informations issues de son environnement, par exemple des variables de session ou des paramètres de session, documentez les noms, les objectifs et les valeurs valides de ceux-ci.

  • Erreurs renvoyées, exceptions levées, etc.

  • Rôles ou privilèges requis pour exécuter la procédure. (Pour plus d’informations à ce sujet, voir la discussion sur les rôles dans Conseils de conception pour les procédures stockées.)

  • Si la procédure stockée est une procédure de droits d’appelant ou une procédure de droits de propriétaire.

  • Toutes les conditions préalables, par exemple les tables qui doivent exister avant l’appel de la procédure.

  • Toutes les sorties (en dehors de la valeur de retour), par exemple les nouvelles tables créées.

  • Tous les « effets de bord », par exemple les modifications de privilèges, la suppression d’anciennes données, etc. La plupart des procédures stockées (contrairement aux fonctions) sont appelées spécifiquement pour leurs effets de bord, et non pour leurs valeurs de retour. Assurez-vous donc de documenter ces effets.

  • Si un nettoyage est requis après l’exécution de la procédure stockée, documentez-le.

  • Si la procédure peut être appelée dans le cadre d’une transaction comportant plusieurs instructions (avec AUTOCOMMIT=FALSE) ou si elle doit être exécutée en dehors d’une transaction (avec AUTOCOMMIT=TRUE).

  • Un exemple d’appel et un exemple de ce qui est retourné.

  • Limitations (le cas échéant). Par exemple, supposons que la procédure lise une table et renvoie un VARIANT contenant des informations sur chaque ligne de la table. Il est possible que le VARIANT dépasse la taille maximale autorisée d’un VARIANT. Vous devrez donc peut-être donner à l’appelant une idée du nombre maximal de lignes de la table auxquelles la procédure accède.

  • Avertissements (le cas échéant).

  • Astuces de dépannage.

Pour les programmeurs :

  • Le ou les auteurs.

  • Expliquez pourquoi la procédure a été créée en tant que procédure de droits de l’appelant ou de droits du propriétaire. La raison peut ne pas être évidente.

  • Les procédures stockées peuvent être imbriquées, mais il existe une limite à la profondeur d’imbrication. Si votre procédure stockée appelle d’autres procédures stockées et qu’elle est susceptible d’être appelée par d’autres procédures stockées, vous pouvez spécifier la profondeur maximale connue de la pile d’appels de votre procédure stockée, afin que les appelants sachent si l’appel de votre procédure stockée peut dépasser la profondeur maximale de la pile d’appels.

  • Astuces de débogage.

L’emplacement et le format de ces informations dépendent de vous. Vous pouvez par exemple stocker les informations au format HTML sur un site Web interne. Avant de décider du lieu de stockage, pensez au lieu où votre entreprise stocke des informations similaires pour d’autres produits ou des informations similaires pour d’autres fonctionnalités Snowflake, telles que les vues, les fonctions définies par l’utilisateur, etc.

Autres astuces :

  • Incluez des commentaires dans le code source, comme vous devriez le faire pour presque tout élément de code source.

    • Rappelez-vous que la rétroingénierie du code est difficile. Décrivez non seulement le fonctionnement de votre algorithme, mais également son objectif.

  • Les procédures stockées autorisent un COMMENT (commentaire) facultatif pouvant être spécifié avec l’instruction CREATE PROCEDURE ou ALTER PROCEDURE. D’autres personnes peuvent lire ce commentaire en exécutant la commande SHOW PROCEDURES.

  • Si possible, pensez à conserver une copie maîtresse de la commande CREATE PROCEDURE de chaque procédure stockée dans un système de contrôle de code source. La fonctionnalité Time Travel de Snowflake ne s’applique pas aux procédures stockées ; par conséquent. La recherche d’anciennes versions de procédures stockées doit donc être effectuée en dehors de Snowflake. Si aucun système de contrôle de code source n’est disponible, vous pouvez en simuler partiellement un en stockant les commandes CREATE PROCEDURE dans un champ VARCHAR d’une table et en ajoutant chaque nouvelle version (sans remplacer les versions précédentes).

  • Pensez à utiliser une convention de dénomination pour fournir des informations sur les procédures stockées. Par exemple, un préfixe ou un suffixe dans le nom peut indiquer si la procédure est une procédure stockée avec droits de l’appelant ou une procédure stockée avec droits du propriétaire. (Par exemple, vous pouvez utiliser cr_ comme préfixe pour les droits de l’appelant.)

  • Pour voir les types de données et l’ordre des arguments d’entrée, ainsi que le commentaire, vous pouvez utiliser la commande SHOW PROCEDURES. Rappelez-vous cependant que cela ne montre que les noms et les types de données des arguments. Cela n’explique pas les arguments.

  • Si vous avez les privilèges appropriés, vous pouvez utiliser la commande DESCRIBE PROCEDURE pour voir :

    • Les noms et types de données des arguments.

    • Le corps de la procédure et si la procédure est exécutée en tant que propriétaire ou appelant.

    • Le type de données de la valeur renvoyée.

    • Autres informations utiles.

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 :

  1. Créez une table :

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

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

    call stproc1(5.14::FLOAT);
    +------------+
    | STPROC1    |
    |------------|
    | Succeeded. |
    +------------+
    
  4. Confirmez que la procédure stockée a inséré la ligne :

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

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

  1. Créez une procédure pour compter le nombre de lignes dans une table (équivalent à select count(*) from table) :

    create or replace procedure get_row_count(table_name VARCHAR)
      returns float not null
      language javascript
      as
      $$
      var row_count = 0;
      // Dynamically compose the SQL statement to execute.
      var sql_command = "select count(*) from " + TABLE_NAME;
      // Run the statement.
      var stmt = snowflake.createStatement(
             {
             sqlText: sql_command
             }
          );
      var res = stmt.execute();
      // Get back the row count. Specifically, ...
      // ... get the first (and in this case only) row from the result set ...
      res.next();
      // ... and then get the returned value, which in this case is the number of
      // rows in the table.
      row_count = res.getColumnValue(1);
      return row_count;
      $$
      ;
    
  2. Demandez à la procédure stockée combien de lignes se trouvent dans la table :

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
  3. Vérifiez indépendamment que vous avez le bon numéro :

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

Exemple de procédure stockée récursive

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

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

Création dynamique d’une instruction SQL

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

Note

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

  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 nom_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.
        // Note that we uppercased the input parameter name.
        var sql_command = "select count(*) from " + TABLE_NAME;
        // Run the statement.
        var stmt = snowflake.createStatement(
               {
               sqlText: sql_command
               }
            );
        var res = stmt.execute();
        // Get back the row count. Specifically, ...
        // ... first, get the first (and in this case only) row from the
        //  result set ...
        res.next();
        // ... then extract the returned value (which in this case is the
        // number of rows in the table).
        row_count = res.getColumnValue(1);
        return row_count;
        $$
        ;
    
  2. Appelez la procédure stockée :

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
  3. Affichez les résultats de select count(*) pour la même table :

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

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

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

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

Intercepter une erreur en utilisant Try/Catch

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

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

        create procedure broken()
          returns varchar not null
          language javascript
          as
          $$
          var result = "";
          try {
              snowflake.execute( {sqlText: "Invalid Command!;"} );
              result = "Succeeded";
              }
          catch (err)  {
              result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
              result += "\n  Message: " + err.message;
              result += "\nStack Trace:\n" + err.stackTraceTxt; 
              }
          return result;
          $$
          ;
    
  2. Appelez la procédure stockée. cela devrait renvoyer une erreur indiquant le numéro de l’erreur et d’autres informations :

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

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

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

    CREATE OR REPLACE PROCEDURE validate_age (age float)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
        try {
            if (AGE < 0) {
                throw "Age cannot be negative!";
            } else {
                return "Age validated.";
            }
        } catch (err) {
            return "Error: " + err;
        }
    $$;
    
  2. Appelez la procédure stockée avec des valeurs valides et non valides :

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

Consignation d’une erreur

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

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

CREATE OR REPLACE PROCEDURE broken() 
RETURNS varchar 
NOT NULL 
LANGUAGE javascript 
AS $$
var result;
try {
    snowflake.execute({ sqlText: "Invalid Command!;" });
    result = "Succeeded";
} catch (err) {
    result = "Failed";
    snowflake.execute({
      sqlText: `insert into error_log VALUES (?,?,?,?)`
      ,binds: [err.code, err.state, err.message, err.stackTraceTxt]
      });
}
return result;
$$;
call broken();
+--------+
| BROKEN |
|--------|
| Failed |
+--------+
select * from error_log;
+------------+-------------+---------------------------------------------------------+------------------------------------------+
| ERROR_CODE | ERROR_STATE | ERROR_MESSAGE                                           | STACK_TRACE                              |
|------------+-------------+---------------------------------------------------------+------------------------------------------|
|     100183 | P0000       | SQL compilation error:                                  | At Snowflake.execute, line 4 position 14 |
|            |             | syntax error line 1 at position 0 unexpected 'Invalid'. |                                          |
+------------+-------------+---------------------------------------------------------+------------------------------------------+

Consignation d’une erreur (version 2)

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

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

Créez les procédures :

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

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

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

    // Now just call the log function anytime...
    try {
        var x = 10/10;
        log('log this message'); //call the log function
        //do some stuff here
        log('x = ' + x.toString()); //log the value of x 
        log('this is another log message'); //throw in another log message
    } catch(ERROR) {
        log(ERROR); //we can even catch/log the error messages
        return ERROR;
    }

    $$
;

Activez la journalisation :

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

Appelez la procédure :

CALL my_test();
+---------+
| MY_TEST |
|---------|
| NULL    |
+---------+

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

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

Surcharge de noms de procédures stockées

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

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

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
        returns string
        language javascript
        strict
        as
        $$
        return FLOAT_PARAM1;
        $$
        ;
    
    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT, FLOAT_PARAM2 FLOAT)
        returns string
        language javascript
        strict
        as
        $$
        return FLOAT_PARAM1 * FLOAT_PARAM2;
        $$
        ;
    
  2. Ensuite, appelez les deux procédures :

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

Note

Vous devez également spécifier les types de données des arguments pour certaines autres opérations sur les procédures stockées. Par exemple, GRANT et REVOKE nécessitent les types d’arguments, ainsi que le nom de la procédure stockée.

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

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

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

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

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

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

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

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

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

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

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

  1. Créez et chargez la table :

    CREATE TABLE western_provinces(ID INT, province VARCHAR);
    
    INSERT INTO western_provinces(ID, province) VALUES
        (1, 'Alberta'),
        (2, 'British Columbia'),
        (3, 'Manitoba')
        ;
    
  2. Créez la procédure stockée. Cette procédure retourne une chaîne bien formatée qui ressemble à un jeu de résultats de trois lignes, mais qui est en réalité une seule chaîne :

    CREATE OR REPLACE PROCEDURE read_western_provinces()
      RETURNS VARCHAR NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      var return_value = "";
      try {
          var command = "SELECT * FROM western_provinces ORDER BY province;"
          var stmt = snowflake.createStatement( {sqlText: command } );
          var rs = stmt.execute();
          if (rs.next())  {
              return_value += rs.getColumnValue(1);
              return_value += ", " + rs.getColumnValue(2);
              }
          while (rs.next())  {
              return_value += "\n";
              return_value += rs.getColumnValue(1);
              return_value += ", " + rs.getColumnValue(2);
              }
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt;
          }
      return return_value;
      $$
      ;
    
  3. Appelez la procédure stockée, puis récupérez les résultats en utilisant RESULT_SCAN :

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

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

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

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

    CREATE TABLE all_provinces(ID INT, province VARCHAR);
    
  2. Appelez la procédure stockée, puis récupérez le résultat à l’aide de RESULT_SCAN, puis extrayez les trois lignes de la chaîne et placez-les dans la table :

    INSERT INTO all_provinces
      WITH 
        one_string (string_col) AS
          (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
        three_strings (one_row) AS
          (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
      SELECT
             STRTOK(one_row, ',', 1) AS ID,
             STRTOK(one_row, ',', 2) AS province
        FROM three_strings
        WHERE NOT (ID IS NULL AND province IS NULL);
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    
  3. Vérifiez que cela a fonctionné en affichant les lignes dans la table :

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

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

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

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
    
  2. Appelez la procédure stockée, puis récupérez le résultat (une chaîne) en utilisant RESULT_SCAN, puis enregistrez-le dans la table intermédiaire nommée one_string :

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

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

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

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

    SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +---------------------+-----+-------+---------------------+
    | STRING_COL          | SEQ | INDEX | VALUE               |
    |---------------------+-----+-------+---------------------|
    | 1, Alberta          |   1 |     1 | 1, Alberta          |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    | 1, Alberta          |   1 |     2 | 2, British Columbia |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    | 1, Alberta          |   1 |     3 | 3, Manitoba         |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    +---------------------+-----+-------+---------------------+
    SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +---------------------+
    | VALUE               |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    
  3. Ensuite, créez une table nommée three_strings. Cette table contiendra le résultat après l’avoir divisé en lignes/chaînes individuelles :

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
    
  4. Convertissez maintenant cette chaîne de la table one_string en trois chaînes distinctes et montrez qu’il s’agit désormais de trois chaînes :

    INSERT INTO three_strings
      SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    SELECT string_col 
        FROM three_strings;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    SELECT COUNT(*) 
        FROM three_strings;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    
  5. Convertissez maintenant les trois chaînes en trois lignes dans notre table à long terme nommée all_provinces :

    INSERT INTO all_provinces
      SELECT 
             STRTOK(string_col, ',', 1) AS ID, 
             STRTOK(string_col, ',', 2) AS province 
        FROM three_strings
        WHERE NOT (ID IS NULL AND province IS NULL);
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    
  6. Affichez les trois lignes de la table à long terme :

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

Renvoi d’un tableau de messages d’erreur

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

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

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

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

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

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

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

Renvoi d’un jeu de résultats

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

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

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

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

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

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

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

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

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

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

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

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

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

Protection de la vie privée

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

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

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

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

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

    create table reviews (customer_ID VARCHAR, review VARCHAR);
    create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                                   product_ID VARCHAR, purchase_date DATE);
    
    insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
        (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
        (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
        (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);
    
    insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
    insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');
    
  2. Créez la procédure stockée :

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

    SELECT * FROM reviews;
    +-------------+---------------------------+
    | CUSTOMER_ID | REVIEW                    |
    |-------------+---------------------------|
    | 1           | Loved the milk chocolate! |
    | 2           | Loved the dark chocolate! |
    +-------------+---------------------------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
  4. Le client #1 a une garantie qui est toujours en vigueur. La procédure stockée supprime les commentaires de révision qu’il a publiés, mais conserve son enregistrement d’achat en raison de la garantie :

    call delete_nonessential_customer_data(1);
    +---------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
    |---------------------------------------------------------|
    | Deleted non-financial, non-warranty data for customer 1 |
    +---------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+---------------------------+
    | CUSTOMER_ID | REVIEW                    |
    |-------------+---------------------------|
    | 2           | Loved the dark chocolate! |
    +-------------+---------------------------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
  5. Le client #2 doit encore de l’argent. La procédure stockée supprime ses commentaires de révision, mais conserve son enregistrement d’achat :

    call delete_nonessential_customer_data(2);
    +---------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
    |---------------------------------------------------------|
    | Deleted non-financial, non-warranty data for customer 2 |
    +---------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+--------+
    | CUSTOMER_ID | REVIEW |
    |-------------+--------|
    +-------------+--------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
  6. Le client #3 ne doit pas d’argent (et ne lui doit pas d’argent). Sa garantie a expiré, donc la procédure stockée supprime à la fois les commentaires de révision et les enregistrements d’achat :

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

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

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

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

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

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

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

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

  1. Créer et charger une table :

    create table sv_table (f float);
    insert into sv_table (f) values (49), (51);
    
  2. Définir une variable de session :

    set SESSION_VAR1 = 50;
    
  3. 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;
      $$
      ;
    
  4. Appelez la procédure :

    CALL session_var_user();
    +------------------+
    | SESSION_VAR_USER |
    |------------------|
    |               51 |
    +------------------+
    
  5. 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.

  1. 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;
      $$
      ;
    
  2. Appelez la procédure (elle devrait échouer) :

    100183 (P0000): Execution error in store procedure CANNOT_USE_SESSION_VARS:
    Use of session variable '$SESSION_VAR1' is not allowed in owners rights stored procedure
    At Statement.execute, line 6 position 16
    
  3. 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.
      $$
      ;
    
  4. Appelez la procédure (elle devrait échouer) :

    100183 (P0000): Execution error in store procedure CANNOT_SET_SESSION_VARS:
    Stored procedure execution error: Unsupported statement type 'SET'.
    At Statement.execute, line 6 position 16
    

Dépannage

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