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 :

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.

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

Vous devez nommer les procédures selon les conventions appliquées par Snowflake.

Pour plus d’informations, voir Nommage et surcharge de procédures et d’UDFs.

Gestion des transactions

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.

Vous pouvez utiliser des procédures stockées avec des transactions pour rendre un groupe d’instructions atomique. Pour plus de détails, voir Procédures et transactions stockées.

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

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 exécuté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;
    $$
    ;
Copy

Appel d’une procédure stockée

Vous appelez une procédure stockée à l’aide d’une commande SQL. Pour plus d’informations sur l’appel de procédures stockées, voir Appel d’une procédure stockée.

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 Understanding Caller’s Rights and Owner’s Rights Stored Procedures.

Considérations relatives aux procédures stockées

  • Bien que les procédures stockées autorisent l’imbrication et la récursivité, la profondeur maximale actuelle de la pile d’appels imbriqués pour les procédures stockées définies par l’utilisateur est de 5 (y compris la procédure stockée de premier niveau) 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.

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 Understanding Caller’s Rights and Owner’s Rights Stored Procedures .

  • 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 d’écrire une procédure stockée ou une fonction définie par l’utilisateur.

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.

Langage: Français