Oracle PL/SQL Tutorial/Numerical Math Functions/TRUNC
Содержание
- 1 The second argument of TRUNC defaults to 0
- 2 The second argument of TRUNC, precision, may be negative, which means displacement to the left of the decimal point
- 3 TRUNC(5.75)
- 4 TRUNC(
- 5 TRUNC(5.75, -1) (2)
- 6 TRUNC column value
- 7 TRUNC may have a second argument to handle precision: means the distance to the right of the decimal point
- 8 TRUNC(x, [y]) gets the result of truncating the number x to an optional y decimal places.
The second argument of TRUNC defaults to 0
<source lang="sql">
SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value NUMBER(6,2) 4 ) 5 /
Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created. SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created. SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created. SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created. SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created. SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created. SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created. SQL> SQL> select * from myTable
2 / ID VALUE
----------
1 9 2 2.11 3 3.44 4 -4.21 5 10 6 3 7 -5.88 8 123.45 9 98.23
9 rows selected. SQL> SQL> SELECT id, value, TRUNC(value,0), TRUNC(value) FROM myTable
2 / ID VALUE TRUNC(VALUE,0) TRUNC(VALUE)
---------- -------------- ------------
1 9 9 9 2 2.11 2 2 3 3.44 3 3 4 -4.21 -4 -4 5 10 10 10 6 3 3 3 7 -5.88 -5 -5 8 123.45 123 123 9 98.23 98 98
9 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
The second argument of TRUNC, precision, may be negative, which means displacement to the left of the decimal point
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value NUMBER(6,2) 4 ) 5 /
Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created. SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created. SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created. SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created. SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created. SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created. SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created. SQL> SQL> select * from myTable
2 / ID VALUE
----------
1 9 2 2.11 3 3.44 4 -4.21 5 10 6 3 7 -5.88 8 123.45 9 98.23
9 rows selected. SQL> SQL> SQL> SELECT id, value, TRUNC(value,-1) FROM myTable
2 / ID VALUE TRUNC(VALUE,-1)
---------- ---------------
1 9 0 2 2.11 0 3 3.44 0 4 -4.21 0 5 10 10 6 3 0 7 -5.88 0 8 123.45 120 9 98.23 90
9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
TRUNC(5.75)
<source lang="sql">
SQL> select TRUNC(5.75) from dual; TRUNC(5.75)
5</source>
TRUNC(
<source lang="sql">
SQL> select TRUNC(5.75, 1) from dual; TRUNC(5.75,1)
5.7</source>
TRUNC(5.75, -1) (2)
<source lang="sql">
SQL> select TRUNC(5.75, -1) from dual; TRUNC(5.75,-1)
0</source>
TRUNC column value
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value NUMBER(6,2) 4 ) 5 /
Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created. SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created. SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created. SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created. SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created. SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created. SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created. SQL> SQL> select * from myTable
2 / ID VALUE
----------
1 9 2 2.11 3 3.44 4 -4.21 5 10 6 3 7 -5.88 8 123.45 9 98.23
9 rows selected. SQL> SQL> SELECT id, value,TRUNC(value) FROM myTable
2 / ID VALUE TRUNC(VALUE)
---------- ------------
1 9 9 2 2.11 2 3 3.44 3 4 -4.21 -4 5 10 10 6 3 3 7 -5.88 -5 8 123.45 123 9 98.23 98
9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL> SQL></source>
TRUNC may have a second argument to handle precision: means the distance to the right of the decimal point
<source lang="sql">
SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value NUMBER(6,2) 4 ) 5 /
Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created. SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created. SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created. SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created. SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created. SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created. SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created. SQL> SQL> select * from myTable
2 / ID VALUE
----------
1 9 2 2.11 3 3.44 4 -4.21 5 10 6 3 7 -5.88 8 123.45 9 98.23
9 rows selected. SQL> SQL> SQL> SELECT id, value, TRUNC(value,1) FROM myTable
2 / ID VALUE TRUNC(VALUE,1)
---------- --------------
1 9 9 2 2.11 2.1 3 3.44 3.4 4 -4.21 -4.2 5 10 10 6 3 3 7 -5.88 -5.8 8 123.45 123.4 9 98.23 98.2
9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
TRUNC(x, [y]) gets the result of truncating the number x to an optional y decimal places.
If y is omitted, x is truncated to zero decimal places.
If y is negative, x is truncated to the left of the decimal point.
The following example displays truncating 5.75 to zero, 1, and - 1 decimal places:
<source lang="sql">
SQL> SELECT TRUNC(5.75), TRUNC(5.75, 1), TRUNC(5.75, -1) FROM dual; TRUNC(5.75) TRUNC(5.75,1) TRUNC(5.75,-1)
------------- --------------
5 5.7 0
SQL></source>