Màu nền Màu tối Mặc định Xám nhạt Xanh nhạt Xanh đậm Vàng nhạt Màu sepia Vàng đậm Vàng ố

Font chữ: Palatino Linotype Bookerly Segoe UI Patrick Hand Times New Roman Verdana Tahoma Arial

Chiều cao dòng: 100 120 140 160 180 200

Kích cỡ chữ:

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