FB

Do you need to use sine, cosine, or tangent functions in your queries or database code?

Oracle has plenty of those functions you can use.

Learn how to use them and see some examples in this article.

What Are The Mathematical Functions?

The mathematical functions are those that calculate the sine, cosine, and tangent of other numbers. There are also some other similar functions, that calculate the “arc cosine” and other concepts that I barely remember from high school maths.Oracle Mathematical Functions

The functions in this article are:

  • SIN: Calculate the sine of a number. The sine is the ratio of the length of the side of the triangle opposite the angle to the length of the triangle’s hypotenuse.
  • COSIN: Calculate the cosine of a number, which is the ratio of the side adjacent to the acute angle to the hypotenuse.
  • TAN: Calculate the tangent of a number, which is the ratio of the side opposite the acute angle to the side adjacent to the angle.
  • SINH: Calculate the hyperbolic sine of a number
  • COSH: Calculate the hyperbolic cosine of a number
  • TANH: Calculate the hyperbolic tangent of a number
  • ASIN: Calculate the arc sine of a number
  • ACOS: Calculate the arc cosine of a number
  • ATAN: Calculate the arc tangent of a number
  • ATAN2: Calculate the arc tangent of two numbers
  • LN: Calculate the natural logarithm of a number.
  • EXP: Calculate the the value of e raised to a specified power. The value of e is a mathematical constant, and it is roughly equal to 2.71828183.

If you’re not sure what a hyperbolic or arc sine, cosine, or tangent is, you can find out here.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

 

Syntax and Parameters

The syntax of most of these functions are the same:

SIN (number)
COS (number)
TAN (number)
SINH (number)
COSH (number)
TANH (number)
ASIN (number)
ACOS (number)
ATAN (number)
LN (number)
EXP (number)

The parameters of the SIN and similar functions are function are:

  • number (mandatory): The number used to calculate the return value (e.g. sine or arc tangent, depending on the function).

However, the ATAN2 syntax is slightly different:

ATAN2 ( number1 [/|,] number2 )

For the ATAN2 function, it can be written as either number1,number2 or number1/number2. Both formats produce the same results.

For ATAN2, the parameters are:

  • number1 (mandatory): The first number to use in the arc tangent calculation.
  • number2 (mandatory): The second number to use in the arc tangent calculation.

There are some things to note about the parameters for all of these functions:

  • The number parameters can be any numeric data type. If the data type is a BINARY_FLOAT, the return type is a BINARY_DOUBLE. Otherwise, the return type is the same as the type of the parameter.
  • For ASIN, ACOS, and ATAN, the function returns the same data type as the input data type (unless the input is a BINARY_FLOAT, then the function returns a BINARY_DOUBLE).
  • For ATAN2, the function returns NUMBER (unless either of the inputs is a BINARY_FLOAT, then the function returns a BINARY_DOUBLE).
  • For COSH, the function returns a DECIMAL value.
  • For ASIN and ACOS, the number must be in the range of -1 to 1.

 

Mathematical Function Examples

Here are some examples of these functions.

 

SIN Function Examples

SELECT
SIN(20) AS sin_20,
SIN(1) AS sin_1,
SIN(6) AS sin_6
FROM dual;

Result:

SIN_20SIN_1SIN_6
0.91294525070.8414709848-0.2794154982

 

COS Function Examples

SELECT
COS(3.141) AS cos_31,
COS(1) AS cos_1,
COS(5.2) AS cos_52
FROM dual;

Result:

COS_31COS_1COS_52
-0.99999982440.5403023050.46851667

 

TAN Function Examples

SELECT
TAN(4) AS tan_4,
TAN(19) AS tan_19,
TAN(1.2) AS tan12
FROM dual;

Result:

TAN_4TAN_19TAN_12
1.1578212820.15158947062.572151622

 

SINH Function Examples

SELECT
SINH(1) AS sinh_1,
SINH(0) AS sinh_0,
SINH(4.3) AS sinh_43
FROM dual;

Result:

SINH_1SINH_0SINH_43
1.175201194036.84311257

 

COSH Function Examples

SELECT
COSH(1) AS cosh_1,
COSH(0) AS cosh_0,
COSH(15.8) AS cosh_158
FROM dual;

Result:

COSH_1COSH_0COSH_158
1.54308063513637665.979

 

TANH Function Examples

SELECT
TANH(1) AS tanh_1,
TANH(0) AS tanh_0,
TANH(9.1) AS tanh_91
FROM dual;

Result:

TANH_1TANH_0TANH_91
0.76159415600.9999999751

 

ASIN Function Example

SELECT
ASIN(-0.4) AS asin_value
FROM dual;

Result:

ASIN_VALUE
-0.4115168461

 

ACOS Function Example

SELECT
ACOS(0.31) AS acos_value
FROM dual;

Result:

ACOS_VALUE
1.255603294

 

ATAN Function Example

SELECT
ATAN(1.1) AS atan_value
FROM dual;

Result:

ATAN_VALUE
0.8329812667

 

ATAN2 Function Example

SELECT
ATAN2(3, 1.6) AS atan2_value
FROM dual;

Result:

ATAN2_VALUE
1.080839001

 

LN Function Examples

SELECT
LN(10) AS ln_10,
LN(50) AS ln_50,
LN(2.1) AS ln_2_1,
LN(102.9) AS ln_102_9
FROM dual;

Results:

LN_10LN_50LN_2_1LN_102_9
2.3025850933.9120230050.74193734474.633757643

 

EXP Function Examples

SELECT EXP(1) AS exp_1,
EXP(2) AS exp_2,
EXP(5) AS exp_5,
EXP(4.2) AS exp_42,
EXP(-1) AS exp_m1
FROM dual;

Results:

EXP_1EXP_2EXP_5EXP_42EXP_M1
2.71828182847.38905609893065022148.413159102566.686331040.3678794411714

 

More Information

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Get Your SQL Cheat Sheets Now: