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 Plantevit}ne 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