Decode,sign, and NVL in Oracle

1. Common usage of decode:

Decode (condition, value1,result1[, value2, Result2],default_result)

Decode is actually equivalent to an IF condition function condition equivalent to an input value, and value I is the parameter list, if the input value is value1, then the corresponding return result is RESULT1.

The function also has a default return value of Default_result if it fails to match any one of the actual argument order pairs successfully.

Decode is different from other SQL functions, the DECODE function can recognize and manipulate null values.

The following example shows how to read an item under the Blood_test_flag column in the User Checkup table Seapark as an argument support value for the Decode function.

SELECT Checkup_type,

DECODE (Blood_test_flag, ' Y ', ' Yes ', ' N ', ' No ', NULL, ' None ', ' Invalid ')

From Checkup;

decode can also be used in nested ways to make multiple choices:

For example, an employee's attendance: There may be four states: Gongchu, leave, attendance, rest. Where ' Gongchu ' is included in the ' Leave ' record, "attendance" and "rest" are two states of a field.

such as: Decode (business_out, y, "Gongchu", decode (leave, Y, "Leave", Decode (Attend,y, "attendance", N, "Rest")));

2. Sign common usage:

function Syntax:

Sign (n)

Function Description:

Take the sign of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0

Example:

First, select sign (0), sign (-+), sign (dual);

Sign ( -100) sign (0)

———- ———- ———-

1-1 0

Second, a=10,b=20

Sign (A-B) returns-1

3. NVL Effect

? 1.NVL function

The format of the NVL function is as follows: NVL (EXPR1,EXPR2)

The meaning is: if the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the first parameter is displayed.

For example:

Sql> Select ENAME,NVL (Comm,-1) from EMP;

Ename NVL (comm,-1)

------- ----

SMITH-1

ALLEN 300

WARD 500

JONES-1

MARTIN 1400

BLAKE-1

FORD-1

MILLER-1

Which shows that 1 of the original values are all null values

Decode,sign, and NVL in Oracle