How to use trunc () and round () in oracle

This article shows you how to use trunc () and round () in oracle. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1.round function (rounded)

Description: returns a value that is the result of rounding to the specified number of decimal places

Parameters:

Number: the value to be processed

Decimal_places: rounded to a few decimal places (default is 0)

Select round (123.456, 0) from dual; returns 123select round (123.456, 1) from dual; returns 123.5select round (- 123.456, 2) from dual; returns-123.46

2.ceil and floor functions

The ceil and floor functions are sometimes useful when dealing with business data.

Ceil (n) takes the smallest integer that is greater than or equal to the value n

Floor (n) takes the largest integer that is less than or equal to the value n

Select ceil (1.5) a from dual; returns 2select ceil (- 1.5) a from dual; returns-1select floor (1.5) a from dual; returns 1select floor (- 1.5) a from dual; returns-2

3.trunc function

1) trunc function handles numbers

TRUNC (number [, decimals])

Where:

The value of number to be intercepted

Decimals indicates that the number of digits after the decimal point should be retained. Optional, ignore it and truncate all decimal parts.

Trunc is to deal with the number of digits displayed. If decimals is negative, deal with the integer part. After processing, it will be zero, and-2 will reach ten digits. If the length of the integer part is exceeded, the whole number will be 0.

2) date of processing

The trunc function returns a date value that is partially truncated in the specified meta-element format.

The specific syntax format is as follows:

TRUNC (date, [fmt])

Where:

Date is a required parameter and is a date value entered

The fmt parameter is a negligible date format that is used to truncate the entered date value in the specified element format. Ignore it and it is truncated by the most recent date

The following is the use of this function:

Trunc (sysdate,'yyyy')-returns the first day of the year .trunc (sysdate,'mm')-returns the first day of the month .trunc (sysdate,'d')-returns the first day of the current week. Selecttrunc (sysdate,'YYYY') from dual;selecttrunc (sysdate,'MM') from dual;selecttrunc (sysdate,'D') from dual; the above is how trunc () and round () are used in oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.