Oracle PL/SQL


précédentsommairesuivant

5. Collections et enregistrements

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

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


 
Sélectionnez

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

 
Sélectionnez

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.

 
Sélectionnez

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.

 
Sélectionnez

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 OKEnd if ;
  Tab1 := TYP_VAR_TAB('','','','','','','','','','');
  -- La collection est manipulable --
End ;

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


 
Sélectionnez

  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

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

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;

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

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

LN$i := ma_collection.LAST ;
While LN$i is not null LoopLN$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

 
Sélectionnez

LN$i := ma_collection.FIRST ;
While LN$i is not null LoopLN$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

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

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

 
Sélectionnez

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é

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

 
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(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

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

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

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

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

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

 
Sélectionnez

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 ;

5.4. 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)

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

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

 
Sélectionnez

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.

5.5. Traitements en masse des collections

Les collections permettent le traitement des données en " masse "

Elles permettent de charger les données d'une table, de les traiter puis de les enregistrer dans la base
Afin de limiter les interactions coûteuses entre le moteur PL/SQL et le moteur SQL,
les collections peuvent être traitées intégralement grâce à la copie des données par blocs

Cette copie des données par blocs autorise un ordre SQL à traiter toute la collection grâce aux instructions BULK COLLECT et FORALL.

Pour s'en convaincre, analysons le code suivant :

 
Sélectionnez

SQL> Declare
  2  TYPE   TYP_TAB_NUM IS TABLE OF TEST.B%TYPE INDEX BY PLS_INTEGER ;
  3  TYPE   TYP_TAB_CAR IS TABLE OF TEST.A%TYPE INDEX BY PLS_INTEGER ;
  4  tab1   TYP_TAB_NUM ;
  5  tab2   TYP_TAB_CAR ;
  6  LN$T1  number ;
  7  LN$T2  number ;
  8  LN$T3  number ;
  9  begin
 10     For i in 1..50000 Loop
 11       tab1(i) := i ;
 12       tab2(i) := ltrim( to_char( i ) ) ;
 13     End loop ;
 14  
 15     Select to_char( sysdate, 'SSSSS' ) into LN$T1 from dual ;
 16     
 17     For i in 1..50000 Loop
 18         Insert into TEST( A, B ) Values( tab2(i), tab1(i) ) ;
 19     End loop ;   
 20  
 21     Select to_char( sysdate, 'SSSSS' ) into LN$T2 from dual ;
 22     
 23     Forall i in 1..50000
 24         Insert into TEST( A, B ) Values( tab2(i), tab1(i) ) ;
 25     
 26     Select to_char( sysdate, 'SSSSS' ) into LN$T3 from dual ;
 27     
 28     dbms_output.put_line( 'Temps d''exécution en secondes' ) ;
 29     dbms_output.put_line( 'For    ' || to_char(LN$T2 - LN$T1) ) ;
 30     dbms_output.put_line( 'Forall ' || to_char(LN$T3 - LN$T2) ) ;
 31     
 32        
 33  End ;    
 34  /
Temps d'exécution en secondes
For    14
Forall 1

Les deux parties de code réalisent exactement la même opération soit l'insertion de 50000 lignes dans une table.
Cependant les temps d'exécutions respectifs sont sans commune mesure
La différence s'explique uniquement par la charge de travail générée par les passages entre le moteur PL/SQL et le moteur SQL

BULK COLLECT

(Select)(Fetch)(execute immediate) … BULK COLLECT Into nom_collection [,nom_collection, …] [LIMIT nombre_lignes] ;

Ce mot clé demande au moteur SQL de retourner l'ensemble des lignes lues dans une ou plusieurs collections avant de rendre la main au moteur PL/SQL.
Cette fonctionnalité réduit donc considérablement les allers-retours entre les deux moteurs.

Dans le cas d'une instruction FETCH, la clause optionnelle LIMIT permet de restreindre le nombre de lignes ramenées.
Ce nombre de lignes doit être exprimé sous forme de littéral ou de variable

Dans l'exemple suivant, on alimente la collection par groupes de 3 lignes

 
Sélectionnez

SQL> Declare
  2   TYPE    TYP_TAB_EMP IS TABLE OF EMP.EMPNO%Type ;
  3   Temp_no TYP_TAB_EMP ;
  4   Cursor  C_EMP is Select empno From EMP ;
  5   Pass    Pls_integer := 1 ;
  6  Begin
  7   Open C_EMP ;
  8   Loop
  9     Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;
 10     For i In Temp_no.first..Temp_no.last Loop
 11       dbms_output.put_line( 'Pass ' || to_char(Pass) || ' Empno= ' || Temp_no(i) ) ;
 12     End loop ;
 13     Pass := Pass + 1 ;
 14     Exit When C_EMP%NOTFOUND ;
 15   End Loop ;
 16  End ;
 17  /
Pass 1 Empno= 7369
Pass 1 Empno= 7499
Pass 1 Empno= 7521
Pass 2 Empno= 7566
Pass 2 Empno= 7654
Pass 2 Empno= 7698
Pass 3 Empno= 7782
Pass 3 Empno= 7788
Pass 3 Empno= 7839
Pass 4 Empno= 7844
Pass 4 Empno= 7876
Pass 4 Empno= 7900
Pass 5 Empno= 7902
Pass 5 Empno= 7934

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

On peut également utiliser le mot clé LIMIT pour effectuer par tranches, des opérations coûteuses pour le ROLLBACK SEGMENT

 
Sélectionnez

SQL> select empno,sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 ligne(s) sélectionnée(s).
 
Sélectionnez

SQL> Declare
  2   TYPE    TYP_TAB_EMP IS TABLE OF EMP.EMPNO%Type ;
  3   Temp_no TYP_TAB_EMP ;
  4   Cursor  C_EMP is Select empno From EMP ;
  5  Begin
  6   Open C_EMP ;
  7   Loop
  8     Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;
  9      Forall i In Temp_no.first..Temp_no.last 
 10         Update EMP set SAL = Round(SAL * 1.1) Where empno = Temp_no(i) ;
 11      Commit ;
 12      Temp_no.DELETE ;
 13      Exit When C_EMP%NOTFOUND ;
 14   End Loop ;
 15  End ;
 16  /

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

SQL> select empno,sal from emp;

     EMPNO        SAL
---------- ----------
      7369        880
      7499       1760
      7521       1375
      7566       3273
      7654       1375
      7698       3135
      7782       2695
      7788       3300
      7839       5500
      7844       1650
      7876       1210
      7900       1045
      7902       3300
      7934       1430

14 ligne(s) sélectionnée(s).

Le mot clé BULK COLLECT peut également être utilisé pour récupérer les résultats d'un ordre DML, lorsqu'il est associé à la clause RETURNING INTO.

 
Sélectionnez

SQL> Declare
  2    TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type ;
  3    TYPE    TYP_TAB_NOM   IS TABLE OF EMP.ENAME%Type ;  
  4    Temp_no TYP_TAB_EMPNO ;
  5    Tnoms   TYP_TAB_NOM ;  
  6  Begin
  7     Delete From EMP where sal > 3000 
  8        RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;
  9     For i in Temp_no.first..Temp_no.last Loop
 10        dbms_output.put_line( 'Employé viré : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;
 11     End  loop ;
 12  End ;
 13  /
Employé viré : 7566 JONES
Employé viré : 7698 BLAKE
Employé viré : 7788 SCOTT
Employé viré : 7839 KING
Employé viré : 7902 FORD

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

Une attention particulière doit être portée sur l'utilisation des méthodes appliquées aux collections, notamment FIRST.

Dans l'exemple suivant, la procédure tombe en erreur car aucune ligne n'est retournée.
Dans ce cas nom_collection.FIRST ne vaut pas zéro mais NULL

 
Sélectionnez

SQL> Declare
  2    TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type ;
  3    TYPE    TYP_TAB_NOM   IS TABLE OF EMP.ENAME%Type ;  
  4    Temp_no TYP_TAB_EMPNO ;
  5    Tnoms   TYP_TAB_NOM ;  
  6  Begin
  7     Delete From EMP where sal < 100 
  8        RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;
  9     For i in Temp_no.first..Temp_no.last Loop
 10        dbms_output.put_line( 'Employé viré : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;
 11     End  loop ;
 12  End ;
 13  /
Declare
*
ERREUR à la ligne 1 :
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à ligne 9

Il convient de tester la nullité d'une méthode avant de l'utiliser :

 
Sélectionnez

SQL> Declare
  2    TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type ;
  3    TYPE    TYP_TAB_NOM   IS TABLE OF EMP.ENAME%Type ;  
  4    Temp_no TYP_TAB_EMPNO ;
  5    Tnoms   TYP_TAB_NOM ;  
  6  Begin
  7     Delete From EMP where sal < 100 
  8        RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;
  9     If Temp_no.first is not null Then
 10       For i in Temp_no.first..Temp_no.last Loop
 11          dbms_output.put_line( 'Employé viré : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;
 12       End  loop ;
 13     End if ;
 14  End ;
 15  /

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


FORALL

FORALL index IN borne_inférieure..borne_supérieure [SAVE EXCEPTION] ordre_sql

Bien que l'instruction FORALL précise une borne début et une borne fin, il ne peut pas être inclus dans une boucle FOR … LOOP
L'instruction FORALL ne peut pas être utilisée dans le code PL/SQL coté client
L'ordre SQL doit être INSERT, UPDATE ou DELETE en relation avec au moins une collection
Il doit exister des éléments dans la collection pour toutes les valeurs d'indice de l'instruction FORALL
Il n'est pas possible d'exprimer l'indice sous forme d'un calcul
FORALL ne peut traiter qu'un seul ordre SQL à la fois sous peine de générer un message d'erreur :

 
Sélectionnez

SQL> Declare
  2  TYPE TYP_TAB_NUM IS TABLE OF TEST.B%TYPE INDEX BY PLS_INTEGER ;
  3  TYPE TYP_TAB_CAR IS TABLE OF TEST.A%TYPE INDEX BY PLS_INTEGER ;
  4  tab1 TYP_TAB_NUM ;
  5  tab2 TYP_TAB_CAR ;
  6  begin
  7     For i in 1..1000 Loop
  8       tab1(i) := i ;
  9       tab2(i) := ltrim( to_char( i ) ) ;
 10     End loop ;
 11     
 12     Forall i in 1..1000
 13         Insert into TEST( A, B ) Values( tab2(i), tab1(i) ) ;
 14         Delete from TEST where A = tab2(i) ;    
 15  End ;
 16  /
       Delete from TEST where A = tab2(i) ;
                                       *
ERREUR à la ligne 14 :
ORA-06550: Ligne 14, colonne 40 :
PLS-00201: l'identificateur 'I' doit être déclaré
ORA-06550: Ligne 14, colonne 35 :
PL/SQL: ORA-00904:  : identificateur non valide
ORA-06550: Ligne 14, colonne 8 :
PL/SQL: SQL Statement ignored

La variable d'index I n'est plus connue en sortie de l'instruction FORALL

A partir d'oracle9i, les copies d'informations par blocs peuvent être effectuées directement dans les collections d'enregistrements

 
Sélectionnez

SQL> Declare
  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;
  3    tabrec TYP_TAB_TEST ;
  4    CURSOR C_test is select A, B From TEST ;
  5  begin
  6     -- chargement de la collection depuis la table --
  7     Select A, B BULK COLLECT into tabrec From TEST ;
  8     
  9     -- insertion de lignes à partir de la collection --
 10     Forall i in tabrec.first..tabrec.last
 11         insert into TEST values tabrec(i) ;
 12      
 13     -- mise à jour des données de la collection --
 14     For i in tabrec.first..tabrec.last Loop
 15         tabrec(i).B := tabrec(i).B * 2 ;    
 16     End loop ;
 17     
 18     -- utilisation du curseur --
 19     Open C_test ;
 20     Fetch C_test BULK COLLECT Into tabrec ;
 21     Close C_test ;
 22         
 23  End ;
 24  /

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

Il n'est pas possible de mettre à jour une ligne complète avec une collection d'enregistrements en conjonction avec l'instruction FORALL

 
Sélectionnez

Forall i in tabrec.first..tabrec.last
	   update TEST set row =  tabrec(i) where A = tabrec(i).A ; -- INVALIDE   

Pour cela il faut utiliser une boucle For..Loop

 
Sélectionnez

   For i in tabrec.first..tabrec.last loop
	   update TEST set row =  tabrec(i) where A = tabrec(i).A ;
   End loop ;

Ou utiliser des collections simples

 
Sélectionnez

Declare
  TYPE   TYP_TAB_A IS TABLE OF TEST.A%TYPE ;
  TYPE   TYP_TAB_B IS TABLE OF TEST.B%TYPE ;    
  taba   TYP_TAB_A ;
  tabb   TYP_TAB_B ;  
  CURSOR C_test is select A, B From TEST ;
Begin
   -- utilisation du curseur --
   Open C_test ;
   Fetch C_test BULK COLLECT Into taba, tabb ;
   Close C_test ;
   
   -- mise à jour des données de la collection --
   For i in taba.first..taba.last Loop
       tabb(i) := tabb(i) * 2 ;
   End loop ;
   
   -- mise à jour des lignes de la table --
   Forall i in taba.first..taba.last
	   update TEST set B =  tabb(i) where A = taba(i) ;             
End ;	   

La gestion des erreurs avec la commande FORALL

Sans gestion particulière des exceptions dans le bloc PL/SQL, toute erreur d'exécution provoque l'annulation de toutes
les instructions réalisées par l'instruction FORALL (ROLLBACK)
En présence d'une section Exception dans le bloc PL/SQL, il sera possible de décider si l'on conserve les modifications valides jusqu'à l'erreur (COMMIT)
ou si l'on annule toute l'instruction FORALL (ROLLBACK)

Il est également possible, lors d'erreur sur une instruction, de sauvegarder l'information concernant l'exception et de poursuivre le processus
Cette fonctionnalité est implémentée par l'ajout du mot clé SAVE EXCEPTION dans l'instruction FORALL
Toutes les exceptions levées en cours d'exécution sont sauvées dans l'attribut %BULK_EXCEPTIONS, qui est une collection d'enregistrements.
Chaque enregistrement est composé de 2 champs :

%BULK_EXCEPTIONS(n).ERROR_INDEX qui contient l'indice de l'itération qui a provoqué l'erreur
%BULK_EXCEPTIONS(n).ERROR_CODE qui contient le code d'erreur

Le nombre total d'exceptions générées par l'instruction FORALL est contenu dans l'attribut SQL%BULK_EXCEPTIONS.COUNT

 
Sélectionnez

SQL> Declare
  2    TYPE    TYP_TAB IS TABLE OF Number ;
  3    tab     TYP_TAB := TYP_TAB( 2, 0, 1, 3, 0, 4, 5 ) ;  
  4    nb_err  Pls_integer ;  
  5  Begin
  6     Forall i in tab.first..tab.last SAVE EXCEPTIONS
  7         delete from TEST where B = 5 / tab(i) ;
  8  Exception
  9    When others then    
 10      nb_err := SQL%BULK_EXCEPTIONS.COUNT ;
 11      dbms_output.put_line( to_char( nb_err ) || ' Erreurs ' ) ;
 12      For i in 1..nb_err Loop   
 13         dbms_output.put_line( 'Indice ' || to_char( SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ) || ' Erreur Oracle :  ' 
 14		|| to_char( SQL%BULK_EXCEPTIONS(i).ERROR_CODE ) ) ;
 15      End loop ;     
 16  End ; 
 17  /
2 Erreurs
Indice 2 Erreur Oracle :  1476
Indice 5 Erreur Oracle :  1476

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


L'attribut %BULK_ROWCOUNT

En plus des attributs de curseur %FOUND, %NOTFOUND, %ISOPEN et %ROWCOUNT, le curseur implicite SQL dispose d'un attribut spécifique
à l'instruction FORALL : %BULK_ROWCOUNT

Il s'agit d'une collection de type INDEX BY TABLE pour laquelle l'élément d'indice n contient le nombre de lignes affectées par l'exécution de l'ordre SQL numéro n
Si aucune ligne n'est affectée par l'instruction numéro n alors l'attribut SQL%BULK_ROWCOUNT(n) vaut 0

 
Sélectionnez

SQL> Declare
  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;
  3    TYPE   TYP_TAB_A IS TABLE OF TEST.A%TYPE ;
  4    TYPE   TYP_TAB_B IS TABLE OF TEST.B%TYPE ;    
  5    tabrec TYP_TAB_TEST ;
  6    taba   TYP_TAB_A ;
  7    tabb   TYP_TAB_B ;
  8    total  Pls_integer := 0 ;  
  9    CURSOR C_test is select A, B From TEST ;
 10  begin
 11     -- chargement de la collection depuis la table --
 12     Select A, B BULK COLLECT into tabrec From TEST ;
 13  
 14     -- insertion de lignes à partir de la collection --
 15     Forall i in tabrec.first..tabrec.last
 16         insert into TEST values tabrec(i) ;
 17      
 18     For i in tabrec.first..tabrec.last Loop
 19         total := total + SQL%BULK_ROWCOUNT(i) ;
 20     End loop ;
 21      
 22     dbms_output.put_line('Total insertions : ' || to_char( total) ) ;
 23          
 24     total := 0 ;
 25     -- mise à jour d'une ligne précise non permise avec les collections d'enregistrements --
 26     For i in tabrec.first..tabrec.last loop
 27       update TEST set row =  tabrec(i) where A = tabrec(i).A ;
 28     End loop ;
 29     
 30     For i in tabrec.first..tabrec.last Loop
 31         total := total + SQL%BULK_ROWCOUNT(i) ;
 32     End loop ;
 33      
 34     dbms_output.put_line('Total mises à jour : ' || to_char( total) ) ;   
 35      
 36  End ;
 37  /
Total insertions : 20
Total mises à jour : 20

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

5.6. Les collections et enregistrements en paramètres des procédures et fonctions

Le passage de ces types d'objets se fait de façon identique qu'avec les types scalaires.

Soit le package suivant :

 
Sélectionnez

SQL> CREATE OR REPLACE PACKAGE PKG_TEST IS
  2    
  3    TYPE TYP_TAB_EMP  IS TABLE OF EMP%ROWTYPE ;
  4    TYPE TYP_TAB_EMP2 IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER ;  
  5    
  6    PROCEDURE Affiche_lignes ( Temp IN TYP_TAB_EMP ) ;
  7    
  8    PROCEDURE Affiche_ligne ( Temp IN OUT EMP%ROWTYPE ) ;
  9    
 10    PROCEDURE Affiche_ligne_nocopy ( Temp IN OUT NOCOPY EMP%ROWTYPE ) ;  
 11    
 12    PROCEDURE Modifie_lignes ( Temp IN OUT TYP_TAB_EMP ) ;
 13    
 14    PROCEDURE Modifie_lignes_nocopy ( Temp IN OUT NOCOPY TYP_TAB_EMP ) ;
 15    
 16    PROCEDURE Modifie_lignes2 ( Temp IN OUT TYP_TAB_EMP2 ) ;
 17    
 18    PROCEDURE Modifie_lignes_nocopy2 ( Temp IN OUT NOCOPY TYP_TAB_EMP2 ) ;
 19    
 20    FUNCTION Affiche_lignes Return TYP_TAB_EMP ;      
 21    
 22  END;
 23  /

Package créé.

SQL> CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
  2  
  3    GN$Lig pls_integer := 0 ;
  4  
  5    PROCEDURE Affiche_lignes ( Temp IN TYP_TAB_EMP )
  6    IS
  7    Begin
  8     GN$Lig := 0 ;
  9     For i IN Temp.first..Temp.last Loop
 10       GN$Lig := GN$Lig + 1 ;
 11       If GN$Lig  <= 10 Then
 12          dbms_output.put_line( Rpad( Temp(i).ename, 25 ) || ' --> ' || To_char( Temp(i).sal ) ) ;
 13       End if ;
 14     End loop ;
 15    End Affiche_lignes ;
 16  
 17    PROCEDURE Affiche_ligne ( Temp IN OUT EMP%ROWTYPE )
 18    IS
 19    Begin
 20      GN$Lig := GN$Lig + 1 ;
 21      Temp.sal := Temp.sal * 1.1 ; 
 22      If GN$Lig  <= 10 Then
 23        dbms_output.put_line( Rpad( Temp.ename, 25 ) || ' --> ' || To_char( Temp.sal ) ) ;
 24      End if ;
 25    End Affiche_ligne ;
 26  
 27    PROCEDURE Affiche_ligne_nocopy ( Temp IN OUT NOCOPY EMP%ROWTYPE )
 28    IS
 29    Begin
 30      GN$Lig := GN$Lig + 1 ;
 31      Temp.sal := Temp.sal * 1.1 ;
 32      If GN$Lig  <= 10 Then  
 33        dbms_output.put_line( Rpad( Temp.ename, 25 ) || ' --> ' || To_char( Temp.sal ) ) ;
 34      End if ;
 35    End Affiche_ligne_nocopy ;
 36      
 37    PROCEDURE Modifie_lignes ( Temp IN OUT TYP_TAB_EMP )
 38    IS
 39    Begin
 40     GN$Lig := 0 ;
 41     For i IN Temp.first..Temp.last Loop
 42       Temp(i).sal := Temp(i).sal * 1.1 ;
 43       Affiche_ligne( Temp(i) ) ;
 44     End loop ;
 45    End Modifie_lignes ;
 46    
 47    PROCEDURE Modifie_lignes_nocopy ( Temp IN OUT NOCOPY TYP_TAB_EMP )
 48    IS
 49    Begin
 50     GN$Lig := 0 ;
 51     For i IN Temp.first..Temp.last Loop
 52       Temp(i).sal := Temp(i).sal * 1.1 ;
 53       Affiche_ligne_nocopy( Temp(i) ) ; 
 54     End loop ;
 55    End Modifie_lignes_nocopy ;
 56    
 57    PROCEDURE Modifie_lignes2 ( Temp IN OUT TYP_TAB_EMP2 )
 58    IS
 59    Begin
 60     GN$Lig := 0 ;
 61     For i IN Temp.first..Temp.last Loop
 62       Temp(i).sal := Temp(i).sal * 1.1 ;
 63       Affiche_ligne( Temp(i) ) ;
 64       --dbms_output.put_line( Rpad( Temp(i).ename, 25 ) || ' --> ' || To_char( Temp(i).sal ) ) ;
 65     End loop ;
 66    End Modifie_lignes2 ;
 67    
 68    PROCEDURE Modifie_lignes_nocopy2 ( Temp IN OUT NOCOPY TYP_TAB_EMP2 )
 69    IS
 70    Begin
 71     GN$Lig := 0 ;
 72     For i IN Temp.first..Temp.last Loop
 73       Temp(i).sal := Temp(i).sal * 1.1 ;
 74       Affiche_ligne_nocopy( Temp(i) ) ; 
 75       --dbms_output.put_line( Rpad( Temp(i).ename, 25 ) || ' --> ' || To_char( Temp(i).sal ) ) ;
 76     End loop ;
 77    End Modifie_lignes_nocopy2 ;    
 78    
 79  
 80    FUNCTION Affiche_lignes Return TYP_TAB_EMP
 81    IS
 82      Tlignes PKG_TEST.TYP_TAB_EMP ;
 83      Cursor C_EMP is Select * From EMP ;
 84    Begin
 85      Open C_EMP ;
 86      Fetch C_EMP BULK COLLECT into Tlignes ;
 87      Close C_EMP ;
 88      Return( Tlignes ) ;
 89    End ;
 90    
 91  END;
 92  /

Corps de package créé.


Collections en argument de procédure

Nous allons maintenant écrire un bloc PL/SQL anonyme qui utilise la fonction Affiche_lignes()

 
Sélectionnez

SQL> Declare
  2    Tlignes PKG_TEST.TYP_TAB_EMP ;
  3    Cursor  C_EMP is Select * From EMP ;
  4  Begin
  5  
  6   Open  C_EMP ;
  7   Fetch C_EMP BULK COLLECT into Tlignes ;
  8   Close C_EMP ;
  9   
 10   PKG_TEST.Affiche_lignes( Tlignes ) ;
 11   
 12  End ;
 13  /
SMITH                     --> 880
ALLEN                     --> 1760
WARD                      --> 1375
JONES                     --> 3273
MARTIN                    --> 1375
BLAKE                     --> 3135
CLARK                     --> 2695
SCOTT                     --> 3300
KING                      --> 5500
TURNER                    --> 1650
ADAMS                     --> 1210
JAMES                     --> 1045
FORD                      --> 3300
MILLER                    --> 1430

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

Une variable Tlignes est déclarée avec le type PKG_TEST.TYP_TAB_EMP
qui est une collection d'enregistrements de type EMP%ROWTYPE

Cette collection est alimentée par le curseur C_EMP et transmise à la procédure qui en affiche une partie du contenu.
Vous pouvez également ne transmettre qu'une ligne de la collection

 
Sélectionnez

SQL> Declare
  2    Tlignes PKG_TEST.TYP_TAB_EMP ;
  3    Cursor  C_EMP is Select * From EMP ;
  4  Begin
  5   Open  C_EMP ;
  6   Fetch C_EMP BULK COLLECT into Tlignes ;
  7   Close C_EMP ;
  8   PKG_TEST.Affiche_ligne( Tlignes(2) ) ;
  9  End ;
 10  /
ALLEN                     --> 1760

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

La procédure Affiche_ligne() reçoit en argument une variable de type EMP%ROWTYPE.
Dans l'appel, seule la deuxième ligne de la collection lui est transmise.

Pour pouvoir en modifier la valeurs des éléments, la collection doit être transmise en mode IN OUT

 
Sélectionnez

SQL> Declare
  2    Tlignes PKG_TEST.TYP_TAB_EMP ;
  3    Cursor  C_EMP is Select * From EMP ;
  4  Begin
  5  
  6   Open  C_EMP ;
  7   Fetch C_EMP BULK COLLECT into Tlignes ;
  8   Close C_EMP ;
  9   
 10   PKG_TEST.Modifie_lignes( Tlignes ) ;
 11   
 12  End ;
 13  /
SMITH                     --> 968
ALLEN                     --> 1936
WARD                      --> 1512,5
JONES                     --> 3600,3
MARTIN                    --> 1512,5
BLAKE                     --> 3448,5
CLARK                     --> 2964,5
SCOTT                     --> 3630
KING                      --> 6050
TURNER                    --> 1815
ADAMS                     --> 1331
JAMES                     --> 1149,5
FORD                      --> 3630
MILLER                    --> 1573

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

Lors du passage de collections ou éléments de collection à des procédures ou fonctions, les arguments sont passés par copie.
Une copie de l'objet est faite dans un espace mémoire particulier.
Dans le cas où des collections très volumineuses sont passées en argument, où de nombreux appels sont effectuées avec des collections
ou éléments de collection, il est préférable de passer les arguments en mode NOCOPY.
En effet dans ce mode, aucun espace mémoire supplémentaire n'est alloué puisque la variable est passée par référence (Le pointeur sur la variable)
Voir la procédure PKG_TEST.Affiche_ligne_nocopy()



Collections en retour de fonction

Soit la fonction PKG_TEST.Affiche_lignes

 
Sélectionnez

  FUNCTION Affiche_lignes Return TYP_TAB_EMP
  IS
    Tlignes PKG_TEST.TYP_TAB_EMP ;
    Cursor  C_EMP is Select * From EMP ;
  Begin
    Open  C_EMP ;
    Fetch C_EMP BULK COLLECT into Tlignes ;
    Close C_EMP ;
    Return( Tlignes ) ;
  End ;

Appelée par le bloc PL/SQL suivant :

 
Sélectionnez

SQL> Declare
  2    Tlignes PKG_TEST.TYP_TAB_EMP ;
  3  Begin
  4    Tlignes := PKG_TEST.Affiche_lignes ;
  5    For i IN Tlignes.first..Tlignes.last Loop
  6       dbms_output.put_line( Rpad( Tlignes(i).ename, 25 ) || ' --> ' || To_char( Tlignes(i).sal ) ) ;
  7    End loop ;
  8  End ;
  9  /
SMITH                     --> 880
ALLEN                     --> 1760
WARD                      --> 1375
JONES                     --> 3273
MARTIN                    --> 1375
BLAKE                     --> 3135
CLARK                     --> 2695
SCOTT                     --> 3300
KING                      --> 5500
TURNER                    --> 1650
ADAMS                     --> 1210
JAMES                     --> 1045
FORD                      --> 3300
MILLER                    --> 1430

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

La fonction Affiche_lignes retourne une collection de type PKG_TEST.TYP_TAB_EMP,
ouvre un curseur sur la table EMP pour remplir la collection et la retourne au bloc PL/SQL appelant qui peut en afficher les valeurs


Enregistrements

Lorsque l'on veut appeler une fonction retournant un type RECORD, il faut respecter la syntaxe suivante pour référencer un champ de l'enregistrement :

Nom_fonction(argument).nom_champ

 
Sélectionnez

SQL> Declare
  2    TYPE Emp_rec IS RECORD
  3    (
  4      empno  emp.empno%type,
  5      salaire  emp.sal%type
  6    );
  7    salaire emp.sal%type ;
  8    Function xx(ln$numemp in emp.EMPNO%type) return Emp_rec
  9    is
 10      emp_info EMp_rec ;
 11    Begin
 12      Select empno, sal into emp_info.empno, emp_info.salaire
 13      from emp where empno = ln$numemp ;
 14      return emp_info ;
 15    End ;   
 16  Begin
 17    salaire := xx(7499).salaire ;
 18    dbms_output.put_line( 'Salaire de 7499 : ' || to_char(salaire) ) ;
 19  End  ; 
 20  /
Salaire de 7499 : 1760

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

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.