Oracle Εκμάθηση PL/SQL Dynamic SQL: Execute Immediate & DBMS_SQL

Τι είναι το Dynamic SQL;

Δυναμικός SQL είναι μια μεθοδολογία προγραμματισμού για τη δημιουργία και εκτέλεση εντολών κατά το χρόνο εκτέλεσης. Χρησιμοποιείται κυρίως για τη σύνταξη των γενικής χρήσης και ευέλικτων προγραμμάτων όπου οι εντολές SQL θα δημιουργηθούν και θα εκτελεστούν κατά το χρόνο εκτέλεσης με βάση την απαίτηση.

Τρόποι εγγραφής δυναμικής SQL

Το PL/SQL παρέχει δύο τρόπους εγγραφής δυναμικής SQL

  1. NDS – Native Dynamic SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Εκτέλεση Άμεση

Το Native Dynamic SQL είναι ο ευκολότερος τρόπος για να γράψετε δυναμική SQL. Χρησιμοποιεί την εντολή 'EXECUTE IMMEDIATE' για να δημιουργήσει και να εκτελέσει την SQL κατά το χρόνο εκτέλεσης. Αλλά για να χρησιμοποιηθεί αυτός ο τρόπος, ο τύπος δεδομένων και ο αριθμός των μεταβλητών που θα χρησιμοποιηθούν σε ένα χρόνο εκτέλεσης πρέπει να είναι γνωστοί από πριν. Παρέχει επίσης καλύτερη απόδοση και λιγότερη πολυπλοκότητα σε σύγκριση με το DBMS_SQL.

Σύνταξη

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Η παραπάνω σύνταξη δείχνει την εντολή EXECUTE IMMEDIATE.
  • Η ρήτρα INTO είναι προαιρετική και χρησιμοποιείται μόνο εάν η δυναμική SQL περιέχει μια πρόταση επιλογής που ανακτά τιμές. Ο τύπος μεταβλητής πρέπει να ταιριάζει με τον τύπο μεταβλητής της δήλωσης Select.
  • Η ρήτρα USING είναι προαιρετική και χρησιμοποιείται μόνο εάν η δυναμική SQL περιέχει οποιαδήποτε μεταβλητή bind.

Παράδειγμα 1: Σε αυτό το παράδειγμα, θα ανακτήσουμε τα δεδομένα από τον πίνακα emp για το emp_no '1001' χρησιμοποιώντας τη δήλωση NDS.

NDS - Εκτέλεση Άμεση

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

Παραγωγή

Employee Name : XXX 
Employee Number: 1001 
Salary: 15000 
Manager ED: 1000

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

  • Κωδικός γραμμή 2-6: Δήλωση μεταβλητών.
  • Κωδικός γραμμή 8: Πλαίσιο της SQL κατά το χρόνο εκτέλεσης. Η SQL περιέχει τη μεταβλητή bind όπου η συνθήκη ':empno'.
  • Κωδικός γραμμή 9: Εκτέλεση του πλαισιωμένου κειμένου SQL (το οποίο γίνεται στη γραμμή κώδικα 8) χρησιμοποιώντας την εντολή NDS 'EXECUTE IMMEDIATE'
  • Οι μεταβλητές στον όρο "INTO" (lv_emp_name, ln_emp_no, ln_salary, ln_manager) χρησιμοποιούνται για τη διατήρηση των τιμών που ανακτήθηκαν από το ερώτημα SQL (emp_name, emp_no, μισθός, manager)
  • Ο όρος 'USING' δίνει τις τιμές στη μεταβλητή bind στο ερώτημα SQL (:emp_no).
  • Κωδικός γραμμή 10-13: Εμφάνιση των τιμών που ανακτήθηκαν.

DBMS_SQL για Dynamic SQL

Το PL/SQL παρέχει το πακέτο DBMS_SQL που σας επιτρέπει να εργάζεστε με δυναμική SQL. Η διαδικασία δημιουργίας και εκτέλεσης της δυναμικής SQL περιέχει την ακόλουθη διαδικασία.

  • ΑΝΟΙΞΤΕ ΔΡΟΜΕΑ: Η δυναμική SQL θα εκτελεστεί με τον ίδιο τρόπο όπως το a δρομέας. Για να εκτελέσουμε λοιπόν την πρόταση SQL, πρέπει να ανοίξουμε τον κέρσορα.
  • ΑΝΑΛΥΣΗ SQL: Το επόμενο βήμα είναι η ανάλυση της δυναμικής SQL. Αυτή η διαδικασία θα ελέγξει απλώς τη σύνταξη και θα διατηρήσει το ερώτημα έτοιμο για εκτέλεση.
  • ΔΕΣΜΕΥΣΕΙΣ ΜΕΤΑΒΛΗΤΕΣ Τιμές: Το επόμενο βήμα είναι να εκχωρήσετε τις τιμές για τις μεταβλητές δέσμευσης εάν υπάρχουν.
  • ΟΡΙΣΜΟΣ ΣΤΗΛΗ: Το επόμενο βήμα είναι να ορίσετε τη στήλη χρησιμοποιώντας τις σχετικές θέσεις τους στη δήλωση επιλογής.
  • ΕΚΤΕΛΕΣΗ: Το επόμενο βήμα είναι να εκτελέσετε το αναλυμένο ερώτημα.
  • ΛΗΨΗ ΑΞΙΩΝ: Το επόμενο βήμα είναι η ανάκτηση των εκτελεσμένων τιμών.
  • ΚΛΕΙΣΙΜΟ ΔΡΟΜΕΑ: Μόλις ληφθούν τα αποτελέσματα, ο δρομέας πρέπει να κλείσει.

Παράδειγμα 1: Σε αυτό το παράδειγμα, πρόκειται να ανακτήσουμε τα δεδομένα από τον πίνακα emp για το emp_no '1001' χρησιμοποιώντας την πρόταση DBMS_SQL.

DBMS_SQL για Dynamic SQL

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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);

DBMS_SQL για Dynamic SQL

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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

Παραγωγή

Employee Name:XXX 
Employee Number:1001 
Salary:15000 
Manager ID:1000

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

  • Κωδικός γραμμή 1-9: Δήλωση μεταβλητής.
  • Κωδικός γραμμή 10: Πλαίσιο της δήλωσης SQL.
  • Κωδικός γραμμή 11: Άνοιγμα του δρομέα χρησιμοποιώντας το DBMS_SQL.OPEN_CURSOR. Θα επιστρέψει το αναγνωριστικό του δρομέα που είναι ανοιχτό.
  • Κωδικός γραμμή 12: Αφού ανοίξει ο δρομέας, γίνεται ανάλυση της SQL.
  • Κωδικός γραμμή 13: Η μεταβλητή δέσμευσης '1001' εκχωρεί στο αναγνωριστικό του δρομέα αντί ':empno'.
  • Κωδικός γραμμή 14-17: Ορισμός του ονόματος της στήλης με βάση τη σχετική θέση τους στη δήλωση SQL. Στην περίπτωσή μας, η σχετική θέση είναι (1) emp_name, (2) emp_no (3) μισθός (4) διευθυντής. Με βάση λοιπόν αυτή τη θέση ορίζουμε τη μεταβλητή στόχο.
  • Κωδικός γραμμή 18: Εκτέλεση του ερωτήματος χρησιμοποιώντας το DBMS_SQL.EXECUTE. Επιστρέφει τον αριθμό των εγγραφών που υποβλήθηκαν σε επεξεργασία.
  • Κωδικός γραμμή 19-33: Ανάκτηση των εγγραφών με χρήση βρόχου και εμφάνιση του ίδιου.
  • Κωδικός γραμμή 20: Το DBMS_SQL.FETCH_ROWS θα ανακτήσει μία εγγραφή από τις σειρές που υποβάλλονται σε επεξεργασία. Μπορεί να κληθεί επανειλημμένα για να ληφθούν όλες οι σειρές. Εάν δεν μπορεί να ανακτήσει σειρές, θα επιστρέψει 0, βγαίνοντας έτσι από τον βρόχο.

Σύνοψη

Σε αυτή την ενότητα, έχουμε συζητήσει τη δυναμική SQL και τους τρόπους εκτέλεσης της DYNAMIC SQL. Έχουμε δει επίσης τα διαφορετικά βήματα στην εκτέλεση της δυναμικής SQL και με τους δύο τρόπους. Έχουμε επίσης δει τα παραδείγματα στα οποία το ίδιο σενάριο αντιμετωπίζεται τόσο με τρόπους NDS όσο και με DBMS_SQL για εκτέλεση εκτέλεσης κατά το χρόνο εκτέλεσης.