Oracle PL/SQL Tutorial/Numerical Math Functions/TRUNC

Материал из SQL эксперт
Перейти к: навигация, поиск

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>