PL/SQL
• PL/SQL - PROCEDURAL LANGUAGE/SQL
Langage procédural de programmation d’Oracle
Combinaison d'instructions de langages classiques avec des instructions
SQL
Composantes des programmes : Fonctions, Procédures, Boucles, Requêtes
SQL, …
2023/2024 49
PL/SQL
• PL/SQL - Structure de base
La structure de base d’un bloc PL/SQL a généralement la forme :
DECLARE
.. /* Déclaration de variables, constantes,
curseurs, … */
BEGIN
.. /* Code, .. */
EXCEPTION
.. /* Optionnel: Gestion des exceptions */
END;
2023/2024 50
PL/SQL
• PL/SQL - Structure de base
Remarque :
Commentaires :
Délimiteurs multi-lignes début et fin /* et */.
Délimiteurs mono-ligne de début --
Les sections déclaration des variables et exception sont facultatives,
La section obligatoire est celle débutée par le mot clé BEGIN et terminée
par le mot clé END.
2023/2024 51
PL/SQL
• PL/SQL - Output
DBMS_OUTPUT.PUT_LINE(<string>);
PUT_LINE est la procédure pour générer l'output sur l’écran
DBMS_OUTPUT est le paquet auquel appartient PUT_LINE
Exemple : || est l’opérateur de concaténation
DECLARE
v number := 0;
BEGIN
SELECT count(*) INTO v FROM Employees;
DBMS_OUTPUT.PUT_LINE('Nombre de lignes : ' || v);
END;
2023/2024 52
PL/SQL
• PL/SQL – Les variables
Sont déclarées dans la section de la déclaration d’un programme PL/SQL.
Une déclaration
alloue de l’espace de stockage pour une valeur,
spécifie son type de données et nomme l’emplacement de stockage pour
y référer au sein du programme.
Les types de données les plus utilisés sont :
VARCHAR2, NUMBER, DATE, BOOLEAN, LONG,
2023/2024 53
PL/SQL
• PL/SQL – Les variables (Syntaxe)
Syntaxe de déclaration :
Nom_var [CONSTANT] type_var [ [NOT NULL] := expression ] ;
Syntaxe d’affectation d’une valeur à une variable dans la section d’exécution
Nom_variable :=valeur ;
Exemple :
DECLARE
v_cpt NUMBER := 0 ; -- initialise la variable à 0
v_today DATE := sysdate ; -- assigne la date système à la variable
v_name VARCHAR2(50) ; --déclaration de la variable
BEGIN
v_cpt := v_cpt+1 ; -- ajoute 1 à la valeur courante du compteur
v_name := ‘mohammed’ ; -- assigne une valeur à la variable
END;
2023/2024 54
PL/SQL
• PL/SQL – Les variables (Syntaxe)
Variable référencée à une colonne d’une table de la base :
Nom_variable table.colonne%TYPE;
Exemple :
DECLARE
num_Emp EMPLOYEES.EMPLOYEE_ID%TYPE;
BEGIN
num_Emp := 2;
DBMS_OUTPUT.PUT_LINE(num_Emp );
END ;
2023/2024 55
PL/SQL
• PL/SQL – Les variables (Syntaxe)
Variable référencée à une table de la base :
Nom_variable table%ROWTYPE;
Exemple :
DECLARE
emp1 Employees%ROWTYPE; -- record basé sur la structure d'une
ligne de la table Eemployes;
BEGIN
SELECT * INTO emp1 FROM Employees WHERE
EMPLOYEE_ID=103;
dbms_output.put_line('Nom :' || emp1.FIRST_NAME);
END;
2023/2024 56
PL/SQL
• PL/SQL – Les constantes
La valeur d’une constante ne peut pas être modifiée.
Nom_var [CONSTANT] type_var;
Exemple :
Declare
v_input number(8);
v_output number(8);
v_line varchar2(80);
v_pi CONSTANT number(8) :=3.14;
begin
v_output := v_pi*&radius**2;
v_line :='The area of a circle with radius '||'&radius'||' is '||to_char(v_output);
dbms_output.put_line(v_line);
end;
2023/2024 57
PL/SQL
• PL/SQL – Exemple avec Select
DECLARE
X DEPARTMENTS.DEPARTMENT_NAME%TYPE;
BEGIN
SELECT DEPARTMENT_NAME INTO X FROM DEPARTMENTS WHERE
DEPARTMENT_ID = 20;
DBMS_OUTPUT.PUT_LINE('Nom du départment : ' || X);
END;
Avec "INTO X", X aura comme valeur le nom
sélectionné du département numéro 20
2023/2024 58
PL/SQL
• PL/SQL – Les tables
Permettent de définir et de manipuler des tableaux dynamiques :
Définis sans dimension initiale.
Est une collection de même type.
Peuvent grandir dynamiquement.
Utilisent des index non consécutifs
Les fonctions pour les tableaux : nom_tableau.fonction
2023/2024 59
PL/SQL
• PL/SQL – Les tables : Syntaxe
Syntaxe de déclaration d’un tableau :
TYPE nom_type IS TABLE OF datatype [NOT NULL]
[INDEX BY BINARY_INTEGER].
Nom_tableau nom_type ;
datatype type (curseur, record,
variable...)
2023/2024 60
PL/SQL
• PL/SQL – Les tables : Exemple
declare
type structure_dep is record (dep DEPARTMENTS%rowtype);
type tab_departments is table of structure_dep index by BINARY_INTEGER;
department tab_departments;
begin
department(0).dep.DEPARTMENT_ID:=280;
department(0).dep.DEPARTMENT_NAME:='INFO';
department(0).dep.MANAGER_ID:=200;
department(0).dep.LOCATION_ID:=1700;
DBMS_OUTPUT.PUT_LINE('Numéro du département: '||
department(0).dep.DEPARTMENT_ID|| ' Nom du département: '||
department(0).dep.DEPARTMENT_NAME
|| ' numéro du manager: '|| department(0).dep.MANAGER_ID || ' numéro de loacle:
'|| department(0).dep.LOCATION_ID);
end;
2023/2024 61
PL/SQL
• PL/SQL – Les structures de contrôle
On dispose des structures suivantes:
Les structures avec IF.
Les expressions CASE.
Les boucles.
2023/2024 62
PL/SQL
• PL/SQL – Les structures de contrôle : IF
Les structures avec IF-THEN :
Sert à tester des conditions simples.
Si la condition est évaluée comme étant TRUE, une ou plusieurs lignes de
code sont exécutées.
Si elle est évaluée comme étant FALSE, aucune action n’a lieu.
Il est possible d’imbriquer des structures IF-THEN
2023/2024 63
PL/SQL
• PL/SQL – Les structures de contrôle : IF
Syntaxe :
IF <condition> THEN
<instruction(s)>
[ ELSEIF <condition> THEN
<instruction(s)> ]
…
[ ELSEIF <condition> THEN
<instruction(s)> ]
[ ELSE
<instruction(s)>]
END IF;
2023/2024 64
PL/SQL
• PL/SQL – Les structures de contrôle : IF
Exemple :
DECLARE
v_res varchar(20);
BEGIN
IF ¬e >= 10 THEN
v_res := 'Validé’;
ELSE
v_res := 'Ajourné’;
END IF;
dbms_output.put_line('Le résultat est : ' || v_res );
END;
2023/2024 65
PL/SQL
• PL/SQL – Les structures de contrôle : CASE
L’expression CASE a été introduit avec oracle 9i
Syntaxe :
CASE sélecteur
WHEN <condition1> THEN résultat1
WHEN <condition2> THEN résultat2
WHEN <condition3> THEN résultat3
ELSE resultat4
END ;
2023/2024 66
PL/SQL
• PL/SQL – Les structures de contrôle : CASE
Exemple : DECLARE
v_mention varchar(20);
note NUMBER;
BEGIN
note := ¬e_saisie;
Case
WHEN note>=10 AND note<12 THEN v_mention := 'Passable’;
WHEN note>=12 AND note<14 THEN v_mention := 'Assez Bien’;
WHEN note>=14 AND note<16 THEN v_mention := 'Bien’;
WHEN note>=16 AND note<18 THEN v_mention := 'Très Bien’;
WHEN note>=18 AND note<=20 THEN v_mention := 'Exellent’;
ELSE v_mention := 'Ajournée’;
END CASE;
dbms_output.put_line('La mention est : ' || v_mention );
END;
2023/2024 67
PL/SQL
• PL/SQL – Les structures de contrôle : Les boucles
Une boucle permet d’exécuter à répétition une série d’instructions.
Dans PL/SQL, il existe trois types de boucles :
LOOP
WHILE-LOOP
FOR-LOOP
2023/2024 68
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle LOOP
Syntaxe :
Pour que cette boucle puisse prendre fin, on
LOOP
doit inclure une instruction EXIT
<instructions>
EXIT-Instruction
END LOOP;
EXIT-Instruction : EXIT WHEN <condition> ;
IF <condition> THEN EXIT ;
EXIT-Instruction :
END IF;
2023/2024 69
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle LOOP
Exemple 1 :
DECLARE
total_salary number := 0;
v_id number :=100;
v_salary EMPLOYEES.SALARY%TYPE;
BEGIN
LOOP
SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = v_id;
total_salary := total_salary + v_salary;
v_id := v_id + 1;
IF v_id > 206 THEN EXIT;
END IF;
END LOOP ;
dbms_output.put_line('La somme est : ' || total_salary );
END;
2023/2024 70
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle LOOP
Exemple 2:
DECLARE
total_salary number := 0;
v_id number :=100;
v_salary EMPLOYEES.SALARY%TYPE;
BEGIN
LOOP
SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = v_id;
total_salary := total_salary + v_salary;
v_id := v_id + 1;
Exit WHEN v_id > 206;
END LOOP ;
dbms_output.put_line('La somme est : ' || total_salary );
END;
2023/2024 71
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle WHILE-LOOP
Syntaxe :
WHILE <condition> LOOP
<instructions>
END LOOP;
2023/2024 72
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle WHILE-LOOP
Exemple :
DECLARE
total_salary number := 0;
v_id number :=100;
v_salary EMPLOYEES.SALARY%TYPE;
BEGIN
WHILE v_id <= 206 LOOP
SELECT SALARY INTO v_salary FROM EMPLOYEES WHERE EMPLOYEE_ID = v_id;
total_salary := total_salary + v_salary;
v_id := v_id + 1;
END LOOP ;
dbms_output.put_line('La somme est : ' || total_salary );
END;
2023/2024 73
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle FOR-LOOP
Syntaxe :
FOR variable IN val_debut .. val_fin
LOOP
<instructions>
END LOOP ;
2023/2024 74
PL/SQL
• PL/SQL – Les structures de contrôle : La boucle FOR-LOOP
Exemple :
DECLARE DECLARE
v_counter number ; v_counter number ;
BEGIN BEGIN
FOR v_counter IN 1..5 FOR v_counter IN REVERSE 1..5
LOOP LOOP
DBMS_OUTPUT.PUT_LINE(v_counter) ; DBMS_OUTPUT.PUT_LINE(v_counter) ;
END LOOP ; END LOOP ;
END ; END ;
Affiche 5 4 3 2 1
Affiche 1 2 3 4 5 En utilisant le mot clé REVERSE
L'itération se déroule de la valeur l'itération se déroule de la valeur finale à
initiale à la valeur finale la valeur initiale
2023/2024 75
PL/SQL
• PL/SQL – Les curseurs
Le moyen le plus simple d’inclure des données dans un programme.
Structure de données permettant de stocker le résultat d’une requête qui
retourne plusieurs lignes.
Lorsqu’on utilise un ordre SQL, un curseur implicite est automatiquement
ouvert pour le gérer.
Une méthode plus efficace consiste à utiliser un curseur explicite.
Un curseur est défini dans la section de déclaration d’un programme.
Il reçoit un nom à l’instar d’une variable.
2023/2024 76
PL/SQL
• PL/SQL – Les curseurs
Déclaration d’un curseur :
DECLARE OR REPLACE
CURSOR nom_cursor IS SELECT ... ;
L’ouverture d’un curseur :
OPEN nom_cursor;
Fermeture d’un curseur : Libère les ressources utilisées par le curseur et
s’effectue dans les sections BEGIN ou EXCEPTION :
CLOSE nom_cursor;
2023/2024 77
PL/SQL
• PL/SQL – Les curseurs
Traitement des lignes d’un curseur : Il est nécessaire de déclarer et d’ouvrir le
curseur avant son utilisation :
FETCH Nom_curseur INTO Liste_de_variables;
FETCH Nom_curseur INTO variables_type_enregistrement;
2023/2024 78
PL/SQL
• PL/SQL – Les curseurs
Exemple :
DECLARE
emp_count NUMBER ;
i number ;
--Déclare le curseur explicite pour la sélection
CURSOR get_emp_data IS SELECT COUNT(*) FROM EMPLOYEES;
BEGIN
OPEN get_emp_data; --ouvre le curseur
FETCH get_emp_data INTO emp_count; -- place le résultat dans une variable
FOR i IN 1..emp_count LOOP
Dbms_output.put_line('Employée : '|| i) ;
END LOOP ;
CLOSE get_emp_data ; --ferme le curseur
END ;
2023/2024 79
PL/SQL
• PL/SQL – Les curseurs
La structure CURSOR-FOR-LOOP :
Permet d’associer un curseur à une boucle.
Permet d’extraire plusieurs lignes de la base de données,
plus simple à programmer.
Il n’est pas nécessaire d’ouvrir et de fermer le curseur,
c’est oracle qui s’en charge dans la boucle.
2023/2024 80
PL/SQL
• PL/SQL – Les curseurs
Exemple :
DECLARE
Emp EMPLOYEES%ROWTYPE;
CURSOR EMP_CUR IS
SELECT * FROM EMPLOYEES;
BEGIN
FOR EMP IN EMP_CUR LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE : ' || Emp.EMPLOYEE_ID || ',' ||
Emp.FIRST_NAME || ' ' || Emp.LAST_NAME);
END LOOP;
END;
2023/2024 81
PL/SQL
• PL/SQL – Les curseurs : L’attribut %FOUND
Prend la valeur TRUE si le dernier ordre SQL a ramené au mois une ligne.
Utilisé avec la syntaxe
Nom_curseur%FOUND pour un curseur explicite
SQL%FOUND pour un curseur implicite
2023/2024 82
PL/SQL
• PL/SQL – Les curseurs : L’attribut %FOUND
Exemple :
Declare
FirstName_emp EMPLOYEES.FIRST_NAME%TYPE;
LastName_emp EMPLOYEES.LAST_NAME%TYPE;
CURSOR get_emp_data IS SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;
BEGIN
OPEN get_emp_data;
LOOP
FETCH get_emp_data INTO FirstName_emp, LastName_emp;
IF get_emp_data%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Prénom : '||FirstName_emp||' et Nom :
'||LastName_emp);
ELSE EXIT; END IF;
END LOOP;
CLOSE get_emp_data;
END;
2023/2024 83
PL/SQL
• PL/SQL – Les curseurs : L’attribut %NOTFOUND
Prend la valeur TRUE si le dernier ordre SQL n’a ramené aucune ligne.
Utilisé avec la syntaxe
Nom_curseur%NOTFOUND pour un curseur explicite
SQL%NOTFOUND pour un curseur implicite
2023/2024 84
PL/SQL
• PL/SQL – Les curseurs : L’attribut %NOTFOUND
Exemple :
Declare
BEGIN
UPDATE EMPLOYEES
SET SALARY=30000
WHERE EMPLOYEE_ID ='&v_id’;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Cet employee n’existe pas') ;
END IF;
END;
2023/2024 85
PL/SQL
• PL/SQL – Les curseurs : L’attribut %ROWCOUNT
Prest de type numérique,
contient le nombre de lignes ramenées par le dernier ordre SQL,
utilisé avec la syntaxe
Nom_curseur%ROWCOUNT pour un curseur explicite.
SQL%ROWCOUNT pour un curseur implicite.
2023/2024 86
PL/SQL
• PL/SQL – Les curseurs : L’attribut %ROWCOUNT
Exemple :
DECLARE
Emp EMPLOYEES%ROWTYPE;
CURSOR EMP_CUR IS
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG';
BEGIN
FOR EMP IN EMP_CUR LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE : ' || Emp.EMPLOYEE_ID ||
',' || Emp.FIRST_NAME || ' ' || Emp.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('nombre des employées séléctionnées : '
|| EMP_CUR%ROWCOUNT);
END LOOP;
END;
2023/2024 87
PL/SQL
• PL/SQL – Les exceptions
Les sources d'erreurs lors de l'exécution d'une application sont multiples
(Erreur du code, erreur système, violation d'une règle Oracle...)
Chaque erreur Oracle générée par le noyau a un code erreur.
En PL/SQL, les erreurs de programme sont gérées au moyen de
gestionnaires d’exceptions.
Lorsqu’une erreur survient pendant l’exécution d’un programme, ce dernier
détermine si on a anticipé le problème à l’aide d’un code.
Oracle dispose d’un certain nombre de gestionnaires d’exceptions
prédéfinis qu’on peut utiliser.
Une exception permet de gérer une erreur pour laquelle oracle n’a pas
prévu de gestionnaire.
2023/2024 88
PL/SQL
• PL/SQL – Les exceptions
Deux méthodes pour gérer les erreurs:
Si le nom d'exception existe
BEGIN
EXCEPTION
WHEN Nom_exception THEN
<instructions>;
END;
2023/2024 89
PL/SQL
• PL/SQL – Les exceptions
Deux méthodes pour gérer les erreurs:
Si le nom d'exception n'existe pas
BEGIN
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE =Numero_code1 THEN
<instructions>;
END IF;
IF SQLCODE=Numero_code2 THEN
<instructions>;
END IF;
END;
2023/2024 90
PL/SQL
• PL/SQL – Les exceptions
Exemple :
Declare
Emp EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO Emp FROM EMPLOYEES WHERE EMPLOYEE_ID ='&v_id';
DBMS_OUTPUT.PUT_LINE('EMPLOYEE : ' || Emp.EMPLOYEE_ID || ',' ||
Emp.FIRST_NAME || ' ' || Emp.LAST_NAME);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Cet employé n existe pas');
END;
2023/2024 91
