Cours de PL/SQL pour débutant
Pourquoi PL/SQL ?
PL/SQL = PROCEDURAL LANGUAGE/SQL
SQL est un langage non procédural
Les traitements complexes sont parfois difficiles à écrire si on
Marc Plantevitne peut utiliser des variables et les structures de
programmation comme les boucles et les alternatives
On ressent vite le besoin d’un langage procédural pour lier plusieurs requêtes SQL avec des variables et dans les structures de programmation habituelles
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 2
Principales Caractéristiques
Extension de SQL : des requêtes SQL cohabitent avec les structures de contrôle habituelles de la programmation structurée (blocs, alternatives, boucles).
La syntaxe ressemble au langage Ada ou Pascal.
Un programme est constitué de procédures et de fonctions.
Des variables permettent l’échange d’information entre les requêtes SQL et le reste du programme
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 3
Plusieurs fac¸ons d’affecter une valeur à une variable
Déclaration et initialisation
Nom variable type variable := valeur;
Initialisation
Nom variable := valeur;
Déclarations multiples interdites.
Exemples :
age integer; nom varchar(30); dateNaissance date; ok boolean := true;
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 1011
SELECT INTO
SELECT expr1,expr2, INTO var1, var2,
Met des valeurs de la BD dans une ou plusieurs variables var1, var2,
Le select ne doit retourner qu’une seule ligne
Avec Oracle il n’est pas possible d’inclure un select sans into dans une procédure.
Pour retourner plusieurs lignes, voir la suite du cours sur les curseurs.
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 12
CASE
CASE sélecteur
Le CASE renvoie une
WHEN expression1 THEN résultat1 valeur qui vaut résultat1
WHEN expression2 THEN résultat2
ou résultat2 ou Ce
ELSE résultat3
n’est pas une instruction.
END;
Les Boucles
LOOP instructions exécutables; EXIT[WHEN condition]; instructions exécutables;
END LOOP;
Obligation d’utiliser la commande EXIT pour éviter une boucle infinie, facultativement quand une condition est vraie.
WHILE condition LOOP instructions exécutables;
END LOOP; -- Tant que la condition est vraie
19 Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 20
FOR
FOR variable IN debut .. fin LOOP instructions;
END LOOP;
La variable de boucle prend successivement les valeurs de debut,debut + 1,debut + 2, , jusqu’à la valeur fin.
On pourra également utiliser un curseur dans la clause IN (dans quelques slides).
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 21
Les curseurs implicites Les curseurs explicites
Pour traiter les select qui renvoient plusieurs lignes.
Ils doivent être déclarés.
Le code doit les utiliser explicitement avec les ordres OPEN,
FETCH et CLOSE
OPEN moncurseur : ouvre le curseur.
FETCH moncurseur : avance le curseur à la ligne suivante. OPEN moncurseur : referme le curseur.
Le plus souvent on les utilise dans une boucle dont on sort quand l’attribut NOTFOUND du curseur est vrai.
On les utilise aussi dans une boucle FOR qui permet une utilisation implicite des instructions OPEN, FETCH et CLOSE.
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 28 Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 29
Exemple Curseurs Explicites
BEGIN open salaires;
LOOP
FETCH salaires into salaire;
EXIT when salaires%notfound;
IF salaire is not null THEN total := total + salaire; Ne pas oublier de
DBMS line(total); fermer le curseur.
END IF;
END LOOP;
CLOSE salaires;
DBMS line(total);
END;
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 30
Les Exceptions Exceptions Prédéfinies
Une exception est une erreur qui survient durant une exécution.
2 types d’exception :
prédéfinie par Oracle, définie par le programmeur.
Saisir une exception :
Une exception ne provoque pas nécessairement l’arrêt du programme si elle est saisie par un bloc (dans la partie
EXCEPTION
Une exception non saisie remonte dans la procédure appelante (ou` elle peut être saisie).
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 3738
Traitement des exceptions
BEGIN
EXCEPTION
WHEN NO DATA FOUND THEN
WHEN TOO MANY ROWS THEN
WHEN OTHERS THEN --optionnel
END;
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 39
Fonctions sans paramètre Fonctions avec paramètres
CREATE OR REPLACE FUNCTION nombre clients Seuls les paramètres IN (en lecture seule) sont autorisés pour les -- Déclaration du type de retour de la fonction fonctions.
RETURN NUMBER
IS create or replace function euro to fr(somme IN
BEGIN number)
DECLARE return number i NUMBER; IS
CURSOR get nb clients IS select count(*) from taux constant number := 6.55957;
clients; BEGIN return somme * taux;
BEGIN END; open get nb clients; /
fetch get nb clients INTO i; show errors; return i;
END; -- appel :
END; select euro to fr(10) from dual;
Le Langage PL/SQLExécution : Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 46 Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 47 select nombre clients() from dual;
Procédures et Fonctions
Suppression de procédures ou fonctions :
DROP PROCEDURE nom procedure DROP FUNCTION nom fonction
Table système contenant les procédures et fonctions : user source;
Les procédures et fonctions peuvent être utilisées dans d’autres procédures ou fonctions ou dans des blocs PL/SQL anonymes.
Les fonctions peuvent aussi être utilisées dans les requêtes SQL.
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 48
Tables Mutantes et Tables Contraignantes Une table mutante est une table en cours de modification par une opération déclenchante (UPDATE, DELETE, INSERTION) ou l’effet de DELETE CASCADE provenant de cette opération. Une table contraignante est une table qu’une opération déclenchante doit lire, soit directement via une commande SQL (UPDATE SET WHERE) ou indirectement pour une contrainte d’intégrité référentielle. | Exemple d’erreur CREATE OR REPLACE TRIGGER emp count AFTER DELETE ON emp FOR EACH ROW DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM emp; DBMS LINE(’On a ’|| n || ’employés dans la |
Les commandes SQL dans le corps d’un trigger ne peuvent base’); pas :
Lire (par une requête) ou modifier un table mutante d’une
opération déclenchante. DELETE FROM emp WHERE empno = 7499;
Changer des valuers sur les colonnes de clés (PRIMARY, On a l’erreur suivante :
FOREIGN, UNIQUE) d’une table contraignante. ORA-04091 : table is mutating, Ces restrictions permettent d’éviter la consultation d’une table trigger/function may not see it. dans un état transitoire et donc incohérent.
Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 55 Le Langage PL/SQL Commandes Les curseurs Les exceptions Procédures et fonctions Triggers 56
Le dictionnaire de données a des vues sur les triggers : USER TRIGGERS, ALL TRIGGERS, DBA TRIGGERS.
Exemple : SELECT trigger type, triggering event, table name FROM user triggers;
Le Langage PL/SQL | Commandes | Les curseurs | Les exceptions | Procédures et fonctions | Triggers | 57 |