Difference between stored procedure and function in MySQL

Overview of MySQL

MySQL is an open-source relational database management system. It can easily manage large volumes of data. For more details on MySQL, refer to MySQL Interview Questions.

MySQL is one of the most popular free databases and is backed by Oracle. It is easy to use, flexible,and fast. In this blog, we will compare custom function and stored procedure and understand how to write a simple one.

Stored procedures

Instead of sending multiple SQL statements to the database server, an application can send a set of queries in what is called a stored procedure. Stored procedures are reusable and can be used by different applications to improve performance.

Functions

There are many types of functions like aggregate functions, control flow functions, string functions, comparison, date and time, Math functions,and so on. Each of these functions performs a specific task and return a result. Refer to SQL Interview Questions to learn about different types of functions,including user-defined and stored functions in detail.

Now that we know the basics of stored procedure and function let us do a side by side comparison of both –

Though MySQL is efficient in handling large amounts of data, there are now NoSQL databases that handle unstructured data in a better manner. Databases like MongoDB are faster when it comes to dealing with huge databases. To know more features and advantages of MongoDB, check out our MongoDB Interview Questionslist. However, MySQL helps with high availability and redundancy of data.

How to create a stored procedure

Let us create a simple stored procedure that gets a countof students who passed with distinction from students table.

CREATE PROCEDURE GetNumberOfDistinctions(IN mintmarksINT, OUT count INT)

BEGIN

SELECT count(student_id) INTO countFROM students WHERE marks >=mintmarks;

END

In this procedure, we have used one in parameter and one out parameter. When we call the procedure, we get the resultant count.

CALL GetNumberOfDistinctions(75, @count);

SELECT @count;

How to create a custom function

Let us now create a custom function for the same purpose as above.

Notice the difference in the syntax.

CREATE FUNCTION get_number_of_distinctions(minmarks INT) RETURNS INT

BEGIN

DECLARE count INT DEFAULT 0;

SELECT count(student_id) INTO count FROM students WHERE marks >= minmarks;

RETURN count;

END;

When you call the function, it returns the value of count.

int count = get_number_of_distinctions(75);

Conclusion

Stored procedures and functions in MySQL have a totally different purpose. Functions are smaller tasks whereas procedure requires more accuracy and time and solves more complex business logic.

We have a collections of Best Interview Questions and Answers that helps you to crack your future interviews