Oracle PL/SQL BULK COLLECT: FORALL Παράδειγμα

Τι είναι το BULK COLLECT;

Το BULK COLLECT μειώνει τις εναλλαγές περιβάλλοντος μεταξύ του κινητήρα SQL και PL/SQL και επιτρέπει στον κινητήρα SQL να ανακτήσει τις εγγραφές ταυτόχρονα.

Oracle Η PL/SQL παρέχει τη λειτουργικότητα της ανάκτησης των εγγραφών μαζικά αντί της ανάκτησης μίας προς μία. Αυτή η Μαζική ΣΥΛΛΟΓΗ μπορεί να χρησιμοποιηθεί στη δήλωση 'ΕΠΙΛΟΓΗ' για τη συμπλήρωση των εγγραφών μαζικά ή για τη μαζική ανάκτηση του δρομέα. Εφόσον το BULK COLLECT ανακτά την εγγραφή σε BULK, ο όρος INTO θα πρέπει πάντα να περιέχει μια μεταβλητή τύπου συλλογής. Το κύριο πλεονέκτημα της χρήσης του BULK COLLECT είναι ότι αυξάνει την απόδοση μειώνοντας την αλληλεπίδραση μεταξύ της βάσης δεδομένων και του κινητήρα PL/SQL.

Σύνταξη:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

Στην παραπάνω σύνταξη, το BULK COLLECT χρησιμοποιείται για τη συλλογή των δεδομένων από τις δηλώσεις 'SELECT' και 'FETCH'.

Ρήτρα FORALL

Το FORALL επιτρέπει την εκτέλεση των λειτουργιών DML σε δεδομένα μαζικά. Είναι παρόμοιο με αυτό της δήλωσης βρόχου FOR εκτός από το in βρόχος ΓΙΑ Τα πράγματα συμβαίνουν σε επίπεδο ρεκόρ, ενώ στο FORALL δεν υπάρχει έννοια LOOP. Αντίθετα, όλα τα δεδομένα που υπάρχουν στο δεδομένο εύρος υποβάλλονται σε επεξεργασία ταυτόχρονα.

Σύνταξη:

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

Στην παραπάνω σύνταξη, η δεδομένη λειτουργία DML θα εκτελεστεί για όλα τα δεδομένα που υπάρχουν μεταξύ χαμηλότερου και υψηλότερου εύρους.

Ρήτρα LIMIT

Η έννοια μαζικής συλλογής φορτώνει ολόκληρα τα δεδομένα στη μεταβλητή συλλογής στόχου ως μαζικά, δηλαδή όλα τα δεδομένα θα συμπληρωθούν στη μεταβλητή συλλογής με μία μόνο κίνηση. Αλλά αυτό δεν συνιστάται όταν η συνολική εγγραφή που πρέπει να φορτωθεί είναι πολύ μεγάλη, γιατί πότε PL / SQL προσπαθεί να φορτώσει ολόκληρα τα δεδομένα καταναλώνει περισσότερη μνήμη συνεδρίας. Ως εκ τούτου, είναι πάντα καλό να περιορίζετε το μέγεθος αυτής της λειτουργίας μαζικής συλλογής.

Ωστόσο, αυτό το όριο μεγέθους μπορεί εύκολα να επιτευχθεί με την εισαγωγή της συνθήκης ROWNUM στη δήλωση 'SELECT', ενώ στην περίπτωση του δρομέα αυτό δεν είναι δυνατό.

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

Σύνταξη:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

Στην παραπάνω σύνταξη, η πρόταση ανάκτησης δρομέα χρησιμοποιεί τη δήλωση BULK COLLECT μαζί με την πρόταση LIMIT.

BULK COLLECT Ιδιότητες

Παρόμοια με δρομέας χαρακτηριστικά BULK COLLECT έχει %BULK_ROWCOUNT(n) που επιστρέφει τον αριθμό των σειρών που επηρεάζονται στο n^th Δήλωση DML της δήλωσης FORALL, δηλαδή θα δώσει τον αριθμό των εγγραφών που επηρεάζονται στη δήλωση FORALL για κάθε τιμή από τη μεταβλητή συλλογής. Ο όρος 'n' υποδηλώνει την ακολουθία της τιμής στη συλλογή, για την οποία απαιτείται η καταμέτρηση σειρών.

Παράδειγμα 1: Σε αυτό το παράδειγμα, θα προβάλουμε όλο το όνομα υπαλλήλου από τον πίνακα emp χρησιμοποιώντας το BULK COLLECT και θα αυξήσουμε επίσης τον μισθό όλων των εργαζομένων κατά 5000 χρησιμοποιώντας FORALL.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||c_emp_name);
END LOOP:
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;	
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

Παραγωγή

Employee Fetched:BBB
Employee Fetched:XXX 
Employee Fetched:YYY
Salary Updated

Επεξήγηση κώδικα:

• Κωδικός γραμμή 2: Δήλωση του δρομέα guru99_det για την πρόταση 'SELECT emp_name FROM emp'.
• Κωδικός γραμμή 3: Δήλωση του lv_emp_name_tbl ως τύπου πίνακα του VARCHAR2(50)
• Κωδικός γραμμή 4: Δήλωση του lv_emp_name ως τύπου lv_emp_name_tbl.
• Κωδικός γραμμή 6: Άνοιγμα του δρομέα.
• Κωδικός γραμμή 7: Ανάκτηση του δρομέα χρησιμοποιώντας το BULK COLLECT με το μέγεθος LIMIT ως μεταβλητή 5000 intl lv_emp_name.
• Γραμμή κώδικα 8-11: Ρύθμιση του βρόχου FOR για την εκτύπωση όλης της εγγραφής στη συλλογή lv_emp_name.
• Κωδικός γραμμή 12: Χρησιμοποιώντας το FORALL ενημερώνοντας τον μισθό όλων των εργαζομένων κατά 5000.
• Κωδικός γραμμή 14: Δέσμευση της συναλλαγής.