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 peut être écrite dans l’un des langages suivants :

Voir aussi :

ALTER PROCEDURE, DROP PROCEDURE , SHOW PROCEDURES , DESCRIBE PROCEDURE

CALL

Syntaxe

JavaScript

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 ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ 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.

Exécution de scripts Snowflake

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
    [ NOT NULL ]
  LANGUAGE SQL
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS <procedure_definition>

Note

Si vous créez une procédure Exécution de scripts Snowflake dans SnowSQL ou dans l’interface Web classique, vous devez utiliser les délimiteurs de littéral de chaîne (' ou $$) autour de la définition de la procédure. Voir Utilisation d’Exécution de scripts Snowflake dans SnowSQL et l’interface Web classique.

Snowpark (Scala et Java)

Pour les procédures stockées en ligne, utilisez la syntaxe suivante :

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  AS '<procedure_definition>'

Pour les procédures stockées précompilées en Scala et Java, utilisez la syntaxe suivante :

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]

Paramètres requis

Tous les langages

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

Spécifie l’identificateur (nom) et tout argument d’entrée pour la procédure stockée.

RETURNS type_données_résultat [ NOT NULL ]

Spécifie le type du résultat renvoyé par la procédure stockée.

  • Pour type_données_résultat, utilisez le type de données Snowflake qui correspond au type de langage que vous utilisez.

  • Pour RETURNS TABLE ( [ nom_col type_données_col [ , ... ] ] ), si vous connaissez les types de données Snowflake des colonnes de la table renvoyée, indiquez les noms et les types de colonnes :

    CREATE OR REPLACE PROCEDURE get_top_sales()
    RETURNS TABLE (sales_date DATE, quantity NUMBER)
    ...
    

    Sinon (par exemple, si vous déterminez les types de colonnes pendant l’exécution), vous pouvez omettre les noms et les types de colonnes :

    CREATE OR REPLACE PROCEDURE get_top_sales()
    RETURNS TABLE ()
    

    Note

    Actuellement, dans la clause RETURNS TABLE(...) de CREATE PROCEDURE, vous ne pouvez pas spécifier GEOGRAPHY comme type de colonne.

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE(g GEOGRAPHY)
    ...
    

    Si vous le faites, l’appel de la procédure stockée entraîne une erreur :

    CALL test_return_geography_table_1();
    
    Stored procedure execution error: data type of returned table does not match expected returned table type
    

    Pour contourner ce problème, vous pouvez omettre les arguments et les types de colonnes dans RETURNS TABLE().

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE()
    ...
    

    RETURNS TABLE(...) n’est possible que dans les procédures stockées Exécution de scripts Snowflake qui renvoient une table. Voir RETURN.

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

LANGUAGE langage

Spécifie le langage du code de la procédure stockée Notez que ceci est facultatif pour les procédures stockées écrites avec Exécution de scripts Snowflake.

Actuellement, les valeurs prises en charge pour le langage comprennent :

Par défaut : SQL.

AS définition_procédure

Définit le code exécuté par la procédure stockée. La définition peut consister en n’importe quel code valide.

Remarques :

  • Pour les procédures stockées précompilées dans Snowpark Scala et Java, omettez la clause AS.

    Utilisez plutôt la clause IMPORTS pour spécifier l’emplacement du fichier JAR contenant le code de la procédure stockée. Pour plus de détails, voir :

  • Vous devez utiliser les délimiteurs de littéral de chaîne (' ou $$) autour de la définition de procédure si :

  • Pour les procédures stockées en JavaScript et si vous écrivez une chaîne qui contient de nouvelles lignes, vous pouvez utiliser des guillemets (également appelés « backticks ») autour de la chaîne.

    L’exemple ci-dessous d’une procédure stockée JavaScript utilise $$ et des guillemets, 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 les langages autres que Exécution de scripts Snowflake, Snowflake ne valide pas complètement le code lorsque vous exécutez la commande CREATE PROCEDURE.

    Par exemple, pour les procédures stockées Snowpark (Scala), le nombre et les types d’arguments d’entrée sont validés, mais le corps de la fonction ne l’est pas. Si le nombre ou les types ne correspondent pas (par exemple, si le type de données Snowflake NUMBER est utilisé alors que l’argument est un type non numérique), l’exécution de la commande CREATE PROCEDURE provoque une erreur.

    Si le code n’est pas valide, la commande CREATE PROCEDURE réussira, et des erreurs s’afficheront lorsque la procédure stockée sera appelée.

Pour plus de détails sur les procédures stockées, voir Travailler avec des procédures stockées.

Snowpark (Scala et Java)

RUNTIME_VERSION = 'version_runtime_scala_ou_java'

Version de la runtime Scala ou Java à utiliser. Actuellement, les seules versions prises en charge sont les suivantes :

  • 2.12 (pour Scala).

  • 11 (pour Java).

PACKAGES = ( 'nom_paquet_entièrement_qualifié' )

Nom de paquet entièrement qualifié de la bibliothèque Snowpark.

Spécifiez le nom de paquet entièrement qualifié pour la bibliothèque Snowpark dans le format suivant :

com.snowflake:snowpark:<version>

version est le numéro de version ou latest pour la dernière version.

Note

Spécifiez une version qui est au moins la version minimale prise en charge ou supérieure :

  • Pour Scala, spécifiez la version 1.1.0 ou ultérieure.

    Snowflake prend en charge l’utilisation de Snowpark version 0.9.0 ou ultérieure dans une procédure stockée. Notez toutefois que ces versions présentent des limites. Par exemple, les versions antérieures à la version 1.1.0 ne prennent pas en charge l’utilisation de transactions dans une procédure stockée.

  • Pour Java, indiquez la version 1.3.0 ou ultérieure.

Pour obtenir la liste des paquets et des versions pris en charge, interrogez la vue INFORMATION_SCHEMA.PACKAGES pour les lignes comportant LANGUAGE = 'scala' ou LANGUAGE = 'java'. Par exemple :

select * from information_schema.packages where language = 'scala';
select * from information_schema.packages where language = 'java';
HANDLER = 'nom_méthode_entièrement_qualifié'

Nom de la méthode ou de la fonction entièrement qualifié pour la procédure stockée. Ceci se présente généralement sous la forme suivante :

com.my_company.my_package.MyClass.myMethod

où :

com.my_company.my_package

correspond au paquet contenant l’objet ou la classe :

package com.my_company.my_package;

Paramètres facultatifs

Tous les langages

[ [ NOT ] NULL ]

Indique si la procédure stockée peut renvoyer des valeurs NULL ou doit uniquement renvoyer des valeurs NON-NULL.

La valeur par défaut est NULL (c’est-à-dire que la procédure stockée peut renvoyer une valeur NULL).

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

Obsolète

Attention

Ces mots-clés sont obsolètes pour les procédures stockées. Ces mots-clés ne sont pas destinés à s’appliquer aux procédures stockées. Dans une prochaine version, ces mots-clés seront supprimés de la documentation.

COMMENT = 'litté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 ou . EXECUTE AS OWNER

Spécifie si 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, voir Présentation des procédures stockées des droits de l’appelant et des droits du propriétaire.

Par défaut : OWNER

Snowpark (Scala et Java)

IMPORTS = ( 'chemin_zone_préparation_et_fichier_à_lire' [, 'chemin_zone_préparation_et_fichier_à_lire' ...] )

L’emplacement (zone de préparation), le chemin et le nom du ou des fichiers à importer. Vous devez définir la clause IMPORTS pour inclure tous les fichiers dont dépend votre procédure stockée :

  • Si vous écrivez une procédure stockée en ligne, vous pouvez omettre cette clause, sauf si votre code dépend de classes définies en dehors de la procédure stockée ou de fichiers de ressources.

  • Si vous écrivez une procédure stockée précompilée, vous devez également inclure le fichier JAR contenant la définition de la procédure stockée.

Chaque fichier de la clause IMPORTS doit avoir un nom unique, même si les fichiers se trouvent dans des sous-répertoires différents ou dans des zones de préparation différentes.

TARGET_PATH = 'chemin_et_nom_de_fichier_à_écrire_de_zone_préparation'

La clause TARGET_PATH spécifie l’emplacement vers lequel Snowflake doit écrire le code compilé (fichierJAR) après avoir compilé le code source spécifié dans la définition_procédure.

Si cette clause est omise, Snowflake recompile le code source chaque fois que le code est nécessaire.

Si vous spécifiez cette clause :

  • Vous ne pouvez pas définir cette option sur un fichier existant. Snowflake renvoie une erreur si le TARGET_PATH pointe vers un fichier existant.

  • Si vous spécifiez les clauses IMPORTS et TARGET_PATH , le nom du fichier dans la clause TARGET_PATH doit être différent de chaque nom de fichier dans la clause IMPORTS , même si les fichiers se trouvent dans des sous-répertoires différents ou dans des zones de préparation différentes.

  • Si vous n’avez plus besoin d’utiliser la procédure stockée (par exemple, si vous détruisez la procédure stockée), vous devez supprimer manuellement ce fichier JAR.

Notes sur l’utilisation

  • Pour toutes les procédures stockées :

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

    • Les procédures stockées ne sont pas atomiques ; si une instruction d’une procédure stockée échoue, les autres instructions de la procédure stockée ne sont pas nécessairement annulées. Pour des informations sur les procédures et les transactions stockées, voir Gestion des transactions.

    • Concernant les métadonnées :

      Attention

      Les clients doivent s’assurer qu’aucune donnée personnelle (autre que pour un objet utilisateur), donnée sensible, donnée à exportation contrôlée ou autre donnée réglementée n’est saisie comme métadonnée lors de l’utilisation du service Snowflake. Pour plus d’informations, voir Champs de métadonnées dans Snowflake.

    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.

  • Pour les procédures stockées JavaScript :

    • Une procédure stockée JavaScript 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.

  • Pour les procédures stockées Snowpark (Scala), voir les limitations connues.

  • Pour les procédures stockées Snowpark (Java), voir les limitations connues.

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 des exemples de procédures stockées Snowpark (Scala), voir Écriture de procédures stockées dans Snowpark (Scala).

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

Revenir au début