Collections Oracle PL / SQL: Varrays, imbriquées & Index par tables

Table des matières:

Anonim

Qu'est-ce que la collection?

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

Dans la collection, chaque élément est identifié par un terme appelé «indice». Chaque élément de la collection est associé à un indice unique. Les données de cette collection peuvent être manipulées ou extraites en se référant à cet indice unique.

Les collections sont les choses les plus utiles lorsqu'une grande donnée du même type doit être traitée ou manipulée. Les collections peuvent être remplies et manipulées dans leur intégralité à l'aide de l'option «BULK» d'Oracle.

Dans ce didacticiel, vous apprendrez-

  • Qu'est-ce que la collection?
  • Varrays
  • Tables imbriquées
  • Index par table
  • Constructeur et concept d'initialisation dans les collections
  • Méthodes de collecte

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

  • Index par tables (également appelé tableau associatif)
  • Tables imbriquées
  • Varrays

À tout moment, les données de la collection peuvent être référencées par trois termes Nom de la collection, Indice, Nom de 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 collecte 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 a une valeur numérique. Voici les attributs de Varrays.

  • La taille de la 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 il peut être coupé à partir de la fin.
  • Comme il est toujours de nature dense, il a beaucoup 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 (sauf l'opération EXISTS) sur une collection non initialisée lèvera 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 explique schématiquement l'allocation de mémoire de Varray (dense).

Indice 1 2 3 4 5 6 7
Évaluer Xyz Dfv Sde Cxs Vbc Nhu Qwe

Syntaxe de VARRAY:

TYPE  IS VARRAY () OF ;
  • Dans la syntaxe ci-dessus, nom_type est déclaré comme VARRAY du type 'DATA_TYPE' pour la limite de taille donnée. Le type de données peut être de type 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 de taille supérieure n'est pas fixe, la collection, la mémoire doit être étendue à 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 collection peut être à la fois dense et clairsemé , c'est-à-dire que nous pouvons créer la collection en tant que dense, et nous pouvons également supprimer l'élément de tableau individuel au hasard, 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 de l'élément du tableau peuvent varier.
  • Ils doivent être initialisés avant de les utiliser dans des programmes. Toute opération (sauf l'opération EXISTS) sur la collection non initialisée lèvera 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 explique schématiquement l'allocation de mémoire de la table imbriquée (dense et éparse). L'espace des éléments 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 Sde Cxs Vbc Nhu Qwe
Valeur (clairsemée) Qwe Asd Afg Asd Wer

Syntaxe de la table imbriquée:

TYPE  IS TABLE OF ;
  • Dans la syntaxe ci-dessus, nom_type est déclaré en tant que collection de tables imbriquées du type 'DATA_TYPE'. Le type de données peut être de type simple ou complexe.

Index par table

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

  • L'indice peut être composé d'entiers ou de chaînes. Au moment de la création de la collection, le type d'indice doit être mentionné.
  • Ces collections ne sont pas stockées séquentiellement.
  • 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 tout programme de cette session particulière.
  • Ils donnent plus de flexibilité en termes de maintien de l'indice.
  • Les indices peuvent également être de séquence d'indices négatifs.
  • Ils sont plus appropriés à 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.
  • Ils n'ont pas besoin d'être initialisés 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 expliquera l'allocation de mémoire de la table imbriquée (clairsemée) de manière schématique. L'espace des éléments de couleur noire désigne l'élément vide dans une collection, c'est-à-dire clairsemé.

Indice (varchar) PREMIÈRE DEUXIÈME TROISIÈME QUATRIÈME CINQUIÈME SIXIÈME SEPTIÈME
Valeur (clairsemée) Qwe Asd Afg Asd Wer

Syntaxe pour Index-by-Table

TYPE  IS TABLE OF  INDEX BY VARCHAR2 (10);
  • Dans la syntaxe ci-dessus, nom_type est déclaré comme une collection index par table du type 'DATA_TYPE'. Le type de données peut être de type simple ou complexe. La variable subsciprt / index est de type 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 celui de l'objet ou des collections. Ils sont exécutés en premier chaque fois qu'un objet ou des collections sont référencés pour la première fois dans une session. Voici 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 imbriquées 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), par conséquent le constructeur peut également affecter les variables aux collections.
  • L'affectation de valeurs à la collection via des constructeurs ne rendra jamais la collection fragmenté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 donnera les différentes fonctions et leur description.

Méthode Description SYNTAXE
EXISTE (n) Cette méthode renverra des résultats booléens. Il retournera 'TRUE' si le n ème élément existe dans cette collection, sinon il retournera FALSE. Seules les fonctions EXISTS peuvent être utilisées dans une collection non initialisée .EXISTS (position_élément)
COMPTER Donne le nombre total des éléments présents dans une collection .COUNT
LIMITE Il renvoie la taille maximale de la collection. Pour Varray, il retournera la taille fixe qui a été définie. Pour la table imbriquée et l'index par table, cela donne NULL .LIMIT
PREMIÈRE Renvoie la valeur de la première variable d'index (indice) des collections .FIRST
DERNIER Renvoie la valeur de la dernière variable d'index (indice) des collections .LAST
AVANT (n) Les retours précèdent la variable d'index dans une collection du n ème élément. S'il n'y a pas de valeur d'index précédente, NULL est renvoyé .PRIOR (n)
SUIVANT (n) Renvoie la variable d'index successive dans une collection du n ème élément. S'il n'y a pas de réussite, la valeur d'index NULL est renvoyée .NEXT (n)
ÉTENDRE Prolonge un élément dans une collection à la fin .EXTEND
PROLONGER (n) Étend n éléments à la fin d'une collection .EXTEND (n)
ÉTENDRE (n, i) Étend n copies du i ème élément à la fin de la collection .EXTEND (n, i)
GARNITURE Supprime un élément de la fin de la collection .TRIM
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 .DELETE
SUPPRIMER (n) Supprime le nième élément de la collection. Si le n ième élément est NULL, cela ne fera rien .DELETE (n)
SUPPRIMER (m, n) Supprime l'élément dans la gamme m e à n e dans la collection .DELETE (m, n)

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

Dans cet exemple, nous allons voir comment remplir la collection à l'aide de 'BULK COLLECT' et comment référencer les données de la collection.

DECLARETYPE 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();BEGININSERT 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_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_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 : Le 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éclarer 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ération des enregistrements de la table 'emp' et remplissage de la variable de collection en bloc à l'aide de la commande "BULK COLLECT". Maintenant, 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' en utilisant 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 de la boucle.

Sortie : comme vous pouvez le voir dans la capture d'écran ci-dessus lorsque le code ci-dessus est exécuté, vous obtiendrez la sortie suivante

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------