Shulou(Shulou.com)06/01 Report--

How is the decode function used in Oracle? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Meaning explanation:

Decode (condition, value 1, return value 1, value 2, return value 2. Value n, return value n, default)

This function has the following meaning:

IF condition = value 1 THEN RETURN (translation value 1) ELSIF condition = value 2 THEN RETURN (translation value 2) .ELSIF condition = value n THEN RETURN (translation value n) ELSE RETURN (default) END IFdecode (field or field operation, value 1, value 2, value 3)

The result of this function is that when the value of the field or field operation is equal to the value 1, the function returns the value 2, otherwise the value 3

Of course, values 1, 2, and 3 can also be expressions, which makes some sql statements much easier.

How to use it:

1. Compare the size

Select decode (sign (variable 1-variable 2),-1, variable 1, variable 2) from dual;-take a smaller value

The sign () function returns 0, 1,-1, respectively, depending on whether a value is 0, positive or negative.

For example:

Variable 1-10, variable 2-20

Then sign (variable 1-variable 2) returns-1 the decode decoding result is "variable 1", which achieves the purpose of taking a smaller value.

2. This function is used in the SQL statement. The function is described as follows:

The Decode function is similar to a series of nested IF-THEN-ELSE statements. Base_exp is compared with compare1,compare2 and so on in turn. If the base_exp matches the I th compare entry, the I th corresponding value is returned. If base_exp does not match any of the compare values, default is returned. Each compare value is evaluated sequentially, and if a match is found, the remaining comparevalue (if any) is no longer evaluated. A base_exp of NULL is considered to be equivalent to the NULL layout value. If necessary, each compare value is converted to the same data type as the first Compare value, which is also the type of return value.

The Decode function is very useful in practical development.

Combined with the Lpad function, how to automatically add 1 and 0 in front of the primary key.

Select LPAD (decode (count (record number), 0Magne1Maginmax (to_number (record number) + 1), 14memorial0') record No. From tetdmis eg: select decode (dir,1,0,1) from a1_interval

The value of dir is 1 to 0, and 0 to 1.

For example, I want to inquire about the number of boys and girls in a class.

Usually we write like this:

Select count (*) from form where gender = male; select count (*) from table where gender = female

It's too troublesome to union if you want to show it together.

With decode, it only takes one sentence.

Select sum (decode, male, 1 else 0), sum (decode (gender, female, 1 else 0)) from table eg:select sum (decode (siteno,'LT',1,0)), sum (decode (siteno,'SZ',1,0)) from facd605;select sum (case siteno when 'LT' then 1 else 0 end), sum (case siteno when' SZ' then 1 else 0 end) from facd605 Vinson's answer to the question on how to use the decode function in Oracle is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.