How To Call Function In Mysql

In MySQL, functions are procedures that accept parameters, perform some actions, and then return a value. You can use functions in SQL statements, such as SELECT, WHERE, and HAVING clauses. In this tutorial, we will learn how to call a function in MySQL.

Step 1: Create a database and a table

DROP DATABASE IF EXISTS my_company;

CREATE DATABASE my_company;

USE my_company;

CREATE TABLE my_company.employees (

id INT PRIMARY KEY,

NAME VARCHAR(50),

salary DECIMAL(10,2)

);

Step 2: Create a function

First, you need to create a function. To create a function, you can use the CREATE FUNCTION statement. Let’s create a simple function that returns the sum of two numbers:

DELIMITER $$

CREATE FUNCTION my_sum (a INT, b INT)

RETURNS INT

BEGIN

RETURN a + b;

END $$

DELIMITER ;

This function takes two integers as input parameters and returns their sum.

Step 3: Call the function

To call a function, you can use the SELECT statement. Let’s call the sum() function we just created:

1	SELECT sum(2, 3);

This will return the value 5.

You can also use a function in a WHERE or HAVING clause. For example, let’s find all the employees whose salary is greater than the average salary:

1 2	SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

In this query, we are using the AVG() function to calculate the average salary of all employees, and then compare it with the salary of each employee.

Conclusion

Functions are a powerful feature of MySQL that can help you perform complex calculations and data manipulations. By following these simple steps, you can create and call your own MySQL functions in no time.

Full Code