Bài thực hành Oracle (4 tuần) Bài 4_Cursor - Tùng Huynh
--Cau 1
declare
l_fullname varchar2(50);
cursor c_emp_fullname
select hr.employees.first_name||' '||hr.employees.last_name as "Full name"
from hr.employees;
--in tieu de
dbms_output.put_line('List employees');
--mo con tro
open c_emp_fullname;
--dung vong lap de lay het du lieu
loop
--day gia tri vao bien l_fullname
fetch c_emp_fullname into l_fullname;
--thoat khoi vong lap khi khong con du lieu
exit when c_emp_fullname%notfound;
--in ket qua ra man hinh
dbms_output.put_line(l_fullname);
--dong vong lap
end loop;
--dong con tro
close c_emp_fullname;
--bat loi
exception when others then
--neu co loi thi in ra loi
dbms_output.put_line('Error:'||sqlerrm);
end;
--cau 2
declare
l_job varchar2(35);
l_min number;
l_max number;
cursor c_jobs(p_saraly1 number,p_saraly2 number)
select hr.jobs.job_title,hr.jobs.min_salary,hr.jobs.max_salary
from hr.jobs
where hr.jobs.min_salary between p_saraly1 and p_saraly2
order by (hr.jobs.max_salary - hr.jobs.min_salary) desc;
--in tieu de
dbms_output.put_line('List jobs');
--mo con tro
open c_jobs(6000,15000);
--dung vong lap de lay het du lieu
loop
--day gia tri vao bien l_fullname
fetch c_jobs into l_job,l_min,l_max;
--thoat khoi vong lap khi khong con du lieu
exit when c_jobs%notfound;
--in ket qua ra man hinh
dbms_output.put_line(l_job||'-'||l_min||'-'||l_max);
--dong vong lap
end loop;
--dong con tro
close c_jobs;
--bat loi
exception when others then
--neu co loi thi in ra loi
dbms_output.put_line('Error:'||sqlerrm);
end;
--cau 3
create table tb_ad_depart(
id number not null,
department_name varchar2(30),
city varchar2(30),
country_name varchar2(40),
region_name varchar2(25)
--tao seq
create sequence seq_tb_ad_depart
increment by 1
start with 1
minvalue 1
maxvalue 999999999999999999999999999
nocycle
noorder
cache 20
declare
cursor c_ad_depart
select hr.departments.department_name,hr.locations.city,
hr.countries.country_name,hr.regions.region_name
from hr.departments,hr.locations,hr.countries,hr.regions
where hr.departments.location_id=hr.locations.location_id
and hr.locations.country_id=hr.countries.country_id
and hr.countries.region_id=hr.regions.region_id
order by hr.regions.region_name;
for item in c_ad_depart loop
insert into tb_ad_depart
values(
seq_tb_ad_depart.nextval,
item.department_name,
item.city,
item.country_name,
item.region_name
end loop;
if c_ad_depart%isopen then
close c_ad_depart;
end if;
commit;
dbms_output.put_line('Insert successfull');
--bat loi
exception when others then
--neu co loi thi in ra loi
dbms_output.put_line('Error:'||sqlerrm);
end;
--cau 4
declare
cursor c_jobs_class
select hr.jobs.job_title,(hr.jobs.min_salary+hr.jobs.max_salary) as average_salary
from hr.jobs
order by average_salary desc;
--in tieu de
dbms_output.put_line('Classification jobs');
for item in c_jobs_class loop
if item.average_salary < 10000 then
dbms_output.put_line(item.job_title||'-'||item.average_salary||'-C');
elsif item.average_salary >= 10000 and item.average_salary <20000 then
dbms_output.put_line(item.job_title||'-'||item.average_salary||'-B');
elsif item.average_salary >= 20000 then
dbms_output.put_line(item.job_title||'-'||item.average_salary||'-A');
end if;
end loop;
if c_jobs_class%isopen then
close c_jobs_class;
end if;
--bat loi
exception when others then
--neu co loi thi in ra loi
dbms_output.put_line('Error:'||sqlerrm);
end;
--cau 5
declare
cursor c_emp_com
select hr.employees.first_name||' '||hr.employees.last_name as "fullname",
to_char(hr.employees.commission_pct,'90.99') as com
from hr.employees;
--in tieu de
dbms_output.put_line('Employees and Commission');
for item in c_emp_com loop
if item.com is null then
dbms_output.put_line(item."fullname"||'-No commission');
else
dbms_output.put_line(item."fullname"||'-'||item.com);
end if;
end loop;
if c_emp_com%isopen then
close c_emp_com;
end if;
--bat loi
exception when others then
--neu co loi thi in ra loi
dbms_output.put_line('Error:'||sqlerrm);
end;
--cau 6
declare
l_no_dep varchar2(30);
l_no_man varchar2(30);
cursor c_emp_dep_man
select em.first_name||' '||em.last_name as employees,
de.department_name as Department,
trim(ma.first_name||' '||ma.last_name) as Manager
from hr.employees em,hr.departments de, hr.employees ma
where em.department_id =de.department_id(+)
and em.manager_id = ma.employee_id(+)
order by Manager;
--in tieu de
dbms_output.put_line('Employees, Department and Manager');
for item in c_emp_dep_man loop
if item.Department is null then
l_no_dep:='No department';
else
l_no_dep:=item.Department;
end if;
if item.Manager is null then
l_no_man:='No manager';
else
l_no_man:=item.Manager;
end if;
--in ra danh sach
dbms_output.put_line(item.employees||'-'||l_no_dep||'-'||l_no_man);
end loop;
if c_emp_dep_man%isopen then
close c_emp_dep_man;
end if;
dbms_output.put_line('Exc successfull');
--bat loi
exception when others then
--neu co loi thi in ra loi
dbms_output.put_line('Error:'||sqlerrm);
end;
Bạn đang đọc truyện trên: TruyenTop.Vip