Learning the value and date function of Oracle database

The study of numerical function

　　1) Number (p,s), # (P), # (*,s), p has a value of 1-38.

2) compatibility of numeric types

--:numeric (p,s) equals number (P,s)

--:decimal (p,s) or Dec (p,s) equals number (P,s)

--:integer or int (cannot specify digits) equivalent to number (38)

--:smallint equivalent to number (38)

--:float equivalent to number

--:double precision equivalent to number

--:real equivalent to number

3) Rounding function: Round (p1,n) rounds the P1, n indicates the number of digits reserved for the decimal point, does not write the default is 0, can be negative, indicating that it is reserved to the first n digits of the decimal point.

Eg: rounding the number 3.14 and reserving two decimal places

Select Round (3.14,2) from dual;

4) The Digital intercept function trunc (p1,n) intercepts the P1, retains the n digits after the decimal point, and n is the negative number until the decimal point N.

Eg: Intercept the number 3.14, leaving two digits after the decimal point

Select Trunc (3.14,2) from dual;

5) The remainder function mod (m,n) indicates that M is doing division of N and taking the residue.

Eg: calculates 100 to 3 to take the remainder

Select mod (100,3) from dual;

6) Take the whole function

--: Rounding up Ceil (m) to take the smallest integer greater than or equal to M

--: Take down the entire floor (m) to take the largest integer less than equal to M

Eg: upward and downward rounding of-3.4 respectively

Select Ceil ( -3.14), Ceil (4.5) from dual;

Second, the study of the date function

　　1)

--:to_char (P1,P2) indicates that the date P1 is converted to a string type in P2 format, which is enclosed in double quotation marks if there is a Chinese character in the format.

--:to_date (P1,P2) indicates that the string P1 is converted to a date type in P2 format.

2) Last_day (p1) View the date of the last day in the P1 month, P1 is a date type.

Eg: Check the last day of the month of birth date

Select Last_day (to_date (' 1997-3-6 ', ' Yyyy-mm-dd ')) from dual;

3) Add_month (P1,n) on the basis of the date P1 added n months to return n months after the date, n can be a decimal, will be truncated to an integer increase n can also be negative, minus n months.

Eg: date after one months of April 15, 2018

Select Add_month (to_date (' 2018-4-15 ', ' Yyyy-mm-dd '), 1) from dual;

4) Month_between (P1,P2) calculates the month of the difference between the two dates P1 and P2, there will be a fractional case, calculate p1-p2.

Eg: Calculates the month in which the current system time differs from the date of birth, and retains one decimal place.

Select Round (Month_between (sysdate,to_date (' 1997-3-6 ', ' yyyy-mm-dd ')), 1) from dual;

5) Next_day (Date,num) represents the date of the next week of the parameter date, and date represents a date, num:1~7.

Eg: View the next Sunday time for the current system time

Select Next_day (sysdate,1) from dual;

6)

--:least (P1,p2,p3 ...) Multiple parameter comparisons display the smallest data.

--:greatest (P1,p2,p3 ...) Multiple parameter comparisons display the largest data.

Requirement: The parameter is the same type, or the default is converted to the type of the first parameter, and the string comparison is the ASCII code of the comparison character.

Eg: Statistics the maximum value in 3,2,1,4,5

Select Greatest (3,2,1,4,5) from dual;

7) Extract (P1 from P2) represents extracting component P1 from date P2, component of date: year, month, day, hour, minute, second, date type keywords: sysdate and systimestamp.

Eg: Extracting component years from Sysdate and Systimestamp respectively

Select Extract (year from sysdate), extract (Yaer from Systimestamp) from dual;

　　　　

Learning the value and date function of Oracle database