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.

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

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

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 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 */

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

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

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

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 : 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

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