A gyakorlat célja a Microsoft SQL szerver platform és szerveroldali programozás alapjainak elsajátítása, az alapfogalmak megismerése és a fejlesztőeszközök használatának gyakorlása.
A labor elvégzéséhez szükséges eszközök:
Amit érdemes átnézned:
Felkészülés ellenőrzése:
2021 tavasszal, amennyiben a gyakorlat (még/már/továbbra is) távoktatásban kerül terítékre, az alábbit kérjük.
Az első három feladatot (beleértve a megoldások tesztelését is) a gyakorlatvezetővel együtt oldjuk meg. Az utolsó feladat önálló munka, amennyiben marad rá idő.
Emlékeztetőként a megoldások is megtalálhatóak az útmutatóban is. Előbb azonban próbáljuk magunk megoldani a feladatot!
Az adatbázis az adott géphez kötött, ezért nem biztos, hogy a korábban létrehozott adatbázis most is létezik. Ezért először ellenőrizzük, és ha nem találjuk, akkor hozzuk létre újra az adatbázist. (Ennek mikéntjét lásd a Tranzakciókezelés gyakorlat anyagában.)
Hozzon létre egy tárolt eljárást, aminek a segítségével egy új kategóriát vehetünk fel. Az eljárás bemenő paramétere a felvételre kerülő kategória neve, és opcionálisan a szülőkategória neve. Dobjon alkalmazás hibát, ha a kategória létezik, vagy a szülőkategória nem létezik. A kategória elsődleges kulcsának generálását bízza az adatbázisra.
create procedure UjKategoria
@Kategoria nvarchar(50),
@SzuloKategoria nvarchar(50)
as
begin tran
declare @ID int
select @ID = ID
from Kategoria with (TABLOCKX)
where upper(Nev) = upper(@Kategoria)
if @ID is not null
begin
rollback
raiserror (' A %s kategoria mar letezik',16,1,@Kategoria)
return
end
declare @SzuloKategoriaID int
if @SzuloKategoria is not null
begin
select @SzuloKategoriaID = id
from Kategoria
where upper(Nev) = upper(@SzuloKategoria)
if @SzuloKategoriaID is null
begin
rollback
raiserror (' A %s kategoria nem letezik',16,1,@SzuloKategoria)
return
end
end
insert into Kategoria
values(@Kategoria, @SzuloKategoriaID)
commit
Nyissunk egy új Query ablakot, és adjuk ki az alábbi parancsot:
exec UjKategoria 'Uszogumik', NULL
Ennek sikerülnie kell. Ellenőrizzük utána a tábla tartalmát.
Ismételjük meg a fenti beszúrást! Ekkor már hibát kell, hogy dobjon.
Írjon triggert, ami a megrendelés státuszának változása esetén a hozzá tartozó egyes tételek státuszát a megfelelőre módosítja, ha azok régi státusza megegyezett a megrendelés régi státuszával. A többi tételt nem érinti a státusz változása.
create trigger StatuszKarbantartas
on Megrendeles
for update
as
update Megrendelestetel
set StatuszID = i.StatuszID
from Megrendelestetel mt
inner join inserted i on i.Id = mt.MegrendelesID
inner join deleted d on d.ID = mt.MegrendelesID
where i.StatuszID != d.StatuszID
and mt.StatuszID = d.StatuszID
Szánjunk egy kis időt az update ... from utasítás működési elvének megértésére. Az alapelvek a következők:
Akkor használjuk, ha a módosítandó tábla bizonyos mezőit más tábla vagy táblák tartalma alapján szeretnénk beállítani. A szintaktika alapvetően a már megszokott update ... set... formát követi, kiegészítve egy from szakasszal, melyben már a select from utasításnál megismerttel azonos szintaktikával más táblákból illeszthetünk (join) adatokat a módosítandó táblához. Így a set szakaszban az illesztett táblák oszlopai is felhasználhatóak adatforrásként (vagyis állhatnak az = jobb oldalán).
Ellenőrizzük a megrendelés és a tételek státuszát:
select megrendelestetel.statuszid, megrendeles.statuszid
from megrendelestetel join megrendeles on
megrendelestetel.megrendelesid = megrendeles.id
where megrendelesid = 1
Változtassuk meg a megrendelést:
update Megrendeles
set StatuszID = 4
where ID = 1
Ellenőrizzük a megrendelést és a tételeket (az update után minden
státusznak meg kellett változnia):
select MegrendelesTetel.StatuszID, Megrendeles.StatuszID
from MegrendelesTetel join Megrendeles on
MegrendelesTetel.MegrendelesID = Megrendeles.ID
where MegrendelesID = 1
Tároljuk el a vevők összes megrendelésének végösszegét a Vevő táblában!
alter table vevo add vegosszeg float
declare cur_vevo cursor
for select ID from Vevo
declare @vevoId int
declare @osszeg float
open cur_vevo
fetch next from cur_vevo into @vevoId
while @@FETCH_STATUS = 0
begin
select @osszeg = sum(mt.Mennyiseg * mt.NettoAr)
from Telephely t
inner join Megrendeles m on m.TelephelyID = t.ID
inner join MegrendelesTetel mt on mt.MegrendelesID = m.ID
where t.VevoID = @vevoId
update Vevo
set vegosszeg = ISNULL(@osszeg, 0)
where ID = @vevoId
fetch next from cur_vevo into @vevoId
end
close cur_vevo
deallocate cur_vevo
Az előző feladatban kiszámolt érték az aktuális állapotot tartalmazza csak. Készítsünk triggert, amivel karbantartjuk azt az összeget minden megrendelést érintő változás esetén. Az összeg újraszámolása helyett csak frissítse a változásokkal az értéket!
A megoldás kulcsa meghatározni, mely táblára kell a triggert tenni. A megrendelések változása érdekes számunkra, de valójában a végösszeg a megrendeléshez felvett tételek módosulásakor fog változni, így erre a táblára kell a trigger.
A feladat nehézségét az adja, hogy az inserted és deleted táblákban nem csak egy vevő adatai módosulhatnak. Egy lehetséges megoldás a korábban használt kurzoros megközelítés (itt a változásokon kell iterálni). Vagy megpróbálhatjuk megírni egy utasításban is, ügyelve arra, hogy vevők szerint csoportosítsuk a változásokat.
create trigger VegosszegKarbatartas
on MegrendelesTetel
for insert, update, delete
as
update Vevo
set Vegosszeg = isnull(Vegosszeg, 0) + OsszegValtozas
from Vevo
inner join
(select t.VevoID, sum(Mennyiseg * NettoAr) as OsszegValtozas
from Telephely t
inner join Megrendeles m on m.TelephelyID = t.ID
inner join inserted i on i.MegrendelesID = m.ID
group by t.VevoId) VevoValtozas on Vevo.ID = VevoValtozas.VevoID
update Vevo
set Vegosszeg = isnull(Vegosszeg, 0) - OsszegValtozas
from Vevo
inner join
(select t.VevoId, sum(Mennyiseg * NettoAr) as OsszegValtozas
from Telephely t
inner join Megrendeles m on m.TelephelyID = t.ID
inner join deleted d on d.MegrendelesID = m.ID
group by t.VevoID) VevoValtozas on Vevo.ID = VevoValtozas.VevoID
Nézzük meg az összmegrendelések aktuális értékét, jegyezzük meg a számokat.
select ID, OsszMegrendeles
from Vevo
Módosítsunk egy megrendelés mennyiségén.
update MegrendelesTetel
set Mennyiseg = 3
where ID = 1
Nézzük meg az összegeket ismét, meg kellett változnia a számnak.
select ID, OsszMegrendeles
from Vevo
Az itt található oktatási segédanyagok a BMEVIAUBB04 tárgy hallgatóinak készültek. Az anyagok oly módú felhasználása, amely a tárgy oktatásához nem szorosan kapcsolódik, csak a szerző(k) és a forrás megjelölésével történhet.
Az anyagok a tárgy keretében oktatott kontextusban értelmezhetőek. Az anyagokért egyéb felhasználás esetén a szerző(k) felelősséget nem vállalnak.