Catégories :

DDL pour les fonctions définies par l’utilisateur, les fonctions externes et les procédures stockées

CREATE PROCEDURE

Crée une nouvelle procédure stockée. Une procédure stockée contient un code JavaScript.

Voir aussi :

ALTER PROCEDURE , DESCRIBE PROCEDURE , DROP PROCEDURE , SHOW PROCEDURES

CALL

Syntaxe

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Important

JavaScript est sensible à la casse, alors que SQL ne l’est pas. Voir Sensibilité à la casse dans les arguments JavaScript pour obtenir des informations importantes sur l’utilisation de noms d’arguments de procédure stockée dans le code JavaScript.

Paramètres requis

nom ( [ nom_argument type_données_argument ] [ , ... ] )

Spécifie l’identificateur (et éventuellement un ou plusieurs arguments/entrées) pour la procédure stockée. L’identificateur n’a pas besoin d’être unique pour le schéma dans lequel la procédure est créée parce que les procédures stockées sont identifiées et résolues par leurs noms et types d’arguments.

Toutefois, l’identificateur doit commencer par un caractère alphabétique et ne peut pas contenir d’espaces ou de caractères spéciaux à moins que toute la chaîne d’identificateur soit délimitée par des guillemets doubles (p. ex. "My object"). Les identificateurs entre guillemets doubles sont également sensibles à la casse.

Pour plus de détails, voir Exigences relatives à l’identificateur.

RETURNS type_données_résultat [ NOT NULL ]

Spécifie les résultats renvoyés par la procédure stockée.

En pratique, la valeur renvoyée ne peut pas être utilisée, car l’appel ne peut pas faire partie d’une expression.

LANGUAGE JAVASCRIPT

Spécifie que la procédure stockée contient du code JavaScript. Actuellement, JavaScript est le seul langage pris en charge ; spécifier un autre langage entraînera un message d’erreur.

AS définition_procédure

Définit le code JavaScript exécuté par la procédure stockée. La définition peut comprendre n’importe quel code JavaScript valide ; cependant, Snowflake ne valide pas le code au moment de la création de la procédure stockée (la création de la procédure stockée réussit, que le code soit valide ou non). Si le code n’est pas valide, des erreurs s’afficheront lorsque la procédure stockée sera appelée.

Les délimiteurs autour de la définition de procédure peuvent être des guillemets simples ou une paire de signes dollar. L’utilisation de « $$ » comme délimiteur facilite l’écriture des procédures stockées contenant des guillemets simples.

JavaScript autorise des guillemets (également appelés « références arrière ») autour des chaînes contenant de nouvelles lignes.

L’exemple ci-dessous utilise « $$ » et des références arrière, car le corps de la procédure stockée contient des guillemets simples et des guillemets doubles :

CREATE OR REPLACE TABLE table1 ("column 1" VARCHAR);
CREATE or replace PROCEDURE proc3()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
  var rs = snowflake.execute( { sqlText: 
      `INSERT INTO table1 ("column 1") 
           SELECT 'value 1' AS "column 1" ;`
       } );
  return 'Done.';
  $$;

Pour plus de détails sur les procédures stockées, voir :

Paramètres facultatifs

CALLED ON NULL INPUT ou . RETURNS NULL ON NULL INPUT | STRICT

Spécifie le comportement de la procédure stockée lors d’un appel avec des entrées « null ». Contrairement aux fonctions définies par le système, qui retournent toujours la valeur « null » lorsqu’une entrée est nulle, les procédures stockées peuvent gérer des entrées null, retournant des valeurs non nulles même lorsqu’une entrée est nulle :

  • CALLED ON NULL INPUT appellera toujours la procédure stockée avec des entrées null. Il appartient à la procédure de traiter ces valeurs de manière appropriée.

  • RETURNS NULL ON NULL INPUT (ou son synonyme STRICT) n’appelle pas la procédure stockée si une entrée est null, ainsi les instructions contenues dans la procédure stockée ne seront pas exécutées. En revanche, une valeur null sera toujours retournée. Notez que la procédure peut toujours retourner une valeur null pour les entrées non null.

Par défaut : CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Spécifie le comportement de la procédure stockée lors de l’affichage de résultats :

  • VOLATILE: La procédure peut afficher des valeurs différentes pour différentes lignes, même pour la même entrée (par exemple, en raison du non-déterminisme et du statut).

  • IMMUTABLE: La procédure suppose que la fonction, lorsqu’elle est appelée avec les mêmes entrées, renvoie toujours le même résultat. Cette garantie n’est pas vérifiée. Spécifier IMMUTABLE pour une procédure qui retourne des valeurs différentes pour la même entrée aura pour résultat un comportement indéfini.

Par défaut : VOLATILE

COMMENT = 'litéral_chaine'

Spécifie un commentaire pour la procédure stockée, qui est affiché dans la colonne DESCRIPTION de la sortie SHOW PROCEDURES.

Par défaut : stored procedure

EXECUTE AS CALLER | OWNER

Une procédure stockée peut s’exécuter avec les privilèges du propriétaire (procédure stockée des « droits du propriétaire ») ou avec les privilèges de l’appelant (procédure stockée des « droits de l’appelant »). Si vous exécutez l’instruction CREATE PROCEDURE ... EXECUTE AS CALLER, alors plus tard, la procédure s’exécutera en tant que procédure de droits de l’appelant. Si vous exécutez CREATE PROCEDURE ... EXECUTE AS OWNER, la procédure sera exécutée comme une procédure de droits du propriétaire. Par défaut (si ni OWNER ni CALLER ne sont spécifiés explicitement au moment de la création de la procédure), la procédure s’exécute en tant que procédure stockée avec droits du propriétaire. Les procédures stockées avec droits du propriétaire ont moins d’accès à l’environnement de l’appelant (par exemple, les variables de session de l’appelant), et Snowflake utilise par défaut ce niveau supérieur de confidentialité et de sécurité. Pour plus d’informations sur les procédures relatives aux droits du propriétaire et de l’appelant, voir Privilèges sur les objets de base de données accessibles par la procédure stockée.

Par défaut : OWNER

Notes sur l’utilisation

  • Une procédure stockée ne peut renvoyer qu’une seule valeur, telle qu’une chaîne (par exemple, un indicateur de réussite/échec) ou un nombre (par exemple, un code d’erreur). Si vous devez renvoyer des informations plus détaillées, vous pouvez renvoyer un VARCHAR contenant des valeurs séparées par un délimiteur (tel qu’une virgule) ou un type de données semi-structuré, tel que VARIANT.

  • Les procédures stockées prennent en charge la surcharge. Deux procédures peuvent avoir le même nom si elles ont un nombre différent de paramètres ou un type de données différent pour leurs paramètres.

Astuce

Si votre organisation utilise une combinaison de procédures stockées relatives aux droits de l’appelant et aux droits du propriétaire, vous pouvez utiliser une convention de dénomination pour vos procédures stockées afin d’indiquer si une procédure stockée individuelle est une procédure stockée avec droits d’un appelant ou une procédure stockée avec droits du propriétaire.

Exemples

Cela crée une procédure stockée triviale qui renvoie une valeur codée en dur. Cela n’est pas réaliste, mais affiche la syntaxe de base SQL avec un minimum de code JavaScript :

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

Voici un exemple plus réaliste incluant un appel à l’API JavaScript. Une version plus complète de cette procédure pourrait permettre à un utilisateur d’insérer des données dans une table dans laquelle l’utilisateur ne disposerait pas de privilèges nécessaires pour insérer directement des données. Des instructions JavaScript pourraient vérifier les paramètres d’entrée et exécuter la requête SQL INSERT uniquement si certaines conditions étaient remplies.

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

Pour plus d’exemples, voir Travailler avec des procédures stockées.