CASE 是一个复杂函数,它具有两种形式;simple-when 形式和 searched-when 形式。在任何一种形式中,CASE 返回一个值,其结果控制后续处理的路径。
这两种 CASE 形式都根据 WHEN 子句中定义的一组规则返回一个值。
在 simple-when 格式中,source_value 与每个 test_value 比较,直至找到匹配。CASE 函数的结果是相应的 result_value 的值。因此,source_value 的数据类型必须可与每个 test_value 的数据类型比较。
CASE 函数必须至少有一个 WHEN。ELSE 是可选的。缺省 ELSE 为 NULL。CASE 表达式由 END 定界。测试值不必是文字值。
searched-when 子句版本相似,但有允许测试许多不同值的其他灵活性。
DECLARE CurrentMonth CHAR; DECLARE MonthText CHAR; SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2); SET MonthText = CASE CurrentMonth WHEN '01' THEN 'January' WHEN '02' THEN 'February' WHEN '03' THEN 'March' WHEN '04' THEN 'April' WHEN '05' THEN 'May' WHEN '06' THEN 'June' ELSE 'Second half of year' END
DECLARE CurrentMonth CHAR; DECLARE MonthText CHAR; SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2); SET MonthText = CASE WHEN Month = '01' THEN 'January' WHEN Month = '02' THEN 'February' WHEN Month = '03' THEN 'March' WHEN Month = '04' THEN 'April' WHEN Month = '05' THEN 'May' WHEN Month = '06' THEN 'June' ELSE 'Second half of year' END
DECLARE CurrentMonth CHAR; DECLARE CurrentYear CHAR; DECLARE MonthText CHAR; SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2); SET CurrentYear = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 1 FOR 4); SET MonthText = CASE WHEN CurrentMonth = '01' THEN 'January' WHEN CurrentMonth = '02' THEN 'February' WHEN CurrentMonth = '03' THEN 'March' WHEN CurrentYear = '2000' THEN 'A month in the Year 2000' WHEN CurrentYear = '2001' THEN 'A month in the Year 2001' ELSE 'Not first three months of any year or a month in the Year 2000 or 2001' END;