Oracle Common functions (2)

1, TRIM ([{{leading | TRAILING | BOTH}[Trim_character]| Trim_character} from]trim_source) function
Parameter:leading   if the leading parameter is specified, the Oracle database will remove any opening characters equal to Trim_character.
Parameter:trailing  If you specify the traling parameter, Oracle will remove any trailing characters equal to Trim_character.
Parameter:both  If the both parameter is specified or none of the three parameters are specified, Oracle removes any start and end characters equal to Trim_character.
Parameter:trim_character  If the Trim_character parameter is not specified, the default stripped value is a space.
Parameter:trim_source   If you specify only the clipping source (trim_source), Oracle removes the opening and closing spaces of the Trim_source.
Note: A, Trim function returns a VARCHAR2 type value. The maximum length of the value equals the length of the Trim_source.
            B, if Trim_source and trim_character have one null, The TRIM function returns NULL.
            C, Trim_character and trim_ Source can be any of the following data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, OR NCLOB.
            D, the type of the return value is the same as the data type of Trim_source.

Select Trim (Leading ' B ' from ' bboat are good ') "Test_trim" from Dual;--oat are goodselect trim (trailing ' d ' from ' bboat are g Ood ' Test_trim ' from Dual;--bboat are gooselect trim (both ' d ' from ' d--boat are handsome--d ') from dual t;----Boat is hand Some--select Trim (' d ' from ' d--boat are handsome--d ') from dual t;----Boat was handsome--select length (trim (leading from ' d --boat is handsome--d ')) from dual T;--23select length (Trim (' d--boat was handsome--d ')) from dual T;--22select trim (trai Ling null from  ' boat ') Nulltrim from Dual;select trim (both ' B ' from  null) Nulltrim from dual;

2. Abs function

Purpose: Returns the absolute value of the specified values

Select ABS (+), ABS ( -100) from dual;--100 100select abs (NULL) from dual T;select abs (") from dual t;

3. ceil function

Purpose: Returns the smallest integer greater than or equal to the given number

Select Ceil ( -7.09) from dual T,---7select ceil (7.09) from dual t;--8

4. Floor function

Purpose: Returns the largest integer less than or equal to the given number

Select Floor ( -7.09) from dual,---8select floor (7.09) from dual;--7

5, mod (A, B) function

Purpose: Returns the remainder of a divided by B

Select mod (2,3) from Dual;--2select mod ( -2,3) from dual;---2

6. Power (A, B) function

Purpose: Returns the B-square function of a

Select Power (2,3) from dual;

7, round (number) function

Purpose: Rounded to take the integer value of the given number

Select round (1.4) from dual;--1

Round (date with hour seconds, parameter)

Parameter: Seconds ss, accurate to seconds.
Parameters: mi, to minutes, seconds 30 is the dividing line, equivalent to rounding 5, if the second is more than 30, go forward for 1 minutes
Parameter: When HH, if more than 30 minutes, go forward 1 hours
Parameter: DD, if the hour is more than 12, go forward 1 hours
Parameter: Month mm,16 number is the critical, equivalent to rounding 5, if more than 16th, forward 1 days
parameter: Year yy, more than July, forward 1 years
parameter: Day, Wednesday is the dividing line is more than Wednesday that is the date of the week of the next Sunday, Less than equals shows the Sunday

of the week in which it was

Select round (to_date (' 2014-06-30 ', ' yyyy-mm-dd '), ' yyyy ') Nian from Dual;--2014/1/1select round (to_date (' 2014-07-01 '), ' Yyyy-mm-dd '), ' yyyy ') Nian from Dual;--2015/1/1select round (to_date (' 2014-06-16 ', ' yyyy-mm-dd '), ' mm ') Yue from dual;-- 2014/7/1select round (to_date (' 2014-06-15 ', ' yyyy-mm-dd '), ' mm ') Yue from Dual;--2014/6/1select round (to_date (' 2014-07-15 12:00:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' DD ') Ri from Dual;--2014/7/16select round (to_date (' 2014-07-15 11:59:59 ', ' yyyy-mm-dd hh24:mi:ss '), ' DD ') Ri from Dual;--2014/7/15select round (to_date (' 2014-07-30 10:30:01 ', ' Yyyy-mm-dd hh24:mi:ss '), ' hh ') Shi from Dual;--2014/7/30 11:00:00select round (to_date (' 2014-07-30 10:29:59 ', ' Yyyy-mm-dd hh24:mi:ss '), ' hh ') Shi from Dual;--2014/7/30 10:00:00select round (to_date (' 2014-07-15 13:30:01 ', ' Yyyy-mm-dd hh24:mi:ss '), ' mi ') fen from DUAL;--2014/7/15 13:30:00select round (to_date (' 2014-07-15 14:30:31 ', ' Yyyy-mm-dd hh24:mi:ss '), ' mi ') fen from DUAL;--2014/7/15 1:31:00select sysdate,round (sysdate-1, ' Day '), round (sYsdate, ' Day '), round (sysdate+1, "Day") from dual; 

8. add_months function

Purpose: Add or subtract a specified month

Select Add_months (to_date (' 2014-7-30 ', ' yyyy-mm-dd '), 2) from Dual;--2014/9/30select add_months (to_date (' 2014-7-30 '), ' Yyyy-mm-dd '), -2) from Dual;--2014/5/30

9. Last_day function

Purpose: Returns the last day of the date

Select To_char (Last_day (sysdate-21), ' yyyy.mm.dd ') from dual;--2014.07.31

10, Months_between (date2,date1) function

Purpose: Returns the month difference of date2-date1

Select Months_between (' 1 June-September-2014 ', ' 1 June-July -2014 ') Mon_between from Dual;--2select months_between (' January-September-2014 ', ' 1 May-July -2014 ') Mon_between from Dual;--1.54838709677419select months_between (to_date (' 2014-07-01 ', ' yyyy-mm-dd '), to_ Date (' 2014-09-01 ', ' yyyy-mm-dd ')) Mon_between from Dual;--2

11, Next_day (date,x) function

Purpose: Date given date and week x after one week X

Select Next_day (Sysdate,to_char (sysdate, ' Day ") Next_day from DUAL;--2014/8/6 11:09:55select to_char (sysdate, ' Day ') From dual;--Wednesday

12. Sysdate function

Purpose: Used to give the current system time (note is the time of the system, if the remote call to the database on the server, the database is the server time)

Select Sysdate from Dual;--2014/7/30 11:33:49select systimestamp from dual;--30-7 month-14 11.33.56.450391 morning +08:00