Hastinapura

Oracle Database Resources

Collections dan Records PL/SQL

5.14. Menampilkan Hasil Query ke dalam Collections dengan Klausa BULK COLLECT

Kata kunci BULK COLLECT memberitahu SQL engine untuk melakukan bulk-bind output collections sebelum mengembalikannya ke PL/SQL engine. Kita dapat menggunakan kata-kata kunci di dalam klausa SELECT INTO, FETCH INTO, dan RETURNING INTO. Berikut ini adalah sintaksnya:

… BULK COLLECT INTO collection_name[, collection_name] …

SQL engine melakukan bulk-binds seluruh collections yang direferensi di dalam daftar INTO. Kolom-kolom terkait dapat menyimpan nilai scalar atau komposit termasuk objects. Dalam contoh berikut ini, SQL engine mengambil seluruh kolom-kolom empno dan ename ke dalam nested tables sebelum mengembalikan tables ke PL/SQL engine:

DECLARE
  TYPE NumTab IS TABLE OF emp.empno%TYPE;
  TYPE NameTab IS TABLE OF emp.ename%TYPE;
  enums NumTab; -- tidak perlu diinisialisasi
  names NameTab;
BEGIN
  SELECT empno, ename BULK COLLECT
  INTO enums, names
  FROM emp;
  ...
END;

Dalam contoh berikutnya, SQL engine mengambil seluruh nilai-nilai di dalam kolom object ke dalam nested table sebelum mengembalikan table ke PL/SQL engine:

CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER);

CREATE TABLE grid (num NUMBER, loc Coords);

INSERT INTO grid VALUES(10, Coords(1,2));

INSERT INTO grid VALUES(20, Coords(3,4));

DECLARE
  TYPE CoordsTab IS TABLE OF Coords;
  pairs CoordsTab;
BEGIN
  SELECT loc BULK COLLECT
  INTO pairs
  FROM grid;
  -- sekarang pasangannya berisi (1,2) dan (3,4)
END;

SQL engine menginisialisasi dan memperpanjang collections untuk kita. (Namun, ia tidak dapat memperpanjang varray melampaui ukuran maksimumnya). Kemudian, dimulai dari index 1, ia menambahkan elemen-elemen secara berurutan dan menimpa beberapa elemen-elemen pre-existent.

SQL engine melakukan bulk-binds seluruh kolom-kolom pada database. Sehingga, jika table memiliki 50,000 baris data, engine mengambil 50,000 nilai-nilai kolom ke dalam collection yang menjadi target. Namun, kita dapat menggunakan pseudocolumn ROWNUM untuk membatasi jumlah baris data yang diproses. Dalam contoh berikut ini, kita membatasi jumlah baris data menjadi 100.

DECLARE
  TYPE SalList IS TABLE OF emp.sal%TYPE;
  sals SalList;
BEGIN
  SELECT sal BULK COLLECT
  INTO sals
  FROM emp
  WHERE ROWNUM <= 100;
  ...
END;

5.14.1. Contoh dari Bulk Fetching dari Cursor Ke dalam Satu atau Lebih Collections

Kita dapat melakukan bulk-fetch dari cursor ke dalam satu atau lebih collections:

DECLARE
  TYPE NameList IS TABLE OF emp.ename%TYPE;
  TYPE SalList IS TABLE OF emp.sal%TYPE;
  CURSOR c1 IS
    SELECT ename, sal
    FROM emp
    WHERE sal > 1000;
  names NameList;
  sals SalList;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO names, sals;
END;

Ke dalam Collection dari Records

Kita dapat melakukan bulk-fetch dari cursor ke dalam collection dari records:

DECLARE
  TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
  dept_recs DeptRecTab;
  CURSOR c1 IS
  SELECT deptno, dname, loc
  FROM dept
  WHERE deptno > 10;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO dept_recs;
END;

5.14.2. Membatasi Baris Data untuk Operasi Bulk FETCH dengan Klausa LIMIT

Klausa opsional LIMIT, diperbolehkan hanya di dalam perintah-perintah FETCH bulk (bukan scalar), mengijinkan kita membatasi jumlah bari data yang diambil dari database. Sintaksnya adalah:

FETCH … BULK COLLECT INTO … [LIMIT rows];

dimana rows dapat berupa literal, variable, atau ekspresi namun harus berupa angka. Jika tidak, PL/SQL memunculkan predefined exception VALUE_ERROR. Jika angkanya tidak positif, PL/SQL memunculkan INVALID_NUMBER. Jika perlu, PL/SQL membulatkan keatas angka tersebut ke integer terdekat.

DECLARE
  TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  CURSOR c1 IS
    SELECT empno FROM emp;
  empnos NumTab;
  rows NATURAL := 10;
BEGIN
  OPEN c1;
  LOOP
    /* Perintah berikut ini mengambil 10 baris data (atau kurang). */
    FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
    EXIT WHEN c1%NOTFOUND;
      ...
  END LOOP;
  CLOSE c1;
END;

5.14.3. Menampilkan Hasil-hasil DML ke dalam Collection dengan Klausa RETURNING INTO

Kita dapat menggunakan klausa BULK COLLECT di dalam klausa RETURNING INTO dari perintah INSERT, UPDATE, atau DELETE, seperti ditunjukkan oleh contoh berikut ini:

DECLARE
  TYPE NumList IS TABLE OF emp.empno%TYPE;
  enums NumList;
BEGIN
  DELETE
  FROM emp
  WHERE deptno = 20
  RETURNING empno BULK COLLECT INTO enums;
  -- jika terdapat lima karyawan di dalam departemen 20,
  -- maka enums berisi lima nomor karyawan
END;

5.14.4. Batasan-batasan pada BULK COLLECT

Batasan-batasan berikut ini diterapkan pada klausa BULK COLLECT:

• Kita tidak dapat melakukan bulk collect ke dalam associative array yang memiliki kunci bertipe string.
• Kita dapat menggunakan klausa BULK COLLECT hanya pada program-program server-side (tidak pada client-side). Jika tidak, kita akan mendapatkan error this feature is not supported in client-side programs.
• Seluruh target-target di dalam klausa BULK COLLECT INTO harus berupa collections, seperti ditunjukkan oleh contoh berikut ini:

DECLARE
  TYPE NameList IS TABLE OF emp.ename%TYPE;
  names NameList;
  salary emp.sal%TYPE;
BEGIN
  SELECT ename, sal BULK COLLECT INTO names, salary -- pelanggaran target
  FROM emp
  WHERE ROWNUM < 50;
  ...
END;

• Target-target komposit (seperti objects) tidak dapat digunakan di dalam klausa RETURNING INTO. Jika tidak, kita akan mendapatkan error unsupported feature with RETURNING INTO clause.
• Ketika konversi-konversi tipe data implisit diperlukan, target-target komposit tidak dapat digunakan di dalam klausa BULK COLLECT INTO.
• Ketika konversi tipe data  implisit diperlukan, collection dari target komposit (seperti collection dari objects) tidak dapat digunakan di dalam klausa BULK COLLECT INTO.

5.14.5. Menggunakan FORALL dan BULK COLLECT Secara Bersama-sama

Kita dapat mengkombinasikan klausa BULK COLLECT bersama perintah FORALL, dimana SQL engine melakukan bulk-binds terhadap nilai-nilai kolom secara menaik (incremental). Di dalam contoh berikut ini, jika collection dept memiliki 3 elemen, setiap yang mana menyebabkan 5 baris data dihapus, maka collection enum memiliki 15 elemen ketika perintah selesai diproses:

FORALL j IN depts.FIRST..depts.LAST
  DELETE FROM emp
  WHERE empno = depts(j)
  RETURNING empno BULK COLLECT INTO enums;

Nilai-nilai kolom yang dihasilkan oleh setiap eksekusi ditambahkan ke nilai-nilai yang dihasilkan sebelumnya. (Dengan perulangan FOR, nilai-nilai sebelumnya akan ditimpa).

Kita tidak dapat menggunakan perintah SELECT…BULK COLLECT di dalam perintah FORALL. Jika tidak, kita akan mendapatkan error implementation restriction: cannot use FORALL and BULK COLLECT INTO together in SELECT statements.

5.14.6. Menggunakan Host Arrays Bersama Bulk Binds

Program-program client-side dapat menggunakan blok-blok PL/SQL tanpa nama (anonymous) untuk melakukan bulk-bind terhadap input dan output dari host arrays. Pada kenyataannya, hal ini merupakan cara yang paling efisien untuk melewatkan collections ke dan dari database server.

Host arrays dideklarasikan di dalam host environment seperti halnya program OCI atau Pro*C dan harus diawali dengan tanda titik dua (:) untuk membedakan mereka dengan collections PL/SQL. Di dalam contoh di bawah ini, input host arrays digunakan di dalam perintah DELETE. Pada saat runtime,  blok PL/SQL tak bernama (anonymous) dikirimkan ke database server untuk eksekusi.

DECLARE
  ...
BEGIN
  -- asumsikan bahwa nilai-nilai diberikan kepada host array
  -- dan host variables di dalam the host environment
  FORALL i IN :lower..:upper
  DELETE FROM emp
  WHERE deptno = :depts(i);
  ...
END;

 

 

Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman)

 

• Native Dynamic SQL
• PL/SQL Object Types
• PL/SQL Packages
• PL/SQL Subprograms
• Menangani PL/SQL Errors

 

Copyright © 2012 Hastinapura. Beranda | Berbagi Cerita | Hastinapura | Kelasik | Langgam | Nunung | Obsesi | Propolis | Serasi | Spektakuler

Switch to our mobile site