We touched on nested functions a little earlier, now we’ll look at them in a little more detail. We will also consider functions for working with the NULL value and functions that help implement the branching operation in a query.
Nested functions
Nested functions use the return value of one function as an input parameter to another function. Functions always return only one value. Therefore, you can treat the result of a function call as a value literal when you use it as a parameter to another function call. String functions can be nested to any nesting level. One function call looks like this
Function1(parameter1, parameter2, …) = result
Replacing a function parameter with a call to another function can result in expressions like
F1(param1.1, F2(param2.1, param2.2, F3(param3.1)), param1.3)
Nested functions are evaluated first before their results are used as input values for other functions. Functions are evaluated from the deepest nesting level to the top one from left to right. The previous expression is executed as follows
- Function F3(param1) is evaluated and the return value is used as the third parameter for function 2, let's call it param2.3
- Then the function F2(param1, param2.2, param2.3) is evaluated and the return value is used as the second parameter of the function F1 - param1.2
- Finally, the function F1(param1, param2, param1.3) is evaluated and the result is returned to the calling program.
Thus, the F3 function is at the third nesting level.
Let's consider the request
select next_day(last_day(sysdate)-7, ‘tue’) from dual;
- There are three functions in this query, from lower level to upper level - SYSDATE, LAST_DAY, NEXT_DAY. The request is performed as follows
- The nested SYSDATE function is executed. It returns the current system time. Let's say the current date is October 28, 2009
- Next, the result of the second level function LAST_DAY is calculated. LAST_DATE('28-OCT-2009') returns the last day of October 2009, which is October 31, 2009.
- Then seven days are subtracted from this date - it turns out October 24.
- Finally, the function NEXT_DAY('24-OCT-2009', 'tue') is evaluated, and the query returns the last Tuesday in October - which in our example is 27-OCT-2009.
Quite difficult to understand and build complex expressions using a lot of nested function calls, but this comes with time and practice. You can break such expressions into parts and test them separately. The DUAL table is very useful for testing queries and function call results. You can test and debug small components, which are then combined into one large desired expression.
Branching functions
Branch functions, also known as IF-THEN-ELSE, are used to determine the path of execution depending on some circumstances. Branching functions return different results based on the result of the condition evaluation. The group of such functions includes functions for working with the NULL value: NVL, NVL2, NULLIF and COALESCE. And also the common functions represented by the DECODE function and the CASE expression. The DECODE function is an Oracle function, while the CASE expression is present in the ANSI SQL standard.
NVL function
The NVL function tests the value of a column or expression of any data type against NULL. If the value is NULL, it returns an alternative non-NULL default value, otherwise it returns the original value.
The NVL function has two required parameters and the syntax is NVL(original, ifnull) where original is the original value to check and ifnull is the result returned by the function if the original value is NULL. The data type of the ifnull and original parameters must be compatible. That is, either the data type must be the same or it must be possible to implicitly convert values from one type to another. The NVL function returns a value of the same data type as the data type of the original parameter. Let's consider three queries
Query 1: select nvl(1234) from dual;
Query 2: select nvl(null, 1234) from dual;
Query 3: select nvl(substr(‘abc’, 4), ‘No substring exists’) from dual;
Since the NVL function requires two parameters, request 1 will return error ORA-00909: invalid number of arguments. Query 2 will return 1234 because the NULL value is checked and it is NULL. Query three uses a nested SUBSTR function that attempts to extract the fourth character from a string three characters long, returning NULL, and the NVL function returning the string 'No sbustring exists'.
The NVL function is very useful when working with numbers. It is used to convert NULL values to 0 so that arithmetic operations NULL was not returned over numbers
NVL function2
The NVL2 function provides more functionality than NVL, but also serves to handle NULL values. It tests the value of a column or expression of any type against NULL. If the value is not NULL, then the second parameter is returned, otherwise the third parameter is returned, unlike the NVL function, which in this case returns the original value.
The NVL2 function has three required parameters and the syntax is NVL2(original, ifnotnull, ifnull), where original is the value being tested, ifnotnull is the value returned if original is not NULL, and ifnull is the value returned if original is NULL. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. The data type returned by the NVL2 function is equal to the data type of the ifnotnull parameter. Let's look at a few examples
Query 1: select nvl2(1234, 1, ‘a string’) from dual;
Query 2: select nvl2(null, 1234, 5678) from dual;
Query 3: select nvl2(substr('abc', 2), 'Not bc', 'No substring') from dual;
The ifnotnull parameter in request 1 is a number, and the ifnull parameter is a string. Because the data types are incompatible, the error “ORA-01722: invalid number” is returned. Query two returns the ifnull parameter, since original is NULL and the result will be 5678. Query three uses the SUBSTR function which returns 'bc' and calls NVL2('bc','Not bc','No substring') - which returns the ifnotnull parameter – 'Not bc'.
NULLIF function
The NULLIF function checks whether two values are identical. If they are the same, NULL is returned, otherwise the first parameter is returned. The NULLIF function has two required parameters and the syntax is NULLIF(ifunequal, comparison_item). The function compares two parameters and if they are identical, NULL is returned, otherwise the parameter is ifunequal. Let's consider the requests
Query 1: select nullif(1234, 1234) from dual;
Query one returns NULL since the parameters are identical. The strings in Query 2 are not converted to a date, but compared as strings. Since the strings are of different lengths, the ifunequal parameter is returned 24-JUL-2009.
In Figure 10-4, the NULLIF function is nested within the NVL2 function. The NULLIF function in turn uses the SUBSTR and UPPER functions as part of an expression in the ifunequal parameter. The EMAIL column is compared to this expression, which returns the first letter of the first name combined with the last name for employees whose first name is 4 characters long. When these values are equal, NULLIF will return NULL, otherwise it will return the value of the ifunequal parameter. These values are used as a parameter for the NVL2 function. NVL2 in turn returns a description of whether the compared elements matched or not.
Figure 10-4 – Using the NULLIF function
COALESCE function
The COALESCE function returns the first non-NULL value from the parameter list. If all parameters are NULL, then NULL is returned. The COALESCE function has two required parameters and any number of optional parameters and the syntax is COALESCE(expr1, expr2, ..., exprn) where the result will be expr1 if the value of expr 1 is not NULL, otherwise the result will be expr2 if it is not NULL, etc. COALESCE is equal in meaning to nested NVL functions
COALESCE(expr1, expr2) = NVL(expr1, expr2)
COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))
The data type of the returned value if a non-NULL value is found is equal to the data type of the first non-NULL value. To avoid the error 'ORA-00932: inconsistent data types', all non-NULL parameters must be compatible with the first non-NULL parameter. Let's look at three examples
Query 1: select coalesce(null, null, null, ‘a string’) from dual;
Query 2: select coalesce(null, null, null) from dual;
Query 3: select coalesce(substr('abc', 4), 'Not bc', 'No substring') from dual;
Query 1 returns the fourth parameter: a string, since it is the first non-NULL parameter. Query two returns NULL because all parameters are NULL. Query 3 evaluates the first parameter, gets the NULL value, and returns the second parameter since it is the first non-NULL parameter.
The NVL2 function parameters can be confusing if you are already familiar with the NVL function. NVL(original, ifnull) returns original if the value is not NULL, otherwise ifnull. NVL2(original, ifnotnull, ifnull) returns ifnotnull if original is not NULL otherwise ifnull. The confusion comes from the fact that the second parameter of the NVL function is ifnull, whereas the NVL2 function is ifnotnull. So don't rely on the position of the parameter in the function.
DECODE function
The DECODE function implements if-then-else logic by testing the first two parameters for equality and returning a third value if they are equal or a different value if they are not equal. The DECODE function has three required parameters and the syntax is DECODE(expr1, comp1, iftrue1, , ). These parameters are used as shown in the following pseudocode example
IF expr1=comp1 then return iftrue1
Else if expr1=comp2 then return iftrue2
Else if exprN=compN then return iftrueN
Else return NULL|iffalse;
First, expr1 is compared with comp1. If they are equal, iftrue1 is returned. If expr1 is not equal to comp1, then what happens next depends on whether the parameters comp2 and iftrue2 are specified. If given, the value of expr1 is compared with comp2. If the values are equal, then iftrue2 is returned. If not, then if there are pairs of parameters compN, iftrueN, expr1 and compN are compared and, if equal, iftrueN is returned. If no match was found in any set of parameters, then either iffalse if this parameter was specified, or NULL is returned.
All parameters in the DECODE function can be expressions. The type of the return value is equal to the type of the first validating element - the parameter comp 1. Expression expr 1 is implicitly converted to the data type of the comp parameter1. All other available comp parameters 1...compN are also implicitly converted to type comp 1. DECODE treats a NULL value as equal to another NULL value, i.e. if expr1 is NULL and comp3 is NULL and comp2 is not NULL, then iftrue3 is returned. Let's look at a few examples
Query 1: select decode(1234, 123, ‘123 is a match’) from dual;
Query 2: select decode(1234, 123, ‘123 is a match’, ‘No match’) from dual;
Query 3: select decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' false') from dual;
Query one compares the value 1234 and 123. Since they are not equal, iftrue1 is ignored and since the value iffalse is not defined, NULL is returned. Request two is identical to request 1 except that the value iffalse is defined. Since 1234 is not equal to 123, it returns iffalse – ‘No match’. Query three checks the parameter values to match the search value. The parameters comp1 and comp2 are not equal to 'search' so the results of iftrue1 and iftrue2 are skipped. A match is found in the third comparison operation of element comp3 (parameter position 6) and the value of iftrue3 (parameter 7) is returned which is equal to 'true3'. Since a match is found, no more calculations are performed. That is, despite the fact that the value of comp4 (parameter 8) also matches expr1, this expression is never calculated since the match was found in the previous comparison.
CASE expression
All third and fourth generation programming languages implement the case construct. Like the DECODE function, the CASE expression allows you to implement if-then-else logic. There are two options for using the CASE expression. A simple CASE expression sets up the source element to compare once and then lists all the necessary test conditions. Complex (searched) CASE evaluates both statements for each condition.
The CASE expression has three required parameters. The expression syntax depends on the type. For a simple CASE expression it looks like this
CASE search_expr
WHEN comparison_expr1 THEN iftrue1
)
The TRUNC function returns the number n, truncated to m decimal places. The m parameter may not be specified; in this case, n is truncated to an integer.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
FROM DUAL
Function SIGN(n)
The SIGN function determines the sign of a number. If n is positive, then the function returns 1. If n is negative, it returns -1. If equal to zero, then 0 is returned. For example:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
An interesting feature of this function is the ability to transmit m equal to zero without causing a division by 0 error.
Function POWER(n, m)
The POWER function raises the number n to the power m. The degree can be fractional and negative, which significantly expands the capabilities of this function.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
FROM DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
In some cases, an exception may occur when calling this function. For example:
SELECT POWER(-100, 1/2) X2
FROM DUAL
In this case, an attempt is made to calculate the square root of a negative number, which will result in an ORA-01428 "Argument out of range" error.
Function SQRT(n)
This function returns the square root of n. For example:
SELECT SQRT(100) X
FROM DUAL
EXP(n) and LN(n) functions
The EXP function raises e to the power n, and the LN function calculates natural logarithm from n (n must be greater than zero). Example:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
NVL function
The NVL function is generally used most often. The function receives two parameters: NVL(expr1, expr2). If the first parameter expr1 is not NULL, then the function returns its value. If the first parameter is NULL, then the function returns the value of the second parameter expr2 instead.
Let's consider practical example. The COMM field in the EMP table can contain NULL values. When executing a query like:
SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
FROM SCOTT.EMP
the NULL value will be replaced by zero. Note that when a value is generated using a function, it is assigned an alias. The query results will look like:
| EMPNO | ENAME | COMM | NVL_COMM |
| 7369 | SMITH | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | WARD | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTIN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | KING | 0 | |
| 7844 | TURNER | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | FORD | 0 | |
| 7934 | MILLER | 0 |
Function CEIL(n)
The CEIL function returns the smallest integer greater than or equal to the number n passed as a parameter. For example:
SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
FROM DUAL
Function TRUNC(n [,m])
The TRUNC function returns the number n, truncated to m decimal places. The m parameter may not be specified; in this case, n is truncated to an integer.
SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,
TRUNC(100.25678, 2) X4
FROM DUAL
Function SIGN(n)
The SIGN function determines the sign of a number. If n is positive, then the function returns 1. If n is negative, it returns -1. If equal to zero, then 0 is returned. For example:
SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3
FROM DUAL
An interesting feature of this function is the ability to transmit m equal to zero without causing a division by 0 error.
Function POWER(n, m)
The POWER function raises the number n to the power m. The degree can be fractional and negative, which significantly expands the capabilities of this function.
SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,
POWER(1000, 1/3) X3, POWER(1000, -1/3) X4
FROM DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
In some cases, an exception may occur when calling this function. For example:
SELECT POWER(-100, 1/2) X2
FROM DUAL
In this case, an attempt is made to calculate the square root of a negative number, which will result in an ORA-01428 "Argument out of range" error.
Function SQRT(n)
This function returns the square root of the number n. For example:
SELECT SQRT(100) X
FROM DUAL
EXP(n) and LN(n) functions
The EXP function raises e to the power n, and the LN function calculates the natural logarithm of n (n must be greater than zero). Example:
SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Loading...
