Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Numeric Functions
Oracle Tips by Burleson
 

Most numeric functions can take either a number or a double and will, in most cases return the same data type.  I'm not going to provide examples for each numeric function.  If you need them, it's enough to know they're there.

ABS:  Return the Absolute Value.

* Parameter 1 = NUMBER
* Returns = NUMBER

CEIL:  Return the CEILING:

* Parameter 1 = NUMBER
* Returns = NUMBER

FLOOR:  Return the FLOOR:

* Parameter 1 = NUMBER
* Returns = NUMBER

DEGREES:  Convert Radian to Degrees:

* Parameter 1 = NUMBER
* Returns = NUMBER

EXP:  Return the exponential:

* Parameter 1 = NUMBER
* Returns = NUMBER

LN:  Return the Natural Logarithm:

* Parameter 1 = NUMBER
* Returns = NUMBER

LOG:  Return the Base 10 Logarithm:

* Parameter 1 = NUMBER
* Returns = NUMBER

LOG:  Return the Logarithm to the base:

* Parameter 1 = NUMBER, base
* Parameter 2 = NUMBER
* Returns = NUMBER

MOD:  Return the Modulus of two numbers:

* Parameter 1 = NUMBER
* Parameter 2 = NUMBER
* Returns = NUMBER

PI:  Returns PI:

* Returns = NUMBER

POWER:  Return a number raised to a power:

* Parameter 1 = NUMBER
* Parameter 2 = NUMBER, Power
* Returns = NUMBER

RADIANS:  Convert Degrees to Radian:

* Parameter 1 = NUMBER
* Returns = NUMBER

RANDOM:  Return a random number:

* Returns = NUMBER

SETSEED:  Sets a seed for calls to Random:

* Parameter 1 = NUMBER
* Returns = NUMBER

ROUND:  Round a number to nearest integer or to number of decimals:

* Parameter 1 = NUMBER
* Parameter 2 = NUMBER, Optional, Round to integer if NULL
* Returns = NUMBER

SIGN:  Return the sign of a number:

* Parameter 1 = NUMBER
* Returns = NUMBER (+1, -1 or 0)

SQRT:  Return the Square Root of a number::

* Parameter 1 = NUMBER
* Returns = NUMBER

ACOS:  Return the Inverse Cosine:

* Parameter 1 = NUMBER
* Returns = NUMBER

ASIN:  Return the Inverse Sine:

* Parameter 1 = NUMBER
* Returns = NUMBER

ATAN:  Return the Inverse Tangent:

* Parameter 1 = NUMBER
* Returns = NUMBER

ATAN2:  Return the Inverse with input:

* Parameter 1 = NUMBER
* Parameter 2 = NUMBER
* Returns = NUMBER ATAN(Parameter 1 / Parameter 2)

COS:  Return the Cosine:

* Parameter 1 = NUMBER
* Returns = NUMBER

COT:  Return the Cotangent:

* Parameter 1 = NUMBER
* Returns = NUMBER

SIN:  Return the Sine:

* Parameter 1 = NUMBER
* Returns = NUMBER

TAN:  Return the Tangent:

* Parameter 1 = NUMBER
* Returns = NUMBER

Aggregate Functions

AVG:  Return the average value of a column (NULLs are excluded):

* Parameter 1 = Numeric Column or Expression
* Returns = NUMBER

COUNT:  Return the number of rows (NULLS are excluded):

* Parameter 1 = Numeric Column.Text or Expression. Can use * as the expression in which case nulls will be counted.  In all other cases, NULL will be excluded.
* Returns = NUMBER

MAX:  Return the maximum value in the column:

* Parameter 1 = Numeric Column , Text or Expression
* Returns = NUMBER

MIN:  Return the minimum value in the column:

* Parameter 1 = Numeric Column, Text or Expression
* Returns = NUMBER

SUM:  Return the sum of the supplied column for all rows:

* Parameter 1 = Numeric Column or Expression
* Returns = NUMBER

STDDEV:  Return the standard deviation of the result set:

* Parameter 1 = Numeric Column or Expression
* Returns = NUMBER

VARIANCE:  Return the variance of the result set:

* Parameter 1 = Numeric Column or Expression
* Returns = NUMBER

SELECT AVG(in_val) avg_val,
       COUNT(in_val) cnt_val,
       COUNT(*) cnt_all,
       MAX(in_val) max_val,
       MIN(in_val) min_val,
       SUM(in_val) sum_val,
       STDDEV(in_val) stdev_val,
       VARIANCE(in_val) var_val
  FROM (
    SELECT 1 in_val
    UNION
    SELECT 2
    UNION
    SELECT NULL
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8 ) AS data_input; 

edb=# SELECT AVG(in_val) avg_val,
edb-#        COUNT(in_val) cnt_val,
edb-#        COUNT(*) cnt_all,
edb-#        MAX(in_val) max_val,
edb-#        MIN(in_val) min_val,
edb-#        SUM(in_val) sum_val,
edb-#        STDDEV(in_val) stdev_val,
edb-#        VARIANCE(in_val) var_val
edb-#   FROM (
edb(#     SELECT 1 in_val
edb(#     UNION
edb(#     SELECT 2
edb(#     UNION
edb(#     SELECT NULL
edb(#     UNION
edb(#     SELECT 3
edb(#     UNION
edb(#     SELECT 4
edb(#     UNION
edb(#     SELECT 5
edb(#     UNION
edb(#     SELECT 6
edb(#     UNION
edb(#     SELECT 7
edb(#     UNION
edb(#     SELECT 8 ) AS data_input;avg_val       | cnt_val | cnt_all | max_val | min_val | sum_val |     stdev_val      |      var_val
--------------------+---------+---------+---------+---------+---------+---------- 4.5000000000000000 |       8 |       9 |       8 |       1 |      36 | 2.4494897427831781 | 6.00000000000

(1 row)

             
This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter