Microsoft SQL Server programozása¶

A gyakorlat célja, hogy a hallgatók megismerjék a Microsoft SQL Server platform szerver oldali programozásának alapjait, elsajátítsák az alapfogalmakat és a fejlesztőeszköz használatát.

Előfeltételek¶

A labor elvégzéséhez szükséges eszközök:

Microsoft SQL Server (LocalDB vagy Express edition)
SQL Server Management Studio
Adatbázis létrehozó script: mssql.sql

Amit érdemes átnézned:

SQL nyelv
Microsoft SQL Server programozása (tárolt eljárások, triggerek)
Microsoft SQL Server használata segédlet

Gyakorlat menete¶

Az első négy 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!

Feladat 0: Adatbázis létrehozása, ellenőrzése¶

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 az első gyakorlat anyagában.)

Feladat 1: SQL parancsok (emlékeztető)¶

Írjon SQL lekérdezés/utasítást az alábbi feladatokhoz.

Hány nem teljesített megrendelésünk van (a státusz alapján)?
Megoldás
```
select count(*)
from [Order] o join Status s on o.StatusID = s.ID
where s.Name != 'Delivered'
```
A join mellett az oszlopfüggvény (aggregáció) használatára látunk példát. (A táblák kapcsolására nem csak ez a szintaktika használható, előadáson szerepelt alternatív is.)
Melyek azok a fizetési módok, amit soha nem választottak a megrendelőink?
Megoldás
```
select p.Method
from [Order] o right outer join PaymentMethod p on o.PaymentMethodID = p.ID
where o.ID is null
```
A megoldás kulcsa az outer join, aminek köszönhetően láthatjuk, mely fizetési mód rekordhoz nem tartozik egyetlen megrendelés se.
Rögzítsünk be egy új vevőt! Kérdezzük le az újonnan létrejött rekord kulcsát!
Megoldás
```
insert into Customer(Name, Login, Password, Email)
values ('Teszt Elek', 't.elek', '********', 't.elek@email.com')

select @@IDENTITY
```
Az insert után javasolt kiírni az oszlopneveket az egyértelműség végett, bár nem kötelező. Vegyük észre, hogy az ID oszlopnak nem adunk értéket, mert azt a tábla definíciójakor meghatározva a szerver adja automatikusan. Ezért kell utána lekérdeznünk, hogy tudjuk, milyen ID-t adott.
A kategóriák között hibásan szerepel a Tricycle kategória név. Javítsuk át a kategória nevét Tricycles-re!
Megoldás
```
update Category
set Name = 'Tricycles'
where Name = 'Tricycle'
```
Melyik termék kategóriában van a legtöbb termék?
Megoldás
```
select top 1 Name, (select count(*) from Product where Product.CategoryID = c.ID) as cnt
from Category c
order by cnt desc
```
A kérdésre több alternatív lekérdezés is eszünkbe juthat. Ez csak egyike a lehetséges megoldásoknak. Itt láthatunk példát az allekérdezésre is.

Feladat 2: Termékkategória rögzítése¶

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 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.

Megoldás

Tárolt eljárás

create or alter procedure AddNewCategory
    @Name nvarchar(50),
    @ParentName nvarchar(50)
as

begin tran

-- Létezik-e ilyen névvel már kategória
declare @ID int
select @ID = ID
from Category with (TABLOCKX)
where upper(Name) = upper(@Name)

if @ID is not null
begin
    rollback
    raiserror ('Category %s already exists',16,1,@Name)
    return
end

-- Szülő kategóriának léteznie kell
declare @ParentID int
if @ParentName is not null
begin
    select @ParentID = ID
    from Category
    where upper(Name) = upper(@ParentName)

    if @ParentID is null
    begin
        rollback
        raiserror ('Category %s does not exist',16,1,@ParentName)
        return
    end
end

insert into Category
values(@Name,@ParentID)

commit

Tesztelés

Nyissunk egy új Query ablakot és adjuk ki az alábbi parancsot.

exec AddNewCategory 'Beach balls', 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 dobjon.

Próbáljuk ki szülőkategóriával is.

exec AddNewCategory 'LEGO Star Wars', 'LEGO'

Feladat 3: Megrendeléstétel státuszának karbantartása¶

Í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.

Megoldás

Trigger

create or alter trigger UpdateOrderStatus
on [Order]
for update
as

update OrderItem
set StatusID = i.StatusID
from OrderItem oi
inner join inserted i on i.Id=oi.OrderID
inner join deleted d on d.ID=oi.OrderID
where i.StatusID != d.StatusID
and oi.StatusID=d.StatusID

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ók adatforrásként (vagyis állhatnak az egyenlőség jobb oldalán).

Tesztelés

Ellenőrizzük a megrendelés és a tételek státuszát:

select OrderItem.StatusID, [Order].StatusID
from OrderItem join [Order] on OrderItem.OrderID=[Order].ID
where OrderID = 1

Változtassuk meg a megrendelést:

update [Order]
set StatusID=4
where ID=1

Ellenőrizzük a megrendelést és a tételeket (update után minden státusznak meg kell változnia):

select OrderItem.StatusID, [Order].StatusID
from OrderItem join [Order] on OrderItem.OrderID=[Order].ID
where OrderID = 1

Feladat 4: Vevő megrendeléseinek összegzése¶

Tároljuk el a vevő összes megrendelésének végösszegét a Vevő táblában!

Adjuk hozzá az a táblához az új oszlopot: alter table Customer add Total float
Számoljuk ki az aktuális végösszeget. A megoldáshoz használjunk kurzort, ami minden vevőn megy végig.

Megoldás

declare cur_customer cursor
    for select ID from Customer
declare @CustomerId int
declare @Total float

open cur_customer
fetch next from cur_customer into @CustomerId
while @@FETCH_STATUS = 0
begin

    select @Total = sum(oi.Amount * oi.Price)
    from CustomerSite s
    inner join [Order] o on o.CustomerSiteID=s.ID
    inner join OrderItem oi on oi.OrderID=o.ID
    where s.CustomerID = @CustomerId

    update Customer
    set Total = ISNULL(@Total, 0)
    where ID = @CustomerId

    fetch next from cur_customer into @CustomerId
end

close cur_customer
deallocate cur_customer

Ellenőrizzük a Customer tábla tartalmát.

Feladat 5: Vevő összmegrendelésének karbantartása (önálló feladat)¶

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!

Megoldás

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). Avagy megpróbálhatjuk megírni egy utasításban is, ügyelve arra, hogy vevők szerint csoportosítsuk a változásokat.

Trigger

create or alter trigger CustomerTotalUpdate
on OrderItem
for insert, update, delete
as

update Customer
set Total=isnull(Total,0) + TotalChange
from Customer
inner join
    (select s.CustomerId, sum(Amount * Price) as TotalChange
    from CustomerSite s
    inner join [Order] o on o.CustomerSiteID=s.ID
    inner join inserted i on i.OrderID=o.ID
    group by s.CustomerId) CustomerChange on Customer.ID = CustomerChange.CustomerId

update Customer
set Total=isnull(Total,0) - TotalChange
from Customer
inner join
    (select s.CustomerId, sum(Amount * Price) as TotalChange
    from CustomerSite s
    inner join [Order] o on o.CustomerSiteID=s.ID
    inner join deleted d on d.OrderID=o.ID
    group by s.CustomerID) CustomerChange on Customer.ID = CustomerChange.CustomerId

Tesztelés

Nézzük meg az összmegrendelések aktuális értékét, jegyezzük meg a számokat.

select ID, Total
from Customer

Módosítsunk egy megrendelés mennyiségén.

update OrderItem
set Amount=3
where ID=1

Nézzük meg az összegeket ismét, meg kellett változnia a számnak.

select ID, Total
from Customer

2023-09-04 Szerzők