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: Salon samochodowy
Ćwiczenie wykonał: Michał Grzelak
grupa: C04E
Data wykonania ćwiczenia: 04.06.2006
1. Triggery
Zmien_fakture_cz - uaktualnia fakturę o sumę pieniędzy po zmianie pozycji w tabeli Zak_cz
ALTER TRIGGER "Zmien_fakture_cz" after UPDATE on tZak_cz
for each row
BEGIN
DECLARE kursor CURSOR FOR SELECT Nr_ser FROM tZak_cz ORDER BY Nr_ser;
DECLARE @zm int;
DECLARE kursor2 CURSOR FOR SELECT Nr_faktury FROM tFaktury;
DECLARE @zm2 int;
OPEN kursor;
p1 : LOOP
FETCH NEXT kursor INTO @zm;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
UPDATE tZak_cz
SET Cena_cz = Ilosc * (SELECT Cena FROM tCzesci WHERE Nr_ser=@zm)
WHERE Nr_ser = @zm;
END LOOP p1;
CLOSE kursor;
UPDATE tFaktury
SET Suma = 0;
OPEN kursor2;
p1 : LOOP
FETCH NEXT kursor2 INTO @zm2;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
UPDATE tFaktury
SET Suma = Suma + ISNULL((SELECT SUM(Cena_cz) FROM tZak_cz WHERE Nr_faktury = @zm2),0) + ISNULL((SELECT SUM(Cena_s) FROM tZak_s WHERE Nr_faktury = @zm2),0)
WHERE Nr_faktury = @zm2;
END LOOP p1;
CLOSE kursor2;
END
Zmien_fakture_s - uaktualnia fakturę o sumę pieniędzy po zmianie pozycji w tabeli Zak_s
ALTER TRIGGER "Zmien_fakture_s" after UPDATE on tZak_s
for each row
BEGIN
DECLARE kursor CURSOR FOR SELECT Nr_seryjny FROM tZak_s ORDER BY Nr_seryjny;
DECLARE @zm int;
DECLARE kursor2 CURSOR FOR SELECT Nr_faktury FROM tFaktury;
DECLARE @zm2 int;
OPEN kursor;
p1:
LOOP
FETCH NEXT kursor INTO @zm;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
UPDATE tZak_s
SET Cena_s = (SELECT Cena FROM tAuta WHERE Nr_seryjny=@zm)
WHERE Nr_seryjny = @zm;
END LOOP p1;
CLOSE kursor;
UPDATE tFaktury
SET Suma = 0;
OPEN kursor2;
p1:
LOOP
FETCH NEXT kursor2 INTO @zm2;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
UPDATE tFaktury
SET Suma = Suma + ISNULL((SELECT SUM(Cena_cz) FROM tZak_cz WHERE Nr_faktury = @zm2),0) + ISNULL((SELECT SUM(Cena_s) FROM tZak_s WHERE Nr_faktury = @zm2),0)
WHERE Nr_faktury = @zm2;
END LOOP p1;
CLOSE kursor2;
END
Zmien_stan - uaktualnia stan auta (sprzedany/dostępny) z salonu po jego dodaniu do faktury w tabeli tFaktury (czyli sprzedaży)
ALTER TRIGGER "Zmien_stan" after INSERT on tFaktury
for each row
BEGIN
DECLARE kursor CURSOR FOR SELECT Nr_seryjny FROM tAuta;
DECLARE @zm int;
OPEN kursor;
p1:
LOOP
FETCH NEXT kursor INTO @zm;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
if exists (SELECT Nr_seryjny FROM tZak_s WHERE Nr_seryjny=@zm) then
UPDATE tAuta
SET Stan = 0
WHERE Nr_ser = @zm;
end if;
END LOOP p1;
CLOSE kursor;
END
Wstaw_promocje - łączy promocje z autami w salonie w tabeli Promocje_w_autach po
dodaniu promocje do tabeli tPromocje
ALTER TRIGGER "Wstaw_promocje" after UPDATE, INSERT, DELETE on tPromocje for each row
BEGIN
declare Szukaj_prom cursor for select Id_prom, Dotyczy_modelu from tPromocje;
declare Szukaj_aut cursor for select Nr_seryjny, Model from tAuta;
declare temp_prom int;
declare temp_auto int;
declare temp_model varchar(10);
declare temp_model2 varchar(10);
delete from Promocje_w_autach where 1=1;
open Szukaj_prom;
open Szukaj_aut;
petla1 : LOOP --początek pętli
fetch next Szukaj_prom into temp_prom, temp_model;
IF SQLCODE <> 0 THEN leave petla1;
end if;
petla2 : LOOP
fetch next Szukaj_aut into temp_auto, temp_model2;
IF SQLCODE <> 0 THEN leave petla2;
end if;
if temp_model = temp_model2 then
insert into Promocje_w_autach values (temp_prom,temp_auto);
end if;
end LOOP;
fetch first Szukaj_aut into temp_auto, temp_model2;
end LOOP;
close Szukaj_aut;
close Szukaj_prom;
END
2. Widoki
Aktualne_promocje - wyświetla aktualne promocje dostępne przy zakupie auta w salonie
ALTER VIEW "DBA"."Aktualne promocje"()
AS
SELECT "DBA"."tPromocje"."Id_prom",
"DBA"."tPromocje"."Typ_promocji",
"DBA"."tPromocje"."Dotyczy_modelu",
"DBA"."tPromocje"."Wartosc"
FROM "DBA"."tPromocje"
WHERE "DBA"."tPromocje"."Data_do" > getdate()
Najlepsze_promocje - wyświetla najbardziej opłacalną kombinację promocji dla modelu
samochodu „Vectra”
ALTER VIEW "DBA"."Najlepsze_promocje"()
AS
SELECT DISTINCT TOP 2 Typ_promocji, Wartosc FROM tPromocje WHERE Dotyczy_modelu = 'Vectra' ORDER BY Wartosc DESC
Najlepsze_promocje - wyświetla zestawienie który pracownik sprzedał który samochód
ALTER VIEW "DBA"."Pracownicy"()
AS
SELECT "DBA"."tPracownicy"."Id",
"DBA"."tPracownicy"."Imię",
"DBA"."tPracownicy"."Nazwisko",
"DBA"."tAuta"."Nr_seryjny",
"DBA"."tAuta"."Rocznik",
"DBA"."tAuta"."Model",
"DBA"."tAuta"."Cena"
FROM ( "DBA"."tPracownicy" CROSS JOIN "DBA"."tAuta" ) JOIN "DBA"."tPrac_auta" ON "DBA"."tAuta"."Nr_seryjny" = "DBA"."tPrac_auta"."Nr_seryjny" and "DBA"."tPracownicy"."Id" = "DBA"."tPrac_auta"."Id"
WHERE "DBA"."tPracownicy"."Dział" = 'Auta'
ORDER BY "DBA"."tPracownicy"."Id" ASC
Vectry - wyświetla samochodowy modelu „Vectra”, których cena jest mniejsza od 86000 zł.
oraz są dostępne
ALTER VIEW "DBA"."Vectry"( )
AS
SELECT "DBA"."tAuta"."Rocznik",
"DBA"."tAuta"."Kolor",
"DBA"."tAuta"."Wersja",
"DBA"."tAuta"."Cena"
FROM "DBA"."tAuta"
WHERE "DBA"."tAuta"."Cena" < 86000 AND Stan = 1 AND Model = 'Vectra'
3. Procedury
Dodaj_promocje - dodaje promocję z wprowadzonymi danymi, jeżeli taka promocja już
istnieje procedura wyświetla odpowiednią informację
ALTER PROCEDURE "DBA"."Dodaj_promocje" (in typ varchar(15), in model char(10), in wart numeric(6,2))
BEGIN
declare @Id_prom bigint;
declare dzis date;
set dzis=getdate();
select max(Id_prom) into @Id_prom from tPromocje;
set @Id_prom=@Id_prom+1;
if exists (select * from tPromocje where Dotyczy_modelu = model and Typ_promocji = typ) then
message 'Taka promocja juz istnieje' type action to client;
else
insert into tPromocje values (typ,model,@Id_prom,dzis,dateadd(day,60,dzis),wart);
end if;
END
Uaktualnij_promocje - uaktualnia wybraną promocję zmieniając jej Data_od na aktualną datę,
a Data_do na 60 dni po aktualnej dacie
ALTER PROCEDURE "DBA"."Uaktualnij_promocje"(in typ varchar(15))
BEGIN
declare dzis date;
set dzis=getdate();
if exists(select * from tPromocje where Typ_promocji = typ) then
update tPromocje set Data_od = dzis, Data_do = dateadd(day,60,dzis) where Typ_promocji = typ;
else
message 'Nie ma takiej promocji' type action to client;
end if;
END
Ile_sprzedali - wyświetla pracowników malejąco wg ilości sprzedanych samochodów,
korzysta z funkcji Ile_sprzedal, która zwraca ilość sprzedanych aut przez pracownika
ALTER PROCEDURE "DBA"."Ile_sprzedali"()
BEGIN
DECLARE @zm int;
DECLARE @zm2 varchar(15);
DECLARE @zm3 varchar(25);
DECLARE kursor CURSOR FOR SELECT "Id", Nazwisko, Imie FROM tPracownicy WHERE Dział = 'Klienci';
CREATE TABLE test (Nazwisko varchar(25), Imie varchar(15), "Ile sprzedał" int);
OPEN kursor;
p1 : LOOP
FETCH NEXT kursor INTO @zm, @zm3, @zm2;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
INSERT INTO test VALUES(@zm3, @zm2, Ile_sprzedal(@zm));
END LOOP p1;
CLOSE kursor;
SELECT * FROM test ORDER BY "Ile sprzedał" DESC;
DROP TABLE test;
END
Ile_wydali - wyświetla malejąco trzech klientów, którzy wydali w salonie najwięcej,
wg wydanej sumy pieniędzy
ALTER PROCEDURE "DBA"."Ile_wydali"()
BEGIN
DECLARE @zm int;
DECLARE @zm2 varchar(15);
DECLARE @zm3 varchar(25);
DECLARE kursor CURSOR FOR SELECT Id_klienta, Nazwisko, Imie FROM tKlienci;
CREATE TABLE test (Nazwisko varchar(25), Imie varchar(15), Kwota numeric (8,2));
OPEN kursor;
p1:
LOOP
FETCH NEXT kursor INTO @zm, @zm3, @zm2;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
INSERT INTO test VALUES(@zm3, @zm2, Ile_wydal(@zm));
END LOOP p1;
CLOSE kursor;
SELECT DISTINCT TOP 3 * FROM test ORDER BY Kwota DESC;
DROP TABLE test;
END
4. Funkcje
Dochod - zwraca sumę pieniędzy zarobioną przez firmę w ciągu ostatniego miesiąca, czyli suma ze sprzedanych aut minus koszt dostawy oraz wynagrodzenia dla pracowników
ALTER FUNCTION "DBA"."Dochod"()
RETURNS numeric(8,2)
DETERMINISTIC
BEGIN
DECLARE suma numeric(8,2);
DECLARE wydatki numeric(8,2);
DECLARE przychod numeric(8,2);
DECLARE dzis date;
SET dzis = getdate();
SET wydatki = (SELECT sum(Wynagrodzenie) FROM tPracownicy) + (SELECT sum(Suma) FROM tDostawy WHERE ("Data" > dateadd(month,-1,dzis)));
SET przychod = (SELECT sum(Suma) FROM tFaktury WHERE ("Data" > dateadd(month,-1,dzis)));
SET suma = przychod - wydatki;
Message suma type action to client;
RETURN suma;
END
Ile_sprzedal - zwraca ilość aut sprzedanych przez pracownika o danym Id
ALTER FUNCTION "DBA"."Ile_sprzedal"(IN "@Id" int)
RETURNS integer
DETERMINISTIC
BEGIN
DECLARE ile integer;
SET ile = (SELECT COUNT(*) FROM tKlienci WHERE "Id" = "@Id");
RETURN ile;
END
Ile_wydal - zwraca sumę pieniędzy wydanych w salonie przez klienta o danym numerze
ALTER FUNCTION "DBA"."Ile_wydal"(IN klient integer)
RETURNS numeric(8,2)
DETERMINISTIC
BEGIN
DECLARE @ile_wydal numeric(8,2);
SET @ile_wydal = (SELECT SUM(Suma) FROM tFaktury WHERE Id_klienta=klient);
RETURN @ile_wydal;
END
Koszt_pracowników - zwraca całkowity koszt pracowników z tytuły wynagrodzeń od
początku ich zatrudnienia
ALTER FUNCTION "DBA"."Koszt_pracownikow"( /* [IN] parameter_name parameter_type [DEFAULT default_value], ... */ )
RETURNS numeric(8,2)
DETERMINISTIC
BEGIN
DECLARE koszt numeric(8,2);
DECLARE kursor CURSOR FOR SELECT Wynagrodzenie, Pracuje_od FROM tPracownicy;
DECLARE @zm numeric(8,2);
DECLARE @zm1 date;
SET koszt = 0;
OPEN kursor;
p1:
LOOP
FETCH NEXT kursor INTO @zm, @zm1;
IF SQLCODE <> 0 THEN
LEAVE p1;
END IF;
SET koszt = (koszt + @zm*datediff(month,@zm1,getdate()));
END LOOP p1;
CLOSE kursor;
Message koszt type action to client;
RETURN koszt;
END