Example cursor explisit and cursor implisit in oracle
cursor explisit
select [daftar kolom]
into [daftar variabel]
from [daftar tabel]
where [syarat]
------------------------------------------------------------
select first_name, last_name, salary
into nama_depan, nama_belakang, gaji
from employees
where first_name = ‘Susan’;
1. declare
nama_cari varchar(15);
nama_depan varchar(15);
nama_belakang varchar(15);
gaji varchar(10);
------------------------------------------------------------------
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;
2. declare
nama_cari varchar(15);
nama_depan varchar(15);
nama_belakang varchar(15);
gaji varchar(10);
begin
nama_cari := '&masukkan_nama';
select first_name, last_name, salary
into nama_depan, nama_belakang, gaji
from employees
where first_name = nama_cari;
dbms_output.put_line('pegawai bernama '||nama_depan||’ ‘ ||nama_belakang||' gajinya adalah USD '||gaji);
end;
/
3. declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;
begin
nama_cari := '&masukkan_nama';
select first_name, last_name, salary
into nama_depan, nama_belakang, gaji
from employees
where first_name = nama_cari;
dbms_output.put_line('pegawai bernama '||nama_depan||
' '||nama_belakang||' gajinya adalah USD '||gaji);
end;
/
Jawaban
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
alamat_email employees.email%type;
telepon employees.phone_number%type;
begin
nama_cari := '&masukkan_nama';
select first_name, email, phone_number
into nama_depan, alamat_email, telepon
from employees
where first_name = nama_cari;
dbms_output.put_line('Nama: '||nama_depan);
dbms_output.put_line('Email: '||alamat_email||'@SemogaJaya.co.id');
dbms_output.put_line('Telepon: '||telepon);
end;
/
Jawaban
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_job jobs.job_title%type;
gaji employees.salary%type;
begin
nama_cari := '&masukkan_nama';
select first_name, job_title, salary
into nama_depan, nama_job, gaji
from employees, jobs
where employees.job_id = jobs.job_id
and first_name = nama_cari;
dbms_output.put_line('pegawai bernama '||nama_depan||' pekerjaannya sebagai '||nama_job||' dan gajinya adalah USD '||gaji);
end;
/
cursor implisit
DOFC à (DECLARE, OPEN, FETCH, CLOSE)
Declare
cursor [nama_cursor_bebas] is
select [daftar_kolom]
from [daftar_tabel]
where [syarat];
begin
open [nama_cursor_bebas];
loop
fetch [nama_cursor_bebas] into [daftar_variabel];
exit when [nama_cursor_bebas] %notfound;
[aksi_lain]
end loop;
close [nama_cursor_bebas];
end;
/
Contoh
1. declare
nama_cari varchar(15);
nama_depan varchar(15);
nama_belakang varchar(15);
gaji varchar(10);
cursor anu is
select first_name, last_name, salary
from employees
where first_name = nama_cari;
begin
nama_cari := '&masukkan_nama';
open anu;
loop
fetch anu into nama_depan, nama_belakang, gaji;
exit when anu%notfound;
dbms_output.put_line('pegawai bernama '||nama_depan||' ' ||nama_belakang||' gajinya adalah USD '||gaji);
end loop;
close anu;
end;
/
2. declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;
cursor anu is
select first_name, last_name, salary
from employees
where first_name = nama_cari;
begin
nama_cari := '&masukkan_nama';
open anu;
loop
fetch anu into nama_depan, nama_belakang, gaji;
exit when anu%notfound;
dbms_output.put_line('pegawai bernama '||nama_depan||' ' ||nama_belakang||' gajinya adalah USD '||gaji);
end loop;
close anu;
end;
/
Latihan Menulis
Jawaban
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
alamat_email employees.email%type;
telepon employees.phone_number%type;
cursor anu is
select first_name, email, phone_number
from employees
where first_name = nama_cari;
begin
nama_cari := '&masukkan_nama';
open anu;
loop
fetch anu into nama_depan, alamat_email, telepon;
exit when anu%notfound;
dbms_output.put_line('Nama: '||nama_depan);
dbms_output.put_line('Email: '||alamat_email||'@SemogaJaya.co.id');
dbms_output.put_line('Telepon: '||telepon);
dbms_output.put_line(‘-----------------------------------------------------------’);
end loop;
close anu;
end;
/
Latihan Menulis
Jawaban
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_job jobs.job_title%type;
gaji employees.salary%type;
cursor anu is
select first_name, job_title, salary
from employees, jobs
where employees.job_id = jobs.job_id
and first_name = nama_cari;
begin
nama_cari := '&masukkan_nama';
open anu;
loop
fetch anu into nama_depan, nama_job, gaji;
exit when anu%notfound;
dbms_output.put_line('pegawai bernama '||nama_depan||' pekerjaannya sebagai '||nama_job||' dan gajinya adalah USD '||gaji);
end loop;
close anu;
end;
/