BASES DE DONNEES RELATIONNELLES
PL/SQL FOR ORACLE
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SOMMAIRE GENERAL
1. MOTIVATIONS
2. STRUCTURE D’UN BLOC PL/SQL
3. LES VARIABLES
4. LES ENREGISTREMENTS
5. ASSIGNATION DES VARIABLES ET AFFECTATION
6. STRUCTURES DE CONTRÔLE
7. LES COLLECTIONS
8. LES TRANSACTIONS
9. INSERT-UPDATE-DELETE DANS UN BLOC PL/SQL
10. GESTION DES ERREURS ET DES EXCEPTIONS
11. LES CURSEURS
12. LES PROCEDURES ET LES FOCNTIONS STOCKEES
13. LES PACKAGES
14. LES TRIGGERS
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
MOTIVATION
ORACLE PL/SQL
PL/SQL: PROCEDURAL LANGUAGE/Structured Query Language
Noyau PL/SQL
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
PARTIE 1:
LES FONDAMENTAUX
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PARTIE 1: SOMMAIRE
STRUCTURE D’UN BLOC PL/SQL
LES VARIABLES
LES ENREGISTREMENTS
ASSIGNATION DES VARIABLES ET AFFECTATION
Dans le bloc PL/SQL
A partir du clavier
A partir d’une BD
STRUCTURES DE CONTRÔLE
IF - THEN - ELSE - END IF
LOOP - EXIT WHEN - END LOOP
WHILE - LOOP - END LOOP
FOR - IN – LOOP
CASE - WHEN - THEN - ELSE - END CASE
TRAVAUX PRATIQUES
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
STRUCTURE D’UN
BLOC PL/SQL
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
DECLARE --section optionnelle
déclaration variables, constantes, types, curseurs,...
BEGIN --section obligatoire
contient le code PL/SQL
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
EXCEPTION --section optionnelle
traitement des erreurs
END; --obligatoire
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES VARIABLES
ORACLE PL/SQL
VARIABLES
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 L'utilisation de cet attribut garantit le maintien de la compatibilité des
types entre les colonnes des tables et les variables PL/SQL
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
Exemples de types de bases PL/SQL
TYPE SEMANTIQUE
NUMBER[(e,d)] Nombre réel avec e chiffres significatifs stockés et d décimales
PLS_INTEGER Nombre entier compris entre -2 147 483 647 et +2 147 483 647
CHAR [(n)] Chaîne de caractères de longueur fixe avec n compris entre 1 et 32767 (par défaut 1)
VARCHAR2[(n)] Chaîne de caractères de longueur variable avec n compris entre 1 et 32767
BOOLEAN
DATE
RAW[[(n)] Chaîne de caractères ou données binaires de longueur variable avec n compris entre 1 et 32767. Le contenu
d'une variable de ce type n'est pas interprété par PL/SQL
LONG RAW Identique au type LONG qui peut contenir des données binaires
LONG Chaîne de caractères de longueur variable avec au maximum 32760 octets
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
Exemple 1:
nom_employe IS VARCHAR2(20) NOT NULL;
nom_employe := ‘ Fred’;
Exemple 2:
nom_variable nom_table.nom_colonne%TYPE ;
nom_variable nom_variable_ref%TYPE ;
Nom E_EMPLOYE.NOM%TYPE;
Dat_COM DATE;
Dat_LIV Dat_COM%TYPE;
Exemple 3:
nom_variable nom_table%ROWTYPE ;
EMPLOYE E_EMPLOYE%ROWTYPE;
BASES DE DONNEES RELATIONNELLES
LES ENREGISTREMENTS
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES ENREGISTREMENTS
Les enregistrements sont semblables aux lignes d'une table de base de
données. PL/SQL offre trois types d'enregistrements :
un record défini par le programmeur,
un record basé sur des colonnes d'une table,
un enregistrement basé sur la liste des colonnes d'un curseur explicite.
Un enregistrement, ou un RECORD en PL/SQL, permet de regrouper dans un même
type un ensemble d’informations caractéristiques d’un objet déterminé
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:
EMP.num EMP.NOM et EMP.Pre
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
ASSIGNATION DES
VARIABLES
(AFFECTATION)
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
VARIABLE := EXPRESSION
• Lors de la déclaration
• Dans le bloc PL/SQL
MON_NUM:= 10 ;
MA_CHAINE := 'Chaîne de caractères' ;
MA_TAXE :=PRIX*TAUX;
MON_BOOLEAN := FALSE;
BONUS := SALAIRE * 0.10;
MA_LIMITE_BUDGET CONSTANT REAL := 5000.00;
MA_DATE:=‘12/12/2012’
MON_DEP := DEPARTEMENT.NUMDEP;
ORACLE PL/SQL
Saisir des données à partir du clavier
--partie des messages pour inviter l'utilisateur
à saisir des données prompt CREATION D'UN
NOUVEAU CLIENT
prompt ENTREZ LES DONNEES DU NOUVEAU CLIENT:
ACCEPT L_NO prompt
'Employee : ' prompt
ACCEPT L_NOM
prompt 'NOM: '
prompt
ACCEPT L_SAL prompt 'SALAIRE : '
prompt
ACCEPT L_DATE prompt 'DATE
(mm/dd/yyyy): ' prompt
BASES DE DONNEES RELATIONNELLES
Exemple 1: exécution
SQL> --partie des messages pour inviter l'utilisateur à saisir des données
SQL> prompt CREATION D'UN NOUVEAU CLIENT
CREATION D'UN NOUVEAU CLIENT
SQL> prompt ENTREZ MES DONNEES DU NOUVEAU CLIENT:
ENTREZ MES DONNEES DU NOUVEAU CLIENT:
SQL>
SQL> ACCEPT L_NO prompt 'Employee : '
Employee : 99
SQL> prompt
SQL> ACCEPT L_NOM prompt 'NOM: '
NOM: toto
SQL> prompt
SQL> ACCEPT L_SAL prompt 'SALAIRE : '
SALAIRE : 5005.50
SQL> prompt
SQL> ACCEPT L_DATE prompt 'DATE (mm/dd/yyyy): '
DATE (mm/dd/yyyy): 10/12/2012
SQL> prompt
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemple 2: exécution
1 DECLARE
2 NUM NUMBER(4):= 123;
3 NOM VARCHAR2(20):=‘FATI';
4 SAL NUMBER(10,2):=&L_SAL;
5 DT_REC DATE:= '&L_DATE';
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('NUMERO: ' || NUM);
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 /
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
AFFECTATION DES VARIABLES
A PARTIR D’UNE BD
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SCHEMA DE LA BASE D’EXEMPLES
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Schéma logique la BD exemples
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SELECT <COLONNE_OU_TUPLE> INTO <VAR> FROM NOM_TABLE WHERE CONDITION;
Exemple 1:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 NOM_EMP VARCHAR2(20);
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 / Exemple 2:
Le nom du client NO 1 est driss
SQL> DECLARE
2 NOM_EMP E_CLIENT.NOM%TYPE;
Procédure PL/SQL terminée avec succès.
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 driss
Procédure PL/SQL terminée avec succès.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemple 3:
1 DECLARE
2 NOM_EMP VARCHAR2(20);
3 PRE_EMP VARCHAR2(20);
4 BEGIN
5 SELECT NOM, PRENOM INTO NOM_EMP, PRE_EMP
6 FROM E_CLIENT
7 WHERE NO=1;
8 DBMS_OUTPUT.PUT_LINE ('Le nom du client NO 1 est '|| NOM_EMP);
9 DBMS_OUTPUT.PUT_LINE ('son prénom est '|| PRE_EMP);
10 END;
11 /
Le nom du client NO 1 est Idrissi son
prénom est Mohammed
Procédure PL/SQL terminée avec succès.
BASES DE DONNEES RELATIONNELLES
Exemple 4:
SQL> DECLARE
2 TYPE T_EMP IS RECORD (
3 NOM_EMP VARCHAR2(20),
4 PRE_EMP VARCHAR2(20)
5 );
6 EMP T_EMP;
7 BEGIN
8 SELECT NOM, PRENOM INTO EMP.NOM_EMP, EMP.PRE_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 5:
SQL> DECLARE
2 TYPE T_EMP IS RECORD (
3 NOM_EMP VARCHAR2(20),
4 PRE_EMP VARCHAR2(20)
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
ROWTYPE
Exemple 6:
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 '|| EMP.NOM);
9 DBMS_OUTPUT.PUT_LINE ('PRENOM '|| EMP.PRENOM);
10 DBMS_OUTPUT.PUT_LINE ('TELEHPONE '|| EMP.TELEPHONE);
11 DBMS_OUTPUT.PUT_LINE ('ADRESSE '|| EMP.ADRESSE);
12 DBMS_OUTPUT.PUT_LINE ('VILLE '|| EMP.VILLE);
13 DBMS_OUTPUT.PUT_LINE ('PAYS '|| EMP.PAYS);
14 DBMS_OUTPUT.PUT_LINE ('CP_POSTAL '|| EMP.CP_POSTAL);
15 DBMS_OUTPUT.PUT_LINE ('COMMENTAIRE '|| EMP.COMMENTAIRE);
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
STRUCTURES DE
CONTRÔLE
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Structures de contrôles PLSQL
IF - THEN - ELSE - END IF
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
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
BASES DE DONNEES RELATIONNELLES
IF condition1 THEN
instruction1;
instruction 2;
ELSE
instruction3;
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 condition1 THEN
instruction1;
instruction 2;
ELSIF condition2 THEN
instruction 3;
instruction 4;
ELSIF condition3 THEN
instruction 5;
instruction 6;
ELSE instruction 7;
END IF;
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
LOOP - EXIT WHEN - END LOOP
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
<<label>>
LOOP
instruction1;
instruction2;
EXIT [label][WHEN condition1]
END LOOP label;
• EXIT force la sortie de la boucle sans conditions.
• EXIT WHEN permet une sortie de boucle si la condition est vraie.
• EXIT <<label>> WHEN permet une sortie d'une boucle nommée label si la condition est vraie.
• EXIT <<label>> force une sortie de boucle nommée label.
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
Structures répétitives PLSQL
WHILE - LOOP - END LOOP
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
WHILE conditions
LOOP
instruction1;
instruction2;
END LOOP;
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PLSQL LOOP – WHILE LOOP-END LOOP: Exemple 1
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 /
ORACLE PL/SQL
RESULTS
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
Procédure PL/SQL terminée avec succès.
BASES DE DONNEES RELATIONNELLES
Structures répétitives PLSQL
FOR - IN - LOOP
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
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
Structures de contrôles PLSQL
CASE - WHEN - THEN - ELSE - END CASE
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 1
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
TRAVAUX PRATIQUES
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
PL/SQL
Objectifs:
manipuler les variables, les bloc PL/SQL
et les structures de contrôle
On considère le schéma de la base de données précédente.
Sachant que les numéro des clients sont numéroté dans l’ordre (1,2,…):
Ecrire un bloc PL/SQL
1. Qui affiche le nom et la ville du client numéro 3;
2. qui affiche le numéro, le nom et la ville de chaque client;
3. qui affiche uniquement les client qui habitent rabat
4. qui vérifie si un client, dont le numéro est saisi au clavier, existe
dans la liste des clients.
5. qui récupère le numéro d’un client du clavier et affiche ses informations
ORACLE PL/SQL
