Interaksi PL/SQL dengan Oracle

6.6. Menggunakan Cursor Attributes

Setiap explicit cursor dan cursor variable memiliki empat atribut: %FOUND, %ISOPEN, %NOTFOUND, dan %ROWCOUNT. Ketika ditambahkan ke cursor atau cursor variable, atribut-atribut ini menghasilkan informasi yang berguna tentang eksekusi dari perintah manipulasi data. Kita dapat menggunakan atribut-atribut cursor di dalam perintah-perintah prosedural tetapi tidak di dalam perintah-perintah SQL.

6.6.1. Pengenalan Explicit Cursor Attributes

Explicit cursor attributes menghasilkan informasi tentang eksekusi dari query multi-baris. Ketika explicit cursor atau cursor variable dibuka, baris-baris data yang sesuai dengan query terkait diidentifikasi dan membentuk result set. Baris-baris data di-fetch dari result set.

6.6.1.1. %FOUND Attribute: Memiliki Baris Data yang Dihasilkan?

Setelah cursor atau cursor variable dibuka tetapi sebelum fetch pertama, %FOUND menghasilkan NULL. Sesudah itu, ia menghasilkan TRUE jika fetch terakhir menghasilkan baris data, atau FALSE jika fetch terakhir gagal menghasilkan baris data. Dalam contoh berikut ini, kita menggunakan %FOUND untuk memilih aksi:

LOOP
  FETCH c1 INTO my_ename, my_sal, my_hiredate;
  IF c1%FOUND THEN -- fetch berhasil
    ...
  ELSE -- fetch gagal, maka keluar dari loop
    EXIT;
  END IF;
END LOOP;

Jika cursor atau cursor variable tidak terbuka, mereferensi terhadapnya dengan %FOUND menyebabkan munculnya predefined exception INVALID_CURSOR.

6.6.1.2. %ISOPEN Attribute: Apakah Cursor Terbuka?

%ISOPEN menghasilkan TRUE jika cursor atau cursor variablenya terbuka; sebaliknya, %ISOPEN menghasilkan FALSE. Dalam contoh berikut ini, kita menggunakan %ISOPEN untuk memilih aksi:

IF c1%ISOPEN THEN -- cursor dibuka
  ...
ELSE -- cursor tertutup, maka dibuka
  OPEN c1;
END IF;

6.6.1.3. %NOTFOUND Attribute: Memiliki Fetch Gagal?

%NOTFOUND merupakan lawan logikal dari %FOUND. %NOTFOUND menghasilkan FALSE jika fetch terakhir menghasilkan baris data, atau TRUE jika fetch terakhir gagal menghasilkan baris data. Dalam contoh berikut ini, kita menggunakan %NOTFOUND untuk keluar dari loop ketika FETCH gagal menghasilkan baris data:

LOOP
  FETCH c1 INTO my_ename, my_sal, my_hiredate;
  EXIT WHEN c1%NOTFOUND;
  ...
END LOOP;

Sebelum fetch pertama, %NOTFOUND dievaluasi menjadi NULL. Sehingga, jika FETCH tidak pernah dieksekusi secara sukses, loop tidak pernah dikeluarkan. Hal ini dikarenakan perintah EXIT WHEN mengeksekusi hanya jika kondisi WHEN bernilai TRUE. Untuk amannya, kita mungkin ingin menggunakan perintah EXIT berikut ini:

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

Jika cursor atau cursor variable tidak terbuka, mereferensinya dengan %NOTFOUND menghasilkan INVALID_CURSOR.

6.6.1.4. Berapa Banyak Baris Data yang Dihasilkan Sejauh Ini?

Ketika cursor atau cursor variable dibuka, %ROWCOUNT di-nol-kan. Sebelum fetch pertama, %ROWCOUNT menghasilkan 0. Dengan demikian, ia menghasilkan jumlah baris data yang di-fetch sejauh ini. Jumlah ditambah jika fetch terakhir menghasilkan baris data. Dalam contoh berikutnya, kita menggunakan %ROWCOUNT untuk melakukan aksi jika lebih dari sepuluh baris data telah di-fetch:

LOOP
  FETCH c1 INTO my_ename, my_deptno;
  IF c1%ROWCOUNT > 10 THEN
    ...
  END IF;
    ...
END LOOP;

Jika cursor atau cursor variable tidak terbuka, mereferensinya dengan %ROWCOUNT menyebabkan munculnya INVALID_CURSOR.

Tabel 6-1 menunjukkan apa yang dihasilkan oleh setiap cursor attributes sebelum dan setelah kita mengeksekusi perintah OPEN, FETCH atau CLOSE.

Tabel 6-1 Nilai-nilai Cursor Attributes

Catatan:

1. Mereferensi %FOUND, %NOTFOUND, atau %ROWCOUNT sebelum cursor dibuka atau setelah cursor ditutup menyebabkan munculnya INVALID_CURSOR.
2. Setelah FETCH pertama, jika result set kosong, %FOUND menghasilkan FALSE, %NOTFOUND menghasilkan TRUE, dan %ROWCOUNT menghasilkan 0.

6.6.1.5. Beberapa Contoh dari Cursor Attributes

Misalkan kita memiliki tabel dengan nama data_table yang menyimpan data yang dikumpulkan dari eksperimen-eksperimen laboratorium, dan kita ingin menganalisa data dari eksperimen 1. Dalam contoh berikut ini, kita menghitung hasil-hasil dan menyimpannya ke dalam database table bernama temp:

DECLARE
  num1 data_table.n1%TYPE; -- mendeklarasikan variables
  num2 data_table.n2%TYPE; -- memiliki tipe data seperti
  num3 data_table.n3%TYPE; -- kolom-kolom pada database
  result temp.col1%TYPE;
  CURSOR c1 IS
    SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO num1, num2, num3;
    EXIT WHEN c1%NOTFOUND; -- TRUE jika FETCH tidak menemukan baris data lagi
    result := num2/(num1 + num3);
    INSERT INTO temp VALUES (result, NULL, NULL);
  END LOOP;
  CLOSE c1;
  COMMIT;
END;

Dalam contoh selanjutnya, kita menguji seluruh kotak penyimpanan yang berisi part bernomor 5469, mengambil isinya hingga mencapai 1000 unit:

DECLARE
  CURSOR bin_cur(part_number NUMBER) IS
    SELECT amt_in_bin FROM bins
    WHERE part_num = part_number AND amt_in_bin > 0
    ORDER BY bin_num
    FOR UPDATE OF amt_in_bin;
  bin_amt bins.amt_in_bin%TYPE;
  total_so_far NUMBER(5) := 0;
  amount_needed CONSTANT NUMBER(5) := 1000;
  bins_looked_at NUMBER(3) := 0;
BEGIN
  OPEN bin_cur(5469);
  WHILE total_so_far < amount_needed LOOP
    FETCH bin_cur INTO bin_amt;
    EXIT WHEN bin_cur%NOTFOUND;
    -- jika kita keluar, tidak cukup untuk mengisi pesanan
    bins_looked_at := bins_looked_at + 1;
    IF total_so_far + bin_amt < amount_needed THEN
      UPDATE bins SET amt_in_bin = 0
      WHERE CURRENT OF bin_cur;
      -- mengambil apapun dari rak
      total_so_far := total_so_far + bin_amt;
    ELSE -- kita akhirnya memiliki cukup
      UPDATE bins SET amt_in_bin = amt_in_bin - (amount_needed - total_so_far)
      WHERE CURRENT OF bin_cur;
      total_so_far := amount_needed;
    END IF;
  END LOOP;
  CLOSE bin_cur;
  INSERT INTO temp VALUES (NULL, bins_looked_at, '<- bins looked at');
  COMMIT;
END;