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
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>
ROUND(345.678,2) ROUND(345.678,-1) ROUND(345.678,-2)
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>
ROUND(5.75)
SQL> select ROUND(5.75) from dual;
ROUND(5.75)
-----------
6
ROUND(
SQL> select ROUND(5.75, 1) from dual;
ROUND(5.75,1)
-------------
5.8
ROUND(5.75, -1) (2)
SQL> select ROUND(5.75, -1) from dual;
ROUND(5.75,-1)
--------------
10
ROUND column value
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>
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
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>
ROUND may have a second argument to handle precision: means the distance to the right of the decimal point
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>
round(sqrt(sal),2)
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.
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:
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>
The second argument of ROUND defaults to 0
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>
The second argument of ROUND, precision, may be negative, which means displacement to the left of the decimal point
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>