Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

function

SIGN Function in Oracle SQL – PLSQL

by

The SIGN function in Oracle SQL / PLSQL is used to get a value which indicates the sign of a number.

Syntax for the SIGN function in Oracle SQL / PLSQL is:

SELECT SIGN(N)
FROM table_name;

  • N is the number whose SIGN is to be determined.
  • If N >0 then 1 is returned
  • If N = 0 then 0 is returned
  • If N< 0 then -1 is returned

Example 1:

SELECT SIGN(1.23456)
FROM DUAL;

Will return “1”

Example 2:

SELECT SIGN(0)
FROM DUAL;

Will return “0”

Example 3:

SELECT SIGN(-2.051)
FROM DUAL;

Will return “-1”

FLOOR Function in Oracle SQL – PLSQL

by

The FLOOR function in Oracle SQL / PLSQL is used to get a largest integer number which is less than or equal to the number passed to the function.

Syntax for the FLOOR function in Oracle SQL / PLSQL is:

SELECT FLOOR(N)
FROM table_name;

  • N is the number passed

Example 1:

SELECT FLOOR(1.23456)
FROM DUAL;

Will return “1”

Example 2:

SELECT FLOOR(2.2)
FROM DUAL;

Will return “2”

Example 3:

SELECT FLOOR(2.051)
FROM DUAL;

Will return “2”

Example 4:

SELECT FLOOR(-2.051)
FROM DUAL;

Will return “-3”

CEIL Function in Oracle SQL – PLSQL

by

The CEIL function in Oracle SQL / PLSQL is used to get a smallest integer number which is greater than or equal to the number passed to the function.

Syntax for the CEIL function in Oracle SQL / PLSQL is:

SELECT CEIL(N)
FROM table_name;

  • N is the number passed

Example 1:

SELECT CEIL(1.23456)
FROM DUAL;

Will return “2”

Example 2:

SELECT CEIL(2.2)
FROM DUAL;

Will return “3”

Example 3:

SELECT CEIL(2.051)
FROM DUAL;

Will return “3”

Example 4:

SELECT CEIL(-2.051)
FROM DUAL;

Will return “-2”

TRUNC Function in Oracle SQL – PLSQL

by

The TRUNC function in Oracle SQL / PLSQL is used to get a number truncated to specific number of decimal places.

Syntax for the TRUNC function in Oracle SQL / PLSQL is:

SELECT TRUNC(N,D)
FROM table_name;

  • N is the number to be truncated
  • D is the decimal places up to which N is to be truncated

Example 1:

SELECT TRUNC(1.23456,4)
FROM DUAL;

Will return “1.2345”

Example 2:

SELECT TRUNC(1.23456,3)
FROM DUAL;

Will return “1.234”

Example 3:

SELECT TRUNC(1.23456,2)
FROM DUAL;

Will return “1.23”

Example 4:

SELECT TRUNC(-1.23456,3)
FROM DUAL;

Will return “-1.234”

Example 5:

SELECT TRUNC(1.23456)
FROM DUAL;

Will return “1”

Oracle PL/SQL ROUND Function

by

Oracle ROUND FunctionOracle SQL ROUND Function returns a number rounded to specific number of decimal places. Oracle ROUND Function can also be used with dates to return the nearest day, month or year etc.

Oracle SQL ROUND Function Syntax

SELECT ROUND(N,D)
FROM table_name;

In the above Oracle ROUND Syntax;

  • N is the number to be rounded
  • D is the decimal places up to which N is to be rounded.

Oracle SQL ROUND Decimal – Using Positive Number Example

Oracle ROUND Function rounds a number to specific decimals.

For example, the SQL ROUND Decimal query below rounds the number ‘1.23456’to 4 places of decimal.

SELECT ROUND(1.23456,4) "ROUND DECIMAL"
FROM DUAL;

Above SQL ROUND Decimal query returns “1.2346”.

Note: We have aliased ROUND(1.23456,4) as “ROUND DECIMAL”.

Oracle SQL ROUND Decimal – Using Negative Number Example

Oracle ROUND, rounds even a negative number to specific decimals.

For example, the SQL ROUND Decimal query below rounds the number ‘-1.23456’to 3 places of decimal

SELECT ROUND(-1.23456,3) "Oracle ROUND DECIMAL"
FROM DUAL;

Above SQL ROUND Decimal query returns “-1.235”.

Note: We have aliased ROUND(-1.23456,3) as “Oracle ROUND DECIMAL”.

Oracle SQL ROUND Function – Using Date Example (Rounding to Year)

Oracle ROUND can also be used with dates.

For example, Oracle ROUND Function query below returns ‘1-Jan-2013’ as the nearest rounding to year.

SELECT ROUND(TO_DATE ('22-AUG-12'),'YEAR') "Oracle ROUND Function"
FROM DUAL;

More on Using Oracle PL/SQL ROUND Function With Dates.

Oracle SQL ROUND Function – Using Date Example (Rounding to Month)

For example, Oracle ROUND Function query below returns ‘1-September-2012’ as the nearest rounding to month.

SELECT ROUND(TO_DATE ('22-AUG-12'),'MONTH') "Oracle ROUND Function"
FROM DUAL;

More on Using Oracle PL/SQL ROUND Function With Dates.