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

In this issue, the editor will bring you about the usage of decode function in Oracle. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Two forms of 1.decode function the first form

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 IF second form

Decode (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.

Sign () function

Explanation:

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

Usage example:

Select id,sign (id-2) from t_decode

Screenshot effect:

Lpad () function

The length of field 0 is 2 before the field id

Select lpad (id,2,0) from t_decode

Some common ideas of 2.decode 's work are summarized a. Prepare test data

Create the test table t_decode and insert the test data

/ / create tables, insert data, and query create table t_decode (id integer, name varchar2 (10)); / / insert data insert into t_decode values (1memoria'); insert into t_decode values (2recordingb`); insert into t_decode values (3recordingc'); insert into t_decode values (4recordinga`)

Data style

b. The common idea of the first form of decode function 1. Easy to use: judge the string select id,name, decode (id,1,' first', 2 Magi 'second', 3 Magi 'third', 'none') new_id from t_decode

two。 Use the decode function to segment

Judge the size of the id in the table and divide different ranges according to the size

(- 2) low

[2pr 4) mid

[4BI -] high

Select id,name, decode (sign (id-4), 1Magnum high id',0,'high id','-1', decode (sign (id-2), 1mam id',0,'mid id',-1,'low id')) from t_decode

c. The common idea of the second form of decode function 1. Compare the size-compare the size of select decode (sign (100-90),-1 mine1000.90) from dual

two。 Use expressions to search for strings

Determine if there is an in name?

Select id,name,decode (instr (name,'a'), 0 as info from t_decode 'does not contain axiomy' contains a') as info from t_decode

3. Achieve row-column conversion

Note: decode is equivalent to: case when then else end statement

Select sum (decode (name,'a',id,0)) id_1, sum (decode (name,'b',id,0)) id_2, sum (decode (name,'c',id,0)) id_3 from t_decode

Add a test:

Select decode (name,'a',id,0) id_1, decode (name,'b',id,0) id_2, decode (name,'c',id,0) id_3 from tasking decodetransits-equals: case when then else endselect case name when'a 'then id else 0 end as id_1, case name when 'b'then id else 0 end as id_2, case name when 'c'then id else 0 end as id_3 from t_decode

4. Combined with the Lpad function, how to automatically add 1 to the value of the primary key and how to add 0 in front of the Lpad function, and how to automatically add 1 to the value of the primary key and add 0select lpad (decode (count (id), 0MagneMax (to_number (id) + 1)), 14) new_id from t_decode.

Split and explain in detail:

Select * from t_decode

Select lpad (id,2,0) from t_decode

Select to_number (id) from t_decode

Select max (to_number (id) + 1) from t_decode

Select decode (count (id), 0meme 1memmax (to_number (id) + 1)) from t_decode

Select lpad (decode (count (id), 0meme 1max (to_number (id) + 1)), 14meme 0') new_id from t_decode

This is what the use of the decode function in Oracle is like. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.