La meilleure façon d’apprendre quelque chose est de pratiquer des exercices. Nous avons préparer ces exercices corrigés pour les personnes (débutantes ou intermédiaires) qui sont familières avec PL/SQL. Nous espérons que ces exercices vous aideront à améliorer vos compétences en PL/SQL. Les exercices corrigés suivantes sont actuellement disponibles, nous travaillons dur pour ajouter plus d’exercices. Bon apprentissage!
Vous pouvez lire notre tutoriel PL/SQL avant de résoudre les exercices suivants.
Exercices corrigés PL/SQL: Principes de base
1. Ecrivez un bloc PL/SQL pour calculer la prime d’un employé (salaire * 0.45) dont l’ID est 1003.
DECLARE
prime NUMBER(6,2);
BEGIN
SELECT salaire * 0.45 INTO prime
FROM employees
WHERE id = 1003;
DBMS_OUTPUT.PUT_LINE('Prime = ' || TO_CHAR(prime));
END;
/
DECLARE
prime NUMBER(6,2);
BEGIN
SELECT salaire * 0.45 INTO prime
FROM employees
WHERE id = 1003;
DBMS_OUTPUT.PUT_LINE('Prime = ' || TO_CHAR(prime));
END;
/
Sortie:
Prime = 2250
2. Écrire un bloc PL/SQL pour afficher les commentaires simples et multilignes.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
nbr NUMBER := 10; -- Commentaire sur une seule ligne
BEGIN
/* Il s'agit d'un commentaire
sur plusieurs lignes */
DBMS_OUTPUT.PUT_LINE('Nombre: ' || nbr);
END;
/
DECLARE
nbr NUMBER := 10; -- Commentaire sur une seule ligne
BEGIN
/* Il s'agit d'un commentaire
sur plusieurs lignes */
DBMS_OUTPUT.PUT_LINE('Nombre: ' || nbr);
END;
/
DECLARE
nbr NUMBER := 10; -- Commentaire sur une seule ligne
BEGIN
/* Il s'agit d'un commentaire
sur plusieurs lignes */
DBMS_OUTPUT.PUT_LINE('Nombre: ' || nbr);
END;
/
Sortie:
Nombre: 10
3. Écrire des blocs PL/SQL pour montrer la déclaration des variables.
Déclaration des variables de type scalaire.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
id NUMBER(5); -- entier
nom VARCHAR2(20); -- string
marie BOOLEAN; -- boolean
salaire NUMBER(8,2); -- float
BEGIN
NULL;
END;
/
DECLARE
id NUMBER(5); -- entier
nom VARCHAR2(20); -- string
marie BOOLEAN; -- boolean
salaire NUMBER(8,2); -- float
BEGIN
NULL;
END;
/
DECLARE
id NUMBER(5); -- entier
nom VARCHAR2(20); -- string
marie BOOLEAN; -- boolean
salaire NUMBER(8,2); -- float
BEGIN
NULL;
END;
/
Déclaration d’une constante avec un type de données scalaire.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
minimum CONSTANTINTEGER := 10;
BEGIN
NULL;
END;
/
DECLARE
minimum CONSTANT INTEGER := 10;
BEGIN
NULL;
END;
/
DECLARE
minimum CONSTANT INTEGER := 10;
BEGIN
NULL;
END;
/
Déclaration d’une variable avec une valeur initiale.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
nbr INTEGER := 0;
BEGIN
NULL;
END;
/
DECLARE
nbr INTEGER := 0;
BEGIN
NULL;
END;
/
DECLARE
nbr INTEGER := 0;
BEGIN
NULL;
END;
/
Initialiser une variable par (NULL).
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
nbr INTEGER; -- par défaut reçoit NULL
BEGIN
NULL;
END;
/
DECLARE
nbr INTEGER; -- par défaut reçoit NULL
BEGIN
NULL;
END;
/
DECLARE
nbr INTEGER; -- par défaut reçoit NULL
BEGIN
NULL;
END;
/
Déclaration d’une variable avec la contrainte NOT NULL.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
id INTEGER(5) NOT NULL := 1005;
BEGIN
NULL;
END;
/
DECLARE
id INTEGER(5) NOT NULL := 1005;
BEGIN
NULL;
END;
/
DECLARE
id INTEGER(5) NOT NULL := 1005;
BEGIN
NULL;
END;
/
Dans l’exemple suivant, la variable nom_emp hérite le type et la taille de la colonne employees.nom, cette déclaration n’a pas besoin d’une valeur initiale.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
nom_emp employees.nom%TYPE;
BEGIN
NULL;
END;
/
DECLARE
nom_emp employees.nom%TYPE;
BEGIN
NULL;
END;
/
DECLARE
nom_emp employees.nom%TYPE;
BEGIN
NULL;
END;
/
4. Écrire un bloc PL/SQL pour montrer qu’un mot réservé peut être utilisé comme variable.
En utilisant une seule commande, PL/SQL exécute plusieurs requêtes en un seul bloc.
PL/SQL peut gérer les exceptions générées dans le bloc PL/SQL. Ce bloc est appelé bloc de gestion des exceptions.
Il est possible de créer un bloc PL/SQL tel que des procédures, des packages, des triggers, des fonctions et des types, qui sont stockés dans la base de données afin d’être réutilisés par des applications.
Les applications écrites en PL/SQL sont portables sur le matériel ou les systèmes d’exploitation où Oracle est fonctionnel.
6. Qu’entendez-vous par un tableau PL/SQL ?
Une collection ordonnée d’éléments du même type est appelée tableau PL/SQL. La position de chaque élément dans la collection est déterminée par son numéro d’index. Un type défini par l’utilisateur doit d’abord être déclaré par le tableau PL/SQL, puis il doit être déclaré en tant que variable.
Syntaxe:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
TYPE nom_du_type ISTABLEOF type_element [NOT NULL] INDEXBY type_indice;
nom_du_tableau nom_du_type;
TYPE nom_du_type IS TABLE OF type_element [NOT NULL] INDEX BY type_indice;
nom_du_tableau nom_du_type;
TYPE nom_du_type IS TABLE OF type_element [NOT NULL] INDEX BY type_indice;
nom_du_tableau nom_du_type;
Exemple:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
TYPE salaire ISTABLEOFNUMBERINDEXBY VARCHAR2(25);
tableau_de_salaire salaire;
BEGIN
-- ajouter des éléments au tableau
tableau_de_salaire('Alex') := 95000;
tableau_de_salaire('Bob') := 63000;
tableau_de_salaire('Jean') := 200000;
END;
/
DECLARE
TYPE salaire IS TABLE OF NUMBER INDEX BY VARCHAR2(25);
tableau_de_salaire salaire;
BEGIN
-- ajouter des éléments au tableau
tableau_de_salaire('Alex') := 95000;
tableau_de_salaire('Bob') := 63000;
tableau_de_salaire('Jean') := 200000;
END;
/
DECLARE
TYPE salaire IS TABLE OF NUMBER INDEX BY VARCHAR2(25);
tableau_de_salaire salaire;
BEGIN
-- ajouter des éléments au tableau
tableau_de_salaire('Alex') := 95000;
tableau_de_salaire('Bob') := 63000;
tableau_de_salaire('Jean') := 200000;
END;
/
7. Expliquer la structure de base du PL/SQL.
Grâce à l’intégration d’éléments issus des langages procéduraux, le PL/SQL enrichit le SQL et crée un langage structurel plus puissant que le SQL. Le bloc est l’élément de base de PL/SQL. Chaque programme PL/SQL est composé de blocs qui peuvent être imbriqués les uns dans les autres.
8. Qu’est-ce qu’un curseur PL/SQL ?
Un curseur est un pointeur sur le résultat d’une requête. Il existe deux types de curseurs en PL/SQL.
Curseur implicite
Curseur explicite
9. Quelle est la différence entre le curseur implicite et le curseur explicite ?
Curseur implicite
Curseur explicite
Le curseur implicite est un curseur créé automatiquement.
Le curseur explicite est défini par l’utilisateur.
Le curseur implicite ne peut récupérer qu’une seule ligne à la fois.
Le curseur explicite peut récupérer plusieurs lignes en même temps.
Il donne moins de contrôle aux programmeurs.
Le curseur explicite est totalement contrôlé par les programmeurs.
Le curseur explicite est moins efficace.
Un curseur explicite est plus efficace.
Les attributs du curseur implicite utilisent toujours le préfixe « SQL ». La structure du curseur implicite est définie par le nom
SQL%nom_attri
SQL%nom_attri. Les curseurs implicites les plus courants sont
SQL%FOUND
SQL%FOUND,
SQL%NOTFOUND
SQL%NOTFOUND,
SQL%ROWCOUNT
SQL%ROWCOUNT.
Structure des curseurs explicites:
nom_du_curseur%nom_attri
nom_du_curseur%nom_attri. Voici quelques curseurs explicites:
nom_du_curseur%FOUND
nom_du_curseur%FOUND,
nom_du_curseur%NOTFOUND
nom_du_curseur%NOTFOUND,
nom_du_curseur%ROWCOUNT
nom_du_curseur%ROWCOUNT.
10. A quoi servent les instructions COMMIT, ROLLBACK et SAVEPOINT en PL/SQL ?
L’instruction COMMIT : Les modifications apportées au cours d’une transaction sont sauvegardées de manière permanente par la commande COMMIT. Syntaxe:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
BEGIN
-- votre code;
COMMIT;
END;
DECLARE
BEGIN
-- votre code;
COMMIT;
END;
DECLARE
BEGIN
-- votre code;
COMMIT;
END;
Déclaration ROLLBACK : Elle est utilisée pour annuler toute modification effectuée depuis le début de la transaction.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
BEGIN
-- votre code;
ROLLBACK;
END;
DECLARE
BEGIN
-- votre code;
ROLLBACK;
END;
DECLARE
BEGIN
-- votre code;
ROLLBACK;
END;
Déclaration SAVEPOINT : Un point de transaction qui peut être utilisé pour revenir à un certain point de la transaction qui est créé à l’aide de l’instruction SAVEPOINT.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
BEGIN
SAVEPOINT sp;
-- votre code;
ROLLBACKTO sp;
END;
DECLARE
BEGIN
SAVEPOINT sp;
-- votre code;
ROLLBACK TO sp;
END;
DECLARE
BEGIN
SAVEPOINT sp;
-- votre code;
ROLLBACK TO sp;
END;
11. Comment déboguer notre code PL/SQL ?
Pour déboguer notre code PL/SQL, nous pouvons utiliser les instructions DBMS_OUTPUT et DBMS_DEBUG. La sortie est affichée sur la console standard via DBMS_OUTPUT. La sortie est affichée dans le fichier log par DBMS_DEBUG.
12. Quels sont les types de commandes que PL/SQL ne prend pas en charge ?
PL/SQL ne prend pas en charge les commandes de définition de données (DDL) telles que CREATE TABLE, ALTER TABLE ou DROP TABLE.
13. Listez les types d’exceptions en PL/SQL.
Deux types d’exceptions sont présents en PL/SQL.
Exception prédéfinie
Exception définie par l’utilisateur
14. Citez quelques exceptions PL/SQL.
Voici quelques exceptions PL/SQL.
ZERO_ERROR
NO_DATA_FOUND
INVALID_NUMBER
TOO_MANY_ROWS
ACCESS_INTO_NULL
CASE_NOT_FOUND
15. Décrivez les types de données présents en PL/SQL.
Il existe deux types de données en PL/SQL.
Les types de données scalaires : NUMBER, DATE, CHAR, VARCHAR2, BOOLEAN et LONG sont des types de données scalaires.
Type de données composites : TABLE et RECORD sont des types de données composites.
16. Qu’est-ce qu’un package PL/SQL ?
Les packages sont des objets de schéma qui regroupent des types, des variables et des sous-programmes PL/SQL logiquement liés. Un package se compose de deux parties.
Spécification du package
Le corps ou la définition du package
Syntaxe:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
nom_package.nom_attribut;
nom_package.nom_attribut;
nom_package.nom_attribut;
17. Ecrire un programme PL/SQL pour trouver qu’une chaîne donnée est un palindrome.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE
-- Déclaration de variable
str VARCHAR(10):="madam";
lettre VARCHAR(25);
string_inverse VARCHAR(10);
BEGIN
FOR i INREVERSE1.. LENGTH(str) LOOP
lettre := SUBSTR(str, i, 1);
-- Concaténer lettre à la variable string_inverse
string_inverse := string_inverse || ” ||lettre;
ENDLOOP;
IF string_inverse = str THEN
dbms_output.Put_line(string_inverse||”||’ est un palindrome’);
ELSE
dbms_output.Put_line(string_inverse||”||’ n'est pas un palindrome’);
ENDIF
END;
DECLARE
-- Déclaration de variable
str VARCHAR(10):="madam";
lettre VARCHAR(25);
string_inverse VARCHAR(10);
BEGIN
FOR i IN REVERSE 1.. LENGTH(str) LOOP
lettre := SUBSTR(str, i, 1);
-- Concaténer lettre à la variable string_inverse
string_inverse := string_inverse || ” ||lettre;
END LOOP;
IF string_inverse = str THEN
dbms_output.Put_line(string_inverse||”||’ est un palindrome’);
ELSE
dbms_output.Put_line(string_inverse||”||’ n'est pas un palindrome’);
END IF
END;
DECLARE
-- Déclaration de variable
str VARCHAR(10):="madam";
lettre VARCHAR(25);
string_inverse VARCHAR(10);
BEGIN
FOR i IN REVERSE 1.. LENGTH(str) LOOP
lettre := SUBSTR(str, i, 1);
-- Concaténer lettre à la variable string_inverse
string_inverse := string_inverse || ” ||lettre;
END LOOP;
IF string_inverse = str THEN
dbms_output.Put_line(string_inverse||”||’ est un palindrome’);
ELSE
dbms_output.Put_line(string_inverse||”||’ n'est pas un palindrome’);
END IF
END;
18. Quelle commande utilisez-vous pour supprimer un package ?
Nous utilisons la commande DROP PACKAGE pour supprimer un package. Syntaxe:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DROPPACKAGE [BODY] Nom_attribut.Nom_de_package;
DROP PACKAGE [BODY] Nom_attribut.Nom_de_package;
DROP PACKAGE [BODY] Nom_attribut.Nom_de_package;
19. Comment exécuter une procédure stockée ?
EXECUTE ou le mot-clé EXEC peuvent être utilisés pour exécuter les procédures stockées. Syntaxe:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
EXECUTE nom_de_procedure;
-- ou
EXEC nom_de_procedure;
EXECUTE nom_de_procedure;
-- ou
EXEC nom_de_procedure;
EXECUTE nom_de_procedure;
-- ou
EXEC nom_de_procedure;
20. Expliquez les paramètres IN, OUT et IN OUT.
IN : le paramètre IN permet de transmettre des valeurs à la procédure appelée. Le paramètre IN se comporte comme une constante.
OUT : L’appelant reçoit une valeur du paramètre OUT. Il s’agit d’une variable non initialisée.
IN OUT : le paramètre IN OUT donne des valeurs de départ à une procédure et envoie les valeurs mises à jour à l’appelant. Le paramètre IN OUT doit être considéré comme une variable initialisée.
21. Faites la différence entre %ROWTYPE et %TYPE.
%ROWTYPE : Il est utilisé pour déclarer une variable qui a la structure des enregistrements d’une table.
%TYPE : Pour déclarer une colonne dans une table qui contient la valeur de cette colonne, utilisez la propriété %TYPE. Le type de la variable et la colonne de la table sont identiques.
22. Pourquoi les mots-clés SYSDATE et USER sont-ils utilisés ?
L’heure et la date actuelles du serveur de base de données local sont renvoyées par le mot-clé SYSDATE. Exemple :
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECTSYSDATEFROM dual;
SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual;
L’identifiant de l’utilisateur de la session en cours sera renvoyé en utilisant le mot-clé USER. Exemple :
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECTUSERFROM dual;
SELECT USER FROM dual;
SELECT USER FROM dual;
23. À quoi sert la condition WHEN dans un trigger ?
Le déclencheur(trigger) se déclenche lorsqu’une certaine condition est remplie. Exemple :
Le programme suivant crée un trigger pour la table employees qui se déclencherait pour les opérations INSERT, UPDATE ou DELETE effectuées sur la table employees. Ce déclencheur affichera la différence de salaire entre les anciennes et les nouvelles valeurs.
dbms_output.put_line('Différence de salaire: ' || salaire_diff);
END;
/
CREATE OR REPLACE TRIGGER afficher_changement_salaire
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.ID > 0) --Si la condition est remplie le trigger se déclenche
DECLARE
salaire_diff number;
BEGIN
salaire_diff := :NEW.salaire - :OLD.salaire;
dbms_output.put_line('Ancien salaire: ' || :OLD.salaire);
dbms_output.put_line('Nouveau salaire: ' || :NEW.salaire);
dbms_output.put_line('Différence de salaire: ' || salaire_diff);
END;
/
CREATE OR REPLACE TRIGGER afficher_changement_salaire
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.ID > 0) --Si la condition est remplie le trigger se déclenche
DECLARE
salaire_diff number;
BEGIN
salaire_diff := :NEW.salaire - :OLD.salaire;
dbms_output.put_line('Ancien salaire: ' || :OLD.salaire);
dbms_output.put_line('Nouveau salaire: ' || :NEW.salaire);
dbms_output.put_line('Différence de salaire: ' || salaire_diff);
END;
/
24. Quelles sont les différences entre SQL et PL/SQL ?
SQL
PL/SQL
SQL gère un système de gestion de base de données relationnelle.
PL/SQL est un langage de programmation pour les bases de données.
SQL peut effectuer une seule opération à la fois.
PL/SQL peut exécuter plusieurs opérations en même temps.
SQL est un langage d’interprétation.
PL/SQL est un langage procédural.
Aucune variable n’est utilisée dans SQL.
PL/SQL se compose de variables, de types de données, etc.
SQL se connecte directement au serveur de base de données.
PL/SQL ne se connecte pas directement au serveur de base de données.
C’est un langage orienté données.
Il s’agit d’un langage orienté application.
Il est utilisé pour effectuer des opérations DDL et DML.
PL/SQL exécute des blocs, des fonctions, des procédures et des triggers.
25. À quoi sert WHERE CURRENT OF dans les curseurs ?
Pour identifiez la dernière ligne récupéré on utilise WHERE CURRENT OF dans le curseur. Nous pouvons utiliser l’instruction WHERE CURRENT OF pour mettre à jour ou supprimer des enregistrements sans utiliser l’instruction SELECT FOR UPDATE. L’enregistrement qui a été récupéré pour la dernière fois par le curseur peut être mis à jour ou supprimé à l’aide de l’instruction WHERE CURRENT OF.
Syntax:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
UPDATE nom_table
SET la_clause
WHERECURRENTOF nom_cursor;
UPDATE nom_table
SET la_clause
WHERE CURRENT OF nom_cursor;
UPDATE nom_table
SET la_clause
WHERE CURRENT OF nom_cursor;
OU:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DELETEFROM nom_table
WHERECURRENTOF nom_cursor;
DELETE FROM nom_table
WHERE CURRENT OF nom_cursor;
DELETE FROM nom_table
WHERE CURRENT OF nom_cursor;
26. Comment attribuer un nom à un bloc d’exception PL/SQL sans nom ?
Le nom du bloc d’exception PL/SQL peut être attribué à l’aide d’un pragma appelé EXCEPTION_INIT.
Le message d’erreur et le numéro d’erreur peuvent être définis à l’aide de la fonction Pragma EXCEPTION_INIT.
WHEN nom_exception THEN-- étapes de traitement des exceptions
END
DECLARE
nom_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(nom_exception, code_erreur);
BEGIN
-- Code
EXCEPTION
WHEN nom_exception THEN -- étapes de traitement des exceptions
END
DECLARE
nom_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(nom_exception, code_erreur);
BEGIN
-- Code
EXCEPTION
WHEN nom_exception THEN -- étapes de traitement des exceptions
END
27. Quelle est la différence entre une table en mutation et une table contraignante ?
Une table qui peut être modifiée à l’aide d’une instruction DML ou avec des déclencheurs définis est dite une table en mutation. La table lue pour une contrainte d’intégrité référentielle est appelée table de contraintes.
28. Quelle est la différence entre SQLERRM et SQLCODE ?
SQLCODE renvoie le numéro d’erreur de l’erreur la plus récente trouvée.
SQLERRM renvoie le message d’erreur pour l’erreur la plus récente.
SQLCODE et SQLERRM peuvent être utilisés dans la gestion des exceptions en PL/SQL pour signaler l’erreur survenue dans le code dans la base de données.
29. Quelle est l’utilité de l’opérateur || ?
L’opérateur || est utilisé pour concaténer les chaînes de caractères. Les fonctions « dbms_output.put_line » et « select statements » utilisent l’opérateur ||. Exemple:
30. Une commande de définition comme la commande CREATE est-elle supportée par PL/SQL ?
Les commandes de définition comme la commande CREATE ne sont pas prises en charge par PL/SQL. Les instructions DDL peuvent créer, modifier ou supprimer des objets du schéma et, par conséquent, modifier les dépendances. Il est donc possible que nous fassions référence à un objet qui a été supprimé à l’aide de l’instruction DDL. Pour éviter ce genre de situation, PL/SQL n’autorise pas les instructions DDL directes.
31. Comment vérifier si une instruction UPDATE a été exécutée ou non, en PL/SQL ?
L’attribut
SQL%NOTFOUND
SQL%NOTFOUND peut être utilisé pour déterminer si l’instruction UPDATE a modifié avec succès des enregistrements. Si la dernière instruction SQL exécutée n’a eu aucune effet sur les lignes, cette variable renvoie TRUE.
32. Expliquez ce qu’est une vue.
Une vue est générée en combinant une ou plusieurs tables. Il s’agit d’une table virtuelle basée sur le résultat des instructions SQL; elle comprend des lignes et des colonnes comme une table réelle. Syntaxe:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CREATEVIEW nom_vue ASSELECT colonnes FROM tables;
CREATE VIEW nom_vue AS SELECT colonnes FROM tables;
CREATE VIEW nom_vue AS SELECT colonnes FROM tables;
