Mysql - Simple Stored Procedure

🔔 Ready to Master SQL & Database Programming?

Subscribe to our channel for beginner-friendly, hands-on tutorials on MySQL, Oracle DB, SQL, and Java!

▶ Subscribe to Ram N Java

Introduction: What is a Stored Procedure in MySQL?

A Stored Procedure in MySQL is a set of SQL statements that can be saved directly on the database server and executed repeatedly whenever needed. Instead of writing and sending SQL queries individually from client code, a stored procedure packages that logic into a neat, callable routine.

For beginners, creating a basic stored procedure without parameters is the best way to understand how procedures work, how they are compiled, and how to execute them inside MySQL Workbench.

Step 1: Creating a Stored Procedure in MySQL Workbench

Inside MySQL Workbench, navigate to your target database schema (for example, the sample world database) in the Navigator panel:

1. Expand your database schema and right-click on Stored Procedures.
2. Select Create Stored Procedure... from the context menu.
3. A template editor will open where you can name the routine and define your query.

Step 2: Writing the Procedure Logic

Inside the body between BEGIN and END, add the SQL statement you want the routine to execute. For example, to fetch all city records:

CREATE PROCEDURE `get_all_cities` ()
BEGIN
    SELECT * FROM City;
END
  

Click the Apply button at the bottom right. MySQL Workbench will generate the DDL script and compile your stored procedure on the server.

Step 3: Calling and Testing the Procedure

To execute your new stored procedure, open a SQL Query tab and run the CALL command:

-- Execute the simple stored procedure
CALL get_all_cities();
  

Alternatively, in MySQL Workbench, you can click the lightning bolt icon next to the stored procedure name in the Navigator tree. MySQL will run the procedure and display the entire table output in the result grid.

📺 Recommended Next Tutorials from Ram N Java

• 👉 How to Store File to a Database Table Using JDBC Program? | MySQL
• 👉 Spring JDBC Tutorial: Calling Stored Functions with SimpleJdbcCall
• 👉 JDBC CallableStatement Tutorial: In-Out Parameters in Oracle DB Stored Procedures