30109376
PIS 0904
cursor IMPLISIT & EKSPLISIT
cursor implisit
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’;
contoh
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
email employees.email%type;
telepon employees.phone_number%type;
begin
nama_cari:='&masukan_nama';
select first_name, email, phone_number
into nama_depan, email, telepon
from employees
where first_name = nama_cari;
dbms_output.put_line(' pegawai bernama '|| nama_depan ||' emailnya adalah '|| email ||' dan no telepon adalah '|| telepon);
end;
/
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_pekerjaan jobs.job_title%type;
gaji employees.salary%type;
begin
nama_cari:='&masukan_nama';
select first_name, job_title, salary
into nama_depan, nama_pekerjaan, gaji
from employees
natural join jobs
where first_name = nama_cari;
dbms_output.put_line(' pegawai bernama '|| nama_depan ||' pekerjaan adalah '|| nama_pekerjaan||' gaji '|| gaji);
end;
/
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_pekerjaan jobs.job_title%type;
gaji employees.salary%type;
begin
nama_cari:='&masukan_nama';
select first_name, job_title, salary
into nama_depan, nama_pekerjaan, gaji
from employees
natural join jobs
where first_name = nama_cari;
dbms_output.put_line(' pegawai bernama '|| nama_depan ||' pekerjaan adalah '|| nama_pekerjaan||' gaji '|| gaji);
if(gaji>5000)then
dbms_output.put_line( nama_depan ||' gajinya gede ');
else
dbms_output.put_line( nama_depan ||' gaji kecil ');
end if;
end;
/
declare
gaji employees.salary%type;
begin
gaji:=&masukan_gaji;
select count(salary)
into gaji
from employees
where salary=gaji;
dbms_output.put_line(' jumlah pegawai '|| gaji);
end;
/
cursor eksplisit
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
declare
nama_cari employees.first_name%type;
nama_depan employees.first_name%type;
nama_belakang employees.last_name%type;
gaji employees.salary%type;
cursor a is
select first_name, last_name, salary
from employees
where first_name = nama_cari;
begin
nama_cari:='&masukan_nama';
open anu;
loop
fetch a 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 a;
end;
/
declare
nama_cari varchar(15);
nama_depan varchar(15);
nama_belakang varchar(15);
gaji varchar(10);
cursor x is
select first_name, last_name, salary
from employees
where first_name = nama_cari;
begin
nama_cari := '&masukkan_nama';
open x;
loop
fetch x into nama_depan, nama_belakang, gaji;
exit when x%notfound;
dbms_output.put_line('pegawai bernama '||nama_depan||' ' ||nama_belakang||' gajinya adalah USD '||gaji);
end loop;
close x;
end;
/
declare
id_karyawan employees.employee_id%type;
nama_depan employees.first_name%type;
gaji_cari employees.salary%type;
cursor a is
select employee_id, first_name, salary
from employees
where salary = gaji_cari;
begin
gaji_cari := '&masukkan_gaji';
open a;
dbms_output.put_line('---------------------------------------- ' );
dbms_output.put_line('|ID karyawan '|| ' |Nama '||'|Gaji| ' );
dbms_output.put_line('---------------------------------------- ' );
loop
fetch a into id_karyawan, nama_depan, gaji_cari;
exit when a%notfound;
dbms_output.put_line( '|' || id_karyawan || '|' || nama_depan || ' |' || gaji_cari || '|' );
end loop;
dbms_output.put_line('---------------------------------------- ' );
close a;
end;
/
declare
id_karyawan employees.employee_id%type;
pekerjaan_cari jobs.job_title%type;
gaji employees.salary%type;
cursor b is
select A.employee_id, B.job_title, A.salary
from employees A join jobs B on A.job_id = B.job_id where B.job_title='&pekerjaan';
begin
open b;
dbms_output.put_line('---------------------------------------- ' );
dbms_output.put_line('|ID karyawan '|| ' |Pekerjaan '||'|Gaji| ' );
dbms_output.put_line('---------------------------------------- ' );
loop
fetch b into id_karyawan, pekerjaan_cari, gaji;
exit when b%notfound;
dbms_output.put_line( '|' || id_karyawan ||'|'|| pekerjaan_cari ||' |'|| gaji || '|' );
end loop;
dbms_output.put_line('---------------------------------------- ' );
close b;
end;
/
declare
id_department_cari departments.department_id%type;
nama_department departments.department_name%type;
gaji_maks jobs.max_salary%type;
gaji_min jobs.min_salary%type;
cursor c is
select departments.department_id, department_name, max(salary), min(salary) from employees
join departments on employees.department_id=departments.department_id
group by departments.department_id, department_name;
begin
id_department_cari := '&id_department';
open c;
dbms_output.put_line('--------------------------------------------------------------------- ' );
dbms_output.put_line('|ID Department '|| ' |Nama Department '||'|Gaji Maks '|| '|Gaji Min|| ');
dbms_output.put_line('--------------------------------------------------------------------- ' );
loop
fetch c into id_department_cari, nama_department, gaji_maks, gaji_min;
exit when c%notfound;
dbms_output.put_line( '|' || id_department_cari ||'|'|| nama_department ||' |'|| gaji_maks || '|'|| gaji_min || '|' );
end loop;
dbms_output.put_line('---------------------------------------------------------------------- ' );
close c;
end;
/
0 komentar:
Posting Komentar