Oracle PL/SQL Tutorial/Numerical Math Functions/TRUNC — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:08, 26 мая 2010
Содержание
- 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
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>
The second argument of TRUNC, precision, may be negative, which means displacement to the left of the decimal point
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>
TRUNC(5.75)
SQL> select TRUNC(5.75) from dual;
TRUNC(5.75)
-----------
5
TRUNC(
SQL> select TRUNC(5.75, 1) from dual;
TRUNC(5.75,1)
-------------
5.7
TRUNC(5.75, -1) (2)
SQL> select TRUNC(5.75, -1) from dual;
TRUNC(5.75,-1)
--------------
0
TRUNC column value
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>
TRUNC may have a second argument to handle precision: means the distance to the right of the decimal point
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>
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:
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>