Introduction au langage PL/SQL

Dr. Mohamed Anis BACH TOBJI Maitre assistant à l’ESEN – Université de Manouba

Notes:

Caractéristiques du PL/SQL

Un langage procédural qui étend SQL. Il est propre à Oracle Modulaire, grâce aux blocs, fonctions & procédures, ainsi que les packages Déclaratif : déclaration de variables, constantes, exceptions, de curseur. Fournit les structures itératives et conditionnelles Traitement des erreurs (routines ou définies par l’utilisateur) Portabilité vers n’importe quel environnement supportant Oracle Intégration : pas de problèmes d’intégration entre Oracle et les environnements de développements Performance : les ordres SQL regroupés dans un bloc PL/SQL sont exécutés en une seule fois

$D:\Enseignement\3- PL-SQL\performance.PNG$

Notes:

Conventions de programmation

Conventions du code En majuscule Les commandes SQL (SELECT, FROM, WHERE etc.) Les mots clé PL/SQL (DECLARE, BEGIN, NUMBER etc.) En minuscule Les noms de variables PL/SQL Les noms de colonnes, tables, vues etc.

Notes:

Conventions de programmation

Conventions de nommage des variables

$D:\Enseignement\3- PL-SQL\variables.PNG$

$D:\Enseignement\3- PL-SQL\variables2.PNG$

Notes:

Structure d’un bloc PL/SQL

Un bloc PL/SQL contient trois sections : Une section déclarative: optionnelle, contient les déclarations des variables, constantes, curseurs, exceptions, etc. Commence par le mot clé DECLARE Une section exécutable: obligatoire, contient les requêtes SQL et les instructions PL/SQL. Commence par le mot BEGIN Une section de traitement d’erreurs : optionnelle, contient les instructions à exécuter au cas où des erreurs se produisent. Commence par le mot EXCEPTION À noter que le bloc se termine par le mot clé END

Notes:

Structure d’un bloc PL/SQL

Exemple de bloc PL/SQL DECLARE vsal NUMBER; BEGIN SELECT sal INTO vsal FROM emp WHERE ename=‘SMITH’; EXCEPTION WHEN NODATAFOUND THEN … END

Notes:

Les structures de programme PL/SQL

Les blocs anonymes Les procédures et fonctions stockées Les procédures et fonctions d’application Les packages Les triggers (déclencheurs) de base de données Les triggers (déclencheurs) d’application

Notes:

Les règles syntaxiques

Un identifiant : Max de 30 caractères, doit débuter par une lettre. Ne doit pas être un mot réservé. Son nom doit être différent des noms de colonnes. Les chaînes de caractères et les dates constantes entre simples quotes. Les commentaires sur plusieurs lignes doivent être inclus entre les caractères " /* " et " */ " Les caractères "- -" sont utilisés pour mettre une seule ligne en commentaire.

Notes:

Redondance : Certaines donnés se répètent dans les fichiers Manipulation lourde : Le programmeur doit écrire des programmes pour la mise à jour, la consultation, la suppression, le contrôle… vs un langage de gestion de donénes apportés par les BD La manipulation des données est limitée par ce qui est programmé dans les applications La gestion des utilisateurs est elle aussi programmée (ce n’est pas le cas avec les SGBD)

La base de données exemple

$D:\Enseignement\3- PL-SQL\la_base.PNG$

Notes:

Les types de variables

Il existe deux types de variables qui fonctionnent sous PL/SQL Les variables PL/SQL Variable scalaire : contenant une valeur unique Variable composée : contenant plusieurs valeurs comme le RECORD, le TABLE, le NESTED TABLE, le VARRAY Variable référence : variable qui pointe vers un type de données LOB : variable localisateur d’objets volumineux tel que les images et les vidéos Les variables non PL/SQL Les variables de substitution Les variables hôtes

Notes:

Les types de données scalaires

Les principaux types de données scalaires Les types numériques INTEGER, POSITIVE PLSINTEGER, BINARYINTEGER NUMBER(n,d), DECIMAL, REAL Les types chaine de caractère LONG, VARCHAR2(n) CHAR(n) Le type booléen BOOLEAN (true, false ou null) Le type date DATE

Notes:

Les variables scalaires

Déclaration et initialisation

variable_name datatype [:=|DEFAULT expr];

datatype : le type de données de la variable, qui est soit scalaire, composé, référence ou LOB. CONSTANT : contraint la variable à être une constante NOT NULL : ce mot clé contraint la variable à contenir une valeur. expr : valeur initiale d’une variable, peut être une valeur littérale, une autre variable ou une expression impliquant des opérateurs et des fonctions.

$D:\Enseignement\3- PL-SQL\ex_var_sca.PNG$

Notes:

Les variables scalaires

La déclaration de plusieurs variables sur la même ligne est interdite

Affectation

variable := expression; DEFAULT : contraint la variable à être une constante SELECT expression INTO variable FROM...

Impossible d’affecter la valeur NULL à une variable déclarée NOT NULL (l’erreur VALUE_ERROR est renvoyée) La contrainte NOT NULL doit être suivie par une initialisation

$D:\Enseignement\3- PL-SQL\ex_aff_var_sca.PNG$

Notes:

Les variables scalaires

Le mot clé %TYPE déclare une variable ayant le même type d’une autre variable ou d’une colonne d’une table ou vue existantes.

Code PL/SQL	Commentaires
DECLARE v_job emp.job%TYPE;	v_job prend le type de la colonne job de la table emp.
v_prime NUMBER(5,2) := 500.50; v_prime_min v_prime%TYPE := v_prime*2; BEGIN …	v_prime et est initialisée à 500,45. v_prime_min prend le type de la variable v_prime

Notes:

Les variables composées – le RECORD

Le mot clé %ROWTYPE déclare une variable RECORD ayant la même structure que l’enregistrement d’une table/vue.

Code PL/SQL	Commentaires
DECLARE emp_record emp%ROWTYPE; v_sal NUMBER:=800;	Emp_record prend la structure d’un enregistrement de la table emp.
BEGIN emp_record.empno:=2564; emp_record.sal:=v_sal+100; …	Accès aux attributs de l’enregistrement

Notes:

Les variables composées – le RECORD

Le mot clé %ROWTYPE déclare une variable RECORD ayant la même structure que l’enregistrement d’une table/vue.

À noter qu’avec la directive %ROWTYPE, les attributs n’héritent pas la contrainte NOT NULL.

Code PL/SQL	Commentaires
DECLARE emp_record emp%ROWTYPE; v_sal NUMBER:=800;	Emp_record prend la structure d’un enregistrement de la table emp.
BEGIN emp_record.empno:=2564; emp_record.sal:=v_sal+100; …	Accès aux attributs de l’enregistrement

Notes:

Les variables composées – le RECORD

Pour définir un enregistrement personnalisé, il faut déclarer un type RECORD

Ensuite, pour l’utiliser, il faut déclarer une variable avec le type nomRecord

$D:\Enseignement\3- PL-SQL\ex_type_record.PNG$

$D:\Enseignement\3- PL-SQL\ex_type_record2.PNG$

Notes:

Les variables composées – le type TABLE

Le type TABLE permet la déclaration de tableaux dynamiques (sans taille initiale fixe) Une variable de type TABLE inclut deux colonnes, une clé primaire de type BINARY_INTEGER et une colonne de type scalaire ou RECORD.

Primary key	Colonne
…	…
1	Mohamed Toumi
2	Sami Tlili
3	Meriam Bouzid
…	…

Notes:

Les variables composées – le type TABLE

Le type TABLE est défini avec cette syntaxe

La déclaration d’une variable se fait selon cette syntaxe

$D:\Enseignement\3- PL-SQL\type_table.PNG$

$D:\Enseignement\3- PL-SQL\var_type_table.PNG$

$D:\Enseignement\3- PL-SQL\ex_type_table.PNG$

Notes:

Les variables composées – le type TABLE

Il existe des procédures et des fonctions qui permettent de manipuler les variables TABLE :

$D:\Enseignement\3- PL-SQL\fonc_proc_table.PNG$

$D:\Enseignement\3- PL-SQL\ex_fct_proc_tab.PNG$

$D:\Enseignement\3- PL-SQL\ex_type_table.PNG$

Notes:

Les variables non-PL/SQL

Les variables hôtes (dite aussi de session) C’est une variable définie dans l’environnement hôte du programme PL/SQL (SQLPlus, Forms Developer…) Sous SQLPlus, l’utilisation d’une variable hôte se fait ainsi :

Instructions	Commentaires
VARIABLE g_compteur NUMBER; DECLARE v_compt NUMBER := 99; BEGIN :g_compteur := v_compt+1; END; / PRINT g_compteur;	Déclaration de la variable sous SQLPlus Manipulation de la variable (ajout de :) Affichage de la variable sous SQLPlus

Notes:

Les variables non-PL/SQL

Les variables de substitution Une variable de substitution est définie sous SQLPlus. Sa valeur est saisie sous SQLPlus, et est remplacée intégralement dans le bloc L’utilisation d’une variable de substitution :

Instructions	Commentaires
SET SERVEROUTPUT ON ACCEPT s_nbr PROMPT ‘Saisir un entier’; DECLARE v_doub NUMBER; BEGIN v_doub := &s_nbr; DBMS_OUTPUT.PUT_LINE(‘Le résultat est ‘		v_doub); END;	Nécessaire à l’affichage Saisie de la variable de session s_nbr Manipulation de la variable (ajout du &) Affichage de la variable PL/SQL v_doub

Notes:

Les opérateurs et les fonctions

Les opérateurs
Arithmétiques
+ , - , * , /
Logiques
AND , OR, NOT
Concaténation

Comparaison
= , != , < , > , <= , >= , LIKE , BETWEEN , IS NULL , IN

Les fonctions
Disponibles dans les instructions PL/SQL
Toutes les fonctions de ligne (lower, substr, length, round, add_months etc.)

Notes:

Les blocs imbriqués

Blocs PL/SQL
DECLARE var1 BEGIN … DECLARE var2 BEGIN … DECLARE var3 BEGIN … END; END; … END;

Notes:

Introduction au langage PL/SQL

Dr. Mohamed Anis BACH TOBJI Maitre assistant à l’ESEN – Université de Manouba

Notes:

Caractéristiques du PL/SQL

$D:\Enseignement\3- PL-SQL\performance.PNG$

Notes:

Conventions de programmation

Notes:

Conventions de programmation

Conventions de nommage des variables

$D:\Enseignement\3- PL-SQL\variables.PNG$

$D:\Enseignement\3- PL-SQL\variables2.PNG$

Notes:

Structure d’un bloc PL/SQL

Notes:

Structure d’un bloc PL/SQL

Exemple de bloc PL/SQL DECLARE vsal NUMBER; BEGIN SELECT sal INTO vsal FROM emp WHERE ename=‘SMITH’; EXCEPTION WHEN NODATAFOUND THEN … END

Notes:

Les structures de programme PL/SQL

Notes:

Les règles syntaxiques

Notes:

La base de données exemple

$D:\Enseignement\3- PL-SQL\la_base.PNG$

Notes:

Les types de variables

Notes:

Les types de données scalaires

Notes:

Les variables scalaires

Déclaration et initialisation

variable_name datatype [:=|DEFAULT expr];

$D:\Enseignement\3- PL-SQL\ex_var_sca.PNG$

Notes:

Les variables scalaires

La déclaration de plusieurs variables sur la même ligne est interdite

Affectation

variable := expression; DEFAULT : contraint la variable à être une constante SELECT expression INTO variable FROM...

Impossible d’affecter la valeur NULL à une variable déclarée NOT NULL (l’erreur VALUE_ERROR est renvoyée) La contrainte NOT NULL doit être suivie par une initialisation

$D:\Enseignement\3- PL-SQL\ex_aff_var_sca.PNG$

Notes:

Les variables scalaires

Le mot clé %TYPE déclare une variable ayant le même type d’une autre variable ou d’une colonne d’une table ou vue existantes.

Code PL/SQL	Commentaires
DECLARE v_job emp.job%TYPE;	v_job prend le type de la colonne job de la table emp.
v_prime NUMBER(5,2) := 500.50; v_prime_min v_prime%TYPE := v_prime*2; BEGIN …	v_prime et est initialisée à 500,45. v_prime_min prend le type de la variable v_prime

Notes:

Les variables composées – le RECORD

Le mot clé %ROWTYPE déclare une variable RECORD ayant la même structure que l’enregistrement d’une table/vue.

Code PL/SQL	Commentaires
DECLARE emp_record emp%ROWTYPE; v_sal NUMBER:=800;	Emp_record prend la structure d’un enregistrement de la table emp.
BEGIN emp_record.empno:=2564; emp_record.sal:=v_sal+100; …	Accès aux attributs de l’enregistrement

Notes:

Les variables composées – le RECORD

Le mot clé %ROWTYPE déclare une variable RECORD ayant la même structure que l’enregistrement d’une table/vue.

À noter qu’avec la directive %ROWTYPE, les attributs n’héritent pas la contrainte NOT NULL.

Code PL/SQL	Commentaires
DECLARE emp_record emp%ROWTYPE; v_sal NUMBER:=800;	Emp_record prend la structure d’un enregistrement de la table emp.
BEGIN emp_record.empno:=2564; emp_record.sal:=v_sal+100; …	Accès aux attributs de l’enregistrement

Notes:

Les variables composées – le RECORD

Pour définir un enregistrement personnalisé, il faut déclarer un type RECORD

Ensuite, pour l’utiliser, il faut déclarer une variable avec le type nomRecord

$D:\Enseignement\3- PL-SQL\ex_type_record.PNG$

$D:\Enseignement\3- PL-SQL\ex_type_record2.PNG$

Notes:

Les variables composées – le type TABLE

Primary key	Colonne
…	…
1	Mohamed Toumi
2	Sami Tlili
3	Meriam Bouzid
…	…

Notes:

Les variables composées – le type TABLE

Le type TABLE est défini avec cette syntaxe

La déclaration d’une variable se fait selon cette syntaxe

$D:\Enseignement\3- PL-SQL\type_table.PNG$

$D:\Enseignement\3- PL-SQL\var_type_table.PNG$

$D:\Enseignement\3- PL-SQL\ex_type_table.PNG$

Notes:

Les variables composées – le type TABLE

Il existe des procédures et des fonctions qui permettent de manipuler les variables TABLE :

$D:\Enseignement\3- PL-SQL\fonc_proc_table.PNG$

$D:\Enseignement\3- PL-SQL\ex_fct_proc_tab.PNG$

$D:\Enseignement\3- PL-SQL\ex_type_table.PNG$

Notes:

Les variables non-PL/SQL

Instructions	Commentaires
VARIABLE g_compteur NUMBER; DECLARE v_compt NUMBER := 99; BEGIN :g_compteur := v_compt+1; END; / PRINT g_compteur;	Déclaration de la variable sous SQLPlus Manipulation de la variable (ajout de :) Affichage de la variable sous SQLPlus

Notes:

Les variables non-PL/SQL

Instructions	Commentaires
SET SERVEROUTPUT ON ACCEPT s_nbr PROMPT ‘Saisir un entier’; DECLARE v_doub NUMBER; BEGIN v_doub := &s_nbr; DBMS_OUTPUT.PUT_LINE(‘Le résultat est ‘		v_doub); END;	Nécessaire à l’affichage Saisie de la variable de session s_nbr Manipulation de la variable (ajout du &) Affichage de la variable PL/SQL v_doub

Notes:

Les opérateurs et les fonctions

Les opérateurs
Arithmétiques
+ , - , * , /
Logiques
AND , OR, NOT
Concaténation

Comparaison
= , != , < , > , <= , >= , LIKE , BETWEEN , IS NULL , IN

Les fonctions
Disponibles dans les instructions PL/SQL
Toutes les fonctions de ligne (lower, substr, length, round, add_months etc.)

Notes:

Les blocs imbriqués

Blocs PL/SQL
DECLARE var1 BEGIN … DECLARE var2 BEGIN … DECLARE var3 BEGIN … END; END; … END;