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>
