32 Exercices Corrigés PL/SQL: Principes de base

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

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

Déclaration des variables de type scalaire.

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.

DECLARE
  minimum CONSTANT INTEGER := 10;
BEGIN
  NULL;
END;
/

Déclaration d’une variable avec une valeur initiale.

DECLARE
  nbr          INTEGER := 0;
BEGIN
  NULL;
END;
/

Initialiser une variable par (NULL).

DECLARE
  nbr          INTEGER;  -- par défaut reçoit NULL
BEGIN
  NULL;
END;
/

Déclaration d’une variable avec la contrainte NOT NULL.

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.

DECLARE
  nom_emp     employees.nom%TYPE;
BEGIN
  NULL;
END;
/

DECLARE
  "BEGIN" varchar2(20) := 'En majuscule';
  "Begin" varchar2(20) := 'En majuscule minuscule';
  "begin" varchar2(20) := 'En minuscule';
BEGIN
  DBMS_Output.Put_Line("BEGIN");
  DBMS_Output.Put_Line("Begin");
  DBMS_Output.Put_Line("begin");
END;
/

Sortie:

BEGIN
Begin
begin

• PL/SQL est un langage procédural.
• 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.

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:

TYPE nom_du_type IS TABLE OF type_element [NOT NULL] INDEX BY type_indice; 
 
nom_du_tableau nom_du_type;

Exemple:

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; 
/

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.

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

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. Les curseurs implicites les plus courants sont SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT.	Structure des curseurs explicites: nom_du_curseur%nom_attri. Voici quelques curseurs explicites: nom_du_curseur%FOUND, nom_du_curseur%NOTFOUND, nom_du_curseur%ROWCOUNT.

L’instruction COMMIT : Les modifications apportées au cours d’une transaction sont sauvegardées de manière permanente par la commande COMMIT. Syntaxe:

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.

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.

DECLARE
BEGIN
SAVEPOINT sp;
-- votre code;
ROLLBACK TO sp;
END;

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.

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.

Deux types d’exceptions sont présents en PL/SQL.

• Exception prédéfinie
• Exception définie par l’utilisateur

Voici quelques exceptions PL/SQL.

• ZERO_ERROR
• NO_DATA_FOUND
• INVALID_NUMBER
• TOO_MANY_ROWS
• ACCESS_INTO_NULL
• CASE_NOT_FOUND

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.

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:

nom_package.nom_attribut;

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;

Nous utilisons la commande DROP PACKAGE pour supprimer un package. Syntaxe:

DROP PACKAGE [BODY] Nom_attribut.Nom_de_package;

EXECUTE ou le mot-clé EXEC peuvent être utilisés pour exécuter les procédures stockées. Syntaxe:

EXECUTE nom_de_procedure;
-- ou
EXEC nom_de_procedure;

• 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.

• %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.

L’heure et la date actuelles du serveur de base de données local sont renvoyées par le mot-clé SYSDATE. Exemple :

SELECT SYSDATE FROM dual;

L’identifiant de l’utilisateur de la session en cours sera renvoyé en utilisant le mot-clé USER. Exemple :

SELECT USER FROM dual;

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.

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; 
/ 

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.

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:

UPDATE nom_table
SET la_clause
WHERE CURRENT OF nom_cursor;

OU:

DELETE FROM nom_table
WHERE CURRENT OF nom_cursor;

• 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.

Syntaxe:

DECLARE
nom_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(nom_exception, code_erreur);
BEGIN
-- Code
    EXCEPTION
WHEN nom_exception THEN -- étapes de traitement des exceptions
END

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.

• 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.

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:

DECLARE 
   str1 string(10) := 'Welcome';
   str2 string(10) := 'to';
   str3 string(10) := 'WayToLearnX!';
   
BEGIN 
   dbms_output.put_line(str1 || str2 || str3);   
END;

Sortie:

Welcome to WayToLearnX!

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.

L’attribut 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.

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:

CREATE VIEW nom_vue AS SELECT colonnes FROM tables;