Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the decode function in Oracle. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope that after reading this article, you will have a certain understanding of relevant knowledge.

Function description:

DECODE function is one of the powerful functions of ORACLE PL/SQL. At present, only ORACLE SQL provides this function, and SQL implementations of other database manufacturers do not have this function. What is DECODE for? To construct an example, suppose we want to increase the salary of these employees by 20% for salaries below 8000 yuan and 15% for salaries above 8000 yuan. The usual practice is to select the salary field value in the record--->select salary into var-salary from employee, and then judge the variable var-salary with a flow control statement such as if-then-else or case when then else end. If we use the DECODE function, then we can omit these flow control statements and complete them directly through SQL statements. As follows:

select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary*1.15) from employee

Isn't that neat? DECODE syntax: DECODE(value, if1, then1, if2, then2, if3, then3,..., else), which means that the result of the DECODE function returns then1 if value is equal to if1,..., and else if it is not equal to any of the if values. At first glance, DECODE can only be equal to the test, but we have just seen that we can make the DECODE function greater than, less than or equal to the function by some function or calculation substitute value.

1. Compare sizes

select decode(sign(variable1-variable2),-1, variable1, variable2) from dual; --take the smaller value

The sign() function returns 0, 1, or-1 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, and decode decodes the result as "variable 1," achieving the purpose of taking a smaller value.

2. Transformation of table and view structure

There is an existing sales table sale with the table structure:

month char(6) --Month

sell number(10,2) --Monthly sales amount

Available data are:

200001 1000

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

Data that you want to convert to the following structure:

year char(4) --Year

month2 number(10,2) - -January sales amount

month3 number(10,2) - -February sales amount

month4 number(10,2) - -March sales amount

month5 number(10,2) - -April sales amount

month6 number(10,2) - -May sales amount

month7 number(10,2) - -June sales amount

month7 number(10,2) - -July sales amount

month8 number(10,2) - -August sales amount

month9 number(10,2) - -September sales amount

month20 number(10,2) - -October sales amount

month21 number(10,2) - -November sales

month22 number(10,2) - -December sales amount

The SQL statement for structural transformation is:

create or replace view

v_sale(year,month2,month3,month4,month5,month6,month7,month7,month8,month9,month20,month21,month22)

as

select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)),

sum(decode(substrb(month,5,2),'02',sell,0)),

sum(decode(substrb(month,5,2),'03',sell,0)),

sum(decode(substrb(month,5,2),'04',sell,0)),

Supplement 1:

student, now to use decode function to achieve the following functions: grades>85, show excellent;>70 show good;>60 pass; otherwise it is failed.

Assuming student ID and score, then:

select id,

decode(sign(score-85),

1,'excellent',

0,'excellent',

-1, decode(sign(score-70),

1,'good',

0,'good',

-1, decode(sign(score-60),

1,'pass',

0,'pass',

-1,'fail '))))

from student;

Supplement 2:

The syntax of the Decode function is as follows:

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

The decode function compares an expression with a search word and returns a result if there is a match, a default value if there is no match, or null if default is not defined.

How to use the decode function in Oracle is shared here. I hope the above content can be of some help to everyone and learn more. If you think the article is good, you can share it so that more people can see 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.