CASE utasítás és beágyazott eset az SQL Serverben: T-SQL példa

A Case áttekintése a való életben!

A való életben ismét a különböző feltételek kimenetelétől függően különböző cselekvéseket hajtunk végre. A további részletekért vegyünk egy példát:

• Ha a repülőjegy 100 dollárnál kevesebb, akkor Los Angelesbe megyek.
• Ha a repülőjegyek ára 100 és 200 dollár között van, akkor New Yorkba fogok menni.
• Ha a repülőjegyek 200 és 400 dollár között vannak, akkor Európába fogok utazni.
• Egyébként inkább egy közeli turisztikai látványosságot látogatok meg.

Vizsgáljuk meg a feltétel és a cselekvés külön kategorizálását a fenti példától:

A fenti példában láthatjuk, hogy a különböző feltételek kimenetele külön cselekvést szabályoz. Például a látogató csak akkor fogja végrehajtani a New York-i látogatás cselekményét, ha a repülőjegy ára 100 és 200 dollár között van. Hasonlóképpen, a MS SQL A CASE utasítás lehetővé teszi különböző T-SQL utasítások végrehajtását is a különböző feltételek kimenetele alapján.

Mi az a CASE nyilatkozat az SQL Serverben?

CASE nyilatkozat be SQL Server a kiterjesztése a HA MÁS utasítás. Az IF…ELSE utasítással ellentétben, ahol maximum egy feltétel engedélyezett, a CASE lehetővé teszi a felhasználó számára, hogy több feltételt alkalmazzon különböző műveletkészletek végrehajtásához MS SQL-ben. A felhasználó által definiált feltételhez társított értéket ad vissza.

Tanuljuk meg, hogyan kell használni a CASE-t SQL és a koncepcióját a következő szakaszokban találod. Az MS SQL-ben kétféle CASE létezik:

• Egyszerű CASE
• Keresett CASE

Egyszerű CASE

Az egyszerű eset szintaxisa

CASE <Case_Expression>
     WHEN Value_1 THEN Statement_1
     WHEN Value_2 THEN Statement_2
     .
     .
     WHEN Value_N THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Itt:

• A Case_Expression paraméter azt a kifejezést jelöli, amelyet végül összehasonlítunk az Value_1, Value_2 és így tovább értékekkel.
• Az _1._utasítás, _2._utasítás… paraméterek azokat az utasításokat jelölik, amelyek akkor hajtódnak végre, ha Esetkifejezés = Érték_1, Esetkifejezés = Érték_2, és így tovább.
• Dióhéjban a feltétel az, hogy Esetkifejezés = Érték_N, és a művelet az N utasítás végrehajtása, ha a fenti eredmény IGAZ.
• Az ALIAS_NAME opcionális, és az SQL Server CASE utasítás eredményéhez adott aliasnév. Leginkább akkor használjuk, amikor a CASE-t használjuk az SQL Server SELECT záradékában.

Az egyszerű eset szabályai

• Az egyszerű esetkifejezés csak a Case_Expression egyenlőségvizsgálatát teszi lehetővé Value_1 és Value_N között.
• A Case_Expression-t egy értékkel hasonlítjuk össze, sorrendben, az első értékkel kezdve, azaz az Érték_1-gyel. Az alábbiakban a végrehajtási megközelítés látható:
• Ha a Case_Expression ekvivalens az Érték_1 értékkel, akkor a további WHEN…THEN utasítások kimaradnak, és a CASE végrehajtása azonnal VÉGEZÉS.
• Ha az Esetkifejezés nem egyezik meg az Érték_1 értékével, akkor az Esetkifejezést összehasonlítja az Érték_2 értékével az ekvivalencia szempontjából. Az Esetkifejezés és egy érték összehasonlításának folyamata addig folytatódik, amíg az Esetkifejezés egyező, ekvivalens értéket nem talál az Érték_1, Érték_2 stb. halmazából.
• Ha semmi sem egyezik, akkor a vezérlés az ELSE utasításra kerül, és a Statement_Else végrehajtásra kerül.
• Az ELSE nem kötelező.
• Ha az ELSE nincs jelen, és a Case_Expression egyik értékkel sem egyezik, akkor NULL jelenik meg.

Az alábbi ábra egy egyszerű eset végrehajtási folyamatát szemlélteti:

Példák

Tegyük fel, hogy van egy ' nevű táblánkGuru99' két oszloppal, a Tutorial_ID és a Tutorial_name oszlopokkal, valamint négy sorral, ahogy az alább látható. Ezt fogjuk használni: 'Guru99 lábas asztal a következő példákban.

1. lekérdezés: EGYSZERŰ ESET a NINCS MÁS opcióval

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Eredmény: Az alábbi ábra egy EGYSZERŰ ESET végrehajtási folyamatát mutatja be NO ELSE esetén.

2. lekérdezés: EGYSZERŰ ESET az ELSE opcióval

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Eredmény: Az alábbi ábra egy SIMPLE CASE végrehajtási folyamatát mutatja be ELSE függvény használatával.

Keresett CASE

A keresett eset szintaxisa

CASE 
     WHEN <Boolean_Expression_1> THEN Statement_1
     WHEN <Boolean_Expression_2> THEN Statement_2
     .
     .
     WHEN <Boolean_Expression_N> THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Itt:

• A Boolean_Expression_1… paraméter azt a kifejezést jelöli, amely IGAZ vagy HAMIS értékre lesz kiértékelve.
• Az _1._kifejezés_ és _2.__kifejezés… paraméterek azokat az utasításokat jelölik, amelyek akkor hajtódnak végre, ha a megfelelő _1.______ és _2.______ eredménye IGAZ.
• Dióhéjban a feltétel a Boolean_Expression_1… és a művelet az Statement_N végrehajtása, ha a fenti Boolean_Expression_1 IGAZ.
• Az ALIAS_NAME opcionális, és a CASE utasítás eredményének adott álnév. Leginkább akkor használjuk, amikor a CASE-t a SELECT záradékban használjuk.

A keresett eset szabályai

• Az Egyszerű esettel ellentétben a Keresett eset nem korlátozódik csak egyenlőség-ellenőrzésre, hanem logikai kifejezéseket is engedélyez.
• A logikai kifejezés kiértékelése sorrendben történik, az első logikai kifejezéssel kezdve, azaz a Boole_Kifejezés_1-gyel. Az alábbiakban a végrehajtási megközelítés látható:
• Ha a Boolean_Expression_1 IGAZ, akkor a további WHEN…THEN utasítások kimaradnak, és a CASE végrehajtása azonnal VÉGET ér.
• Ha a Boole_Expression_1 HAMIS, akkor a Boole_Expression_2 kiértékelése TRUE feltételt keres. A Boole-kifejezés kiértékelésének folyamata addig folytatódik, amíg az egyik Boole-kifejezés IGAZ értéket nem ad vissza.
• Ha semmi sem egyezik, akkor a vezérlés az ELSE utasításra kerül, és a Statement_Else végrehajtásra kerül.
• Az Egyszerű esethez hasonlóan az ELSE a Keresett esetben is opcionális.
• Ha az ELSE nincs jelen, és egyik logikai kifejezés sem ad vissza IGAZ értéket, akkor NULL érték jelenik meg.

Az alábbi ábra a Keresett eset végrehajtási folyamatát szemlélteti:

Példák

1. lekérdezés: KERESÉS ESET a NINCS MÁS opcióval

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Eredmény: Az alábbi ábra a KERESETT ÜGY végrehajtási folyamatát mutatja be NO ELSE esetén.

2. lekérdezés: KERESETT ESET az ELSE opcióval

SELECT Tutorial_ID, Tutorial_name,
CASE 
	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Eredmény: Az alábbi ábra a SEARCHED CASE végrehajtási folyamatát mutatja be ELSE utasítással.

Végrehajtási megközelítés közötti különbség: EGYSZERŰ és KERESÉSI ESET

Nézzük meg az alábbi EGYSZERŰ ESETRE vonatkozó példát:

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Itt a „Tutorial_name” a CASE kifejezés része az SQL-ben. Ezután a „Tutorial_name” értéket összehasonlítjuk minden WHEN értékkel, azaz „SQL”-lel… amíg a „Tutorial_name” meg nem egyezik egy WHEN értékkel.

Épp ellenkezőleg, a SEARCH CASE példában nincs CASE kifejezés:

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Itt minden WHEN utasításnak saját feltételes logikai kifejezése van. Minden logikai kifejezés, például Tutorial_name = 'SQL'…, IGAZ/HAMIS értékre kerül kiértékelésre, amíg meg nem találja az első olyan logikai kifejezést, amely IGAZ értéket ad vissza.

Az egyszerű és a keresett eset közötti különbség

Beágyazott CASE: CASE az IF ELSE-ben

Az IF…ELSE utasításokon belül használhatjuk a CASE kifejezést. változó a repülőjegyhez és a fiókhoz az értékén. Az alábbiakban egy példa MS-SQL kód látható:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
   PRINT 'Visit Nearby Tourist Location';
ELSE 
BEGIN
    SELECT
	CASE 
	WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
	WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
	WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
	END AS Location	
END

A fenti példában a CASE utasítás az IF…ELSE utasításba van beágyazva. Először az IF utasítás hajtódik végre, és ha az SQL Server CASE feltétele hamis, akkor az ELSE utasítás hajtódik végre. Az ELSE rész egy beágyazott CASE utasítást tartalmaz az SQL-ben. A repülőjegy értékétől függően az alábbi eredmények egyike jelenik meg:

• A rendszer a „Látogasson el a közeli turisztikai helyszínre” szöveget nyomtatja ki, ha a repülőjegyek ára > 400 dollár.
• A rendszer a „Látogasson el Los Angelesbe” szöveget nyomtatja ki, ha a repülőjegyek ára 0 ÉS 100 dollár KÖZÖTT van.
• A rendszer a „Visit New York” szöveget nyomtatja ki, ha a repülőjegyek ára 101 ÉS 200 dollár KÖZÖTT van.
• A rendszer a „Visit Europe” szöveget nyomtatja ki, ha a repülőjegyek ára 201 ÉS 400 dollár KÖZÖTT van.

Az alábbi eredmény azt a helyet mutatja, amelyet a beágyazott CASE függvény az ELSE ágon belüli futtatásakor ad vissza:

Beágyazott CASE: CASE a CASE belsejében

SQL-ben használhatunk CASE-t egy másik CASE-en belül. Az alábbiakban egy példa MS-SQL kód látható:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 250;
SELECT
CASE 
WHEN @Flight_Ticket >= 400 THEN 'Visit Nearby Tourist Location.'
WHEN @Flight_Ticket < 400 THEN 
    	CASE 
		WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
		WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
		WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
		END	
END AS Location

A fenti példában a CASE egy másik CASE utasításba van beágyazva. A rendszer először a külső CASE utasítást hajtja végre; ha a Flight_Ticket < $400, akkor a belső CASE fut le. A visszaadott hely ugyanazokat a jegyártartományokat követi, mint az előző példa, tehát egy 250 dolláros jegy a $201–$400 tartományba esik, és a 'Visit Europe' értéket adja vissza.

Az alábbi eredmény a belső CASE által visszaadott helyet mutatja egy 250 dolláros jegy esetén:

CASE UPDATE

Tegyük fel ismét, hogy megvan a 'Guru99 lábas táblázat két oszloppal és négy sorral, ahogy az alább látható:

A CASE-t használhatjuk az UPDATE funkcióval. Az alábbiakban egy példa az MS-SQL kódra:

UPDATE Guru99
SET Tutorial_Name = 
	(
	CASE
	WHEN Tutorial_Name = 'SQL' THEN 'Structured Query language.'
	WHEN Tutorial_Name = 'PL/SQL' THEN 'Oracle PL/SQL'
	WHEN Tutorial_Name = 'MSSQL' THEN 'Microsoft SQL.'
	WHEN Tutorial_Name = 'Hadoop' THEN 'Apache Hadoop.'
	END
	)

A fenti példában a CASE változót használjuk az UPDATE utasításban. A Tutorial_Name értékétől függően a Tutorial_Name oszlop frissül a THEN utasítás értékével:

• Ha a Tutorial_Name = 'SQL', akkor frissítse a Tutorial_Name értékét 'Strukturált lekérdezési nyelv' értékre.
• Ha a Tutorial_Name = 'PL/SQL', akkor frissítse a Tutorial_Name értékét ' értékreOracle PL/SQL.
• Ha a Tutorial_Name = 'MSSQL', akkor frissítse a Tutorial_Name értékét 'Microsoft SQL.
• Ha a Tutorial_Name = 'Hadoop', akkor frissítse a Tutorial_Name értéket 'Apache Hadoop'-ra.

Az utasítás futtatása frissíti az egyező sorokat az alábbiak szerint:

Kérdezzük meg a 'Guru99'-es táblázat a frissített értékek ellenőrzéséhez:

ESET Rendeléssel

Használhatjuk a CASE-t az ORDER BY-val. Az alábbiakban egy példa MS-SQL kód látható:

Declare @Order Int;
Set @Order = 1
Select * from Guru99 order by 
CASE 
	WHEN @Order = 1 THEN Tutorial_ID
	WHEN @Order = 2 THEN Tutorial_Name
	END
DESC

Itt a CASE-t használjuk az ORDER BY feltétellel. Az @Order értéke 1, és mivel az első WHEN logikai kifejezés TRUE értéket ad ki, a Tutorial_ID van kiválasztva az ORDER BY feltételhez. Az eredmény alább látható:

Érdekes tények!

• A CASE utasítás beágyazható egy másik CASE utasításba, valamint egy IF…ELSE utasításba is.
• A SELECT mellett a CASE más SQL záradékokkal is használható, például az UPDATE és az ORDER BY záradékokkal.

GYIK