Oracle Collections PL/SQL : Varrays, imbriqués et indexés par tables

Qu'est-ce que la collecte ?

Une collection est un groupe ordonné d’éléments de types de données particuliers. Il peut s'agir d'une collection de types de données simples ou de types de données complexes (comme les types définis par l'utilisateur ou les types d'enregistrement).

Dans la collection, chaque élément est identifié par un terme appelé "indice." Chaque élément de la collection se voit attribuer un indice unique. Les données de cette collection peuvent être manipulées ou récupérées en faisant référence à cet indice unique.

Les collections sont des éléments très utiles lorsqu'il est nécessaire de traiter ou de manipuler des données volumineuses du même type. Les collections peuvent être remplies et manipulées dans leur ensemble à l'aide de l'option « VRAC » dans Oracle.

Les collections sont classées en fonction de la structure, de l'indice et du stockage, comme indiqué ci-dessous.

Index par tables (également connu sous le nom de tableau associatif)
Tableaux imbriqués
Varrays

À tout moment, les données de la collection peuvent être référencées par trois termes Nom de la collection, Indice, Nom du champ/colonne comme « ( ). ». Vous allez en apprendre davantage sur ces catégories de collections mentionnées ci-dessus dans la section ci-dessous.

Varrays

Varray est une méthode de collection dans laquelle la taille du tableau est fixe. La taille du tableau ne peut pas être dépassée par rapport à sa valeur fixe. L'indice du Varray est d'une valeur numérique. Voici les attributs de Varrays.

La taille limite supérieure est fixe
Rempli séquentiellement en commençant par l'indice « 1 »
Ce type de collection est toujours dense, c'est-à-dire que nous ne pouvons supprimer aucun élément du tableau. Varray peut être supprimé dans son ensemble ou coupé à partir de la fin.
Comme il est toujours de nature dense, il a très moins de flexibilité.
Il est plus approprié de l'utiliser lorsque la taille du tableau est connue et d'effectuer des activités similaires sur tous les éléments du tableau.
L'indice et la séquence restent toujours stables, c'est-à-dire que l'indice et le nombre de la collection sont toujours les mêmes.
Ils doivent être initialisés avant de les utiliser dans des programmes. Toute opération (à l'exception de l'opération EXISTS) sur une collection non initialisée générera une erreur.
Il peut être créé en tant qu'objet de base de données, visible dans toute la base de données ou à l'intérieur du sous-programme, qui ne peut être utilisé que dans ce sous-programme.

La figure ci-dessous expliquera schématiquement l'allocation de mémoire de Varray (dense).

indice	1	2	3	4	5	6	7
Propositions	Xyz	Dfv	Sdé	Cxs	Vbc	Mou, tendre	Qwe

Syntaxe pour VARRAY :

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

Dans la syntaxe ci-dessus, type_name est déclaré comme VARRAY du type « DATA_TYPE » pour la limite de taille donnée. Le type de données peut être simple ou complexe.

Tables imbriquées

Une table imbriquée est une collection dans laquelle la taille du tableau n'est pas fixe. Il a le type d’indice numérique. Vous trouverez ci-dessous plus de descriptions sur le type de table imbriquée.

La table imbriquée n'a pas de limite de taille supérieure.
Étant donné que la limite supérieure de taille n'est pas fixe, la collection et la mémoire doivent être étendues à chaque fois avant de l'utiliser. Nous pouvons étendre la collection en utilisant le mot-clé 'EXTEND'.
Rempli séquentiellement en commençant par l'indice « 1 ».
Ce type de collecte peut être à la fois dense et clairsemé, c'est-à-dire que nous pouvons créer la collection comme dense, et nous pouvons également supprimer l'élément individuel du tableau de manière aléatoire, ce qui le rend aussi clairsemé.
Cela donne plus de flexibilité concernant la suppression de l'élément du tableau.
Il est stocké dans la table de base de données générée par le système et peut être utilisé dans la requête de sélection pour récupérer les valeurs.
L'indice et la séquence ne sont pas stables, c'est-à-dire que l'indice et le nombre d'éléments du tableau peuvent varier.
Ils doivent être initialisés avant de les utiliser dans des programmes. Toute opération (à l'exception de l'opération EXISTS) sur la collection non initialisée générera une erreur.
Il peut être créé en tant qu'objet de base de données, visible dans toute la base de données ou à l'intérieur du sous-programme, qui ne peut être utilisé que dans ce sous-programme.

La figure ci-dessous expliquera schématiquement l'allocation de mémoire de la table imbriquée (dense et clairsemée). L'espace d'élément de couleur noire désigne l'élément vide dans une collection, c'est-à-dire clairsemé.

indice	1	2	3	4	5	6	7
Valeur (dense)	Xyz	Dfv	Sdé	Cxs	Vbc	Mou, tendre	Qwe
Valeur (clairsemée)	Qwe		Asd	afg		Asd	Wer

Syntaxe pour la table imbriquée :

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

Dans la syntaxe ci-dessus, type_name est déclaré comme collection de tables imbriquées du type « DATA_TYPE ». Le type de données peut être simple ou complexe.

Index par table

L'index par table est une collection dans laquelle la taille du tableau n'est pas fixe. Contrairement aux autres types de collections, dans la collection index par table, l'indice peut être défini par l'utilisateur. Voici les attributs de l'index par table.

L'indice peut être un entier ou une chaîne. Au moment de la création de la collection, le type d'indice doit être mentionné.
Ces collections ne sont pas stockées de manière séquentielle.
Ils sont toujours de nature clairsemée.
La taille du tableau n'est pas fixe.
Ils ne peuvent pas être stockés dans la colonne de la base de données. Ils doivent être créés et utilisés dans n’importe quel programme de cette session particulière.
Ils offrent plus de flexibilité en termes de maintien de l'indice.
Les indices peuvent également être de séquence d'indices négatifs.
Il est plus approprié de les utiliser pour des valeurs collectives relativement plus petites dans lesquelles la collection peut être initialisée et utilisée dans les mêmes sous-programmes.
Il n'est pas nécessaire de les initialiser avant de commencer à les utiliser.
Il ne peut pas être créé en tant qu'objet de base de données. Il ne peut être créé qu'à l'intérieur du sous-programme, qui ne peut être utilisé que dans ce sous-programme.
BULK COLLECT ne peut pas être utilisé dans ce type de collection car l'indice doit être donné explicitement pour chaque enregistrement de la collection.

La figure ci-dessous explique schématiquement l'allocation de mémoire de la table imbriquée (sparse). L'espace d'élément de couleur noire désigne l'élément vide dans une collection, c'est-à-dire clairsemé.

Indice (varchar)	PREMIERE	DEUXIÈME	TROISIÈME	QUATRIÈME	CINQUIÈME	SIXIÈME	SEPTIÈME
Valeur (clairsemée)	Qwe		Asd	afg		Asd	Wer

Syntaxe pour l'index par table

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

Dans la syntaxe ci-dessus, type_name est déclaré comme une collection index par table du type « DATA_TYPE ». Le type de données peut être simple ou complexe. La variable subsciprt/index est donnée sous la forme VARCHAR2 avec une taille maximale de 10.

Constructeur et concept d'initialisation dans les collections

Les constructeurs sont la fonction intégrée fournie par l'oracle qui porte le même nom que l'objet ou les collections. Ils sont exécutés en premier chaque fois qu'un objet ou une collection est référencé pour la première fois au cours d'une session. Vous trouverez ci-dessous les détails importants du constructeur dans le contexte de la collection :

Pour les collections, ces constructeurs doivent être appelés explicitement pour l'initialiser.
Les tables Varray et Nested doivent être initialisées via ces constructeurs avant d'être référencées dans le programme.
Le constructeur étend implicitement l'allocation de mémoire pour une collection (sauf Varray), le constructeur peut donc également attribuer les variables aux collections.
L'attribution de valeurs à la collection via des constructeurs ne rendra jamais la collection clairsemée.

Méthodes de collecte

Oracle fournit de nombreuses fonctions pour manipuler et travailler avec les collections. Ces fonctions sont très utiles dans le programme pour déterminer et modifier les différents attributs des collections. Le tableau suivant donne les différentes fonctions et leur description.

Method	Description	SYNTAXE
EXISTE (n)	Cette méthode renverra des résultats booléens. Il renverra « VRAI » si le n^th L'élément existe dans cette collection, sinon il retournera FALSE. Seules les fonctions EXISTS peuvent être utilisées dans une collection non initialisée	.EXISTE(élément_position)
COUNT	Donne le nombre total d'éléments présents dans une collection	.COMPTER
LIMIT	Il renvoie la taille maximale de la collection. Pour Varray, il renverra la taille fixe qui a été définie. Pour les tables imbriquées et les index par table, cela donne NULL	.LIMITE
PREMIERE	Renvoie la valeur de la première variable d'index (indice) des collections	.D'ABORD
DERNIER	Renvoie la valeur de la dernière variable d'index (indice) des collections	.DERNIER
ANTÉRIEUR (n)	Renvoie la variable d'index dans une collection des n^th élément. S'il n'y a pas de valeur d'index précédent, NULL est renvoyé	.PRIOR(n)
SUIVANT (n)	Renvoie la variable d'index réussie dans une collection des n^th élément. S'il n'y a pas de réussite, la valeur d'index NULL est renvoyée	.SUIVANT(n)
ÉTENDRE	Étend un élément d'une collection à la fin	.ÉTENDRE
PROLONGER (n)	Étend n éléments à la fin d'une collection	.EXTEND(n)
PROLONGER (n,i)	Étend n copies du i^th élément à la fin de la collection	.EXTEND(n,i)
TRIM	Supprime un élément de la fin de la collection	.GARNITURE
GARNITURE (n)	Supprime n éléments de la fin de la collection	.TRIM (n)
EFFACER	Supprime tous les éléments de la collection. Rend la collection vide	.SUPPRIMER
SUPPRIMER (n)	Supprime le nième élément de la collection. Si donc^th l'élément est NULL, alors cela ne fera rien	.DELETE(n)
SUPPRIMER (m,n)	Supprime l'élément dans la plage m^th tonne^th dans le recueil	.DELETE(m,n)

Exemple 1 : Type d'enregistrement au niveau du sous-programme

Dans cet exemple, nous allons voir comment remplir la collection en utilisant 'COLLECTE EN VRAC' et comment référencer les données de collecte.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det; guru99_emp_rec emp_det_tbl:= emp_det_tbl(); 
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,’AAA’,25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX’,10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,’ZZZ’,'7500,1000);
COMMIT:
SELECT emp no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line (‘Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line (‘Employee Number: '||guru99_emp_rec(i).emp_no); 
dbms_output.put_line (‘Employee Name: '||guru99_emp_rec(i).emp_name); 
dbms_output.put_line (‘Employee Salary:'|| guru99_emp_rec(i).salary); 
dbms_output.put_line(‘Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Explication du code :

Ligne de code 2-8: Type d'enregistrement 'emp_det' est déclaré avec les colonnes emp_no, emp_name, salaire et manager de type de données NUMBER, VARCHAR2, NUMBER, NUMBER.
Ligne de code 9 : Création de la collection 'emp_det_tbl' de l'élément de type enregistrement 'emp_det'
Ligne de code 10 : Déclaration de la variable 'guru99_emp_rec' comme type 'emp_det_tbl' et initialisée avec un constructeur nul.
Ligne de code 12 à 15 : Insertion des exemples de données dans la table 'emp'.
Ligne de code 16 : Validation de la transaction d'insertion.
Ligne de code 17 : Récupérer les enregistrements de la table 'emp' et remplir la variable de collection en masse à l'aide de la commande « BULK COLLECT ». Désormais, la variable 'guru99_emp_rec' contient tous les enregistrements présents dans la table 'emp'.
Ligne de code 19 à 26 : Définition de la boucle 'FOR' pour imprimer tous les enregistrements de la collection un par un. La méthode de collecte FIRST et LAST est utilisée comme limite inférieure et supérieure du boucle.

Sortie: Comme vous pouvez le voir dans la capture d'écran ci-dessus, lorsque le code ci-dessus est exécuté, vous obtiendrez le résultat suivant

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

Tu pourrais aimer: