Introduction Show
MySQL stored procedures group multiple tasks into one and save the task on the server for future use. Stored procedures simplify database management and reduce network traffic. For example, issuing a query to the MySQL server processes the query and returns the results. Using stored procedures saves the queries on the server so they can be executed later. In this tutorial, you will learn to create, list, alter, and drop stored procedures. Prerequisites
What Are Stored Procedures in MySQL?MySQL stored procedures are pre-compiled SQL statements stored in a database. They are subroutines containing a name, a parameter list, and SQL statements. All relational database systems support stored procedures and do not require any additional runtime-environment packages. How to Use Stored Procedures?To invoke stored procedures, you can use the Subsequent runs in the same session execute stored procedures from the cache memory, making them extremely useful for repetitive tasks. Stored procedures make use of parameters to pass values and customize results. Parameters are used to specify the columns in a table in which the query operates and returns results. Stored procedures can also include the Create Stored ProcedureCreate a stored procedure in two ways: 1. Use MySQL Shell Use the following syntax to create a stored procedure in MySQL:
By default, the syntax is associated with the database in use, but you can also use the syntax for another database by specifying the database name in the following way: Here, the first The procedure name comes after the The parameter modes are:
For example: Execute the stored procedure by calling it:
The query returns results for the stored procedure. 2. Use MySQL Workbench Another way to create a stored procedure is to use the MySQL Workbench Wizard. The wizard is intuitive and simplifies the process since you do not have to place delimiters or worry about the format. Follow these steps: Step 1: Right-click Stored Procedures in the Navigator window of MySQL Workbench and choose Create Stored Procedure… to start the wizard.
Step 2: Specify the procedure name and enter the code within the BEGIN … END block. Step 3: Review the code and click Apply. Step 4: Confirm execution by clicking Apply and create the procedure by clicking Finish. Step 5: Execute the procedure to see if it works. Create a new SQL tab for executing queries. Step
6: If no errors return, MySQL executes the stored procedure and displays the results. List Stored ProceduresThere are three ways to see a list of all stored procedures: 1. Use MySQL Shell To get a list of all stored procedures you have access to, including their characteristics, use the following syntax:
The Scroll through the output to find the procedures currently on the server. The For example: The For example: In this example, the statement returns only the stored procedures for the ’customer_list’ database. 2. Use Data Dictionary The information_schema database contains a table called routines, which has information on stored procedures and functions related to all databases on the current MySQL server. Use the following syntax to see all stored procedures for a database:
3. Use MySQL Workbench For a GUI approach to viewing stored procedures, use MySQL Workbench. Follow these steps to see stored procedures: Step 1: Double-click the database you want to use in the Navigator section. Step 2: Expand the Stored Procedures drop-down item. This item shows all stored procedures for the current database. Alter Stored ProcedureTo alter a stored procedure means to change the characteristics of a procedure. There is no statement in MySQL for modifying the parameters or the body of a stored procedure. To change parameters or the body, drop the stored procedure and create a new one. Alter a stored procedure in two ways: 1. Use MySQL Shell Change a procedure characteristic by using the
2. Use MySQL Workbench MySQL Workbench GUI allows users to alter a stored procedure where users can add parameters or change the code. MySQL Workbench drops the existing stored procedure and creates a new one after the changes have been made. Follow these steps: Step 1: In the Navigator section, right-click the stored procedure you want to modify. Select the Alter Stored procedure… item. Step 2: When the tab opens, make the desired changes to the existing stored procedure and click Apply.
Step 3: An SQL Script review window appears showing the process – dropping the existing stored procedure and creating a new one containing the changes. Click Apply and then Finish in the next window to execute the script. Drop Stored ProcedureTo drop (delete) a procedure: 1. Use MySQL Shell Delete a stored procedure from the server by using the The basic syntax is:
The For example: Since there is no procedure named ‘test’ on the server, the output states that 0 rows were affected and that the specified procedure does not exist. Dropping
a non-existing procedure without the 2. Use MySQL Workbench To drop a stored procedure with MySQL Workbench, follow these steps: Step 1: Expand the Stored Procedures item in the Navigator section. Right-click the stored procedure you want to delete and choose Drop Stored Procedure… in the context menu. Step 2: In the confirmation window, click Drop Now to delete the stored procedure. This action permanently deletes the procedure. MySQL Stored Procedures Advantages and DisadvantagesStored procedures have several advantages and disadvantages as they tailor to specific needs. Below are some of the advantages and disadvantages. Advantages of Using Stored ProceduresThe advantages of stored procedures are: Network Traffic Reduction Stored procedures help reduce the network traffic between applications and MySQL Server by keeping all the programming logic on the server. Instead of sending multiple query results across the network, apps send only the procedure name and the parameter input. Improved Security The database administrator grants apps privileges to call and access only specific stored procedures without giving them direct access to tables. Stored procedures help prevent script injection attacks since input parameters are treated as values and not as executable code. Centralized Business Logic Stored procedures encapsulate the business logic reusable by multiple applications. That helps reduce duplicating that same logic in many different applications and makes the database more consistent. Disadvantages of Using Stored ProceduresThe disadvantages of stored procedures are: Resource Usage Using many stored procedures and logical operations causes the memory and CPU usage to increase significantly for every connection. No Portability It is not easy to port stored procedures written in a specific language from one installation to another. Relying on a stored procedure also ties the user to a particular database. Troubleshooting and Testing MySQL does not provide utilities for testing and debugging stored procedures, so it can be difficult to debug them. Developing and maintaining stored procedures require extensive knowledge. This is a challenge for new developers and results in added maintenance costs. Conclusion After reading this article, you know what stored procedures are and when to use them. You also know how to create, modify, see all available stored procedures, and delete the ones you no longer need. Where is stored procedure in MySQL?Where are stored procedures stored? Stored procedures are stored in the mysql. routines and mysql. parameters tables, which are part of the data dictionary.
What is stored procedures in database?A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system (RDBMS) as a group, so it can be reused and shared by multiple programs.
What is difference between stored procedure and function in MySQL?The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.
How do I create a stored procedure in MySQL?To create a new stored procedure, you use the CREATE PROCEDURE statement. First, specify the name of the stored procedure that you want to create after the CREATE PROCEDURE keywords. Second, specify a list of comma-separated parameters for the stored procedure in parentheses after the procedure name.
|