Database Management

4.sql

-- 4.1
create or replace procedure GoiTen(mgvien giaovien.magv%type)
is 
  tengv giaovien.hoten%type;
  phaigv giaovien.phai%type;
begin
  select hoten, phai into tengv, phaigv from giaovien where magv = mgvien;
  if phaigv = '1' then
    dbms_output.put_line('Co ' || tengv);
  else 
    dbms_output.put_line('Thay ' || tengv);
  end if;
end;

set serveroutput on;
execute GoiTen('CT04');

-- 4.2 viet ham TongGioChuan nhan vao tham so ma giao vien, ham tra ve
-- tong so tiet chuan ma gv nay da giang day,
-- biet: so tiet qui chuan  = sotiet* heso, 
-- trong do heso la 1 neu si so thuoc [0..80]
-- 1.2 -> (80..120]
-- 1.4 -> (120..150]
--  15. -> (150++]

create or replace function TongGioChuan(mgv gd_0506.magv%type)
return number
is
  tongqc number;
  tongst gd_0506.sotiet%type;
  tongss gd_0506.siso%type;
begin
  select sum(sotiet), sum(siso) into tongst, tongss from gd_0506 where magv = mgv;
  if tongss >= 0 and tongss <= 80 then
    tongqc := tongst * 1;
  elsif tongss > 80 and tongss <= 120 then
    tongqc := tongst * 1.2;
  elsif tongss > 120 and tongss <= 150 then
    tongqc := tongst * 1.4;
  elsif tongss > 150 then
    tongqc := tongst * 1.5;
  end if;
  
  return tongqc;
end;

set serveroutput on;
declare
  tong number := TongGioChuan('CT03');
begin
  dbms_output.put_line(tong);
end;


-- 4.3 Viet ham TamUngCN tra ve ho ten cua gv co tam ung nhieu nhat tu truoc toi nay
create or replace function TamUngCN 
return number
is
  tongtamung number := 0;
  cursor bangttu is select magv, sum(sotien) as tong from tamung group by magv
                order by tong DESC;
begin
  for dong in bangttu
    loop
      if tongtamung < dong.tong then
        tongtamung := dong.tong;
      end if;
      
    end loop;
  return tongtamung;
end;

set serveroutput on;
declare
  tong number := TamUngCN;
begin
  dbms_output.put_line(tong);
end;

-- 4.4 Viet ham NgayTamUngGanNhat tra ve ngay co gv tam ung gan day nhat
create or replace function NgayTamUngGanNhat 
return date
is
  ngaytu date;
begin
  select ngay_ta into ngaytu from (select * from tamung order by ngay_ta desc) where rownum = 1;
  return ngaytu;
end;

set serveroutput on;
declare
  n date := NgayTamUngGanNhat;
begin
  dbms_output.put_line(n);
end;


-- 4.5 su dung ham tonggiochuan o cau 2 , viet thu tuc TontgGioTheoCDanh nhan vao tham so
-- ten chuc danh -> in ra ho ten gv cung voi tong gio qui chuan cua tung gv thuoc chuc danh do

create or replace procedure TontgGioTheoCDanh(cd giaovien.macd%type)
is
  cursor dsgvtheocd is select hoten, TongGioChuan(magv) as tgc from giaovien where macd = cd;
begin
  for dong in dsgvtheocd
    loop
      dbms_output.put_line(dong.hoten || '   ' || dong.tgc);    
    end loop;
end;

set serveroutput on;
execute TontgGioTheoCDanh('15.110');

-- 4.6 Viet thu tuc TgTamUngTrongTG nhan vao 2 moc thoi gian (tu ngay, den ngay) 
-- thu tuc in ra danh sach gom ho ten, tong so tien tam ung cua tung giao vien trong
-- khoang thoi gian da cho

create or replace procedure TgTamUngTrongTG(n1 tamung.ngay_ta%type, n2 tamung.ngay_ta%type)
is
  cursor dstutg is select tt.magv, gv.hoten, tt.tong from (select magv, sum(sotien) as tong 
                    from tamung
                    where ngay_ta >= n1 and ngay_ta <= n2
                    group by magv) tt
                    join giaovien gv on gv.magv = tt.magv;
                    
begin
  for dong in dstutg
    loop
      dbms_output.put_line(dong.hoten || '   ' || dong.tong);    
    end loop;
end;

set serveroutput on;
execute TgTamUngTrongTG('01-MAY-04', '01-MAY-05');

-- 4.7 su dung ham TongGioChuan, viet thu tuc TienGD nhan vao ma giao vien, tra ve
-- tong gio chuan, so gio vuot va so tien giang day cua GV do

create or replace procedure 
  TienGD(mgv giaovien.magv%type, gc OUT number, giovuot OUT number, tienday OUT number)
is                
begin
  select TongGioChuan(gv.magv), TongGioChuan(gv.magv)-cd.giochuan, TongGioChuan(gv.magv)*cd.tien_1tiet 
  into gc, giovuot, tienday
  from giaovien gv 
  join chucdanh cd on cd.macd = gv.macd where gv.magv = mgv;
end;

set serveroutput on;
declare
  gc number;
  giovuot number;
  tienday number;
begin
  TienGD('CT00', gc, giovuot, tienday);
  dbms_output.put_line(to_char(gc) || '   '|| to_char(giovuot) || '   '|| to_char(tienday));
end;

-- 4.8 Su dung thu tuc TienGD vua tao cau 7, viet thu tuc InBangLuong hien thi danh sach gom
-- ho ten, tong gio chuan da day, so gio vuot, va so tien giang day cua tung gv
--
create or replace procedure InBangLuong
is
  cursor bl is select magv, hoten from giaovien;
  gc number;
  giovuot number;
  tienday number;
begin
  dbms_output.put_line('Ho ten' || '     ' || 'Gio chuan' || '     '  || 'Gio vuot' || '     ' || 'Tien day');
  for d in bl
    loop
      TienGD(d.magv, gc, giovuot, tienday);
      dbms_output.put_line(d.hoten || '     ' || to_char(gc) || '     ' || to_char(giovuot) || '     ' || to_char(tienday));
    end loop;
end;

set serveroutput on;
execute InBangLuong;

procedure_function.sql

-- thay
--Viet thu tuc de in ra danh sach nhan vien cua mot phong ban. Biet ma phban la tham so cua thu tuc
create or replace procedure InDS(maphongban number)
is
  cursor DSNV is select * from EMP where deptno = maphongban;
begin
 for dong in DSNV
  loop
      dbms_output.put_line(Rpad(dong.empno,10)||Lpad(dong.ename,20));
  end loop;
end;


set serveroutput on;
execute InDS(20);


set serveroutput on;
declare
  MaPB number(2,0):=&Nhap_mapb;
begin
  InDS(20);
end;


-- Viet mot ham de tra tong so tien luong cua tat ca nhan vien trong mot phong ban nao do
create or replace function TongLuong(mapb emp.deptno%type)
return number
is
  tongluong number(8,2);
begin
  select sum(sal) into tongluong from EMP where deptno = mapb;
  return tongluong;
end;

set serveroutput on;
declare
  Luong number(8,0);
begin
  Luong := TongLuong(20);
  dbms_output.put_line(Luong);
end;


-- Cho 1 ma nv=> In ra nguoi quan ly truc tiep cung nhu gian tiep

create or replace procedure InSepLongVong(manv emp.empno%type)
is
  masep_tam emp.empno%type;
  tensep_tam emp.ename%type;
  manv_tam emp.empno%type;
begin
  manv_tam:= manv;
  loop
      select sep.empno, sep.ename into masep_tam, tensep_tam
      from EMP nv, EMP sep
      where nv.empno = manv_tam and
          nv.mgr= sep.empno;
      dbms_output.put_line(masep_tam || ' - '|| tensep_tam);  
      manv_tam:= masep_tam;
  end loop;
  exception when no_data_found then
      dbms_output.put_line('Ket thuc!');
      
end;



set serveroutput on;
execute InSepLongVong(7369);


-- Bai tap: 1(2), cau 2(3), cau 3(3), Cau 4(8)


--Scott

-- Procedure
--1.1 
create or replace procedure TenThang(ngaythang varchar2)
is
  thang varchar(20);
begin
  select to_char(to_date(ngaythang, 'DD-MM-YYYY'), 'Month') into thang from dual;
  dbms_output.put_line(thang);
end;

set serveroutput on;
declare
  nt varchar(30) := '&Nhap_ngay_thang';
begin
  TenThang(nt);
end;

--1.2
create  or replace procedure LayTen(hoten varchar2)
is
  ten varchar2(10);
begin
  select substr(hoten, INSTR(hoten, ' ', -1)+1) into ten from dual;
  dbms_output.put_line(ten);
end;

set serveroutput on;
execute LayTen('Dang Ngoc Trinh Tu');

--2.1
create or replace procedure TruTienThuong(mv emp.empno%type, giam float)
is
  
begin
  update emp set comm = comm - comm * (giam/100) where empno = mv;
end;

execute TruTienThuong(7499, 10);

-- 2.2
create or replace function NVfun
return number
is
  salmax emp.sal%type; 
  manv number(4,0);
begin
  select max(sal) into salmax  from EMP;
  select empno into manv from EMP where sal = salmax;
  return manv;
end;

set serveroutput on;
declare
  mnv number(4,0);
begin
  mnv := NVfun;
  dbms_output.put_line(mnv);
end;

-- 2.3
create or replace procedure InSepDeQuy(manv emp.empno%type)
is
  masep_tam emp.empno%type;
  tensep_tam emp.ename%type;
  manv_tam emp.empno%type;
begin
  manv_tam:= manv;
  loop
      select sep.empno, sep.ename into masep_tam, tensep_tam
      from EMP nv, EMP sep
      where nv.empno = manv_tam and
          nv.mgr= sep.empno;
      dbms_output.put_line(masep_tam || ' - '|| tensep_tam);  
      manv_tam:= masep_tam;
  end loop;
  exception when no_data_found then
      dbms_output.put_line('Ket thuc!');    
end;

set serveroutput on;
execute InSepDeQuy(7369);


-- 3.3
create or replace procedure ThemPhongBan(tenphong dept.dname%type, vitriphong dept.loc%type)
is 
  maxdeptno dept.deptno%type;
begin
  select max(deptno) into maxdeptno from dept;
  insert into dept values(maxdeptno+10, tenphong, vitriphong);
end;

set serveroutput on;
declare
  tp dept.dname%type := '&Nhap_ten_phong';
  vt dept.loc%type := '&Nhap_vi_tri';
begin
  ThemPhongBan(tp, vt);
end;


-- hr
-- 3.1
select employee_id, first_name || ' ' || last_name as full_name 
from employees
where length(first_name || ' ' || last_name) > 15;

-- 3.2 
-- thu tuc muc luong
create or replace procedure MucLuong 
  (p_jobid jobs.job_id%type, 
  min_lg OUT jobs.min_salary%type,
  max_lg OUT jobs.max_salary%type)
is

begin
  select min_salary, max_salary into min_lg, max_lg from jobs where job_id = p_jobid;
  exception
    when no_data_found then
      dbms_output.put_line('Ma cong viec ' || p_jobid || ' khong tim thay');
end;

set serveroutput on;
declare
  min1 jobs.min_salary%type;
  max1 jobs.max_salary%type;
begin
  MucLuong('AD_VP', min1, max1);
  dbms_output.put_line('Min = ' || to_char(min1) || 'Max = ' || to_char(max1));
end;

select * from employees

select * from jobs

trigger.sql

-- baitaphd
create table NHATKY
(
  Nguoixoa varchar2(20),
  ngayxoa date,
  manv number(4,0),
  tennv varchar2(20)
)

-- Theo dong lenh: quan tam nhieu dong du lieu
-- Theo dong du lieu: quan tam tung dong du lieu: for each row; :old (delete, update), :new (insert, update)

create or replace trigger THEODOIXOA
after delete on EMP
for each row
declare
  
begin
  insert into NHATKY values(user, sysdate, :old.empno, :old.ename);

end;

-- Them mot cot co ten la QUYLUONG  trong bang DEPT: Luu lai luong se tra cho nv

select deptno, sum(sal)
from emp
group by deptno;


alter table DEPT
add QUYLUONG number(8,2)

create or replace trigger THAYDOINS
after insert or update of deptno, sal on EMP
declare
  cursor DSLUONG_DTRA is
        select deptno, sum(sal) as luongdtra
        from emp
        group by deptno;
  qluong number(8,2);
begin
   for dong in DSLUONG_DTRA
     loop
         select quyluong into qluong from DEPT where deptno = dong.DEPTNO;
         if qluong <dong.luongDtra then
            raise_application_error(-20001,'Khong the thay doi nhan su');
         end if;
     end loop;
end;




-- trigger
-- cau 7
create table GIABAN (
  MAHG char(2),
  DGIA number(6,2)
)

create or replace trigger capnhatdongiahanghoa
after insert or update of dgia on HANGHOA
for each row
declare
  
begin
  insert into GIABAN values(:new.mahg, :new.dgia);
end;

-- cau 8
-- 
-- tham cot TongTriGia
alter table CT_DDH add TongTriGia number(8, 2);
-- cap nhat gia tri cho TongTriGia= sl*dongia
update CT_DDH set TongTriGia = SL*DONGIA;
select * from CT_DDH

create or replace trigger capnhatchotrigia
after insert or update of SL, DonGia on CT_DDH
declare
  cursor cn is
              select * from ct_ddh;
begin
  for i in cn
    loop
      update CT_DDH set tongtrigia = i.sl * i.dongia where MADDH = i.maddh and mahg = i.mahg;
    end loop;
end;

-- cau 9
-- trong bang schema banhang, hay tao trigger de kiem tra viec cap nhat don gia
-- cua mot mat hang: gia moi k dc cao hon gia cu qua 10%

create or replace trigger capnhatdongia
after update of DGIA on HANGHOA
for each row
declare

begin
  if :new.DGIA  > :old.DGIA*1.1 then
    raise_application_error(-20001,'Gia moi cao hon gia cu qua 10%!');
  end if;
end;

--Cau 6:Trong CSDL 5 viet trigger de kiem tra viec xen du lieu(INSERT). TRong 1 thang: Toi da 2 lan va toi da la 3chai
-- dieu kien bat loi: Solan >2 OR TongTien >3.000.000

create or replace trigger KHONGCHOMUONTIEN
before insert on TAMUNG
for each row
declare
 solan int;
 tongtien number(10,2);

begin
  select count(*)+1, sum(sotien)+:new.sotien into solan, tongtien from TAMUNG where magv =:new.magv and
          extract(month from :new.ngay_ta) = extract(month from ngay_ta) and
          extract(year from :new.ngay_ta) = extract(year from ngay_ta);
  
  if solan > 2 or tongtien>3000000 then
    raise_application_error(-20004,'Khong cho muon');
  end if;

end;

--Cau 8: 
declare
  cursor donhang is 
    select maddh, sum(sl*dongia)as tongtien
    from ct_ddh
    group by maddh;

begin
   for dong in donhang
    loop
      update donhang set trigia = dong.tongtien where maddh=dong.maddh;
    end loop;

end;



create or replace trigger CAPNHATTRIGIA
after update of sl, dongia or insert on CT_DDH
declare
  cursor trigiahoadon is 
             select maddh, sum(sl*dongia)as tongtien
            from ct_ddh
            group by maddh;
begin
  for dong in trigiahoadon
    loop
      update donhang set trigia = dong.tongtien where maddh=dong.maddh;
    end loop;

end;