Oracle frequently uses functions (2)

1. TRIM ([{{leading | TRAILING | BOTH}[Trim_character]| Trim_character} from]trim_source) function
Reference: Leading assuming that the leading parameter is specified, the Oracle database will remove the opening character regardless of what is equal to Trim_character.

Reference: Trailing assuming that traling is specified, Oracle will remove the trailing character whatever equals trim_character.
Reference: Both assumes that the both parameter is specified or that three parameters are not specified. Oracle will remove the start and end characters regardless of what equals trim_character.

Number of references: Trim_character assumes that no trim_character parameters are specified. The default stripped value is a space.
Number of references: Trim_source assuming that only the clipping source (trim_source) is specified, Oracle will strip the beginning and end 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, assuming that Trim_source and trim_character have one null. The TRIM function returns NULL.

C, Trim_character, and Trim_source can all be a random type of data: 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, number of references)

Parameters: Seconds ss, accurate to seconds.
Number of references: mi, Min. The number of seconds 30 is the dividing line, equivalent to rounding 5, assuming that the second is more than 30, moving forward for 1 minutes
Number of parameters: hh, assuming more than 30 minutes, 1 hours to move forward
Number of days DD, assuming that the hour is more than 12 points, moving forward for 1 hours
Number of months: mm,16 is critical, equivalent to rounding 5, assuming more than 16th, moving forward for 1 days
Number of references: Year yy. More than July. 1 years on the march
Number of references: day. Wednesday is the next Sunday of the week in which the dividing line is greater than Wednesday, which shows the date. Less than equals displays the Sunday of the week

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 (The 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: The time used to give the current system (note the time of the system, assuming that the database on the server is remotely called, the time of the server in which the database resides)

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

Oracle frequently uses functions (2)