Minggu, 31 Oktober 2010

Cursor Explisit Implisit in oracle

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;
/
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;
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;
/
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;
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;
/

Diberdayakan oleh Blogger.

Blogger template by AdsenseBloggerTemplates | Original design by andrastudio

Powered by Blogger