PL/SQL MASTER SID
ROUDANE-EL FAZAZI
PL/SQL : Exemples & Exercices
Section Déclarative :
Exemple 1 :
Tester les déclarations suivantes :
DECLARE
V_x NUMBER NOT NULL := 10 ;
V_hiredate DATE NOT NULL ;
V_nb INTEGER DEFAULT 100 ;
V_chaine1, v_chaine2 VARCHAR2(30) ;
V_var BOOLEAN := 1 ;
BEGIN
NULL ;
END ;
Exemple 2 :
Découvrir les blocs internes et les procédures prédéfinies.
Set SERVEROUTPUT ON
DECLARE
V_x VARCHAR2(50) ; Procédure
V_y VARCHAR2(50) ; Interne
PROCEDURE Proc1 (str IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(str) ;
END Proc1 ;
BEGIN
V_x := ‘chaine1’ ; v_y :=’chaine2’ ;
IF (v_x=v_y) THEN
Proc1(‘Les chaines sont Egales !’) ;
ELSE
Proc1(‘Les chaines ne sont pas Egales !!’) ;
END IF ; Fonction
Proc1( LENGTH(v_x)) ; Prédéfinie
Proc1( LENGTH(v_y)) ;
END ;
/
1
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
Exemple 3 :
Variables PLSQL / BIND / Substitution
Découvrir les différences entre la déclaration et l’utilisation des variables PLSQL et celles des
variables NON PLSQL.
Les variables
VARIABLE g_chaine VARCHAR2(50) Hôtes (Bind
VARIABLE g_resultat NUMBER
Variable)
ACCEPT g_nombre1 PROMPT ‘Entrez le nombre 2 : ‘ Les variables de
substitution
DECLARE
V_nombre2 NUMBER NOT NULL := 10 ;
BEGIN L’utilisation des
IF (&g_nombre1 <> 0) THEN
variables Hôtes
:g_resultat := (v_nombre2 + 12)/ &g_nombre1 ;
avec ‘ : ’ et les
:g_chaine :=’la division est terminée avec succès !’ ;
variables de
ELSE
substitution
:g_chaine :=’Erreur : la division sur 0 !’ ;
avec ‘ & ‘
END ;
/
PRINT g_chaine Affichage des
PRINT g_resultat variables Hôtes
avec « PRINT »
Exercice :
Codez un bloc PL/SQL qui calcule le gain total pour une année. Le salaire annuel et le pourcentage de
bonus de l’année sont transmis au bloc PL/SQL à l’aide de variables de substitution SQL*PLUS, et le
bonus doit être converti d’un nombre entier en décimal (par exemple, 15 en 0,15). Si le salaire est
NULL, définissez-le à zéro avant de calculer le traitement total. Exécutez le bloc PL/SQL.
Exemple :
Veuillez saisir le salaire annuel : 50000
Veuillez saisir le % de bonus : 10
TOTAL
----------
55000
2
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
Section Exécutable :
Exemple 1 : la porté des variables
Remplir le tableau au-dessous en se basant sur le bloc PL/SQL suivant :
Valeur dans le bloc Valeur dans le sous-
Variable
Principal bloc
V_nombre
V_message
V_test
V_local
SET SERVEROUTPUT ON
DECLARE
v_nombre NUMBER := 600;
v_message VARCHAR2(100):=‘Message Principal’;
v_test VARCHAR2(10) := ‘True’;
BEGIN
DECLARE
v_nombre NUMBER :=1;
v_message VARCHAR2(100):=‘Message second’;
v_local VARCHAR2(100):=‘Message Interne’;
BEGIN
v_nombre:=v_nombre+1;
v_local:=‘MSG1: ‘||v_local;
DBMS_OUTPUT.PUT_LINE(‘Les valeurs des variables du sous-bloc :’) ;
DBMS_OUTPUT.PUT_LINE(‘v_nombre= ’||to_char(v_nombre)) ;
DBMS_OUTPUT.PUT_LINE(‘v_message= ’||v_message) ;
DBMS_OUTPUT.PUT_LINE(‘v_local= ’||v_local) ;
DBMS_OUTPUT.PUT_LINE(‘v_test= ’||v_test) ;
END;
v_nombre:=v_nombre+1;
v_message:=v_message||’MSG2’;
v_local:=‘MSG3 : ’||v_local;
DBMS_OUTPUT.PUT_LINE(‘Les valeurs des variables du bloc principal :’) ;
DBMS_OUTPUT.PUT_LINE(‘v_nombre= ’||to_char(v_nombre)) ;
DBMS_OUTPUT.PUT_LINE(‘v_message= ’||v_message) ;
DBMS_OUTPUT.PUT_LINE(‘v_test= ’||v_test) ;
DBMS_OUTPUT.PUT_LINE(‘v_local= ’||v_local) ;
END; 2 :
Exemple
3
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
Instructions PL/SQL
ACCEPT g_employe PROMPT ‘Entrez le numéro de l’employé : ‘
VARIABLE g_message VARCHAR2(60)
DECLARE
V_sal [Link]%TYPE ;
V_comm [Link]%TYPE ;
BEGIN
Select sal, comm into v_sal, v_comm
From emp where empno=&g_employe ;
IF (NVL(v_sal, 0) >1500) THEN
V_comm :=NVL(v_sal,0)*0.2 ;
ELSIF (NVL(v_sal, 0) <1000) THEN
V_comm :=NVL(v_sal,0)*0.1;
ELSE
V_comm :=NVL(v_sal,0)*0.15 ;
END IF ;
UPDATE emp set comm=v_comm
Where empno=&g_employe ;
COMMIT ;
:g_message :=’La commission est mise à jour par la valeur : ‘||to_char(v_comm) ;
SAVEPOINT save1 ;
INSERT INTO emp(empno, ename) VALUES(8000, ‘MASTER ‘) ;
SAVEPOINT save2 ;
INSERT INTO emp(empno, ename) VALUES(9000, ‘SID‘) ;
DELETE FROM emp where empno=9000 ;
ROLLBACK to SAVEPOINT save2;
END ;
/
PRINT g_message
Select empno, ename, sal, comm from emp ;
Exemple 3 :
Ajouter une nouvelle colonne STARS varchar2(50), dans la table EMP qui permettra de stocker des
étoiles (*).
Créer un programme PL/SQL qui récompense les employés en leur attribuant une étoile dans la
colonne STARTS par tranche de salaire de $100.
4
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
Alter table emp add STARS varchar2(100) ;
ACCEPT g_num_emp PROMPT ‘Entrez le numéro de l’employé : ‘
DECLARE
V_star VARCHAR2(100) :=’*’ ;
V_sal [Link]%TYPE ;
V_cpt NUMBER :=0 ;
BEGIN
Select sal into v_sal from emp
Where empno=&g_num_emp ;
WHILE (v_cpt< ROUND(v_sal/100))
LOOP
V_star :=v_star||’*’ ;
V_cpt :=v_cpt+1 ;
END LOOP ;
UPDATE emp SET STARS=v_star
Where empno=&g_num_emp ;
COMMIT ;
END ;
/
Select ename, sal, stars from emp ;
5
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
Exemple 4 : Curseurs SQL
DECLARE
--declaration du curseur
CURSOR c_deptEmp IS
--l’instruction select
select ename, job, dname from emp e, dept d
where [Link]=[Link] ;
--variables d’acceuil
v_ename [Link]%TYPE;
v_job [Link]%TYPE;
v_dname [Link]%TYPE;
BEGIN
--ouverture du curseur
open c_deptEmp;
--boucle sur les lignes
loop
--lecture d’une ligne
fetch c_deptEmp into v_ename, v_job, v_dname;
dbms_output.put_line(‘l’employé ‘||v_ename||’ travaille comme
‘||v_job||’ dans le departement ‘||v_dname) ;
--sortir lorsque le curseur ne ramène pas de ligne
EXIT When c_deptEMP%NOTFOUND;
end loop;
--fermeture du curseur
close c_deptEMP;
END ;
/
Exercice 1 :
Créer une table Messages ayant un RESULT de type chaine de caractères.
Ecrire un bloc PL/SQL pour insérer les nombres de 1 à 10, en excluant 6 et 8.
Afficher le contenu de la table Messages.
Ecrire le code nécessaire pour afficher le texte « Nombre pair » ou « Nombre impair », selon que le
nombre correspondant dans la table Messages est pair ou impair.
--création de la table Messages
CREATE TABLE Messages (Result VARCHAR2(5)) ; 6
--Remplissage de la table Messages
BEGIN
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
Exercice 2 :
On souhaite appliquer une règle d’échantillonnage à la table EMP(empno, ename,…).
Ecrire un programme PLSQL permettant de lire la table EMP et d’afficher les noms des employés dont
les rangs d’apparition dans la table sont : 1, 3, 6, 10, 15,….
Règles de calcul des rangs successifs :
7
PL/SQL MASTER SID
ROUDANE-EL FAZAZI
1=0+1
3=1+2
6=3+3
10=6+4
15=10+5
Résultat attendu :
1 : SMITH
3 : BLAKE
6 : WARD
10 : TURNER
Bon Apprentissage !
