Pourquoi - et comment - apprendre PL/SQL (…T-SQL…PL/pgSQL…)

Quoi faire si le langage de programmation SQL ne vous permet autant de contrôle de la logique conditionnelle et des boucles qu'il vous faut ? Pour cette raison, la plupart des grandes marques de bases de données comprennent également des commandes écrites dans un langage de programmation "procédural". Chez Oracle, la langue s'appelle PL/SQL. Microsoft SQL Server : T-SQL. PostgreSQL : PL/pgSQL. Etc. Lisez cet article pour découvrir s'il vous faut apprendre l'un de ces langages.

(This post is also available in English)

(Article originel à katiekodes.com, édité le 01 Nov, 2018)

Pourquoi existent de telles langues ?

SQL est une langue de programmation "déclarative" qui accompagne la plupart des bases de données connues. SQL permit de:

1. configurer la base de données
2. la remplir avec des données
3. effectuer des "requêtes" simples pour récupérer les données

SQL se veut simple et accessible aux non-programmeurs. C'est censé être relativement infaillible.

Alors aussi complexe et puissant que puisse être SQL, dans le grand ordre des choses, il y a beaucoup de choses qu'il ne peut pas faire.

S'il vous faut rédiger une logique conditionnelle très complexe ou des boucles spécialisées sur les enregistrements d'un ensembles de données, il est possible que SQL ne suffise pas.

Un langage comme PL/SQL vous permet d'atteindre vos objectifs.

Aussi, dans la plupart des bases de données, les "déclencheurs" s'écrivent dans de tels langages.

Dois-je apprendre le PL/SQL?

Cela dépend de vos objectifs.

La plupart des administrateurs de base de données ne permettent pas à des utilisateurs ordinaires (comme vous ?) d'enregistrer des logiciels PL/SQL (tels que que "déclencheurs", "fonctions" ou "procédures stockées") dans la base de données.

Il y en a certain(e)s qui:

1. vous permettraient rédiger de tels logiciels
2. les reliraient pour s'assurer que vous faisiez du bon travail
3. les enregistrerait dans la base de données eux-mêmes

Mais généralement, il vous serait peut-être interdit d'aider votre entreprise à écrire les déclencheurs, les fonctions, et les procédures stockées. Vous serez probablement limité à l'écriture de blocs anonymes. Jetons un coup d'oeil à chacun de ces 4 types de programme PL/SQL.

Déclencheurs

Un déclencheur est un programme, enregistré dans la base de données, que le système exécute à chaque fois que l'on essaie d'insérer, de mettre à jour ou de supprimer un enregistrement répondant à certains critères (par exemple, un enregistrement stocké dans une certaine table).

On s'en sert pour:

• fournir un contrôle des autorisations sur l'enregistrement des données plus avancé que ne permet les commandes simples de la base de données
• vérifier / empêcher / corriger / améliorer la saisie des données
• etc.

Fonctions

Une fonction est un programme, enregistré dans la base de données, qui devient une extension du langage SQL (pour les utilisateurs autorisés à exécuter cette fonction).

Par exemple, dans les systèmes Oracle, NVL(TelChamp,TelValeur) est un fonction incluse qui veut dire "si la valeur de TelChamp est vide, remplacez-la avec TelValeur".

Le stockage de "fonctions" dans la base de données vous permet d'ajouter des opérations genre NomAstucieux (...) dont les utilisateurs peuvent se servir dans leurs scripts SQL.

Par exemple, à la faculté où je travaille, on a enregistré 3 fonctions:

1. code_du_semestre_actuel()
2. code_du_semestre_precedent()
3. code_du_prochain_semestre()

Ces 3 fonctions transforment l'idée d'"aujourd'hui" en informations qui simplifient l'écriture de requêtes telles que:

• Qui est inscrit ce semestre?
• Qui a déjà obtenu son diplôme?

Procédures stockées

Supposons qu'un développeur web rédige un portail en libre-service qui extrait des données de votre base de données et les affiche pour les clients qui s'en servent.

Ce logiciel crée au moins une requête SQL sur votre base de données, car il faut indiquer quel client demande ses informations.

Donc, le développeur web pourrait écrire une telle requête SQL:

select *
from info_client
where email_client='test@example.com'

Voyez-vous ce test@example.com?

D'où vient test@example.com ?

D'un inconnu qui prétend que c'est son adresse mail ?

Et s'il ment? Que se passe-t-il si l'adresse prétendue est du code SQL qui supprime toutes les tables de votre base de données ?

Ces attaques s'appellent "l'injection SQL" et sont très dangereuses. On peut en apprendre plus aux liens suivants :

• Comment fonctionne l'injection SQL de la BD XKCD "Bobby Tables"?
• Wikipedia: injection SQL
• Bobby Tables (guides de prévention, en anglais)

Il est possible de laisser au développeur web le soin de vérifier que "l'adresse" soit correcte, mais ... si elle oublie? Si elle part en vacances et son développeur junior supprime, par accident, la partie du code qui fait la verification ?

From a database administrator's perspective, it's considered best practice to prevent the username under which "public"-facing code like the customer self-service portal runs from directly executing any SQL queries against the database.

Les administrateurs des bases de données préfèrent d'empêcher tout code destiné au public d'exécuter des requêtes SQL.

Au lieu de cela, on enregistre des "procédures stockées" dans la base de données et n'autorise le code web qu'à appeler ces procédures. De telles procédures servent d'API que le programmeur web peut appeler. Par exemple, il pourrait y avoir une procédure stockée qui s'appelle:
client_selon_adresse(adresse_ici)

C'est donc la procédure stockée qui est responsable de la validation de adresse_ici et du refus de répondre à la demande si les données sont incorrectes.

Le programmeur web peut perdre moins de sommeil sur la validation des entrées.

Si vous rêvez de devenir l'un des programmeurs qui écrivent des déclencheurs, des fonctions, et des procédures stockées, il est absolument nécessaire d'apprendre un langage de programmation "procédural" comme PL/SQL de Oracle, T-SQL de Microsoft SQL Server ou PL/pgSQL de PostgreSQL.

Ils sont tous un peu pareils, alors n'hésitez pas à choisir le langage qui vous convient.

Blocs anonymes

Si l'on est autorisé à exécuter le SQL sur une base de données, elle peut exécuter aussi du code "procédural" en forme de "blocs anonymes".

(C'est-à-dire, si ce code ne fait rien à la base de données que l'on pe serait pas autorisé à faire avec SQL.)

En tant qu'utilisateur, généralement je n'ai pas besoin de plus que ce que peut faire SQL. Mais une ou deux fois, j'ai eu besoin de faire des choses un peu plus délicates où un bloc anonyme PL/SQL m'offrait un bonus en puissance de calcul.

Pourtant

Si vous extrayez des données avec SQL dans un autre programme (Python, Java, etc.) il est possible que le langage de votre logiciel principal suffise pour effectuer les calculs que SQL ne peut pas.

Je vous conseille de choisir l'approche qui répond le mieux à vos besoins (y compris la sécurité).

Ressources pour apprendre le PL/SQL

1. Beginning PL/SQL: From Novice to Professional de Donald J. Bales.C'est une lecture légère, parfaite pour les débutants. Le seul problème, en tant que francophone, est que l'auteur fait beaucoup de blagues. Mais l'effort pourrait améliorer votre anglais en même temps que votre PL/SQL!
2. Oracle certification prep study guide for 1Z0-144: Oracle Database 11g: program with PL/SQL by Matthew Morris.Relativement court. Destiné aux personnes qui comprennent déjà la programmation.Il s'agit d'une version obsolète d'Oracle, mais c'est quand même un livre bien écrit et les principes fondamentaux n'ont pas beaucoup changé.
3. Database PL/SQL Language Reference by Oracle.C'est un manuel de référence, pas un tutoriel.

Désolée de ne pas connaître de ressources en français ou sur T-SQL, PL/pgSQL, etc. Prévenez-moi dans la section commentaires si vous en connaissez !