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

  1. NDS – SQL Dinamis Asli
  2. 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.

NDS - Jalankan Segera

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.

DBMS_SQL untuk SQL Dinamis

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 untuk SQL Dinamis

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.