numeric functions
1, take the whole function (ceil up, floor down rounding)
Select Ceil (66.6) N1,floor (66.6) N2 from dual;
2, exponentiation (Power) and square root (sqrt)
Select Power (3,2) n1,sqrt (9) N2 from dual;
3, redundancy
Select mod (9,5) from dual;
4, returns a fixed number of decimal digits (round: Rounding, Trunc: direct truncation)
Select Round (66.667,2) N1,trunc (66.667,2) N2 from dual;
5, the sign of the return value (positive return is 1, negative number is-1)
Select sign ( -32), sign (293) from dual;
Other functions
1.user:
Returns the user name of the login
Select User from Dual;
2.vsize:
The number of bytes required to return an expression
Select Vsize (' HELLO ') from dual;
3.NVL (EX1,EX2):
EX1 value is null returns EX2, otherwise the value itself is returned EX1 (common)
Example: If an employee does not have a commission, 0 is displayed, otherwise the Commission is displayed
Select COMM,NVL (comm,0) from EMP;
4.nullif (EX1,EX2):
value is equal to NULL, otherwise the first value is returned
Example: If wages and commissions are equal, the display is empty, otherwise the wages are displayed
Select Nullif (Sal,comm), sal,comm from EMP;
5.COALESCE:
Returns the first non-empty expression in a list
Select Comm,sal,coalesce (comm,sal,sal*10) from EMP;
6.NVL2 (EX1,EX2,EX3):
If Ex1 is not empty, display ex2, otherwise ex3
such as: View the names of employees with commissions and their commissions
Select NVL2 (Comm,ename, ') as Havecommname,comm from EMP;
The original address; http://bbs.delit.cn/thread-198-1-1.html
Reprint please specify the source:
Author: metric Technology www. Delit. cn
Oracle Digital functions