Oracle SQL statement realizes rounding and rounding of numbers

Time:2020-11-16

Rounding (rounding down)

Copy codeThe code is as follows:
select floor(5.534) from dual;
select trunc(5.534) from dual;

Both of the above two usages can round down the number 5. 534 and the result is 5

If you want to round up and get a result of 6, you should use ceil

Copy codeThe code is as follows:
select ceil(5.534) from dual;

rounding:

Copy codeThe code is as follows:
SELECT round(5.534) FROM dual;
SELECT round(5.534,0) FROM dual;
SELECT round(5.534,1) FROM dual;
SELECT round(5.534,2) FROM dual;

The results were 6,6,5.5,5.53

Keep N decimal places (not rounded)

Copy codeThe code is as follows:
select trunc(5.534,0) from dual;
select trunc(5.534,1) from dual;
select trunc(5.534,2) from dual;

The results are 5,5.5,5.53 respectively, in which keeping 0 decimal place is equivalent to rounding directly.

Number format:

Copy codeThe code is as follows:
select to_char(12345.123,’99999999.9999′) from dual;

The result was 12345.123

Copy codeThe code is as follows:
select to_char(12345.123,’99999999.9900′) from dual;

The third and fourth decimal point is insufficient complement 0, the result is 12345.1230

Copy codeThe code is as follows:
select to_char(0.123,’99999999.9900′) from dual;
select to_char(0.123,’99999990.9900′) from dual;

123, 0. 123, respectively

The above is the small edition of developeppaer for everyone to sort out the common operations on numbers in Oracle, the need for small partners can refer to it.