Oracle Tutorial SQL Dinamis PL/SQL: Jalankan Segera & DBMS_SQL
Apa itu SQL Dinamis?
Dinamis SQL adalah metodologi pemrograman untuk menghasilkan dan menjalankan pernyataan pada saat run-time. Hal ini terutama digunakan untuk menulis program tujuan umum dan fleksibel di mana pernyataan SQL akan dibuat dan dieksekusi pada saat run-time berdasarkan kebutuhan.
Cara menulis SQL dinamis
PL/SQL menyediakan dua cara untuk menulis SQL dinamis
- NDS – SQL Dinamis Asli
- DBMS_SQL
NDS (Native Dynamic SQL) – Jalankan Segera
Native Dynamic SQL adalah cara yang lebih mudah untuk menulis SQL dinamis. Native Dynamic SQL menggunakan perintah 'EXECUTE IMMEDIATE' untuk membuat dan mengeksekusi SQL saat run-time. Namun, untuk menggunakan cara ini, tipe data dan jumlah variabel yang akan digunakan saat run-time perlu diketahui sebelumnya. Native Dynamic SQL juga memberikan kinerja yang lebih baik dan kompleksitas yang lebih rendah jika dibandingkan dengan DBMS_SQL.
Sintaksis
EXECUTE IMMEDIATE(<SQL>) [INTO<variable>] [USING <bind_variable_value>]
- Sintaks di atas menunjukkan perintah EXECUTE IMMEDIATE.
- Klausul INTO bersifat opsional dan hanya digunakan jika SQL dinamis berisi pernyataan pilih yang mengambil nilai. Tipe variabel harus sesuai dengan tipe variabel dari pernyataan pilih.
- Klausul USING bersifat opsional dan hanya digunakan jika SQL dinamis berisi variabel pengikat apa pun.
Contoh 1: Dalam contoh ini, kita akan mengambil data dari tabel emp untuk emp_no '1001' menggunakan pernyataan 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; /
Keluaran
Employee Name : XXX Employee Number: 1001 Salary: 15000 Manager ED: 1000
Penjelasan Kode:
- Baris kode 2-6: Mendeklarasikan variabel.
- Baris kode 8: Membingkai SQL pada saat run-time. SQL berisi variabel pengikatan di mana kondisi ':empno'.
- Baris kode 9: Mengeksekusi teks SQL berbingkai (yang dilakukan pada baris kode 8) menggunakan perintah NDS 'EXECUTE IMMEDIATE'
- Variabel dalam klausa 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) digunakan untuk menyimpan nilai yang diambil dari query SQL (emp_name, emp_no, gaji, manajer)
- Klausa 'USING' memberikan nilai ke variabel pengikat dalam kueri SQL (:emp_no).
- Baris kode 10-13: Menampilkan nilai yang diambil.
DBMS_SQL untuk SQL Dinamis
PL/SQL menyediakan paket DBMS_SQL yang memungkinkan Anda bekerja dengan SQL dinamis. Proses pembuatan dan pelaksanaan SQL dinamis meliputi proses berikut.
- BUKA KURSOR: SQL dinamis akan dijalankan dengan cara yang sama seperti a kursor. Jadi untuk mengeksekusi pernyataan SQL, kita harus membuka kursor.
- MENGurai SQL: Langkah selanjutnya adalah mengurai SQL dinamis. Proses ini hanya akan memeriksa sintaksis dan menjaga agar kueri tetap siap dijalankan.
- Nilai VARIABEL BIND: Langkah selanjutnya adalah menetapkan nilai untuk variabel pengikat jika ada.
- DEFINISI KOLOM: Langkah selanjutnya adalah mendefinisikan kolom menggunakan posisi relatifnya dalam pernyataan pilih.
- MENJALANKAN: Langkah selanjutnya adalah mengeksekusi query yang telah diurai.
- MENGAMBIL NILAI: Langkah selanjutnya adalah mengambil nilai yang dieksekusi.
- TUTUP KURSOR: Setelah hasilnya diambil, kursor harus ditutup.
Contoh 1: Dalam contoh ini, kita akan mengambil data dari tabel emp untuk emp_no '1001' menggunakan pernyataan DBMS_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);
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: /
Keluaran
Employee Name:XXX Employee Number:1001 Salary:15000 Manager ID:1000
Penjelasan Kode:
- Baris kode 1-9: Deklarasi variabel.
- Baris kode 10: Membingkai pernyataan SQL.
- Baris kode 11: Membuka kursor menggunakan DBMS_SQL.OPEN_CURSOR. Ini akan mengembalikan id kursor yang dibuka.
- Baris kode 12: Setelah kursor dibuka, SQL diurai.
- Baris kode 13: Variabel pengikat '1001' ditugaskan ke id kursor sebagai gantinya ':empno'.
- Baris kode 14-17: Mendefinisikan nama kolom berdasarkan posisi relatifnya dalam pernyataan SQL. Dalam kasus kami, posisi relatifnya adalah (1) nama_karyawan, (2) no_karyawan (3) gaji (4) manajer. Jadi berdasarkan posisi ini kita mendefinisikan variabel target.
- Baris kode 18: Menjalankan query menggunakan DBMS_SQL.EXECUTE. Ini mengembalikan jumlah catatan yang diproses.
- Baris kode 19-33: Mengambil catatan menggunakan loop dan menampilkannya.
- Baris kode 20: DBMS_SQL.FETCH_ROWS akan mengambil satu record dari baris yang diproses. Itu bisa dipanggil berulang kali untuk mengambil semua baris. Jika tidak dapat mengambil baris, ia akan mengembalikan 0, sehingga keluar dari perulangan.
Kesimpulan
Di bagian ini, kita telah membahas SQL dinamis dan cara mengeksekusi SQL DINAMIS. Kita juga telah melihat langkah-langkah berbeda dalam mengeksekusi SQL dinamis dengan kedua cara tersebut. Kita juga telah melihat contoh di mana skenario yang sama ditangani dengan cara NDS dan DBMS_SQL untuk melakukan eksekusi pada waktu proses.