Hastinapura

Oracle Database Resources

Interaksi PL/SQL dengan Oracle

6.5.5. Mengontrol Cursor Variables

Kita menggunakan tiga perintah untuk mengontrol cursor variable: OPEN-FOR, FETCH, dan CLOSE. Pertama, kita OPEN cursor variable FOR query multi-baris. Kemudian, kita melakukan FETCH baris-baris data dari result set. Ketika seluruh baris data selesai diproses, kita melakukan CLOSE terhadap cursor variable.

6.5.5.1. Membuka Cursor Variable

Perintah OPEN-FOR menghubungkan cursor variable dengan query multi-baris, mengeksekusi query, dan mengidentifikasi result set. Berikut ini sintaksnya:

OPEN {cursor_variable | :host_cursor_variable} FOR
{ select_statement
| dynamic_string [USING bind_argument[, bind_argument]...] };

dimana host_cursor_variable adalah cursor variable yang dideklarasikan dalam lingkungan PL/SQL host seperti OCI program, dan dynamic_string merupakan ekspresi string yang merepresentasikan query multi-baris.

Catatan: Bagian ini mendiskusikan kasus SQL statis, dimana select_statement digunakan. Untuk kasus SQL dinamis, dimana dynamic_string digunakan, akan kita bicarakan pada pembahasan yang akan datang.

Tidak seperti cursors, cursor variables tidak mengambil parameter-parameter. Namun, tidak ada fleksibilitas yang hilang karena kita dapat melewatkan seluruh query (tidak hanya parameter) ke cursor variable. Query dapat merefer host variables dan PL/SQL variables, parameters, dan functions.

Dalam contoh dibawah ini, kita membuka cursor variable emp_cv. Perlu dicatat bahwa kita dapat mengaplikasikan cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, dan %ROWCOUNT) kepada cursor variable.

IF NOT emp_cv%ISOPEN THEN
  /* Membuka cursor variable. */
  OPEN emp_cv FOR SELECT * FROM emp;
END IF;

Perintah-perintah OPEN-FOR dapat membuka cursor variable yang sama untuk query yang berbeda. Kita tidak perlu menutup cursor variable sebelum membukanya. (Pembanggilan ulang bertalian dengan OPEN terhadap cursor statis memunculkan predefined exception CURSOR_ALREADY_OPEN.) Ketika kita membuka kembali cursor variable untuk query yang berbeda, query sebelumnya hilang.

Biasanya, kita membuka cursor variable dengan melewatkannya ke stored procedure yang mendeklarasikan cursor variable sebagai satu dari parameter-parameter formalnya. Sebagai contoh, procedure ter-package berikut ini membuka cursor variable emp_cv:

CREATE PACKAGE emp_data AS
  ...
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;

CREATE PACKAGE BODY emp_data AS
  ...
  PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
  BEGIN
    OPEN emp_cv FOR SELECT * FROM emp;
  END open_emp_cv;
END emp_data;

Ketika kita mendeklarasikan cursor variable sebagai parameter formal dari subprogram yang membuka cursor variable, kita harus menentukan mode IN OUT. Dengan begitu, subprogram dapat melewatkan open cursor kembali ke pemanggilnya.

Alternatifnya, kita dapat menggunakan standalone procedure untuk membuka cursor variable. Secara sederhana kita definisikan tipe REF CURSOR dalam package terpisah, kemudian mengacu tipe tersebut di dalam standalone procedure. Sebagai gambaran, jika kita menciptakan package yang tak memiliki tubuh berikut ini, kita dapat menciptakan standalone procedures yang mengacu kepada tipe yang didefinisikannya:

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

Dalam contoh berikutnya, kita menciptakan standalone procedure yang mengacu kepada tipe REF CURSOR EmpCurTyp, yang didefinisikan di dalam package 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;

Untuk memusatkan proses penampilan data, kita dapat mengelompokkan query-query bertipe kompatibel di dalam stored procedure. Di dalam contoh dibawah ini, procedure ter-package mendeklarasikan sebuah penyeleksi sebagai satu dari parameter-parameter formalnya. Ketika dipanggil, procedure membuka cursor variable emp_cv untuk query yang dipilih.

CREATE PACKAGE emp_data AS
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;

CREATE PACKAGE BODY emp_data AS
  PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
    ELSIF choice = 2 THEN
      OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
    ELSIF choice = 3 THEN
      OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
    END IF;
  END;
END emp_data;

Untuk fleksibilitas lebih, kita dapat melewatkan cursor variable adan penyeleksi (selector) ke stored procedure yang mengeksekusi queries dengan tipe hasil yang berbeda. Berikut ini adalah contohnya:

CREATE PACKAGE admin_data AS
  TYPE GenCurTyp IS REF CURSOR;
  PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;

CREATE PACKAGE BODY admin_data AS
  PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS
  BEGIN
    IF choice = 1 THEN
      OPEN generic_cv FOR SELECT * FROM emp;
    ELSIF choice = 2 THEN
      OPEN generic_cv FOR SELECT * FROM dept;
    ELSIF choice = 3 THEN
      OPEN generic_cv FOR SELECT * FROM salgrade;
    END IF;
  END;
END admin_data;

6.5.5.2. Menggunakan Cursor Variable Sebagai Host Variable

Kita dapat mendeklarasikan cursor variable di dalam lingkungan host PL/SQL seperti program OCI atau Pro*C. Untuk menggunakan cursor variable, kita harus melewatkannya sebagai host variable ke PL/SQL. Dalam contoh Pro*C berikut ini, kita melewatkan host cursor variable dan selector ke blok PL/SQL, yang membuka cursor variable untuk query yang dipilih:

EXEC SQL BEGIN DECLARE SECTION;
...
/* Mendeklarasikan host cursor variable. */
SQL_CURSOR generic_cv;
int choice;
EXEC SQL END DECLARE SECTION;
...
/* Menginisialisasi host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Melewatkan host cursor variable dan selector ke blok PL/SQL. */
EXEC SQL EXECUTE
BEGIN
  IF :choice = 1 THEN
    OPEN :generic_cv FOR SELECT * FROM emp;
  ELSIF :choice = 2 THEN
    OPEN :generic_cv FOR SELECT * FROM dept;
  ELSIF :choice = 3 THEN
    OPEN :generic_cv FOR SELECT * FROM salgrade;
  END IF;
END;
END-EXEC;

Host cursor variables kompatibel dengan semua segala tipe hasil query. Mereka berkelakuan seperti PL/SQL cursor variable bertipe weak.

6.5.5.3. Melakukan Fetch dari Cursor Variable

Perintah FETCH menampilkan baris-baris data dari result set dari query multi-baris. Berikut ini adalah sintaksnya:

FETCH {cursor_variable_name | :host_cursor_variable_name}
[BULK COLLECT]
INTO {variable_name[, variable_name]... | record_name};

Dalam contoh berikut ini, kita mengambil baris-baris data sekali dalam satu waktu dari cursor variable emp_cv ke dalam user-defined record emp_rec:

LOOP
  /* Fetch dari cursor variable. */
  FETCH emp_cv INTO emp_rec;
  EXIT WHEN emp_cv%NOTFOUND; -- keluar setelah baris terakhir telah di-fetch
  -- memproses data record
END LOOP;

Menggunakan klausa BULK COLLECT, kita dapat melakukan bulk fetch baris-baris data dari cursor variable ke dalam satu atau lebih collections. Contohnya adalah sebagai berikut:

DECLARE
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
  TYPE NameList IS TABLE OF emp.ename%TYPE;
  TYPE SalList IS TABLE OF emp.sal%TYPE;
  emp_cv EmpCurTyp;
  names NameList;
  sals SalList;
BEGIN
  OPEN emp_cv FOR SELECT ename, sal FROM emp;
  FETCH emp_cv BULK COLLECT INTO names, sals;
  ...
END;

Beberapa variables di dalam query yang terhubung dievaluasi hanya ketika cursor variable dibuka. Untuk mengubah result set atau nilai-nilai dari variables di dalam query, kita harus membuka kembali cursor variable dengan variables set ke nilai-nilai baru mereka. Namun, kita dapat menggunakan klausa INTO yang berbeda pada fetches yang terpisah dengan cursor variable yang sama. Setiap fetch menampilkan baris data yang lain dari result set yang sama.

PL/SQL meyakinkan bahwa tipe hasil dari cursor variable kompatibel dengan klausa INTO pada perintah FETCH. Untuk setiap nilai kolom yang dihasilkan oleh query yang terkait dengan cursor variable, harus ada field atau variable yang terhubung serta bertipe kompatibel di dalam klausa INTO. Juga, jumlah dari field-field atau variable-variable harus sama dengan jumlah dari nilai-nilai kolom. Jika tidak, kita akan mendapati pesan kesalahan (error). Error muncuk pada saat proses kompilasi jika cursor variable bertipe strong, atau saat runtime jika bersifat weak. Saat runtime, PL/SQL memunculkan predefined exception ROWTYPE_MISMATCH sebelum fetch pertama. Jadi, jika kita menangkap error dan mengeksekusi perintah FETCH menggunakan klausa INTO yang berbeda , tidak ada baris-baris data yang hilang.

Ketika kita mendeklarasikan cursor variable sebagai parameter format dari subprogram yang melakukan fetch dari cursor variable, kita harus menentukan mode IN atau IN OUT. Namun, jika subprogram juga membuka cursor variable, kita harus menentukan mode IN OUT.

Jika kita berusaha melakukan fetch dari cursor variable yang sedang tertutup, atau yang tidak pernah dibuka, maka PL/SQL memunculkan predefined exception INVALID_CURSOR.

6.5.5.4. Menutup Cursor Variable

Perintah CLOSE menonaktifkan cursor variable. Setelah itu, result set terkait akan dibuat menjadi tidak terdefinisi. Berikut ini adalah sintaksnya:

CLOSE {cursor_variable_name | :host_cursor_variable_name);

Dalam contoh berikut ini, ketika baris data terakhir diproses, kita menutup cursor variable emp_cv:

LOOP
  FETCH emp_cv INTO emp_rec;
  EXIT WHEN emp_cv%NOTFOUND;
  -- memproses data record
  END LOOP;
  /* Menutup cursor variable. */
CLOSE emp_cv;

Ketika mendeklarasikan cursor variable sebagai parameter formal dari subprogram yang menutup cursor variable, kita harus menentukan mode IN atau IN OUT.

Jika kita berusaha menutup cursor variable yang sudah tertutup atau yang tidak pernah dibuka, PL/SQL memunculkan predefined exception INVALID_CURSOR.

 

 

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