Oracle PL/SQL


précédentsommairesuivant

2. Les variables, types et littéraux

2.1. Les variables

nom variable [CONSTANT] type [ [NOT NULL] := expression ] ;

nom variable représente le nom de la variable composé de lettres, chiffres, $, _ ou #
Le nom de la variable ne peut pas excéder 30 caractères

CONSTANT indique que la valeur ne pourra pas être modifiée dans le code du bloc PL/SQL

NOT NULL indique que la variable ne peut pas être NULL, et dans ce cas expression doit être indiqué.

type représente de type de la variable correspondant à l'un des types suivants :


(dans le premier tableau, les types Oracle sont en gras, les sous-types compatible ANSI/ISO en normal)

Types scalaires        
BINARY_INTEGER DEC DECIMAL DOUBLE PRECISION FLOAT
INT INTEGER NATURAL NATURALN NUMBER
NUMERIC PLS_INTEGER POSITIVE POSITIVEN REAL
SIGNTYPE SMALLINT CHAR CHARACTER LONG
LONG RAW NCHAR NVARCHAR2 RAW ROWID
STRING UROWID VARCHAR VARCHAR2  
BOOLEAN DATE      
INTERVAL DAY TO SECOND (9i) INTERVAL YEAR TO MONTH (9i) TIMESTAMP (9i) TIMESTAMP WITH LOCAL TIME ZONE (9i) TIMESTAMP WITH TIME ZONE (9i)
Types composés    
RECORD TABLE VARRAY
Types références  
REF CURSOR REF type_objet
Types grands objets      
BFILE BLOB CLOB NCLOB
Types suplémentaires        
SYS.ANYDATA SYS.ANYTYPE SYS.ANYDATASET    
XMLTYPE URITYPE      
MDSYS.SDO_GEOMETRY        
ORDSYS.ORDAUDIO ORDSYS.ORDIMAGE ORDSYS.ORDVIDEO ORDSYS.ORDDOC ORDSYS.ORDIMAGESIGNATURE


Vous pouvez également créer des sous-types :

SUBTYPE nom_sous-type IS type ;

SUBTYPE entier_court IS SMALLINT ;
i entier_court ;

Et utiliser les types dérivés

%TYPE
référence à un type existant qui est soit une colonne d'une table soit un type défini précédemment

nom_variable nom_table.nom_colonne%TYPE ;
nom_variable nom_variable_ref%TYPE ;

%ROWTYPE
référence à une ligne d'une table ou d'un curseur

nom_variable nom_table%ROWTYPE ;
nom_variable nom_curseur%ROWTYPE ;

 
Sélectionnez

Declare
  -- variable de même type que le colonne ENAME de la table EMP
  LC$Nom	EMP.ENAME%TYPE ;
  -- variable de même type qu'une ligne de la table EMP
  LR$EMP    EMP%ROWTYPE ;
  LC$Dat1	DATE ;
  -- variable de même type que LC$Dat1 (DATE)
  LC$Dat2   LC$Dat1%TYPE ;
  -- Curseur --
  Cursor C_EMP is
  Select empno, ename, job From EMP ;
  -- variable de type ligne du curseur C_EMP
  LR$C_emp C_EMP%ROWTYPE ;

2.2. Types prédéfinis

2.2.1. Types caractères

CHAR[(n)]
Chaîne de caractères de longueur fixe avec n compris entre 1 et 32767 (par défaut 1)

VARCHAR2(n)
Chaîne de caractères de longueur variable avec n compris entre 1 et 32767

Ces types PL/SQL ont une capacité supérieure à celle des colonnes de tables de même type.
(une colonne CHAR ne peut excéder 2000 caractères et une colonne de type VARCHAR2 4000 caractères)


LONG
Chaîne de caractères de longueur variable avec au maximum 32760 octets

RAW[(n)]
Chaîne de caractères ou données binaires de longueur variable avec n compris entre 1 et 32767. Le contenu d'une variable de ce type n'est pas interprété par PL/SQL (pas de gestion des caractères nationaux)

LONG RAW
Identique au type LONG qui peut contenir des données binaires


Jeux de caractères multi-octets

NCHAR[(n)]
Chaîne de caractères de longueur fixe avec n compris entre 1 et 32767 (par défaut 1)

NVARCHAR2[(n)]
Chaîne de caractères de longueur variable avec n compris entre 1 et 32767

Le nombre de caractères réellement stockés dépend du nombre d'octets utilisés pour coder chaque caractère



UROWID, ROWID
Permet de stocker l'adresse absolue d'une ligne dans une table sous la forme d'une chaîne de caractères

Le format d'une telle variable est le suivant :

000000FFFBBBBBBRRR

000000 représente le numéro de l'objet qui possède cette ligne (dans le cas de cluster, plusieurs objets peuvent partager le même segment)

FFF représente le numéro du fichier qui contient la ligne

BBBBBB représente le numéro du bloc dans le fichier

RRR représente le numéro de ligne dans le bloc

2.2.2. Types numériques

NUMBER[(e,d)]
Nombre réel avec e chiffres significatifs stockés et d décimales

BINARY_INTEGER
Nombre entier compris entre -2 147 483 647 et +2 147 483 647
(Utilise les fonctions de la librairie arithmétique)

(10g)BINARY_FLOAT
Nombre à virgule flottante simple précision au format IEEE 754
un littéral de ce type est écrit avec un f terminateur (ex. 3.125f)

(10g)BINARY_DOUBLE
Nombre à virgule flottante double précision au format IEEE 754
un littéral de ce type est écrit avec un d terminateur (ex. 3.12548d)

PLS_INTEGER
Nombre entier compris entre -2 147 483 647 et +2 147 483 647
(Plus rapide que BINARY_INTEGER car il utilise les registres du processeur)

2.2.3. Types pour les grands objets

BFILE
Stocke la référence vers un fichier du système d'exploitation

BLOB
Permet de stocker un objet binaire jusqu'à 4 Go

CLOB
Permet de stocker un ensemble de caractères, jusqu'à 4 Go

NCLOB
Permet de stocker un ensemble de caractères, codés sur un ou plusieurs octets, jusqu'à 4 Go

2.2.4. Types supplémentaires

SYS.ANYTYPE, SYS.ANYDATA

Une variable de ce type peut contenir un objet de n'importe quel type scalaire ou objet
Définie comme colonne d'une table, elle pourrait contenir une variable de type NUMBER dans une ligne, une variable de type VARCHAR2 dans une autre, une variable de type objet dans une troisième, etc.

Il faut utiliser les méthodes associées pour insérer la valeur correspondant au type désiré sur chaque ligne
SYS.ANYDATA.CONVERT...

SYS.ANYDATA.ConvertNumber(1500) pour insérer une variable numérique
SYS.ANYDATA.ConvertVarchar2('Hello') pour insérer une variable caractère

Liste des fonctions de conversion

  • ConvertNumber(num IN NUMBER) RETURN AnyData
  • ConvertDate(dat IN DATE) RETURN AnyData
  • ConvertChar(c IN CHAR) RETURN AnyData
  • ConvertVarchar(c IN VARCHAR) RETURN AnyData
  • ConvertVarchar2(c IN VARCHAR2) RETURN AnyData
  • ConvertRaw(r IN RAW) RETURN AnyData
  • ConvertBlob(b IN BLOB) RETURN AnyData
  • ConvertClob(c IN CLOB) RETURN AnyData
  • ConvertBfile(b IN BFILE) RETURN AnyData
  • ConvertObject(obj IN "(object_type)") RETURN AnyData
  • ConvertRef(rf IN REF "(object_type)") RETURN AnyData
  • ConvertCollection(col IN "(COLLECTION_1)") RETURN AnyData

Et les méthodes suivantes pour retrouver les valeurs insérées
nom_variable.GET...

  • GetNumber(self IN AnyData, num OUT NOCOPY NUMBER) RETURN PLS_INTEGER
  • GetDate(self IN AnyData, dat OUT NOCOPY DATE) RETURN PLS_INTEGER
  • GetChar(self IN AnyData, c OUT NOCOPY CHAR) RETURN PLS_INTEGER
  • GetVarchar(self IN AnyData, c OUT NOCOPY VARCHAR) RETURN PLS_INTEGER
  • GetVarchar2(self IN AnyData, c OUT NOCOPY VARCHAR2) RETURN PLS_INTEGER
  • GetRaw(self IN AnyData, r OUT NOCOPY RAW) RETURN PLS_INTEGER
  • GetBlob(self IN AnyData, b OUT NOCOPY BLOB) RETURN PLS_INTEGER
  • GetClob(self IN AnyData, c OUT NOCOPY CLOB) RETURN PLS_INTEGER
  • GetBfile(self IN AnyData, b OUT NOCOPY BFILE) RETURN PLS_INTEGER
  • GetObject(self IN AnyData, obj OUT NOCOPY "(object_type)") RETURN PLS_INTEGER
  • GetRef(self IN AnyData, rf OUT NOCOPY REF "(object_type)") RETURN PLS_INTEGER
  • GetCollection(self IN AnyData, col OUT NOCOPY "(collection_type)") RETURN PLS_INTEGER

Le type ANYDATA supporte également les méthodes suivantes:

  • Procédure BEGINCREATE pour la création d'un nouveau type
  • Procédure membre PIECEWISE pour définir le mode d'accès à la valeur courante
  • Procédure membre SET... Pour positionner les valeurs
  • Procédure membre ENDCREATE Pour terminer la création d'un nouveau type
  • Fonction membre GETTYPENAME Pour retrouver la définition complète du type
  • Fonction membre GETTYPE Pour retrouver le type de l'objet


SYS.ANYDATASET

Ce type contient à la fois la description et un ensemble de données de même type.

Liste des fonctions attachées à ce type

  • Procédure membre ADDINSTANCE Pour l'ajout d'une nouvelle instance de données
  • Procédure BEGINCREATE pour la création d'un nouveau type
  • Procédure membre PIECEWISE pour définir le mode d'accès à la valeur courante
  • Procédure membre SET... Pour positionner les valeurs
  • Procédure membre ENDCREATE Pour terminer la création d'un nouveau type
  • Fonction membre GETTYPENAME Pour retrouver la définition complète du type
  • Fonction membre GETTYPE Pour retrouver le type de l'objet
  • Fonction membre GETINSTANCE Pour retrouver l'instance suivante
  • Fonctions membre GET... Pour retrouver les valeurs
  • Fonction membre GETCOUNT Pour retrouver le nombre d'instances du type



Types XML

Ces types sont utilisés pour stocker des objets XML

Le type XMLTYPE posséde des fonctions membres pour insérer, extraire et interroger les données XML via les expressions de type XPATH

Pour manipuler les données XML, Oracle met à disposition les fonctions

  • XMLAGG
  • XMLCOLATTVAL
  • XMLCONCAT
  • XMLDATA
  • XMLELEMENT
  • XMLFOREST
  • XMLSEQUENCE
  • XMLTRANSFORM

Ainsi que les paquetages

  • DBMS_XMLDOM
  • DBMS_XMLGEN
  • DBMS_XMLPARSER
  • DBMS_XMLQUERY
  • DBMS_XMLSAVE
  • DBMS_XMLSCHEMA


Les types URI (URITYPE, DBURITYPE, XDBURITYPE et HTTPURITYPE) permettent de gérer les données sous forme d'URL.

Pour manipuler les données XML, Oracle met à disposition le paquetage URIFACTORY


Type Spatial

MDSYS.SDO_GEOMETRY

Pour la manipulation d'objets Oracle Spatial


Types MEDIA

Ces types sont utilisés pour stocker des objets multi-média avec Oracle interMedia

ORDSYS.ORDAUDIO
Pour le stockage de données audio

ORDSYS.ORDIMAGE
Pour le stockage des images

ORDSYS.ORDIMAGESIGNATURE
Pour le stockage des propriétés des images

ORDSYS.ORDVIDEO
Pour le stockage des données vidéo

ORDSYS.ORDDOC
Pour le stockage de tout type de données multi-média

2.3. Les Types et Sous-types définis par l'utilisateur

En plus des types prédéfinis, l'utilisateur peut définir ses propres types avec le mot clé TYPE ou SUBTYPE

  • SUBTYPE nom_sous-type IS type_base[(précision)] [NOT NULL]

nom_sous-type représente le nom du sous-type déclaré
type_base représente le nom du type prédéfini
précision représente une longueur pour les caractères et longueur + décimales pour les numériques

SUBTYPE chaine_courte IS VARCHAR2(10);
le sous-type utilisateur chaine_courte définit un VARCHAR2(10)
à la suite de cette définition, toute déclaration de variable de type chaine_courte sera égale à VARCHAR2(10)

SUBTYPE chaine IS VARCHAR2(100);
le sous-type utilisateur chaine définit un VARCHAR2(100)

SUBTYPE chaine_longue IS VARCHAR2(1000);
le sous-type utilisateur chaine_longue définit un VARCHAR2(1000)

SUBTYPE NOM_EMP IS EMP.ename%Type;
le sous-type NOM_EMP définit un type identique à la colonne ename de la table EMP

SUBTYPE REC_EMP IS EMP%ROWTYPE;
le sous-type REC_EMP définit un type identique à une ligne de la table EMP



TYPE tableau_numerique IS TABLE OF NUMBER;
le type tableau_numerique définit un tableau de NUMBER

TYPE TAB_REC_EMP IS TABLE OF REC_EMP;
le type TAB_REC_EMP définit un tableau d'éléments de type REC_EMP
Lorsque vos nouveaux types et sous-types sont déclarés, vous pouvez les utiliser pour typer de nouvelles variables

 
Sélectionnez

SQL> Declare
  2    -- définition des types et sous-types
  3    SUBTYPE chaine_courte IS VARCHAR2(10);
  4    SUBTYPE chaine IS VARCHAR2(100);
  5    SUBTYPE chaine_longue IS VARCHAR2(1000);
  6    TYPE tableau_numerique IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  7    SUBTYPE NOM_EMP IS EMP.ename%Type;
  8    SUBTYPE REC_EMP IS EMP%ROWTYPE;
  9    TYPE TAB_REC_EMP IS TABLE OF REC_EMP;
 10    -- définition des variables
 11    LC$cc chaine_courte;
 12    LC$cl chaine_longue;
 13    Tab   tableau_numerique;
 14  Begin
 15    LC$cc := 'Court' ;
 16    LC$cl := 'Chaîne beaucoup plus longue' ;
 17    dbms_output.put_line( 'Chaîne longue = ' || LC$cl ) ;
 18    For i in 1..5 Loop
 19       Tab(i) := i + (.1 * i ) ;
 20    dbms_output.put_line( 'Tab(' || Ltrim( To_char( i ) ) || ') = ' || To_char( Tab(i) ) ) ;
 21    End loop ;
 22  End ;
 23  /
Chaîne longue = Chaîne beaucoup plus longue
Tab(1) = 1,1
Tab(2) = 2,2
Tab(3) = 3,3
Tab(4) = 4,4
Tab(5) = 5,5

Procédure PL/SQL terminée avec succès.

2.4. Les littéraux

Un littéral ou valeur constante désigne une valeur fixe.
Par exemple 'LUNDI', 'Montpellier', '2012' représentent des valeurs littérales de type caractère.
12.3, 25 représentent des valeurs littérales de type numérique

Ces valeurs peuvent apparaître dans des initialisations de variables, des calculs ou transmises à des procédures ou fonctions.

2.4.1. Littéral de type caractère

Désigne une valeur fixe comme étant de type caractère
La valeur peut contenir n'importe quel caractère à l'exception d'une simple apostrophe
(pour saisir une apostrophe dans un littéral, il faut la doubler (''))
Il doit être encadré d'une paire d'apostrophes
Il peut être précédé du caractère N pour indiquer qu'il doit être transformé dans le jeu de caractères national
Il a les mêmes propriétés que les types CHAR et VARCHAR2
Sa longueur ne peut pas dépasser 4000 octets

'J''aime le PL/SQL'
'Cordialement'
'Select * From EMP'

2.4.2. Littéral de type entier

Désigne une valeur fixe comme étant de type entier
Ne peut contenir que les chiffres de 0 à 9
Il peut être précédé des signes + ou -
Il peut contenir jusqu'à 38 chiffres de précision

-12
267589
+3

2.4.3. Littéral de type décimal

Désigne une valeur fixe comme étant de type numérique
Ne peut contenir que les chiffres de 0 à 9
Il peut être précédé des signes + ou -
Il peut contenir jusqu'à 38 chiffres de précision
Il peut contenir le caractère e ou E qui indique que la valeur est spécifiée en notation scientifique. Les chiffres après le E indiquent l'exposant. Ce dernier est valide dans un intervalle de -130 à 125

-54
+3.1415
0.006
75E-12

2.4.4. Littéral de type intervalle (9i)

Nouveauté 9i Spécifie une période de temps, déclinée en années et mois ou en jours, heures, minutes et secondes.
Les deux types de littéraux de type intervalle sont YEAR TO MONTH et DAY TO SECOND
Chaque type contient un préfixe et peut contenir un suffixe. Le préfixe désigne l'unité de base de date ou d'heure. Le suffixe définit les parties d'incrément associées à l'unité de base.


Si vos données sont sous forme numérique, vous pouvez utiliser les fonctions de conversion NUMTOYMINTERVAL ou NUMTODSINTERVAL pour les convertir en littéraux de type intervalle.

  • Intervalle de type YEAR TO MONTH


INTERVAL 'nombre_entier [-nombre_entier]' YEAR ou MONTH (précision) TO YEAR ou MONTH

Nombre_entier [-nombre_entier] spécifie une valeur entière pour le préfixe et éventuellement le suffixe du littéral. Si le préfixe est YEAR et le suffixe est MONTH, nombre_entier pour le mois doit être entre 0 et 11

Précision représente le nombre maximum de chiffres pour le préfixe compris entre 0 et 9. Par défaut sa valeur est 2

INTERVAL '12-3' YEAR TO MONTH : intervalle de 12 ans et 3 mois

INTERVAL '115' YEAR(3) : intervalle de 115 ans (la précision du suffixe doit être spécifiée YEAR(3) si elle est supérieure à la valeur par défaut)

INTERVAL '24' MONTH : intervalle de 24 mois

Il est possible d'additionner ou soustraire un littéral de type intervalle à un autre

INTERVAL '6-4' YEAR TO MONTH - INTERVAL '6' MONTH

  • Intervalle de type DAY TO SECOND


INTERVAL 'nombre_entier' DAY ou HOUR ou MINUTE ou SECOND (précision) TO DAY ou HOUR ou MINUTE ou SECOND (fractions de secondes)

Nombre_entier peut représenter soit :
Un nombre de jours
Une heure au format HH[ :MI[ :SS[.fractions_de_secondes]]]


Précision représente le nombre de chiffres du préfixe, compris entre 0 et 9. Par défaut sa valeur est 2

Fractions_de_secondes représente le nombre de chiffres des fractions de secondes, compris entre 1 et 9. Par défaut sa valeur est 6

Les valeurs correctes pour les champs sont :
HOUR 0 à 23
MINUTE 0 à 59
SECOND 0 à 59.999999999


INTERVAL '6 4 :10 :22.356' DAY TO SECOND(3) : intervalle de 6 jours, 4 heures, 10 minutes, 22 secondes et 356 millièmes de secondes

INTERVAL '6 4 :10' DAY TO MINUTE : intervalle de 6 jours, 4 heures et 10 minutes

INTERVAL '365 12' DAY(3) TO HOUR : intervalle de 365 jours et 12 heures

INTERVAL '8 :10 :20.3333333' HOUR TO SECOND(7) : Intervalle de 8 heures, 10 minutes, 20.3333333 secondes

INTERVAL '18 :30' HOUR TO MINUTE : intervalle de 18 heures et 30 minutes

INTERVAL '20' MINUTE : intervalle de 20 minutes

INTERVAL '4.12345' SECOND(2,4) : intervalle arrondi à 4.1235 secondes car la précision demandée sur les fractions de secondes est de 4 chiffres

Il est possible d'additionner ou soustraire un littéral de type intervalle à un autre

INTERVAL '30' DAY - INTERVAL '18' HOUR


précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2004 SheikYerbouti. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.