Native Dynamic SQL

11.5. Menggunakan Bulk Dynamic SQLDalam bagian ini, kita belajar bagaimana menambahkan kekuatan dari bulk binding kepada dynamic SQL. Bulk binding meningkatkan performa dengan cara meminimalkan jumlah perpindahan context diantara PL/SQL dan SQL engines. Dengan bulk binding, seluruh collections, tidak hanya individual elements, dilewatkan ke belakang dan seterusnya.

Menggunakan perintah-perintah, klausa-klausa, dan cursor attribute berikut, aplikasi-aplikasi kita dapat membangun perintah-perintah bulk SQL, kemudian mengeksekusi mereka secara dinamis saat run time:

• Perintah BULK FETCH
• Perintah BULK EXECUTE IMMEDIATE
• Perintah FORALL
• Klausa COLLECT INTO
• Klausa RETURNING INTO
• Cursor attribute %BULK_ROWCOUNT

11.5.1. Sintaks untuk Dynamic Bulk Binds

Bulk binding mengijinkan Oracle mengikat sebuah variable di dalam sebuah perintah SQL untuk sebuah kumpulan nilai-nilai. Collection type dapat berupa suatu PL/SQL collection type (index-by table, nested table, or varray). Namun, collection elements harus memiliki sebuah SQL datatype seperti CHAR, DATE, atau NUMBER. Tiga perintah mendukung dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, dan FORALL.

11.5.1.1. Bulk EXECUTE IMMEDIATE

Perintah ini mengijinkan ini untuk melakukan bulk-bind mendefinisikan variables atau OUT bind arguments yang dilewatkan sebagai parameters ke sebuah perintah SQL dynamic. Sintaksnya adalah:

EXECUTE IMMEDIATE dynamic_string
[[BULK COLLECT] INTO define_variable[, define_variable ...]]
[USING bind_argument[, bind_argument ...]]
[{RETURNING | RETURN}
BULK COLLECT INTO bind_argument[, bind_argument ...]];

Dengan sebuah dynamic multi-row query, kita dapat menggunakan klausa BULK COLLECT INTO kepada bind define variables. Nilai-nilai di dalam setiap column disimpan di dalam sebuah collection.

Dengan sebuah perintah dynamic INSERT, UPDATE, atau DELETE yang menghasilkan banyak baris data, kita dapat menggunakan klausa RETURNING BULK COLLECT INTO untuk melakukan bulk-bind output variables. Nilai-nilai baris-baris yang dihasilkan disimpan di dalam sebuah kumpulan collections.

11.5.1.2. Bulk FETCH

Perintah ini mengijinkan kita mengambil nilai dari sebuah dynamic cursor dengan cara yang sama dengan jika kita mengambilnya dari sebuah static cursor. Sintaksnya adalah:

FETCH dynamic_cursor
BULK COLLECT INTO define_variable[, define_variable ...];

Jika jumlah fari define variables di dalam daftar BULK COLLECT INTO melampaui jumlah column di dalam query select-list, Oracle memunculkan sebuah error.

11.5.1..3. Bulk FORALL

Perintah ini mengijinkan kita untuk melakukan bulk-bind input variables di dalam sebuah perintah SQL dynamic. Sebagai tambahan, kita dapat menggunakan perintah EXECUTE IMMEDIATE di dalam sebuah FORALL loop. Sintaksnya adalah:

FORALL index IN lower bound..upper bound
EXECUTE IMMEDIATE dynamic_string
USING bind_argument | bind_argument(index)
[, bind_argument | bind_argument(index)] ...
[{RETURNING | RETURN} BULK COLLECT
INTO bind_argument[, bind_argument ... ]];

Dynamic string harus merepresentasikan sebuah perintah INSERT, UPDATE, atau DELETE (tidak sebuah perintah SELECT).

11.5.2. Contoh-contoh Dynamic Bulk Binds

Kita dapat melakukan bind define variable di dalam sebuah dynamic query menggunakan klausa BULK COLLECT INTO. Seperti ditunjukkan oleh contoh berikut, kita dapat menggunakan klausa itu di dalam sebuah perintah bulk FETCH atau bulk EXECUTE IMMEDIATE.

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;b
BEGIN
  OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
  FETCH emp_cv BULK COLLECT INTO empnos, enames;
  CLOSE emp_cv;
  EXECUTE IMMEDIATE 'SELECT sal FROM emp'
  BULK COLLECT INTO sals;
END;

Hanya perintah-perintah INSERT, UPDATE, dan DELETE dapat memiliki output bind variables. Untuk melakukan bulk-bind terhadap mereka, kita menggunakan klausa BULK RETURNING INTO, yang dapat muncul hanya di dalam sebuah EXECUTE IMMEDIATE. Sebuah contohnya berikut ini:

DECLARE
  TYPE NameList IS TABLE OF VARCHAR2(15);
  enames NameList;
  bonus_amt NUMBER := 500;
  sql_stmt VARCHAR(200);
BEGIN
  sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';
  EXECUTE IMMEDIATE sql_stmt
  USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;

Untuk melakukan bind terhadap input variables di dalam sebuah perintah SQL, kita dapat menggunakan perintah FORALL dan perintah USING, seperti ditunjukkan di bawah ini. Namun, perintah SQL tidak dapat berupa sebuah query.

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  TYPE NameList IS TABLE OF VARCHAR2(15);
  empnos NumList;
  enames NameList;
BEGIN
  empnos := NumList(1,2,3,4,5);
  FORALL i IN 1..5
  EXECUTE IMMEDIATE
  'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1
  RETURNING ename INTO :2'
  USING empnos(i) RETURNING BULK COLLECT INTO enames;
  ...
END;