Tutorial SQL Dinamik Oracle PL / SQL: Laksanakan Segera & DBMS_SQL

Isi kandungan:

Anonim

Apa itu Dynamic SQL?

Dynamic SQL adalah metodologi pengaturcaraan untuk menghasilkan dan menjalankan pernyataan pada masa berjalan. Ia digunakan terutamanya untuk menulis program tujuan umum dan fleksibel di mana pernyataan SQL akan dibuat dan dilaksanakan pada waktu berjalan berdasarkan keperluan.

Dalam tutorial ini, anda akan belajar-

  • Kaedah menulis SQL dinamik
  • NDS (Native Dynamic SQL) - Laksanakan Segera
  • DBMS_SQL untuk SQL Dinamik

Kaedah menulis SQL dinamik

PL / SQL menyediakan dua cara untuk menulis SQL dinamik

  1. NDS - SQL Dinamik Asli
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Laksanakan Segera

Native Dynamic SQL adalah kaedah yang lebih mudah untuk menulis SQL dinamik. Ia menggunakan perintah 'EXECUTE IMMEDIATE' untuk membuat dan melaksanakan SQL pada waktu berjalan. Tetapi untuk menggunakan cara ini, jenis data dan bilangan pemboleh ubah yang akan digunakan pada masa berjalan perlu diketahui sebelumnya. Ia juga memberikan prestasi yang lebih baik dan kurang kerumitan jika dibandingkan dengan DBMS_SQL.

Sintaks 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Sintaks di atas menunjukkan arahan SEGERA.
  • Klausa INTO adalah pilihan dan digunakan hanya jika SQL dinamik mengandungi pernyataan pilih yang mengambil nilai. Jenis pemboleh ubah harus sesuai dengan jenis pemboleh ubah pernyataan pilih.
  • Klausa MENGGUNAKAN adalah pilihan dan digunakan hanya jika SQL dinamik mengandungi pemboleh ubah bind.

Contoh 1 : Dalam contoh ini, kita akan mengambil data dari jadual 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;/

Pengeluaran

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

Penjelasan Kod:

  • Garis kod 2-6 : Menyatakan pemboleh ubah.
  • Baris kod 8 : Membingkaikan SQL pada waktu berjalan. SQL mengandungi pemboleh ubah bind di mana keadaan ': empno'.
  • Baris kod 9 : Melaksanakan teks SQL berbingkai (yang dilakukan dalam baris kod 8) menggunakan arahan NDS 'EXECUTE IMMEDIATE'
  • Pemboleh ubah dalam klausa 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) digunakan untuk menahan nilai yang diambil dari pertanyaan SQL (emp_name, emp_no, gaji, manager)
  • Klausa 'MENGGUNAKAN' memberikan nilai kepada pemboleh ubah bind dalam pertanyaan SQL (: emp_no)
  • Baris kod 10-13 : Menampilkan nilai yang diambil.

DBMS_SQL untuk SQL Dinamik

PL / SQL menyediakan pakej DBMS_SQL yang membolehkan anda bekerja dengan SQL dinamik. Proses membuat dan melaksanakan SQL dinamik mengandungi proses berikut.

  • KURSOR TERBUKA : SQL dinamik akan dilaksanakan dengan cara yang sama seperti kursor. Jadi untuk melaksanakan pernyataan SQL, kita mesti membuka kursor.
  • PARSE SQL : Langkah seterusnya adalah menguraikan SQL dinamik. Proses ini hanya akan memeriksa sintaks dan memastikan pertanyaan siap dilaksanakan.
  • Nilai BERBAGAI BIND : Langkah seterusnya adalah menetapkan nilai untuk pemboleh ubah bind jika ada.
  • DEFINE COLUMN : Langkah seterusnya adalah menentukan lajur menggunakan kedudukan relatif mereka dalam pernyataan pilih.
  • EXECUTE : Langkah seterusnya adalah melaksanakan pertanyaan yang dihuraikan.
  • NILAI FETCH : Langkah seterusnya adalah mengambil nilai yang dilaksanakan.
  • KURSOR TUTUP : Setelah hasilnya diambil, kursor harus ditutup.

Contoh 1 : Dalam contoh ini, kita akan mengambil data dari jadual 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:/

Pengeluaran

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

Penjelasan Kod:

  • Baris kod 1-9 : Perisytiharan berubah.
  • Baris kod 10 : Membingkai pernyataan SQL.
  • Baris kod 11 : Membuka kursor menggunakan DBMS_SQL.OPEN_CURSOR. Ia akan mengembalikan kursor id yang dibuka.
  • Baris kod 12 : Setelah kursor dibuka, SQL diuraikan.
  • Baris kod 13 : Pemboleh ubah ikatan '1001' menetapkan kepada kursor id ': empno'.
  • Baris kod 14-17 : Menentukan nama lajur berdasarkan kedudukan relatif mereka dalam pernyataan SQL. Dalam kes kami, kedudukan relatif adalah (1) emp_name, (2) emp_no (3) gaji (4) pengurus. Oleh itu berdasarkan kedudukan ini kita menentukan pemboleh ubah sasaran.
  • Baris kod 18 : Melaksanakan pertanyaan menggunakan DBMS_SQL.EXECUTE. Ia mengembalikan jumlah rekod yang diproses.
  • Baris kod 19-33 : Mengambil rekod menggunakan gelung dan memaparkannya.
  • Baris kod 20: DBMS_SQL.FETCH_ROWS akan mengambil satu rekod dari baris yang diproses. Ia boleh dipanggil berulang kali untuk mengambil semua baris. Sekiranya tidak dapat mengambil baris, ia akan mengembalikan 0, sehingga keluar dari gelung.

Ringkasan

Dalam bahagian ini, kami telah membincangkan SQL dinamik dan cara-cara untuk melaksanakan DYNAMIC SQL. Kami juga telah melihat langkah-langkah yang berbeza dalam melaksanakan SQL dinamik dengan kedua-dua cara. Kami juga telah melihat contoh-contoh di mana senario yang sama ditangani dengan cara NDS dan DBMS_SQL untuk melakukan pelaksanaan pada waktu berjalan.