Tech Honey – Page 40 – The #1 Website for Oracle PL/SQL, OA Framework and HTML

Oracle PL/SQL ROUND Function

November 5, 2012 by techhoneyadmin

Oracle 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.

POWER Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

The POWER function in Oracle SQL / PLSQL is used to get ‘m’ raised to the power of ‘n’.

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

SELECT POWER(m,n)
FROM table_name;

M is the number to be raised or base
N is the number up to which base is to be raised or exponent

Example 1:

`1`	`SELECT` `POWER(2,2)`

`2`	`FROM` `DUAL;`

Will return “4” because 2*2 = 4

Example 2:

`1`	`SELECT` `POWER(2,3)`

`2`	`FROM` `DUAL;`

Will return “8” because 2*2*2 = 8

Example 3:

`1`	`SELECT` `POWER(5,3)`

`2`	`FROM` `DUAL;`

Will return 125 as 5*5*5 = 125

Example 4:

`1`	`SELECT` `POWER(5,-3)`

`2`	`FROM` `DUAL;`

Will return 0.008 as 1/(5*5*5) = 0.008

SQRT Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

The SQRT function in Oracle SQL / PLSQL is used to get the square root of a number.

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

SELECT SQRT(n)
FROM table_name;

Here ‘n’ is a positive number whose square root is to be determined.

Example 1:

`1`	`SELECT` `SQRT(9)`

`2`	`FROM` `DUAL;`

Will return “3” because 3*3 = 9

Example 2:

`1`	`SELECT` `SQRT(16)`

`2`	`FROM` `DUAL;`

Will return “4” because 4*4 = 16

Example 3:

`1`	`SELECT` `SQRT(2)`

`2`	`FROM` `DUAL;`

Will return “1.4142135623731” because 1.4142135623731 * 1.4142135623731 = 2

Example 4:

`1`	`SELECT` `SQRT(45)`

`2`	`FROM` `DUAL;`

Will return “6.70820393249937” as 6.70820393249937 * 6.70820393249937 = 45

EXP Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

Oracle SQL /PLSQL uses the EXP (exponential) function to return ‘e’ raised to the n^th power, where e = 2.71828182845905.

Oracle SQL / PLSQL syntax for the EXP function is:

SELECT EXP(n)
FROM table_name;

Here ‘n’ is the power to raise e up to.

Example 1: Using Oracle SQL EXP Function with SQL SELECT Statement

`1`	`SELECT` `EXP(1)`

`2`	`FROM` `DUAL;`

The above SQL SELECT example of EXP() Function will return “2.71828182845905” as e*1 = 2.71828182845905

Example 2: Using Oracle SQL EXP Function with SQL SELECT Statement

`1`	`SELECT` `EXP(2)`

`2`	`FROM` `DUAL;`

The above SQL SELECT example of EXP() Function will return “7.38905609893065” as e*e = 7.38905609893065

Example 3: Using Oracle SQL EXP Function with SQL SELECT Statement

`1`	`SELECT` `EXP(-2)`

`2`	`FROM` `DUAL;`

The above SQL SELECT example of EXP() Function will return “0.135335283236613” as 1/(e*e) = 0.135335283236613

Example 4: Using Oracle SQL EXP Function with SQL SELECT Statement

`1`	`SELECT` `EXP(0)`

`2`	`FROM` `DUAL;`

The above SQL SELECT example of EXP Function will return “1” as anything raised to 0 is 1

MOD Function in Oracle SQL – PLSQL

November 5, 2012 by techhoneyadmin

The MOD function in Oracle SQL / PLSQL is used to get the remainder of ‘n’ divided by ‘m’.

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

SELECT MOD(n,m)
FROM table_name;

MOD is calculated as n – m * floor(n/m)
MOD function uses floor in the calculation whereas remainder function uses round function in its calculation.
MOD function will return ‘n’ if ‘m’ is ‘0’.

Example 1:

`1`	`SELECT` `MOD(5,2)`

`2`	`FROM` `DUAL;`

Will return “1” because 1 will be remainder if 5 is divided by 2

Example 2:

`1`	`SELECT` `MOD(6,2)`

`2`	`FROM` `DUAL;`

Will return “0” because there is no remainder if 6 is divided by 2

Example 3:

`1`	`SELECT` `MOD(-5,2)`

`2`	`FROM` `DUAL;`

Will return “-1” because -1 will be remainder if -5 is divided by 2

Example 4:

`1`	`SELECT` `MOD(5,0)`

`2`	`FROM` `DUAL;`

Will return “5”

Example 5:

`1`	`SELECT` `MOD(5.5,2)`

`2`	`FROM` `DUAL;`

Will return “1.5” because 1.5 will be remainder if 5.5 is divided by 2