Bases de données 2

PL/SQL : les bases

D'après le cours d'Emmanuel Waller

Rappels

Les deux parties du cours

Création et gestion de la base
- Les problèmes BD :
  - liés à la construction de la base : modèle, conception, mise à jour, persistance, contraintes, indépendance des niveaux (L3), confidentialité
  - liés à l'interrogation de la base : interrogation (L3), grandes quantités
  - liés à la dynamique de la base : reprise sur panne, contrôle de concurrence
- Les traitements bas niveau en mode programme : PL/SQL
Accès à la base depuis un programme généraliste :
- Les problèmes MP étudiés à travers (outre PL/SQL) : Java, PHP
XML, XSQL, XSLT, intégration avec relationnel

Introduction

Qu'est-ce que c'est ?

« Programming Language for SQL »
PL/SQL = sous-ensemble SQL + langage procédural
Sous-ensemble SQL : gestion de l'instance seulement
Programmation pure : langage complet (variables, affectation, tests, boucles, fonctions, exceptions, etc.)
Programmation BD : curseurs, gestion erreurs, etc.
Rappel :
- Déclaratif : dire résultat voulu sans dire comment l'obtenir (SQL : pas de : variables, if, boucles)
- Procédural : construire soi-même le résultat

A quoi ça sert ?

Factoriser dans du code les séquences d'ordres que l'expert doit retaper plusieurs fois (principe des fonctions)
Expressivité :
- Il existe des fonctions qu'on ne peut pas écrire en SQL (ex : ancêtres)
- PL/SQL permet d'écrire toutes les fonctions calculables
Performances :
- Bloc PL/SQL : envoyé au serveur en une seule fois : compilé puis exécuté (échanges client-serveur)
- Compilé lors création si procédure stockée (avant runtime)

Architecture en couches

Séparer la gestion BD du reste de l'application
Traitements BD en PL/SQL dans la base (procédures stockées, triggers)
« Esprit objet » :
- Encapsulation des traitements dans la base
- Accès à base uniquement par appel de procédures stockées
- Procédures : partagées par modules applis utilisant cette base
- Conséquence : portabilité : toute la partie BD (données et traitements) de l'application portable (sur le même SGBD)

Principe

Le programmeur de l'application écrit des procédures et des fonctions PL/SQL.
Ces procédures sont compilées et stockées dans la base, d'où leur nom : « procédures stockées ».

Rem : on dira « procédure stockée » pour procédure ou fonction stockée
Elles sont appelées par les couches hautes de l'application

Modes d'utilisation

Appel des procédures stockées depuis :
- SQL*Plus : mode interactif
- Programmes généralistes Java, PHP, etc. : mode programme

Vocabulaire : base de données (revisitée)

Base contient :

Données
Code BD : procédures stockées PL/SQL
Méta-données dans dictionnaire de données : données, procédures, utilisateurs, événements, etc.

Exemples

PL/SQL, les bases

Exemples
Le langage :
- Partie programmation : bloc, types, variables, opérateurs, tests, boucles
- Partie BD : ordres SQL, types
Procédures stockées

Exemple

On considère un centre de loisir, représenté par une table loisir(nom, âge, activité).
Quand c'est l'anniversaire de quelqu'un, il faut :
- Augmenter son âge de 1
- Et toutes les personnes qui ont son âge font son activité
Écrire une procédure stockée qui prend en paramètre le nom de la personne dont c'est l'anniversaire, et effectue ce traitement.
- On suppose qu'il y en a exactement une.
Code, appel en mode intractif: démonsration

Brefs commentaires

PL/SQL :
- On reviendra sur tout cela en détail
- Create or replace
- Paramètres, corps, is
- Variables
- Ordres SQL (variante select)
- Casse pas signifiante

Devant les machines

Charger sous SQL*Plus provoque :
- Compile
- Stocke (si pas d'erreur de compilation)
Return + slash + return
Message :
- Correct
- Erreur => débuguer
Set serveroutput on

Exemple

Écrire une procédure stockée qui renvoie le nombre de lignes de la table :
- Rem : ce sera une fonction.
Code, appel
- Démonstration

Le langage

Le bloc PL/SQL

Séquence d'ordres SQL et instructions PL/SQL
Unité de base :

Structure des procédures stockées
Exécution hors procédures : compilé et exécuté en une seule fois

3 sections :
Commentaires :
- -- ceci est un commentaire, sur une ligne
- /* et cela aussi, sur plusieurs */

Un bloc PL/SQL est l’unité de base dans ce langage. Il contient généralement trois sections : 1. **Declare** : C’est ici que vous déclarez vos variables, constantes, curseurs, et autres éléments. Si vous créez une procédure stockée, le mot-clé `is` est utilisé à la place de `declare`. 2. **Begin** : C’est la section où les ordres SQL et les instructions PL/SQL sont placés. C'est ici que se déroule le traitement principal. 3. **Exception** : Cette section est optionnelle. Elle permet de gérer les erreurs et exceptions qui pourraient se produire pendant l'exécution du bloc. Une fois le bloc exécuté, toutes les instructions sont compilées et exécutées d’un seul coup, ce qui améliore l'efficacité par rapport aux échanges client-serveur.

Structure du bloc

declare :
- mots clés remplacés par is dans procédures stockées
- Variables, constantes, exceptions, curseurs
begin :
- ordres SQL, instructions PL/SQL, structures de contrôle
exceptions :
- traitement des exceptions
end

Quels ordres SQL dans PL/SQL ?

Interrogation : select
Mise à jour (instance) : insert, update, delete
Persistance et transactions
Fonctions : to_char, to_date, round, etc.
Rien d'autre

Syntaxe : identique à SQL (sauf certains cas)

Déclaration des variables

Dans section declare
Variable = nom + type (+ contrainte BD)
Types =
- Types BD : tous types (integer, varchar2, etc.)
- Types Programmation : non BD (ex : booléen), dérivé

Exemple

Programmation :

adresse varchar2(20);
x integer := 1;

BD :

nom varchar2(10) not null;

Types dérivés

Référence à une entité existante :
- Programmation : variable
- BD : colonne, table, curseur

Exemple :

-- programmation :
x integer;
y x%type;
-- BD :
vnom client.nom%type;
cli client%rowtype;

Utilisation des variables

Affectation à une variable de :
- Programmation : expression
- BD : résultat requête : select into, fetch into (curseur)
  - Rappel : sous SQL*Plus : à l'écran
  - Dans PL/SQL : dans une variable (dans curseur si plusieurs lignes)

Exemple (programmation)

x := 0;
vnom := 'Monsieur' || vnom;
y := (x + 5) * y;

Exemple (BD)

declare
vref varchar2(10);
  vprix articles.prixht%type; 
  cli client%rowtype;

begin
  select refart, prixht
    into vref, vprix
    from articles
    where nom = 'cravate';

  select * 
    into cli
    from client
    where nom = 'Toto';
end;

Résultat de la requête :

Exactement une ligne
Zéro ou plusieurs : erreur

Exemple (BD)

declare
  vnocli client.nocli%type := 10;
  vnom client.nom%type;
begin
  vnom := 'Dupont';
  update client
    set nom = vnom
    where nocli = vnocli;
end;

Portée des variables

Concept programmation : leur bloc PL/SQL

Il existe des cas plus généraux.

Opérateurs, tests

Opérateurs :

programmation : =, <,>, !, >=, <=, between, like, and, or, etc.
BD : is (not) null
(comme SQL)

Tests :

if vnocli = 10 then
    update . . . ; 
    insert . . . ;
else 
    delete . . . ;
end if;

Boucles

Exemple : créer des clients

for n in 100 . . 110 loop
    insert into client(nocli) values (n);
end loop;

n := 100;
while n <= 110 loop
    insert into client(nocli) values (n);
    n := n + 1;
end loop;

Procédures stockées

Procédures stockées : syntaxe

create or replace procedure p
    (x1 in | out t1, x2 . . .) is
    . . . -- déclarations
begin
    . . . -- corps
    [ exception . . . ] -- gestion des exceptions
end;

Out : passage par adresse

Fonction stockée

create or replace function f (...)
    return integer /* ou autre */ is
begin
    ...     
end;

Remarques

SQL : drop procédure p ou drop function f
Appel :
- Depuis bloc PL/SQL :
```
declare x char;
begin . . . suppr(x); . . . end;
```
- Sous SQL*Plus :
```
SQL> execute suppr('W');
```
- fonction : utiliser la valeur de retour

Résumé

Vocabulaire

Modes d'utilisation : appel procédures stockées depuis :
- Une procédure stockée
- Mode interactif
- Un programme généraliste
- bref, comme... un ordre SQL
Ordre BD =
- un ordre SQL
- appel de procédure stockée PL/SQL

Que faut-il se rappeler ?

PL/SQL :
- Conçu : couplage fort avec SQL et manipulations BD
  - (gestion des problèmes du mode programme)
- Expressivité : toutes fonctions calculables
Les parties de code purement BD d'une appli généraliste doivent être :
- Écrites en PL/SQL
- Stockées dans la base

Compétences à acquérir

Savoir écrire et appeler des procédures stockées utilisant :
- Ordres SQL
- Tests, boucles
Dans le cadre d'un cahier des charges