Introduction à pl/sql

PL/SQL (Procedural Language / SQL), l’extension procédurale proposée par Oracle pour SQL (L4G),
Il permet de combiner des requêtes SQL (SELECT, INSERT, UPDATE et DELETE) et des instructions procédurales (boucles, conditions...),
Créer des traitements complexes destinés à être stockés sur le serveur de base de données (objets serveur),
Comme on le sait, les structures de contrôle habituelles d’un langage (IF, WHILE…) ne font pas partie intégrante de la norme SQL. Oracle les prend en compte dans PL/SQL.

1. AbdelouahedSabri
[email protected] 1
INTRODUCTION ÀLA
PROGRAMMATION PL/SQL

2. PLAN
Introduction,
Avantages de PL/SQL,
Les principales caractéristiques du PL / SQL,
Les variables et constantes,
Les structures de contrôle
Interaction avec la base de données à partir de PL/SQL
Les sous programmes PL/SQL
Déclencheurs (Triggers)
2

3. BIBLIOGRAPHIE
Christian Soutou«SQL pour Oracle»
Gilles Briard «Oracle10g sous Windows»
John Russell «PL_SQL ReferenceUser guide 10g
Release»
Denis Roegel«Le langage procédural PL/SQL»
SheikYerbouti«Oracle PL/SQL»
3

4. PRÉ-REQUISPOURCECOURS
Des connaissances dans le Structured Query
Language SQL,
des connaissances de base dans la
programmation (IF-THEN comparaisons,
boucles, …)
Des connaissances dans le SGBD Oracle,
4

5. INTRODUCTION
PL/SQL (ProceduralLanguage/ SQL), l’extension
procédurale proposée par Oracle pour SQL (L4G),
Il permet de combiner des requêtes SQL
(SELECT, INSERT, UPDATE et DELETE) et des
instructions procédurales (boucles, conditions...),
Créer des traitements complexes destinés à être
stockés sur le serveur de base de données (objets
serveur),
Comme on le sait, les structures de contrôle
habituelles d’un langage (IF, WHILE…) ne font pas
partie intégrante de la norme SQL. Oracle les prend
en compte dans PL/SQL.
5

6. AVANTAGES DEPL/SQL 1/2
Ce langage propose des-performances pour le
traitement des transactions et offre les avantages
suivants:
Intégration complète du SQL,
Prise en charge de la programmation orientée objet
(surcharge),
Parfaite Intégration avec Oracle et Java,
en fait PL/SQL est un langage propriétaire de Oracle,
On peut lancer des sous-programme Pl/SQL à partir de
Java et de même, on peut appeler des procédures Java à
partir d’un block Pl/SQL
6

7. AVANTAGES DEPL/SQL 2/2
Portabilité totale: Toutes les bases de données Oracle
comportent un moteur d’exécution PL/SQL.
lesprogrammesPL/SQLsontindépendantsdusystème
d’exploitationquihébergeleserveurOracle.Enchangeant
desystème,lesapplicatifsn’ontpasàêtremodifiés.
Facilité de programmation:
PL/SQLestunlangagesimpled’apprentissageetdemise
enœuvre.Sasyntaxeclaireoffreunegrandelisibilitéen
phasedemaintenancedevosapplications.Denombreux
outilsdedéveloppement,autresqueceuxd’Oracle10g,
autorisentlaprogrammationenPL/SQLdanslabasede
données.
7

8. AVANTAGES DEPL/SQL
EXEMPLED’INTÉGRATION ENTREPL/SQL ETSQL
Vous n'avez pas à traduire les types de données entre SQL
et PL/SQL:
OnretrouveraavecPL/SQLtouslestypesdedonnéeset
instructionsdisponiblessousSQL,etdesmécanismespour
parcourirdesrésultatsderequêtes(curseurs),pourtraiterdes
erreurs(exceptions),pourmanipulerdesdonnéescomplexes
(paquetagesDBMS_xxx)etpourprogrammerdestransactions
«COMMIT,ROLLBACK,SAVEPOINT»
Cetteintégrationnouspermetd'économiseràlafoisle
tempsd'apprentissageetletempsd’exécution.Les
fonctionnalitésdulangagePL/SQLnouspermettentde
travailleravecdescolonnesetdeslignesdestablessans
préciserlestypesdedonnées,cequinouspermet
d’économiserdutempsd'entretienlorsqueladéfinitiondes
tableschange.
8

9. AVANTAGES DEPL/SQL
PL/SQL PROPOSEUNEBONNEPERFORMANCE
DansPL/SQL,cenesontplusdesrequêtesSQLqui
sonttransmisunàunaumoteurdebasededonnées,
maisdesblocsetdessous-programmes,Alors:
Letraitementdesdonnéesestdoncinterneàlabase,cequi
réduitconsidérablementletraficentrecelle-ciet
l’application,
IlestpréférabledetravailleravecunblocPL/SQLplutôt
qu’avecunesuited’instructionsSQLsusceptibles
d’encombrerletraficréseau
9

10. AVANTAGES DEPL/SQL
PL/SQL PROPOSEUNEBONNEPERFORMANCE
Dansunenvironnementclient-
serveur,chaqueinstruction
SQLdonnelieuàl’envoid’un
messageduclientversle
serveursuividelaréponsedu
serveurversleclient.
UnblocPL/SQLdonnelieuà
unseuléchangesurleréseau
entreleclientetleserveur.Les
résultatsintermédiairessont
traitéscôtéserveuretseulle
résultatfinalestretournéau
client 10

11. LESPRINCIPALES CARACTÉRISTIQUES DU
PL/SQL
PL/SQLcombinelapuissancedemanipulationde
donnéesd’SQLaveclapuissancedetraitementdes
langagesprocédurales:
Vouspouvezcontrôlerledéroulementduprogrammeavec
desdéclarationscommeIFetLOOP
Vouspouvezdéclarerdesvariables,définirdesprocédures
etfonctionsetfairelagestiondesdifférentesexceptions,
PL/SQLvouspermetdedécomposerdesproblèmes
complexesencompréhensiblecode(ouprocédures),et
permetl’utilisationdececodedansplusieursapplications.
11

12. LESPRINCIPALES CARACTÉRISTIQUES DU
PL/SQL
La figure suivante présente la façon avec laquelle le
moteur PL/QL traite un bloc PL/SQL anonyme.
LemoteurPL/SQLexécutedesinstructionsprocédurales,
maisenvoidesinstructionsaumoteurSQLdanslabasede
donnéesOracle.
12

13. STRUCTURE D’UNPROGRAMME PL/SQL
PL/SQLestunlangagestructuréenblocs,constitués
d'unensembled'instructions,
UnblocPL/SQLpeutêtre"externe",onditalorsqu'il
estanonyme,oualorsstockédanslabasededonnées
sousformedeprocédure,fonctionoutrigger(onlui
affecteunnom),
UnblocPL/SQLcontienttroisparties:
1.unepartiedéclarative,
2.unepartieexécutable,
3.unepartiepourlagestiondesexceptions.
NB:Seulelapartieexécutableestnécessaire.
13

14. STRUCTURE D’UNPROGRAMME PL/SQL
Lapremièrepartied’uncodeestlapartiedéclarative:
Définitiondestypes,variablescurseurs,exceptions,etc.
Cesélémentssontmanipulésdanslapartieexécutable.
LenomréservéestDECLARE.
NB:Cettesectionestoptionnelle.
Ladeuxièmepartieestlapartieexécutable:
ContientlecodePL/SQLincluantounondesdirectivesSQL.
CettepartiedecodedébuteparlemotréservéBEGINetse
termineparlemotréservéEND;(nepasoublierlepoint-
virgule).
NB:Cettesectionestobligatoire.
Latroisièmesectionestlapartiedegestiondes
exceptions.
CettepartiedébuteparlemotréservéEXCEPTION .
EllepermetdetraiterleserreursretournéesparleSGBDàla
suited’exécutionsd’instructionsSQL.
NB:Cettepartieestoptionnelle. 14

15. STRUCTURE D’UNPROGRAMME PL/SQL
Un bloc peut être imbriqué dans le code d’un autre bloc
(on parle de sous-bloc).
NB: Un sous-bloc commence par BEGIN et se termine par
END;
15
NB: Un sous-bloc peut aussi se trouver dans la partie des exceptions
La portée d’un objet (variable, type, curseur, exception,
etc.) est la zone du programme qui peut y accéder

16. LEJEUDECARACTÈRES ENPL/SQL
Les caractères qui sont interprétés par PL/SQL, comme
SQL, sont:
Les alphanumérique: lettres A à Z et a à z ; chiffres de 0 à 9
Les symboles ( ) + -* / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ]
Les caractères spéciaux comme: tabulations, espaces et
retours-chariot
NB: PL/SQL n’est pas sensible à la casse Tva=TVA
16

17. LEJEUDECARACTÈRES ENPL/SQL
Pour une meilleure lisibilité, on peut séparer les
unités lexicales par des espaces, des tabulations
et des retours à la ligne.
…
IF x>y THEN max:=x;ELSEmax:=y;ENDIF;
Code (correct) peu lisible
…
IF x > y THEN
max := x;
ELSE
max := y;
END IF;
…
Code lisible
17

18. LESIDENTIFIANTS
Avantdeparlerdesdifférentstypesdevariables
PL/SQL,décrivonscommentilestpossibledenommer
desobjetsPL/SQL(variables,curseurs,exceptions,
etc.).
Unidentificateurcommenceparunelettresuivie(optionnel)
desymboles(lettres,chiffres,$,_,#).
Certainssymbolessontinterdits:&,-,/,+,:
Unidentificateurpeutcontenirjusqu’àtrentecaractères.
Identifiants autorisés Identifiants non autorisés
A
AA2
TVA#
Hors_Taxe
Requéte$Sql
A&A (symbole &)
2A (debutchiffre)
Hors-Taxe(symbole -)
Vrai/Faux (symbole /)
RequéteSql(symbole espace)
18

19. LESCOMMENTAIRES
PL/SQL supporte deux types de commentaires :
1.Mono-lignes;
commençant au symbole --et finissant à la fin de la ligne,
2.Multi-lignes;
commençant par /*et finissant par */
19
BEGIN
…
--Lecture du nom d’un utilisateur utilisant son ID
SELECT nom INTO v_nomFROM T_USER WHERE id= 5;
/*
Ecriture dans la table Log pour spécifier quel utilisateur est connecté à l’application en spécifiant:
1-le nom
2-l’heure et la date
*/
INSERT INTO log VALUES (v_nom, v_heure, v_date);
…
END;

20. LESMOTSRÉSERVÉS
Certainsidentifiants,appelésmotsréservés,ontune
significationsyntaxiquesparticulièredansPL/SQL.
Parexemple,lesmotsBEGINetENDsontréservés.
Uneredéfinitiond’unmotréservéprovoqueuneerreurde
compilation
Souvent,lesmotsréservéssontécritsenmajusculespour
unemeilleurelisibilité
20

21. EXÉCUTIOND’UNPROGRAMME PL/SQL
PourexécuterunprogrammePL/SQL,l’outilleplus
simplelivréparOracleestSQL*Plus:
Cetoutilpermet:
D’accéderàl’ensembledesfonctionnalitésd’Oracle
(10g)àpartird’uneinterfacetextuelleidentique
(Console),quelquesoitlesystèmed’exploitationutilisé
D’exécuterdefaçoninteractivedesordresSQL
unitaires,
Delancerdesfichierscontenantunensembled’ordres
SQL.
21

22. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
Il existe plusieurs interfaces SQL*Plus Sous Windows:
En mode ligne de commande (qui ressemble à une fenêtre DOS ou
telnet)
Avec une interface graphique SQL*Plus Worksheetde l’outil
Enterprise Manager
Avec le navigateur via l’interface Web iSQL*Plus (i comme «
Internet »:
Cette interface s’apparente assez à celle de EasyPHPen étant très
intuitive).
NB: Les interfaces graphiques SQL*Plus et iSQL*Plus ne
sont plus offertes depuis la version 11g.
L’interface en mode ligne de commande reste toutefois disponible
pour toutes les versions.
Pour cela on va s’intéresser à SQL*Plus en mode ligne de
commande.
22

23. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
PourlancerSQL*Plus,tapezdansunefenêtre
ConsoleDOS(biensuronsupposequeOracle(10g)
estinstallé):
C:\> sqlplus
Ily’adeuxmodesdeconnexions:
Modeutilisationsimpledelabasededonnées,
Modeavecprivilègespouradministration.
LeprivilègeSYSOPER:permetàunadministrateur
delabasededonnéesd’exécuter:STARTUP,
SHUTDOWN, ALTERDATABASE OPEN/MOUNT,
ALTERDATABASE BACKUP,ARCHIVELOG,and
RECOVER
LeprivilègeSYSDBA:contienttoutlesprivilègesy
comprisADMINOTPIONetSYSOPER
23

24. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
Pour se connecter en mode administration, dans
la fenêtre Console DOS on tape:
# Connexion avec un utilisateur ayant reçu le privilège SYSDBA
connectutilisateur/passwdas SYSDBA.
# connexion avec un utilisateur ayant reçu le privilège SYSOPER
connectutilisateur/passwdas SYSOPER
24

25. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
-Spourspécifierquec’estunmode«silencieux»
l’utilisateuretlemotdepassepeuventêtresuivisdel’alias
OracleNetdelabasecibleàlaquellevoussouhaitezvous
connecter.Danscecas,lesigne@estaccoléaumotdepasse
unfichierdecommandecontenantdesordresSQL,des
commandesSQL*PlusetPL/SQLpeutêtreautomatiquement
lancé,unespacedoitalorsprécéderlesigne@
C:\> sqlplus-S utilisateur/mot_de_passe@base_cible@fichier_sql
25
On peut aussi saisir des options lors du lancement de
SQL*Plus :

26. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
Quelques commandes utiles:
HOST pour lancer des commandes OS à partir de la session
SQL*Plus,:
Pour lister le contenu d’un répertoire:
HOST DIR
NB: Celle-ci ne termine pas votre session SQL*Plus
DISC (DISCONNECT) pour se déconnecter de la base Oracle cible
EXIT ou QUIT pour se déconnecter et terminer une
session SQL*Plus
LISTou Lpermet de visualiser la dernière commande
SQL saisie
RUNou Rexécute l’ordre SQL présent dans le buffer
NB: Un bloc d’instructions PL/SQL doit se terminer par une
«/».
26

27. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
Quelques commandes utiles:
ED (EDIT)
permet d’appeler l’éditeur de texte et y place l’ordre SQL
présent dans le buffer courant.
Pour préciser l’éditeur de texte de votre choix (ici notepad), utilisez
la commande SQL*Plus ;
SQL> def_editor= notepad
edmon_fichier.sql
# si le fichier n’existe pas, il sera crée
Pour exécuter un fichier de commande à partir de SQL*Plus
startmon_fichier.sql
--ou bien
@mon_fichier.sql
27

28. EXÉCUTIOND’UNPROGRAMME PL/SQL
INTERFACESSQL*PLUS
Quelques commandes utiles:
SET TIMING ON|OFF
permet de mesurer la durée d’exécution de vos ordres SQL (en
millisecondes).
SET SERVEROUTPUT ON|OFF
Permet d’activer ou de désactiver l’affichage à la console
PROMPT ‘Texte à afficher à la console’
Permet d’afficher un texte à la console
ACCEPT nom_var
Permet de récupérer une valeur saisie par l’utilisateur et de la stocker
dans nom_var. pour l’utiliser dans un bloc Pl/SQL on utilise
‘&nom_var’
VARIABLE nom_varTYPE
Déclarer une variable globale on précisant son type
Pour l’utiliser en PL/SQL on utilise :nom_var
PRINT var
Afficher le contenu de var
28

29. EXÉCUTIOND’UNPROGRAMME PL/SQL
EXEMPLE
Exemple:
Créer un nouvel utilisateur.
Nb: On se connecte sous le compte SYSTEM (le mot de
passe SYSTEM est saisi lors de l’installation d’oracle).
connect SYSTEM/pass as SYSDBA
--sousl’utilisateurSYSTEM
create user SIRM identified by SIRMpass;
--Se connecter sous SIRM
ConnectSIRM/SIRMpassas sysdba
--executerun script
start ORACLE_HOME \RDBMS\ADMIN\SCRIPT.SQL
--Afficher le nom de l’utilisateur courant
show user 29

30. LESVARIABLES ETCONSTANTES
PL / SQL permet de déclarer des constantes (valeur
invariable) et des variables pour les utiliser dans des
programme SQL.
Ilfautdéclareruneconstanteouvariableavantdela
référencer.
Déclaration:
Lesvariablesetlesconstantessontdéclarées(et
éventuellementinitialisées)danslasectionDECLARE.
30
nom_de_la_variable[CONSTANT] TYPE_DE_DONNEE [NOT NULL] [:= | DEFAULT expression];
La syntaxe:
EnplusdestypesdedonnéespropreàPL/SQLcommeBOOLEAN
ouPLS_INTEGER, Lesvariablespeuventavoirn'importequel
typeSQLcommeCHAR,DATE,ouNUMBER.

31. LESVARIABLES ETCONSTANTES
DÉCLARATION:
Exemple:
Déclarer une variable nommée v_telephonequi va contenir 10
chiffres (Type NUMBER (10))
Déclarer une variable nommée v_disponiblepour contenir une
valeur booléenne (BOOLEAN) TRUE ou FALSE.
Déclarer une variable v_TVAde type NUMBER et l’initialiser par
0.02
La syntaxe pour déclarer une variable est la suivante:
DECLARE
v_telephoneNUMBER(10);
v_disponibleBOOLEAN;
v_TVANUMBER :=0.02;
31
nom_de_la_variable[CONSTANT] TYPE_DE_DONNEE [NOT NULL] [:= | DEFAULT expression];

32. LESVARIABLES ETCONSTANTES
DÉCLARATION:
Les constantes:
Pour la déclaration des constantes on utilise le
mot réservé CONSTANT
nom_de_la_variable [CONSTANT] TYPE_DE_DONNEE [NOT NULL] [:= | DEFAULT expression];
NB: Il faut initialiser les constantes lors de leur déclaration.
Exemple:
DECLARE
c_TVACONSTANT NUMBER := 0.02;
…
BEGIN
…
END
32

33. LESVARIABLES ETCONSTANTES
DÉCLARATION:
La portée d'une variable:
Est la région d'une unité de programme (bloc ou sous-
programme) à partir duquel on peut faire référence à
cet identifiant.
Les identifiants (variables) qui sont déclarés dans un bloc
PL/SQL sont considérés comme locaux à ce bloc et globaux à
ces sous-blocs.
Bien qu’on ne puisse pas déclarer un identifiant deux fois
dans le même bloc, on peut déclarer le même identifiant
dans deux blocs différents.
Les deux éléments (variables) représentés par l'identifiant sont
distincts, et tout changement dans l'un n'affecte pas l'autre.
Un bloc ne peut pas faire référence à identifiants qui est
déclaré dans l’autre bloc parce que ces identifiants ne sont
ni locaux ni globaux au bloc.
33

34. LESVARIABLES ETCONSTANTES
AFFECTATION:
L’affectation d’une valeur à une variable est effectuée
en PL/SQL en utilisant l’operateur «:=»
L’affectation peut être effectuée dans la partie
déclaration comme dans la partie exécutable;
DECLARE
nom_de_la_variable TYPE_DE_DONNEE [NOT NULL] [:= | DEFAULT expression];
--ou bien
BEGIN
nom_de_la_variable := valeur;
…
END;
34

35. LESVARIABLES ETCONSTANTES
AFFECTATION:
Exemple:
DECLARE
v_telephoneNUMBER(10);
v_disponibleBOOLEAN;
v_DateDeNaissanceDate;
c_TVACONSTANT NUMBER := 0.02;
v_TTCNUMBER ;
v_HTNUMBER ;
BEGIN
v_disponible:=TRUE ;
v_telephone:=0666666666;
v_TTC:= v_HT+ c_TVA;
END;
/
35

36. LESVARIABLES ETCONSTANTES
AFFECTATION:
Une autre façon pour affecter à une variable une valeur est
d’utiliser la directive DEFAULT.
v_paysCHAR(5) := ’MAROC’;
/* Équivaut à
v_paysCHAR(5) DEFAULT 'MAROC';
*/
La troisième façon pour attribuer des valeurs à une variable
consiste à sélectionner (ou récupérer) des valeurs de la base de
données:
Dans l'exemple ci-dessous, nous allons récupérer le nom d’un employé
dont ID=10 et l’enregistrer dans la variable v_nom.
BEGIN
SELECT nom INTO v_nomFROM T_EMPLOYE WHERE ID= 10;
Il existe d’autres façons pour affecter à une variable une valeur
36

37. LESVARIABLES ETCONSTANTES
AFFECTATION:
NB1: La contrainte NOT NULL doit être suivie d’une clause
d’initialisation.
NB2: À l’inverse de la plupart des langages de programmation, les
déclarations multiples ne sont pas permises.
37
NB3: Par défaut, les variables sont initialisées à la valeur NULL
NB4: Seules les valeurs TRUE, FALSE et NULL peuvent être
affectée à une variable booléenne..

38. LESVARIABLES ETCONSTANTES
EXEMPE:
DECLARE
--Déclarations de variables.
v_nomVARCHAR2(7);
v_aliasVARCHAR2(7);
v_ageNUMBER(2);
v_naissanceDATE;
v_existantBOOLEAN NOT NULL DEFAULT FALSE;
BEGIN
v_nom:= 'Mohamed'; --Affectation d’une chaîne de caractères.
v_alias:= v_nom; --Affectation d’une variable.
v_age:= 23; --Affectation d’un nombre.
v_naissance:= '01-01-2000'; --Affectation de dates.
v_existant:= TRUE; --Affectation d’un booléen.
/*Affectation d’une chaîne de caractères par une requête.*/
SELECT nom INTO v_nomFROM T_UserWHERE age= 21;
END;
38

39. LESTYPESDEDONNÉES
EnplusdestypesdedonnéespropreàPL/SQL,onpeut
utilisertouslestypesdedonnéesSQL(NUMBER,CHAR,
BOOLEAN, VARCHAR2,DATE, TIMESTAMP,
INTERVAL,BLOB,ROWID…)
On peut distinguer:
Typescalaire:n'apasdecomposantsinternes.Ilcontientune
valeursingulière,commeunnombreouunechaînedecaractères.
Typecomposé:adescomposantsinternesquipeuventêtre
manipulésindividuellement,tellesquelesélémentsd'untableau.
Typeréférence:contientlesvaleurs,appeléespointeurs,qui
désignentd’autresélémentsduprogramme.
TypeLOB(LargeObjectBinary):estuntypededonnéesORACLE
permettantdestockerdesdonnéesdegrandetailleallantjusqu’à
4Go,telsquedesblocsdetexteoudesimagesgraphiques.
39

40. LESTYPESDEDONNÉES
Les types scalaires sont répartis en quatre familles, ou
on trouve les numéros, caractères, booléens, et les
données date/heure.
40

41. LESTYPESDEDONNÉES
TYPESSCALAIRES
Le type scalaire permet de stocker des données
numériques (entiers, nombres réels, et nombres à
virgule flottante), représenter des quantités et de
faire des calculs.
BINARY_INTEGER,
PLS_INTEGER,
BINARY_FLOAT,
BINARY_DOUBLE,
NUMBER.
41

42. LESTYPESDEDONNÉES
TYPESSCALAIRES
NUMBER
On utilise le type de données NUMBER pour stocker des
numéros à virgule fixe ou à virgule flottante.
Le domaine des valeurs sont de1E-130 à 10E125.
La précision est le nombre de chiffres à droite de la virgule.
La syntaxe est la suivante:
NUMBER[(precision[,scale])] --scalemax = 38, la valeur par défaut est 38
Exemple:
v_noteNUMBER(2,1) :=10.5;
43
v_noteNUMBER:=9.05;
v_noteNUMBER (2):=10; –même que la déclaration NUMBER(2,0)

43. LESTYPESDEDONNÉES
LESTYPESCARACTÈRE ETCHAINEDECARACTÈRES
Les types de données caractères permettent de stocker
des données alphanumériques, représenter des mots et
du texte, et de manipuler des chaînes de caractères.
CHAR
VARCHAR2
…
44

44. LESTYPESDEDONNÉES
LESTYPESCARACTÈRE ETCHAINEDECARACTÈRES
CHAR
Le type de données CHAR est utilisé pour stocker des
caractères (de longueur fixe),
CHAR[(max_size)] –max_sizecomprise entre 1 et 32767 (par défaut égale à 1).
Exemple
v_charCHAR :='A';
v_stringCHAR(5) :='SALAM';
45

45. LESTYPESDEDONNÉES
LESTYPESCARACTÈRE ETCHAINEDECARACTÈRES
VARCAHR2:
Le type de données VARCHAR2 est utilisé pour stocker les
données de type chaine de caractères
Le type de données VARCHAR2 prend un paramètre
obligatoire qui spécifie une taille maximale qui peut aller de
1 à 32767 octets
VARCHAR2(maximum_size);
NB: on ne peut pas insérer des valeurs VARCHAR2 plus grandes
que 4000 octets dans une colonne de type VARCHAR2 d’une base
de données.
46

46. LESTYPESDEDONNÉES
TYPESPOURLESGRANDSOBJETS(LOB LARGEOBJECT)
Les types de données LOB (large object) permettent de
stocker jusqu'à quatre giga-octets, en taille, de blocs de
données non structurées (comme des textes, images
graphiques, clips vidéo et des signaux sonores).
Et, ils permettent un accès par morceaux efficace, et
non séquentiel, aux données.
BFILE: utilisé pour stocker de grands objets binaires dans
des fichiers systèmes
BLOB: utilisé pour stocker de grands objets binaires dans la
base de données
CLOB: utilisé pour stocker de grands blocs de données de
types caractères dans la base de données
47

47. LESTYPESDEDONNÉES
LESTYPESBOOLÉENS
PL/SQL propose un type pour représenter des valeurs
booléennes (vrai et faux).
Le type de données BOOLEAN peut être utilisé pour
stocker les valeurs logiques VRAI, FAUX, et NULL.
Du fait que SQL ne dispose pas d'un type équivalent, on ne
peut utiliser des variables booléennes que dans des
contextes PL/SQL, mais pas à l'intérieur des requêtes SQL.
48

48. LESTYPESDEDONNÉES
LESTYPESDATE ETTIMESTAMP
DATE:
Utilisé pour stocker des variables datetimesde
longueur fixe, qui inclut le moment de la journée en
secondes depuis minuit.
Par défaut, la valeur du jour est le premier jour du mois en
cours, la valeur de l’heure égale à minuit,
Sous oracle, la fonction date SYSDATE renvoie la date et
l'heure courante.
49

49. LESTYPESDEDONNÉES
LESTYPESDATE ETTIMESTAMP
DATE:
PL/SQL propose des fonctions pour la manipulation des
dates
Nom de la fonction Description
ADD_MONTHS (date, nbre_de_moi)Ajoute un nombre spécifié de
mois à une date.
LAST_DAY ( date ) Retourne le dernier jour du mois
de la date spécifiée.
MONTHS_BETWEEN ( date 2, date1 )Calcule le nombre de mois entre
deux dates.
NEXT_DAY( date, 'jour' ) Date du prochain jouraprès date
oùjourest un jour de la semaine
50

50. LESTYPESDEDONNÉES
LESTYPESDATE ETTIMESTAMP
DATE:
Exemples
DECLARE
v_dateDATE :='01-10-2010';
BEGIN
DBMS_OUTPUT.PUT_LINE(ADD_MONTHS( v_date,3)); --01/01/11
DBMS_OUTPUT.PUT_LINE(LAST_DAY( v_date)); --31/10/10
DBMS_OUTPUT.PUT_LINE(MONTHS_BETWEEN( v_date,
ADD_MONTHS( v_date,-2))); --2
DBMS_OUTPUT.PUT_LINE(NEXT_DAY( v_date, 'Lundi')); --04/10/10
End;
/
51
On peut tester ces fonctions directement en utilisant la table
system dual:
SELECT SYSDATE fromdual;
SELECT NEXT_DAY( '31/12/2013', 'Lundi' ) from dual ;

51. LESTYPESDEDONNÉES
LESSOUS-TYPES
Un sous-type n’introduit pas un nouveau type mais en
restreint (limite) un existant.
PL/SQL propose plusieurs sous-types prédéfinis et il est
possible de définir des sous-types personnalisés
52
Sous-type Type restreint Caractéristiques
CHARACTER CHAR Mêmes caractéristiques.
INTEGER NUMBER(38,0) Entiers.
BINARY_INTEGER NUMBER
….
NATURAL, POSITIVE
BINARY_INTEGER
Non négatif.
NATURALN, POSITIVEN Non négatif et non nul.
SIGNTYPE Domaine de valeurs {–1, 0, 1}.
DEC, DECIMAL, NUMERIC NUMBER Décimaux, précision de 38
chiffres.

52. LESTYPESDEDONNÉES
PERSONNALISER UNTYPE
Il est possible de définir un sous-type (dit «personnalisé»
car n’existe que durant le programme) par la syntaxe
suivante :
SUBTYPE nomSousTypeIStypeBase[(contrainte)] [NOT NULL];
typeBaseest un type prédéfini ou personnalisé.
contraintes’applique au type de base et concerne seulement la
précision ou la taille maximale.
53
DECLARE
SUBTYPE dateNaiss_styIS DATE NOT NULL;
SUBTYPE insee_styIS NUMBER(13);
SUBTYPE nombre_styIS NUMBER(2,2);

53. LESTYPESDEDONNÉES
TYPESDÉRIVÉS
Pourfaciliterlamaintenanceducodequiinteragit
aveclabasededonnées,onpeutégalementutiliserdes
typesquisontdérivésdelabasesansàlesconnaitre.
Ainsi, on peut utiliser les deux attribues:
%TYPE,
%ROWTYPE
54

54. LESTYPESDEDONNÉES
TYPESDÉRIVÉS
%TYPE
L’attribut%TYPEfaitréférenceàuntypeexistantquiest
soitunecolonned'unetablesoituntypedéfini
précédemment.
Ilfournitletypededonnéesd'unevariableouunecolonne
debasededonnées.
Celaestparticulièrementutilelorsqu’ondéclaredes
variablesquivontcontenirdesvaleursdelabasede
données.
55

55. LESTYPESDEDONNÉES
TYPESDÉRIVÉS
%TYPE
Exemple:
DECLARE
v_adresseT_utilisateurs.adresses%TYPE;
Déclarant v_adresseavec %TYPE a deux avantages.
1.On n’est pas obligé de savoir exactement le type de
données du champ adressesde la table T_utilisateurs
2.Si on modifie la définition du champ adressesdans la
table T_utilisateurs(changer la taille ou le type) de la
base de données, le type de la variable v_adresseva
changer au moment de l'exécution.
56

56. LESTYPESDEDONNÉES
TYPESDÉRIVÉS
%ROWTYPE
L'attribut%ROWTYPE fournitun type
d'enregistrementquireprésenteunelignedansune
table.
L’enregistrementpeutstockeruneligneentière
sélectionnéeàpartird’unetableourécupéréeà
partird'uncurseurouvariabledecurseur.
Les éléments de cetenregistrement
correspondantsportentlemêmenomettypede
données.
57

57. LESTYPESDEDONNÉES
TYPESDÉRIVÉS
%ROWTYPE
DECLARE
rty_utilisateursT_utilisateur%ROWTYPE; --on déclare une variable type enregistrement
Ici, nous avons déclaré un enregistrement nommé rty_utilisateurs.
Les champs de cet enregistrement ont les mêmes noms et types de
données que les colonnes dans la table T_utilisateur.
L’enregistrement peut contenir toutes les colonnes d’une table ou
seulement certaines.
Nous détaillerons, dans la suite, le mécanisme des curseurs
(CURSOR) qui emploient beaucoup cette directive.
Pour accéder à un champ on utilise la notation du « point » comme
sur l’exemple suivant:
v_ID:=rty_utilisateurs.ID; --ID est le nom d’un champ dans la table T_utilisateur
58

58. LESTYPESDEDONNÉES
TYPESDÉRIVÉS
%ROWTYPE
Exemple:
DECALRE
rty_utilisateursT_utilisateur%ROWTYPE; --on déclare une variable type enregistrement
v_IDT_utilisateur.ID%TYPE;
BEGIN
SELECT * INTO rty_utilisateursFROM T_utilisateurwhereID=5;
v_ID:=rty_utilisateurs.ID;
rty_utilisateurs.Nom:= Tarik;
rty_utilisateurs.Prénom:= Ilias;
--Insertion dans la table T_utilisateurà partir d’un enregistrement.
INSERT INTO T_utilisateurVALUES rty_utilisateurs;
END;
NB:Lescolonnesrécupéréesparladirective%ROWTYPE
n’héritentpasdescontraintesNOTNULLquiseraient
éventuellementdéclaréesauniveaudelatable.
59

59. LESTYPESDEDONNÉES
TYPESCOMPOSÉS
RECORD
LetypededonnéesRECORD(disponibledepuisla
version7)définitnospropresstructuresdedonnées
(l’équivalentdustructenC).
La syntaxe pour déclarer une variable de type
RECORD est la suivante:
TYPEnomRecordIS RECORD ( nomChamptypeDonnées[[NOT NULL] {:= | DEFAULT}
expression] [,nomChamptypeDonnées… ]… );
60

60. LESTYPESDEDONNÉES
TYPESCOMPOSÉS
RECORD
Exemple:
DECLARE
/*
Déclaration d’un RECORD contenant 3 champs, dont un est non nul avec initialisation du
champ qtyInStock
*/
TYPE R_productIS RECORD (id NUMBER, identifiantCHAR(15) NOT NULL,
qtyInStockNUMBER := 50);
r_produitR_product;
BEGIN
--Affectation d’un record
r_produit.id:=5;
r_produit.identifiant:=’Lait’;
r_produit.qtyInStock:=150;
END;
61

61. LESTYPESDEDONNÉES
TYPESCOMPOSÉS
RECORD:
NB1:LestypesRECORDnepeuventpasêtrestockésdans
unetabledelabasededonnées.
NB2:Ilestpossiblequ’unchampd’unRECORDsoitlui-
mêmeunRECORD(nestedrecord),ousoitdéclaréavecles
directives%TYPEou%ROWTYPE.
TYPE R_productIS RECORD (id NUMBER, identifiantCHAR(15) NOT NULL, qtyInStock
NUMBER := 50);
TYPE R_stockIS RECORD (produit R_product, fournisseur T_ fournisseur%ROWTYPE,
employeT_employe.id%TYPE);
NB: Les RECORD ne peuvent pas être comparés (nullité, égalité et inégalité).
62

62. CONVERSION DETYPESDEDONNÉES EN
PL/SQL
Parfois,ilestnécessairedeconvertirunevaleurd'un
typededonnéesàunautre.
Parexemple,pourutiliserunedatedansunrapport,il
fautlaconvertirenunechaînedecaractères.
PL/SQLsupporteàlafoislaconversionexpliciteet
implicite(automatique)detypesdedonnées.
Ilestpréférabled’utiliserlaconversionexplicite
pours’assurerdubonfonctionnementdenotre
programme.
63

63. CONVERSION DETYPESDEDONNÉES ENPL/SQL
CONVERSION EXPLICITE
On utilise des fonctions fournies pas PL/SQL.
Exemple:
Pour convertir une valeur CHAR en une valeur DATE ou
NUMBER, on utilise respectivement les fonctions TO_DATE
ou TO_NUMBER,
Exemple
64
DECLARE
v_chr1CHAR :=‘1’;
v_nbr1 NUMBER;
BEGIN
v_nbr1 := TO_NUMBER(v_chr1);
dbms_output.put_line( ‘TO_NUMBER ( ‘||v_chr1|| ‘) =‘ || v_nbr1 ) ;
END;

64. CONVERSION DETYPESDEDONNÉES ENPL/SQL
CONVERSION EXPLICITE
Quelques fonctions de conversion
65
BIN_TO_NUM( bit [,bit[...]] ) Conversion d'une
suite de bits en
nombre
BIN_TO_NUM(1,0,1,0)
10
TO_CHAR( date [, 'format' ] )
TO_CHAR( nombre [, 'format' ] )
Transformation
d'un type DATE
ou NUMBER en
chaîne
TO_CHAR( SYSDATE,
'DD/MM/YYYY
HH24:MI:SS' )
18/10/2010 00:27:28
TO_DATE( nombre [, 'format' ] )
TO_DATE( chaîne [, 'format' ] )
Transformation
d'un type
NUMBER ou
CHAR ou
VARCHAR2 en
date
TO_DATE( '18/11/2010',
'DD/MM/RR' )
18/11/10

65. CONVERSION DETYPESDEDONNÉES ENPL/SQL
CONVERSION IMPLICITE
Quand il est logique, PL/SQL peut implicitement
convertir le type de données d'une valeur.
Exemple 1:
66
DECLARE
v_blBOOLEAN;
begin
v_bl:=1; --renvoieune erreur “expression du mauvais type »
end;
DECLARE
v_chr1CHAR :=‘1’;
v_nbr1 NUMBER;
BEGIN
v_nbr1 := v_chr1;
dbms_output.put_line( v_nbr1 ) ;
END;
Exemple 2:

66. QUELQUESOPÉRATEURS:
LESOPERATEURS LOGIQUES
Les operateurs logiques AND, OR et NOT suivent la
logique des trois états indiqués dans le tableau ci-
dessous.
AND et OR sont des opérateurs binaires; NOT est un
opérateur unaire.
67
TRUE FALSE NULL
L’opérateur AND
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL
TRUE FALSE NULL
L’opérateur OR
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL
TRUE FALSE NULL
L’opérateur NOT
FALSE TRUE NULL
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL

67. QUELQUESOPÉRATEURS:
OPÉRATEURS ARITHMÉTIQUES
Operateur Signification
= Egal à
<>, !=, ~=, ^= Non égal à
<
Inférieur à
>
Supérieur à
<=
Inférieur ou égal à
>=
Supérieur ou égal à 68

68. QUELQUESOPÉRATEURS:
OPÉRATEUR DECONCATÉNATION
Les double-barres verticales (||) servent comme
opérateur de concaténation entre chaines de caractères
69

69. EXERCICE:
Ecrire un bloc PL/SQL pour:
Demander de saisir le nom à la console
Afficher le nombre de caractères du nom saisi
NB:
Utiliser un fichier
Pour ouvrir un fichier: ednom_fichier
Pour executerun fichier: @nom_fichierou bien startnom_fichier
Pour afficher un message à la console, utiliser commande SQL*PLUS:
PROMPT « Texte à Afficher »
Pour récupérer ce qui est saisi par l’utilisateur, utiliser la commande
SQL*PLUS:
Acceptnom_var
Pour utiliser la variable nom_vardans un bloc pl/sql: ‘&nom_var’
Utiliser une variable de type NUMBER(38,0) qui va contenir la longueur de
la chaine de caractères
Utiliser la fonction length(chaine) pour calculer la longueur de la chaine
Pour afficher un message à la console utiliser la fonction:
DBMS_OUTPUT.PUT_LINE (texte à afficher)
Pour convertir un chiffre en un caractère, utiliser la fonction TO_CHAR()
L’opérateur de concaténation est: ||
Pour activer l’affichage à la console, utiliser la commande:
SET SERVEROUTPUT ON
Pour désactiver l’affichage des vérifications
SET VERIFY OFF
70

70. Correction
71
--configuration de la console SQL*PLUS
SET SERVEROUTPUT ON
SET VERIFY OFF
PROMPT entrer un nom
ACCEPT var
--Code PL/SQL
DECLARE
lg number;
BEGIN
lg :=length('&var');
dbms_output.put_line( 'le nombre de caractères dans '|| '&var' || ' est ' ||
TO_CHAR(lg)) ;
END;
/