Hastinapura

Oracle Database Resources

Native Dynamic SQL

11.4. Menggunakan Perintah-perintah OPEN-FOR, FETCH, dan CLOSEKita menggunakan tiga perintah untuk memproses sebuah dynamic multi-row query: OPEN-FOR, FETCH, dan CLOSE. Pertama, kita OPEN (buka) sebuah cursor variable  FOR (untuk) sebuah multi-row query. Kemudian, kita FETCH baris-baris data dari result set sekali pada satu waktu. Ketika seluruh baris-baris data selesai diproses, kita CLOSE (tutup) cursor variable tersebut.

11.4.1. Membuka Cursor Variable

Perintah OPEN-FOR mengaitkan sebuah cursor variable dengan sebuah multi-row query, mengeksekusi query tersebut, mengidentifikasi result set, memposisikan cursor pada baris pertama dalam result set, kemudian mengosongkan penghitungan baris-baris data yang telah diproses, yang dipegang oleh %ROWCOUNT.

Tidak seperti bentuk static dari OPEN-FOR, bentuk dymanic memiliki klausa opsional USING. Saat run time, bind arguments di dalam klausa USING me-replace placeholders yang sesuai di dalam perintah SELECT dynamic. Sintaksnya adalah

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

dimana cursor_variable adalah sebuah weakly typed cursor variable (satu tanpa sebuah return type), host_cursor adalah sebuah cursor variable yang dideklarasikan di dalam sebuah PL/SQL host environment seperti halnya sebuah OCI program, dan dynamic_string adalah sebuah string expression yang merepresentasikan sebuah multi-row query.

Dalam contoh berikut ini, kita mendeklarasikan sebuah cursor variable, kemudian menghubungkannya dengan sebuah perintah SELECT dynamic yang menhasilkan baris-baris data dari table emp:

DECLARE
  TYPE EmpCurTyp IS REF CURSOR; -- mendifiniskan weak REF CURSOR type
  emp_cv EmpCurTyp; -- mendeklarasikan cursor variable
  my_ename VARCHAR2(15);
  my_sal NUMBER := 1000;
BEGIN
  OPEN emp_cv FOR -- open cursor variable
  'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
  ...
END;

Suatu bind arguments di dalam query tersebut dievaluasi hanya ketika cursor variable dibuka. Jadi, untuk melakukan fetch dari cursor tersebut dengan menggunakan nilai-nilai bind yang berbeda, kita harus membuka kembali cursor variable tersebut dengan bind arguments yang di-set ke nilai-nilai beru mereka.

11.4.2. Mengambil Nilai Cursor Variable

Perintah FETCH menghasilkan sebuah baris dari result set dari multi-row query, memberikan nilai-nilai dari item-item pada select list kepada variables atau fields yang sesuai di dalam klausa INTO, menambah perhitungan yang dipegang oleh %ROWCOUNT, dan memajukan cursor ke baris berikutnya. Sintaksnya adalah:

FETCH {cursor_variable | :host_cursor_variable}
INTO {define_variable[, define_variable]... | record};

Melanjutkan contoh tersebut, kita mengambil nilai baris-baris dari cursor variable emp_cv dan meletakkanya ke dalam variable my_ename dan my_sal:

LOOP
  FETCH emp_cv INTO my_ename, my_sal; -- fetch baris selanjutnya
  EXIT WHEN emp_cv%NOTFOUND; -- keluar dari loop ketika baris terakhir selesai di-fetch
  -- memproses baris
END LOOP;

Untuk setiap nilai column yang dihasilkan oleh query yang terkait dengan cursor variable tersebut, harus ada sebuah variable atau field yang sesuai dan bertipe kompatibel di dalam klausa INTO. Kita dapat menggunakan sebuah klausa INTO yang berbeda pada fetches yang terpisah di dalam cursor variable yang sama. Setiap pengambilan nilai menampikan baris lain dari result set yang sama.
Jika kita berusaha mengambil data dari sebuah cursor variable yang tertutup atau tidak pernah dibuka, PL/SQL memunculkan predefined exception INVALID_CURSOR.

11.4.3. Menutup Cursor Variable

Perintah CLOSE menutup sebuah cursor variable. Setelah ini, result set terkait menjadi tak terdefinisi. Sintaksnya adalah:

CLOSE {cursor_variable | :host_cursor_variable};

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

LOOP

  FETCH emp_cv INTO my_ename, my_sal;

  EXIT WHEN emp_cv%NOTFOUND;

  -- memproses baris

END LOOP;

CLOSE emp_cv; -- menutup cursor variable

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

11.4.4. Contoh-contoh Dynamic SQL untuk Records, Objects, dan Collections

Seperti ditunjukkan oleh contoh-contoh berikut ini, kita dapat mengambil baris-baris dari result set dari dynamic multi-row query dan meletakkannya ke dalam sebuah record:

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  emp_cv EmpCurTyp;
  emp_rec emp%ROWTYPE;
  sql_stmt VARCHAR2(200);
  my_job VARCHAR2(15) := 'CLERK';
BEGIN
  sql_stmt := 'SELECT * FROM emp WHERE job = :j';
  OPEN emp_cv FOR sql_stmt USING my_job;
  LOOP
    FETCH emp_cv INTO emp_rec;
    EXIT WHEN emp_cv%NOTFOUND;
    -- memproses record
  END LOOP;
  CLOSE emp_cv;
END;

Contoh berikutnya menggambarkan penggunaan objects dan collections. Misalkan kita mendefinisikan object type Person dan VARRAY type Hobbies, seperti berikut:

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

Sekarang, menggunakan dynamic SQL, kita dapat menulis sebuah package dari procedures yang menggunakan types ini, seperti berikut:

CREATE PACKAGE teams AS
PROCEDURE create_table (tab_name VARCHAR2);
PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);
PROCEDURE print_table (tab_name VARCHAR2);
END;
 CREATE PACKAGE BODY teams AS
PROCEDURE create_table (tab_name VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
  ' (pers Person, hobbs Hobbies)';
END;
 PROCEDURE insert_row (
tab_name VARCHAR2,
p Person,
h Hobbies) IS
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
  ' VALUES (:1, :2)' USING p, h;
END;
PROCEDURE print_table (tab_name VARCHAR2) IS
TYPE RefCurTyp IS REF CURSOR;
cv RefCurTyp;
p Person;
h Hobbies;
BEGIN
  OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;
  LOOP
    FETCH cv INTO p, h;
    EXIT WHEN cv%NOTFOUND;
    -- mencetak attributes dari 'p' dan elements dari 'h'
  END LOOP;
  CLOSE cv;
END;
END;

Dari sebuah anonymous PL/SQL block, kita dapat memanggil procedures tersebut di dalam package team, seperti berikut:

DECLARE
team_name VARCHAR2(15);
...
BEGIN
  ...
  team_name := 'Notables';
  teams.create_table(team_name);
  teams.insert_row(team_name, Person('John', 31),
  Hobbies('skiing', 'coin collecting', 'tennis'));
  teams.insert_row(team_name, Person('Mary', 28),
  Hobbies('golf', 'quilting', 'rock climbing'));
  teams.print_table(team_name);
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)

 

• PL/SQL Object Types
• PL/SQL Packages
• PL/SQL Subprograms
• Menangani PL/SQL Errors
• Interaksi PL/SQL dengan Oracle

 

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

Switch to our mobile site