Vue d’ensemble des procédures stockées¶
Vous pouvez écrire des procédures stockées pour étendre le système avec du code procédural qui exécute SQL. Dans une procédure stockée, vous pouvez utiliser des constructions programmatiques pour effectuer des branchements et des boucles. Une fois que vous avez créé une procédure stockée, vous pouvez la réutiliser plusieurs fois.
Vous écrivez la logique d’une procédure (son gestionnaire) dans l’un des langages pris en charge. Une fois que vous avez un gestionnaire, vous pouvez créer une procédure avec une commande CREATE PROCEDURE, puis appeler la procédure avec une instruction CALL.
À partir d’une procédure stockée, vous pouvez renvoyer une valeur unique ou (si le langage du gestionnaire le permet) des données tabulaires. Pour plus d’informations sur les types de retour pris en charge, voir CREATE PROCEDURE.
Note
Pour créer et appeler une procédure anonyme, utilisez CALL (avec procédure anonyme). La création et l’appel d’une procédure anonyme ne nécessitent pas un rôle avec des privilèges de schéma CREATE PROCEDURE.
Note
Une procédure stockée est comme une UDF, mais les deux diffèrent sur des points importants. Pour plus d’informations, voir Choisir d’écrire une procédure stockée ou une fonction définie par l’utilisateur.
Dans ce chapitre :
Qu’est-ce qu’une procédure stockée ?¶
Une procédure stockée contient une logique que vous écrivez afin de pouvoir l’appeler à partir de SQL. La logique d’une procédure stockée effectue généralement des opérations de base de données en exécutant des instructions SQL.
Avec une procédure stockée, vous pouvez également :
Créer et exécuter dynamiquement des instructions SQL de façon dynamique ;
Exécuter du code avec les privilèges du rôle propriétaire de la procédure plutôt qu’avec les privilèges du rôle qui exécute la procédure.
Cela permet au propriétaire de la procédure stockée de déléguer le pouvoir d’effectuer des opérations spécifiées à des utilisateurs qui, autrement, ne pourraient pas le faire. Cependant, il existe des limitations à ces procédures stockées avec droits du propriétaire.
Vous pouvez utiliser une procédure stockée pour automatiser une tâche qui nécessite plusieurs instructions SQL et qui est exécutée fréquemment. Par exemple, imaginons que vous souhaitiez « nettoyer » une base de données en supprimant des données antérieures à une date spécifiée. Vous pouvez écrire plusieurs instructions DELETE, chacune d’elles supprimant les données d’une table spécifique. Vous pouvez placer toutes ces instructions dans une seule procédure stockée et transmettre un paramètre spécifiant la date limite. Ensuite, vous pouvez simplement appeler la procédure pour nettoyer la base de données. Lorsque votre base de données change, vous pouvez mettre à jour la procédure pour nettoyer les tables supplémentaires. Si plusieurs utilisateurs utilisent la commande de nettoyage, ils peuvent appeler une seule procédure plutôt que de se souvenir de chaque nom de table et de nettoyer chaque table individuellement.
Exemple de procédure stockée¶
Le code de l’exemple suivant crée une procédure stockée appelée myproc avec un gestionnaire Python appelé run.
create or replace procedure myproc(from_table string, to_table string, count int)
returns string
language python
runtime_version = '3.8'
packages = ('snowflake-snowpark-python')
handler = 'run'
as
$$
def run(session, from_table, to_table, count):
session.table(from_table).limit(count).write.save_as_table(to_table)
return "SUCCESS"
$$;
Le code de l’exemple suivant appelle la procédure stockée myproc.
CALL myproc('table_a', 'table_b', 5);
Langues acceptées¶
Vous écrivez le gestionnaire d’une procédure - sa logique - dans l’un des nombreux langages de programmation. Chaque langage permet de manipuler des données en respectant les contraintes du langage et de son environnement d’exécution. Quel que soit le langage du gestionnaire, vous créez la procédure elle-même de la même manière en utilisant SQL, en spécifiant votre gestionnaire et le langage du gestionnaire.
Vous pouvez écrire un gestionnaire dans l’un des langages suivants :
Langage |
Guide du développeur |
|---|---|
Java (en utilisant l’API Snowpark) |
|
JavaScript |
|
Python (en utilisant l’API de Snowpark) |
|
Scala (en utilisant l’API de Snowpark) |
|
Exécution de scripts Snowflake (SQL) |
Écriture de procédures stockées dans Exécution de scripts Snowflake |
Choix du langage¶
Vous écrivez le gestionnaire d’une procédure - sa logique - dans l’un des nombreux langages de programmation. Chaque langage permet de manipuler des données en respectant les contraintes du langage et de son environnement d’exécution.
Vous pouvez choisir un langage particulier si :
Vous disposez déjà de code dans ce langage.
Par exemple, si vous disposez déjà d’une méthode Java qui fonctionnera en tant que gestionnaire et que l’objet de la méthode se trouve dans un fichier .jar, vous pouvez copier le fichier .jar dans une zone de préparation, spécifier le gestionnaire en tant que classe et méthode, puis choisir Java comme langage.
Le langage possède des capacités que d’autres n’ont pas.
Le langage possède des bibliothèques qui peuvent vous aider à effectuer le traitement dont vous avez besoin.
Lorsque vous choisissez un langage, tenez également compte des emplacements du gestionnaire pris en charge. Tous les langages ne permettent pas de faire référence au gestionnaire sur une zone de préparation (le code du gestionnaire doit être en ligne). Pour plus d’informations, consultez Conserver le code du gestionnaire en ligne ou dans une zone de préparation.
Langage |
Emplacement du gestionnaire |
|---|---|
Java |
En ligne ou en zone de préparation |
JavaScript |
En ligne |
Python |
En ligne ou en zone de préparation |
Scala |
En ligne ou en zone de préparation |
Exécution de scripts Snowflake |
En ligne |
Guides du développeur¶
Lignes directrices et contraintes¶
- Conseils
Pour des conseils sur l’écriture de procédures stockées, voir Travailler avec des procédures stockées.
- Contraintes liées à Snowflake
Vous pouvez assurer la stabilité de l’environnement Snowflake en vous développant dans le cadre des contraintes de Snowflake. Pour plus d’informations, consultez Concevoir des gestionnaires qui respectent les contraintes imposées par Snowflake.
- Nommage
Veillez à nommer les procédures de manière à éviter les collisions avec d’autres procédures. Pour plus d’informations, consultez Nommage et surcharge de procédures et d’UDFs.
- Arguments
Spécifiez les arguments de votre procédure stockée et indiquez quels arguments sont facultatifs. Pour plus d’informations, consultez Définition des arguments pour UDFs et les procédures stockées.
- Mappages de types de données
Pour chaque langage de traitement, il existe un ensemble distinct de mappages entre les types de données du langage et les types SQL utilisés pour les arguments et les valeurs de retour. Pour plus d’informations sur les mappages pour chaque langage, voir Mappage des types de données entre SQL et les langages de traitement.
Écriture du gestionnaire¶
- Langues du gestionnaire
Pour du contenu spécifique à une langue sur l’écriture d’un gestionnaire, consultez Langues acceptées.
- Accès au réseau externe
Vous pouvez accéder aux emplacements réseau externes avec un accès au réseau externe. Vous pouvez créer un accès sécurisé à des emplacements réseau spécifiques externes à Snowflake, puis utiliser cet accès à partir du code du gestionnaire.
- Journalisation et traçage
Vous pouvez enregistrer l’activité du code en capturant les messages du journal et les événements de trace, en stockant les données dans une base de données que vous pouvez interroger ultérieurement.
Sécurité¶
Le fait qu’une procédure stockée soit exécutée avec les droits de l’appelant ou du propriétaire peut avoir une incidence sur les informations auxquelles elle a accès et sur les tâches qu’elle peut être autorisée à effectuer. Pour plus d’informations, consultez Understanding Caller’s Rights and Owner’s Rights Stored Procedures.
Les procédures stockées partagent certains problèmes de sécurité avec les fonctions définies par l’utilisateur (UDFs). Pour plus d’informations, voir ci-dessous :
Vous pouvez aider le code du gestionnaire d’une procédure à s’exécuter en toute sécurité en suivant les bonnes pratiques décrites dans la section Pratiques de sécurité pour UDFs et procédures
Veillez à ce que les informations sensibles soient dissimulées aux utilisateurs qui ne devraient pas y avoir accès. Pour plus d’informations, voir Protection des informations sensibles avec les UDFs et les procédures stockées sécurisées
Déploiement du code du gestionnaire¶
Lors de la création d’une procédure, vous pouvez spécifier son gestionnaire (qui met en œuvre la logique de la procédure) en tant que code en ligne avec l’instruction CREATE PROCEDURE ou en tant que code externe à l’instruction, tel que le code compilé et copié dans une zone de préparation.
Pour plus d’informations, voir Conserver le code du gestionnaire en ligne ou dans une zone de préparation.
Créer et appeler des procédures¶
Vous utilisez SQL pour créer et appeler une procédure.
Une fois que vous avez écrit le code du gestionnaire, vous pouvez créer une procédure stockée en exécutant l’instruction CREATE PROCEDURE, en spécifiant le gestionnaire de la procédure. Pour plus d’informations, voir Création d’une procédure stockée.
Pour appeler une procédure, exécutez une instruction SQL CALL qui spécifie la procédure. Pour plus d’informations, consultez Appel d’une procédure stockée.
Pour créer une procédure temporaire qui ne s’exécute qu’une fois et qui est rejetée, utilisez WITH…CALL . Pour plus d’informations, voir CALL (avec procédure anonyme).