Common oracle functions (2): oracle Functions

Common oracle functions (2): oracle Functions

1. TRIM ([{LEADING | TRAILING | BOTH} [trim_character] | trim_character} FROM] trim_source) Function
Parameter: If leading is specified, the oracle database removes any starting characters equal to trim_character.
Parameter: If the traling parameter is specified for trailing, oracle removes any trailing characters equal to trim_character.
Parameter: If the both parameter is specified or none of the three parameters are specified, oracle removes any starting and ending characters equal to trim_character.
Parameter: If the trim_character parameter is not specified, the space is removed by default.
Parameter: If trim_source only specifies the trim source (trim_source), oracle removes spaces at the beginning and end of trim_source.
Note: The a and trim functions return a value of the varchar2 type. The maximum length of this value is equal to the length of trim_source.
B. If either trim_source or trim_character is 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 returned value is the same as that of trim_source.

select trim(leading 'B' from 'BBoat is good') "test_trim" from dual;--oat is goodselect trim(trailing 'd' from 'BBoat is good') "test_trim" from dual;--BBoat is gooselect trim(both 'd' from 'd--Boat is handsome--d') from dual t;----Boat is handsome--select trim('d' from 'd--Boat is handsome--d') from dual t;----Boat is handsome--select length(trim(leading from ' d--Boat is handsome--d ')) from dual t;--23select length(trim(' d--Boat is handsome--d ')) from dual t;--22select trim(trailing null  from 'boat ') nulltrim from dual;select trim(both 'b'  from null) nulltrim from dual;

2. abs Functions

Purpose: return the absolute value of a specified value.

select abs(100),abs(-100) from dual;--100 100select abs(null) from dual t;select abs('') from dual t;

3. ceil Functions

Purpose: return 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 Functions

Purpose: return the maximum 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) Functions

Purpose: return the remainder of a divided by B.

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

6. power (a, B) Functions

Purpose: return the power B function of.

select power(2,3) from dual;

7. round (number) Function

Purpose: returns the integer of a given number in rounding mode.

select round(1.4) from dual;--1

Round (date with hour/minute/second, parameter)

Parameter: SS in seconds, accurate to seconds.
Parameter: minute MI, to minute, 30 seconds is the dividing line, equivalent to rounding 5, if the second exceeds 30, 1 minute forward
Parameter: hour HH. If the time is more than 30 minutes, it will be 1 hour forward.
Parameter: DD. If the hour is more than 12, it is one hour forward.
Parameter: MM, 16 is critical, equivalent to rounding 5, if more than 16, forward for 1 day
Parameter: Year YY, more than July, 1 year forward
Parameter: day. Wednesday indicates the next Sunday of the week where the date is located. If the value is less than or equal to the value, the Sunday of the week is displayed.

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: return 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: return the month difference for the date2-date1

Select months_between ('16-September-2014 ', '16-July-2014') mon_between from dual; -- 2 select months_between ('01-September-100 ', '15-March-2014 ') mon_between from dual; -- 1.54838709677419 select months_between (to_date ('2017-07-01', 'yyyy-mm-dd '), to_date ('2017-09-01 ', 'yyyy-mm-dd') mon_between from dual; -- 2

11. NEXT_DAY (date, x) Functions

Purpose: Calculate the date of the next week after the given date and week x

Select next_day (sysdate, to_char (sysdate, 'day') next_day from dual; -- 2014/8/6 11: 09: 55 select to_char (sysdate, 'day') from dual; -- Wednesday

12. SYSDATE Functions

Purpose: it is used to give the current system time (note the system time. If you remotely call the database on the server, it is the time on the server where the database is located)

Select sysdate from dual; -- 2014/7/30 11: 33: 49 select distinct imestamp from dual; -- 30-7 months-14 11.33.56.20.391 AM +

 

What are common oracle functions?

It usually depends on your usage.

Commonly used date processing functions (month_between, add_months, next_day, extract ...)
Conversion Function (to_number, to_char, to_date)
Character Processing functions (substr, replace, trim, upper, lower, concat, instr ...)
Mathematical functions (I have never used them, so rondom and trunc cannot be provided)
Logic Functions (coalesce, nvl ..)
Aggregate functions (sum, avg, max, min)

What are common Oracle functions?

Too many values include sum (), max (), min (), count (), decode (), case, avg (), to_date (), and Trunc, this can be learned only when used. These are basic things. When you use them, pay attention to accumulation and remember them.