# Numeric Functions

Numeric functions perform operations on numeric data types such as INT and FLOAT.

9.2.1. ABS

` number1 =ABS (number2 )`

ABS returns the absolute value of a numberthat is, the magnitude of the value ignoring any minus sign.

``` SET var1=ABS(2.143); 2.143
SET var2=ABS(-10); 10
SET var3=ABS(10); 10
SET var4=ABS(-2.3); 2.3
```

9.2.2. BIN

` binary_number =BIN (decimal_number )`

BIN returns the binary (base 2) representation of an integer value.

``` SET var1=BIN(1); 1
SET var2=BIN(2); 10
SET var3=BIN(3); 11
SET var4=BIN(45); 101101
```

9.2.3. CEILING

` number1 =CEILING (number2 )`

CEILING returns the next integer number that is higher than the input floating-point number.

``` SET var1=CEILING(3.5); 4
SET var2=CEILING(-3.5); -3
```

9.2.4. CONV

` number1=CONV(number2,from_base,to_base)`

CONV converts numbers from one base system to another. Although CONV is, in essence, a numeric function, it may return values that you may need to deal with as strings (e.g., hexadecimal numbers).

The following CONV statements convert the number 45 (base 10) into binary (base 2), hexadecimal (base 16), and octal (base 8):

``` SET var1=CONV(45,10,2); 101101
SET var2=CONV(45,10,16); 2D
SET var3=CONV(45,10,8) ; 55```

These statements convert the number 45 (base 2) into base 10, and converts 45 (base 8) into base 2:

``` SET var4=CONV(101101,2,10); 45
SET var5=CONV(55,8,2); 101101
```

9.2.5. FLOOR

` number1 =FLOOR (number2 )`

FLOOR returns the largest integer value not greater than X.

``` SET var1=FLOOR(3.5); 3
SET var2=FLOOR(-3.5); -4
```

9.2.6. FORMAT

` string =FORMAT (number,decimal_places )`

FORMAT returns a string representation of a number with comma separators at each thousand and with the specified number of decimal places.

``` SET var1=FORMAT(21321.3424,2); 21,321.34
```

9.2.7. HEX

` HexNumber =HEX (DecimalNumber )`

HEX returns the hexadecimal representation of a number.

``` SET var1=HEX(9); 9
SET var2=HEX(11); B
SET var3=HEX(32); 20
```

9.2.8. LEAST

` number1 =LEAST (number , number2  [,..])`

LEAST returns the number in the input series with the smallest numerical value.

``` SET var1=LEAST(32,432,-2,-1.4); -2
```

9.2.9. MOD

` remainder =MOD (numerator ,denominator )`

MOD returns the remainder (modulus) when the first number is divided by the second number.

MOD is particularly handy when you want something to happen at regular intervals in a loop. For instance, Example 9-8 purges (deletes) rows from the LOG_ARCHIVE table based on some criteria. As we discuss in Chapter 22, reducing commit frequency is an important optimization for transactional storage engines such as InnoDB. However, we do want to commit at regular intervals; otherwise, we risk losing all the work if the program fails midway through execution.

So Example 9-8 calculates the modulus of the delete count divided by 100. If this modulus is 0which happens every 100 rowsa COMMIT is issued. The end result is that the program commits the delete operations every 100 rows.

Example 9-8. Using the MOD function to perform periodic COMMITs

 ```CREATE PROCEDURE bulk_processing_example( ) MODIFIES SQL DATA BEGIN DECLARE delete_count INT DEFAULT 0; DECLARE last_row INT DEFAULT 0; DECLARE l_rec_id INT; DECLARE c1 CURSOR FOR SELECT rec_id FROM log_archive; DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row=1; OPEN c1; MainLoop: LOOP FETCH c1 INTO l_rec_id; IF last_row THEN LEAVE MainLoop; END IF; IF purge_due(l_rec_id) THEN DELETE FROM log_archive WHERE rec_id=l_rec_id; SET delete_count=delete_count+1; IF MOD(delete_count,100)=0 THEN COMMIT; END IF; END IF; END LOOP MainLoop; CLOSE c1; END;```

You can also calculate a modulus using numerator%denominator or numerator MOD denominator. Thus, these three assignments are all equivalent:

``` SET var1=MOD(5,3); 2
SET var2=5%3; 2
SET var3=5 MOD 3 ; 2
```

9.2.10. POWER

` result =POWER (number ,power )`

POWER returns the result of raising the first number to the power of the second number. You can use POW as a synonym for POWER.

``` SET var1=POWER(3,2); 9 (3*3)
SET var2=POWER(2,3); 8 (2*2*2)
SET var3=POWER(4,.5); 2 (square root of 4)
SET var4=POWER(10,-2); 0.01
SET var5=POWER(10,-3); 0.001
SET var6=POW(2,2); 4
```

9.2.11. RAND

` number=RAND([seed])`

RAND returns a random floating-point number between 0 and 1. If seed is specified, it is used to initialize the random-number generator, which lets you avoid generating repeatable sequences.

``` SET var1=RAND( ); 0.86494333191304
SET var2=RAND( ); 0.96148952838172
SET var3=RAND(5); 0.40613597483014
SET var4=RAND( ); 0.21261767690314
SET var5=RAND(5) ; 0.40613597483014
SET var6=RAND( ); 0.17861983010417```

RAND can be used within stored programs to generate or select random table data. For instance, in Example 9-9, we use the RAND function to randomly select the employee of the week (and you thought we based it on performance!). We first find the maximum employee_id and then generate a random number between 1 and that number. Since RAND returns a floating-point number between 0 and 1, we multiply that number by the maximum employee number, generating a number between 0 and the maximum employee number. Next, we use FLOOR to convert the number to an integer value, and then add 1 to avoid generating an employee_id of 0.

Example 9-9. Using the RAND function to retrieve random rows

 ```CREATE PROCEDURE select_winner( ) READS SQL DATA BEGIN DECLARE winner_id INT; DECLARE max_employee_id INT; DECLARE winner_name VARCHAR(70); SELECT MAX(employee_id) INTO max_employee_id FROM employees; SET winner_id=FLOOR(RAND( )*max_employee_id)+1; SELECT CONCAT_WS(' ','Employee of the week is',firstname,surname) FROM employees WHERE employee_id=winner_id; END; ```

9.2.12. ROUND

` integer =ROUND (number  [,decimals ])`

ROUND converts a floating-point number to the nearest integer value orif the second argument is specifiedto the specified number of decimal points.

``` SET var1=PI( ); 3.141593
SET var2=ROUND(PI( )); 3
SET var3=ROUND(PI( ),4); 3.1416
SET var5=ROUND(4.49); 4
SET var6=ROUND(4.51); 5
```

9.2.13. SIGN

` number1 =SIGN (number2 )`

SIGN returns -1 if a number is less than 0, 0 if the number is 0, and 1 if the number is greater than 0.

``` SET var1=SIGN(-5); -1
SET var2=SIGN(0); 0
SET var3=SIGN(5); 1
```

9.2.14. SQRT

` number1 =SQRT (number2 )`

SQRT returns the square root of a number. It is equivalent to POWER(number,.5).

``` SET var1=SQRT(4); 2
SET var2=SQRT(64); 8
SET var3=POWER(64,.5); 8
```

9.2.15. Other Numeric Functions

Table 9-2 lists additional numeric functions. These functions are rarely used in mainstream MySQL applications; in this category are the trigonometric and logarithmic functions that you probably studied in high school and have never used since!

Function

Syntax

Description

ACOS

number1=ACOS(number2)

Arc cosine of a number.

ASIN

number1=ASIN(number2)

Arc sine of a number.

ATAN

number1=ATAN(number2)

Arc tangent of a number.

COT

number1=COT(number2)

Cotangent of a number.

CRC32

number=CRC32(string)

Cyclic redundancy check value for a string.

DEGREES

EXP

number1=EXP(number2)

Natural logarithm (base e) to the power of a number.

LN

number1=LN(number2)

Natural logarithm of a number.

LOG

number1=LOG(number2,base)

Logarithm of a number in the base specified.

LOG10

number=LOG10(number2)

Base 10 logarithm of a number.

LOG2

number1=LOG2(number)

Base 2 logarithm of a number.

PI

number=PI( )

Returns the value of PI.

SIN

number1=SIN(number2)

Sine of a number (expressed in radians).

TAN

number1=TAN(number2)

Tangent of a number expressed in radians. MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208 