Native Dynamic SQL

Pembahasan kali ini menunjukkan kepada kita bagaimana menggunakan native dynamic SQL (dynamic SQL untuk singkatnya), sebuah antarmuka PL/SQL yang membuat aplikasi-aplikasi kita lebih fleksibel dan cakap. Kita akan mempelajari cara-cara sederhana untuk menulis program-program yang dapat membangun dan memproses perintah-perintah SQL secara “on the fly” pada saat runtime.

Bersama dengan PL/SQL, kita dapat mengeksekusi berbagai bentuk perintah SQL (bahkan perintah-perintah data definition dan data control) tanpa mengurutkan ulang pendekatan-pendekatan programatik yang tidak praktis. Dynamic SQL memadukan keterbukaan ke dalam program-program kita, membuatnya lebih efisien, mudah dibaca dan ringkas.

Pembahasan kali ini akan mendiskusikan topik-topik berikut:

• Apa itu Dynamic SQL?
• Kebutuhan untuk Dynamic SQL
• Menggunakan Perintah EXECUTE IMMEDIATE
• Menggunakan Perintah-perintah OPEN-FOR, FETCH, dan CLOSE
• Menggunakan Bulk Dynamic SQL
• Tips dan Traps untuk Dynamic SQL

11.1. Apa itu Dynamic SQL?

Kebanyakan program-program PL/SQL melakukan pekerjaan yang spesifik (tertentu), dan dapat diduga. Sebagai contoh, sebuah stored procedure mungkin menerima sebuah employee number dan peningkatan salary (gaji), kemudian meng-update column sal di dalam table emp. Dalam kasus ini, teks penuh dari perintah UPDATE diketahui ketika saat kompilasi. Perintah-perintah seperti ini tidak berubah dari eksekusi satu ke berikutnya. Sehingga, mereka disebut dengan perintah-perintah SQL yang static.

Namun, beberapa program harus membangun dan memproses berbagai macam perintah-perintah SQL saat runtime. Sebagai contoh, sebuah general-purpose report writer harus membangun perintah-perintah SQL yang berbeda untuk berbagai macam reports yang dihasilkannya. Dalam kasus ini, teks penuh dari perintah tersebut tidak diketahui hingga saat runtime. Perintah-perintah seperti ini dapat, dan mungkin akan, berubah dari satu eksekusi ke eksekusi berikutnya. Sehingga, mereka disebut dengan perintah-perintah SQL yang dynamic.

Perintah-perintah SQL dynamic disimpan dalam string karakter yang dibangun oleh program kita saat runtime. String-string seperti ini harus mengandung teks dari sebuah perintah SQL yang valid atau sebuah PL/SQL block. Mereka dapat juga mengandung placeholders untuk bind arguments. Sebuah placeholder merupakan sebuah identifier tak terdeklarasi, sehingga namanya, dimana harus dimulai dengan sebuah titik dua (:), tidak berarti. Sebagai contoh, PL/SQL tidak membedakan string-string berikut ini:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'

Untuk memproses kebanyakan perintah-perintah SQL, kita menggunakan perintah EXECUTE IMMEDIATE. Namun, untuk memproses sebuah multi-row query (perintah SELECT), kita harus menggunakan perintah-perintah OPEN-FOR, FETCH, dan CLOSE.

11.2. Kebutuhan untuk Dynamic SQL

Kita membutuhkan dynamic SQL di dalam situasi sebagai berikut:

• Kita ingin mengeksekusi sebuah perintah data definition SQL (seperti CREATE), sebuah perintah data control (seperti GRANT), atau sebuah perintah session control (seperti ALTER SESSION). Di dalam PL/SQL, perintah-perintah seperti itu tidak dapat dieksekusi secara statis.
• Kita menginginkan fleksibilitas lebih. Sebagai contoh, kita mungkin ingin menangguhkan pilihan schema objects hingga saat runtime. Atau, kita mungkin ingin program kita membangun kondisi-kondisi pencarian yang berbeda untuk klausa WHERE dari sebuah perintah SELECT. Sebuah program yang lebih kompleks mungkin memilih dari berbagai macam operasi-operasi SQL, klausa, dan sebagainya.
• Kita menggunakan package DBMS_SQL untuk mengeksekusi perintah-perintah SQL, namun kita menginginkan performa yang lebih baik, sesuatu yang lebih mudah digunakan, atau fungsionalitas yang merupakan kekurangan dari DBMS_SQL seperti dukungan untuk objects dan collections.

11.3. Menggunakan Perintah EXECUTE IMMEDIATE

Perintah EXECUTE IMMEDIATE menyiapkan dan secara segera mengeksekusi sebuah perintah SQL dynamic atau sebuah anonymous PL/SQL block. Sintaksnya adalah

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

dimana dynamic_string adalah sebuah string expression yang merepresentasikan sebuah perintah SQL atau PL/SQL block, define_variable adalah sebuah variable yang menyimpan sebuah nilai column yang dipilih, dan record adalah sebuah user-defined atau %ROWTYPE record yang menyimpan sebuah baris data yang dipilih. Sebuah input bind_argument adalah sebuah expression yang nilainya dilewatkan ke perintah SQL dynamic atau PL/SQL block. Sebuah output bind_argument adalah sebuah variable yang menyimpan sebuah nilai yang dihasilkan oleh perintah SQL dynamic atau PL/SQL block.

Kecuali untuk multi-row queries, dynamic string dapat mengandung suatu perintah SQL (tanpa terminator) atau suatu PL/SQL block (dengan terminator). String tersebut dapat juga mengandung placeholders untuk bind arguments. Namun, kita tidak dapat menggunakan bind arguments untuk melewatkan nama-nama dari schema objects ke sebuah perintah SQL dynamic.

Digunakan hanya untuk single-row query, klausa INTO menentukan variable-variable atau record ke column mana nilai-nilainya ditampilkan. Untuk setiap nilai yang ditampilkan oleh query tersebut, harus ada variable yang sesuai, dan kompatibel dalam type atau field di dalam klausa INTO.

Digunakan hanya untuk perintah-perintah DML yang memiliki sebuah klausa RETURNING (tanpa sebuah klausa BULK COLLECT), klausa RETURNING INTO menentukan variable-variable ke column mana nilai-nilainya akan dikembalikan. Untuk setiap nilai yang dikembalikan oleh perintah DML, harus ada variable yang sesuai dan kompatibel dalam tipe di dalam klausa RETURNING INTO.

Kita dapat meletakkan seluruh bind arguments di dalam klausa USING. Mode default untuk parameternya adalah IN. Untuk perintah-perintah DML yang memiliki sebuah klausa RETURNING, kita dapat meletakkan arguments OUT di dalam klausa RETURNING tanpa menentukan parameter mode, dimana, secara definisi, merupakan OUT. Jika kita menggunakan kedua klausa USING dan RETURNING INTO, klausa IN hanya dapat mengandung arguments IN.

Pada saat run time, bind arguments me-replace placeholders yang sesuai di dalam dynamic string tersebut. Jadi, setiap placeholder harus terhubung dengan sebuah bind argument di dalam klausa USING dan/atau klausa RETURNING INTO. Kita dapat menggunakan numeric, character, dan string literals sebagai bind arguments, namun kita tidak dapat menggunakan Boolean literals (TRUE, FALSE, dan NULL).

Dynamic SQL mendukung seluruh tipe data SQL. Jadi, sebagai contoh, mendefinisikan variables dan bind arguments dapat berupa collections, LOBs, instances dari sebuah object type, dan refs. Sebagai aturannya, dynamic SQL tidak mendukung PL/SQL-specific types. Jadi, sebagai contoh, mendefinisikan bariables dan bind arguments tidak dapat berupa Boolean atau index-by table. Satu-satunya perkecualian adalah bahwa sebuah PL/SQL record dapat muncip di dalam klausa INTO.

Kita dapat mengeksekusi sebuah perintah SQL dynamic secara berulang-ulang dengan menggunakan nilai-nilai baru untuk bind arguments. Namun, kita mendatangkan beberapa overhead karena EXECUTE IMMEDIATE menyiapkan kembali dynamic string sebelum setiap eksekusi.