Příkaz CASE a vnořený případ na serveru SQL: Příklad T-SQL

Přehled případu v reálném životě!

V reálném životě opět provádíme různé akce v závislosti na výsledku různých podmínek.

Chcete-li to upřesnit, zvažte níže uvedený příklad:

• Pokud jsou letenky nižší než 100 USD, navštívím Los Angeles.
• Pokud jsou letenky mezi 100 až 200 dolary, navštívím New York
• Pokud jsou letenky mezi 200 až 400 dolary, navštívím Evropu
• Jinak raději navštívím nějaké blízké turistické místo.

Zvažme kategorizaci stavu a akce odděleně od výše uvedeného příkladu:

Podmínky - Letenky	Provedené akce, pouze pokud je podmínka TRUE
Less než $ 100	Navštivte Los Angeles
Mezi $ 100 až $ 200	Navštivte New York
Mezi $ 200 až $ 400	Navštivte Evropu
Žádná z výše uvedených podmínek nebyla splněna	Blízké turistické místo

Ve výše uvedeném příkladu můžeme vidět, že výsledkem různých podmínek je řízení samostatné akce. Např. Návštěvník provede akt návštěvy New Yorku pouze za podmínky, že letenka je mezi 100 až 200 USD.

Podobně příkaz MS SQL CASE také poskytuje možnost provádět různé příkazy T-SQL na základě výsledku různých podmínek.

V následujících částech se naučíme, jak používat Case v SQL a jeho koncept.

In MS SQL, existují dva typy CASE.

1. Jednoduchý PŘÍPAD
2. Vyhledán CASE

Jednoduchý PŘÍPAD

Syntaxe pro Simple Case

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]

Zde,

• Parametr Case_Expression označuje výraz, ke kterému budeme nakonec přirovnáni Hodnota_1, Hodnota_2, ...
• Parametry Výpis_1, Výpis_2… označují příkazy, které budou provedeny, pokud Case_Expression = Hodnota_1, Case_Expression = Hodnota_2, … a tak dále.
• Stručně řečeno, stav je zda Case_Expression = Hodnota_N a ACTION je provedení příkazu_N, pokud je výše uvedený výsledek je PRAVDA.
• ALIAS_NAME je volitelný a jedná se o název aliasu daného výsledku příkazu SQL Server CASE. Většinou se používá, když používáme klauzuli Case v SQL serveru select.

Pravidla pro jednoduchý případ

• Simple Case umožňuje pouze kontrolu rovnosti Case_Expression s Value_1 až Value_N.
• Case_Expression se porovnává s hodnotou v pořadí počínaje první hodnotou, tj. Value_1. Níže je uveden postup provádění:
• Pokud je Case_Expression ekvivalentní Value_1, pak jsou další příkazy WHEN…THEN přeskočeny a provádění CASE se okamžitě UKONČÍ.
• Pokud Case_Expression neodpovídá Value_1, Case_Expression se porovná s Value_2 pro ekvivalenci. Tento proces porovnávání Case_Expression s Value bude pokračovat, dokud Case_Expression nenajde odpovídající ekvivalentní hodnotu ze sady Value_1, Value_2,…
• Pokud nic neodpovídá, pak řízení přejde na příkaz ELSE a Statement_Else se provede.
• ELSE je nepovinné.
• Pokud ELSE není přítomno a Case_Expression se neshoduje s žádnou z hodnot, pak Zobrazí se null.

Níže uvedený diagram ilustruje průběh provádění Simple Case.

Příklady

Předpoklad: Předpokládejme, že máme tabulku jako 'Guru99' se dvěma sloupci a čtyřmi řádky, jak je uvedeno níže:

Budeme používat 'Guru99' tabulka v dalších příkladech

Dotaz 1: JEDNODUCHÝ PŘÍPAD s volbou NO ELSE

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

Výsledek: Níže uvedený diagram vysvětluje průběh provádění JEDNODUCHÉHO PŘÍPADU bez ŽÁDNÝCH JINÝCH.

Dotaz 2: JEDNODUCHÝ PŘÍPAD s volbou ELSE.

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

Výsledek: Níže uvedený diagram vysvětluje průběh provádění JEDNODUCHÉHO PŘÍPADU s ELSE.

Vyhledán CASE

Syntaxe pro Searched Case

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]

Zde,

• Parametr Boolean_Expression_1, … označuje výraz, který bude vyhodnocen jako TRUE nebo FALSE.
• Parametry Příkaz_1, Příkaz_2… označují Příkazy, které se provedou, pokud jejich odpovídající výsledek Boolean_Expression_1, Boolean_Expression_2 je PRAVDA.
• Stručně řečeno, Condition je Boolean_Expression_1,… a ACTION je provedení Statement_N, pokud je výše boolean_Expression_1 TRUE.
• ALIAS_NAME je volitelný a jedná se o název aliasu přidělený výsledku příkazu CASE. Většinou se používá, když používáme CASE v klauzuli select.

Pravidla pro hledaný případ

• Na rozdíl od jednoduchého případu se Searched Case neomezuje pouze na kontrolu rovnosti, ale umožňuje booleovský výraz.
• Booleovský výraz je vyhodnocen v pořadí počínaje prvním booleovským výrazem, tj. Booleovský_výraz_1. Níže je uveden postup provádění:
  • Pokud má Boolean_expression_1 hodnotu TRUE, pak jsou další příkazy WHEN…THEN přeskočeny a provádění CASE se okamžitě UKONČÍ.
  • Pokud je logický_výraz_1 NEPRAVDA, pak je logický_výraz_2 vyhodnocen jako podmínka TRUE. Tento proces hodnocení Boolean_expression bude pokračovat, dokud jeden z Boolean_expression nevrátí hodnotu TRUE.
  • Pokud nic neodpovídá, pak řízení přejde na příkaz ELSE a Statement_Else se provede.
• Stejně jako Simple Case ELSE je volitelné také v případě vyhledávání.
• Pokud ELSE není přítomen a žádný z booleovských_výrazů nevrací hodnotu TRUE, zobrazí se Null.

Níže uvedený diagram ilustrují tok provádění Hledaný případ.

Příklady

Dotaz 1: VYHLEDÁVANÝ PŘÍPAD s volbou NO ELSE

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

Výsledek: Níže uvedený diagram vysvětluje průběh provádění z VYHLEDÁVANÝ PŘÍPAD s ŽÁDNÝ JINÝ.

Dotaz 2: VYHLEDÁVANÝ PŘÍPAD s ELSE volba.

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

Výsledek: Níže uvedený diagram vysvětluje průběh provádění of VYHLEDÁVANÝ PŘÍPAD s JINÝ.

Rozdíl mezi přístupem provádění: SIMPLE a SEARCH CASE.

Pojďme se na to podívat JEDNODUCHÝ PŘÍPAD příklad níže:

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

Zde, 'název_tutoriálu' je součástí výrazu CASE v SQL. Pak 'název_tutoriálu' hodnota se porovnává s každým KDYŽ hodnoty, tj. 'SQL'… dokud se 'Název_výukového programu' neshoduje s hodnotami WHEN.

Naopak, HLEDAT PŘÍPAD příklad nemá č CASE výraz:

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

Tady, každý prohlášení WHEN má své Podmíněný booleovský výraz. Každý Booleovský výraz tj. Tutorial_name = 'SQL',… je vyhodnoceno pro PRAVDA/NEPRAVDA až do první booleovský výraz, který se vyhodnocuje k TRUE.

Rozdíl mezi jednoduchým a hledaným případem

Jednoduchý případ	Hledaný případ
Za klíčovým slovem CASE bezprostředně následuje CASE_Expression a před příkazem WHEN. Např: POUZDRO KDYŽ Hodnota_1 THEN Statement_1…	Za klíčovým slovem Case následuje příkaz WHEN a mezi CASE a WHEN není žádný výraz. Např: PŘÍPAD KDY THEN Statement_1…
V jednoduchých případech existuje HODNOTA pro každý příkaz WHEN. Tyto hodnoty: Value_1, Value_2… Jsou postupně porovnávány s jedním CASE_Expression. Výsledek dostane vyhodnocení pro podmínku TRUE/FALSE pro každý příkaz WHEN. Např: POUZDRO KDYŽ Hodnota_1 THEN Statement_1… KDYŽ Hodnota_2 THEN Statement_2…	V Searched Case existuje Boolean_Expression pro každý příkaz WHEN. Tento Boolean_Expressions: Boolean_Expression_1, Boolean_Expression_2,… vyhodnocuje podmínku TRUE/FALSE pro každý příkaz WHEN. Např: CASE KDYŽ THEN Statement_1… KDYŽ THEN Statement_2…
Simple Case podporuje pouze kontrolu rovnosti. Tj. zda CASE_Expression = VALUE_1, VALUE_2… Např: VĚC WHEN Value_1 THEN Statement_1…Ve výše uvedeném příkladu je jedinou operací, kterou systém provede, kontrola, zda Case_Expression = Hodnota_1	S Boolean_Expression_N podporuje Search Case jakoukoli operaci, která má za následek logickou hodnotu. Zahrnuje stejný a nerovný operátor. Např: PŘÍPAD KDY THEN Statement_1… Ve výše uvedeném příkladu může Boolean_Expression_1 obsahovat operátory „rovná se“ i „nerovná se“ jako A = B, A != B.

Vnořený CASE: CASE v IF ELSE

Můžeme použít POUZDRO uvnitř POKUD JINAK. Níže je uveden příklad kódu MS-SQL

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

Ve výše uvedeném příkladu je CASE VNOŘENO do příkazu IF…ELSE:

Nejprve se provede příkaz IF a pokud bude případová podmínka v sqlserver je False, pak se provede příkaz ELSE.

Else uvnitř obsahuje vnořený příkaz CASE v SQL. V závislosti na hodnotě letenky se zobrazí jeden z následujících výsledků:

• Pokud jsou letenky > 400 USD, systém vytiskne „Navštivte turistické místo v okolí“.
• Systém vytiskne 'Navštivte Los Angeles', pokud jsou letenky MEZI 0 A 100 USD
• Systém vytiskne 'Navštivte New York', pokud jsou letenky MEZI 101 AŽ 200 USD
• Systém vytiskne „Navštivte Evropu“, pokud jsou letenky MEZI 201 AŽ 400 USD

Vnořené CASE: CASE uvnitř CASE

Můžeme použít CASE uvnitř CASE v SQL. Níže je uveden příklad kódu MS-SQL

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

Ve výše uvedeném příkladu je CASE VNOŘENO do jiného příkazu CASE:

Systém začíná provedením vnějšího CASE. Pokud Flight_Ticket < 400 $, pak se spustí vnitřní CASE.

V závislosti na hodnotě letenky se zobrazí jeden z následujících výsledků:

• Pokud jsou letenky > 400 USD, systém vytiskne „Navštivte turistické místo v okolí“.
• Systém vytiskne 'Navštivte Los Angeles', pokud jsou letenky MEZI 0 A 100 USD
• Systém vytiskne 'Navštivte New York', pokud jsou letenky MEZI 101 AŽ 200 USD
• Systém vytiskne „Navštivte Evropu“, pokud jsou letenky MEZI 201 AŽ 400 USD

CASE s AKTUALIZACÍ

Předpoklad: Předpokládejme, že máme tabulku jako „Guru99“ se dvěma sloupci a čtyřmi řádky, jak je zobrazeno níže:

V dalších příkladech použijeme tabulku 'Guru99'

Můžeme použít CASE s UPDATE. Níže je ukázkový kód MS-SQL:

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
	)

Ve výše uvedeném příkladu se CASE používá v příkazu UPDATE.

V závislosti na hodnotě Tutorial_Name získá sloupec Tutorial_Name aktualizaci s hodnotou příkazu THEN.

• Pokud Tutorial_Name = 'SQL' PAK aktualizujte Tutorial_Name na 'Structured Query language'
• If Tutorial_Name = 'PL/SQL' PAK aktualizujte Tutorial_Name na 'Oracle PL/SQL'
• If Tutorial_Name = 'MSSQL' PAK aktualizujte Tutorial_Name na 'Microsoft SQL'
• Pokud Tutorial_Name = 'Hadoop' PAK aktualizujte Tutorial_Name na 'Apache Hadoop'

Pojďme se dotázat na tabulku Guru99 a zkontrolovat aktualizovanou hodnotu:

CASE s Objednávkou podle

Můžeme použít CASE s Order By. Níže je ukázkový kód MS-SQL:

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

Zde se CASE používá s Order By.

@Pořadí je nastaveno na 1 a jako první, KDYŽ se booleovský výraz vyhodnotí jako PRAVDA, je vybráno ID kurzu pro pořadí podle podmínky

Zajímavosti!

• CASE lze vnořit do jiného příkazu CASE i do jiného příkazu IF…ELSE.
• Kromě SELECT lze CASE použít s jiným SQL klauzule jako UPDATE, ORDER BY.

Shrnutí

• V MS SQL existují dva typy CASE: Simple CASE a Searched CASE
• ELSE je v příkazu CASE nepovinné.