nguyenanhque.sql.k3-2
--4.4 a:
select sodh,
case
when datename(dw,ngaydh)='monday' then 'Thứ Hai'
when datename(dw,ngaydh)='tuesday' then 'Thứ Ba'
when datename(dw,ngaydh)='wednesday'then'Thứ Tư'
when datename(dw,ngaydh)='thursday' then 'Thứ Năm'
when datename(dw,ngaydh)='friday' then 'Thứ Sáu'
when datename(dw,ngaydh)='saturday' then 'Thứ Bảy'
when datename(dw,ngaydh)='sunday' then 'Chủ Nhật'
else NULL
end as NGAY
from dondh
--4.4 b :
select Sopx,SLxuat,slxuat*dgxuat as ThanhTien,
case
when slxuat<4 then 'Ko Giam'
when slxuat>=4 and slxuat<10 then 'Giam 5%'
when slxuat>=10 and slxuat<20 then 'Giam 10%'
else 'Giam 20%'
end as KhuyenMai,
case
when slxuat<4 then slxuat*dgxuat
when slxuat>=4 and slxuat<10 then slxuat*dgxuat-(slxuat*dgxuat*5)/100
when slxuat>=10 and slxuat<20 then slxuat*dgxuat-(slxuat*dgxuat*10)/100
else slxuat*dgxuat-(slxuat*dgxuat*20)/100
end as GiaSaukhiKhuyenMai
from ctpxuat
--5.1 a : dang bi loi
alter proc spud_DONDH_TinhSLDat @Sodh char(4),@Mavtu char(4), @TongSLgDat int output
as
begin
select @TongSLgDat= sum(sldat)
from ctdondh,dondh
where ctdondh.mavtu=@mavtu and dondh.sodh=@sodh and ctdondh.sodh=dondh.sodh
end
exec spud_DONDH_TinhSLDat
--5.1 b :
alter proc spud_PNHAP_TinhTongSLNHang
@Sodh char(4),@mavtu char(4),@tongslnhap int output
as
begin
select @tongslnhap=sum(slnhap)
from ctpnhap,pnhap
where ctpnhap.mavtu=@mavtu and pnhap.sodh=@sodh and ctpnhap.sopn=pnhap.sopn
end
--Thuc hien:
Declare @a int
set @a=0
exec spud_PNHAP_TinhTongSLNHang 'D001','DD01',@a output
print 'Tong so luong nhap cua vat tu DD01 la:'+ convert(char(4),@a,)--cast(@a as char(4))
--5.1 c : chua lam dc
create procedure spud_TONKHO_TinhSLDau @namthang char(6),@mavtu char(4),@slbatki int output
as
begin
select @slbatki =
from tonkho
--5.2 a :
create proc spud_VATTU_THEM @mavtu char(4),@tenvtu char(100),@dvtinh varchar(10),@phantram real
as
begin
if((select count(mavtu) from vattu where mavtu = @mavtu )> 0)
begin
print'Ma vat tu nay da co trong bang VATTU'
end
else
begin
insert into vattu values(@mavtu,@tenvtu,@dvtinh,@phantram)
end
end
----- thuc hien thu coi,hehe:
exec spud_VATTU_THEM 'olal','thi thoi em','ta','20'
--5.2 b :
create proc spud_VATTU_XOA @mavtu char(4)
as
begin
if
(
(select count(mavtu) from ctdondh where mavtu=@mavtu)>0 and
(select count(mavtu) from ctpnhap where mavtu=@mavtu)>0 and
(select count(mavtu) from ctpxuat where mavtu=@mavtu)>0 and
(select count(mavtu) from tonkho where mavtu=@mavtu)>0
)
begin
print'Vat tu nay khong xoa duoc'
end
else
begin
delete from vattu where mavtu=@mavtu
end
end
------thuc hien thu coi:
exec spud_vattu_xoa 'olal'
--5.2 c :
alter proc spud_VATTU_update
@mavtu char(4),@tenvtu varchar(100),@donvtinh varchar(10),@phantram real
as
Begin
if((select count from vattu where mavtu=@mavtu)>0)
begin
update vattu
set tenvtu=@tenvtu, dvtinh=@donvtinh, phantram=@phantram
where mavtu=@mavtu
end
else
begin
print 'MaVTu nay khong co trong bang VATTU'
return
end
end
--Thuc Hien :
exec spud_VATTU_update 'CPUi',' central processing unit','Chiec','10'
--5.3 a :
create proc spud_VATTU_BcaoDanhSach
as
begin
select * from vattu
order by tenvtu
end
---thuc hien:
exec spud_VATTU_BcaoDanhSach
--5.3 b :
create proc spud_TONKHO_BcaoTonKho @namthang char(6)
as
begin
select namthang,tonkho.mavtu,tenvtu,sldau,tongsln,tongslx ,slcuoi
from tonkho,vattu
where namthang=@namthang and tonkho.mavtu=vattu.mavtu
end
----thuc hien:
exec spud_tonkho_bcaotonkho '200502'
--5.3 c :
create proc spud_PXUAT_BcaoPxuat
@Sopx char(4)=n
as
if(@sopx is null)
(select pxuat.sopx,ngayxuat,tenkh,mavtu,slxuat,dgxuat
from ctpxuat,pxuat
where pxuat.sopx=ctpxuat.sopx )
else
(select pxuat.sopx,ngayxuat,tenkh,mavtu,slxuat,dgxuat
from ctpxuat,pxuat
where pxuat.sopx=ctpxuat.sopx and pxuat.sopx=@sopx)
--thuc hien:
exec spud_PXUAT_BcaoPxuat ''
exec spud_PXUAT_BcaoPxuat 'X001'
--5.4 a : Thay Lam
create proc spud_DONDH_Them
@Sodh char(4),@manhacc char(4),@ngaydh datetime
as
begin
if(exists(select * from dondh where @sodh=sodh))
begin
print 'SoDH phai duy nhat.SoDH nay da co trong bang DONDH'
return
end
if(not exists(select * from manhacc where @manhacc=manhacc))
begin
print'Manhacc phai co trong bang NHACC'
return
end
if(@ngaydh is null)
insert into dondh values(@sodh,getdate(),@manhacc)
else
insert into dondh values(@sodh,@ngaydh,@manhacc)
end
--Thuc hien :
exec spud_DONDH_Them 'D006','01/01/2008','C01'
--5.4 b :
alter proc spud_DONDH_XOA @sodh char(4)
as
begin
if(exists(select sodh from pnhap where sodh=@sodh))
print'so dat hang '+@sodh+ ' da co trong bang PNHAP...Khong the xoa duoc !'
else
begin
delete from ctdondh from dondh where ctdondh.sodh=dondh.sodh and dondh.sodh=@sodh
delete from dondh where sodh=@sodh
end
end
--thuc hien:
exec spud_DONDH_XOA 'D002'
--5.4 f : thay lam
create proc supd_CTDONDH_Sua
@sodh char(4),@mavtu char(4),@sldat int
as
begin
if(not exists(select * from ctdondh where sodh=@sodh and mavtu=@mavtu))
begin
print'Sodh va Mavtu nay chua co trong bang DonDH'
return
end
declare @sldatmoi int, @tongslnhap int
select @sldatmoi=slat from ctdondh where sodh=@sodh and mavtu=@mavtu
select @tongslnhap=sum(slnhap)
from ctpnhap
where Mavtu=@Mavtu
if @sldatmoi<@tongslnhap
begin
print 'SlDatMoi phai >=Tong so luong da nhap'
return
end
update ctdondh
set sldat=@sldat
where sodh=@sodh and mavtu=@mavtu
end
--thuc hien :
exec spud_CTDONDH_Sua 'D001','DD01','15'
Bai 7.
-- cau 7.3a tao trigger
create trigger tg_pnhap_sua
on pnhap
for update
as
-- neu sua doi 2 cot sopn va sodh
if update (sopn) or update(sodh)
begin
Rollback tran
Raiserror (' khong the sua sopn hay sodh',16,1)
return
end
-- khi sua doi du lieu cot ngaynhap
if update(ngaynhap)
begin
-- tinh ra xem sodh da duoc dat hay chua
if exists ( select * from sodh)
begin
-- tinh ra ngay ngay dat hang cuoi cung
declare @maxngaydh datetime
select @maxngaydh=max(ngaydh)
from dondh, deleted
where dondh.sodh = deleted.sodh
if(@maxngaydh>(select ngaynhap from inserted))
begin
declare @loi char(200)
set @loi='ngay nhap hang phai sau '+ convert(char(10),@maxngaydh,103)
raiserror (@loi,16,1)
rollback tran
end
end
end
Bạn đang đọc truyện trên: Truyen247.Pro