Branch operators in the SELECT command. Branch operators in the SELECT command NVL conversion for various data types

Function to_char with numbers

Data conversion functions to other data types. To_char (number) converts a number into text. To_number converts text to the number.

Select to_char (123) From Dual will return the line 123, Select to_number (`12345") from Dual will return the number 12345.

Laboratory work. Changing the format of the output numbers

Changes in the format of numerical values \u200b\u200bin Oracle SQL., T_Char function to work with numeric values.

The task:

Write a request that would display information about the name, surname and salary of employees from the HR.Employees table in the format shown in Fig. 3.4-1:

Fig. 3.4. -1

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

Note:

Some wages in fig. 3.4-1 were changed, so they may not coincide with your values.

Decision:

SELECT FIRST_NAME AS "Name", Last_Name AS "Surname", to_char (Salary, "L999999999.99") AS "Salad" from Hr.Employees Order by Salary Desc.

Functions to_number and to_date

Row Conversion Function at the T_DATE date (string, format). Possible formats are already considered above, so I will give some 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."

Row conversion function to the numeric value to_number (string, format). The most common formats are listed in the table, so consider the use of this function on the 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 dates format

The element of the Date and Time RR format is similar to the YY date and time format element, but it provides additional flexibility to store the date values \u200b\u200band in other centuries. An element of the RR dates and time format allows you to store the dates of the 20th century in the 21st century, indicating only the two last digits of the year.

If the two last few figures of the current year are 00 to 49, the return year has the same first two digits as in the current year.

If the two last figures of the current year from 50 to 99, the first 2 digits of the returned year are 1 more than in the first 2 digits of the current year.

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

If the two last figures of the current year from 50 to 99, then the returned year has the same first two digits as in the current year.

NVL feature

NVL featureAs a rule, it is most often applied. The function receives two parameters: NVL (EXPR1, EXERG2). If the first EXPR1 parameter is not equal to NULL, then the function returns its value. If the first NULL parameter, then the function returns the value of the second parameter of the EXERG2.

Example: Select NVL (Supplier_city, N / A ") from suppliers:

The SQL statement will return N / "if the SUPPLIER_CITY field contains a zero value. Otherwise, it will return the value supplier_city.

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

select Supplier_ID, NVL (Supplier_Desc, Supplier_name) from suppliers.

This SQL application will return supplier_name. Field, if supplier_Desc. contains zero value. Otherwise he will return supplier_Desc..

Last example: Using the NVL function in Oracle / Plsql is: Select NVL (Commission, 0) from sales;

This SQL statement returned to 0 if commission The field contains zero value. Otherwise, it would be possible to return commission field.

NVL Conversion for Different Data Types

To convert an indefinite value to the actual function, the NVL function is used: NVL ( expression1, expression 2), where:

expression1- Source or calculated value that can be uncertain.

expression2. - A value that is substituted instead of an indefinite value.

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

NVL transformation for different types:

Number - NVL. (numerical column, 9).

Char or Varchar2 - NVL (Symbols | column, "Not available").

Laboratory work. Application of the NVL function

NVL feature to work with uncertain values \u200b\u200bin Oracle SQL.

The task:

Write a request that displays information about the name and surname of staff from the HR.Employees table., As well as the bid of the Commission (Column Commission_PCT) for the employee. At the same time, for those employees for whom the Commission is not defined, it is necessary to display a value of 0. The result of the query must be as presented in Fig. 3.5-1.

Fig. 3.5 -1 (Values \u200b\u200bstart from line 51)

Decision:

The code of the corresponding request may be:

SELECT FIRST_NAME AS "Name", Last_Name As "Surname", NVL (Commission_PCT, 0) AS "Commission Rate" from Hr.Employees.

NVL feature

The NVL function is usually applied most often. The function receives two parameters: NVL (EXPR1, EXPR2). If the first EXPR1 parameter is not equal to NULL, then the function returns its value. If the first NULL parameter, then the function returns the value of the second EXPR2 parameter.

Consider a practical example. The COMM field in the EMP table may contain NULL values. When performing a query of the form:

SELECT EMPNO, ENAME, COMM, NVL (COMM, 0) NVL_COMM

From scott.emp

nULL value will be replaced by zero. Please note that in the case of the formation of the value with the help of the function, it is assigned to a pseudonym. Request results will be:

CEIL (N)

The CEIL function returns the smallest integer, greater or equal to the number of N. For example:

SELECT CEIL (100) X1, CEIL (-100) X2, CEIL (100.2) x3, Ceil (-100.2) x4

From dual.

TRUNC function (N [, M])

The trunc function returns the number n truncated to m signs after the decimal point. The m parameter may not be indicated - in this case, N truncated to the whole.

SELECT TRUNC (100.25678) x1, trunc (-100.25678) x2, trunc (100.99) x3,

Trunc (100.25678, 2) x4

From dual.

Sign (N) function

Sign function determines the number of numbers. If N is positive, then the function returns 1. If negative is returned -1. If zero 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 feature is the possibility of transmitting M equal to zero - it does not arise the division errors by 0.

Power (N, M) function

The POWER function builds the number N to the degree m. The degree may 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, when calling this feature, an exception may occur. For example:

SELECT POWER (-100, 1/2) X2

From dual.

In this case, an attempt is made to calculate a square root from a negative number, which will lead to an ORA-01428 "argument outside the range" error occur.

SQRT (N) function

This function returns a square root from N number. For example:

SELECT SQRT (100) X

From dual.

Functions exp (n) and ln (n)

The EXP function erends E to the degree n, and the LN function calculates the natural logarithm from N (with N, N must be greater than zero). Example:

SELECT EXP (2) X1, LN (1) X2, LN (exp (2)) x3

NVL feature

The NVL function is usually applied most often. The function receives two parameters: NVL (EXPR1, EXPR2). If the first EXPR1 parameter is not equal to NULL, then the function returns its value. If the first NULL parameter, then the function returns the value of the second EXPR2 parameter.

Consider a practical example. The COMM field in the EMP table may contain NULL values. When performing a query of the form:

SELECT EMPNO, ENAME, COMM, NVL (COMM, 0) NVL_COMM

From scott.emp

nULL value will be replaced by zero. Please note that in the case of the formation of the value with the help of the function, it is assigned to a pseudonym. Request results will be:

CEIL (N)

The CEIL function returns the smallest integer, greater or equal to the number of N. For example:

SELECT CEIL (100) X1, CEIL (-100) X2, CEIL (100.2) x3, Ceil (-100.2) x4

From dual.

TRUNC function (N [, M])

The trunc function returns the number n truncated to m signs after the decimal point. The m parameter may not be indicated - in this case, N truncated to the whole.

SELECT TRUNC (100.25678) x1, trunc (-100.25678) x2, trunc (100.99) x3,

Trunc (100.25678, 2) x4

From dual.

Sign (N) function

Sign function determines the number of numbers. If N is positive, then the function returns 1. If negative is returned -1. If zero 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 feature is the possibility of transmitting M equal to zero - it does not arise the division errors by 0.

Power (N, M) function

The POWER function builds the number N to the degree m. The degree may 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, when calling this feature, an exception may occur. For example:

SELECT POWER (-100, 1/2) X2

From dual.

In this case, an attempt is made to calculate a square root from a negative number, which will lead to an ORA-01428 "argument outside the range" error occur.

SQRT (N) function

This function returns a square root from N number. For example:

SELECT SQRT (100) X

From dual.

Functions exp (n) and ln (n)

The EXP function erends E to the degree n, and the LN function calculates the natural logarithm from N (with N, N must be greater than zero). Example:

SELECT EXP (2) X1, LN (1) X2, LN (exp (2)) x3