BAB V – CURSOR AND RESULT SETS TUJUAN BELAJAR: - Menjelaskan tentang proses cursor - Menggunakan cursor dalam SP - Melakukan position delete dan position update - Mendeskripsikan jalannya cursor pada commit dan rollback - Menggunakan proses cursor untuk mengembalikan result sets pada sebuah aplkasi - Menjelaskan bagaimana cursor mempengaruhi locking
Cursor memungkinkan sebuah aplikasi me-retrive beberapa baris dan kemudian memprosesnya menjadi beberapa baris dalam 1 waktu.Ketika menggunakan sebuah cursor, program dapat mengambil setiap baris secara sequential dari result table sampai data terakhir.Statement SELECT digunakan harus dalam statement DECLARE CURSOR. Statement DECLARE CURSOR menentukan dan menamai cursor, mengidentifikasi sekumpulan baris yang diambil(retrieved) dengan statement SELECT dari cursor. Result table diproses seperti sebuah data set yang berurutan. Cursor harus dibuka(dengan statement OPEN) sebelum baris lainnya di-retrieved. Statement FETCH digunakan untuk meretrieve baris terakhir cursor. FETCH dapat dijalankan berkali-kali sampai semua baris diretrieved. Ketika sudah sampai pada data terakhir, cursor harus ditutup dengan statement CLOSE. 5.1 CONTOH DEKLARASI CURSOR DECLARE c1 CURSOR FOR select * from staff; DECLARE c1 CURSOR WITH HOLD FOR select * form staff; DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * from staff; DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select * from staff;
Cursor yang dideklarasikan di dalam Store Procedure dapat diproses seperti halnya cursor pada konvensional program.
•
WITH HOLD – Mendeklarasikan cursor menggunakan WITH HOLD clause akan menyebabkan cursor mempertahankan posisinya dan beberapa lock melewati transaksi. Ketika cursor dideklarasikan dengan WITH HOLD, resource tidak dibebaskan pada saat COMMIT. Jika cursor tidak dideklarasikan dengan WITH HOLD clause, semua resource dibebaskan ketika COMMIT atau ROLLBACK.
•
WITH RETURN – clause ini mengindikasikan bahwa cursor yang dideklarasikan akan menghasilan result set.
•
TO CALLER – Merupakan default dan menetapkan bahwa cursor dapat mengembalikan result set ke pemanggil. Cotohnya, jika pemanggil adalah Store Procedure lain, result set dikembalikan ke store procedure tersebut. Jika pemanggil adalah aplikasi client, result set dikembalikan ke aplikasi client.
Result set dari
RETURN TO CALLER hanya tampak ke program yang terletak pada sebelum level bersarang. •
TO CLIENT – Menetapkan bahwa cursor dapat mengembalikan result set ko aplikasi client. Cursor ini idak terlihat pada semua intermediate nested procedure.
TO CALLER clause dan TO CLIENT clause berarti hanya pada konteks nested SQL procedure. Hanya saja row yang tidak terbaca dilewati. Contohnya, jika result set dari cursor adalah 500 row, dan 150 dari row tersebut telah dibaca oleh procedure ketika procedure diakhiri, row 151 sampai row 500 akan dikembalikan ke pemanggil atau aplikasi. 5.1.1
Cursor dalam sebuah SQL Procededure
Cursor dapat didefinisikan di dalam stored procedure. Stored procedure DB2ADMIN.Sample1 menerima satu input parameter, in_Dept. Sebuah Cursor cursor1 didefinisikan dan akan berisi sebuah barisdari table ORG dimana parameter input sama dengan DEPRNUMB. Result set yang dikembalikan oleh stored procedure akan berisi DEPTNUMB, MANAGER, dan LOCATION untuk DEPTNUMB yang sama dengan parameter in_Dept.
CREATE PROCEDURE Sample1( IN in_Dept INT ) RESULT SETS 1 LANGUAGE SQL -----------------------------------------------------------------SQL Stored Procedure -------------------------------------------------------------------------------------------------------P1: BEGIN DECLARE cursor1 CURSOR WITH RETURN FOR SELECT DEPTNAME, MANAGER, LOCATION FROM ORG WHERE DEPTNUMB = in_Dept; ---OPEN cursor1; END P1
5.1.2
Contoh Cursor CREATE PROCEDURE Cur_Samp ( IN v_name VARCHAR(254), OUT v_job VARCHAR(5) ) LANGUAGE SQL P1: BEGIN DECLARE c1 CURSOR FOR SELECT JOB FROM STAFF WHERE NAME = v_name; OPEN c1; FETCH c1 INTO v_job; END P1
Contoh di atas merupakan stored procedure yang mendeklarasikan local cursor. Karena tidak ditentukan dengan WITH RETURN, cursor akan ditutup oleh database manager ketika store procedure selesai. Tanpa kode tambahan pada stored procedure, maka cursor tersebut tidak berguna. Contoh cursor di atas merupakan local cursor karena tidak ada WITH RETURN clause, dan tidak ada result set dibuat. Stored procedure tersebut menggunakan FETCH untuk proses cursor pada select sebuah job untuk user dengan
nama tertentu, dan mengembalikan nilai dari job ke program yang memanggil melalui parameter OUT. 5.2 POSITIONED DELETE Cursors dapat digunakan untuk menghapus data pada posisi cursor saat itu selama cursor deletable. Sebuah cursor dinyatakan deletable ketika : •
Setiap FROM clause dari outer fullselect me-refer hanya pada satu table.
•
Outer fullselect tidak mengandung VALUES, GROUP BY, atau HAVING clause dan tidak mengandung fungsi kolom.
– Daftar select pada outer fullselect tidak mengandung DISTINCT. •
Select – statement tidak mengandung ORDER BY atau FOR READ ONLY clause. • Cursor didefinisikan secara statis, atau terdapat FO UPDATE clause. Ketika cursor dideklarasikan dapat dispesifikasikan operasi jenis apa yang akan dilakukan. Jika tidak menyediakan spesifikasi serupa, cursor di – refer sebagai ambigu, dab DB2 hanya akan menyediakan optimisasi minor. Menentukan type cursor dengan menyediakan FOR READ ONLY atau FOR UPDATE clause dalam SELECT statement pada cursor. CREATEPROCEDURE pos_del() DYNAMICRESULTSETS 1 P1: BEGIN DECLARE v_DEPTNUMB SMALLINT; DECLARE v_DEPTNAME VARCHAR(14); DECLARE v_LOCATION VARCHAR(13); DECLARE cursor1 CURSORFOR SELECT DEPTNUMB, DEPTNAME, LOCATION FROM DB2ADMIN.ORG FORUPDATE;-OPEN cursor1;-FETCHFROM cursor1 INTO v_DEPTNUMB, V_DEPTNAME, v_LOCATION; DELETEFROM DB2ADMIN.ORG WHERECURRENTOF cursor1; CLOSE cursor1; END P1
5.3 POSITIONED UPDATE Dalam sebuah positioned update, cursor harus updatable. cursor updatable jika semua pernyataan berikut benar : •
cursormerupakan deletable. • Kolong yang diupdate berubah pada kolom pada table asal
•
Semua kolom yang diupdate harus ditentukan secara explisit atau implisit dalam FOR UPDATE clause. CREATEPROCEDURE pos_upd() DYNAMICRESULTSETS 1 P1: BEGIN DECLARE v_DEPTNUMB SMALLINT; DECLARE v_DEPTNAME VARCHAR(14); DECLARE v_LOCATION VARCHAR(13); DECLARE cursor1 CURSORFOR SELECT DEPTNUMB, DEPTNAME, LOCATION FROM DB2ADMIN.ORG FORUPDATEOF DEPTNAME;-OPEN cursor1; FETCHFROM cursor1 INTO v_DEPTNUMB, V_DEPTNAME, v_LOCATION; UPDATE ORG SET DEPTNAME = 'NEW NAME' WHERECURRENTOF cursor1; END P1
5.4 MENGGUNAKAN PROSES CURSOR UNTUK MENGEMBALIKAN MULTIPLE RESULT SETS Stored procedures dibuat untuk mengembalikan multiple result sets pada aplikasi client (pemanggil) memerlukan : • AdanyaDYNAMIC RESULT SETS clause pada CREATE PROCEDURE statement. • Mendeklarasikan cursor untuk setiap result set dengan RETURN. • Meninggalkan semua cursor terbuka untuk mengambalikan result set ke aplikasi client (pemanggil).
CREATEPROCEDURE MULTIPLE_RESULTS ( ) DYNAMICRESULTSETS 2 -------------------------------------------------------------------------SQL Stored Procedure ----------------------------------------------------------------------------------------------P1: BEGIN --Declare cursors1 DECLARE cursor1 CURSORWITHRETURNFOR SELECT DEPTNAME FROM ORG; --Declare cursors2 DECLARE cursor2 CURSORWITHRETURNFOR SELECTDISTINCT DIVISION FROM ORG; --Cursor left open for client application OPEN cursor1; --Cursor left open for client application OPEN cursor2; END P1
5.5 CURSOR MEMPENGARUHI LOCKING Lock Modes:
– S(Share) Lock
Rows, tables o Pemilik dan aplikasi yang bersamaan dapat membaca, tidak meng-update.
– U(Update) Lock
Rows, tables o Pemilik dapat meng-update. Aplikasi yang bersamaan dapat membaca data dalam objek yang dikunci, tidak meng-update.
– X(Exclusive) Lock
Rows, tables o Pemilik dapat membaca dan meng-update. Aplikasi uncommitted read yang bersamaan dapat membaca data dalam objek yang terkunci.
Cursor dalam stored procedure dapat mempengaruhi aplikasi lain yang mengakses objek yang sama. Ketika table diakses, DB2 meletakkan lock pada table dan/atau row. Lock merubah keadaan dari yang akan mencegah siapapun membaca data dalam table untuk sebuah row lock terakhir selama membaca row tersebut. Type lock yang digunakan DB2 bergantung pada type cursor yang digunakan dan DB2 isolation level. 5.6 ISOLATION LEVEL
Uncommitted Read (UR) Dirty Read: Mengabaikan semua kunci pada baris dan table, mengijinkan aplikasi untuk membaca uncommitted data.
Cursor Stability (CS) Default (maximum concurrency): Menjaga current row dari pembacaan atau update dari perubahan oleh aplikasi lain.
Read Stability (RS) RS isolation mengijinkan aplikasi untuk membaca pages yang sama atau baris lebih dari satu kali ketika mencegah proses lain dari mengubah row.
Repeatable Read (RR) Baris yang di-refer sebuah aplikasi tidak dapat diupdate oleh aplikasi lain sebelum aplikasi tersebut mencapai commit poin.
LATIHAN 1. Buatlah tabel pegawai yang memiliki struktur seperti tabel employee. kemudian buat prosedur dengan nama multiple_4digitnimbelakang dengan ketentuan :
prosedur terdiri dari 2 result sets
buat cursor untuk memasukkan data dari tabel employee ke tabel pegawai (gunakan perulangan while atau loop)
result set 1 menampilkan pesan berhasil insert
result set 2 menampilkan semua data pegawai
2. Buatlah prosedur yang berfungsi untuk mengupdate salary pegawai tertentu dengan menggunakan cursor kemudian tampilkan hasilnya dengan multiple result sets
