How to create a Stored procedure in MySQL workbench.

In this article we discuss about How to create a Stored procedure in MySQL workbench.

We first launch the tool and log in with the username and password to create procedures in the MySQL Workbench. Now, we need to do the following steps for creating a stored procedure.

How to create a Stored procedure in MySQL workbench.

1. Go to the Navigation tab and click on the Schema menu where all the previously created databases available. Select your desired database (for example, employeedb). It will pop up the following options.

How to create a Stored procedure in MySQL workbench.

2. Right-click on the Stored Procedure, and we will get the default procedure code. See the below screen

How to create a Stored procedure in MySQL workbench

3.We first launch the tool and log in with the username and password to create procedures in the MySQL Workbench. Now, we need to do the following steps for creating a stored procedure:

How to create a Stored procedure in MySQL workbench

4. After clicking on the Apply button, click on the Finish button for completion.

How to create a Stored procedure in MySQL workbench

5. We can navigate to the schema menu again to verify this newly created procedure. It means first select your database and expand it to display its sub-menu. In the sub-menu, expanding the stored procedure option will show the newly created procedure. See the below image:

How to create a Stored procedure in MySQL workbench

6. We can call the procedure by tapping on the red rectangle box or simply execute the CALL statement.

How to create a Stored procedure in MySQL workbench

How to alter the procedure in MySQL?

MySQL does not allow any command to alter the procedure in MySQL. However, it provides a command that is used to change the characteristics of a stored procedure. This command may alter more than one change in the procedure but does not modify the stored procedure’s parameters or body. If we want to make such changes, we must drop and re-create the procedure using the DROP PROCEDURE and CREATE PROCEDURE statement

ALTER PROCEDURE procedure_name [characteristics ...]  
  
characteristics: {  
    COMMENT 'string'  
  | LANGUAGE SQL  
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  
  | SQL SECURITY { DEFINER | INVOKER }  
}  

Suppose we want to add a comment to the existing procedure. In such a case, we can use the ALTER statement 

mysql> ALTER PROCEDURE get_merit_student  
COMMENT 'It displays all records';  

After executing this statement, we can verify it by using

mysql> SHOW CREATE PROCEDURE get_merit_student \G;  
How to create a Stored procedure in MySQL workbench

we can alter the body of the stored procedure in MySQL using the workbench tool. So open this tool, navigate to the schema menu, and expand the database that contains stored procedures. Now, select your procedure, right-click on it and choose ALTER STORED PROCEDURE option. 

How to create a Stored procedure in MySQL workbench

After clicking this option, we will get a window that contains a procedure code. See the below screen that contains procedure code to display all employee

How to create a Stored procedure in MySQL workbench

Now, we will modify this code. Suppose we want to display only male employees. To do this, we can change this code from the below code and click on the Apply button:

SELECT * FROM employee WHERE gender = 'M';  
How to create a Stored procedure in MySQL workbench

n this window, we will review the procedure code once again, and if no error was found, click on the Apply -> Apply -> Finish button to complete the process.

Drawbacks of Using Stored procedure in MySQL

  • If we use stored procedures, the memory uses of every connection that uses those stored procedures will increase substantially. Also, if we overuse many logical applications inside stored procedures, CPU usage will increase. It is because the database server is not well designed for logical operations.
  • Stored procedure constructs are not designed to develop complex and flexible business logic.
  • It is difficult to debug stored procedures. Only a few database management systems allow us to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
  • It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. It may lead to problems in both application development and maintenance phases.

Also Check Out : Basics of MySQL Stored Procedures

If you interested in Entertainment genre please check this also thebiographys.com

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Protect yourself from CovidVaccination saves lives at every stage of life
  • Clean your home regularly, particularly frequently touched surfaces like
  • Stay home stay safe. Follow the Golden Rule. Wash your hands frequently with soap and water or use alchohol based hand-rub.
  • Stay home stay safe. Stay physically fit. Exercise regulary. Eat a nutritious diet. Don’t smoke.
  • Stay home stay safe. If you show symptoms of COVID-19, self isolate yourself, wear a mask around others and seek medical advice