Case when then and decode Analysis and Comparison

Case when is convenient when different conditions require different return values. It can be used when assigning values to variables or in select query statements.

(Exit immediately if one meets the requirement)

-- Search format, NO content after case

Str: = case

When I = 1 then 'A'

When I = 2 then 'B'

Else 'C'

End; -- assign a value here, which is an expression and does not use end case. If str is not assigned, end case is used.

-- Expression statement format. The required variables are placed after case, not in when.

Case var

When 1 then 'A'

When 2 then 'B'

Else 'C'

End;

Note: In the search, "when" is a condition and "when" is a value. There is no semicolon in the middle, and it is a whole link

(The expression format is similar to decode, that is, each value is distinguished by value)

Decode (var, 1, 'A', 2, 'B', 'C ')

-- Used in select

Select case I when 1 then 'A'

When 2 then 'B'

When 3 then 'C'

Else 'D'

End case from table1;

Case when and decode can be used for column-to-row conversion.

Select Name,
Sum (decode (Course, 'China', Grade, 0) language,
Sum (decode (Course, 'mate', Grade, 0) mathematics,
Sum (decode (Course, 'English ', Grade, 0) English
From S
Group by Name;

-------------

End and end case: Add and not add case after end

"End" must be used in the expression, while "end" must be used in PL/SQL process judgment.End case".

-------------------------

Comparison:

Case expression

Decode Function

Decode can avoid multiple scans and improve performance. if then else function is provided when case is 9i

The decode statement can violate row 3NF and the column cannot be split, but the column cannot be repeated.): duplicate columns.

The two are basically the same, but there is a slight difference in performance and the simplicity of writing.

Oracle mentioned in the document that CASE statements are more efficient, especially the CASE expression WHEN constant THEN syntax, which is more efficient than the case when expression THEN syntax.

This article is from the "Running antelope" blog, please be sure to keep this source http://heshw.blog.51cto.com/5891747/1293709