Oracle PL/SQL Tutorial/Numerical Math Functions/ROUND

Материал из SQL эксперт
Перейти к: навигация, поиск

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>