In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.