Decode, sign, and trunc functions of Oracle databases

Oracle DatabaseThere are many functions. This article mainly introducesDecode,Sign,TruncNext, let's take a look at this part of the function.

I. decode

In Oracle/PLSQL, decode has the same features as IF-THEN-ELSE.

The decode function syntax is as follows:

Decode (expression, search, result [, search, result]... [, default]);

Expression to be compared.

The field to be compared with expression in search ..

Result: If expression is the same as search, this result is returned ..

Default: this parameter is optional. If no search matches expression, this result is returned. If this parameter is not set, null is returned if no search matches expression.

Search and result can be matched multiple times.

Applies to Oracle 9i, Oracle 10g, and Oracle 11g.

For example:

 
 

  
  
You could use the decode function in an SQL statement as follows:  
  
  
 
  
  
select supplier_name,decode(supplier_id,1000,'IBM',10001,'Microsoft','1002','Hewlett Packard','Gateway') result from suppliers; 
 
 

The preceding SQL statement is equivalent to the following IF-THEN-ELSE:

 
 

  
  
IF supplier_id = 10000 THEN  
  
  
 
  
  
result := 'IBM';  
  
  
 
  
  
ELSIF supplier_id = 10001 THEN  
  
  
 
  
  
result := 'Microsoft';  
  
  
 
  
  
ELSIF supplier_id = 10002 THEN  
  
  
 
  
  
result := 'Hewlett Packard';  
  
  
 
  
  
ELSE  
  
  
 
  
  
result := 'Gateway';  
  
  
 
  
  
END IF; 
 
 

The decode function will match the value of supplier_id one by one.

FAQs:

Question 1:Now, a reader wants to ask how to use the decode function to compare two dates? For example, date1 and date2). If date1> date2, the decode function returns date2. otherwise, the decode function returns date1.

Answer:To meet the preceding requirements, use the decode function as follows:

Decode (date1-date2)-abs (date1-date2), 0, date2, date1)

If date1 is greater than date2, the following expression is equal to 0:

(Date1-date2)-abs (date1-date2)

Helpful: bind the SIGN function to the decode function, as shown in the following code:

The statement for comparing dates can be modified as follows:

DECODE (SIGN (date1-date2), 1, date2, date1)

The combination of SIGN and DECODE is very useful for comparing sales dividends and other figures.

DECODE (SIGN (actual-target),-1, 'no Bonus for you', 0, 'just made it', 1, 'congrats, you are a winner ').

Question 2:I want to know whether the decode function can be used to determine the number range, for example, 1-10 = 'category 1 ', 11-20 = 'category 2', which is better than one by one.

Answer:Unfortunately, we cannot use the decode function to determine the number range. however, you can try to create an expression that can get a range specified by a number, the next number corresponds to the next specified range, and so on.

For example:

Select supplier_id, decode (trunc (supplier_id-1)/10), 0, 'category 1', 1, 'category 2', 2, 'category 3', 'unknown ') result from suppliers;

This example is based on this formula: trunc (supplier_id-1)/10

If supplier_id is between 1 and 10, the expression is calculated as 0.

If supplier_id is between 11 and 20, the expression is calculated as 1.

Ii. sign

In Oracle/PLSQL, the sign function returns a positive and negative sign of a number.

Syntax: sign (number)

Number of the flag to be tested.

If number <0, then sign returns-1.

If number = 0, then sign returns 0.

If number> 0, then sign returns 1.

Applicable to: Oracle 8i, Oracle 9i, Oracle 10g, and Oracle 11g.

For example:

 
 

  
  
sign(-23) would return -1 sign(0.001) would return -1 sign(0) would return 0 sign(0.001)   
  
  
 
  
  
would return 1 sign(23) would return 1 sig(23.601)  
  
  
 
  
  
would return 1 
 
 

3. trunc (number)

In Oracle/PLSQL, trunc function returns a number truncated to a certain number of decimal places.

The trunc function syntax is as follows:

Trunc (number, [decimal_places])

Number.

Decimal_places: the number of decimal places to retain. This parameter must be an integer. If this parameter is set to default, it will retain 0 decimal places by default.

Application: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

Trunc (125.815) wowould return 125 trunc (125.815, 0) wowould return 125 trunc (125.815, 1) wowould return 125.8 trunc (125.815, 2) wowould return 125.81 trunc (125.81, 3) wowould return 125.81 trunc (-125.815, 2) wowould return-125.81 trunc (125.815,-1) wowould return 120 trunc (125.815,-2) wowould return 100 trunc (125.81, -3) wocould return 0

If decimal_places is greater than the decimal point of the number, the original number is not 0.

For example, trunc (125.81, 3) wowould return 125.81;

If decimal_places is negative

If supplier_id is between 21 and 30, the expression is calculated as 3.

Here is an introduction to the use of the decode, sign, and trunc functions of the Oracle database. I hope this introduction will be helpful to you!