Oracle PL/SQL/Numeric Math Functions/TRUNC — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 select trunc( 12345.67890 )
- 2 select trunc( 12345.
- 3 The value 109.29 was truncated to the single digit on the left side of the decimal point
- 4 TRUNC(
- 5 TRUNC(44.647, -1)
- 6 TRUNC Demo
- 7 TRUNC(Number,-1)
- 8 TRUNC(Number,1): truncate values from a column
- 9 TRUNC() returns its argument truncated to the number of decimal places specified with the second argument.
- 10 TRUNC: Returns the truncated value (removes decimal part of a number, precision adjustable)
- 11 TRUNC with precision
- 12 TRUNC(x [, y]) Returns the result of truncating x an optional y decimal places
- 13 Use TRUNC in PL/SQL
select trunc( 12345.67890 )
SQL>
SQL>
SQL> select trunc( 12345.67890 )
2 from dual
3 /
TRUNC(12345.67890)
------------------
12345
1 row selected.
SQL>
SQL> --
select trunc( 12345.
SQL>
SQL> select trunc( 12345.67890, 2 )
2 from dual
3 /
TRUNC(12345.67890,2)
--------------------
12345.67
1 row selected.
SQL>
SQL> --
The value 109.29 was truncated to the single digit on the left side of the decimal point
SQL>
SQL> SELECT
2 TRUNC(109.29, -1) truncated
TRUNC(
SQL>
SQL> SELECT TRUNC(44.647, 0) AS ZERO FROM Dual;
ZERO
----------
44
TRUNC(44.647, -1)
SQL>
SQL>
SQL> SELECT TRUNC(44.647, -1) AS NEGATIVE FROM Dual;
NEGATIVE
----------
40
SQL>
TRUNC Demo
SQL> SELECT TRUNC(1234.5678, 4) FROM DUAL;
TRUNC(1234.5678,4)
------------------
1234.5678
SQL> SELECT TRUNC(1234.5678, 3) FROM DUAL;
TRUNC(1234.5678,3)
------------------
1234.567
SQL> SELECT TRUNC(1234.5678, 2) FROM DUAL;
TRUNC(1234.5678,2)
------------------
1234.56
SQL> SELECT TRUNC(1234.5678, 1) FROM DUAL;
TRUNC(1234.5678,1)
------------------
1234.5
SQL> SELECT TRUNC(1234.5678, 0) FROM DUAL;
TRUNC(1234.5678,0)
------------------
1234
SQL> SELECT TRUNC(1234.5678, -1) FROM DUAL;
TRUNC(1234.5678,-1)
-------------------
1230
SQL> SELECT TRUNC(1234.5678, -2) FROM DUAL;
TRUNC(1234.5678,-2)
-------------------
1200
SQL> SELECT TRUNC(1234.5678, -3) FROM DUAL;
TRUNC(1234.5678,-3)
-------------------
1000
TRUNC(Number,-1)
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 MyName VARCHAR2(10 BYTE),
4 MyDate DATE,
5 MyNumber Number(8,2)
6 )
7 /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL> select * from TestTable;
ID MYNAME MYDATE MYNUMBER
---- ---------- --------- ----------
1 Alison 11-JUL-96 12.12
1 Alison 22-JUN-97 -12.12
1 Alison 13-MAY-98 22.1
1 Alison 24-JUN-99 -2.12
1 Alison 15-APR-00 2.1
SQL>
SQL> SELECT ID, ROUND(MyNumber,-1), TRUNC(MyNumber,-1) FROM TestTable;
ID ROUND(MYNUMBER,-1) TRUNC(MYNUMBER,-1)
---- ------------------ ------------------
1 10 10
1 -10 -10
1 20 20
1 0 0
1 0 0
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>
TRUNC(Number,1): truncate values from a column
SQL>
SQL>
SQL> -- create demo table
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 MyName VARCHAR2(10 BYTE),
4 MyDate DATE,
5 MyNumber Number(8,2)
6 )
7 /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL>
SQL> select * from TestTable;
ID MYNAME MYDATE MYNUMBER
---- ---------- --------- ----------
1 Alison 11-JUL-96 12.12
1 Alison 22-JUN-97 -12.12
1 Alison 13-MAY-98 22.1
1 Alison 24-JUN-99 -2.12
1 Alison 15-APR-00 2.1
SQL>
SQL>
SQL>
SQL> SELECT ROUND(MyNumber,1), TRUNC(MyNumber,1) FROM TestTable;
ROUND(MYNUMBER,1) TRUNC(MYNUMBER,1)
----------------- -----------------
12.1 12.1
-12.1 -12.1
22.1 22.1
-2.1 -2.1
2.1 2.1
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>
TRUNC() returns its argument truncated to the number of decimal places specified with the second argument.
SQL>
Syntax:TRUNC(<numeric expression>, <decimal_places>)
TRUNCATE(<numeric_expression>,<decimal_places>))
SQL>
SQL> SELECT TRUNC(109.29, 1) truncated from dual;
TRUNCATED
----------
109.2
TRUNC: Returns the truncated value (removes decimal part of a number, precision adjustable)
SQL>
SQL>
SQL> -- create demo table
SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 MyName VARCHAR2(10 BYTE),
4 MyDate DATE,
5 MyNumber Number(8,2)
6 )
7 /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("2","Jason",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("3","Smith",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("4","Tailor",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("5","Darlene",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL>
SQL> select * from TestTable;
ID MYNAME MYDATE MYNUMBER
---- ---------- --------- ----------
1 Alison 11-JUL-96 12.12
2 Jason 22-JUN-97 -12.12
3 Smith 13-MAY-98 22.1
4 Tailor 24-JUN-99 -2.12
5 Darlene 15-APR-00 2.1
SQL>
SQL>
SQL>
SQL> -- TRUNC: Returns the truncated value (removes decimal part of a number, precision adjustable).
SQL>
SQL> select MyNumber, TRUNC(MyNumber) from TestTable;
MYNUMBER TRUNC(MYNUMBER)
---------- ---------------
12.12 12
-12.12 -12
22.1 22
-2.12 -2
2.1 2
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>
SQL>
TRUNC with precision
SQL>
SQL> SELECT TRUNC(44.647, 2) AS POSITIVE FROM Dual;
POSITIVE
----------
44.64
SQL>
TRUNC(x [, y]) Returns the result of truncating x an optional y decimal places
SQL>
SQL> --TRUNC(x [, y]) Returns the result of truncating x 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.
SQL>
SQL>
SQL> select TRUNC(5.75) from dual;
TRUNC(5.75)
-----------
5
SQL>
Use TRUNC in PL/SQL
SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(TRUNC(3.5));
3 DBMS_OUTPUT.PUT_LINE(TRUNC(105.09,1));
4 DBMS_OUTPUT.PUT_LINE(TRUNC(-3.5));
5 DBMS_OUTPUT.PUT_LINE(TRUNC(105.15,-2));
6 DBMS_OUTPUT.PUT_LINE(TRUNC(150.15,-2));
7 END;
8 /
3
105
-3
100
100
PL/SQL procedure successfully completed.
SQL>