How to Create a Stored Procedure in MySQL

In this article, we will describe how to create a stored procedure in MySQL for your acknowledgment,which may come superbly handy with your advance preparation of MySQL interview questions. MySQL excellently supports stored procedures which are able to execute a set of codes by enforcing relevant business logic.

So, What’s a Stored Procedure?

It can be defined as a sub program present in a regular computing language, stored in a database. It has a name, SQL statements, SQL command logic,and a parameter list. It allows developers to create SQL queries which can be stored and executed on the server. These can also be cached and reused. The prime purpose of MySQL stored procedures is to hide direct SQL queries from the code, thus improving the performance of database operations such as update, delete,and select data.

Let’s Create a Simple MySQL Stored Procedure

Before we start creating a MySQL stored procedure;we would like to repeat that practicing these steps could be extremely helpful with the preparation of many important MySQL interview questions. Here, we will develop a simple stored procedure named - Get All Products()to help you better understand the syntax. This procedure will select all the available products from the product table.

·         At first, launch MySQL client tool and use below-mentioned command.

DELIMITER //  CREATE PROCEDURE Get All Products()    BEGIN    SELECT *  FROM products;    END //  DELIMITER ;

Now, let’s briefly describe the stored procedure mentioned above.

·         The DELIMITER// statement used above is not a part of the stored procedure syntax,andit changes the standard semicolon(;)delimiter to another. Here semicolon is transformed to double slashes. It is required to pass the whole stored procedure to the server rather than each statement at a time due to MySQL tool interpretation.

·         The END keyword here followed by the delimiter // indicates the end of the stored procedure.

·         The last used command DELIMITER ;changes the delimiter again to the semicolon (;).

·         The statement CREATE PROCEDURE will create the new stored procedure, which will be followed by the name of the stored produce. In this case, we have used Get All Products as our stored procedure name, after which we have put the parentheses to continue.

·         The section present in between the BEGIN and END is the body of our stored procedure. We will have to the declarative SQL statements in this body section to handle the business logic. Here, we have used a SELECT statement to query the data from our product table.

As it’s a tedious process to write complex stored procedures in MySQL client tool, almost every GUI tool available for MySQL allows users to create new stored procedures with an intuitive interface.

To get more MySQL interview questions with their best-suggested answers, please browse our website Best Interview Question today, an exclusive portal to support beginners as well as experienced personnel.

Visit - Best Interview Questions