Oracle common single-line functions

Numeric functions:

1.round: Returns the value after rounding

Round (number, parameter) parameter does not write default to 0

The following parameter specifies the precision of the return value, or the start of rounding from the beginning of the decimal number, or rounding the integer by default if no subsequent arguments are taken. Precision Intercept is a decimal point, positive numbers are truncated from left to right and rounded, negative numbers are truncated from right to left and rounded, and the precision value can be decimal.

Select Round (1992.54, 1) from dual;

Select Round (1992.54) from dual;

Select Round (1992.54,-1) from dual;

2.trunc: Returns the truncated value (not rounded)

Trunc (number, parameter) parameter does not write the default is 0, precision interception is also a decimal point, the positive number is from left to right, negative numbers are from right to left, the precision value can be decimal

Select Trunc (1992.92) from dual;

3.mod: Return remainder

MoD (number, parameter) number divided by parameter, parameter cannot be written

Select MoD (2001, 5) from dual;

Date function

Date Action Formula

Date + number = date after n days

Date-number = n days before date

Date-date = number of days between dates

1.sysdate: Get the current time

Select Sysdate from dual;

Select Sysdate + 1 from dual;

Select sysdate-1 from dual;

Select Sysdate-(sysdate-1) from dual;

2.add_months: Returns a date after n months

Add_months (date, parameter) parameter for the added month
Select Add_months (sysdate, 1) from dual;

3.months_between: Returns the difference between two dates in a month

Months_between (date, date)

Select Months_between (Sysdate, (Sysdate +)) from dual;

4.last_day: Returns the last day on which the date is located

Select Last_day (sysdate) from dual;

5.next_day: return date in next week, Day of week X

Next_day (date, parameter) parameter must have and 1 <= parameter <= 7

Select Next_day (sysdate, 1) from dual;

Conversion functions

1.to_char: Converting other formats to string formats

The conversion date is a specified return value: yyyy mm month dd day HH hours mi minutes ss seconds

Select To_char (sysdate, ' yyyy ') from dual;

2.to_date: Convert other formats to date formats

The To_date (number/string, parameter) parameter is the specified date format, and the number and string formats correspond to the specified date format

Select To_date (20171120, ' YyyyMMdd ') from dual;

3.to_number: Converting other formats to number formats

To_num (String)

When using To_number, make sure that the converted fields are convertible to a number.

Select To_number (' 123 ') from dual;

Other functions

1.NVL: null replacement, if NULL is used when the data is passed in , the default value , if not null, uses the original data .

NVL (data, replacement value)

2.decode: Conditional value, set the content will be compared with each comparison content , if the content is the same, then use the display content for output , if not all the same , using the default content output .

Decode ( column | String | value , compare content 1, display content 1, compare content 2, Show content 2,... [, default display ])

Oracle common single-line functions