– PLSQL : Records et Collections | Cours Oracle , cours MySQL et supports de cours Oracle gratuits en ligne

Ces types de données n’existent qu’en PL/SQL et n’ont pas d’équivalent dans la base Oracle. Il n’est pas possible de stocker un enregistrement directement dans la base

Les collections

Une collection est un ensemble ordonné d’éléments de même type.
Elle est indexée par une valeur de type numérique ou alphanumérique
Elle ne peut avoir qu’une seule dimension ( mais en créant des collections de collections on peut obtenir des tableaux à plusieurs dimensions)

On peut distinguer trois types différents de collections :

Les tables (INDEX-BY TABLES) qui peuvent être indicées par des variables numériques ou alpha-numériques

Les tables imbriquées(NESTED TABLES) qui sont indicées par des variables numériques et peuvent être lues et écrites directement depuis les colonnes d’une table

Les tableaux de type VARRAY, indicés par des variables numériques, dont le nombre d’éléments maximum est fixé dès leur déclaration et peuvent être lus et écrits directement depuis les colonnes d’une table

Les collections de type NESTED TABLE et VARRAY doivent-être initialisées après leur déclaration, à l’aide de leur constructeur qui porte le même nom que la collection
(elles sont assignées à NULL lors de leur déclaration. Il est donc possible de tester leur nullité)

Les enregistrements

Un enregistrement ressemble à une structure d’un L3G
Il est composé de champs qui peuvent être de type différent

Déclarations et initialisation

Les collections de type NESTED TABLE et INDEX-BY TABLES

Elles sont de taille dynamique et il n’existe pas forcément de valeur pour toutes les positions

Déclaration d’une collection de type nested table
TYPE nom type IS TABLE OF type élément [NOT NULL] ;

Déclaration d’une collection de type index by
TYPE nom type IS TABLE OF type élément [NOT NULL] INDEX BY index_by_type ;

index_by_type représente l’un des types suivants :

BINARY_INTEGER
PLS_INTEGER(9i)
VARCHAR2(taille)
LONG

SQL> declare 2 -- collection de type nested table 3 TYPE TYP_NES_TAB is table of varchar2(100) ; 4 -- collection de type index by   5 TYPE TYP_IND_TAB is table of number index by binary_integer ; 6 tab1 TYP_NES_TAB ; 7 tab2 TYP_IND_TAB ; 8 Begin 9 tab1 := TYP_NES_TAB('Lundi','Mardi','Mercredi','Jeudi' ) ; 10 for i in 1..10 loop 11 tab2(i):= i ; 12 end loop ; 13 End; 14 / Procédure PL/SQL terminée avec succès.

Les collections de type VARRAY

Ce type de collection possède une dimension maximale qui doit être précisée lors de sa déclaration
Elle possède une longueur fixe et donc la suppression d’éléments ne permet pas de gagner de place en mémoire
Ses éléments sont numérotés à partir de la valeur 1

Déclaration d’une collection de type VARRAY
TYPE nom type IS VARRAY (taille maximum) OF type élément [NOT NULL] ;

 SQL> declare 2 -- collection de type VARRAY 3 TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ; 4 tab1 TYP_VAR_TAB := TYP_VAR_TAB('','','','','','','','','',''); 5 Begin 6 for i in 1..10 loop 7 tab1(i):= to_char(i) ; 8 end loop ; 9 End; 10 / Procédure PL/SQL terminée avec succès.

Déclaration d’un tableau VARRAY de 30 éléments de type varchar2(100)

Les enregistrements

TYPE nom type IS RECORD ( nom_champ type_élément [[ NOT NULL] := expression ] [, ….] ) ;
Nom_variable nom_type ;

Comme pour la déclaration des variables, il est possible d’initialiser les champs lors de leur déclaration

Sélectionnez


SQL> declare
  2    -- Record --
  3    TYPE T_REC_EMP IS RECORD (
  4       Num emp.empno%TYPE,
  5       Nom emp.ename%TYPE,
  6       Job emp.job%TYPE );    
  7    
  8    R_EMP T_REC_EMP ; -- variable enregistrement de type T_REC_EMP
  9  Begin
 10    R_EMP.Num := 1 ;
 11    R_EMP.Nom := 'Scott' ;
 12    R_EMP.job := 'GASMAN' ; 
 13  End; 
 14  /

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

Bien sûr il est possible de gérer des tableaux d’enregistrements

Sélectionnez


SQL> declare
  2    -- Record --
  3    TYPE T_REC_EMP IS RECORD (
  4       Num emp.empno%TYPE,
  5       Nom emp.ename%TYPE,
  6       Job emp.job%TYPE );    
  7    -- Table de records --
  8    TYPE TAB_T_REC_EMP IS TABLE OF T_REC_EMP index by binary_integer ;
  9    t_rec TAB_T_REC_EMP ; -- variable tableau d'enregistrements
 10  Begin
 11    t_rec(1).Num := 1 ;
 12    t_rec(1).Nom := 'Scott' ;
 13    t_rec(1).job := 'GASMAN' ;
 14    t_rec(2).Num := 2 ;
 15    t_rec(2).Nom := 'Smith' ;
 16    t_rec(2).job := 'CLERK' ;    
 17  End; 
 18  /

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

Les éléments d’un enregistrement peuvent être des objets, des collections ou d’autres enregistrements.


Declare
  TYPE Temps IS RECORD
  (
    heures   SMALLINT,
    minutes  SMALLINT,
    secondes SMALLINT
  );
  TYPE Vol IS RECORD
  (
    numvol     PLS_INTEGER,
    Numavion   VARCHAR2(15),
    Commandant Employe,     -- type objet
    Passagers  ListClients, -- type nested table
    depart     Temps,       -- type record
    arrivee    Temps        -- type record
  );  		
Begin
  ...
End  ;

A la différence des types VARRAY et (NESTED)TABLES, les types RECORD ne peuvent pas être créés et stockés dans la base.

Initialisation des collections

Les collections de type NESTED TABLE et VARRAY doivent être initialisées avant toute utilisation (à l’exception des collections de type INDEX-BY TABLE).

Pour initialiser une collection, il faut se référer à son constructeur. Celui-ci, créé automatiquement par Oracle porte le même nom que la collection.

Sélectionnez


Declare
-- Déclaration d'un type tableau VARRAY de 30 éléments de type Varchar2(100)
TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
-- Déclaration et initialisation d'une variable de type TYP_VAR_TAB
tab1 TYP_VAR_TAB := TYP_VAR_TAB('','','','','','','','','','');

Il n’est pas obligatoire d’initialiser tous les éléments d’une collection. On peut même n’en initialiser aucun. Dans ce cas l’appel de la méthode constructeur se fait sans argument .

tab1 TYP_VAR_TAB := TYP_VAR_TAB();

Cette collection n’a aucun élément initialisé. On dit qu’elle est vide.

Une collection non initialisée n’est pas vide mais NULL.

Sélectionnez


Declare
TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
tab1 TYP_VAR_TAB; -- collection NULL

L’initialisation d’une collection peut se faire dans la section instructions, mais dans tous les cas, elle ne pourra pas être utilisée avant d’avoir été initialisée.


Declare
TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
tab1 TYP_VAR_TAB ; -- collection automatiquement assignée à NULL
Begin
  -- La collection est assignée à NULL mais n'est pas manipulable --
   If Tab1 is null Then -- Test OK
	… 
   End if ;
  Tab1 := TYP_VAR_TAB('','','','','','','','','','');
  -- La collection est manipulable --
End ;

Accès aux éléments d’une collection▲

La syntaxe d’accès à un élément d’une collection est la suivante :

Nom_collection( indice )

L’indice doit être un nombre valide compris entre -2**31 et 2**31
Pour une collection de type NESTED TABLE, l’indice doit être un nombre valide compris entre 1 et 2**31
Pour une collection de type VARRAY, l’indice doit être un nombre valide compris entre 1 et la taille maximum du tableau
Dans le cas d’une collection de type INDEX-BY Varchar2 ou Long, l’indice représente toute valeur possible du type concerné.

Indice peut être un littéral, une variable ou une expression

Sélectionnez


  1   Declare
  2     Type TYPE_TAB_EMP IS TABLE OF Varchar2(60) INDEX BY BINARY_INTEGER ;
  3     emp_tab TYPE_TAB_EMP ;
  4     i       pls_integer ;
  5   Begin
  6     For i in 0..10 Loop
  7       emp_tab( i+1 ) := 'Emp ' || ltrim( to_char( i ) ) ;
  8     End loop ;
  9* End ;
SQL> /

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


  1  Declare
  2    Type TYPE_TAB_JOURS IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20) ;
  3    jour_tab TYPE_TAB_JOURS ;
  4  Begin
  5      jour_tab( 'LUNDI' ) := 10 ;
  6      jour_tab( 'MARDI' ) := 20 ;
  7      jour_tab( 'MERCREDI' ) := 30 ;
  8* End ;
SQL> /

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

Il est possible d’assigner une collection à une autre à condition qu’elles soient de même type


Declare
  Type TYPE_TAB_EMP  IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;
  Type TYPE_TAB_EMP2 IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;  
  tab1 TYPE_TAB_EMP  := TYPE_TAB_EMP( ... );
  tab2 TYPE_TAB_EMP  := TYPE_TAB_EMP( ... );
  tab3 TYPE_TAB_EMP2 := TYPE_TAB_EMP2( ... );  
Begin
    tab2 := tab1 ; -- OK
    tab3 := tab1 ; -- Illégal : types différents
    ...		
End ;

Les collections ne peuvent pas être comparées entre elles.
Les opérateurs d’égalité ou de comparaison ne peuvent pas être utilisés entre 2 collections


SQL> Declare
  2    Type TYPE_TAB_STRING  IS TABLE OF Varchar2(10) ;
  3    tab1 TYPE_TAB_STRING  := TYPE_TAB_STRING( '1','2','3' );
  4    tab2 tab1%TYPE        := TYPE_TAB_STRING( '1','2','3' );
  5  Begin  
  6     If tab1 = tab2 Then
  7        null ;
  8     End if ;
  9  End ; 
 10  /
   If tab1 = tab2 Then
           *
ERREUR à la ligne 6 :
ORA-06550: Ligne 6, colonne 12 :
PLS-00306: numéro ou types d'arguments erronés dans appel à '='
ORA-06550: Ligne 6, colonne 4 :
PL/SQL: Statement ignored

(10g)
Les collections de même type peuvent être comparées en égalité ou différence


DECLARE
   TYPE Colors IS TABLE OF VARCHAR2(64);
   primaries Colors := Colors('Blue','Green','Red');
   rgb Colors := Colors('Red','Green','Blue');
   traffic_light Colors := Colors('Red','Green','Amber');
BEGIN
-- On peut utiliser = ou !=, mais pas < ou >.
-- Notez que ces 2 collections sont égales même si leurs membres sont dans un ordre différent.
   IF primaries = rgb THEN
      dbms_output.put_line('OK, PRIMARIES et RGB ont les mêmes membres.');
   END IF;
   IF rgb != traffic_light THEN
      dbms_output.put_line('RGB et TRAFFIC_LIGHT ont des membres différents.');
   END IF;
END;

(10g)
Il est possible d’appliquer certains opérateurs sur des tables imbriquées


DECLARE
  TYPE nested_typ IS TABLE OF NUMBER;
  nt1 nested_typ := nested_typ(1,2,3);
  nt2 nested_typ := nested_typ(3,2,1);
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4);
  reponse BOOLEAN;
  combien NUMBER;
  PROCEDURE verif(test BOOLEAN DEFAULT NULL, quantite NUMBER DEFAULT NULL) IS
  BEGIN
     IF truth IS NOT NULL THEN
        dbms_output.put_line(CASE test WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
     END IF;
     IF quantity IS NOT NULL THEN
        dbms_output.put_line(quantite);
     END IF;
  END;
BEGIN
  reponse := nt1 IN (nt2,nt3,nt4); -- true, nt1 correspond à nt2
  verif(test => reponse);
  reponse := nt1 SUBMULTISET OF nt3; -- true, tous les éléments correspondent
  verif(test => reponse);
  reponse := nt1 NOT SUBMULTISET OF nt4; -- true
  verif(test => reponse);

  combien := CARDINALITY(nt3); -- nombre d'éléments dans nt3
  verif(quantite => combien);
  combien := CARDINALITY(SET(nt3)); -- nombre d'éléments distincts
  verif(quantite => combien);

  reponse := 4 MEMBER OF nt1; -- false, aucun élément ne correspond
  verif(test => reponse);
  reponse := nt3 IS A SET; -- false, nt3 a des éléments dupliqués
  verif(test => reponse);
  reponse := nt3 IS NOT A SET; -- true, nt3 a des éléments dupliqués
  verif(test => reponse);
  reponse := nt1 IS EMPTY; -- false, nt1 a des éléments
  verif(test => reponse);
END;

Méthodes associées aux collections▲

Les méthodes sont des fonctions ou des procédures qui s’appliquent uniquement aux collections.
L’appel de ces méthodes s’effectue en préfixant le nom de la méthode par le nom de la collection

Nom_collection.nom_méthode[(paramètre, …)]

Les méthodes ne peuvent pas être utilisées à l’intérieur de commandes SQL

Seule la méthode EXISTS peut être utilisée sur une collection NULL.
L’utilisation de toute autre méthode sur une collection NULL provoque l’exception COLLECTION_IS_NULL

EXISTS(indice)

Cette méthode retourne la valeur TRUE si l’élément indice de la collection existe et retourne la valeur FALSE dans le cas contraire

Cette méthode doit être utilisée afin de s’assurer que l’on va réaliser une opération conforme sur la collection

Le test d’existence d’un élément qui n’appartient pas à la collection ne provoque pas l’exception SUBSCRIPT_OUTSIDE_LIMIT mais retourne simplement FALSE

Sélectionnez


If ma_collection.EXISTS(10) Then
   Ma_collection.DELETE(10) ;
End if ;

COUNT

Cette méthode retourne le nombre d’éléments de la collection y compris les éléments NULL consécutifs à des suppressions
Elle est particulièrement utile pour effectuer des traitements sur l’ensemble des éléments d’une collection.


Declare
  LN$Nbre pls_integer ;
Begin
  LN$Nbre := ma_collection.COUNT ;
End ;

LIMIT

Cette méthode retourne le nombre maximum d’éléments permis d’une collection
Elle n’est utile que pour les collections de type VARRAY et retourne NULL pour les collections des autre types


Declare
    -- collection de type VARRAY
    TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
    tab1 TYP_VAR_TAB := TYP_VAR_TAB('','','','','','','','','','');
Begin
    for i in 1..tab1.LIMIT loop
       ……….
    end loop ;
End;

FIRST

Cette méthode retourne le plus petit indice d’une collection.
Elle retourne NULL si la collection est vide
Pour une collection de type VARRAY cette méthode retourne toujours 1

LAST
Cette méthode retourne le plus grand indice d’une collection.
Elle retourne NULL si la collection est vide
Pour une collection de type VARRAY cette méthode retourne la même valeur que la méthode COUNT

PRIOR(indice)
Cette méthode retourne l’indice de l’élément précédent l’indice donné en argument
Elle retourne NULL si indice est le premier élément de la collection


LN$i := ma_collection.LAST ;
While LN$i is not null Loop
   …
   LN$I := ma_collection.PRIOR(LN$I) ;
End loop ;

NEXT(indice)

Cette méthode retourne l’indice de l’élément suivant l’indice donné en argument
Elle retourne NULL si indice est le dernier élément de la collection


LN$i := ma_collection.FIRST ;
While LN$i is not null Loop
   …
   LN$I := ma_collection.NEXT(LN$I) ;
End loop ;

EXTEND[(n[,i])]

Cette méthode permet d’étendre une collection par ajout de nouveaux éléments
Elle dispose de 3 syntaxes différentes

EXTEND

Un seul élément NULL est ajouté à la collection


SQL> declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND ;
  7    tab(4) := 'jeudi' ;
  8  End;
  9  /

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

EXTEND(n)

n éléments NULL sont ajoutés à la collection


SQL> declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4) ;
  7    tab(4) := 'jeudi' ;
  8    tab(5) := 'vendredi' ;
  9    tab(6) := 'samedi' ;
 10    tab(7) := 'dimanche' ;      
 11  End; 
 12  /

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

EXTEND(n,i)

n éléments sont ajoutés à la collection. Chaque élément ajouté contient une copie de la valeur contenue dans l’élément d’indice i


SQL> set serveroutput on
SQL> declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10  End;
 11  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi

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

TRIM[(n)]
Cette méthode permet de supprimer un ou plusieurs éléments situés à la fin d’une collection
Elle dispose de 2 formes de syntaxe différentes

TRIM
Le dernier élément de la collection est supprimé

SQL> declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.TRIM ;
 11    For i in tab.first..tab.last Loop
 12      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 13    End loop ;  
 14  End; 
 15  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi

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

TRIM(n)
Les n derniers éléments de la collection sont supprimés


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.TRIM(4) ;
 11    dbms_output.put_line( 'Suppression des 4 derniers éléments' ) ;
 12    For i in tab.first..tab.last Loop
 13      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 14    End loop ;  
 15  End; 
 16  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
Suppression des 4 derniers éléments
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi

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

Si le nombre d’éléments que l’on veut supprimer est supérieur au nombre total d’éléments de la collection,
une exception SUBSCRIPT_BEYOND_COUNT est générée

Sélectionnez


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.TRIM(8) ;
 11    dbms_output.put_line( 'Suppression des 8 derniers éléments' ) ;
 12    For i in tab.first..tab.last Loop
 13      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 14    End loop ;  
 15  End; 
 16  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
declare
*
ERREUR à la ligne 1 :
ORA-06533: Valeur de l'indice trop grande
ORA-06512: à ligne 10

DELETE[(n[,m])]
Cette méthode permet de supprimer un, plusieurs, ou la totalité des éléments d’une collection
Elle dispose de 3 formes de syntaxe différentes

DELETE

Suppression de tous les éléments d’une collection


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.DELETE ;
 11    dbms_output.put_line( 'Suppression de tous les éléments' ) ;
 12    dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;  
 13  End;
 14  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
Suppression de tous les éléments
tab.COUNT = 0

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

DELETE(n)

Suppression de l’élément d’indice n de la collection


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.DELETE(5) ;
 11    dbms_output.put_line( 'Suppression de l''élément d''indice 5' ) ;
 12    dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;
 13    For i in tab.first..tab.last Loop
 14      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 15    End loop ;    
 16  End;
 17  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
Suppression de l'élément d'indice 5
tab.COUNT = 6
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
declare
*
ERREUR à la ligne 1 :
ORA-01403: Aucune donnée trouvée
ORA-06512: à ligne 14

On peut observer que l’élément d’indice 5 de la collection, une fois supprimé, ne peut plus être affiché.
Il convient, lorsque l’on supprime un ou plusieurs éléments d’une collection des tester l’existence d’une valeur avant de la manipuler


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.DELETE(5) ;
 11    dbms_output.put_line( 'Suppression de l''élément d''indice 5' ) ;
 12    dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;
 13    For i in tab.first..tab.last Loop
 14      If tab.EXISTS(i) Then
 15         dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 16      Else
 17         dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') inexistant ') ; 
 18      End if ;
 19    End loop ;    
 20  End;  
 21  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
Suppression de l'élément d'indice 5
tab.COUNT = 6
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) inexistant
tab(6) = lundi
tab(7) = lundi

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

Il est important de noter le décalage entre la valeur retournée par la méthode COUNT et celle retournée par la méthode LAST
Dans l’exemple précédent LAST retourne la plus grande valeur d’indice de la collection soit 7, alors que COUNT retourne le nombre d’éléments de la collection soit 6

Méfiez-vous de l’erreur facile consistant à penser que COUNT = LAST

DELETE(n,m)

Suppression des l’éléments dont les indices sont compris entre n et m (inclus) Si m est plus grand que n, aucun élément n’est supprimé


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.DELETE(4,6) ;
 11    dbms_output.put_line( 'Suppression des élément d''indice 4, 5 et 6' ) ;
 12    dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;
 13    For i in tab.first..tab.last Loop
 14      If tab.EXISTS(i) Then
 15         dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 16      Else
 17         dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') inexistant ') ; 
 18      End if ;
 19    End loop ;    
 20  End;
 21  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
Suppression des élément d'indice 4, 5 et 6
tab.COUNT = 4
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) inexistant
tab(5) inexistant
tab(6) inexistant
tab(7) = lundi

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

Pour les collections de type VARRAY on ne peut supprimer que le dernier élément
Si l’élément à supprimer n’existe pas, aucune exception n’est générée
L’espace mémoire assigné aux éléments supprimés est conservé. Il est tout à fait permis de réassigner une nouvelle valeur à ces éléments.


SQL> Declare
  2    TYPE TYP_TAB is table of varchar2(100) ;
  3    tab  TYP_TAB ;
  4  Begin
  5    tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  6    tab.EXTEND(4,1) ;
  7    For i in tab.first..tab.last Loop
  8      dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
  9    End loop ; 
 10    tab.DELETE(4,6) ;
 11    dbms_output.put_line( 'Suppression des élément d''indice 4, 5 et 6' ) ;
 12    dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;
 13    dbms_output.put_line( 'Réassignation des élément d''indice 4, 5 et 6' ) ;
 14    tab(4) := 'Jeudi' ;
 15    tab(5) := 'Vendredi' ;
 16    tab(6) := 'Samedi' ;    
 17    dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;  
 18    For i in tab.first..tab.last Loop
 19      If tab.EXISTS(i) Then
 20         dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
 21      Else
 22         dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') inexistant ') ; 
 23      End if ;
 24    End loop ;    
 25  End;    
 26  /
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = lundi
tab(5) = lundi
tab(6) = lundi
tab(7) = lundi
Suppression des élément d'indice 4, 5 et 6
tab.COUNT = 4
Réassignation des élément d'indice 4, 5 et 6
tab.COUNT = 7
tab(1) = lundi
tab(2) = mardi
tab(3) = mercredi
tab(4) = Jeudi
tab(5) = Vendredi
tab(6) = Samedi
tab(7) = lundi

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

Principales exceptions


Declare
  TYPE TYP_TAB is table of varchar2(100) ;
  tab  TYP_TAB ;
  lc$valeur varchar2(100) ;
Begin
  tab(1) := 'Lundi' ; -- ORA-06531: Référence à un ensemble non initialisé
  tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
  tab.EXTEND(4,1) ;
  tab.DELETE(4,6) ;
  lc$valeur := tab(4) ; -- ORA-01403: Aucune donnée trouvée
  tab(0) :='lunmanche' ; -- ORA-06532: Indice hors limites
  tab(22) :='marcredi' ; -- ORA-06533: Valeur de l'indice trop grande
  lc$valeur := tab(999999999999999999) ; -- ORA-01426: dépassement numérique
  lc$valeur := tab(NULL) ; -- ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur:
      la valeur de clé de la table d'index est NULL.
End ;

Utilisation des collections avec les données issues de la base

Prenons l’exemple d’une table des entêtes de factures qui stocke également les lignes des factures

Sous Sql*Plus définissons le type ligne de facture (TYP_LIG_FAC)


SQL>  CREATE TYPE TYP_LIG_FAC AS OBJECT (
  2   numlig Integer,
  3   code   Varchar2(20),
  4   Pht    Number(6,2),
  5   Tva    Number(3,2),
  6   Qte    Integer
  7   );
  8  /

Type créé.

Définissons le type TYP_TAB_LIG_FAC comme étant une collection d’éléments du type TYP_LIG_FAC

Sélectionnez

SQL>  CREATE TYPE TYP_TAB_LIG_FAC AS TABLE OF TYP_LIG_FAC ;
  2  /
Type créé.

Création de la table des factures

SQL> CREATE TABLE FACTURE (
  2   numero   Number(9),
  3   numcli   Number(6),
  4   date_fac Date,
  5   ligne    TYP_TAB_LIG_FAC )
  6   NESTED TABLE ligne STORE AS ligne_table ;

Table créée.

Chaque élément présent dans la colonne ligne est une collection de type NESTED TABLE qui va permettre de stocker les différentes lignes de la facture

Insertion de données dans la table FACTURE


SQL> Begin
  2     Insert into FACTURE
  3       values( 1, 1214, sysdate,
  4       Typ_tab_lig_fac( Typ_lig_fac( 1, 'Oracle 9i', 999.99, 5.5, 3 ),
  5                        Typ_lig_fac( 1, 'Forms 9i', 899.99, 5.5, 3 ),
  6                        Typ_lig_fac( 1, 'Reports 9i', 699.99, 5.5, 3 )
  7          )
  8     );
  9         
 10     Insert into FACTURE
 11       values( 2, 1215, sysdate,
 12       Typ_tab_lig_fac( Typ_lig_fac( 1, 'Oracle 9i', 999.99, 5.5, 1 ),
 13                        Typ_lig_fac( 1, 'Forms 9i', 899.99, 5.5, 1 ),
 14                        Typ_lig_fac( 1, 'Reports 9i', 699.99, 5.5, 1 )
 15          )
 16     );
 17  End ;
 18  /

Procédure PL/SQL terminée avec succès.
SQL> commit;
Validation effectuée.

Modification d’une facture

SQL> Declare
  2    Tab_lig Typ_tab_lig_fac := Typ_tab_lig_fac( 
  3             Typ_lig_fac( 1, 'Forms 9i', 899.99, 5.5, 2 ),
  4             Typ_lig_fac( 1, 'Reports 9i', 699.99, 5.5, 2 )
  5             );
  6  Begin
  7    Update FACTURE
  8    Set ligne = Tab_lig Where numero = 2 ;
  9  End;
 10  /

Procédure PL/SQL terminée avec succès.
SQL> commit;
Validation effectuée.

Utilisation d’un type enregistrement (RECORD) pour sélectionner une ligne de la table FACTURE ainsi que toutes les lignes rattachées via la NESTED TABLE


SQL> Declare
  2    TYPE Fact_rec IS RECORD
  3    (
  4      numero   facture.NUMERO%type, 
  5      numcli   facture.NUMCLI%type,
  6      date_fac facture.DATE_FAC%type,
  7      lignes   facture.LIGNE%type
  8    ) ;
  9    rec_fact Fact_rec ;
 10    Cursor C_Fact is select * from facture ;
 11  Begin
 12    Open C_Fact ;
 13    Loop
 14      Fetch C_Fact into rec_fact ;
 15      Exit when C_Fact%NOTFOUND ;
 16      For i IN 1..rec_fact.lignes.last Loop
 17        dbms_output.put_line( 'Numcli/Numfac ' || rec_fact.numcli || '/' || rec_fact.numero 
 18          || ' Ligne ' || rec_fact.lignes(i).numlig 
 19          || ' code ' || rec_fact.lignes(i).code || ' Qté ' 
 20          || To_char(rec_fact.lignes(i).qte) ) ;
 21      End loop ;
 22    End loop ;
 23  End ;  
 24  /
Numcli/Numfac 1214/1 Ligne 1 code Oracle 9i Qté 3
Numcli/Numfac 1214/1 Ligne 1 code Forms 9i Qté 3
Numcli/Numfac 1214/1 Ligne 1 code Reports 9i Qté 3
Numcli/Numfac 1215/2 Ligne 1 code Forms 9i Qté 2
Numcli/Numfac 1215/2 Ligne 1 code Reports 9i Qté 2

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

Le champ lignes de l’enregistrement est déclaré de type LIGNE%type donc de type TYP_LIG_FAC.
On récupère dans un enregistrement l’entête de la facture ainsi que toutes les colonnes des lignes attachées.

Ou l’on s’aperçoit que le type RECORD permet de stocker et manipuler des objets complexes.

Une variable de type RECORD peut être utilisée dans une clause RETURNING INTO


SQL> Declare
  2    TYPE Emp_rec IS RECORD
  3    (
  4      empno    emp.empno%type,
  5      empname  emp.ename%type,
  6      salaire  emp.sal%type
  7    );
  8    emp_info Emp_rec ;
  9  Begin
 10      Select empno, ename, sal Into emp_info From EMP where empno = 7499 ;
 11      dbms_output.put_line( 'Ancien  salaire pour ' || emp_info.empno || ' : ' || To_char(emp_info.salaire) ) ;
 12   
 13      Update EMP set sal = sal * 1.1 Where empno = 7499 
 14         RETURNING empno, ename, sal INTO emp_info ; 
 15   
 16      dbms_output.put_line( 'Nouveau salaire pour ' || emp_info.empno || ' : ' || To_char(emp_info.salaire) ) ;
 17  End  ;
 18  /
Ancien  salaire pour 7499 : 1760
Nouveau salaire pour 7499 : 1936

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

Crédit: ce chapitre est extrait d’un tutoriel de Francois Degrelle disponible sur : http://sheikyerbouti.developpez.com/