W O J S K O W A A K A D E M I A T E C H N I C Z N A
L A B O R A T O R I U M B A Z D A N Y C H
SPRAWOZDANIE
Z ĆWICZENIA LABORATORYJNEGO
Temat ćwiczenia: Baza danych: Sklep spożywczy.
Ćwiczenie wykonał: Rafał Sylwestrzuk
grupa: I5X5S0
Data wykonania ćwiczenia: 11.06.2007
Procedury
koniec_terminu - jest to procedura, która znajduje towary z przekroczonym terminem przydatności.
ALTER PROCEDURE "DBA"."koniec_terminu"() /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */
/* RESULT( column_name column_type, ... ) */
begin
declare data_ date;
declare nazwa varchar(20);
declare i integer;
declare j integer;
declare kursor cursor for select data_przyd,nazwa_tow from towar where data_przyd < getdate(*);
set i=(select count(*) from towar where data_przyd < getdate(*));
set j=0;
open kursor;
petelka: loop
fetch next kursor into data_,nazwa;
message 'Zaraz zostanie usunięty z bazy przeterminowany towar: ',data_,' ',nazwa type action to client;
delete from towar where
data_przyd = data_;
set j=j+1;
if i = j then
leave petelka
end if
end loop petelka;
close kursor;
end
suma_dost - to procedura która przenosi do tabeli „towar” towary z dostawy.
ALTER PROCEDURE "DBA"."suma_dost"( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
/* RESULT( column_name column_type, ... ) */
BEGIN
declare naz varchar(20);
declare il int;
declare dat date;
declare wart float;
declare jm int;
declare stawka int;
declare i int;
declare j int;
declare wyst int;
declare index_ int;
--declare err_notfound exception for sqlstate '02000';
declare dost cursor for select nazwa, ilosc, data_przyd, wartosc, id_jm, id_stawki from wiersz_dost where nr_dostawy = (select count(*) from dostawa);
set i = (select count(*) from wiersz_dost);
set j = 0;
open dost;
petelka:
loop
fetch next dost into naz, il, dat, wart, jm, stawka;
set wyst = 0;
set wyst = (select count(*) from towar where nazwa_tow = naz and data_przyd = dat);
set index_ = (select count(*) from towar);
if wyst > 0 then
update towar
set ilosc = ilosc + il
where nazwa_tow = naz and data_przyd = dat;
else
insert into towar
values (index_+1,stawka, jm, naz, wart, dat, il);
end if;
set j = j + 1;
if i = j then
leave petelka;
end if;
end loop petelka;
close dost;
END
Suma_zak - procedura która podlicza wartość pragonu.
ALTER PROCEDURE "DBA"."suma_zak"( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
/* RESULT( column_name column_type, ... ) */
BEGIN
declare ii int;
declare a float;
declare rabat numeric(6,2);
set rabat = 0;
set ii = (select count(*) from paragon);
set a = (select sum(w.ilosc * t.cena)
from wiersz_fakt_parag w, towar t, paragon p
where p.numer_parag = ii and w.numer_parag = ii and w.id_towaru = t.id_towaru);
update paragon
set wartosc = a
where numer_parag = ii;
set rabat = (select rabatta());
if rabat > 0 then
MESSAGE 'Rabat naliczono w wysokości: ', rabat type action to client;
end if;
END
Utarg - zlicza wartości paragonów i wstawia je wraz z data dnia podliczanego.
ALTER PROCEDURE "DBA"."utarg"( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
/* RESULT( column_name column_type, ... ) */
BEGIN
declare dzis date;
declare msg numeric(6,2);
set dzis = getdate();
message dzis type action to client;
set msg = (select sum(wartosc)
from paragon
where data_wyst < now() and data_wyst > dzis);
message 'Utarg dnia dzisiejszego wynosi ', msg type action to client;
insert into utargi
values (dzis, msg);
END
Wartość_dost - wylicza i aktualizuje wartość dostawy.
ALTER PROCEDURE "DBA"."wartosc_dost"( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
/* RESULT( column_name column_type, ... ) */
BEGIN
declare ii int;
declare a float;
declare rabat numeric(6,2);
set rabat = 0;
set ii = (select count(*) from dostawa);
set a = (select sum(ilosc * w.wartosc)
from wiersz_dost w, dostawa d
where w.nr_dostawy = ii and d.nr_dostawy = ii);
update dostawa
set wartosc = a
where nr_dostawy = ii;
END
Funkcje
Rabatta - zwraca wartość rabatu i aktualizuje paragon w polu wartość i id_rab.
ALTER FUNCTION "DBA"."rabatta"( /* [IN] parameter_name parameter_type [DEFAULT default_value], ... */ )
RETURNS float
DETERMINISTIC
BEGIN
DECLARE rab float;
declare nr_parag int;
declare wart float;
declare a float;
declare procent numeric(6, 2);
set nr_parag = (select count(*) from paragon);
set wart = (select wartosc from paragon where numer_parag = nr_parag);
if wart >= 100 and wart < 200 then
set procent = (select wart_rabatu(1));
update paragon
set id_rab = 1
where numer_parag = nr_parag;
update paragon
set wartosc = (wartosc - (wartosc * procent))
where numer_parag = nr_parag;
set rab = wart * procent;
end if;
if wart >= 200 and wart < 300 then
set procent = (select wart_rabatu(2));
update paragon
set id_rab = 2
where numer_parag = nr_parag;
update paragon
set wartosc = wartosc - (wartosc * procent)
where numer_parag = nr_parag;
set rab = wart * procent;
end if;
if wart >= 300 and wart < 400 then
set procent = (select wart_rabatu(3));
update paragon
set id_rab = 3
where numer_parag = nr_parag;
update paragon
set wartosc = wartosc - (wartosc * procent)
where numer_parag = nr_parag;
set rab = wart * procent;
end if;
if wart >= 400 and wart < 500 then
set procent = (select wart_rabatu(4));
update paragon
set id_rab = 4
where numer_parag = nr_parag;
update paragon
set wartosc = wartosc - (wartosc * procent)
where numer_parag = nr_parag;
set rab = wart * procent;
end if;
if wart >= 500 then
set procent = (select wart_rabatu(5));
update paragon
set id_rab = 5
where numer_parag = nr_parag;
update paragon
set wartosc = wartosc - (wartosc * procent)
where numer_parag = nr_parag;
set rab = wart * procent;
end if;
RETURN rab;
END
Wart_rabatu - zwraca ile procent odliczone zostanie od kwoty paragonu.
ALTER FUNCTION "DBA"."wart_rabatu"(in id_ra int /* [IN] parameter_name parameter_type [DEFAULT default_value], ... */ )
RETURNS numeric(6,2)
DETERMINISTIC
BEGIN
DECLARE rabat numeric(6,2);
set rabat = (select rabat from rabat where id_rab = id_ra);
RETURN rabat;
END
Triggery
Empty - zgłasza braki w towarze
ALTER TRIGGER "empty" AFTER UPDATE
ORDER 1 ON "DBA"."TOWAR"
/* REFERENCING OLD AS old_name NEW AS new_name */
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare il float;
declare naz varchar(20);
set il = 0;
set naz = (select nazwa_tow from towar where ilosc = 0);
if (select count(*) from towar where ilosc = 0) > 0 then
message 'BRAKI W MAGAZYNIE ', naz type action to client;
end if;
END
Rabat - zgłasza że paragon będzie z rabatem.
ALTER TRIGGER "rabat" AFTER INSERT, UPDATE
ORDER 1 ON "DBA"."PARAGON"
/* REFERENCING OLD AS old_name NEW AS new_name */
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare a float;
declare nr_p int;
DECLARE msg varchar(255);
declare id_r int;
set id_r = 0;
SET msg = 'UWAGA!!! Paragon z rabatem numer ';
if a > 100 then
MESSAGE msg, id_r type action to client;
end if;
END
Redukcja - pozwala na automatyczne zaktualizowanie tabeli towar po wstawieniu do wiersza paragonu.
ALTER TRIGGER "redukcja" AFTER INSERT
ORDER 1 ON "DBA"."WIERSZ_FAKT_PARAG"
/* REFERENCING NEW AS new_name */
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
declare ost int;
declare il float;
declare id_t int;
set ost = (select count(*)
from wiersz_fakt_parag);
set il = (select ilosc
from wiersz_fakt_parag
where lp = ost);
set id_t = (select id_towaru
from wiersz_fakt_parag
where lp = ost);
update towar
set ilosc = ilosc - il
where id_towaru = id_t;
message 'zredukowano ',ost type action to client;
END
Widoki
Dostępność - widok pokazujący dostępność towaru.
ALTER VIEW "DBA"."dotepnosc"( /* view_column_name, ... */ )
AS
SELECT "DBA"."TOWAR"."NAZWA_TOW",
"DBA"."TOWAR"."ILOSC"
FROM "DBA"."TOWAR"
Ostatnie_dostawy - wyświetla ostatnie dostawy wraz dostawcami.
ALTER VIEW "DBA"."ostatnie_dostawy"( /* view_column_name, ... */ )
AS
SELECT "DBA"."WIERSZ_DOST"."NAZWA",
"DBA"."WIERSZ_DOST"."ILOSC",
"DBA"."DOSTAWA"."DATA_DOST"
FROM ( "DBA"."WIERSZ_DOST" JOIN "DBA"."DOSTAWA" ON "DBA"."WIERSZ_DOST"."NR_DOSTAWY" = "DBA"."DOSTAWA"."NR_DOSTAWY" ) JOIN "DBA"."DOSTAWCA" ON "DBA"."DOSTAWA"."ID_DOST" = "DBA"."DOSTAWCA"."ID_DOST"
ORDER BY "DBA"."DOSTAWA"."DATA_DOST" ASC
Pracownicy - wyświetla wszelkie informacje o pracownikach.
ALTER VIEW "DBA"."pracownicy"( /* view_column_name, ... */ )
AS
SELECT "DBA"."PRACOWNIK"."NAZWISKO",
"DBA"."PRACOWNIK"."START_UMOWY",
"DBA"."PRACOWNIK"."KONIEC_UMOWY",
"DBA"."STANOWISKO"."STANOWISKO",
"DBA"."UMOWA"."RODZAJ_UMOWY",
"DBA"."WYKSZTALCENIE"."WYKSZTALCENIE"
FROM ( ( "DBA"."PRACOWNIK" JOIN "DBA"."STANOWISKO" ON "DBA"."PRACOWNIK"."ID_STANOWISKO" = "DBA"."STANOWISKO"."ID_STANOWISKO" ) JOIN "DBA"."UMOWA" ON "DBA"."PRACOWNIK"."ID_UMOWY" = "DBA"."UMOWA"."ID_UMOWY" ) JOIN "DBA"."WYKSZTALCENIE" ON "DBA"."PRACOWNIK"."ID_WYKSZ" = "DBA"."WYKSZTALCENIE"."ID_WYKSZ"