CASE-lauseke ja sisäkkäinen tapaus SQL Serverissä: T-SQL-esimerkki

Yleiskatsaus Case tosielämään!

Jälleen kerran, tosielämässä suoritamme erilaisia ​​toimia eri olosuhteiden lopputuloksesta riippuen. Tarkastellaanpa asiaa tarkemmin alla olevaa esimerkkiä:

• Jos lentoliput ovat alle 100 dollaria, vierailen Los Angelesissa.
• Jos lentoliput maksavat 100–200 dollaria, niin menen New Yorkiin.
• Jos lentoliput maksavat 200–400 dollaria, niin matkustan Eurooppaan.
• Muuten käyn mieluummin lähellä olevassa turistikohteessa.

Tarkastellaanpa ehdon ja toiminnan luokittelua erikseen yllä olevasta esimerkistä:

Yllä olevassa esimerkissä voimme nähdä, että eri ehtojen lopputulos ohjaa erillistä toimintoa. Esimerkiksi vierailija suorittaa New Yorkin-vierailun vain, jos lentolippu maksaa 100–200 dollaria. Vastaavasti MS SQL CASE-lauseke tarjoaa myös mahdollisuuden suorittaa erilaisia ​​T-SQL-lauseita eri ehtojen tulosten perusteella.

Mikä on CASE-lauseke SQL Serverissä?

CASE-selostus sisään SQL Server on jatke TAI MUUTEN lauseke. Toisin kuin IF…ELSE, jossa sallitaan enintään yksi ehto, CASE antaa käyttäjän soveltaa useita ehtoja eri toimintojen suorittamiseksi MS SQL:ssä. Se palauttaa käyttäjän määrittämään ehtoon liittyvän vastaavan arvon.

Opetellaan käyttämään CASE-kieltä SQL ja sen käsite seuraavissa osioissa. MS SQL:ssä on kahdenlaisia ​​CASE-käskyjä:

• Yksinkertainen CASE
• Etsittiin CASE

Yksinkertainen CASE

Yksinkertaisen tapauksen syntaksi

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]

Tässä:

• Parametri Case_Expression ilmaisee lausekkeen, jota lopulta verrataan Arvo_1:een, Arvo_2:een ja niin edelleen.
• Parametrit Lauseke_1, Lauseke_2… osoittavat lauseet, jotka suoritetaan, jos Tapauslauseke = Arvo_1, Tapauslauseke = Arvo_2 ja niin edelleen.
• Lyhyesti sanottuna ehto on, onko Case_Expression = Value_N, ja toiminto on Statement_N:n suorittaminen, jos yllä oleva tulos on TRUE.
• ALIAS_NAME on valinnainen ja se on SQL Serverin CASE-lausekkeen tulokselle annettu alias-nimi. Sitä käytetään enimmäkseen silloin, kun käytämme CASE-lauseketta SQL Serverin SELECT-lausekkeessa.

Yksinkertaisen tapauksen säännöt

• Yksinkertainen tapaus (Yksinkertainen Case) sallii Case_Expression-lausekkeen yhtäsuuruustarkistuksen vain arvoilla Value_1 ja Value_N.
• Case_Expression-lauseketta verrataan arvoon järjestyksessä alkaen ensimmäisestä arvosta, eli Arvo_1. Suoritustapa on seuraava:
• Jos Case_Expression vastaa arvoa_1, muut WHEN…THEN-käskyt ohitetaan ja CASE-suoritus PÄÄTTYY välittömästi.
• Jos Case_Expression ei vastaa Arvoa_1, Case_Expressiota verrataan Arvoon_2 vastaavuuden selvittämiseksi. Tämä Case_Expressionin ja arvon vertailuprosessi jatkuu, kunnes Case_Expression löytää vastaavan arvon Arvon_1, Arvon_2 ja niin edelleen joukosta.
• Jos mikään ei täsmää, ohjaus siirtyy ELSE-lausekkeeseen ja Statement_Else suoritetaan.
• ELSE on valinnainen.
• Jos ELSE-arvoa ei ole ja Case_Expression ei vastaa mitään arvoista, näytetään NULL.

Alla oleva kaavio havainnollistaa yksinkertaisen tapauksen suorituskulkua:

Esimerkit

Oletetaan, että meillä on taulukko nimeltä 'Guru99' kahdella sarakkeella, Tutorial_ID ja Tutorial_name, ja neljällä rivillä, kuten alla on esitetty. Käytämme tätä 'Guru99' pöytä seuraavissa esimerkeissä.

Kysymys 1: YKSINKERTAINEN TAPAUS EI MUUTA -vaihtoehdolla

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

Tulos: Alla oleva kaavio selittää SIMPLE CASE -menetelmän suoritusvirran ilman muuta.

Kysely 2: YKSINKERTAINEN TAPAUS MUUTEN-optiolla

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

Tulos: Alla oleva kaavio selittää SIMPLE CASE -operaation suoritusvirran ELSE-laskennan avulla.

Etsittiin CASE

Haetun tapauksen syntaksi

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]

Tässä:

• Parametri Boolean_Expression_1… ilmaisee lausekkeen, jonka arvoksi arvioidaan TOSI tai EPÄTOSI.
• Parametrit Lauseke_1, Lauseke_2… osoittavat lauseet, jotka suoritetaan, jos vastaava Boolean_Expression_1- ja Boolean_Expression_2-tulos on TOSI.
• Lyhyesti sanottuna ehto on Boolean_Expression_1… ja toiminto on lausekkeen_N suoritus, jos yllä oleva Boolean_Expression_1 on TOSI.
• ALIAS_NAME on valinnainen ja se on CASE-lauseen tulokselle annettu alias-nimi. Sitä käytetään enimmäkseen silloin, kun käytämme CASE-lauseketta SELECT-lausekkeessa.

Etsityn tapauksen säännöt

• Toisin kuin yksinkertainen tapaus, etsitty tapaus ei rajoitu pelkästään yhtäsuuruustarkistukseen, vaan se sallii myös totuusarvolausekkeen.
• Totuusarvolauseke lasketaan järjestyksessä alkaen ensimmäisestä totuusarvolausekkeesta, eli Totuusarvolauseke_1. Suoritustapa on seuraava:
• Jos Boolean_Expression_1 on TRUE, niin muut WHEN…THEN-lausekkeet ohitetaan ja CASE-suoritus PÄÄTTYY välittömästi.
• Jos Boolean_lauseke_1 on EPÄTOSI, Boolean_lauseke_2 arvioidaan TOSI-ehdon osalta. Tämä Boolean-lausekkeen arviointiprosessi jatkuu, kunnes jokin Boolean-lausekkeista palauttaa TOSI-arvon.
• Jos mikään ei täsmää, ohjaus siirtyy ELSE-lausekkeeseen ja Statement_Else suoritetaan.
• Kuten yksinkertainen tapaus, myös etsitty tapaus on valinnainen.
• Jos ELSE-lauseketta ei ole eikä mikään totuusarvolauseke palauta TOSI-arvoa, näytetään NULL.

Alla oleva kaavio havainnollistaa haetun tapauksen suorituskulkua:

Esimerkit

Kysely 1: HAETTU TAPAUS EI MUUTA -vaihtoehdolla

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

Tulos: Alla oleva kaavio selittää SEARCHED CASE -funktion suorituskulun ilman muuta.

Kysely 2: HAETTU TAPAUS MUUTEN-optiolla

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

Tulos: Alla oleva kaavio selittää SEARCHED CASE -funktion suorituskulun ELSE-funktion kanssa.

Suoritustavan välinen ero: SIMPLE ja SEARCH CASE

Katsotaanpa alla olevaa YKSINKERTAISTA TAPAUSTA:

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

Tässä 'Tutorial_name' on osa SQL:n CASE-lauseketta. Sitten 'Tutorial_name'-arvoa verrataan kaikkiin WHEN-arvoihin, eli 'SQL'… kunnes 'Tutorial_name' vastaa WHEN-arvoa.

Päinvastoin, SEARCH CASE -esimerkissä ei ole CASE-lauseketta:

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

Tässä jokaisella WHEN-lausekkeella on oma ehdollinen totuuslauseke. Jokaista totuuslauseketta, eli Tutorial_name = 'SQL'…, arvioidaan TOSI/EPÄTOSI-arvon suhteen, kunnes ensimmäinen totuuslauseke antaa tulokseksi TOSI.

Yksinkertaisen ja haetun tapauksen välinen ero

Sisäkkäinen CASE: CASE kohdassa IF ELSE

Voimme käyttää CASE-operaattoria IF…ELSE-lausekkeen sisällä. Esittelemme muuttuja lentolipulle ja sen arvolle haarautuvalle toimipisteelle. Alla on esimerkki MS-SQL-koodista:

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

Yllä olevassa esimerkissä CASE on sisäkkäin IF…ELSE-lausekkeen sisällä. Ensin suoritetaan IF-lauseke, ja jos SQL Serverin CASE-ehto on False, suoritetaan ELSE-lauseke. ELSE-osa sisältää sisäkkäisen CASE-lausekkeen SQL:ssä. Lentolipun arvosta riippuen näytetään yksi seuraavista tuloksista:

• Järjestelmä tulostaa tekstin 'Käy lähellä olevassa turistikohteessa', jos lentoliput maksavat yli 400 dollaria.
• Järjestelmä tulostaa "Visit Los Angeles", jos lentoliput ovat 0–100 dollarin välillä.
• Järjestelmä tulostaa "Visit New York", jos lentoliput ovat 101–200 dollarin välillä.
• Järjestelmä tulostaa "Visit Europe", jos lentoliput ovat 201–400 dollarin välillä.

Alla oleva tulos näyttää sijainnin, joka palautetaan, kun sisäkkäinen CASE-käsky suoritetaan ELSE-haaran sisällä:

Sisäkkäinen CASE: CASE CASE:n sisällä

Voimme käyttää CASE-käskyä toisen CASE-käskyn sisällä SQL:ssä. Alla on esimerkki MS-SQL-koodista:

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

Yllä olevassa esimerkissä CASE on sisäkkäin toisen CASE-lausekkeen sisällä. Järjestelmä suorittaa ensin ulomman CASE-lausekkeen; jos Flight_Ticket < $400, sisempi CASE suoritetaan. Palautettu sijainti noudattaa samoja lippualueita kuin edellisessä esimerkissä, joten 250 dollarin lippu sijoittuu $201–$400 välille ja palauttaa 'Visit Europe'.

Alla oleva tulos näyttää sisäisen CASE-funktion palauttaman sijainnin 250 dollarin lipulle:

TAPAUS PÄIVITYKSEN kanssa

Oletetaan jälleen, että meillä on 'Guru99' taulukko, jossa on kaksi saraketta ja neljä riviä, kuten alla olevassa kuvassa:

Voimme käyttää CASEa UPDATE:n kanssa. Alla on esimerkki MS-SQL-koodista:

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
	)

Yllä olevassa esimerkissä UPDATE-lausekkeessa käytetään CASE-metodia. Tutorial_Name-arvosta riippuen Tutorial_Name-sarake päivittyy THEN-lausekkeen arvolla:

• Jos Tutorial_Name = 'SQL', päivitä Tutorial_Name muotoon 'Structured Query language'.
• Jos Tutorial_Name = 'PL/SQL', päivitä Tutorial_Name arvoon 'Oracle PL/SQL.
• Jos Tutorial_Name = 'MSSQL', päivitä Tutorial_Name arvoon 'Microsoft SQL.
• Jos Tutorial_Name = 'Hadoop', päivitä Tutorial_Name arvoksi 'Apache Hadoop'.

Lausekkeen suorittaminen päivittää vastaavat rivit alla olevan mukaisesti:

Kysytäänpä asiaa 'Guru99' taulukko päivitettyjen arvojen tarkistamiseksi:

TAPAUS tilauksella

Voimme käyttää CASE-lauseketta ORDER BY -lausekkeen kanssa. Alla on esimerkki MS-SQL-koodista:

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

Tässä käytetään CASE-lauseketta ORDER BY -lausekkeen kanssa. @Order asetetaan arvoon 1, ja koska ensimmäinen WHEN-totuusarvolauseke antaa tulokseksi TRUE, ORDER BY -ehdoksi valitaan Tutorial_ID. Tulos näkyy alla:

Mielenkiintoisia seikkoja!

• CASE-lausekkeen voi sisäkkäin sijoittaa toisen CASE-lausekkeen sisään sekä IF…ELSE-lausekkeen sisään.
• SELECTin lisäksi CASE-lauseita voidaan käyttää muiden SQL-lauseiden, kuten UPDATE:n ja ORDER BY:n, kanssa.

UKK