Oracle function-one-line function-number, date, date processing function
Classification of functions
One-line function: an input corresponds to an output,input and there is an one-to-one correspondence between output,input and output, such as lower
Group function: multiple input, but only one output. Such as sum ()
=
One-line function
Features:
Each row returns a result, and there is an one-to-one correspondence between input and output.
Can be nested, the output of one function can be used as the input of another function, such as: select lowner (upper ('aa')) from dual
The variable passed in can be the value of a column or an expression. Such as select lower (ename) from emp
=
1. Numeric function
Manipulating numbers is a supplement to addition, subtraction, multiplication and division.
SQL > select round (45.926 point 2) from dual;-rounded, with a few decimal places reserved in the second place
SQL > select trunc (45.926) from dual;-intercept directly, not rounded
SQL > select mod (10jue 3) from dual;-- find the remainder
2. Date function
The default display format for date stored in oracle database is DD-MON-YY
Modify the date display format:
Alter session set nls_date_format=''
3. SYSDATA: check the system time
SQL > select sysdate from dual;-- View the current time of the system
SQL > select sysdate,sysdate-7 from dual;-minus 1 week
SQL > select sysdate,sysdate-1 from dual;-minus 1 day
SQL > select sysdate,sysdate-1/24 from dual;-minus 1 hour
SQL > select sysdate,sysdate-1/24/60 from dual;-minus 1 minute
SQL > select sysdate,sysdate-1/24/60/60 from dual;-minus 1 second
4. Date processing function
Add or subtract a value from the existing date to get a date. Such as select sysdate,sysdate-7 from dual
Subtract two dates to get the number of days (interval) of two dates, such as select (sysdate-hiredate) / 7 weeks from emp
How many months are there between two dates of ① months_between
SQL > select months_between (sysdate,sysdate-1000) from dual
How many months are added under the date specified by ② add_months
SQL > select add_months (sysdate,2) from dual
The last day of the date specified by ③ last_date
SQL > select last_day (sysdate) from dual
5. Rounding the date:
For the year, it is rounded from January to June and from July to December.
For the month, it is rounded according to 1-15. 15-31.
For Japan, from Sunday to Wednesday, note: the beginning of each week is Sunday.
SQL > select sysdate from dual
SYSDATE
-
16-MAY-16 is currently May 16, 2006.
=
SQL > select round (sysdate,'year') from dual
-rounding the year, that is, accurate to the year, it is now May, not June, so the result is 16-1-1.
ROUND (SYSDATE)
-
01-JAN-16
=
SQL > select round (sysdate+60,'year') from dual;-May 16 plus 60 days, it's July, so the result is 17-1-1
ROUND (SYSDATE)
-
01-JAN-17
=
SQL > select round (sysdate,'month') from dual
-rounding the month, that is, accurate to the month. It is now the 16th, after the 15th, all the results are the 1st of the next month.
ROUND (SYSDATE)
-
01-JUN-16
=
SQL > select round (sysdate+22,'month') from dual
-it's May 16, 22 days later is July 7, and there is no 15 on the 7th, so the result is 16-07-01.
ROUND (SYSDATE)
-
01-JUL-16
=
SQL >! date
Mon May 16 22:47:25 CST 2016
Monday, May 16, 2016, the beginning of the week is May 15 (every Sunday is the beginning of the week and Saturday is the end of the week)
=
SQL > select round (sysdate,'day') from dual
-rounding the day, that is, what day it is, May 16th is Monday, but not Wednesday, so it is not "rounded", showing the first day of the week.
ROUND (SYSDATE)
-
15-MAY-16
=
SQL > select round (sysdate+3,'day') from dual
-the 16th is Monday, plus three days is Thursday, more than Wednesday, so carry, show the Monday of the following week
=
SQL > select round (sysdate-3,'day') from dual
Monday minus three days is last week's Friday, surpassing Wednesday, so rounding shows the next Monday of the current week, that is, the 15th
