Oracle PL/SQL Tutorial/Numerical Math Functions/FLOOR

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

FLOOR(-5.2)

   <source lang="sql">

SQL> select FLOOR(-5.2) from dual; FLOOR(-5.2)


        -6</source>
   
  

FLOOR(5.8)

   <source lang="sql">

SQL> select FLOOR(5.8) from dual; FLOOR(5.8)


        5</source>
   
  

FLOOR for negative value

   <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,FLOOR(value) FROM myTable

 2  /
       ID      VALUE FLOOR(VALUE)

---------- ------------
        1          9            9
        2       2.11            2
        3       3.44            3
        4      -4.21           -5
        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>


FLOOR(x) gets the largest integer less than or equal to x.

The following example uses FLOOR() to display the absolute value of 5.8 and -5.2, respectively:



   <source lang="sql">

SQL> SELECT FLOOR(5.8), FLOOR(-5.2) FROM dual; FLOOR(5.8) FLOOR(-5.2)


-----------
        5          -6

SQL></source>


select round(345.678), ceil(345.678), floor(345.678)

   <source lang="sql">

SQL> SQL> SQL> select round(345.678), ceil(345.678), floor(345.678)

 2  from   dual;

ROUND(345.678) CEIL(345.678) FLOOR(345.678)


------------- --------------
          346           346            345

SQL> SQL> SQL></source>


Use floor and mod to get the number of days

   <source lang="sql">

SQL> SQL> create table employees(

 2    empno      NUMBER(4)
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , msal       NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2) ) ;

Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> select ename

 2  ,      floor(mod(sysdate-bdate,7)) as days
 3  from   employees;

ENAME DAYS


----------

Jason 5 Jerry 5 Jord 5 Mary 4 Joe 5 Black 6 Red 6 White 6 Yellow 6 Pink 0 10 rows selected. SQL> SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>


Use FLOOR function to get the flooring value

   <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> SELECT id, value,FLOOR(value) FROM myTable

 2  /
       ID      VALUE FLOOR(VALUE)

---------- ------------
        1          9            9
        2       2.11            2
        3       3.44            3
        4      -4.21           -5
        5         10           10
        6          3            3
        7      -5.88           -6
        8     123.45          123
        9      98.23           98

9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped. SQL> SQL></source>


Use floor to get the number of weeks

   <source lang="sql">

SQL> SQL> SQL> create table employees(

 2    empno      NUMBER(4)
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , msal       NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2) ) ;

Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> select ename ,floor((sysdate-bdate)/7) as weeks

 2  from   employees;

ENAME WEEKS


----------

Jason 2222 Jerry 2174 Jord 2126 Mary 2078 Joe 2030 Black 1982 Red 1934 White 1886 Yellow 1838 Pink 1791 10 rows selected. SQL> SQL> SQL> drop table employees; Table dropped.</source>