Stored Procedures in MySQL: How to use them?

Main » Databases » Stored Procedures in MySQL: How to use them?

Stored procedures are routines stored in the MySQL database that encapsulate SQL operations and programming logic. They act as mini programs that can be invoked to perform repetitive or complex tasks.

They offer multiple benefits such as modularity, code reuse, increased security, and better performance. Therefore, mastering stored procedures is essential for any MySQL developer or administrator.

Below we will see what they are exactly, their advantages, how to create and use stored procedures in MySQL step by step and some practical examples so that you can get the most out of them.

What are stored procedures in MySQL?

Stored procedures are sets of precompiled SQL statements which are stored in the MySQL database with a name and input and output parameters.

They can be invoked by name by passing values ​​for their parameters. When executed, they perform the operations defined within them, such as queries, updates, flow control logic, and more.

They are like mini programs resident in the database that encapsulate complex operations for:

• Simplify repetitive administrative tasks
• Centralize business logic on the server
• Reduce client/server traffic
• Improve performance when precompiling query

When stored on the server, they provide desirable features such as:

• Modularity: They break logic down into manageable chunks.
• Reuse: Eliminate code duplication.
• Safety: Procedure level permissions.
• Hiding logic: The client does not see internal details.

In summary, stored procedures are a very useful tool for MySQL developers. Let's take a closer look at their main advantages.

Advantages of using stored procedures

Stored procedures provide important benefits:

1. Better organization and maintenance of code

They allow complex operations to be modularized into separate, easily manageable parts. Code can be reused from multiple places by invoking the stored procedure.

This leads to more organized, maintainable code with less duplication.

2. Reduction of traffic between client and server

By storing them on the server, the transfer of information is reduced since it is not necessary to send SQL queries each time. This improves performance.

3. Faster operations and transactions

MySQL only compiles the procedure once and reuses it. This speeds up execution by eliminating the need to recompile each time.

Additionally, they can be used to encapsulate complex transactions that will execute faster.

4. Increased security and permissions

Permissions and privileges can be assigned at the stored procedure level, rather than giving full access to database tables.

This allows for greater control over the data and operations that users can perform.

5. Easy to use and maintain by other developers

Since they are shared in the database, any developer with permissions can execute the procedures without knowing their internal code. For a deeper understanding of permission management, review users in MySQL.

This makes maintenance easier and reduces errors.

6. Portability between different applications

Since its logic is contained in the database, it can be invoked from any programming language that supports it, including Create functions in MySQL.

How to create stored procedures in MySQL

Creating a stored procedure in MySQL is simple using SQL syntax:

CREATE PROCEDURE procedure_name()
BEGIN
instructions;
END

The procedure is created with a unique name and between the BEGIN – END we will place the SQL statements to be executed when it is invoked.

For example, this procedure records a new sale:

CREATE PROCEDURE insert_sale(
p_value FLOAT,
p_client VARCHAR(50)
)
BEGIN
INSERT INTO sales(value, customer)
VALUES(p_value, p_client);
END

This procedure takes the p_value and p_customer parameters, inserts a new sale into the sales table, and returns the auto-generated id. If you'd like to expand your knowledge of data management, see MySQL queries in PHP with examples.

Procedures also support more advanced code such as local variables, cursors, error handling with BEGIN-EXCEPTION-END, etc.

How to use stored procedures in MySQL

Once the stored procedure is created, it can be invoked in any SQL query using CALL:

CALL procedure_name(parameter_value1, parameter_value2);

For example, to insert a sale:

CALL insert_sale(125.50, 'Juan Perez');

Procedures can also be invoked from any programming language by passing values ​​to the parameters. For example, in PHP:

$result = $mysqli->query("CALL insert_sale(125.50, 'Juan Perez')");

And from Java:

CallableStatement stmt = conn.prepareCall("{CALL insert_sale(?, ?)}");
stmt.setDouble(1, 125.50);
stmt.setString(2, "John Perez");
stmt.executeUpdate();

Examples of stored procedures in MySQL

Let's look at some practical examples to better leverage this powerful MySQL feature:

Get total sales per customer

CREATE PROCEDURE total_sales_customer(IN p_customer VARCHAR(50))
BEGIN
SELECT SUM(value) AS total
FROM sales
WHERE client = p_client;
END

Use:

CALL total_sales_customer('Ana Valdez');

List products under a given price

CREATE PROCEDURE cheap_products(IN p_price DECIMAL(10,2))
BEGIN
SELECT id, name, price FROM products
WHERE price <= p_price;
END

Use:

CALL cheap_products(500);

Delete and re-insert records

CREATE PROCEDURE regenerate_table()
BEGIN
DELETE FROM my_table;
— Insert new records
END;

Practical for regenerating test tables. For additional information on database structure, visit database elements.

Call another internal procedure

CREATE PROCEDURE my_procedure()
BEGIN
CALL other_procedure();

— more instructions
END;

Allows you to break down into reusable parts.

Transfer funds between accounts

CREATE PROCEDURE transfer(
p_cuentaorigen INT,
p_cuentaDestino INT,
p_decimal amount(10,2)
)
BEGIN
START TRANSACTION?

UPDATE accounts
SET balance = balance – p_amount
WHERE id = p_cuentaOrigen;

UPDATE accounts
SET balance = balance + p_amount
WHERE id = p_cuentaDestino;

COMMIT;
END;

Encapsulates the transfer logic in a transaction.

Conclusions on stored procedures

Stored procedures are a very powerful feature of MySQL that every developer should master. To learn more about other related features, check out relational databases.

They allow you to modularize complex operations, reuse code, reduce traffic between client and server, improve performance and security.

In addition to stored procedures, MySQL offers a variety of functions that are also essential for any developer. These functions allow specific and complex operations to be performed in an efficient and modular manner. Some of the most commonly used functions in MySQL include `CONCAT` for concatenating strings, `SUBSTRING` for extracting substrings, and mathematical functions such as `ROUND` and `CEIL` for rounding. and you can also