Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.