Le Langage PL/SQL est une extension du SQL, qui offre

1. PL/SQL : Le Langage PL/SQL est une extension du SQL, qui offre un environnement procédural au langage SQL . Il permet entre autres: L'utilisation d'un sous ensemble du SQL La mise en œuvre de structures procédurales L'optimisation de l'exécution des requêtes.

2. Un programme PL/SQL est constitué d'un ou de plusieurs blocs. Chaque bloc comporte 3 sections : • La section Déclaration, introduite par le mot clé declare contient la déclaration des structures et des variables utilisés dans le bloc. - La section Corps du bloc, introduite par le mot clé begin contient les instructions du programme et éventuellement la section de traitement d'erreurs. Elle se termine par le mot clé end. - La section de traitements d'erreurs , introduite par le mot clé Exception contient les instructions de traitement d'erreurs. Cette section est facultative.

3. DECLARE Déclarations des variables locales au bloc, constantes, et curseurs BEGIN Instructions PL/SQL et SQL Possibilités de blocs imbriqués EXCEPTION Traitement des erreurs END;

4. Gestion des données : Deux classes de types de données : scalaire et composé • Scalaire : Char, varchar2, Date, number, boolean, smallint,…. Deux manières pour déclarer : - directe : nom_var type_var ; Ex : i smallint; - par référence à une colonne de table : nom_var nom_table.nom_colonne%type; Ex : f produit.reference%type;

5. Types composés : enregistrement et table + Enregistrement : RECORD Déclaration : - par référence à une structure de table : Nom_var nom_table%rowtype ; • - par énumération des rubriques, • dans ce cas il faut déclarer le type enregistrement : TYPE nom_type IS RECORD ( nom_champ type_champ ,……..); puis déclarer la variable : nom_variable nom_type;

6. + Table :structure d'éléments d'un même type scalaire. L'accès à un élément se fait grâce à un indice déclaré de type BINARY_INTEGER ; Déclaration en deux étapes : - déclarer le type : TYPE nom_type IS TABLE OF type_champ INDEX BY BINARY_INTEGER ; - puis la variable : nom_var nom_type;

7. * Définir une constante : • nom_var CONSTANT type := valeur; Affectation d'une valeur à une variable : - Affectation ::= nom_var := valeur ; table_nom(i) := 'DRISS' ; revenu.salaire := 10000; - Valeur résultat d'une requête : L'utilisation de la clause INTO de l'ordre SELECT permet d'affecter à une variable le résultat d'une requête. Cet ordre ne doit retourner qu'une seule valeur sinon il faut utiliser un curseur. SELECT liste_expressions into liste_variables from ……….;

8. Les Instructions de contrôle : Instruction conditionnelle : IF condition Then Instructions; ELSE Instructions; END IF ; SELECT liste_expressions into liste_variables from ……….;

9. Instructions itératives : • LOOP • …. • IF • EXIT ; • END IF ; • …… • END LOOP ; FOR indice IN valdebut ..Valfin LOOP ….. ….. END LOOP; • WHILE condition • LOOP • …. • …. • END LOOP;

10. Instructions d’affichage : Pour afficher un résultat à l’écran, on utilise l’instruction suivante : Dbms_output.put_line ( ‘message ..’||nom_var||…………); Remarque : l’affichage à l’écran par cette instruction nécessite l’activation du ‘serveur ‘ d’affichage par l’instruction : set serveroutput on ;

11. Exercice : Ecrire le programme PL/SQL qui permet de déterminer le nombre de triathlètes de la catégorie junior et d’afficher les messages suivants : Si ce nombre > 5 :afficher les jeunes sont nombreux, ils sont : xx ! sinon afficher les jeunes ne sont que : xx ! set serveroutput on; declare nombre number; begin nombre := 0; select count(numlicence) into nombre from triathlete where lower(categorie) = 'junior'; if nombre > 5 then dbms_output.put_line (' les jeunes sont nombreux, ils sont : '||nombre||' !'); else dbms_output.put_line (' les jeunes ne sont que : '||nombre||' !'); end if; end;

12. Les Curseurs : Dès l'instant où on exécute une instruction SQL, il y a création d'un curseur. Le curseur est une zone de travail de l'environnement utilisateur qui contient les informations permettant l'exécution d'un ordre SQL : - texte source de l'ordre SQL - forme "traduite" de l'ordre - tampon correspondant à une ligne résultat - statut - information de travail - information de contrôle L'utilisation d'un curseur est nécessaire pour traiter un ordre SELECT renvoyant plusieurs lignes.

13. Une telle utilisation nécessite 4 étapes : • Déclaration du curseur • 2. Ouverture du curseur • 3. Traitements des lignes • 4. Fermeture du curseur

14. 1 - Déclaration : dans la section DECLARE par la clause CURSOR : DECLARE CURSOR nom_curseur IS requête ; Un curseur peut être défini à l'aide de paramètres : CURSOR nom_curseur ( nom_param type,……) IS ……….. ; Exemple : precedent DECLARE CURSOR C1 is select designation from produit where prix <10 ; CURSOR C2 (Q number(4)) is select designation from produit where quantité >Q ;

15. 2 – Ouverture du curseur : OPEN nom_curseur; OPEN nom_curseur ( paramètre ); open C2(30); 3 – Fermeture du curseur : Close nom_curseur ;

16. 4 – Traitement des lignes : Les lignes obtenues par l'exécution de la requête SQL sont distribuées une à une, par l'exécution d'un ordre FETCH inclus dans une structure répétitive. Pour chaque ligne, cette instruction transfère les valeurs des attributs projetés par l'ordre SELECT dans des variables PL/SQL : FETCH nom_curseur INTO liste_var ; Ou FETCH nom_curseur into nom_enreg ;

17. Exemple : DECLARE CURSOR C1is select nom, sal from employes; V_nom employes.nom%type; V_sal employes.sal%type; BEGIN Open C1; LOOP FETCH c1 into V_nom, V_sal ; Exitwhen (c1%Notfound); // traitement END LOOP; Close C1; END;

18. Modification des Données : PL/SQL offre la possibilité de modifier ou de supprimer la ligne distribuée par la commande FETCH, en utilisant dans une clause : WHERE CURRENT OF nom_curseur Dans ce cas, la déclaration du curseur doit inclure la clause FOR UPDATE.

19. Exemple : DECLARE CURSOR C2 is select nom, sal from employes where n_emp >15 FOR UPDATE; V_nom employes.nom%type; V_sal employes.sal%type; BEGIN Open C2; LOOP FETCH c2 into V_nom, V_sal ; Exit when (c2%Notfound); If v_sal <10000 then Update employés set sal = v_sal*1.2 where current of c2; End if; END LOOP; END; Exercice : Ecrire le prg PL/SQL qui permet de modifier la distance à parcourir pour des triathlètes dont le numéro de licence est > à 400.

20. Procédures et fonctions stockées C’est un programme écrit en PL/SQL , qui peut être appelé : - en mode interactif - dans une application - dans d’autres procédures ou dans des déclencheurs Exercice : Ecrire la procedure PL/SQL qui permet d’afficher, les informations d’un triathlète donné par son numéro mais en les affichant ligne par ligne . Structure d’une procédure : Create or replace procedure nom_proc (argument1 mode type_argument1,……) [is | as] bloc ; Remarque : mode définit le type de l’argument : - IN : argument en entrée - OUT : argument en sortie - IN OUT : argument en entrée sortie

21. Structure d’une fonction : Create or replacefunction nom_func (argument1 mode type_argument1,……) return type_retourné [is | as] bloc ; Remarque : la fonction comporte une instruction obligatoirement dans le bloc une instruction return qui renvoie la variable résultat de la fonction. return (nom_var_résultat);

22. Exemple de procédure : ajouter un triathlète : create or replace procedure nv_triathlete ( nlic IN triathlète.numlicence%type, nomat IN triathlète.nomathlète%type, cat IN triathlète.categorie%type) is begin insert into triathlete values( nlic,nomat,cat); commit work; end nv_triathlete;

23. Exemple de fonction : retourner le nombre de triathlètes d’une catégorie donnée : create or replace function nb_categorie ( cat IN triathlète.categorie%type) return integer is nb integer; begin select count(numlicence) into nb from triathlete where categorie =cat; return nb; end nb_categorie;

24. Appels et utilisation des procédures et fonctions : • En mode interactif : • EXECUTE nom_proc(param1, param2,…); • EXECUTE :var_locale := nom_fonction(param1,param2,….); • Exemple : • execute nv_triathlete(450,’hamidi’,’senior’); • execute :nbr := nb_categorie(‘senior’); • A partie d’un bloc PL/SQL : • nom_proc(param1, param2,…); • nom_fonction(param1,param2,….); • Exemple : • begin • nv_triathlete(458,’hamida’,’junior’); • nbr := nb_categorie(‘senior’); ……..end;

25. A partir d’un autre schéma : • SQL>EXECUTE nomschema.nomproc(param1, param2,…); Exercice : Ecrire la procedure PL/SQL qui permet d’afficher, les informations d’un triathlète donné par son numéro mais en les affichant ligne par ligne .

26. Gestion des erreurs : IL s’agit d’effectuer un traitement approprié aux erreurs qui apparaissent lors de l’exécution d’un bloc PL/SQL. Les erreurs sont de deux types : - les erreurs standards détectées par le moteur PL/SQL. Dans ce cas, il y a erreur avec un code ORA XXXXX et le système reprend la main. - les anomalies générées par l’utilisateur. Gestion des erreurs standards : La procédure de traitement se définit dans la section Exception: ………. Exception when nom_erreur then // traitement erreur end;

27. LISTE D’ERREURS : NO_DATA_FOUND DUP_VAL_ON_INDEX VALUE_ERROR INVALID_CURSOR INVALID_NUMBER PROGRAM_ERROR LOGIN_DENIED TOO_MANY_ROWS ZERO_DIVIDE

28. Gestion des erreurs utilisateurs: • Il faut déclarer l’exception : • DECLARE • NOM_ANOMALIE EXCEPTION. • Il faut la traiter : idem que les Exceptions standards • -Il faut la déclencher : • Une exception utilisateur doit explicitement être déclenchée dans la procédure PL/SQL par l’ordre RAISE: • if ………..then • RAISE NOM_ANOMALIE ;