Type TABLE EXTEND

Version imprimable

Type TABLE EXTEND

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:

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,

11/05/2017, 10h18
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.

Citation:

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

Bonjour mnitu,

J'ai parvenu à faire ceci :

Code:

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,

11/05/2017, 22h03
mnitu

Pourquoi il a BEGIN suivi de BEGIN ?
Pourquoi charger via extend quand le bulk fetch permet de faire la même chose directement ?
12/05/2017, 00h46
Jinkas99

Merci pour votre réponse.

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

Code:

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.

12/05/2017, 11h53
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.
12/05/2017, 15h09
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.

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:

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:

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,

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:

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 ?

12/05/2017, 17h09
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:

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