Functions in sql

SELECT 23 DIV 6 ;

SELECT 23 / 6 ;

SELECT 23 - 6 ;

SELECT 23 + 6 ;

SELECT 23 * 6 AS `multiplication_result`;

Modulo operator (-)

The modulo operator divides N by M and gives us the remainder. Let's now look at an example of a modulo operator. We will use the same values ​​as in the previous examples.

SELECT 23 % 6 ;

SELECT 23 MOD 6 ;

Executing the above script gives us 5

Let's now look at some of the common numeric functions in MySQL.

Floor - This function removes decimal places from the number and rounds to the nearest smallest 

umber. The script below demonstrates its use.

SELECT FLOOR(23 / 6) AS `floor_result`;

Executing the above script gives us the following results.

Round - This function rounds a number with decimal places to the nearest whole number. The script below demonstrates its use.

SELECT ROUND(23 / 6) AS `round_result`;

Executing the above script gives us the following results.

Rand - This function is used to generate a random number whose value changes every time the function is called. The script below demonstrates its use.

SELECT RAND() AS `random_result`;

Stored Functions

Stored functions are similar to built-in functions, except that you must define the stored function yourself. Once a stored function has been created, it can be used in SQL statements like any other function. The basic syntax to create a stored function is as below

CREATE FUNCTION sf_name([parameter(s)])
   RETURNS data type
   DETERMINISTIC
   STATEMENTS

HERE

• "CREATE FUNCTION sf_name ([parameter(s)])" is required and tells the MySQL server to create a function named "sf_name" with optional parameters defined in parentheses.
• "RETURNS data type" is required and specifies the type of data the function should return.
• "DETERMINISTIC" means that the function will return the same values ​​if given the same arguments.
• "STATES" is the procedural code that executes the function.

Let's now look at a practical example that implements a built-in function. Suppose we want to know which rented movies are overdue. We can create a stored function that takes a return date as a parameter and then compares it to the current date on the MySQL server. If the current date is less than the movie's return date, we return "No", otherwise we return "Yes". The script shown below helps us achieve this.

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date(return_date DATE)
  RETURNS VARCHAR(3)
   DETERMINISTIC
    BEGIN
     DECLARE sf_value VARCHAR(3);
        IF curdate() > return_date
            THEN SET sf_value = 'Yes';
        ELSEIF curdate() <= return_date
            THEN SET sf_value = 'No';
        END IF;
     RETURN sf_value;
    END |

When the above script was executed, the stored function `sf_past_movie_return_date` was created.

Let's now check our stored function.

SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;

Running the above script in MySQL Workbench against myflixdb gives us the following results.

Custom Functions

MySQL also supports user-defined functions that extend MySQL. User-defined functions are functions that can be created with a programming language like C, C++, etc. and then added to the MySQL server. Once added, they can be used like any other function.

Summary

• Functions allow us to extend the capabilities of MySQL.
• Functions always return a value and can optionally take parameters.
• Built-in functions are functions that come with MySQL. They can be categorized according to the types of data they work with, i.e. strings, date, and numeric built-in functions.
• Stored functions are created by the user in the MySQL server and can be used in SQL statements.
• User defined functions are created outside of MySQL and can be included in the MySQL server.