Resumo : neste tutorial, você aprenderá como usar CASEinstruções MySQL para construir instruções condicionais complexas dentro de procedimentos armazenados.
Introdução à instrução CASE do MySQL
Além da IFinstrução, o MySQL fornece uma instrução condicional alternativa chamada CASEinstrução para construir instruções condicionais em procedimentos armazenados. As CASEinstruções tornam o código mais legível e eficiente.
A CASEdeclaração tem duas formas: declarações simples CASEe pesquisadas .CASE
Observe que se você quiser adicionar a lógica if-else a uma instrução SQL, use a CASEexpressão que difere da CASEinstrução descrita neste tutorial.
Instrução CASE simples
A seguir está a sintaxe básica da CASEinstrução simples:
CASE case_value
WHEN when_value1 THEN statements
WHEN when_value2 THEN statements
...
[ELSE else-statements]
END CASE;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nesta sintaxe, a CASEinstrução simples compara sequencialmente case_valueis com when_value1, when_value2e assim por diante até encontrar uma correspondência.
Quando CASEencontra um case_valueigual a a when_value, ele é executado statementsna THENcláusula correspondente.
Se CASEa instrução não puder encontrar nenhum when_valueigual a case_value, ela executará na else-statementscláusula ELSEse a ELSEcláusula for fornecida.
Quando a ELSEcláusula não existe e CASEnão consegue encontrar nenhum when_valueigual a case_value, ocorre um erro:
Case not found for CASE statementLinguagem de código: PHP ( php )Observe que case_valuepode ser um valor literal ou uma expressão. O statementspode consistir em uma ou mais instruções SQL e não pode estar vazio.
Para evitar o erro quando the case_valuenão é igual a any when_value, você pode usar um BEGIN...ENDbloco vazio na ELSEcláusula, como segue:
CASE case_value
WHEN when_value1 THEN ...
WHEN when_value2 THEN ...
ELSE
BEGIN
END;
END CASE;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A CASEinstrução simples testa a igualdade ( =), você não pode usá-la para testar a igualdade com NULL; porque NULL= NULLretorna FALSE.
Exemplo simples de instrução CASE
O procedimento armazenado a seguir ilustra como usar a CASEinstrução simples:
DELIMITER $$
CREATE PROCEDURE GetCustomerShipping(
IN pCustomerNUmber INT,
OUT pShipping VARCHAR(50)
)
BEGIN
DECLARE customerCountry VARCHAR(100);
SELECT
country
INTO customerCountry FROM
customers
WHERE
customerNumber = pCustomerNUmber;
CASE customerCountry
WHEN 'USA' THEN
SET pShipping = '2-day Shipping';
WHEN 'Canada' THEN
SET pShipping = '3-day Shipping';
ELSE
SET pShipping = '5-day Shipping';
END CASE;
END$$
DELIMITER ;Linguagem de código: PHP ( php )Como funciona.
O GetCustomerShipping()procedimento armazenado aceita dois parâmetros: pCustomerNumbercomo INparâmetro e pShippingcomo OUTparâmetro.
No procedimento armazenado:
Primeiro, selecione o país do cliente na customerstabela pelo número do cliente inserido.
Em segundo lugar, use o CASEextrato simples para determinar o tempo de envio com base no país do cliente. Se o cliente localizar em USA, o tempo de envio é de 2-day shipping. Se o cliente localizar em Canada, o tempo de envio é de 3-day shipping. Os clientes de outros países têm 5-day shipping.
O fluxograma a seguir demonstra a lógica da instrução CASE para determinar o tempo de envio:
Esta instrução chama o procedimento armazenado com o ID de cliente 112 e retorna as informações de envio:
CALL GetCustomerShipping(112,@shipping);
SELECT @shipping;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+----------------+
| @shipping |
+----------------+
| 2-day Shipping |
+----------------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Declaração CASE pesquisada
A declaração simples CASEpermite apenas comparar um valor com um conjunto de valores distintos.
Para realizar correspondências mais complexas, como intervalos, você usa a CASEinstrução pesquisada. A CASEafirmação pesquisada é equivalente à IF afirmação, porém é muito mais legível que a IFafirmação.
Aqui está a sintaxe básica da CASEinstrução pesquisada:
CASE
WHEN search_condition1 THEN statements
WHEN search_condition1 THEN statements
...
[ELSE else-statements]
END CASE;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nesta sintaxe, searched CASEavalia cada um search_conditionna WHENcláusula até encontrar uma condição que avalie como e TRUE, em seguida, executa a THENcláusula correspondente statements.
Se não search_conditionfor avaliado como TRUE, CASEserá executado else-statementsna ELSEcláusula se uma ELSEcláusula estiver disponível.
Semelhante à CASEinstrução simples, se você não especificar uma ELSEcláusula e nenhuma condição for TRUE, o MySQL gerará o mesmo erro:
Case not found for CASE statementLinguagem de código: PHP ( php )O MySQL também não permite que você tenha um vazio statementsna cláusula THENou ELSE. Se você não quiser manipular a lógica na ELSEcláusula enquanto evita que o MySQL gere um erro caso não search_conditionseja verdadeiro, você pode usar um BEGIN...END bloco vazio na ELSEcláusula.
Exemplo de instrução CASE pesquisada
O exemplo a seguir demonstra como usar um CASEextrato pesquisado para obter o status de entrega de um pedido com base no número de dias de espera:
DELIMITER $$
CREATE PROCEDURE GetDeliveryStatus(
IN pOrderNumber INT,
OUT pDeliveryStatus VARCHAR(100)
)
BEGIN
DECLARE waitingDay INT DEFAULT 0;
SELECT
DATEDIFF(requiredDate, shippedDate)
INTO waitingDay
FROM orders
WHERE orderNumber = pOrderNumber;
CASE
WHEN waitingDay = 0 THEN
SET pDeliveryStatus = 'On Time';
WHEN waitingDay >= 1 AND waitingDay < 5 THEN
SET pDeliveryStatus = 'Late';
WHEN waitingDay >= 5 THEN
SET pDeliveryStatus = 'Very Late';
ELSE
SET pDeliveryStatus = 'No Information';
END CASE;
END$$
DELIMITER ;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Como funciona.
O procedimento armazenado GetDeliveryStatus()aceita um número de pedido como INparâmetro e retorna o status de entrega como OUTparâmetro.
Primeiro, calcule o número de dias entre a data solicitada e a data de envio.
Segundo, determine o status da entrega com base no número de dias de espera usando o CASEextrato pesquisado:
- Se o número de dias de espera for zero, a entrega será pontual.
- Quando o número de dias de espera está entre 1 e 5, a entrega está atrasada.
- Quando o número de dias de espera é superior a 5 dias, a entrega atrasa muito.
- Se o número de dias de espera for NULL ou então, a entrega terá o status de sem informação especificada na
ELSEcláusula.
Esta instrução usa o procedimento armazenado GetDeliveryStatus()para obter o status de entrega do pedido 10100:
CALL GetDeliveryStatus(10100,@delivery);
SELECT @delivery;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+-----------+
| @delivery |
+-----------+
| Late |
+-----------+
1 row in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Instrução MySQL CASE vs. instrução IF
Ambas as instruções IFe CASEpermitem executar um bloco de código com base em uma condição específica. Escolher entre IFou CASEàs vezes é apenas uma questão de preferência pessoal.
Aqui estão algumas diretrizes gerais:
- Uma instrução simples
CASEé mais legível e eficiente do que umaIFinstrução quando você compara uma única expressão com um intervalo de valores exclusivos. - Ao verificar expressões complexas com base em vários valores, a
IFinstrução fica mais fácil de entender. - Se você usar a
CASEinstrução, deverá certificar-se de que pelo menos uma dasCASEcondições seja atendida. Caso contrário, você precisará definir um manipulador de erros para capturar o erro. Observe que você não precisa fazer isso com aIFinstrução. - Em algumas situações, você pode usar ambos
IFeCASEpara tornar o código mais legível e eficiente.
Resumo
- Use uma
CASEinstrução simples para avaliar uma expressão específica em relação a uma série de valores possíveis e executar as ações correspondentes. - Use uma
CASEinstrução pesquisada para avaliar diversas condições individualmente, permitindo um código mais flexível.
