Oracle PL/SQL/Numeric Math Functions/ROUND — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 ROUND(44.647, -1)
- 2 ROUND(5.75)
- 3 Round an AVG function
- 4 Round date to day
- 5 round Demo
- 6 Rounding Up and Down
- 7 ROUND(Number,0)
- 8 ROUND(Number,-1)
- 9 ROUND(Number,1): round values from column
- 10 Round price as new price
- 11 Round result from months_between
- 12 ROUND: Returns the number rounded to nearest value (precision adjustable)
- 13 Rounds 7:45:26 P.M. on May, to the nearest hour
- 14 Rounds May, to the first day in the nearest month
- 15 ROUND with precision
- 16 Simple demo for ROUND: round a number
- 17 Specifying negative precision will round numbers on the left side of the decimal point, as shown here:
- 18 Syntax: ROUND(<numeric expression>,<precision>)
- 19 Use ROUND function in PL/SQL
ROUND(44.647, -1)
SQL>
SQL>
SQL> SELECT ROUND(44.647, -1) AS NEGATIVE FROM Dual;
NEGATIVE
----------
40
SQL>
ROUND(5.75)
SQL> --ROUND(x [, y]) Returns the result of rounding x an optional
SQL> -- y decimal places. If y is omitted, x is
SQL> -- rounded to zero decimal places. If y is
SQL> -- negative, x is rounded to the left of the
SQL> -- decimal point.
SQL>
SQL> select ROUND(5.75) from dual;
ROUND(5.75)
-----------
6
SQL>
Round an AVG function
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W")
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E")
3 /
1 row created.
SQL>
SQL> select * from employee;
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> -- Query with a ROUND function
SQL>
SQL> SELECT empno, ename, orig_salary,
2 ROUND(AVG(orig_salary) OVER()) "Avg. salary"
3 FROM employee
4 ORDER BY ename;
EMPNO ENAME ORIG_SALARY Avg. salary
---------- ---------- ----------- -----------
122 Alison 45000 42000
104 Celia 53000 42000
117 David 78000 42000
123 James 23000 42000
108 Jode 21000 42000
116 Linda 43000 42000
105 Robert 31000 42000
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Round date to day
SQL>
SQL> SELECT ROUND(TO_DATE("25-OCT-2005"), "DD") FROM dual;
ROUND(TO_
---------
25-OCT-05
round Demo
SQL>
SQL> SELECT ROUND(1234.5678, 4) FROM DUAL;
ROUND(1234.5678,4)
------------------
1234.5678
SQL> SELECT ROUND(1234.5678, 3) FROM DUAL;
ROUND(1234.5678,3)
------------------
1234.568
SQL> SELECT ROUND(1234.5678, 2) FROM DUAL;
ROUND(1234.5678,2)
------------------
1234.57
SQL> SELECT ROUND(1234.5678, 1) FROM DUAL;
ROUND(1234.5678,1)
------------------
1234.6
SQL> SELECT ROUND(1234.5678, 0) FROM DUAL;
ROUND(1234.5678,0)
------------------
1235
SQL> SELECT ROUND(1234.5678, -1) FROM DUAL;
ROUND(1234.5678,-1)
-------------------
1230
SQL> SELECT ROUND(1234.5678, -2) FROM DUAL;
ROUND(1234.5678,-2)
-------------------
1200
SQL> SELECT ROUND(1234.5678, -3) FROM DUAL;
ROUND(1234.5678,-3)
-------------------
1000
SQL>
Rounding Up and Down
SQL>
SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL,
3 ExamID INT NOT NULL,
4 Mark INT,
5 IfPassed SMALLINT,
6 Comments VARCHAR(255),
7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));
Table created.
SQL>
SQL>
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory");
1 row created.
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result");
1 row created.
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard");
1 row created.
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple");
1 row created.
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1);
1 row created.
SQL>
SQL>
SQL> SELECT StudentID, AVG(CAST(Mark AS FLOAT)) AS AverageMark,
2 FLOOR(AVG(CAST(Mark AS FLOAT))) AS RoundDown,
3 CEIL(AVG(CAST(Mark AS FLOAT))) AS RoundUp,
4 ROUND(AVG(CAST(Mark AS FLOAT)), 0) AS ClosestInt
5 FROM SAT
6 GROUP BY StudentID;
STUDENTID AVERAGEMARK ROUNDDOWN ROUNDUP CLOSESTINT
---------- ----------- ---------- ---------- ----------
1 64 64 64 64
2 48.6666667 48 49 49
2 rows selected.
SQL>
SQL>
SQL>
SQL> drop table SAT;
Table dropped.
ROUND(Number,0)
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> SELECT ID, ROUND(MyNumber,0), TRUNC(MyNumber,0) FROM TestTable;
ID ROUND(MYNUMBER,0) TRUNC(MYNUMBER,0)
---- ----------------- -----------------
1 12 12
1 -12 -12
1 22 22
1 -2 -2
1 2 2
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
ROUND(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>
ROUND(Number,1): round values from 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>
Round price as new price
SQL>
SQL> create table product(
2 product_id integer primary key
3 ,price number(7,2)
4 ,description varchar2(75)
5 ,onhand number(5,0)
6 ,reorder number(5,0)
7 ,supplier_no integer
8 );
Table created.
SQL> -- product Table Inserts:
SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"Happy Birthday",100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"Happy Birthday",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"Happy New Year",null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"Happy New Year",50,10);
1 row created.
SQL>
SQL> select description, round(price) as price
2 from product;
DESCRIPTION
---------------------------------------------------------------------------
PRICE
----------
Happy Birthday
3
Happy Birthday
23
Happy New Year
Happy New Year
2
4 rows selected.
SQL>
SQL> drop table product;
Table dropped.
Round result from months_between
SQL>
SQL>
SQL> select round(months_between("17-MAR-61","21-APR-62"))
2 from dual;
ROUND(MONTHS_BETWEEN("17-MAR-61","21-APR-62"))
----------------------------------------------
-13
1 row selected.
SQL>
ROUND: Returns the number rounded to nearest value (precision adjustable)
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> -- ROUND: Returns the number rounded to nearest value (precision adjustable).
SQL>
SQL>
SQL>
SQL> select MyNumber, ROUND(MyNumber) from TestTable;
MYNUMBER ROUND(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>
Rounds 7:45:26 P.M. on May, to the nearest hour
SQL>
SQL> SELECT TO_CHAR(ROUND(TO_DATE("25-MAY-2005 19:45:26","DD-MON-YYYY HH24:MI:SS
"), "HH24"), "DD-MON-YYYY HH24:MI:SS") FROM dual;
TO_CHAR(ROUND(TO_DAT
--------------------
25-MAY-2005 20:00:00
SQL>
SQL>
SQL>
Rounds May, to the first day in the nearest month
SQL>
SQL> SELECT ROUND(TO_DATE("25-MAY-2005"), "MM") FROM dual;
ROUND(TO_
---------
01-JUN-05
SQL>
SQL>
ROUND with precision
SQL>
SQL>
SQL> --Consider the following example:
SQL>
SQL> SELECT ROUND(44.647, 2) AS POSITIVE FROM Dual;
POSITIVE
----------
44.65
SQL>
Simple demo for ROUND: round a number
SQL>
SQL>
SQL> SELECT ROUND(44.647, 0) AS ZERO FROM Dual;
ZERO
----------
45
SQL>
Specifying negative precision will round numbers on the left side of the decimal point, as shown here:
SQL>
SQL> SELECT ROUND(109.09 ,-1) rounded from dual;
ROUNDED
----------
110
SQL>
Syntax: ROUND(<numeric expression>,<precision>)
SQL>
SQL>
SQL> SELECT ROUND(109.09 ,1) rounded from dual;
ROUNDED
----------
109.1
Use ROUND function in PL/SQL
SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(ROUND(3.5));
3 DBMS_OUTPUT.PUT_LINE(ROUND(105.09,1));
4 DBMS_OUTPUT.PUT_LINE(ROUND(-3.5));
5 DBMS_OUTPUT.PUT_LINE(ROUND(105.15,-2));
6 DBMS_OUTPUT.PUT_LINE(ROUND(150.15,-2));
7 END;
8 /
4
105.1
-4
100
200
PL/SQL procedure successfully completed.
SQL>
SQL>