Nvl syntax. NVL conversions for various data types. EXP (n) and LN (n) Functions

TO_CHAR function with numbers

Functions for converting data to other data types. TO_CHAR (number) converts a number to text. TO_NUMBER (string) converts text to number.

SELECT TO_CHAR (123) FROM DUAL will return string 123, SELECT TO_NUMBER (`12345") FROM DUAL will return 12345.

Laboratory work. Changing the format of displayed numbers

Changes to the format of numeric values ​​in Oracle SQL, TO_CHAR function for working with numeric values.

Exercise:

Write a query that displays the first name, last name, and salary information for employees from the hr.employees table, in the format shown in Figure 1. 3.4-1:

Rice. 3.4 -1

In this case, the data should be sorted in such a way that the first lines are displayed for the employees with the highest salary.

Note:

Some salary values ​​in Fig. 3.4-1 have changed, so they may not match your values.

Solution:

SELECT first_name AS "First name", last_name As "Last name", TO_CHAR (SALARY, "L999999999.99") As "Salary" FROM hr.employees ORDER BY SALARY DESC.

TO_NUMBER and TO_DATE Functions

Function for converting a string to a date TO_DATE (string, format). Possible format values ​​have already been discussed above, so I will give several examples of using this function. Examples:

SELECT TO_DATE ("01.01.2010", `DD.MM.YYYY") FROM DUAL will return the date `01.01.2010";

SELECT TO_DATE ("01.JAN.2010", `DD.MON.YYYY") FROM DUAL will return the date `01.01.2009";

SELECT TO_DATE ("15-01-10", `DD-MM-YY") FROM DUAL will return the date `01/15/2010".

Function for converting a string to a numeric value TO_NUMBER (string, format). The most common format values ​​are listed in the table, so let's look at the use of this function by examples. Examples:

SELECT TO_NUMBER (`100") FROM DUAL will return the number 100 SELECT TO_NUMBER (`0010.01", "9999D99") FROM DUAL will return 10.01;

SELECT TO_NUMBER ("500,000", "999G999") FROM DUAL will return 500000.

RR element in date format

The RR date and time format element is similar to the YY date and time format element, but it provides additional flexibility for storing date values ​​in other centuries. The RR datetime format element allows you to store dates from the 20th century to the 21st century by specifying only the last two digits of the year.

If the last two digits of the current year are 00 through 49, then the returned year has the same first two digits as the current year.

If the last two digits of the current year are between 50 and 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the last two digits of the current year are 00 through 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If the last two digits of the current year are between 50 and 99, then the returned year has the same first two digits as the current year.

NVL function

The NVL function is usually the most commonly used. The function takes two parameters: NVL (expr1, exp2). If the first parameter of 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 exp2 instead.

Example: Select NVL (supplier_city, n / a ") from suppliers:

The SQL statement above will return n / "if the supplier_city is null, otherwise it will return supplier_city.

Another example of using the NVL function in Oracle / PLSQL is:

select supplier_id, NVL (supplier_desc, supplier_name) from suppliers.

it SQL statement will return supplier_name field if supplier_desc contains a null value. Otherwise it will return supplier_desc.

A final example: using the NVL function in Oracle / PLSQL is: select NVL (commission, 0) from sales;

This SQL statement returned 0 if commission the field contains a null value. Otherwise, it would return commission field.

NVL conversions for different data types

The NVL function is used to convert an undefined value to an actual value: NVL ( expression1, expression 2), where:

expression1- The original or calculated value, which may be undefined.

expression2- The value that is substituted for the undefined value.

Note: The NVL function can be used to convert any data type, but the result will always be the same type as expression1.

Convert NVL for different types:

NUMBER - NVL (numeric column, 9).

CHAR or VARCHAR2 - NVL (symbols | column,"Not available").

Laboratory work. Applying the NVL function

NVL function for working with null values ​​in Oracle SQL.

Exercise:

Write a query that displays information about the first and last names of employees from the hr.employees. Table, as well as the commission rate (COMMISSION_PCT column) for the employee. In this case, for those employees for whom the commission is not defined, you need to display the value 0. The result of the query should be the same as shown in fig. 3.5-1.

Rice. 3.5 -1 (showing values ​​starting at line 51)

Solution:

The corresponding request code can be like this:

SELECT first_name AS “First name”, last_name As “Last name”, NVL (COMMISSION_PCT, 0) As “Commission rate” FROM hr.employees.

We touched on nested functions a little earlier, now we will consider them in more detail. We will also look at functions for working with NULL values ​​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 literal value when you use it as a parameter to a call to another function. Inline 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 may 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 inputs to other functions. Functions are evaluated from the deepest nesting level to the highest, from left to right. The previous expression is executed as follows

1. Function F3 (param1) is calculated and the return value is used as the third parameter for function 2, let's call it param2.
2. Then the function F2 (param1, param2.2, param2.3) is calculated and the returned value is used as the second parameter of the F1 function - param1.2
3. Finally, function F1 (param1, param2, param1.3) is evaluated and returned to the calling program.

Thus, the function F3 is at the third nesting level.

Consider the request

select next_day (last_day (sysdate) -7, ‘tue’) from dual;

1. There are three functions in this request, from bottom to top - SYSDATE, LAST_DAY, NEXT_DAY. The request is executed as follows
2. The most nested SYSDATE function is executed. It returns the current system time. Suppose the current date is October 28, 2009
3. 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.
4. Then seven days are subtracted from this date - it turns out October 24.
5. Finally, the function NEXT_DAY ('24 -OCT-2009 ',' tue ') is evaluated, and the query returns the last Tuesday in October - which is 27-OCT-2009 in our example.

It's hard enough to understand and build complex expressions using many nested function calls, but this comes with time and practice. You can split such expressions into parts and test them separately. The DUAL table is very useful for testing queries and function calls. You can test and debug small components, which can then be combined into one large, desired expression.

Branching functions

Branching functions, also known as IF-THEN-ELSE, are used to determine the execution path based on some circumstance. Branching functions return different results based on the result of evaluating a condition. In the group of such functions, the functions for working with the NULL value are distinguished: 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 for NULL. If the value is NULL, it returns an alternative non-NULL default value; otherwise, the original value is returned.

The NVL function has two required parameters and the syntax is NVL (original, ifnull) where original is the original value to be checked and ifnull is the result returned by the function if original 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. Consider three requests

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, query 1 will return an ORA-00909: invalid number of arguments error. Query 2 will return 1234 since NULL is checked and it is NULL. Query three uses a nested SUBSTR function that tries to extract the fourth character from a three-character string, returns NULL, and NVL returns 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 on numbers do not return NULL.

NVL function2

NVL2 provides more functionality than NVL, but it also handles NULL values. It tests the value of a column or expression of any type for 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 NVL2 (original, ifnotnull, ifnull), where original is the value to be checked, 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 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 some 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 query 1 is a number, and the ifnull parameter is a string. Since the data types are not compatible, the error “ORA-01722: invalid number” is returned. Request two returns the ifnull parameter, since original is NULL and the result is 5678. Request 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 tests two values ​​for identity. If they are the same, NULL is returned otherwise the first parameter is returned. The NULLIF function has two required parameters and the NULLIF syntax (ifunequal, comparison_item). The function compares two parameters and if they are identical - NULL is returned, otherwise the ifunequal parameter. Consider requests

Query 1: select nullif (1234, 1234) from dual;

Query one returns NULL because the parameters are identical. The strings in query 2 are not converted to date, but compared as strings. Since strings are of different lengths, ifunequal 24-JUL-2009 is returned.

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 the expression in the ifunequal parameter. The EMAIL column is compared to this expression, which returns the first letter of the first name concatenated with the last name for employees whose first name is 4 characters long. When these values ​​are equal, NULLIF will return NULL, and the value 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 items 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 as many optional parameters as you like, and the COALESCE syntax (expr1, expr2, ..., exprn) where the result is expr1 if expr 1 is not NULL, otherwise expr2 is returned if it is not NULL, and so on. COALESCE equals nested NVL functions

COALESCE (expr1, expr2) = NVL (expr1, expr2)

COALESCE (expr1, expr2, expr3) = NVL (expr1, NVL (expr2, expr3))

The data type of the return 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 this is the first non-NULL parameter. Query two returns NULL because all parameters are NULL. Query 3 evaluates the first parameter, gets a NULL value, and returns the second parameter, since it is the first non-NULL parameter.

The parameters of the NVL2 function 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 is due to the fact that the second parameter of the NVL function is ifnull, while in NVL2 it 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 checking the first two parameters for equality and returning a third value if they are equal, or another value if they are not equal. The DECODE function has three required parameters and the DECODE syntax (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, what happens next depends on whether the parameters comp2 and iftrue2 are specified. If given, the value of expr1 is compared to 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 iftrueN is returned if equal. If no match was found in any of the parameter sets, then either iffalse is returned if this parameter was specified, or NULL.

All parameters in the DECODE function can be expressions. The return type is the same as the type of the first validating element - the parameter comp 1. Expression expr 1 is implicitly convertible to the data type of the parameter comp1. All other available comp parameters 1 ... compN also implicitly convertible to 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 some 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 no iffalse value is defined, NULL is returned. Request two is identical to request 1 except that iffalse is specified. Since 1234 is not equal to 123 then iffalse - ‘No match’ is returned. Request three checks the parameter values ​​to see if they match the search value. The parameters comp1 and comp2 are not equal 'search' so the results of iftrue1 and iftrue2 are skipped. The match is found in the third comparison of comp3 (parameter position 6) and the return value iftrue3 (parameter 7) is ‘true3’. Since a match was found, no further calculations are performed. That is, despite the fact that the value of comp4 (parameter 8) also matches expr1, this expression is never calculated since a 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 the source to compare once, and then lists all the necessary test conditions. The searched CASE evaluates both statements for each condition.

The CASE expression has three required parameters. The expression syntax is type dependent. For a simple CASE expression, it looks like this

CASE search_expr

WHEN comparison_expr1 THEN iftrue1

)

TRUNC returns the number n, truncated to m decimal places. The m parameter can be omitted - 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

SIGN (n) function

The SIGN function determines the sign of a number. If n is positive, the function returns 1. If n is negative, -1 is returned. 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 pass m equal to zero - in this case, there is no division by 0 error.

POWER (n, m) function

The POWER function raises n to the m power. The degree can be fractional or 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

In some cases, when calling this function, an exception may be thrown. For example:

SELECT POWER (-100, 1/2) X2

FROM DUAL

V in this case an attempt is made to calculate the square root of a negative number, which will result in the ORA-01428 "Argument out of range" error.

SQRT (n) function

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 of n, and the LN function calculates the natural logarithm of n (where n must be greater than zero). Example:

SELECT EXP (2) X1, LN (1) X2, LN (EXP (2)) X3