Programmation PL/SQL Introduction

1. Programmation PL/SQLIntroduction

2. PL/SQL ? • ProceduralLanguage extensions to SQL • Version 8.0 • Disponible dans deux environnements: • Les outils (Forms, Reports, Graphics) • Au serveur de BD

3. Vue d’ensemble • Inclut les énoncés SQL. • Langage avec une structure de bloc. • Déclare des variables et des constantes. • Affecte des valeurs aux variables. • Opérateurs logiques. • Fonctions built-in.

4. Vue d’ensembleStructures de contrôle • if - then • if – then – else • if – then - elsif • loop - end loop • for – end loop • while – end loop • exit • goto • null

5. Vue d’ensembleLes curseurs • On peut définir des zones de travail contenant plusieurs enregistrements (les curseurs) • DECLARE CURSOR • OPEN • FETCH • CLOSE

6. Vue d’ensembleTraitement d’erreurs • Une erreur ou un avertissement se nomme une exception • Dans nos programmes nous pouvons mettre une : Section EXCEPTION • EXCEPTION • WHEN … THEN

7. Vue d’ensemble • Sous-programme • Procédure externe • Package • Abstraction de données • Collections • Structures • Types • Déclarations et manipulation d’objets

8. Programmation PL/SQLNotions fondamentales

9. Structures de blocs • 3 sections • Section déclarative (les variables) • Section exécutable (le code) • Section des exceptions • La section exécutable est obligatoire • Différentes sortes de blocs • Anonyme, nommé, procédure ou fonction, déclencheur

10. Structures de blocs(anonyme) DECLARE Section déclarative (les variables) BEGIN Section exécutable (le code) EXCEPTION Section des exceptions (si une erreur) END;

11. Structures de blocs exemple DECLARE V_NoEmp NUMBER(4) := 1010; V_NomE VARCHAR2(20); --variable BEGIN /* Retrouver les infos de l'élève */ SELECT NomE INTO V_NomE FROM Emp WHERE NoEmp = V_NoEmp; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO TableErreur (ColonneInfo) VALUES (‘Employé 1010 n''existe pas'); END;

12. Commentaires --sur une ligne ∕*sur plusieurs lignes physiques *∕

13. Nom d ’objet • Maximum 30 caractères • tous significatifs • Commence par une lettre • Peut contenir • $ • _ • # • Pas d ’espace • Pas de différence entre maj et min

14. Littéraux • Nombre • 415, 21.6, 3.05 E19, null, … • chaîne de caractères • ‘ chaîne de caractères ’ • ‘ 31-jan-97 ’ • null • booléen • TRUE • FALSE • NULL

15. Type de variables 38 types différents • Numérique • NUMBER • Caractère • VARCHAR2, CHAR • Booléen • BOOLEAN • Date Heure • DATE

16. Déclaration de variables Nom_Var Type [CONSTANT] [NOT NULL] [:=valeur] Exemples : • V_DateEmbauche DATE; • V_AssezDonnees BOOLEAN; • V_RevenueTot NUMBER (15,2); • V_LongPara VARCHAR2(2000); • C_Date CONSTANT DATE := ‘ 15-avr-99 ’; • V_Temp NUMBER NOT NULL := 0; • V_Compte NUMBER := 0; • V_Compte NUMBER DEFAULT 0; • C_ValMin CONSTANT NUMBER(5) := 10; • V_NoEmpEmp.NoEmp%TYPE; (Prend le type de la colonne NoEmp de la table Emp et l’applique à la variable V_NoEmp.

17. Les opérateurs

18. Les opérateurs relationnels

19. Les autres opérateurs • IS NULL • Compare avec la valeur NULL • IF V_Temp IS NULL THEN … • LIKE • Compare avec une valeur contenant des frimes • V_Nom LIKE ‘L%SE’ • (permet Lise, Lyse ou Louise) • BETWEEN • IN

20. Le SQL permis • Seulement les énoncés de DML (Data Manipulation Language) • SELECT • INSERT • UPDATE • DELETE

21. INSERT • INSERT en SQL INSERT INTO TABLE Classes (Dept, Cours, Groupe, Description, Max_Ele, Inscrits, Local) VALUES (‘INF’,101, 2901, ‘Intro prog’,30, 28, ‘C5:29’); • INSERT en PL/SQL DECLARE V_TexteClasses.Description%TYPE; BEGIN V_Texte := ‘Intro prog’; INSERT INTO Classes (Dept, Cours, Groupe, Description,Max_Ele, Inscrits, Local) VALUES (‘INF’,101, 2901, V_Texte, 30, 28, ‘C5:29’); END;

22. SELECT Le SELECT doit retourner une ligne sinon il y aura erreur (EXCEPTION) SELECT Nom_Colonne INTO Variable FROM Table WHERE Clause_Where; Exemple: DECLARE V_DeptClasses.Dept%TYPE; V_CoursClasses.Cours%TYPE; BEGIN SELECT Dept, Cours INTO V_Dept, V_Cours FROM Classes WHERE Local = ‘C5.29’; END;

23. INSERT + SELECT DECLARE V_NoEleve.No%TYPE; BEGIN SELECT Seq_EleveId.NEXTVALINTO V_No FROM DUAL; INSERT INTO Eleve(No,Nom,Prenom) VALUES (V_No, ‘Tard’, ‘Guy’); INSERT INTO Eleve(No,Nom, Prenom) VALUES (Seq_EleveId.NEXTVAL, ‘Polo’, ‘Marco’); END;

24. DBMS_OUTPUT • Package permettant des sorties dans SQL*Plus venant de procédures PL/SQL • Il faut obligatoirement avoir: • SET SERVEROUTPUT ON PROCEDURE Prc_Sal (P_idNUMBER) IS V_TempSal NUMBER; BEGIN SELECT Salaire INTO V_TempSal FROM Employe WHERE id = P_id; DBMS_OUTPUT.PUT_LINE ('Salaire=‘ || TO_CHAR(V_TempSal)); END;

25. Les énoncés decontrôle

26. IF THEN IF condition THEN une séquence d’instructions END IF; Exemple: IF V_TotalVente> V_Quota THEN UPDATE Employe SET Salaire = Salaire + V_Bonus WHERE id = V_Temp; END IF;

27. IF THEN ELSE IF condition THEN une séquence d’instructions ELSE une séquence d’instructions END IF; Exemple: IF V_TypeTrans = ‘CR’ THEN UPDATE Compte SET Balance = Balance + V_Credit WHERE …; ELSE UPDATE Compte SET Balance = Balance – V_Credit WHERE …; END IF;

28. IF THEN ELSIF IF condition THEN une séquence d’instructions ELSIF condition THEN une séquence d’instructions ELSE une séquence d’instructions END IF; Exemple: IF V_TotalVentes > 500000 THEN V_Bonus:=15000; ELSIF V_TotalVentes > 50000 THEN V_Bonus:=5000; ELSE V_Bonus:=1000; END IF;

29. Les exceptions • Quand une erreur arrive on dit qu’une exception s’est produite • Si nécessaire, on traite les exceptions dans la section EXCEPTION • Il y a des exceptions: • Pré-définies • Définies par l’usager

30. Exceptions Pré-définies • ACCESS_INTO_NULL • DUP_VAL_ON_INDEX • INVALID_NUMBER • LOGIN_DENIED • NO_DATA_FOUND • NOT_LOGGED_ON • TOO_MANY_ROWS • VALUE_ERROR • ZERO_DIVIDE • OTHERS

31. Exemple DECLARE V_TempSal NUMBER; BEGIN SELECT salaire INTO V_TempSal FROM Employe WHERE Nom = ‘Tremblay’; … EXCEPTION WHEN NO_DATA_FOUND THEN Message(‘Tremblay n’’existe pas’); WHEN TOO_MANY_ROWS THEN Message(‘Il y a plusieurs Tremblay’); END;

32. Exceptions Usager • On utilise une exception usager lorsque l’on rencontre un cas particulier (un cas à tous les 2 ans par exemple) • Déclarations DECLARE Nom_Exeption EXCEPTION; … • Déclenchement BEGIN IF Condition THEN RAISE Nom_Exception; … • Code EXCEPTION WHEN Nom_Exception THEN Traitement de l’exeption; END; …

33. Procédures et Fonctions de base de données

34. Créer une Procédure • Utiliser un éditeur pour écrire un énoncé SQL • CREATE PROCEDURE • Transporter l’énoncé dans SQLPLUS. • Pour détruire la procédure • DROP PROCEDURE nom;

35. Syntaxe de l’énoncé SQL CREATE [OR REPLACE] PROCEDURE Nom_fct(Paramètres[,…]) IS [Déclarations] BEGIN Énoncés; [EXCEPTION Traitement d ’exception;] END;

36. Paramètred ’une procédure • Il est possible d’utiliser les paramètres pour retourner de l’information (passage par valeur ou référence). • Nom_Paramètre [ IN | OUT | INOUT ] Type [ := valeur par défaut ] • pas de restriction sur le type Ex. NUMBER(4) - - invalide NUMBER - - valide • IN : valeur transférée vers la procédure mais non retournée. • OUT : valeur retournée uniquement. • INOUT : valeur transféré et retournée. • Par défaut le préfix IN est appliqué.

37. Exemple de paramètres • Exemple CREATE PROCEDURE Verif(P_Nom IN VARCHAR2, P_No OUT NUMBER) IS Déclaration BEGIN Énoncées; [EXEPTION Traitement des exceptions; END;]

38. Créer une fonction • Utiliser l’énoncé SQL • CREATE FUNCTION CREATE [OR REPLACE] FUNCTION Nom_fct(Paramètres[,…])RETURN Type IS Déclarations BEGIN Enoncés; RETURN …; [EXCEPTION Traitement d ’exception;] END; • Pour détruire la procédure • DROP FUNCTION Nom_fct;

39. Appel d’uneprocédure ou fonction • D’une autre fonction ou procédure. • D’un outils interactif • ex: SQL PLUS. • D ’une application • Formbuilder • programme avec pré-compilateur • Dans un énoncé SQL (pour une fonction).

40. Appel de SQLPLUS PROCEDURE • EXECUTE Nom_fct(Paramètre[,…¸]); Exemple: • soit la procédure Fct_Augmt_Sal(P_NoEmp, P_Montant) • EXECUTE Fct_Augmt_Sal (1043,200);

41. Appel de SQLPLUSFONCTION Créer une variable session VARIABLE V_Montant NUMBER Exécuter la fonction EXECUTE :V_Montant := Fct_Revenu(1243);

42. Appel d’une fonctiond’un énoncé SQL • Dans SELECT, INSERT, UPDATE, DELETE, comme les fonctions SQL régulières SELECT Nom, TO_DATE(date_embauche), Revenu(id) FROM employe ORDER BY Revenu(id);

43. L ’énoncé RETURN • Met fin à l’exécution d ’une procédure ou fonction. • Fonction: • RETURN (valeur) • Procédure: • RETURN

44. PL/SQL Avancé Les variables simples Les variables de type enregistrement Structure itérative (boucles) Définition et manipulation d'un curseur

45. Variable simple Exemple 1: V_NoEmpemploye.id%TYPE; (variable ayant le même type que la colonne id de la table employe) Exemple 2: V_EssaiV_NoEmp%TYPE; (variable du même type qu'une autre variable)

46. Boucles SANS CONDITION LOOP Séquence d ’instructions END LOOP; on sort de la boucle avec: EXIT WHEN ou EXIT Exemple1: DECLARE … BEGIN LOOP SELECT… EXIT WHEN V_Salaire > 200; END LOOP; END;

47. Boucle sans condition Exemple2 On cherche le nom et le salaire du premier employé dont le salaire est plus grand que 20000 et on les inscrit dans la table Tempo. DECLARE V_SalEmploye.Salaire%TYPE; V_NomEEmploye.Nom%TYPE; BEGIN LOOP SELECT Nom, Salaire INTO V_Sal, V_NomE FROM Employe; IF V_Sal > 20000 THEN EXIT; END IF; END LOOP; INSERT INTO Tempo VALUES(V_Sal, V_NomE); COMMIT; END;

48. Boucles avec CONDITION WHILE Condition LOOP Séquence d ’instructions LOOP; Exemple: On cherche le nom et le salaire du premier employé dont le salaire est supérieur à 20000 et on les inscrit dans la table tempo. DECLARE V_Sa; Employe.Salaire%TYPE; V_NomEEmploye.Nom%TYPE; BEGIN WHILE V_Sal < 20000 LOOP SELECT Salaire,NomINTO V_Sal,V_ NomE FROM Employe; END LOOP; INSERT INTO Tempo VALUES(V_Sal, V_NomE); COMMIT; END;

49. Boucles avec CONDITION FOR V_Compteur IN [REVERSE] BorneInf..BorneSup LOOP Séquence d'instructions; END LOOP; • Bornes: variables ou des constantes numériques • Si la variable compteur n'est pas définie à l'exécution de la boucle, elle le sera implicitement • exemple: V_Limite := 50; FOR V_Compteur IN 1 ..V_Limite LOOP Séquences d'instructions; END LOOP; Note : l’incrémentation ou la décrémentation ne peut être que de 1

50. Définition et utilisation du curseur • L'utilisation d'un curseur nous permet de manipuler ligne par ligne le résultat d'une requête qui retourne plusieurs lignes. Le curseur pointe sur la ligne courante de l'ensemble des lignes. • Mot clef CURSOR dans la section DECLARE d'un bloc d'instruction PL/SQL • CURSOR NomDuCurseur IS Requete • Exemple 1: CURSOR Cur_Vente IS SELECT Numero,Detail,Quantite FROM Produit;