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

Daftar Isi:

Anonim

Apa itu SQL Dinamis?

SQL Dinamis adalah metodologi pemrograman untuk menghasilkan dan menjalankan pernyataan pada saat run-time. Ini terutama digunakan untuk menulis program tujuan umum dan fleksibel di mana pernyataan SQL akan dibuat dan dijalankan pada waktu berjalan berdasarkan persyaratan.

Dalam tutorial ini, Anda akan belajar-

  • Cara menulis SQL dinamis
  • NDS (Native Dynamic SQL) - Jalankan Segera
  • DBMS_SQL untuk Dynamic SQL

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. Ia menggunakan perintah 'EXECUTE IMMEDIATE' untuk membuat dan menjalankan SQL pada saat run-time. Namun untuk menggunakan cara ini, tipe data dan jumlah variabel yang akan digunakan pada suatu waktu perlu diketahui sebelumnya. Ini juga memberikan kinerja yang lebih baik dan lebih sedikit kerumitan jika dibandingkan dengan DBMS_SQL.

Sintaksis 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Sintaks di atas menunjukkan perintah EXECUTE IMMEDIATE.
  • Klausa INTO bersifat opsional dan hanya digunakan jika SQL dinamis berisi pernyataan pilih yang mengambil nilai. Jenis variabel harus cocok dengan jenis variabel dari pernyataan pilih.
  • Klausa MENGGUNAKAN bersifat opsional dan hanya digunakan jika SQL dinamis berisi variabel mengikat apa pun.

Contoh 1 : Dalam contoh ini, kita akan mengambil data dari tabel emp untuk emp_no '1001' menggunakan pernyataan NDS.

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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 : XXXEmployee Number: 1001Salary: 15000Manager ED: 1000

Penjelasan Kode:

  • Baris kode 2-6 : Mendeklarasikan variabel.
  • Baris kode 8 : Membingkai SQL pada saat run-time. SQL berisi variabel bind di mana kondisi ': empno'.
  • Baris kode 9 : Menjalankan teks SQL berbingkai (yang dilakukan dalam 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 kueri SQL (emp_name, emp_no, gaji, manajer)
  • Klausa 'USING' memberikan nilai ke variabel bind dalam kueri SQL (: emp_no).
  • Baris kode 10-13 : Menampilkan nilai yang diambil.

DBMS_SQL untuk Dynamic SQL

PL / SQL menyediakan paket DBMS_SQL yang memungkinkan Anda bekerja dengan SQL dinamis. Proses membuat dan menjalankan SQL dinamis berisi proses berikut.

  • OPEN CURSOR : SQL dinamis akan dijalankan dengan cara yang sama seperti kursor. Jadi untuk menjalankan pernyataan SQL, kita harus membuka kursor.
  • PARSE SQL : Langkah selanjutnya adalah mengurai SQL dinamis. Proses ini hanya akan memeriksa sintaks dan membuat kueri siap untuk dieksekusi.
  • BIND VARIABLE Values : Langkah selanjutnya adalah menetapkan nilai untuk variabel bind jika ada.
  • DEFINE COLUMN : Langkah selanjutnya adalah menentukan kolom menggunakan posisi relatifnya dalam pernyataan pilih.
  • LAKUKAN : Langkah selanjutnya adalah menjalankan kueri yang diurai.
  • FETCH VALUES : Langkah selanjutnya adalah mengambil nilai yang dieksekusi.
  • TUTUP KURSOR : Setelah hasil diambil, kursor harus ditutup.

Contoh 1 : Dalam contoh ini, kita akan mengambil data dari tabel emp untuk emp_no '1001' menggunakan pernyataan DBMS_SQL.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:XXXEmployee Number:1001Salary:15000Manager 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 ikat '1001' ditugaskan ke id kursor sebagai gantinya ': empno'.
  • Baris kode 14-17 : Menentukan nama kolom berdasarkan posisi relatifnya dalam pernyataan SQL. Dalam kasus kami, posisi relatifnya adalah (1) emp_name, (2) emp_no (3) gaji (4) manajer. Jadi berdasarkan posisi ini kami mendefinisikan variabel target.
  • Baris kode 18 : Menjalankan kueri menggunakan DBMS_SQL.EXECUTE. Ini mengembalikan jumlah rekaman yang diproses.
  • Baris kode 19-33 : Mengambil record menggunakan loop dan menampilkan record yang sama.
  • Baris kode 20: DBMS_SQL.FETCH_ROWS akan mengambil satu catatan dari baris yang diproses. Dapat dipanggil berulang kali untuk mengambil semua baris. Jika tidak dapat mengambil baris, itu akan mengembalikan 0, sehingga keluar dari loop.

Ringkasan

Pada bagian ini, kita telah membahas SQL dinamis dan cara menjalankan SQL DINAMIS. Kami juga telah melihat langkah-langkah berbeda dalam menjalankan SQL dinamis dengan kedua cara tersebut. Kami juga telah melihat contoh di mana skenario yang sama ditangani dengan cara NDS dan DBMS_SQL untuk melakukan eksekusi pada saat run-time.