в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:50Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Numerical Math Functions/ROUND - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия