Implisit dan Eksplisit
PL/SQL menggunakan dua tipe cursor: implisit dan eksplisit. PL/SQL mendeklarasikan cursor secara implisit untuk seluruh perintah-perintah manipulasi data SQL, termasuk query-query yang hanya menghasilkan satu baris data. Namun, untuk query-query yang menghasilkan lebih dari satu baris data, kita harus mendeklarasikan cursor eksplisit, menggunakan cursor FOR loop, atau menggunakan klausa BULK COLLECT.
Explicit Cursors
Kumpulan baris-baris data yang dihasilkan oleh query dapat terdiri dari nol, satu, atau banyak baris data, bergantung pada berapa banyak baris-baris data yang sesuai dengan kriteria pencarian yang kita inginkan. Ketika query menghasilkan banyak baris data, kita dapat secara eksplisit mendeklarasikan cursor untuk memproses baris-baris data tersebut. Lebih luas lagi, kita dapat mendeklarasikan cursor di dalam bagian deklaratif dari suatu blok PL/SQL, subprogram, atau package.
Kita menggunakan tiga perintah untuk mengontrol cursor: OPEN, FETCH, dan CLOSE. Pertama, kita menginisialisasi cursor dengan perintah OPEN, yang mana mengidentifikasi result set. Kemudian, kita dapat mengeksekusi FETCH secara berulang-ulang sampai seluruh baris-baris data ditampilkan, atau kita dapat menggunakan klausa BULK COLLECT untuk menampilkan seluruh baris-baris data sekali saja. Ketika baris data terakhir selesai diproses, kita membebaskan cursor dengan perintah CLOSE. Kita dapat memproses beberapa query di secara paralel dengan mendeklarasikan dan membuka beberapa cursor.
Ketika kita mendeklarasikan cursor, kita menamainya dan menghubungkannya dengan query tertentu dengan menggunakan sintaks:
CURSOR cursor_name [(parameter[, parameter]...)]
[RETURN return_type] IS select_statement;
dimana return_type harus merepresentasikan record atau baris data di dalam database table, dan parameter terdiri dari sintaks berikut ini:
cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]
Sebagai contoh, kita dapat mendeklarasikan cursor-cursor bernama c1 dan c2, seperti berikut ini:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job, sal FROM emp
WHERE sal > 2000;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * FROM dept WHERE deptno = 10;
Nama cursor merupakan identifier tak terdeklarasi, dan bukan merupakan nama variable PL/SQL. Kita tidak dapat memberikan nilai kepada nama cursor atau menggunakannya di dalam ekspresi. Namun, cursors dan variables mengikuti aturan jangkauan yang sama. Memberikan nama cursor-cursor setelah table-table database diperbolehkan namun tidak direkomendasikan.
Cursor dapat mengambil parameters, yang mana dapat muncul di dalam query terkait dimanapun konstanta dapat muncul. Parameter-parameter formal dari cursor haruslah parameter-parameter IN. Sehingga, mereka tidak dapat mengembalikan nilai ke parameter-parameter aktual. Juka, kita tidak dapat menentukan constraint NOT NULL pada parameter cursor.
Seperti ditunjukkan oleh contoh di bawah ini, kita dapat menginisialisasi parameter-parameter cursor menjadi nilai-nilai default. Dengan demikian, kita dapat melewatka jumlah aktual parameter yang berbeda kepada cursor, menerima atau menimpa nilai-nilai default sesuai keinginan kita. Juga, kita dapat menambahkan nilai parameter-parameter formal tanpa harus mengubah setiap referensi terhadap cursor tersebut.
DECLARE
CURSOR c1 (low INTEGER DEFAULT 0,
high INTEGER DEFAULT 99) IS SELECT ...
Scope dari parameter-parameter cursor adalah lokal bagi cursor tersebut, artinya bahwa mereka dapat direferensi hanya di dalam query yang ditentukan di dalam deklarasi cursor. Nilai-nilai dari parameter-parameter cursor digunakan oleh query terkait ketika cursor dibuka.
Membuka Cursor
Membuka cursor mengeksekusi query dan mengidentifikasi result set, yang terdiri dari seluruh baris-baris data yang sesuai dengan kriteria pencarian dari query. Untuk cursor yang dideklarasikan dengan menggunakan klausa FOR UPDATE, perintah OPEN juka mengunci baris-baris data tersebut. Contoh dari perintah OPEN adalah sebagai berikut:
DECLARE CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000; ...BEGIN OPEN c1; ...END;Baris-baris data di dalam result set tidak ditampilkan ketika perintah OPEN dieksekusi. Sebaliknya, perintah FETCH menampilkan baris-baris data.
Melewatkan Parameter-parameter Cursor
Kita menggunakan perintah OPEN untuk melewatkan parameter-parameter kepada cursor. Kecuali kita ingin menerima nilai default, setiap parameter formal di dalam deklarasi cursor harus memiliki parameter aktual yang sesuai di dalam perintah OPEN. Sebagai contoh, deklarasi cursor berikut ini
DECLARE emp_name emp.ename%TYPE; salary emp.sal%TYPE; CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT ...beberapa perintah-perintah berikut ini membuka cursor:
OPEN c1(emp_name, 3000);OPEN c1('ATTLEY', 1500);OPEN c1(emp_name, salary);Di dalam contoh terakhir, ketika identifier salary digunakan di dalam deklarasi cursor, ia mengacu kepada parameter formal. Namun, ketika ia digunakan di dalam perintah OPEN, ia mengacu kepada variable PL/SQL. Untuk menghindari kebingungan, gunakan identifier-identifier yang unik.
Parameter-parameter formal yang dideklarasikan dengan nilai default tidak perlu memiliki parameter aktual yang sesuai. Mereka dapat secara sederhana mengasumsikan nilai-nilai default mereka ketika perintah OPEN dieksekusi.
Kita dapat menghubungkan parameter-parameter aktual di dalam perintah OPEN dengan parameter-parameter formal di dalam deklarasi cursor dengan menggunakan notasi posisi atau penamaan. Tipe-tipe data dari setiap parameter aktual dan parameter formal terkaitnya haruslah kompatibel.
Fetching dengan Cursor
Kecuali kita menggunakan klausa BULK COLLECT (akan kita bicarakan pada bagian berikutnya), perintah FETCH menampilkan baris-baris data di dalam result set sekali pada satu waktu. Setiap fetch menampilkan baris terkini dan kemudian memajukan cursor ke baris berikutnya di dalam result set. Contohnya adalah sebagai berikut:
FETCH c1 INTO my_empno, my_ename, my_deptno;Untuk setiap nilai kolom yang dihasilkan oleh query yang terkait dengan cursor, harus ada variable yang sesuai, dan bertipe kompatibel di dalam daftar INTO. Khususnya, kita menggunakan perintah FETCH dengan cara berikut:
LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; -- memproses baris data (record)END LOOP;Query dapat mereferensi variable-variable PL/SQL dalam scope-nya. Namun, beberapa variable-variable di dalam query dievaluasi hanya ketika cursor dibuka. Di dalam contoh berikut ini,setiap menampilkan salary dikalikan dengan 2, meskipun factor ditambah setelah setiap fetch:
DECLARE my_sal emp.sal%TYPE; my_job emp.job%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT factor*sal FROM emp WHERE job = my_job;BEGIN ... OPEN c1; -- disini faktor sama dengan 2 LOOP FETCH c1 INTO my_sal; EXIT WHEN c1%NOTFOUND; factor := factor + 1; -- does not affect FETCH END LOOP;END;Untuk mengubah result set atau nilai-nilai dari variable-variable di dalam query, kita harus menutup dan membuka ulang cursor dengan kumpulan variable-variable input ke nilai-nilai baru mereka.
Namun, kita dapat menggunakan daftar INTO yang berbeda pada fetch-fetch yang terpisah pada cursor yang sama. Setiap fetch menampilkan baris yang lain dan memberikan nilai-nilai kepada variable-variable target, seperti ditunjukkan oleh contoh berikut ini:
DECLARE CURSOR c1 IS SELECT ename FROM emp; name1 emp.ename%TYPE; name2 emp.ename%TYPE; name3 emp.ename%TYPE;BEGIN OPEN c1; FETCH c1 INTO name1; -- ini mem-fetch baris data pertama FETCH c1 INTO name2; -- ini mem-fetch baris data kedua FETCH c1 INTO name3; -- ini mem-fetch baris data ketiga ... CLOSE c1;END;Jika kita mem-fetch ke belakang baris data terakhir di dalam result set, nilai-nilai dari variable-variable target menjadi tidak menentukan.
