Oracle PL/SQL/Numeric Math Functions/ROUND
Содержание
- 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)
<source lang="sql">
SQL> SQL> SQL> SELECT ROUND(44.647, -1) AS NEGATIVE FROM Dual;
NEGATIVE
40
SQL>
</source>
ROUND(5.75)
<source lang="sql">
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>
</source>
Round an AVG function
<source lang="sql">
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>
</source>
Round date to day
<source lang="sql">
SQL> SQL> SELECT ROUND(TO_DATE("25-OCT-2005"), "DD") FROM dual; ROUND(TO_
25-OCT-05
</source>
round Demo
<source lang="sql">
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>
</source>
Rounding Up and Down
<source lang="sql">
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.
</source>
ROUND(Number,0)
<source lang="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> 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>
</source>
ROUND(Number,-1)
<source lang="sql">
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>
</source>
ROUND(Number,1): round values from column
<source lang="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> 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>
</source>
Round price as new price
<source lang="sql">
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.
</source>
Round result from months_between
<source lang="sql">
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>
</source>
ROUND: Returns the number rounded to nearest value (precision adjustable)
<source lang="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> -- 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>
</source>
Rounds 7:45:26 P.M. on May, to the nearest hour
<source lang="sql">
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>
</source>
Rounds May, to the first day in the nearest month
<source lang="sql">
SQL> SQL> SELECT ROUND(TO_DATE("25-MAY-2005"), "MM") FROM dual; ROUND(TO_
01-JUN-05 SQL> SQL>
</source>
ROUND with precision
<source lang="sql">
SQL> SQL> SQL> --Consider the following example: SQL> SQL> SELECT ROUND(44.647, 2) AS POSITIVE FROM Dual;
POSITIVE
44.65
SQL>
</source>
Simple demo for ROUND: round a number
<source lang="sql">
SQL> SQL> SQL> SELECT ROUND(44.647, 0) AS ZERO FROM Dual;
ZERO
45
SQL>
</source>
Specifying negative precision will round numbers on the left side of the decimal point, as shown here:
<source lang="sql">
SQL> SQL> SELECT ROUND(109.09 ,-1) rounded from dual;
ROUNDED
110
SQL>
</source>
Syntax: ROUND(<numeric expression>,<precision>)
<source lang="sql">
SQL> SQL> SQL> SELECT ROUND(109.09 ,1) rounded from dual;
ROUNDED
109.1 </source>
Use ROUND function in PL/SQL
<source lang="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>
</source>
