PL/SQL

1. Qu’est-ce que le PL/SQL ?

Le PL/SQL est le langage procédural d’Oracle. Il constitue une extension au SQL qui est un langage ensembliste.

L’intérêt du PL/SQL est de pouvoir mélanger la puissance des instructions SQL avec la souplesse d’un langage procédural dans un même traitement.

Ces traitements peuvent être exécutés, soit directement par les outils Oracle (blocs anonymes), soit à partir d’objets de la base (procédures stockées et packages). 

Les avantages du PL/SQL sont multiples :

1. Variables locales

Déclaration

PL/SQL dispose de l’ensemble des types utilisables dans la définition des colonnes des tables dans le but de faciliter les échanges de données entre les tables et les blocs de code. Cependant, les étendues des valeurs possibles pour chacun de ces types peuvent être différentes de celles du SQL.

Il dispose également d’un certain nombre de types propres, principalement pour gérer les données numériques.

Enfin, PL/SQL permet de définir des types complexes basés soit sur des structures issues des tables, soit sur des descriptions propres à l’utilisateur.

Syntaxe

nom-de-variable [CONSTANT]  
type [[NOT NULL]:=expression] ;
 

CONSTANT

NOT NULL

expression

2. Types prédéfinis

1. Traitements conditionnels

If

Il permet l’exécution d’instructions en fonction du résultat d’une condition.

Syntaxe

IF condition1 THEN traitement1; 
[ELSIF condition2 THEN traitement2;] 
[ELSE  
       traitement3;] 
[END IF;]
 

Les opérateurs utilisés dans les conditions sont les mêmes que dans SQL : =; <, >, !, >=, <=, IS NULL, IS NOT NULL, BETWEEN, LIKE, AND, OR, etc.

Exemple

Si le client 10 est en cours de traitement, on le met à jour sinon on annule la transaction :

if  Vnocli = 10 THEN 
    UPDATE CLIENTS SET NOM = 'Dupont' where NOCLI = Vnocli ; 
    COMMIT ; 
else 
     ROLLBACK ; 
end if ;
 

CASE

L’instruction CASE permet une exécution conditionnelle comme l’instruction IF. Cependant, cette instruction CASE est particulièrement bien adaptée aux conditions comportant de nombreux choix différents. Elle permet une présentation plus lisible du code, donc moins de risque d’erreur. De plus, l’utilisation du CASE est susceptible d’améliorer les performances au cours de l’exécution.

Syntaxe

[<<étiquette>>] 
CASE element_de_selection 
  WHEN valeur1 THEN instructions1; 
  WHEN valeur2 THEN instructions2; 
  ... 
  [ELSE instructions;] 
END CASE [étiquette]; 
 
[<<étiquette>>] 
CASE 
  WHEN condition1 THEN instructions1; 
  WHEN condition2 THEN instructions2; 
  ... 
  [ELSE instructions;] 
END CASE [étiquette];
 

1. Définition

Le curseur est une zone de mémoire de taille fixe, utilisé par le moteur de la base Oracle pour analyser et interpréter tout ordre SQL.

Les statuts d’exécution de l’ordre se trouvent dans le curseur.

Il existe deux types de curseurs :

2. Étapes d’utilisation d’un curseur explicite

CURSOR nom_curseur IS ordre_select;
 

OPEN nom_curseur;
 

FETCH nom_curseur INTO liste_variables;
 

CLOSE nom_curseur;
 

FOR variable IN cursor LOOP 
--instructions ...

1. Énoncé du traitement

On veut pouvoir mettre à jour la quantité en stock de la table ARTICLES (REFART, DESIGNATION, PRIXHT, QTESTK) à partir des commandes en cours (ETATCDE = ’EC’) stockées dans les tables COMMANDES (NOCDE, NOCLI, DATCDE, ETATCDE) et LIGNESCDE (NOCDE, NOLIG, REFART, QTECDE).

Le traitement doit mettre à jour la colonne QTESTK de ARTICLES en otant les QTECDE de LIGNESCDE pour l’article. Il doit également y avoir mise à jour de ETATCDE à ’LI’ si toutes les quantités peuvent être livrées pour la commande (QTESTK > 0 après décrémentation).

Dans le cas où la quantité en stock de l’article devient négative, les mises à jour pour cette commande sont annulées.

Une table témoin est mise à jour pour chaque article à problème (non livrable) et pour chaque commande livrée entièrement.

2. Exemple

Script majliv.sql.

-- Livraison des commandes et mise à jour des stocks 
-- Création de la table TEMOIN 
create table TEMOIN(nocde number(6), texte char(60)); 
 
-- Bloc PL/SQL de mise à jour 
DECLARE 
  cursor ccde is select c.nocde, refart, qtecde 
    from ligcdes l,commandes c 
    where c.nocde=l.nocde 
      and c.etatcde='EC' 
    order by c.nocde; 
  vcde ccde%rowtype; 
  vqtestk articles.qtestk%type; 
  vnvqte vqtestk%type; 
  vtexte temoin.texte%type; 
  verr...