3
Ecrire des instructions exécutables
Objectifs
A la fin de ce TP, vous pourrez :
• identifier les unités lexicales d'un bloc PL/SQL
• utiliser des fonctions SQL intégrées dans du code PL/SQL
• décrire dans quels cas des conversions implicites ont lieu
et dans quels cas effectuer des conversions explicites
• écrire des blocs imbriqués et qualifier des variables avec
des étiquettes
• écrire du code lisible, grâce à une indentation appropriée
• utiliser des séquences dans des expressions PL/SQL
3-2
1
Contenu du TP
• Ecrire des instructions exécutables dans un bloc PL/SQL
• Ecrire des blocs imbriqués
• Utiliser des opérateurs et développer du code lisible
3-3
Unités lexicales d'un bloc PL/SQL
Les unités lexicales :
• sont les blocs constitutifs de n'importe quel bloc PL/SQL
• sont des séquences de caractères incluant des lettres,
des nombres, des tabulations, des espaces, des retours
chariot et des symboles
• peuvent être classées en différents groupes :
– Identificateurs : v_fname, c_percent
– Délimiteurs : ; , +, -
– Littéraux : John, 428, True
– Commentaires : --, /* */
3-4
2
Syntaxe des blocs PL/SQL et règles à respecter
• Littéraux
– Les littéraux de type caractère et date doivent être placés entre
apostrophes.
– Les nombres peuvent être des valeurs simples ou utiliser une notation
scientifique.
v_name := 'Henderson';
• Mise en forme du code : Les instructions peuvent s'étendre sur
plusieurs lignes.
3-5
Inclure des commentaires dans le code
• Faites précéder les commentaires monolignes de deux
traits d'union (--).
•Placez les commentaires multilignes entre les symboles
/* et */.
Exemple :
DECLARE
...
v_annual_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
monthly salary input from the user */
v_annual_sal := monthly_sal * 12;
--The following line displays the annual salary
DBMS_OUTPUT.PUT_LINE(v_annual_sal);
END;
/
3-6
3
Fonctions SQL dans le code PL/SQL
• Disponibles dans les instructions procédurales :
– Fonctions monolignes numériques et de type caractère, les
fonctions de conversion de type de données et les fonctions
de date et d'horodatage :
• Non disponibles dans les instructions procédurales :
– DECODE
– Fonctions de groupe (AVG, MIN, MAX, COUNT, SUM,
STDDEV et VARIANCE). Les fonctions de groupe
s'appliquent à des groupes de lignes dans une table. Par
conséquent, elles ne sont disponibles que dans les
instructions SQL d'un bloc PL/SQL.
LOWER CONCAT ROUND MONTHS_BETWEEN TO_CHAR
UPPER SUBSTR TRUNC ADD_MONTHS TO_NUMBER
INITCAP LENGTH MOD NEXT_DAY TO_DATE
INSTR LAST_DAY
LPAD | RPAD ROUND
TRIM TRUNC
3-7 REPLACE
Fonctions SQL dans le code PL/SQL : Exemples
• Déterminer la longueur d'une chaîne :
v_desc_size INTEGER(5);
v_prod_description VARCHAR2(70):='You can use this
product with your radios for higher frequency';
-- get the length of the string in prod_description
v_desc_size:= LENGTH(v_prod_description);
• Déterminer le nombre de mois travaillés par un employé :
v_tenure:= MONTHS_BETWEEN (CURRENT_DATE, v_hiredate);
3-8
4
Utiliser des séquences dans des expressions PL/SQL
Depuis la version 11g :
DECLARE
v_new_id NUMBER;
BEGIN
v_new_id := my_seq.NEXTVAL;
END;
/
Avant la version 11g :
DECLARE
v_new_id NUMBER;
BEGIN
SELECT my_seq.NEXTVAL INTO v_new_id FROM Dual;
END;
/
3-9
Conversion de type de données
• Conversion de données en types comparables
• Deux types de conversion :
– Conversion implicite
– Conversion explicite
• Fonctions :
– TO_CHAR
– TO_DATE
– TO_NUMBER
– TO_TIMESTAMP
3 - 10
5
Exemple de conversion implicite
DECLARE
v_salary NUMBER(6):=6000;
v_sal_hike VARCHAR2(5):='1000';
v_total_salary v_salary%TYPE;
BEGIN
v_total_salary:=v_salary + v_sal_hike;
dbms_output.put_line (v_total_salary);
END;
/
3 - 11
Conversion de type de données
-- implicit data type conversion
1 v_date_of_joining DATE:= '02-Feb-2000';
-- error in data type conversion
2 v_date_of_joining DATE:= 'February 02,2000';
-- explicit data type conversion
3 v_date_of_joining DATE:= TO_DATE('February
02,2000','Month DD, YYYY');
3 - 12
6
Contenu du TP
• Ecrire des instructions exécutables dans un bloc PL/SQL
• Ecrire des blocs imbriqués
• Utiliser des opérateurs et développer du code lisible
3 - 13
Blocs imbriqués
Les blocs PL/SQL peuvent être imbriqués.
• Une section exécutable (BEGIN …
END) peut contenir des blocs imbriqués.
• Une section de traitement des
exceptions peut contenir des
blocs imbriqués.
3 - 14
7
Blocs imbriqués : Exemple
DECLARE
v_outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
DECLARE
v_inner_variable VARCHAR2(20):='LOCAL VARIABLE';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_inner_variable);
DBMS_OUTPUT.PUT_LINE(v_outer_variable);
END;
DBMS_OUTPUT.PUT_LINE(v_outer_variable);
END;
3 - 15
Portée et visibilité des variables
DECLARE
v_father_name VARCHAR2(20):='Patrick';
v_date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
v_child_name VARCHAR2(20):='Mike';
v_date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
/
3 - 16
8
Exercice
Tester ensuite rectifier ce bloc pour un affichage propre des
contenus de la chaîne Ch1 :
Begin
Declare
Ch1 varchar2 (10) := 'Phrase 2';
Begin
dbms_output.put_line ('Ch1 = ' || Ch1);
End ;
dbms_output.put_line ('Ch1 = ' || Ch1);
End ;
/
3 - 17
Utiliser un qualificateur avec des blocs imbriqués
BEGIN <<outer>>
DECLARE
v_father_name VARCHAR2(20):='Patrick';
v_date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
v_child_name VARCHAR2(20):='Mike';
v_date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '
||outer.v_date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
END;
END outer;
3 - 18
9
Problème : Déterminer la portée des variables
BEGIN <<outer>>
DECLARE
v_sal NUMBER(7,2) := 60000;
v_comm NUMBER(7,2) := v_sal * 0.20;
v_message VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
v_sal NUMBER(7,2) := 50000;
v_comm NUMBER(7,2) := 0;
v_total_comp NUMBER(7,2) := v_sal + v_comm;
BEGIN
1 v_message := 'CLERK not'||v_message;
outer.v_comm := v_sal * 0.30;
END;
2 v_message := 'SALESMAN'||v_message;
END;
END outer;
/
3 - 19
Contenu du TP
• Ecrire des instructions exécutables dans un bloc PL/SQL
• Ecrire des blocs imbriqués
• Utiliser des opérateurs et développer du code lisible
3 - 20
10
Opérateurs en langage PL/SQL
• Opérateurs logiques
• Opérateurs arithmétiques
• Opérateur de concaténation Identiques en
• Parenthèses permettant de langage SQL
contrôler l'ordre des opérations
• Opérateur exponentiel (**)
3 - 21
Opérateurs en langage PL/SQL : Exemples
• Incrémenter le compteur pour une boucle :
loop_count := loop_count + 1;
• Définir la valeur d'un indicateur booléen :
good_sal := sal BETWEEN 50000 AND 150000;
• Vérifier qu'un numéro d'employé contient une valeur :
valid := (empno IS NOT NULL);
3 - 22
11
Règles de programmation
Facilitez la maintenance du code :
• en ajoutant des commentaires
• en développant une convention d'utilisation des
majuscules et des minuscules
• en développant des conventions d'appellation pour
les identificateurs et les autres objets
• en utilisant des indentations pour améliorer la clarté
3 - 23
Indenter le code
Pour plus de clarté, indentez chaque niveau du code.
BEGIN DECLARE
IF x=0 THEN y:=1; deptno NUMBER(4);
END IF; locatno NUMBER(4);
END; BEGIN
/ SELECT department_id, location_id
INTO deptno, locatno
FROM departments
WHERE department_name = 'Sales';
...
END;
/
3 - 24
12
Quiz
Vous pouvez utiliser la plupart des fonctions SQL monolignes
de type numérique, caractère, conversion et date dans des
expressions PL/SQL.
a. Vrai
b. Faux
3 - 25
Synthèse
Ce TP a permis d'apprendre à :
• identifier les unités lexicales d'un bloc PL/SQL
• utiliser des fonctions SQL intégrées dans du code PL/SQL
• écrire des blocs imbriqués afin de fractionner des
fonctionnalités liées logiquement
• déterminer dans quels cas effectuer des conversions
explicites
• qualifier des variables dans les blocs imbriqués
• utiliser des séquences dans des expressions PL/SQL
3 - 26
13
