Discussion :

[Jinkas99]

Bonjour,

Je cherche à retourner une liste en IN OUT à partir d'une fonction.

Je voudrais pouvoir ajouter des éléments dans la liste en utilisant la fonction extend mais avant cela vérifier si l'élément à ajouter existe déjà dans une autre table.

J'ai essayé de faire ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DECLARE
 
	TYPE REC IS RECORD(
		CODE_REMISE VARCHAR2(10),
		CODE_POURCENTAGE NUMBER
    );
 
	TYPE TAB IS TABLE OF REC
		INDEX BY BINARY_INTEGER;
 
	VTAB TAB;
 
	CURSOR C1 IS
		SELECT CODE_REMISE, CODE_POURCENTAGE
		FROM TABLE_REMISE
		WHERE NUM = '1DOLL123';
 
	V_CODE_REMISE VARCHAR2(10);
 
BEGIN
 
  OPEN C1;
  FETCH C1 BULK COLLECT INTO VTAB;
 
  IF VTAB.COUNT > 0 THEN
	FOR I IN 1..VTAB.COUNT
	LOOP
 
	SELECT C_REMISE
	INTO V_CODE_REMISE
	FROM TABLE_REMISE_TMP
	WHERE C_REMISE = VTAB(I).CODE_REMISE;
 
	IF V_CODE_REMISE IS NOT NULL OR V_CODE_REMISE <> '' THEN
	VTAB.EXTEND(....);
    ...
	...
 
END;

mais je ne sais si ce que j'ai fait jusqu'à présent est correct et je ne vois pas comment utiliser la fonction extend dans mon code.

Merci,

[mnitu]

Lisez un peu la doc PL/SQL Collections and Records avant de vous lancer dans la programmation cela permet d'avancer beaucoup plus vite.

EXTEND is a procedure that adds elements to the end of a varray or nested table.

Veut dire que la méthode ne s'applique pas aux collections de type vecteur index by

[Jinkas99]

Bonjour mnitu,

J'ai parvenu à faire ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE
	TYPE TYPE_LISTE IS TABLE OF MA_TABLE.MON_CHAMPS%TYPE;
 
	MA_LISTE TYPE_LISTE := TYPE_LISTE();
 
	CURSOR C IS
		SELECT T1.MON_CHAMPS
		FROM MA_TABLE1 T1
		WHERE EXISTS (SELECT T2.MON_CHAMPS FROM MA_TABLE2 T2 WHERE T2.MON_CHAMPS = T1.MON_CHAMPS);
 
BEGIN  
    BEGIN
      FOR R IN C
      LOOP
        MA_LISTE.EXTEND;
        MA_LISTE(MA_LISTE.COUNT) := R.MON_CHAMPS;
      END LOOP;
    END;
END;

Merci,

[mnitu]

Pourquoi il a BEGIN suivi de BEGIN ?
Pourquoi charger via extend quand le bulk fetch permet de faire la même chose directement ?

[Jinkas99]

Merci pour votre réponse.

Pour le second begin c'est parce que je voulais ajouter une exception.

[mnitu]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Declare
  Type TYPE_LISTE Is Table OF EMP.ENAME%Type; 
  ma_liste        TYPE_LISTE;
Begin
  Select e.ename
    Bulk Collect Into ma_liste
    From emp e    
   Where Exists (Select Null 
                   From dept d
                  Where d.deptno = e.deptno
                );
End;

Pour les exceptions la règle est simple gérez que les exceptions que vous pouvez traiter au niveau de cette procédure sinon laisse-les se propager vers la routine appelante.

[Jinkas99]

Merci pour cette réponse claire.
Je vais tester votre méthode(bulk) dans ma fonction pour remplir 2 listes en paramètres IN OUT avec 2 curseurs différents. Je ne sais pas si je dois définir 2 cur et les parcourir avec une boucle for car j'ai des traitements à faire lorsque je parcours mes curseurs.

[mnitu]

Cela dépende du traitement. Si il peut être fait via SQL le tour est joué.
Et faite également attention au nombre des enregistrements traités: Select ... Bulk Collect Into est adéquat pour des volumétries allant entre 1000 et 5000 enregistrements. Au delà vous devez traiter les collections par lot.

[Jinkas99]

Que voulez vous dire par lot?

De plus je voudrais savoir comment je pourrais gérer les exceptions when others and when no_data_found dans les méthodes proposées. Car, je souhaiterais pour chacun des cas initialiser une variable déclarée (ma_variable) de type number à 1, 2, 3 en fonction des différents cas c'est à dire :
Cas 1 - Trouvé (initialiser ma_variable := 1)
Cas 2 - NON Trouvé (initialiser ma_variable := 2)
Cas 3 - Erreur (initialiser ma_variable := 3)

Methode 1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Declare
  Type TYPE_LISTE Is Table OF EMP.ENAME%Type; 
  ma_liste        TYPE_LISTE;
Begin
  Select e.ename
    Bulk Collect Into ma_liste
    From emp e    
   Where Exists (Select Null 
                   From dept d
                  Where d.deptno = e.deptno
                );
End;

Methode 2:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Declare
  Type TYPE_LISTE Is Table OF EMP.ENAME%Type; 
  ma_liste        TYPE_LISTE;
 
CURSOR CUR IS Select e.ename
    Bulk Collect Into ma_liste
    From emp e    
   Where Exists (Select Null 
                   From dept d
                  Where d.deptno = e.deptno
                );
Begin
 
	for r in c
        loop
	    ma_liste.extend;
	    ma_liste(ma_liste.count) := r.ename;
     end loop;
 
     return ma_variable;
End;

Merci,

[mnitu]

Les applications des bases des données nécessite des développeurs ayant appris à réfléchir en termes des traitements des ensembles des données et non pas comme dans la programmation des interfaces utilisateurs.

Etudiez l'exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
Declare
  -- un employé 
  Type Emp Is Record (
    first_name       hr.employees.first_name%Type,
    last_name        hr.employees.last_name%Type,
    department_name  hr.departments.department_name%Type,
    Ind_Trt          Pls_Integer
  );
  -- tableau des employés
  Type Emp_nt Is Table Of Emp;
  -- variable tableau des employées
  Emps        Emp_Nt;
Begin
  Select e.first_name, 
         e.last_name, 
         d.department_name, 
         Case 
           When d.department_id Is Null Then 2 
           Else 1 
        End Ind_trt
    Bulk Collect Into Emps
    From hr.employees e
         Left Outer Join 
         hr.departments d
      On d.department_id = e.department_id;
End;

Il utilise une jointure externe entre la table employees et la table départements. Cela permet de préserver l'employé qui n'est pas encore rattaché à une direction. Dans ce cas le departement_id (clé primaire) de la table est
nul. En testant cette valeur pour nulle vous pouvez remplir directement dans SQL votre variable indicateur avec les valeurs 1 - présence et 2 - absence. Mais une fois que vous arrivez à cette solution vous commencez à vous interroger sur la pertinence de son existence; peut être que la zone department_name est déclarée en not null et dans ce cas l'absence de valeur pour cette zone vous indique directement le cas 2 - absence.

Autre remarque le bulk collect Into est insensible à l'exception NO_DATA_FOUND mais si la collection valorisée par la requête SQL n'as pas des éléments cela veut dire qu'il n'y a pas d'enregistrement correspondant aux critères. Testez le cas en ajoutant une clause Where 1 = 2.

Reste la question When Others liée à la valeur 3 de l'indicateur: quelle exception saurez-vous traiter dans ce cas ? Et d'autre part quelles sont les anomalies de votre traitement à utiliser pour la valeur 3 ?

[Jinkas99]

Encore merci pour vos explications.

Le seul moment où j'arrive à passer dans le when others c'est lorsque par exemple dans la méthode 2 :

j'ajoute dans ma liste des valeurs qui ont type différent de ce qu'attend la liste.

Si par exemple la liste est une liste de type number et que je lui ajoute des valeurs de type varchar

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ma_liste(ma_liste.count) := 'r.ename';