Oracle Συλλογές PL/SQL: Varrays, Nested & Index by Tables

Τι είναι μια Συλλογή;

Μια συλλογή είναι μια ταξινομημένη ομάδα στοιχείων ενός συγκεκριμένου τύπου δεδομένων. Μπορεί να είναι μια συλλογή ενός απλού τύπου δεδομένων ή ενός σύνθετου τύπου δεδομένων, όπως τύποι που ορίζονται από τον χρήστη ή τύποι εγγραφών.

Σε μια συλλογή, κάθε στοιχείο προσδιορίζεται από έναν όρο που ονομάζεται "υπογεγραμμένη." Σε κάθε στοιχείο αντιστοιχίζεται ένας μοναδικός δείκτης και τα δεδομένα μπορούν να χειριστούν ή να ανακτηθούν αναφερόμενα σε αυτόν τον μοναδικό δείκτη.

Οι συλλογές είναι πιο χρήσιμες όταν χρειάζεται επεξεργασία ή χειρισμός μεγάλης ποσότητας δεδομένων του ίδιου τύπου. Οι συλλογές μπορούν να συμπληρωθούν και να χειριστούν ως σύνολο χρησιμοποιώντας την επιλογή «BULK» στο Oracle.

Οι συλλογές ταξινομούνται με βάση τη δομή, το δείκτη και τον χώρο αποθήκευσης, όπως φαίνεται παρακάτω:

• Πίνακες με ευρετήριο (γνωστοί και ως συσχετιστικοί πίνακες)
• Ένθετα τραπέζια
• Varrays

Σε οποιοδήποτε σημείο, τα δεδομένα σε μια συλλογή μπορούν να αναφερθούν με τρεις όρους: όνομα συλλογής, δείκτης και όνομα πεδίου ή στήλης, ως " ( ). Θα μάθετε για αυτές τις κατηγορίες συλλογών στις παρακάτω ενότητες.

Τύποι Συλλογών με μια Ματιά

Οι τρεις τύποι συλλογών έχουν διαφορετικούς συμβιβασμούς. Ο παρακάτω πίνακας τους παρουσιάζει δίπλα-δίπλα, προτού εξεταστεί λεπτομερώς ο καθένας.

Varrays

Ένα Varray είναι μια συλλογή στην οποία το μέγεθος του πίνακα είναι σταθερό και δεν μπορεί να ξεπεραστεί. Ο δείκτης ενός Varray είναι μια αριθμητική τιμή. Τα χαρακτηριστικά των Varray είναι:

• Το ανώτατο όριο μεγέθους είναι σταθερό.
• Συμπληρώνεται διαδοχικά ξεκινώντας από τον δείκτη '1'.
• Αυτός ο τύπος συλλογής είναι πάντα πυκνός. Δεν μπορούμε να διαγράψουμε μεμονωμένα στοιχεία πίνακα. Ένα Varray μπορεί να διαγραφεί ολόκληρο ή να περικοπεί από το τέλος.
• Επειδή είναι πάντα πυκνό, έχει πολύ μικρή ευελιξία.
• Είναι πιο κατάλληλο όταν το μέγεθος του πίνακα είναι γνωστό και παρόμοιες δραστηριότητες εκτελούνται σε όλα τα στοιχεία.
• Ο δείκτης και ο αριθμός των αριθμών της συλλογής παραμένουν πάντα σταθεροί.
• Πρέπει να αρχικοποιηθεί πριν από τη χρήση. Οποιαδήποτε λειτουργία εκτός από την EXISTS σε μια μη αρχικοποιημένη συλλογή εμφανίζει σφάλμα.
• Μπορεί να δημιουργηθεί ως αντικείμενο βάσης δεδομένων ορατό σε όλη τη βάση δεδομένων ή μέσα σε ένα υποπρόγραμμα για χρήση μόνο εκεί.

Το παρακάτω σχήμα εξηγεί την κατανομή μνήμης ενός Varray (πυκνού).

Σύνταξη για VARRAY:

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

• Στην παραπάνω σύνταξη, το type_name δηλώνεται ως VARRAY του τύπου 'DATA_TYPE' για το δεδομένο όριο μεγέθους. Ο τύπος δεδομένων μπορεί να είναι απλός ή σύνθετος.

Ένθετοι πίνακες

Ένας ένθετος πίνακας είναι μια συλλογή στην οποία το μέγεθος του πίνακα δεν είναι σταθερό. Έχει έναν αριθμητικό τύπο δείκτη. Περισσότερα σχετικά με τον τύπο ένθετου πίνακα:

• Ο ένθετος πίνακας δεν έχει ανώτατο όριο μεγέθους.
• Επειδή το ανώτατο όριο δεν είναι σταθερό, η μνήμη πρέπει να επεκτείνεται κάθε φορά πριν από τη χρήση, χρησιμοποιώντας τη λέξη-κλειδί 'EXTEND'.
• Συμπληρώνεται διαδοχικά ξεκινώντας από τον δείκτη '1'.
• Αυτός ο τύπος συλλογής μπορεί να είναι και οι δύο πυκνό και αραιόμπορούμε να το δημιουργήσουμε ως πυκνό και επίσης να διαγράψουμε τυχαία μεμονωμένα στοιχεία, γεγονός που το καθιστά αραιό.
• Παρέχει μεγαλύτερη ευελιξία για τη διαγραφή στοιχείων πίνακα.
• Αποθηκεύεται σε έναν πίνακα βάσης δεδομένων που δημιουργείται από το σύστημα και μπορεί να χρησιμοποιηθεί σε ένα ερώτημα επιλογής για την ανάκτηση τιμών.
• Ο δείκτης και ο αριθμός μπορεί να διαφέρουν.
• Πρέπει να αρχικοποιηθεί πριν από τη χρήση. Οποιαδήποτε λειτουργία εκτός από την EXISTS σε μια μη αρχικοποιημένη συλλογή εμφανίζει σφάλμα.
• Μπορεί να δημιουργηθεί ως αντικείμενο βάσης δεδομένων ορατό σε όλη τη βάση δεδομένων ή μέσα σε ένα υποπρόγραμμα για χρήση μόνο εκεί.

Το παρακάτω σχήμα εξηγεί την κατανομή μνήμης ενός ένθετου πίνακα (πυκνού και αραιού). Ένας κενός χώρος στοιχείων υποδηλώνει ένα αραιό στοιχείο.

Σύνταξη για ένθετο πίνακα:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• Στην παραπάνω σύνταξη, το type_name δηλώνεται ως μια ένθετη συλλογή πινάκων του τύπου 'DATA_TYPE'. Ο τύπος δεδομένων μπορεί να είναι απλός ή σύνθετος.

Πίνακας ευρετηρίου ανά

Ένας πίνακας με βάση το ευρετήριο είναι μια συλλογή στην οποία το μέγεθος του πίνακα δεν είναι σταθερό. Σε αντίθεση με άλλους τύπους συλλογών, ο δείκτης ενός πίνακα με βάση το ευρετήριο μπορεί να οριστεί από τον χρήστη. Τα χαρακτηριστικά ενός πίνακα με βάση το ευρετήριο είναι:

• Ο δείκτης μπορεί να είναι ακέραιος αριθμός ή συμβολοσειρά. Ο τύπος του δείκτη θα πρέπει να αναφέρεται κατά τη δημιουργία της συλλογής.
• Αυτές οι συλλογές δεν αποθηκεύονται διαδοχικά.
• Είναι πάντα αραιοί στη φύση τους.
• Το μέγεθος του πίνακα δεν είναι σταθερό.
• Δεν μπορούν να αποθηκευτούν σε μια στήλη βάσης δεδομένων. Δημιουργούνται και χρησιμοποιούνται εντός μιας συγκεκριμένης συνεδρίας.
• Δίνουν μεγαλύτερη ευελιξία στη διατήρηση του δείκτη.
• Οι δείκτες μπορούν να είναι αρνητική ακολουθία.
• Είναι πιο κατάλληλα για σχετικά μικρότερες τιμές συλλογής που χρησιμοποιούνται στο ίδιο υποπρόγραμμα.
• Δεν χρειάζεται να αρχικοποιηθούν πριν από τη χρήση.
• Δεν μπορούν να δημιουργηθούν ως αντικείμενο βάσης δεδομένων· δημιουργούνται μόνο μέσα σε ένα υποπρόγραμμα.
• Η ΜΑΖΙΚΗ ΣΥΛΛΟΓΗ δεν μπορεί να χρησιμοποιηθεί με αυτόν τον τύπο συλλογής, επειδή ο δείκτης πρέπει να δοθεί ρητά για κάθε εγγραφή.

Το παρακάτω σχήμα εξηγεί την κατανομή μνήμης ενός πίνακα με ευρετήριο (sparse). Ένας κενός χώρος στοιχείων υποδηλώνει ένα αραιό στοιχείο.

Σύνταξη για τον Πίνακα Ευρετηρίου ανά:

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

• Στην παραπάνω σύνταξη, το type_name δηλώνεται ως συλλογή πινάκων με βάση τον δείκτη (index-by) του τύπου 'DATA_TYPE'. Η μεταβλητή δείκτη δίνεται ως τύπος VARCHAR2 με μέγιστο μέγεθος 10.

Κατασκευαστής και ιδέα αρχικοποίησης σε συλλογές

Οι κατασκευαστές είναι ενσωματωμένες συναρτήσεις που παρέχονται από Oracle που έχουν το ίδιο όνομα με το αντικείμενο ή τη συλλογή. Εκτελούνται πρώτα κάθε φορά που ένα αντικείμενο ή μια συλλογή αναφέρεται για πρώτη φορά σε μια συνεδρία. Σημαντικές λεπτομέρειες ενός κατασκευαστή στο περιβάλλον της συλλογής:

• Για συλλογές, αυτοί οι κατασκευαστές πρέπει να κληθούν ρητά για την αρχικοποίηση της συλλογής.
• Τόσο οι πίνακες Varray όσο και οι ένθετοι πίνακες πρέπει να αρχικοποιηθούν μέσω αυτών των κατασκευαστών πριν αναφερθούν στο πρόγραμμα.
• Ένας κατασκευαστής επεκτείνει έμμεσα την κατανομή μνήμης για μια συλλογή (εκτός από τη Varray), επομένως μπορεί επίσης να αντιστοιχίσει μεταβλητές στη συλλογή.
• Η ανάθεση τιμών μέσω κατασκευαστών δεν καθιστά ποτέ τη συλλογή αραιή.

Μέθοδοι συλλογής

Oracle παρέχει πολλές συναρτήσεις για τον χειρισμό και την εργασία με συλλογές. Αυτές οι συναρτήσεις καθορίζουν και τροποποιούν τα διαφορετικά χαρακτηριστικά μιας συλλογής. Ο παρακάτω πίνακας δίνει τις διαφορετικές συναρτήσεις και τις περιγραφές τους.

Παράδειγμα 1: Τύπος εγγραφής σε επίπεδο υποπρογράμματος

Σε αυτό το παράδειγμα, βλέπουμε πώς να συμπληρώσουμε τη συλλογή χρησιμοποιώντας το 'ΧΥΜΑ ΣΥΛΛΟΓΗκαι πώς να ανατρέχετε στα δεδομένα της συλλογής.

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

Code εξήγηση

• Code γραμμή 2-8: Τύπος εγγραφής Το 'emp_det' δηλώνεται με τις στήλες emp_no, emp_name, manager και salary τύπου δεδομένων NUMBER, VARCHAR2, NUMBER και NUMBER.
• Code γραμμή 9: Δημιουργία της συλλογής 'emp_det_tbl' με στοιχείο τύπου εγγραφής 'emp_det'.
• Code γραμμή 10: Δηλώνοντας τη μεταβλητή 'guru99_emp_rec' ως τύπο 'emp_det_tbl' και αρχικοποιώντας την με έναν κατασκευαστή null.
• Code γραμμή 12-15: Εισαγωγή των δεδομένων δείγματος στον πίνακα 'emp'.
• Code γραμμή 16: Πραγματοποίηση της συναλλαγής εισαγωγής.
• Code γραμμή 17: Ανάκτηση των εγγραφών από τον πίνακα 'emp' και συμπλήρωση της μεταβλητής συλλογής μαζικά χρησιμοποιώντας την εντολή "BULK COLLECT". Η μεταβλητή 'guru99_emp_rec' περιέχει πλέον όλες τις εγγραφές που υπάρχουν στον πίνακα 'emp'.
• Code γραμμή 19-26: Ρύθμιση του βρόχου 'FOR' για εκτύπωση όλων των εγγραφών στη συλλογή μία προς μία. Οι μέθοδοι συλλογής FIRST και LAST χρησιμοποιούνται ως το κατώτερο και ανώτερο όριο του βρόχος.

Παραγωγή: Όταν εκτελεστεί ο παραπάνω κώδικας, θα λάβετε την ακόλουθη έξοδο.

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

Συχνές Ερωτήσεις