BASES DE DONNEES RELATIONNELLES
PL/SQL FOR ORACLE
LES FONDAMENTAUX
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
MOTIVATION
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PROBLEME
REGLE DE GESTION:
Le salaire d’un employé ne doit jamais baisser!!!
COMMENT IMPLEMENTER UNE TELLE REGLE DU METIER?
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
REGLE DE GESTION: le salaire d’un employé ne doit jamais baissé!!!
INTERCEPTION
Update employe
Set salaire=salaire*0.80
Where …..
RECUPERATION DE LA NOUVELLE VALEUR: NEWSAL
RECUPERATION DE LA VALEUR DANS LA BD: OLDSAL
OUI MAJ
SI NEWSAL>OLDSAL
NON
ECHEC: AVORTER LE UPDATE
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SQL EST UN LANGAGE INCOMPLET
SQL NE PERMET PAS D’IMPLEMENTER TOUTES LES REGLES METIER
TOUTES LES REGLES DU METIER DOIVENT ETRE IMPLEMNTEES AU NIVEAU DU SCHEMA DE LA
BASE DE DONNES
LES LANGAGES DE PROGRAMMATION SONT COMPLETS
SQL EST LA SEULE INTERFACE POUR ACCEDER A UNE BD RELATIONNELLE
POUR FAIRE LES BD RELATIONNELLES:
SQL + LP
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Avantages de PL / SQL
• Prise en charge de SQL
• Prise en charge de la programmation
orientée objet
• Meilleure performance
• Une productivité accrue
• La portabilité
• L'intégration très forte avec Oracle
• Haute sécurité
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
STRUCTURE D’UN BLOC PL/SQL
BLOC PL/SQL
Partie Partie Partie
déclarative exécutable exception
(Obligatoire) (Optionnelle)
(Optionnelle)
DECLARE BEGIN … END EXCEPTION
• Chaque instruction se termine par un point virgule
• Les commentaires sont possibles /* */.
• Possibilité d’imbrication des blocs
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
DECLARE --section optionnelle
déclaration variables, constantes, types, curseurs,...
BEGIN --section obligatoire
contient le code PL/SQL
EXCEPTION --section optionnelle
traitement des erreurs
END; --obligatoire
REMARQUE
LA PORTEE DES VARIABLES EST LA MEME QUE DANS LES LANGAGES DE PROGRAMMATION
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES VARIABLES
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
nom variable [CONSTANT] type [ [NOT NULL] [:= expression | DEFAULT expression ];
nom variable représente le nom de la variable composé de lettres, chiffres, $, _ ou #
Le nom de la variable ne peut pas excéder 30 caractères
CONSTANT indique que la valeur ne pourra pas être modifiée dans le code du bloc
PL/SQL
NOT NULL indique que la variable ne peut pas être NULL, et dans ce cas expression doit
être indiqué.
type représente de type de la variable correspondant à l'un des types suivants :
Remarque
Si une variable est déclarée avec l’option CONSTANTE, elle doit être initialisée
Si une variable est déclarée avec l’option NOT NULL, elle doit être initialisée
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemples de types de bases PL/SQL
TYPE SEMANTIQUE EXEMPLE
NUMBER[(e,d)] Nombre réel avec e chiffres significatifs stockés et d décimales Nom_variable NUMBER( 9, 2 ) := 0;
PLS_INTEGER Nombre entier compris entre -2 147 483 647 et +2 147 483 647 Nom_variable PLS_INTEGER := 0;
CHAR [(n)] Chaîne de caractères de longueur fixe avec n compris entre 1 et Nom_variable CHAR( 1 );
32767 (par défaut 1)
VARCHAR2[(n)] Chaîne de caractères de longueur variable avec n compris entre 1 Nom_variable VARCHAR2( 20 );
et 32767
BOOLEAN Nom_variable BOOLEAN NOT NULL := TRUE;
DATE Nom_variable DATE := SYSDATE;
LONG Chaîne de caractères de longueur variable avec au maximum
32760 octets
CONSTANT les constantes sont des identificateurs associés à une valeur fixe CONSTANT pi NUMBER := 3.14159;
qui ne peut pas être modifiée pendant l'exécution du programme.
ROWID Permet de stocker l'adresse absolue d'une ligne dans une table
sous la forme d'une chaîne de caractères
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
L’Attribut SUBTYPE: SUBTYPE nom_sous_type IS type ;
Exemple:
SUBTYPE nom_employe IS VARCHAR2(20) NOT NULL:=‘inconnu’;
nom nom_employe;
L’Attribut %TYPE :
nom_variable nom_table.nom_colonne%TYPE ;
nom_variable nom_variable_ref%TYPE ;
Exemple:
Nom E_EMPLOYE.NOM%TYPE;
Dat_COM DATE;
Dat_LIV Dat_COM%TYPE;
L’Attribut %ROWTYPE: nom_variable nom_table%ROWTYPE ;
Exemple:
EMPLOYE E_EMPLOYE%ROWTYPE;
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES ENREGISTREMENTS
Un enregistrement PL/SQL est une structure de données composée de plusieurs champs,
chacun pouvant être d’un type différent.
Pour utiliser un enregistrement, vous devez d'abord le définir puis déclarer une variable de ce
type.
Il existe 3 types d'enregistrements :
• basés sur une table
• sur un curseur
• définis par un programme.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TYPE nom_type_rec IS RECORD (
nom_champ1 type_élément1 [[ NOT NULL] := expression ],
nom_champ2 type_élément2 [[ NOT NULL] := expression ],
…
nom_champN type_élémentN[[ NOT NULL] := expression ]
);
Nom_variable nom_type_rec ;
Exemple:
TYPE T_REC_EMP IS RECORD (
Num E_EMPLOYE.NO%TYPE,
Nom E_EMPLOYE.NOM%TYPE,
Pre E_EMPLOYE.PRENOM%TYPE
);
EMP T_REC_EMP ;
ACCES:
[Link] [Link] et [Link]
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
ASSIGNATION DES VARIABLES (AFFECTATION)
En PL/SQL, l'affectation est réalisée à l'aide de l'opérateur :=.
Cet opérateur est utilisé pour attribuer une valeur à une variable.
VARIABLE := EXPRESSION
•Lors de la déclaration
•Dans le bloc PL/SQL
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
ASSIGNATION DES VARIABLES (AFFECTATION)
MON_NUM:= 10 ;
MA_CHAINE := 'Chaîne de caractères' ;
MA_TAXE :=PRIX*TAUX;
MON_BOOLEAN := FALSE; MON_BOOLEAN := (NOM=‘toto’);
BONUS := SALAIRE * 0.10;
MA_LIMITE_BUDGET CONSTANT REAL := 5000.00;
MA_DATE:=‘12/12/2012’
MON_DEP := [Link];
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
AFFICHAGE DES VALEURS DES VARIABLES
DBMS_OUTPUT.PUT_LINE est une procédure fournie par Oracle permettant d’afficher du texte
depuis un bloc PL/SQL vers la sortie standard (console).
Elle est surtout utilisée pour le débogage, le suivi d’exécution ou l’affichage de messages
intermédiaires.
DBMS_OUTPUT.PUT_LINE(expression);
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
ACTIVATION DU SERVEUR D’AFFICHAGE:
SQL> SET SERVEROUTPUT ON
EXEMPLES:
MA_CHAINE := 'Chaîne de caractères' ;
DBMS_OUTPUT.PUT_LINE (‘Affichage de la valeur de la chaine: '|| MA_CHAINE);
DBMS_OUTPUT.PUT_LINE (‘Le prix TTC'|| PRIX*TAUX);
DBMS_OUTPUT.PUT_LINE (‘NOM:’||nom_emp ||’ prenom : ‘||pre_emp||’DT Naissance’||
DT)
|| est l’opérateur de concaténation de chaînes.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
AFFECTATION DES VARIABLES A PARTIR D’UNE BD
Schéma logique la BD exemples
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SELECT <COLONNE_OU_TUPLE> INTO <VAR>
FROM NOM_TABLE
WHERE CONDITION;
• Retrouver des lignes de la base de données avec le SELECT
• La clause INTO est obligatoire.
• Une seule ligne doit être retournée.
• Toute la syntaxe du SELECT est disponible.
Exemple 1:
SQL> DECLARE
2 NOM_EMP E_CLIENT.NOM%TYPE;
3 BEGIN
4 SELECT NOM INTO NOM_EMP
5 FROM E_CLIENT
6 WHERE NO=1;
7 DBMS_OUTPUT.PUT_LINE ('Le nom du client NO 1 est '|| NOM_EMP);
8 END;
9 /
Le nom du client NO 1 est Idrissi
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemple 2:
SQL> DECLARE
2 TYPE T_EMP IS RECORD (
3 NOM_EMP E_CLIENT.NOM%TYPE,
4 PRE_EMP E_CLIENT.PRENOM%TYPE
5 );
6 EMP T_EMP;
7 BEGIN
8 SELECT NOM, PRENOM INTO EMP
9 FROM E_CLIENT
10 WHERE NO=1;
11 DBMS_OUTPUT.PUT_LINE ('Le nom du client NO 1 est '|| EMP.NOM_EMP);
12 DBMS_OUTPUT.PUT_LINE ('son prénom est '|| EMP.PRE_EMP);
13 END;
14 /
Le nom du client NO 1 est Idrissi
son prénom est Mohammed
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemple 3:
SQL> DECLARE
2 EMP E_CLIENT%ROWTYPE;
3 BEGIN
4 SELECT * INTO EMP
5 FROM E_CLIENT
6 WHERE NO=1;
7 DBMS_OUTPUT.PUT_LINE ('client NO 1 est :');
8 DBMS_OUTPUT.PUT_LINE ('NOM '|| [Link]);
9 DBMS_OUTPUT.PUT_LINE ('PRENOM '|| [Link]);
10 DBMS_OUTPUT.PUT_LINE ('TELEHPONE '|| [Link]);
11 DBMS_OUTPUT.PUT_LINE ('ADRESSE '|| [Link]);
12 DBMS_OUTPUT.PUT_LINE ('VILLE '|| [Link]);
13 DBMS_OUTPUT.PUT_LINE ('PAYS '|| [Link]);
14 DBMS_OUTPUT.PUT_LINE ('CP_POSTAL '|| EMP.CP_POSTAL);
15 DBMS_OUTPUT.PUT_LINE ('COMMENTAIRE '|| [Link]);
16 END;
17 /
client NO 1 est :
NOM Idrissi
PRENOM Mohammed
TELEHPONE O60000000
ADRESSE Rue 1, N° 23
VILLE Rabat
PAYS Maroc
CP_POSTAL 5000
COMMENTAIRE Pas de commentaire
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemple 4:
SQL> DECLARE
2 NOM_EMP VARCHAR2(20);
3 BEGIN
4 SELECT NOM INTO NOM_EMP
5 FROM E_CLIENT Exemple 5:
6 WHERE NO=99; SQL> DECLARE
7 END; 2 NOM_EMP VARCHAR2(20);
8 / 3 BEGIN
ERREUR à la ligne 1 : 4 SELECT NOM INTO NOM_EMP
ORA-01403: aucune donnée trouvée 5 FROM E_CLIENT
ORA-06512: à ligne 4 6 WHERE NO=1 OR NO=2;
7 END;
8 /
ERREUR à la ligne 1 :
ORA-01422: l'extraction exacte ramène plus que le
nombre de lignes demandé
CURSEUR SOLUTION ORA-06512: à ligne 4
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SAISIE DE VALEUR EN LIGNE POUR LES VARIABLES (UNIQUEMENT POUR LES TESTS)
SQL> DECLARE
2 NO NUMBER(4):=&L_NO;
Exemple 1: exécution
3 NOM VARCHAR2(20):='&L_NOM'; Variables avec
4 SAL NUMBER(10,2):=&L_SAL; substitution de
5 DT_REC DATE:='&L_DATE'; valeurs
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('NUMERO: ' || NO);
8 DBMS_OUTPUT.PUT_LINE ('NOM: ' || NOM);
9 DBMS_OUTPUT.PUT_LINE ('SALAIRE: ' || SAL);
10 DBMS_OUTPUT.PUT_LINE ('DATE: ' || DT_REC);
11 END;
12 /
Entrez une valeur pour l_no : 1
ancien 2 : NO NUMBER(4):=&L_NO;
nouveau 2 : NO NUMBER(4):=1;
Entrez une valeur pour l_nom : toto
ancien 3 : NOM VARCHAR2(20):='&L_NOM';
nouveau 3 : NOM VARCHAR2(20):='toto';
Entrez une valeur pour l_sal : 890.60
ancien 4 : SAL NUMBER(10,2):=&L_SAL;
nouveau 4 : SAL NUMBER(10,2):=890.60;
Entrez une valeur pour l_date : 21/11/2021
ancien 5 : DT_REC DATE:='&L_DATE';
nouveau 5 : DT_REC DATE:='21/11/2021';
NUMERO: 1
NOM: toto
SALAIRE: 890,6
DATE: 21/11/21
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemple 2: exécution
Désactivation SQL> SETVERIFY OFF; --pour ne pas afficher les anciennes
de la
vérification des
valeurs &…
variables SQL> SET SERVEROUTPUT ON
SQL> Activation
SQL> DECLARE d’Affichage
2 NO NUMBER(4):=&L_NO;
3 NOM VARCHAR2(20):='&L_NOM'; Variables avec
4 SAL NUMBER(10,2):=&L_SAL; substitution de
5 DT_REC DATE:='&L_DATE'; valeurs
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('NUMERO: ' || NO);
8 DBMS_OUTPUT.PUT_LINE ('NOM: ' || NOM);
9 DBMS_OUTPUT.PUT_LINE ('SALAIRE: ' || SAL);
10 DBMS_OUTPUT.PUT_LINE ('DATE: ' || DT_REC);
11 END;
12 /
Entrez une valeur pour l_no : 1
Entrez une valeur pour l_nom : toto
Entrez une valeur pour l_sal : 890.60
Entrez une valeur pour l_date : 21/11/2021
NUMERO: 1
NOM: toto
SALAIRE: 890,6
DATE: 21/11/21
ORACLE Procédure PL/SQL terminée avec succès. PL/SQL
BASES DE DONNEES RELATIONNELLES
STRUCTURES DE CONTRÔLE
Modifier le déroulement logique des instructions en utilisant des structures de contrôle
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
IF - THEN - ELSE - END IF
IF condition THEN
instruction1 ;
instruction 2 ;
……..
instruction 2 ;
END IF;
PLSQL IF-THEN-END IF: Exemple 1
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 x integer := 10; y integer := 15;
3 BEGIN
4 IF x<Y THEN
5 DBMS_OUTPUT.PUT_LINE (x || ' < ' ||y);
6 END IF ;
7 END;
8 /
10 < 15
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
IFBASES DE DONNEES
condition1 THENRELATIONNELLES
instruction1;
instruction 2;
ELSE
instruction3;
instruction4;
instruction 5;
END IF;
PLSQL IF-THEN-ELSE-END IF: Exemple 2
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 x integer := 20; y integer := 15;
3 BEGIN
4 IF x<Y THEN
5 DBMS_OUTPUT.PUT_LINE (x || ' < ' ||y);
6 ELSE
7 DBMS_OUTPUT.PUT_LINE (x || ' >= ' ||y);
8 END IF ;
9 END;
10 /
20 >= 15
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
IF …. THEN … ELSIF … ELSE ... END IF
IF condition1 THEN
instruction1;
instruction 2;
ELSIF condition2 THEN
instruction 3;
instruction 4;
ELSIF condition3 THEN
instruction 5;
instruction 6;
ELSE instruction 7;
END IF;
• ELSIF en un SEUL mot
• END IF en DEUX mots
• Une seule clause ELSE est permise
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PLSQL IF-THEN-ELSIF-THEN…..END IF: Exemple 3
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 x integer := 20; y integer := 20;
3 BEGIN
4 IF x<Y THEN
5 DBMS_OUTPUT.PUT_LINE (x || ' < ' ||y);
6 ELSIF x=Y THEN
7 DBMS_OUTPUT.PUT_LINE (x || ' = ' ||y);
8 ELSIF x<Y THEN
9 DBMS_OUTPUT.PUT_LINE (x || ' < ' ||y);
10 ELSE
11 DBMS_OUTPUT.PUT_LINE ('Bizzare!!');
12 END IF ;
13 END;
14 /
20 = 20
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Structures répétitives PLSQL
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
WHILE - LOOP - END LOOP
WHILE conditions
LOOP
instruction1;
instruction2;
END LOOP;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 cpt INTEGER := 0;
3 BEGIN
4 WHILE cpt<10 LOOP
5 DBMS_OUTPUT.PUT_LINE ('Valeur suivante de X : ' || cpt);
6 cpt:=cpt+1;
7 END LOOP;
8 END;
9 /
Valeur suivante de X : 0
Valeur suivante de X : 1
Valeur suivante de X : 2
Valeur suivante de X : 3
Valeur suivante de X : 4
Valeur suivante de X : 5
Valeur suivante de X : 6
Valeur suivante de X : 7
Valeur suivante de X : 8
Valeur suivante de X : 9
ORACLE PL/SQL
Procédure PL/SQL terminée avec succès.
BASES DE DONNEES RELATIONNELLES
LOOP - EXIT WHEN - END LOOP
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LOOP - EXIT WHEN - END LOOP
LOOP
instruction1;
instruction2;
EXIT [WHEN condition1]
END LOOP;
•EXIT force la sortie de la boucle sans conditions.
•EXIT WHEN permet une sortie de boucle si la condition est vraie.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PLSQL LOOP - END LOOP : Exemple 1
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 x integer := 0;
3 BEGIN
4 LOOP
5 x := x + 1;
6 DBMS_OUTPUT.PUT_LINE (x);
7 EXIT WHEN x = 10;
8 END LOOP;
9 END;
10 /
1
2
3
4
5
6
7
8
9
10
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
FOR - IN - LOOP
FOR compteur IN [REVERSE] borne_inf..borne_sup LOOP
instruction1 ;
instruction2 ;
instruction3 ;
[EXIT WHEN condition];
END LOOP;
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PLSQL FOR –IN-LOOP: Exemple 1
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 FOR i IN 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE (i);
4 END LOOP;
5 END;
6 /
1
2
3
4
5
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PLSQL FOR –IN-LOOP: Exemple 2
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 FOR i IN REVERSE 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE (i);
4 END LOOP;
5 END;
6 /
5
4
3
2
1
Procédure PL/SQL terminée avec succès.
SQL>
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PLSQL FOR –IN-LOOP: Exemple 3
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 FOR i IN 1..5 LOOP
3 DBMS_OUTPUT.PUT_LINE (i);
4 EXIT WHEN i>3;
5 END LOOP;
6 END;
7 /
1
2
3
4
Procédure PL/SQL terminée avec succès.
SQL>
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
CASE -WHEN -ELSE -END CASE
CASE selecteur
WHEN expression1 THEN instruction1;
WHEN expression2 THEN instruction2;
…
WHEN expression3 THEN instruction3;
ELSE instruction4;
END CASE;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 x integer := 2;
3 BEGIN
4 CASE X
5 WHEN 1 THEN DBMS_OUTPUT.PUT_LINE ('Le premier');
6 WHEN 2 THEN DBMS_OUTPUT.PUT_LINE ('Le deuxième');
7 WHEN 3 THEN DBMS_OUTPUT.PUT_LINE ('Le troisième');
8 ELSE DBMS_OUTPUT.PUT_LINE ('Le dernier');
9 END CASE;
10 END;
11 /
Le deuxième
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
CASE selecteur
WHEN expression1 THEN instruction1;
WHEN expression2 THEN instruction2;
…
WHEN expression3 THEN instruction3;
ELSE instruction4;
END CASE;
PLSQL CASE -WHEN -ELSE -END CASE: Exemple 2
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 x integer := 2;
3 BEGIN
4 CASE
5 WHEN x=1 THEN DBMS_OUTPUT.PUT_LINE ('Le premier');
6 WHEN x=2 THEN DBMS_OUTPUT.PUT_LINE ('Le deuxième');
7 WHEN x=3 THEN DBMS_OUTPUT.PUT_LINE ('Le troisième');
8 ELSE DBMS_OUTPUT.PUT_LINE ('Le dernier');
9 END CASE;
10 END;
11 /
Le deuxième
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL