cac ban sua dum minh voi mot so loi ma ko biet sua
create or replace PROCEDURE CAU_22 AS
BEGIN
DECLARE MANGID VARCHAR2(15);
cursor cursor_1 is SELECT id FROM donhang;
BEGIN
open cursor_1;
FETCH cursor_1 INTO mangid;
while cursor_1%FOUND
loop
DECLARE idctdh VARCHAR2(15);
cursor cursor_2 is SELECT iddonhang FROM chitietdonhang where mangid= iddonhang;
BEGIN
open cursor_2;
------------------------
create or replace
PROCEDURE CAU_24
iS
loai1 float;
loai2 float;
loai3 float;
tyle1 float;
tyle2 float;
tyle3 float;
set loai1:=0;
set loai2:=0;
set loai3:=0;
BEGIN
declare
cursor cursor_1 is SELECT id FROM thongtinphanhoi;
mangID VARCHAR2(15);
begin
open cursor_1;
FETCH cursor_1 INTO mangid;
while cursor_1%FOUND
loop
if exists (SELECT * FROM thongtinphanhoi where mangid= id and loaiphanhoi='0') THEN
begin
loai1:=loai1+1;
end;
if EXISTS( SELECT * FROM thongtinphanhoi where mangid= id and loaiphanhoi='1') THEN
BEGIN
loai2:=loai2+1;
end ;
if exists (select * from thongtinphanhoi where mangid= id and loaiphanhoi='2') THEN
BEGIN
loai3:=loai3+1;
end;
FETCH cursor_1 INTO mangid;
END LOOP ;
close cursor_1;
end;
BEGIN
tyle1:=loai1/(loai1+loai2+loai3);
tyle2:=loai2/(loai1+loai2+loai3);
tyle3:=loai3/(loai1+loai2+loai3);
dbms_output.put_line(tyle1);
dbms_output.put_line(tyle2);
dbms_output.put_line(tyle3);
END;
END CAU_24;
FETCH cursor_2 INTO idctdh;
while mangidctdh%FOUND
loop
DECLARE phantramthue float;
BEGIN
select ts.phantram into phantramthue from chitietdonhang ctdh,thuesuat ts where ctdh.iddonhang=idctdh and ctdh.idthuesuat= ts.id;
UPDATE chitietdonhang
SET giachuachietkhau=soluong*dongia,
tienchietkhau=soluong*dongia*chiethau,
giasauchietkhau=(soluong*dongia)-(soluong*dongia*chietkhau),
tienthue=(soluong*dongia)-(soluong*dongia*chietkhau)*phantramthue,
giasauthue=dongia*soluong-tienchietkhau+tienthue
where idctdh=iddonhang;
FETCH cursor_2 INTO idctdh;
end loop;
close cursor_2;
DECLARE tonggiatri float;
BEGIN
select sum(giasauthue) into tonggiatri from chitietdonhang where mangid= iddonhang;
UPDATE donhang
set tonggt=tonggiatri
where mangid= id;
FETCH cursor_1 INTO mangid;
end loop;
close cursor_1;
END;
-------------------
create or replace
PROCEDURE CAU23 AS
BEGIN
declare mangIDhoadon cursor for( select HD.ID,HD.soseries from hoadhon HD)
open mangIDhoadon
declare @IDhoadon varchar2(15)
declare @soseries varchar2(15)
Fetch next from mangIDhoadon into @IDhoadon,@soseries
while(@@Fetch_status=0)
begin
declare mangIDCTHD cursor for select IDhoadon,soseries from chitiethoadon where IDhoadon=@IDhoadon and @soseries=soseries
open mangIDCTHD
declare @IDCTHD varchar2(15)
declare @soseriesCTHD varchar2(15)
Fetch next from mangIDCTHD into @IDCTHD, @soseriesCTHD
while(@@Fetch_status=0)
begin
declare @phantramthue float
set @phantramthue=(select ts.phantram from chitiethoadon cthd,thuesuat ts
where cthd.IDthuesuat=ts.ID and @IDCTHD=cthd.IDhoadon and @soseriesCTHD=cthd.soseries)
update chitiethoadon
set giachuachietkhau=soluong*giaban,
tienchietkhau=soluong*giaban*chietkhau,
giasauchietkhau=soluong*giaban-soluong*giaban*chietkhau,
tienthue=(soluong*giaban-soluong*giaban*chietkhau)*@phantramthue,
giasauthue=soluong*giaban-tienchietkhau+tienthue
where @IDCTHD=IDhoadon and @soseries=soseries;
Fetch next from mangIDCTHD into @IDCTHD, @soseriesCTHD
END
close mangIDCTHD
deallocate mangIDCTHD
DECLARE @tonggiatri float
set @tonggiatri=(select sum(giasauthue)from chitiethoadon
where @IDhoadon=IDhoadon and @soseriesCTHD=soseries group by IDhoadon)
update hoadon
set tonggt=@tonggiatri
where @idhoadon=id and soseries=@soseries;
Fetch next from mangIDhoadon into @IDhoadon,@soseries
end
close mangIDhoadon
deallocate manghoadon ;
END CAU23;
