Oracle PL/SQL Tutorial/Numerical Math Functions/ROUND
Содержание
- 1 Demonstrates the ROUND function
- 2 ROUND(345.678,2) ROUND(345.678,-1) ROUND(345.678,-2)
- 3 ROUND(5.75)
- 4 ROUND(
- 5 ROUND(5.75, -1) (2)
- 6 ROUND column value
- 7 ROUND for negative value
- 8 ROUND may have a second argument to handle precision: means the distance to the right of the decimal point
- 9 round(sqrt(sal),2)
- 10 ROUND(x, [y]) gets the result of rounding x an optional y decimal places.
- 11 The second argument of ROUND defaults to 0
- 12 The second argument of ROUND, precision, may be negative, which means displacement to the left of the decimal point
Demonstrates the ROUND function
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 v_round NUMBER (10,4) := 12345.6789;
3 BEGIN
4
5 DBMS_OUTPUT.PUT_LINE("Default: "||ROUND(v_round));
6 DBMS_OUTPUT.PUT_LINE("+2: "||ROUND(v_round, 2));
7 DBMS_OUTPUT.PUT_LINE("-2: "||ROUND(v_round, -2));
8
9 END;
10 /
Default: 12346 +2: 12345.68 -2: 12300 PL/SQL procedure successfully completed. SQL> SQL></source>
ROUND(345.678,2) ROUND(345.678,-1) ROUND(345.678,-2)
<source lang="sql">
SQL> SQL> SQL> select round(345.678,2)
2 , round(345.678,-1) 3 , round(345.678,-2) 4 from dual;
ROUND(345.678,2) ROUND(345.678,-1) ROUND(345.678,-2)
----------------- -----------------
345.68 350 300
SQL></source>
ROUND(5.75)
<source lang="sql">
SQL> select ROUND(5.75) from dual; ROUND(5.75)
6</source>
ROUND(
<source lang="sql">
SQL> select ROUND(5.75, 1) from dual; ROUND(5.75,1)
5.8</source>
ROUND(5.75, -1) (2)
<source lang="sql">
SQL> select ROUND(5.75, -1) from dual; ROUND(5.75,-1)
10</source>
ROUND column value
<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, ROUND(value) FROM myTable
2 /
ID VALUE ROUND(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 -6
8 123.45 123
9 98.23 98
9 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL> SQL></source>
ROUND for negative value
"Next highest absolute value" for negative numbers rounds to the negative value of the appropriate absolute value of the negative number; e.g., ROUND(6.8) = 7
<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> SQL> SELECT id, value,ROUND(value) FROM myTable
2 /
ID VALUE ROUND(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 -6
8 123.45 123
9 98.23 98
9 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL> SQL></source>
ROUND may have a second argument to handle precision: means the distance to the right 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, ROUND(value,1)FROM myTable
2 /
ID VALUE ROUND(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.9
8 123.45 123.5
9 98.23 98.2
9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
round(sqrt(sal),2)
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select ename, sal, round(sqrt(sal),2) "Bonus" from emp; ENAME SAL Bonus
---------- ----------
SMITH 800 28.28 ALLEN 1600 40 WARD 1250 35.36 JONES 2975 54.54 MARTIN 1250 35.36 BLAKE 2850 53.39 CLARK 2450 49.5 SCOTT 3000 54.77 KING 5000 70.71 TURNER 1500 38.73 ADAMS 1100 33.17 ENAME SAL Bonus
---------- ----------
JAMES 950 30.82 FORD 3000 54.77 MILLER 1300 36.06 14 rows selected. SQL> SQL> drop table emp; Table dropped.</source>
ROUND(x, [y]) gets the result of rounding x an optional y decimal places.
If y is omitted, x is rounded to zero decimal places.
If y is negative, x is rounded to the left of the decimal point.
The following example uses ROUND() to display the result of rounding 5.75 to zero, 1, and -1 decimal places, respectively:
<source lang="sql">
SQL> SQL> SELECT ROUND(5.75), ROUND(5.75, 1), ROUND(5.75, -1) FROM dual; ROUND(5.75) ROUND(5.75,1) ROUND(5.75,-1)
------------- --------------
6 5.8 10
SQL> SQL></source>
The second argument of ROUND 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, ROUND(value,0), ROUND(value) FROM myTable
2 /
ID VALUE ROUND(VALUE,0) ROUND(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 -6 -6
8 123.45 123 123
9 98.23 98 98
9 rows selected. SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
The second argument of ROUND, 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> SELECT id, value, ROUND(value,-1) FROM myTable
2 /
ID VALUE ROUND(VALUE,-1)
---------- ---------------
1 9 10
2 2.11 0
3 3.44 0
4 -4.21 0
5 10 10
6 3 0
7 -5.88 -10
8 123.45 120
9 98.23 100
9 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
