Interaksi PL/SQL dengan Oracle

6.5.10. Mengurangi Network Traffic ketika Melewatkan Host Cursor Variable ke PL/SQL

Ketika melewatkan host cursor variable ke PL/SQL, kita dapat menekan network traffic dengan melakukan grouping perintah-perintah OPEN-FOR. Sebagai contoh, blok PL/SQL berikut ini membuka lima cursor variables dalam satu round trip tunggal:

/* anonymous PL/SQL block di dalam host environment */
BEGIN
  OPEN :emp_cv FOR SELECT * FROM emp;
  OPEN :dept_cv FOR SELECT * FROM dept;
  OPEN :grade_cv FOR SELECT * FROM salgrade;
  OPEN :pay_cv FOR SELECT * FROM payroll;
  OPEN :ins_cv FOR SELECT * FROM insurance;
END;

Hal ini mungkin berguna di dalam Oracle Forms, contohnya, ketika kita ingin mempopulasikan multi-block form.

Ketika kita melewatkan host cursor variables ke blok PL/SQL untuk pembukaan, area-area kerja query dimana mereka menunjuk tetap dapat diakses setelah blok selesai diproses. Hal ini mengijinkan program OCI atau Pro*C kita untuk menggunakan area-area kerja ini untuk operasi-operasi cursor biasa. Dalam contoh berikut ini, kita membuka beberapa area-area kerja seperti itu dalam satu round trip tunggal:

BEGIN
  OPEN :c1 FOR SELECT 1 FROM dual;
  OPEN :c2 FOR SELECT 1 FROM dual;
  OPEN :c3 FOR SELECT 1 FROM dual;
  OPEN :c4 FOR SELECT 1 FROM dual;
  OPEN :c5 FOR SELECT 1 FROM dual;
  ...
END;

Cursors diberikan nama c1, c2, c3, c4, dan c5 berkelakuan normal, dan kita dapat menggunakan mereka untuk berbagai tujuan. Ketika selesai, bebaskan cursor secara sederhana dengan cara sebagai berikut::

BEGIN
  CLOSE :c1;
  CLOSE :c2;
  CLOSE :c3;
  CLOSE :c4;
  CLOSE :c5;
  ...
END;

6.5.11. Menghindari Errors dengan Cursor Variables

Jika kedua cursor variables yang terkait di dalam proses assignment (pemberian nilai) adalah bertipe strong, mereka harus memiliki tipe data yang sama. Dalamc contoh berikut ini, meskipun cursor variables memiliki tipe hasil yang sama, proses pemberian nilai menyebabkan munculnya exception karena mereka memiliki tipe data yang berbeda:

DECLARE
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  PROCEDURE open_emp_cv (
  emp_cv IN OUT EmpCurTyp,
  tmp_cv IN OUT TmpCurTyp) IS
BEGIN
  ...
  emp_cv := tmp_cv; -- menyebabkan error 'wrong type'
END;

Namun, jika satu atau kedua cursor variables memiliki tipe weak, mereka tidak perlu memiliki tipe data yang sama.

Jika kita berusaha melakukan fetch dari, menutup, atau mengaplikasikan cursor attributes terhadap cursor variable yang tidak menunjuk kepada area kerja query, PL/SQL memunculkan INVALID_CURSOR. Kita dapat membuat cursor variable (atau parameter) menunjuk kepada area kerja query dengan dua cara:

• membuka (OPEN) cursor variable untuk (FOR) query tersebut.
• memberikan nilai kepada cursor variable dari host cursor variable atau PL/SQL cursor variable yang telah dibuka (OPEN).

Contoh berikut ini menunjukkan bagaimana cara-cara ini saling mempengaruhi:

DECLARE
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  emp_cv1 EmpCurTyp;
  emp_cv2 EmpCurTyp;
  emp_rec emp%ROWTYPE;
BEGIN
  /* Pemberian nilai berikut ini tidak berguna karena emp_cv1
  belum menunjuk ke area kerja query. */
  emp_cv2 := emp_cv1; -- useless
  /* Membuat emp_cv1 menunjuk ke area kerja query. */
  OPEN emp_cv1 FOR SELECT * FROM emp;
  /* Menggunakan emp_cv1 untuk fetch baris pertama dari tabel emp. */
  FETCH emp_cv1 INTO emp_rec;
  /* Fetch berikut ini memunculkan exception karena emp_cv2
  belum menunjuk ke area kerja query. */
  FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR
  EXCEPTION
  WHEN INVALID_CURSOR THEN
  /* Menciptakan emp_cv1 dan emp_cv2 yang menunjuk ke area kerja yang sama. */
  emp_cv2 := emp_cv1;
  /* Menggunakan emp_cv2 untuk melakukan fetch baris ke dua dari tabel emp. */
  FETCH emp_cv2 INTO emp_rec;
  /* Menggunakan kembali area kerja untuk query yang lain. */
  OPEN emp_cv2 FOR SELECT * FROM old_emp;
  /* Menggunakan emp_cv1 untuk fetch baris pertama dari tabel old_emp.
  Fetch berikut ini berhasil karena emp_cv1 dan emp_cv2 menunjuk ke area
  kerja query yang sama. */
  FETCH emp_cv1 INTO emp_rec; -- succeeds
END;

Berhati-hatilah ketika melewatkan cursor variables sebagai parameters. Saat runtime, PL/SQL memunculkan ROWTYPE_MISMATCH jika tipe-tipe hasil dari parameter-parameter aktual dan formal tidak kompatibel.

Di dalam contoh Pro*C berikut ini, kita mendefinisikan tipe ter-package REF CURSOR, menentukan tipe hasil emp%ROWTYPE. Selanjutnya, kita menciptakan procedure standalone yang mereferensi tipe baru. Kemudian, di dalam blok PL/SQL, kita membuka host cursor variable untuk query dari table dept. Terakhir, ketika kita melewatkan host cursor variable terbuka kepada stored procedure, PL/SQL memunculkan ROWTYPE_MISMATCH karena tipe-tipe hasil dari parameter-parameter aktual dan formal tidak kompatibel.

CREATE PACKAGE cv_types AS
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  ...
END cv_types;
/

CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN
  OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
/

-- anonymous PL/SQL block in Pro*C program
EXEC SQL EXECUTE
  BEGIN
    OPEN :cv FOR SELECT * FROM dept;
    ...
    open_emp_cv(:cv); -- raises ROWTYPE_MISMATCH
  END;
END-EXEC;

6.5.12. Batasan-batasan Cursor Variables

Saat ini, cursor variables mengikuti batasan-batasan berikut ini:

• Kita tidak dapat mendeklarasikan cursor variables di dalam package. Sebagai contoh, deklarasi berikut ini tidak diperbolehkan:

CREATE PACKAGE emp_stuff AS
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  emp_cv EmpCurTyp; -- tidak diperbolehkan
END emp_stuff;

• Remote subprograms pada server lain tidak dapat menerima nilai-nilai dari cursor variables. Dengan demikian, kita tidak dapat menggunakan RPCs untuk melewatkan cursor variables dari satu server ke lainnya.
• Jika kita melewatkan host cursor variable ke PL/SQL, kita tidak dapat melakukan fetch darinya pada sisi server kecuali kita juga membukanya disana dalam satu pemanggilan server yang sama.
• Kita tidak dapat menggunakan operator-operator pembanding untuk menguji cursor variables untuk kesamaan, ketidaksamaan, atau ke-null-an.
• Kita tidak dapat memberikan null ke cursor variable.
• Kita tidak dapat menggunakan tipe-tipe REF CURSOR untuk menentukan tipe-tipe kolom di dalam perintah CREATE TABLE atau CREATE VIEW. Sehingga, kolom-kolom pada database tidak dapat menyimpan nilai-nilai dari cursor variables.
• Kita tidak dapat menggunakan tipe REF CURSOR untuk menentukan tipe elemen dari sebuah collection, yang berarti bahwa elemen-elemen di dalam index-by table, nested table, atau varray tidak dapat menyimpan nilai-nilai cursor variables.
• Cursors dan cursor variables tidak dapat saling menggunakan., sehingga, kita tidak dapat menggunakan salah satu dimana lainnya diharapkan. Sebagai contoh, cursor FOR loop berikut ini tidak diperbolehkan karena ia berusaha untuk melakukan fetch dari cursor variable lain:

DECLARE
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  emp_cv EmpCurTyp;
  ...
BEGIN
  ...
  FOR emp_rec IN emp_cv LOOP ... -- tidak diperbolehkan
END;