Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to use decode function in oracle

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Today, I will talk to you about how to use the decode function in oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

First, the DECODE function is one of the powerful functions of ORACLE PL/SQL. At present, only SQL of ORACLE company provides this function, and other database vendors' SQL implementations do not have this function. What is the use of DECODE? First, construct an example. If we want to increase the salary of Zhixing staff, the standard is: 20% for those with a salary below 8000 yuan, and 15% for those with a salary above 8000 yuan. The usual practice is. First select the salary field value in the record? Select salary into var-salary from employee, and then infer the variable var-salary using flow control statements such as if-then-else or choose case. Suppose we use the DECODE function, then we can omit these flow control statements. It can be done directly through the SQL statement. For example, the following: select decode (sign (salary-8000), 1meme salaryhorse 1.15memoretype 1.2memoir from employee is very succinct?

The grammar of DECODE: DECODE (value,if1,then1,if2,then2,if3,then3,...,else). When it is assumed that value is equal to if1, the result of the DECODE function returns the then1,..., assumption that is not equal to an if value regardless of what. Then else is returned. At first glance. DECODE can only do the same as testing, but as you just saw, we replaced value with some functions or calculations. Is the ability to make DECODE functions greater than, less than, or equal to.

3. The meaning of this function is as follows:

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

4. The meaning of this function is as follows:

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

5. Compare the size of the dog.

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

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

For example:

Variable 1-10, variable 2-20

Then sign (variable 1-variable 2) returns-1. The decode decoding result is "variable 1". The purpose of taking a smaller value is achieved.

VI. Transformation of table and view structure

There is a sales table sale. The structure of the table is:

Month char (6)-month

Sell number (10Pol 2)-monthly sales amount

The 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 (10Pol 2)-January sales amount

Month3 number (10Pol 2)-February sales amount

Month4 number (10Pol 2)-March sales amount

Month5 number (10Pol 2)-April sales amount

Month6 number (10Pol 2)-May sales amount

Month7 number (10Pol 2)-June sales amount

Month7 number (10Pol 2)-July sales amount

Month8 number (10J2)-August sales amount

Month9 number (10Pol 2)-September sales amount

Month20 number (10Pol 2)-October sales amount

Month21 number (10Pol 2)-November sales amount

Month22 number (10Pol 2)-December sales amount

7. The SQL statements for structural transformation are:

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), '01century recording SellMere 0))

Sum (decode (substrb (month,5,2),'02))

Sum (decode (substrb (month,5,2),'03))

Sum (decode (substrb (month,5,2), '04mom, Sell, 0))

VIII. Supplement 1:

There is a student score sheet student, now we need to use the decode function to achieve the following functions: score > 85, show excellent; > 70 show good; > 60 pass; otherwise it is failed.

If the number of the student is id and the score is score, then:

Select id, decode (sign (score-85), 1 Magi 'excellent', 0 Magi 'excellent',-1

Decode (sign (score-70), 1, dint 'good', 0, 'good',-1

Decode (sign (score-60), 1 dint 'pass', 0 dint 'pass',-1 dint 'fail')

From student .

Supplement 2:

The syntax structure 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 the expression with the search word, assumes a match, and returns a result; it assumes a mismatch. Returns the default value; assuming no default value is defined, returns a null value.

Here is a simple test to illustrate the use of the Decode function:

SQL > create table t as select username,default_tablespace,lock_date from dba_users

Table created.

SQL > select * from t

USERNAME DEFAULT_TABLESPACE LOCK_DATE

-

SYS SYSTEM

SYSTEM SYSTEM

OUTLN SYSTEM

CSMIG SYSTEM

SCOTT SYSTEM

EYGLE USERS

DBSNMP SYSTEM

WMSYS SYSTEM 20-OCT-04

8 rows selected.

SQL > select username,decode (lock_date,null,'unlocked','locked') status from t

USERNAME STATUS

SYS unlocked

SYSTEM unlocked

OUTLN unlocked

CSMIG unlocked

SCOTT unlocked

EYGLE unlocked

DBSNMP unlocked

WMSYS locked

8 rows selected.

SQL > select username,decode (lock_date,null,'unlocked') status from t

USERNAME STATUS

SYS unlocked

SYSTEM unlocked

OUTLN unlocked

CSMIG unlocked

SCOTT unlocked

EYGLE unlocked

DBSNMP unlocked

WMSYS

8 rows selected.

After reading the above, do you have any further understanding of how to use the decode function in oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.

Views: 261

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report