Oracle digital function

Numeric function

1, take the entire function (ceil rounded up, floor rounded down)

Select ceil (66.6) N1 Magnum (66.6) N2 from dual

2, take power (power) and find square root (sqrt)

Select power (3prime2) N1 Magi sqrt (9) N2 from dual

3, ask for the remainder

Select mod (9 dint 5) from dual

4. Returns a fixed number of decimal places (round: rounded, trunc: directly truncated)

Select round (66.667) N1 from dual trunc (66.667) N2

5, the symbol of the return value (positive number returns 1, negative number is-1)

Select sign (- 32), sign (293) from dual

Other functions

1.user:

Returns the name of the logged in user

Select user from dual

2.vsize:

Returns the number of bytes required for the expression

Select vsize ('HELLO') from dual

3.nvl (ex1,ex2):

Ex2 is returned if ex1 is empty, otherwise the value itself is ex1 (commonly used)

Example: if the employee has no commission, 0 will be displayed, otherwise the commission will be displayed.

Select comm,nvl (comm,0) from emp

4.nullif (ex1,ex2):

The value is equal to return empty, otherwise the first value is returned

Ex.: if the salary is equal to the commission, it will be empty, otherwise the salary will be displayed

Select nullif (sal,comm), sal,comm from emp

5.coalesce:

Returns the first non-empty expression in the list

Select comm,sal,coalesce (comm,sal,sal*10) from emp

6.nvl2 (ex1,ex2,ex3):

If ex1 is not empty, display ex2, otherwise display ex3

For example, check the names of employees with commission and their commission

Select nvl2 (comm,ename,') as HaveCommName,comm from emp

Original address; http://bbs.delit.cn/thread-198-1-1.html

Please indicate the source of the reprint

Written by: measurement Technology http://www.delit.cn