Declarație CASE și caz imbricat în SQL Server: Exemplu T-SQL

Prezentare generală a Casei în viața reală!

Din nou, în viața reală, efectuăm acțiuni diferite în funcție de rezultatul diferitelor condiții. Pentru a detalia mai mult, luați în considerare exemplul de mai jos:

• Dacă biletele de avion sunt mai mici de 100 USD, atunci voi vizita Los Angeles.
• Dacă biletele de avion costă între 100 și 200 de dolari, atunci voi vizita New York-ul.
• Dacă biletele de avion costă între 200 și 400 de dolari, atunci voi vizita Europa.
• Altfel, aș prefera să vizitez un obiectiv turistic din apropiere.

Să luăm în considerare clasificarea condiției și acțiunii separat din exemplul de mai sus:

În exemplul de mai sus, putem observa că rezultatul diferitelor condiții guvernează o acțiune separată. De exemplu, vizitatorul va efectua actul de a vizita New York-ul doar în condiția ca biletul de avion să fie între 100 și 200 de dolari. În mod similar, MS SQL Instrucțiunea CASE oferă, de asemenea, capacitatea de a executa diferite instrucțiuni T-SQL pe baza rezultatului diferitelor condiții.

Ce este instrucțiunea CASE în SQL Server?

Declarație CASE în SQL Server este extensia DACA... ALTE Spre deosebire de IF…ELSE, unde este permisă o singură condiție, CASE permite utilizatorului să aplice mai multe condiții pentru a efectua diferite seturi de acțiuni în MS SQL. Returnează o valoare corespunzătoare asociată cu condiția definită de utilizator.

Să învățăm cum să folosim CASE în SQL și conceptul său în secțiunile următoare. În MS SQL, există două tipuri de CASE:

• CAZ simplu
• S-a căutat CASE

CAZ simplu

Sintaxa pentru cazul simplu

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]

Aici:

• Parametrul Case_Expression denotă expresia care va fi în cele din urmă comparată cu Value_1, Value_2 și așa mai departe.
• Parametrii Statement_1, Statement_2… denotă instrucțiunile care vor fi executate dacă Case_Expression = Value_1, Case_Expression = Value_2 și așa mai departe.
• Pe scurt, condiția este dacă Case_Expression = Value_N, iar acțiunea este execuția Statement_N dacă rezultatul de mai sus este TRUE.
• ALIAS_NAME este opțional și este numele alias dat rezultatului instrucțiunii SQL Server CASE. Este utilizat mai ales atunci când folosim CASE în clauza SELECT din SQL Server.

Reguli pentru cazuri simple

• Simple Case permite doar o verificare a egalității Case_Expression cu Value_1 la Value_N.
• Case_Expression este comparată cu o valoare, în ordine, începând de la prima valoare, adică Value_1. Mai jos este abordarea de execuție:
• Dacă Case_Expression este echivalentă cu Value_1, atunci alte instrucțiuni WHEN…THEN sunt omise, iar execuția CASE se va TERMINA imediat.
• Dacă Case_Expression nu se potrivește cu Value_1, atunci Case_Expression este comparată cu Value_2 pentru echivalență. Acest proces de comparare a Case_Expression cu o valoare continuă până când Case_Expression găsește o valoare echivalentă corespunzătoare din setul Value_1, Value_2 și așa mai departe.
• Dacă nimic nu se potrivește, controlul trece la instrucțiunea ELSE, iar se execută Statement_Else.
• ELSE este opțional.
• Dacă ELSE nu este prezent și Case_Expression nu se potrivește cu niciuna dintre valori, atunci se afișează NULL.

Diagrama de mai jos ilustrează fluxul de execuție al unui Caz ​​Simplu:

Exemple

Să presupunem că avem un tabel numit 'Guru99' cu două coloane, Tutorial_ID și Tutorial_name, și patru rânduri, așa cum se arată mai jos. Vom folosi acest 'GuruTabelul 99' din exemplele care urmează.

Interogarea 1: CAZ SIMPLU cu opțiunea 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

Rezultat: Diagrama de mai jos explică fluxul de execuție al unui CAZ SIMPLU fără ALTCEVA.

Interogarea 2: CAZ SIMPLU cu opțiunea 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

Rezultat: Diagrama de mai jos explică fluxul de execuție al unui caz SIMPLE cu ELSE.

S-a căutat CASE

Sintaxa pentru cazul căutat

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]

Aici:

• Parametrul Boolean_Expression_1… denotă expresia care va fi evaluată pentru ADEVĂRAT sau FALS.
• Parametrii Statement_1, Statement_2… denotă instrucțiunile care se vor executa dacă rezultatul corespunzător Boolean_Expression_1, Boolean_Expression_2 este TRUE.
• Pe scurt, condiția este Boolean_Expression_1… iar acțiunea este execuția Statement_N dacă Boolean_Expression_1 de mai sus este TRUE.
• ALIAS_NAME este opțional și este numele alias dat rezultatului instrucțiunii CASE. Este folosit mai ales atunci când folosim CASE în clauza SELECT.

Reguli pentru cazul căutat

• Spre deosebire de cazul simplu, cazul căutat nu se limitează doar la o verificare a egalității, ci permite o expresie booleană.
• Expresia booleană este evaluată, în ordine, începând de la prima expresie booleană, adică Boolean_Expression_1. Mai jos este prezentată abordarea de execuție:
• Dacă Boolean_Expression_1 este TRUE, atunci instrucțiunile WHEN…THEN ulterioare sunt omise, iar execuția CASE se va END imediat.
• Dacă Boolean_Expression_1 este FALSE, atunci Boolean_Expression_2 este evaluată pentru o condiție TRUE. Acest proces de evaluare a expresiei booleene continuă până când una dintre expresiile booleene returnează TRUE.
• Dacă nimic nu se potrivește, controlul trece la instrucțiunea ELSE, iar se execută Statement_Else.
• La fel ca în cazul simplu, ELSE este opțional și în cazul căutat.
• Dacă ELSE nu este prezent și niciuna dintre expresiile booleene nu returnează TRUE, atunci se afișează NULL.

Diagrama de mai jos ilustrează fluxul de execuție al Cazului Căutat:

Exemple

Interogarea 1: CAZ CĂUTAT cu opțiunea 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

Rezultat: Diagrama de mai jos explică fluxul de execuție al CAZULUI CĂUTAT cu NICIUN ALTĂ opțiune.

Interogarea 2: CAZ CĂUTAT cu opțiunea 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.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Rezultat: Diagrama de mai jos explică fluxul de execuție al CAZULUI CĂUTAT cu ALTFEL.

Diferența dintre abordarea de execuție: SIMPLE și SEARCH CASE

Să aruncăm o privire la exemplul SIMPLU de mai jos:

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

Aici, „Tutorial_name” face parte din expresia CASE din SQL. Apoi, valoarea „Tutorial_name” este comparată cu fiecare valoare WHEN, adică „SQL”… până când „Tutorial_name” se potrivește cu o valoare WHEN.

Dimpotrivă, exemplul SEARCH CASE nu are nicio expresie CASE:

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

Aici, fiecare instrucțiune WHEN are propria expresie booleană condiționată. Fiecare expresie booleană, de exemplu, Tutorial_name = 'SQL'… este evaluată pentru TRUE/FALSE până la prima expresie booleană care se evaluează ca TRUE.

Diferența dintre cazul simplu și cel căutat

CASE imbricat: CASE în IF ELSE

Putem folosi CASE în interiorul unei instrucțiuni IF…ELSE. Declarăm un variabil pentru biletul de avion și filiala în funcție de valoarea sa. Mai jos este exemplul de cod 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

În exemplul de mai sus, CASE este imbricat în instrucțiunea IF…ELSE. Mai întâi, se execută instrucțiunea IF, iar dacă condiția CASE din SQL Server este False, atunci se execută instrucțiunea ELSE. Partea ELSE conține o instrucțiune CASE imbricată în SQL. În funcție de valoarea biletului de avion, se afișează unul dintre următoarele rezultate:

• Sistemul afișează „Vizitați locația turistică din apropiere” dacă biletele de avion costă > 400 USD.
• Sistemul afișează „Vizitați Los Angeles” dacă biletele de avion costă ÎNTRE 0 USD ȘI 100 USD.
• Sistemul afișează „Vizitați New York” dacă biletele de avion costă ÎNTRE 101 USD ȘI 200 USD.
• Sistemul afișează „Vizitați Europa” dacă biletele de avion costă ÎNTRE 201 USD ȘI 400 USD.

Rezultatul de mai jos arată locația returnată atunci când CASE imbricat rulează în interiorul ramurii ELSE:

CASE imbricate: CASE în interiorul CASE

Putem folosi CASE în interiorul unui alt CASE în SQL. Mai jos este un exemplu de cod 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

În exemplul de mai sus, instrucțiunea CASE este imbricată într-o altă instrucțiune CASE. Sistemul execută mai întâi instrucțiunea CASE externă; dacă Flight_Ticket < 400 USD, se execută instrucțiunea CASE internă. Locația returnată respectă aceleași intervale de bilete ca în exemplul anterior, deci un bilet de 250 USD se încadrează în intervalul 201 USD–400 USD și returnează „Vizitează Europa”.

Rezultatul de mai jos arată locația returnată de CASE-ul intern pentru un bilet de 250 USD:

CAZ cu UPDATE

Să presupunem din nou că avem 'GuruTabel de 99' cu două coloane și patru rânduri, așa cum se arată mai jos:

Putem folosi CASE cu UPDATE. Mai jos este exemplul de cod 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
	)

În exemplul de mai sus, CASE este utilizat în instrucțiunea UPDATE. În funcție de valoarea Tutorial_Name, coloana Tutorial_Name este actualizată cu valoarea instrucțiunii THEN:

• Dacă Tutorial_Name = 'SQL', atunci actualizați Tutorial_Name la 'Limbaj de interogare structurată'.
• Dacă Tutorial_Name = 'PL/SQL', atunci actualizați Tutorial_Name la 'Oracle PL/SQL.
• Dacă Nume_Tutorial = 'MSSQL', atunci actualizați Nume_Tutorial la 'Microsoft SQL.
• Dacă Tutorial_Name = „Hadoop”, atunci actualizați Tutorial_Name la „Apache Hadoop”.

Rularea instrucțiunii actualizează rândurile corespunzătoare, așa cum se arată mai jos:

Să interogăm „GuruTabelul 99' pentru verificarea valorilor actualizate:

CAZ cu Comanda de

Putem folosi CASE cu ORDER BY. Mai jos este exemplul de cod 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

Aici, se folosește CASE cu ORDER BY. @Order este setat la 1, iar când prima expresie booleană WHEN se evaluează ca TRUE, se selectează Tutorial_ID pentru condiția ORDER BY. Rezultatul este prezentat mai jos:

Fapte interesante!

• CASE poate fi imbricat atât într-un alt CASE, cât și într-o instrucțiune IF…ELSE.
• Pe lângă SELECT, CASE poate fi utilizată cu alte clauze SQL, cum ar fi UPDATE și ORDER BY.

Întrebări frecvente