What are the uses and differences of decode and NVL functions in Oracle?

Decode

decode (condition, value 1, translation value 1, value 2, translation value 2,..., default) The function is the same as the If...else If...else in the program.

NVL

format: NVL (string1, Replace_with)

function: If string1 is null, the NVL function returns the value of Replace_with, otherwise returns the value of string1, or null if all two parameters are null.

Note:string1 and Replace_with must be of the same data type unless you explicitly use the To_char function for type conversion .

Select NVL (SUM (T.DWXHL), 1) from Tb_jhde t means that if sum (T.DWXHL) = NULL returns 1

Oracle expands on the functionality of the NVL function, providing the NVL2 function

NVL2

The function of Nvl2 (E1, E2, E3) is: If E1 is null, the function returns E3, otherwise returns E2

Combine

Functions such as Decode and NVL are often used in conjunction, such as

Select Monthid,decode (NVL (sale,6000), 6000, ' NG ', ' OK ') from output

The sign () function returns 0, 1,-1, depending on whether a value is 0, positive, or negative.

If you take a smaller value , it's Select Monthid,decode (sale-6000), -1,sale,6000) from output, which is the purpose of taking a smaller value.

What are the uses and differences of decode and NVL functions in Oracle?