Trang Chính
Posts
Coins
Thanked
Tiêu đề: Nhóm 4 QT CSDL thực thi phần index THÔNG BÁO Đăng kýthông báo đăng ký để phân công làm phần index
các bạn trong nhóm đăng ký cho bạn danh Được với nick là tinhanhnoixaxu
đăng ký trong hạn là nay thứ 5 ngày 27/5/2010 tới hết ngày chủ nhật 30/5/2010
bạn hoàng đưa tài liệu mình ko hiểu mong bạn hoàng giải thích lại cho các bạn trong nhóm
- Trích dẫn :
1
select idchiphi from
tonghopngansach where tungay >= ngaybatdau and
denngay <= ngayketthuc
while iDchiPhi_cursor%found
loop
if
idchiphi=idchiphitemp then
select
sotien
into
soTien1id
from
tonghopngansach
where
idchiphi=idchiphitemp;
tongSoTien:=tongSoTien+soTien1id;
end if;
fetch
idChiPhi_cursor into idchiphitemp;
end loop;
insert into tonghopngansach(idchiphi,tungay,denngay,sotien,loaingansach)
values(idchiphi,ngaybatdau,ngayketthuc,tongSoTien,hinhthucngansach);
while iDchiPhi_cursor%found
loop
select
sotien
into
soTien1id
from
tonghopngansach
where
idchiphi=idchiphitemp;
tongSoTien:=tongSoTien+soTien1id;
fetch idChiPhi_cursor
into idchiphitemp;
end loop;
insert into tonghopngansach(idchiphi,tungay,denngay,sotien,loaingansach)
values ('Tong Hop',ngaybatdau,ngayketthuc,tongSoTien,hinhthucngansach);
TONGHOPNGANSACH(IDCHIPHI,SOTIEN,TUNGAY,DENNGAY)
KHACHHANG
(TENKH,
TENDAYDU,
DIENGIAI,
DOANHTHU,
KH.NGAYTHANHLAPCONGTY,
NGAYTIMTHAY,
NGAYLIENHEKETIEP
IDKHOIKHACHHANG)
HOATDONG
(TIEUDE,
NOIDUNG,
TRANGTHAI,
IDKHACHHANG,
NGUOILIENHE,
SONV,
NGUOICN,
NGAYCN,
NGAYTHUCHIEN,
NGAYDUKIEN,
HINHTHUC)
CHITIETHOATDONG
(NGAYGIODKKETTHUC,
NGAYGIODKBATDAU
ID)
NHANVIENHOATDONG
(IDHOATDONG)
NHATKY
(IDNHATKY
TIEUDE
IDHOATDONG
IDKHACH
IDLIENHE
NGAYCN
NGUOICN)
TIENTRINHBANHANG
(TRANGTHAI,
TIEUDE,
DIENGIAI,
IDKHACHHANG)
DOITHU_TIENTRINHBANHANG
(IDTIENTRINHBANHANG
IDDOITHU)
SANPHAM
(IDDOITHU
IDNHOMSANPHAM
LASPCUATA)
DIEMMANH
(IDDOITHU)
DIEMYEU
(IDDOITHU)
KHOIKHACHHANG
()
KHUCTHITRUONG
(IDKHOIKHACHHANG)
HOADON
(IDKHACHHANG,
SOSERIES,
TONGGT)
CHITIETHOADON
(IDSANPHAM
IDTHUESUAT)
CHITIETHOPDONG
(IDTHUESUAT)
HOPDONG
(TONGGT,
IDNHANVIEN)
DONHANG
(TONGGT)
CHITIETDONHANG
(IDTHUESUAT)
2
select kh1.ID, kh1.TenKH, kh1.TenDayDuKH
from
Khachhang kh1
where kh1.ngaysinh >= Ngay1 and kh1.ngaysinh <= Ngay2
3
select kh.ID, kh.TenKH,
kh.TenDayDuKH
from khachhang kh
where thoiGianDau<=kh.ngaythanhlapcongty and kh.ngaythanhlapcongty<=thoiGianSau
4
select kh.ID, kh.TenKH,
kh.TenDayDuKH
from khachhang kh
where thoiGianDau<=kh.ngaytimthay and kh.ngaytimthay<=thoiGianSau
5
select kh.ID, kh.TenKH,
kh.TenDayDuKH
from
KhachHang kh
where kh.NgayLienHeGanNhat
>= Ngay1 and
kh.NgayLienHeGanNhat <=
Ngay2
6
select kh.ID, kh.TenKH,
kh.TenDayDuKH
from
KhachHang kh
where kh.NgayLienHeKeTiep
>= Ngay1 and
kh.NgayLienHeKeTiep <=
Ngay2
7
select hd.ID, hd.TieuDe, hd.NoiDung, hd.NgayDuKien
from
HoatDong hd
where hd.NgayDuKien >= Ngay1 and hd.NgayDuKien <=
Ngay2 and hd.HinhThuc
= HinhThuc
8
select to_char(cthd.ngaygiodkketthuc-cthd.ngaygiodkbatdau,'dd')
into
TongThoiGianDuKien
from
chitiethoatdong cthd
where cthd.idhoatdong
= ID;
9
select id into
idtemp from hoatdong hd where hd.id=idhoatdong;
select hd.trangthai into tthai from
hoatdong hd where hd.id=idhoatdong;
select hd.tieude, hd.idkhachhang, hd.idnguoilienhe, hd.sonv, hd.nguoicn, hd.ngaycn
into
tde, idkhach,
idlienhe, nv,
nguoicn, ngaycn
from
hoatdong hd
where
hd.id=idhoatdong;
idnhatky := 'nk001';
insert into
nhatky values(idnhatky,tde,idhoatdong,null,idkhach,idlienhe,'0',null,null,nv,null,null,null,ngaycn, nguoicn);
select
ct.ngaygiodkbatdau,
ct.ngaygiodkketthuc
into
ngaydukienbd, ngaydukienkt
from
hoatdong hd, chitiethoatdong ct
where
hd.id=idhoatdong
and ct.idhoatdong= hd.id;
insert into chitietnhatky values('1',idnhatky,
ngaydukienbd, ngaydukienkt,null);
update
hoatdong set trangthai=1
where id=idhoatdong;
10
select id into idtemp from
nhanvien where id=
idnv;
select
hd.hanthuchien -
hd.ngaydukien into
ngaycong
from
hoatdong hd, nhanvienhoatdong nvhd
where
nvhd.idnhanvien=
idnv
and
hd.id= nvhd.idhoatdong
and
hd.ngaydukien between
tgbd and tgkt
and
hd.hanthuchien between
tgbd and tgkt;
11
select count(*) into ChoQuyetDinh
from TIENTRINHBANHANG TT where TT.TRANGTHAI='0';
select count(*)into ThanhCong from TIENTRINHBANHANG TT where
TT.TRANGTHAI='1';
select count(*) into MatCoHoi from TIENTRINHBANHANG TT where
TT.TRANGTHAI='2';
select count(*) into Tong from TIENTRINHBANHANG;
12
select SP.ID,SP.IDDOITHU,SP.IDNHOMSANPHAM
from SANPHAM SP,NHOMSANPHAM
NSP
where SP.IDDOITHU =
IDDOITHU AND
SP.IDNHOMSANPHAM =
NSP.ID AND SP.LaSPCuaTa = '1'
13
SELECT
TempFinal.ID
,TempFinal.TieuDe
,TempFinal.DienGiai
,TempFinal.TenKH
FROM
(SELECT kh.TenKH
FROM
KhachHang kh
JOIN
(SELECT ttbh.ID
,ttbh.TieuDe
,ttbh.DienGiai
,ttbh.IDKhachHang
FROM
TienTrinhBanHang ttbh
WHERE
ttbh.ID IN (SELECT dt_tt.IDTienTrinhBanHang
FROM
DoiThu_TienTrinhBH dt_tt
WHERE dt_tt.IDDoiThu = IDDT))Temp
ON
Temp.IDKhachHang =
kh.ID)TempFinal;
select dttr.idtientrinhbanhang
from doithu_tientrinhbh dttr where dttr.iddoithu=IDDT
select
trbh.id,trbh.tieude,trbh.diengiai,trbh.idkhachhang
into
idtemp,tieudetemp,diengiaitemp,idkhachhangtemp
from
tientrinhbanhang trbh
where
trbh.id=idtientrinhbanhangtemp;
select kh.tendaydukh
into
tenkhachangtemp
from
khachhang kh
where kh.id=idkhachhangtemp;
14
SELECT dm.Ten
FROM DiemManh dm
WHERE dm.IDDoiThu
= IDDT
SELECT dy.Ten
FROM DiemYeu dy
WHERE dy.IDDoiThu
= IDDT
15
select kh.idkhoikhachhang
into
idkhoikhachhang
from
khachhang kh, khoikhachhang kkh
where kh.id=idkh and kh.idkhoikhachhang=kkh.id;
select count(ktt.idkhoikhachhang)
into tongKtt
from
khucthitruong ktt
where ktt.idkhoikhachhang=idkhoikhachhang;
16
select hd.idkhachhang,hd.tonggt from hoadon hd where
hd.idkhachhang=idkh
update khachhang
set doanhthu=tonggthoadon
where id=idkh;
17
select cthd.soseries,cthd.idhoadon from chitiethoadon cthd where
cthd.idsanpham=idsanpham
while iDHopDong_cursor%found
loop
select hd.tonggt into
tonggttemp from hoadon hd where hd.soseries=soseriestemp and hd.id=idhoadontemp;
tonggthoadon:=tonggthoadon+tonggttemp;
fetch
iDHopDong_cursor into soseriestemp,idhoadontemp;
end loop;
18
select cthd.GiaBan, cthd.SoLuong, cthd.chietkhau
into DonGia, SoLuong,
PhanTramChietKhau
from
ChiTietHopDong cthd
where cthd.IDHopDong =
IDHopDong;
select ts.phantram
into
ThueSuat
from
ChiTietHopDong cthd, ThueSuat ts
where cthd.idhopdong =
idhopdong and cthd.idthuesuat=ts.id;
update HopDong
set TongGt = GiaTriHopDong
where ID = IDHopDong;
19
select ctdh.GiaBan, ctdh.SoLuong, ctdh.chietkhau
into DonGia, SoLuong,
PhanTramChietKhau
from chitietdonhang
ctdh
where ctdh.IDdonhang = iDDH;
select ts.phantram
into
ThueSuat
from
ChiTietDonHang ctdh, ThueSuat ts
where ctdh.iddonhang = iDDH and ctdh.idthuesuat=ts.id;
update DonHang
set TongGt = tongGTDH
where ID = IDDH;
20
select cthd.GiaBan, cthd.SoLuong, cthd.chietkhau
into DonGia, SoLuong,
PhanTramChietKhau
from
ChiTiethoadon cthd
where cthd.IDhoadon = iDHD;
select ts.phantram
into
ThueSuat
from
ChiTietHoaDon cthd, ThueSuat ts
where cthd.idhoadon = iDHD and cthd.idthuesuat=ts.id;
update HoaDon
set TongGt = tongGTHD
where ID = IDHD;
21
select id from
hopdong
while c%found
loop
begin
select ts.phantram
into
ThueSuat
from
ChiTietHopDong cthd, ThueSuat ts
where
cthd.idhopdong =
idHopDong and cthd.idthuesuat=ts.id;
select (SoLuong*GiaBan)-(soluong*giaban*chietkhau)+(giaban*soluong-(soluong*giaban*chietkhau))*ThueSuat
into
tongGT1HD
from
chitiethopdong cthd
where
cthd.idhopdong=idhopdong;
update
hopdong
set
tonggt = tongGT1HD
where id = idHopDong;
22
select id from
donhang
while c%found
loop
begin
select ts.phantram
into
ThueSuat
from
ChiTietHopDong cthd, ThueSuat ts
where
cthd.idhopdong =
idHopDong and cthd.idthuesuat=ts.id;
select (SoLuong*GiaBan)-(soluong*giaban*chietkhau)+(giaban*soluong-(soluong*giaban*chietkhau))*ThueSuat
into
tongGT1DH
from
chitietdonhang ctdh
where
ctdh.iddonhang=iddonhang;
update
donhang
set
tonggt = tongGT1DH
where id = idDonHang;
23
select id from
hoadon
while c%found
loop
begin
select (SoLuong*GiaBan)-(soluong*giaban*chietkhau)+(giaban*soluong-(soluong*giaban*chietkhau))*tienthue
into tongGT1HD
from
chitiethoadon cthd
where
cthd.idhoadon=idhoadon;
update
hoadon
set
tonggt = tongGT1HD
where id = idHoaDon;
24
select loaiphanhoi from thongtinphanhoi
25
select tonggt from hopdong where hopdong.IDNhanVien=iDNV
