Oracle PL/SQL/Numeric Math Functions/FLOOR
Содержание
FLOOR: Returns the floor value (next lowest integer below number)
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table TestTable(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 MyName VARCHAR2(10 BYTE), 4 MyDate DATE, 5 MyNumber Number(8,2) 6 ) 7 /
Table created. SQL> SQL> SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("2","Jason",to_date("19970622","YYYYMMDD"),-12.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("3","Smith",to_date("19980513","YYYYMMDD"),22.1); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("4","Tailor",to_date("19990624","YYYYMMDD"),-2.12); 1 row created. SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("5","Darlene",to_date("20000415","YYYYMMDD"),2.1); 1 row created. SQL> SQL> SQL> select * from TestTable; ID MYNAME MYDATE MYNUMBER
---------- --------- ----------
1 Alison 11-JUL-96 12.12 2 Jason 22-JUN-97 -12.12 3 Smith 13-MAY-98 22.1 4 Tailor 24-JUN-99 -2.12 5 Darlene 15-APR-00 2.1 SQL> SQL> SQL> SQL> -- FLOOR: Returns the floor value (next lowest integer below number). SQL> SQL> select MyNumber, FLOOR(MyNumber) from TestTable;
MYNUMBER FLOOR(MYNUMBER)
---------------
12.12 12 -12.12 -13 22.1 22 -2.12 -3 2.1 2
SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
FLOOR(x): Returns the largest integer less than or equal to x
<source lang="sql">
SQL> --FLOOR(x): Returns the largest integer less than or equal to x. SQL> SQL> select FLOOR(-5.2) from dual; FLOOR(-5.2)
-6
SQL>
</source>
round(345.678), ceil(345.678), floor(345.678)
<source lang="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 </source>
Use floor(), mod() and date calculation to show how many weeks and days an employee has been working here
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select ename
2 , floor( (sysdate-bdate)/7) as weeks 3 , floor(mod(sysdate-bdate,7)) as days 4 from emp 5 where deptno = 10;
ENAME WEEKS DAYS
---------- ----------
Chris 2315 5 Peter 2971 0 Mike 2491 6 SQL> SQL> drop table emp; Table dropped.
</source>