Oracle Tutorial SQL Dinamis PL/SQL: Jalankan Segera & DBMS_SQL

Apa itu SQL Dinamis?

Dinamis SQL SQL adalah metodologi pemrograman untuk menghasilkan dan menjalankan pernyataan pada saat eksekusi program. Metode ini terutama digunakan untuk menulis program serbaguna dan fleksibel di mana pernyataan SQL dibuat dan dieksekusi pada saat eksekusi berdasarkan kebutuhan, misalnya ketika nama tabel, daftar kolom, atau kondisi WHERE tidak diketahui hingga program dijalankan.

Cara Menulis SQL Dinamis

PL/SQL menyediakan dua cara untuk menulis SQL dinamis:

1. NDS – SQL Dinamis Asli (pernyataan EXECUTE IMMEDIATE dan OPEN-FOR)
2. DBMS_SQL (paket yang disediakan)

Aturan umumnya sederhana: jika jumlah dan tipe data variabel input dan output diketahui pada saat kompilasi, gunakan Native Dynamic SQL karena lebih cepat dan membutuhkan lebih sedikit kode. Jika informasi tersebut hanya diketahui pada saat eksekusi, gunakan paket DBMS_SQL.

NDS (Native Dynamic SQL) – Jalankan Segera

Native Dynamic SQL adalah cara yang lebih mudah untuk menulis SQL dinamis. Ia menggunakan perintah EXECUTE IMMEDIATE untuk membuat dan mengeksekusi SQL pada saat runtime. Untuk menggunakan pendekatan ini, tipe data dan jumlah variabel yang digunakan pada saat runtime harus diketahui sebelumnya. Pendekatan ini juga memberikan kinerja yang lebih baik dan kompleksitas yang lebih rendah dibandingkan dengan DBMS_SQL.

Sintaksis

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• string_sql_dinamis: Ekspresi string (VARCHAR2 atau CHAR, bukan NVARCHAR2/NCHAR) yang berisi satu pernyataan SQL atau blok PL/SQL.
• Klausul INTO: Opsional. Hanya digunakan ketika SQL dinamis berupa SELECT satu baris; ini menangkap nilai yang dikembalikan ke dalam variabel atau record. Setiap kolom yang dipilih memerlukan variabel yang kompatibel dengan tipe datanya.
• MENGGUNAKAN klausa: Opsional. Menyediakan variabel pengikat. Mode default adalah IN; OUT dan IN OUT digunakan untuk menerima nilai kembali.
• KLAUSUL KEMBALI KE: Digunakan dengan pernyataan DML yang membawa klausa RETURNING, untuk menangkap nilai baris yang terpengaruh ke dalam argumen bind.

Contoh 1: Dalam contoh ini, kita mengambil data dari tabel emp untuk emp_no '1001' menggunakan pernyataan NDS dengan variabel 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;
/

Keluaran

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

Code Penjelasan:

• Baris 2-6: Mendeklarasikan variabel.
• Baris 8: Menyusun SQL saat runtime. SQL tersebut berisi variabel bind ':empno' dalam kondisi WHERE.
• Baris 9-11: Menjalankan SQL yang telah disusun dengan EXECUTE IMMEDIATE. Variabel klausa INTO menyimpan nilai yang diambil, dan klausa USING menyediakan nilai untuk variabel bind :empno.
• Baris 12-15: Menampilkan nilai-nilai yang diambil.

Menggunakan SQL Dinamis untuk DDL

PL/SQL statis tidak dapat menjalankan DDL seperti CREATE, ALTER, atau DROP secara langsung. EXECUTE IMMEDIATE mengatasi hal ini dengan membangun pernyataan sebagai string, yang juga berguna ketika nama objek diberikan saat runtime:

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;
/

Nama objek (tabel, kolom, skema) tidak dapat dilewatkan sebagai variabel terikat, jadi harus digabungkan ke dalam string. Selalu validasi input tersebut, misalnya dengan DBMS_ASSERT.SIMPLE_SQL_NAME, untuk menghindari injeksi SQL.

DBMS_SQL untuk SQL Dinamis

PL/SQL menyediakan paket DBMS_SQL untuk bekerja dengan SQL dinamis ketika struktur pernyataan tidak diketahui hingga waktu eksekusi. Proses pembuatan dan eksekusi SQL dinamis melibatkan langkah-langkah berikut:

• BUKA KURSOR: SQL dinamis dieksekusi seperti kursorUntuk mengeksekusi pernyataan SQL, kita harus membuka kursor terlebih dahulu.
• Mengurai SQL: Uraikan SQL dinamis. Ini memeriksa sintaks dan menyiapkan kueri untuk dieksekusi.
• Nilai Variabel Ikat: Tetapkan nilai untuk variabel pengikat, jika ada.
• DEFINISIKAN KOLOM: Definisikan setiap kolom menggunakan posisi relatifnya dalam pernyataan select.
• MENJALANKAN: Jalankan kueri yang telah diuraikan.
• AMBIL NILAI: Ambil nilai yang telah dieksekusi.
• TUTUP KURSOR: Setelah hasil diambil, tutup kursor.

Contoh 1: Dalam contoh ini, kita mengambil data dari tabel emp untuk emp_no '1001' menggunakan pernyataan DBMS_SQL. Blok EXCEPTION menutup kursor bahkan jika terjadi kesalahan.

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;
/

Keluaran

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

Code Penjelasan:

• Baris 1-8: Deklarasi variabel.
• Baris 10: Menyusun pernyataan SQL.
• Baris 11: Membuka kursor menggunakan DBMS_SQL.OPEN_CURSOR, yang mengembalikan id dari kursor yang dibuka.
• Baris 12: Setelah kursor dibuka, SQL akan diuraikan.
• Baris 13: Nilai bind '1001' ditetapkan sebagai pengganti ':empno'.
• Baris 14-17: Menentukan kolom berdasarkan posisi relatifnya: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Baris 18: Menjalankan kueri dengan DBMS_SQL.EXECUTE, yang mengembalikan jumlah catatan yang diproses.
• Baris 19-32: Mengambil data dalam sebuah perulangan. FETCH_ROWS mengembalikan nilai 0 jika tidak ada baris yang tersisa, yang berarti perulangan berakhir.
• Blok PENGECUALIAN: Memastikan kursor ditutup sehingga kursor yang terbuka tidak bocor jika terjadi kesalahan.

NDS vs DBMS_SQL: Kapan Menggunakan yang Mana?

Kedua pendekatan tersebut menjalankan SQL saat runtime, tetapi keduanya cocok untuk situasi yang berbeda:

• Gunakan Native Dynamic SQL (EXECUTE IMMEDIATE / OPEN-FOR) Ketika jumlah dan tipe data input dan output diketahui pada saat kompilasi, maka akan lebih cepat, lebih mudah dibaca, dan membutuhkan lebih sedikit kode.
• Gunakan DBMS_SQL ketika struktur tidak diketahui hingga waktu eksekusi, misalnya kueri yang jumlah kolom terpilih atau variabel terikatnya bervariasi, yang dikenal sebagai SQL dinamis metode-4, atau pernyataan yang terlalu besar untuk dimuat dalam satu variabel VARCHAR2 32K.

Pertanyaan Umum Demo Slot