CURSOR (Penjelasan Lengkap)

Postingan ini menyempurnakan postingan sebelumnya yang juga membahas tentang Cursor, tapi karena informasi yang belum terlalu lengkap, maka postingan ini menyempurnakan yang sebelumnya.

CURSOR
Pada block PL/SQL kita tidak bisa menampilkan beberapa baris dengan menggunakan perintah SELECT secara langsung. Untuk mengatasi hal tersebut, maka kita dapat menggunakan sebuah kursor. Berbeda dengan variabel skalar, cursor dapat menampung banyak nilai berupa baris atau record. Nilai-nilai yang disimpan dalam sebuah kursor kemudian dapat dimanipulasi sehingga dapat digunakan sesuai kebutuhan.

Cursor sendiri terdiri dari 2 tipe :

1. Implicit Cursor
   Merupakan Cursor yang dideeklarasikan dan dikelola secara otomatis oleh PL/SQL untuk semua operasi DML SQL dan PL/SQL SELECT Statement
2. Eksplicit Cursor
   Merupakan Cursor yang dikelola dan dideklarasikan oleh Programmer

Nb : Sebelum memulai mempelajari CURSOR ada baiknya mempelajari metode Looping terlebih dahulu.

Dalam Membuat cursor terdapat 4 tahap yang harus kita lakukan yaitu :

1. DECLARE
   DECLARE digunakan untuk mendeklarasikan cursor yang akan kita gunakan
2. OPEN
   OPEN digunakan untuk mengaktifkan cursor yang akan kita gunakan
3. FETCH
   FETCH digunakan untuk mengambil data dari cursor,  dan kemudian data tersebut kita tampung ke dalam satu atau beberapa variabel.
4. CLOSE
   CLOSE digunakan untuk menonaktifkan atau menutup cursor yang kita gunakan
   
   

Sintax Deklarasi Cursor :

CURSOR cursor_name IS
select_statement;

Contoh :

DECLARE
CURSOR cur_emp IS
SELECT employee_id, last_name FROM employees WHEREdepartment_id = 30

Cursor diatas kita deklarasikan dengan menggunakan nama cur_emp dan cursor tersebut menunjuk atau berisi data employee_id, last_name dari tabel employees yang mempunyai department id  = 30

Contoh OPEN CURSOR :

DECLARE
CURSOR cur_emp IS
SELECT employee_id, last_name FROM employees WHEREdepartment_id = 30;
BEGIN
OPEN cur_emp;

Contoh FETCH CURSOR :

DECLARE
CURSOR cur_emp IS
SELECT employee_id, last_name FROM employees WHEREdepartment_id = 30;
empID employees.employee_id%TYPE;
Name employees.last_name%TYPE;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO empID, Name ;

Contoh Lengkap :

SET SERVEROUTPUT ON

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name, salary FROM Employees
WHERE department_id = 20;
empId employees.employee_id%type;
name employees.last_name%type;
Sal employees.salary%type;
BEGIN

OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empID, Name, Sal ;
EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(empID||’ ‘||Name||’ ‘||Sal);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘jumlah Data ‘||’ ‘||emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
END;

Penjelasan :

Cursor diatas dideklarasikan dengan nama emp_cur yang berisi data employee_id, last_name dari tabel employees dengan department_id =30. lalu dibuat 3 buah variabel baru yang bernama empID, Name, dan Sal dengan tipe data reference. Karena employee_id pada department  id = 30 terdapat beberapa employee, maka kita gunakan sebuah metode Looping yang akan secara otomatis akan menunjuk data satu persatu yang mempunyai department_id =30.

Berhenti ketika Cursor tersebut tidak menemukan data yang mempunyai department id = 30. Setelah itu datanya kita FETCH dan kemudian ditampung ke dalam variabel empId, Name, dan Sal. Lalu kita tampilkan hasilnya dengan menggunnakan DBMS_OUTPUT.PUT_LINE. Dan terakhir cursor tersebut ditutup.

CURSOR Dengan RECORD

SET SERVEROUTPUT ON

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name, Salary FROM employees
WHERE department_id = 30;
emp_record emp_cursor%ROWTYPE;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id||’ ‘ ||emp_record.last_name||’ ‘||emp_record.salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Jumlah Data Yang Diambil: ‘||emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
END;

CURSOR dengan  FOR

SET SERVEROUTPUT ON

DECLARE
CURSOR emp_cur IS
SELECT employee_id, Last_name FROM employees
WHERE department_id = 30;
BEGIN
FOR emp_rec IN Emp_cur
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || emp_rec.last_name);
END LOOP;

END;

Dengan menggunkana metode pengulangan FOR,  kita tidak perlu lagi mendeklarasikan emp_rec karena record tersebut secara implisit telah dideklarasikan. Selain itu, kita juga tidak perlu mendeklarasikan OPEN, FETCH, EXIT, dan CLOSE seperti saat kita menggunakan metode BASIC LOOP  karena perintah tersebut juga secara implisit telah dideklarasikan. Dan Pada contoh diatas, emp_record akan berisi semua data pada Emp_Cur

CURSOR dengan FOR SUBQUERY

SET SERVEROUTPUT ON

BEGIN
FOR emp_record IN (SELECT employee_id, last_name, salary FROMemployees WHERE salary < 5000)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||’ ‘|| emp_record.last_name||’ ‘||emp_record.salary);
END LOOP;
END;

Dengan menggunakan FOR SubQuery kita tidak perlu lagi mendeklarasikan cursor.

UPDATE dengan CURSOR

Salah satu kemampuan cursor yaitu cursor dapat menunjuk sekaligus mengUpdate banyak data di dalam database. Untuk melakukan proses tersebut kita membutuhkan keyword UPDATE dan keyword WHERE CURRENT OF.

Keyword Update, digunakan untuk mendefinisikan apa tujuan dari cursor tersebut dibuat, sedangkan WHERE CURRENT OF digunakan untuk menunjukan data yang mana yang ingin di UPDATE. Satu hal yang perlu kita ketahui, keyword UPDATE disini bukan hanya sebatas untuk merubah data menjadi data yang baru, tetapi juga termasuk UPDATE untuk menghilangkan atau menghapus data.

contoh UPDATE:

data sebelum di update

DECLARE

CURSOR emp_cursor IS
SELECT employee_id FROM emp
WHERE department_id = 20
FOR UPDATE OF Salary;

empId employees.employee_id%TYPE;

BEGIN

FOR emp_upd IN emp_cursor
LOOP
UPDATE emp
SET salary = 5000
WHERE CURRENT OF emp_cursor;
END LOOP;
COMMIT;
END;

data setelah di update

Penjelasan :

Cursor diatas digunakan untuk merubah salary pada tabel empoloyees menjadi 5000, dimana salary yang dirubah adalah karyawan yang employee_id nya terdapat pada department_id = 20

contoh DELETE :

DECLARE

CURSOR emp_cursor IS
SELECT employee_id FROM emp
WHERE department_id = 20
FOR UPDATE OF Salary;

empId employees.employee_id%TYPE;

BEGIN

FOR emp_upd IN emp_cursor
LOOP
DELETE FROM emp

WHERE CURRENT OF emp_cursor;
END LOOP;
COMMIT;
END;

sumber: http://risnotes.com/2012/02/cursor/