nvl syntax. NVL transformations for various data types. Laboratory work. Applying the NVL function

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 a number.

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

Laboratory work. Changing the format of output numbers

Numeric format changes in Oracle SQL, TO_CHAR function for working with numeric values.

The task:

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

Rice. 3.4 -1

At the same time, the data should be sorted in such a way that the rows for employees with the highest salary are displayed first.

Note:

Some salary values ​​in Fig. 3.4-1 have been 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 to convert string to date TO_DATE(string, format). Possible format values ​​have already been discussed above, so I will give a few 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'.

A function to convert a string to a numeric value TO_NUMBER (string, format). The most common format values ​​are listed in the table, so let's consider the use of this function using examples. Examples:

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

SELECT TO_NUMBER("500,000", "999G999") FROM DUAL will return the number 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 20th century dates in the 21st century by specifying only the last two digits of the year.

If the last two digits of the current year are 00 to 49, then the year returned 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 year returned has the same first two digits as the current year.

NVL function

The NVL function is generally the most commonly used. 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 exp2 instead.

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

In the SQL statement above, it will return n/" if the supplier_city field contains a null value. Otherwise, it will return the supplier_city value.

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

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

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

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

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

NVL transformations for various data types

To convert an undefined value to an actual value, use the NVL function: NVL ( expression1, expression2), where:

expression1- The original or computed value, which may be null.

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

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

NVL conversion for different types:

NUMBER-NVL (numeric column, 9).

CHAR or VARCHAR2 - NVL (characters|column,"Unavailable").

Laboratory work. Applying the NVL function

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

The task:

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 (column COMMISSION_PCT) 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 (values ​​shown starting from line 51)

Solution:

The corresponding request code might look like this:

SELECT first_name AS "First Name", last_name As "Last Name", NVL (COMMISSION_PCT, 0) As "Commission Rate" FROM hr.employees.

NVL function

The NVL function is generally the most commonly used. 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 a 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 with zero. Note that if a value is generated using a function, it is assigned an alias. The query results will look like:

CEIL(n) function

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

TRUNC(n[,m])

The TRUNC function returns the number n truncated to m decimal places. The m parameter may be omitted, in which 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, then the function returns 1. If negative, -1 is returned. If it is 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 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

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

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

We touched on nested functions a little earlier, now let's look at them in a little more detail. We will also look at 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 literal value when you use it as a parameter to another function call. Row functions can be nested up to any level of nesting. A single 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 topmost from left to right. The previous expression is executed as follows

1. The function F3(param1) is evaluated and the return value is used as the third parameter for function 2, let's call it param2.3
2. Then the function F2(param1, param2.2, param2.3) is evaluated and the returned value is used as the second parameter of the function F1 - param1.2
3. 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.

Consider a query

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

1. There are three functions in this query, from the bottom to the top - SYSDATE, LAST_DAY, NEXT_DAY. The request is made like this
2. The nested SYSDATE function is executed. It returns the current system time. Let's say 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, i.e. October 31, 2009.
4. Then seven days are subtracted from this date - October 24 is obtained.
5. Finally, the NEXT_DAY('24-OCT-2009', 'tue') function is evaluated and the query returns the last Tuesday in October - which in our example is 27-OCT-2009.

It is quite difficult to understand and build complex expressions using many nested function calls, but this comes with time and practice. You can break such expressions into parts and test separately. The DUAL table is very useful for testing queries and the results of function calls. You can test and debug small components, which are then combined into one large desired expression.

Branch functions

Branch functions, also known as IF-THEN-ELSE, are used to determine the path of execution depending on some circumstances. Branch functions return different results based on the result of evaluating the condition. In the group of such functions, there are functions for working with the NULL value: NVL, NVL2, NULLIF and COALESCE. And also the general functions represented by the DECODE function and the CASE expression. The DECODE function is an Oracle function, while the CASE expression is in the ANSI SQL standard.

NVL function

The NVL function checks the value of a column or expression of any data type for a null value. 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 check 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 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, query 1 will return an ORA-00909: invalid number of arguments error. Query 2 will return 1234 because NULL is being 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 the NVL function 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

The NVL2 function provides more functionality than NVL, but also handles NULL. It checks the value of a column or expression of any type for a null value. 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 to test, ifnotnull is the value to return if original is not NULL, and ifnull is the value to return 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 query 1 is a number, and the ifnull parameter is a string. Since the data types are incompatible, the error “ORA-01722: invalid number” is returned. Query two returns an ifnull parameter since original is NULL and the result is 5678. Query three uses the SUBSTR function which returns 'bc' and calls NVL2('bc','Not bc','No substring') - which returns an ifnotnull parameter – 'Not bc'.

NULLIF function

The NULLIF function tests two values ​​for the same. 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, it returns NULL, otherwise the ifunequal parameter. Consider queries

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 a date, but are compared as strings. Since the strings are of different lengths, the ifunequal 24-JUL-2009 parameter 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, 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 COALESCE(expr1, expr2, ..., exprn) where the result is expr1 if the value of expr 1 is not NULL, otherwise the result is expr2 if it is not NULL, and so on. 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 'ORA-00932: inconsistent data types' error, all non-NULL parameters must be compatible with the first non-NULL parameter. Consider 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, as it is the first non-NULL parameter. Query two returns NULL because all parameters are NULL. Query 3 evaluates the first parameter, gets NULL, and returns the second parameter because 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, while NVL2 has ifnotnull. So don't rely on the position of the parameter in the function.

DECO function

The DECODE function implements if-then-else logic by checking the first two parameters for equality and returning the third value if they are equal, or a different value if not. 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, then the value of expr1 is compared with comp2. If the values ​​are equal, then iftrue2 is returned. If not, then if there are compN, iftrueN parameter pairs, expr1 and compN are compared, and if true, iftrueN is returned if equal. If no match was found in any set of parameters, then either iffalse is returned if this parameter was specified, or NULL.

All parameters in the DECODE function can be expressions. The type of the return value is equal to the type of the first validator - the parameter comp 1. Expression expr 1 implicitly converts to comp parameter data type1. All other comp options available 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 iffalse is not defined, NULL is returned. Query two is identical to query 1 except that the iffalse value is defined. Since 1234 is not equal to 123, iffalse - 'No match' is returned. Query three checks the parameter values ​​against 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 comp3 element comparison operation (parameter position 6) and the return value of iftrue3 (parameter 7) is 'true3'. Since a match is found no more calculations are made. That is, despite the fact that the value of comp4 (parameter 8) also matches expr1, this expression is never evaluated because 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 statement sets the source element to be compared 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 syntax of an expression depends on the type. For a simple CASE expression it looks like this

CASE search_expr

WHEN comparison_expr1 THEN iftrue1

}