Oracle Εκμάθηση PL/SQL Dynamic SQL: Execute Immediate & DBMS_SQL
Φιόνα Μπράουν
⚡ Έξυπνη Σύνοψη
Δυναμική SQL σε Oracle Η PL/SQL δημιουργεί και εκτελεί εντολές κατά τον χρόνο εκτέλεσης, προσαρμόζοντας τα ερωτήματα στις μεταβαλλόμενες απαιτήσεις μέσω δύο προσεγγίσεων: της εγγενούς δυναμικής SQL με EXECUTE IMMEDIATE και OPEN-FOR, και του ευέλικτου πακέτου DBMS_SQL για πολύπλοκες περιπτώσεις.
Τι είναι το Dynamic SQL;
Δυναμικός SQL είναι μια μεθοδολογία προγραμματισμού για τη δημιουργία και την εκτέλεση εντολών κατά τον χρόνο εκτέλεσης. Χρησιμοποιείται κυρίως για τη σύνταξη προγραμμάτων γενικής χρήσης και ευέλικτων προγραμμάτων όπου οι εντολές SQL δημιουργούνται και εκτελούνται κατά τον χρόνο εκτέλεσης με βάση την απαίτηση, για παράδειγμα όταν τα ονόματα πινάκων, οι λίστες στηλών ή οι συνθήκες WHERE δεν είναι γνωστά μέχρι να εκτελεστεί το πρόγραμμα.
Τρόποι Συγγραφής Δυναμικής SQL
Η PL/SQL παρέχει δύο τρόπους για να γράψετε δυναμική SQL:
- NDS – Native Dynamic SQL (οι εντολές EXECUTE IMMEDIATE και OPEN-FOR)
- DBMS_SQL (παρεχόμενη συσκευασία)
Ο γενικός κανόνας είναι απλός: εάν ο αριθμός και οι τύποι δεδομένων των μεταβλητών εισόδου και εξόδου είναι γνωστοί κατά τον χρόνο μεταγλώττισης, χρησιμοποιήστε το Native Dynamic SQL επειδή είναι ταχύτερο και απαιτεί λιγότερο κώδικα. Όταν αυτές οι πληροφορίες είναι γνωστές μόνο κατά τον χρόνο εκτέλεσης, χρησιμοποιήστε το πακέτο DBMS_SQL.
NDS (Native Dynamic SQL) – Εκτέλεση Άμεση
Η εγγενής δυναμική SQL είναι ο ευκολότερος τρόπος για να γράψετε δυναμική SQL. Χρησιμοποιεί την εντολή EXECUTE IMMEDIATE για να δημιουργήσει και να εκτελέσει την SQL κατά τον χρόνο εκτέλεσης. Για να χρησιμοποιήσετε αυτήν την προσέγγιση, ο τύπος δεδομένων και ο αριθμός των μεταβλητών που χρησιμοποιούνται κατά τον χρόνο εκτέλεσης πρέπει να είναι γνωστοί εκ των προτέρων. Προσφέρει επίσης καλύτερη απόδοση και χαμηλότερη πολυπλοκότητα σε σύγκριση με το DBMS_SQL.
Σύνταξη
EXECUTE IMMEDIATE dynamic_sql_string [INTO {variable[, variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument[, ...]] [RETURNING INTO bind_argument[, ...]];
- δυναμική_sql_συμβολοσειρά: Μια έκφραση συμβολοσειράς (VARCHAR2 ή CHAR, όχι NVARCHAR2/NCHAR) που περιέχει μία μόνο πρόταση SQL ή ένα μπλοκ PL/SQL.
- Πρόταση INTO: Προαιρετικό. Χρησιμοποιείται μόνο όταν η δυναμική SQL είναι μια SELECT μίας γραμμής. Καταγράφει τις τιμές που επιστρέφονται σε μεταβλητές ή σε μια εγγραφή. Κάθε επιλεγμένη στήλη χρειάζεται μια μεταβλητή συμβατή με τύπο.
- ΧΡΗΣΗ όρου: Προαιρετικό. Παρέχει μεταβλητές σύνδεσης. Η προεπιλεγμένη λειτουργία είναι IN. Οι OUT και IN OUT χρησιμοποιούνται για την επιστροφή τιμών.
- ΕΠΙΣΤΡΟΦΗ ΣΤΗ ρήτρα: Χρησιμοποιείται με εντολές DML που φέρουν έναν όρο RETURNING, για την καταγραφή τιμών επηρεαζόμενων γραμμών σε ορίσματα σύνδεσης.
Παράδειγμα 1: Σε αυτό το παράδειγμα, ανακτούμε τα δεδομένα από τον πίνακα emp για το emp_no '1001' χρησιμοποιώντας μια πρόταση NDS με μια μεταβλητή bind.
DECLARE lv_sql VARCHAR2(500); lv_emp_name VARCHAR2(50); ln_emp_no NUMBER; ln_salary NUMBER; ln_manager NUMBER; BEGIN lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno'; EXECUTE IMMEDIATE lv_sql INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager USING 1001; DBMS_OUTPUT.PUT_LINE('Employee Name: ' || lv_emp_name); DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no); DBMS_OUTPUT.PUT_LINE('Salary: ' || ln_salary); DBMS_OUTPUT.PUT_LINE('Manager ID: ' || ln_manager); END; /
Παραγωγή
Employee Name : XXX Employee Number: 1001 Salary : 15000 Manager ID : 1000
Code Επεξήγηση:
- Γραμμές 2-6: Δήλωση των μεταβλητών.
- Γραμμή 8: Πλαίσιο της SQL κατά τον χρόνο εκτέλεσης. Η SQL περιέχει τη μεταβλητή σύνδεσης ':empno' στη συνθήκη WHERE.
- Γραμμές 9-11: Εκτέλεση της πλαισιωμένης SQL με EXECUTE IMMEDIATE. Οι μεταβλητές της πρότασης INTO περιέχουν τις τιμές που ανακτώνται και η πρόταση USING παρέχει την τιμή για τη μεταβλητή bind :empno.
- Γραμμές 12-15: Εμφάνιση των τιμών που ανακτήθηκαν.
Χρήση Δυναμικής SQL για DDL
Η στατική PL/SQL δεν μπορεί να εκτελέσει απευθείας DDL όπως CREATE, ALTER ή DROP. Η εντολή EXECUTE IMMEDIATE λύνει αυτό το πρόβλημα δημιουργώντας την πρόταση ως συμβολοσειρά, η οποία είναι επίσης χρήσιμη όταν παρέχεται ένα όνομα αντικειμένου κατά τον χρόνο εκτέλεσης:
DECLARE l_table_name VARCHAR2(30) := 'my_table'; l_sql_stmt VARCHAR2(200); BEGIN l_sql_stmt := 'CREATE TABLE ' || l_table_name || ' (id NUMBER, name VARCHAR2(30))'; EXECUTE IMMEDIATE l_sql_stmt; END; /
Τα ονόματα αντικειμένων (πίνακας, στήλη, σχήμα) δεν μπορούν να διαβιβαστούν ως μεταβλητές σύνδεσης, επομένως πρέπει να συνενωθούν στη συμβολοσειρά. Να επικυρώνετε πάντα τέτοια δεδομένα εισόδου, για παράδειγμα με το DBMS_ASSERT.SIMPLE_SQL_NAME, για να αποφύγετε την εισαγωγή SQL.
DBMS_SQL για Dynamic SQL
Η PL/SQL παρέχει το πακέτο DBMS_SQL για εργασία με δυναμική SQL όταν η δομή της πρότασης δεν είναι γνωστή μέχρι τον χρόνο εκτέλεσης. Η διαδικασία δημιουργίας και εκτέλεσης της δυναμικής SQL περιλαμβάνει τα ακόλουθα βήματα:
- ΑΝΟΙΓΜΑ ΔΡΟΜΕΑ: Η δυναμική SQL εκτελείται ως εξής: δρομέαςΓια να εκτελέσουμε την πρόταση SQL, πρέπει πρώτα να ανοίξουμε τον κέρσορα.
- ΑΝΑΛΥΣΗ SQL: Ανάλυση της δυναμικής SQL. Αυτό ελέγχει τη σύνταξη και διατηρεί το ερώτημα έτοιμο για εκτέλεση.
- Τιμές μεταβλητής σύνδεσης: Αντιστοιχίστε τις τιμές για τις μεταβλητές σύνδεσης, εάν υπάρχουν.
- ΟΡΙΣΜΟΣ ΣΤΗΛΗΣ: Ορίστε κάθε στήλη χρησιμοποιώντας τη σχετική της θέση στην πρόταση select.
- ΕΚΤΕΛΩ: Εκτελέστε το αναλυμένο ερώτημα.
- ΤΙΜΕΣ ΑΝΑΚΤΗΣΗΣ: Ανάκτηση των εκτελεσμένων τιμών.
- ΚΛΕΙΣΙΜΟ ΔΡΟΜΕΑ: Μόλις ληφθούν τα αποτελέσματα, κλείστε τον κέρσορα.
Παράδειγμα 1: Σε αυτό το παράδειγμα, ανακτούμε τα δεδομένα από τον πίνακα emp για το emp_no '1001' χρησιμοποιώντας μια πρόταση DBMS_SQL. Το μπλοκ EXCEPTION κλείνει τον κέρσορα ακόμα και αν παρουσιαστεί σφάλμα.
DECLARE lv_sql VARCHAR2(500); lv_emp_name VARCHAR2(50); ln_emp_no NUMBER; ln_salary NUMBER; ln_manager NUMBER; ln_cursor_id NUMBER; ln_rows_processed NUMBER; BEGIN lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno'; ln_cursor_id := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001); DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50); DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no); DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary); DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager); ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id); LOOP IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN EXIT; ELSE DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name); DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no); DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary); DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager); DBMS_OUTPUT.PUT_LINE('Employee Name: ' || lv_emp_name); DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no); DBMS_OUTPUT.PUT_LINE('Salary: ' || ln_salary); DBMS_OUTPUT.PUT_LINE('Manager ID: ' || ln_manager); END IF; END LOOP; DBMS_SQL.CLOSE_CURSOR(ln_cursor_id); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(ln_cursor_id); END; /
Παραγωγή
Employee Name : XXX Employee Number: 1001 Salary : 15000 Manager ID : 1000
Code Επεξήγηση:
- Γραμμές 1-8: Δήλωση μεταβλητής.
- Γραμμή 10: Πλαίσιο της πρότασης SQL.
- Γραμμή 11: Άνοιγμα του δρομέα χρησιμοποιώντας την εντολή DBMS_SQL.OPEN_CURSOR, η οποία επιστρέφει το αναγνωριστικό του ανοιχτού δρομέα.
- Γραμμή 12: Αφού ανοίξει ο κέρσορας, η SQL αναλύεται.
- Γραμμή 13: Η τιμή σύνδεσης '1001' αντιστοιχίζεται στη θέση του ':empno'.
- Γραμμές 14-17: Ορισμός των στηλών με βάση τη σχετική τους θέση: (1) emp_name, (2) emp_no, (3) μισθός, (4) διευθυντής.
- Γραμμή 18: Εκτέλεση του ερωτήματος με την εντολή DBMS_SQL.EXECUTE, η οποία επιστρέφει τον αριθμό των εγγραφών που έχουν υποστεί επεξεργασία.
- Γραμμές 19-32: Ανάκτηση των εγγραφών σε βρόχο. Η συνάρτηση FETCH_ROWS επιστρέφει 0 όταν δεν υπάρχουν γραμμές, γεγονός που τερματίζει τον βρόχο.
- Μπλοκ ΕΞΑΙΡΕΣΗΣ: Εξασφαλίζει ότι ο κέρσορας είναι κλειστός, ώστε οι ανοιχτοί κέρσορες να μην παρουσιάζουν διαρροή σε περίπτωση σφάλματος.
NDS vs DBMS_SQL: Πότε να χρησιμοποιήσετε ποιο
Και οι δύο προσεγγίσεις εκτελούν SQL κατά το χρόνο εκτέλεσης, αλλά ταιριάζουν σε διαφορετικές περιπτώσεις:
- Χρήση εγγενούς δυναμικής SQL (ΕΚΤΕΛΕΣΗ ΑΜΕΣΑ / ΑΝΟΙΓΜΑ-ΓΙΑ) όταν ο αριθμός και οι τύποι δεδομένων των εισόδων και των εξόδων είναι γνωστοί κατά το χρόνο μεταγλώττισης. Είναι ταχύτερο, πιο εύκολο στην ανάγνωση και χρειάζεται λιγότερο κώδικα.
- Χρήση DBMS_SQL όταν η δομή είναι άγνωστη μέχρι το χρόνο εκτέλεσης, για παράδειγμα ένα ερώτημα του οποίου ο αριθμός των επιλεγμένων στηλών ή μεταβλητών σύνδεσης ποικίλλει, γνωστό ως δυναμική SQL μεθόδου-4, ή μια πρόταση πολύ μεγάλη για να χωρέσει σε μία μόνο μεταβλητή VARCHAR2 32K.
