Why stored procedures centralize logic in MySQL - See It in Action

Why Stored Procedures Centralize Logic

📖 Scenario: You work for a company that manages customer orders. The company wants to ensure that the logic for adding new orders is consistent and secure. Instead of writing the same SQL commands in many places, they want to use a stored procedure to centralize the logic.

🎯 Goal: Build a stored procedure in MySQL that inserts a new order into an orders table. This procedure will centralize the logic for adding orders, making it easier to maintain and secure.

📋 What You'll Learn

Create an orders table with columns order_id (auto-increment primary key), customer_name (VARCHAR), and order_amount (DECIMAL).

Create a stored procedure called add_order that takes customer_name and order_amount as input parameters.

The stored procedure should insert a new row into the orders table using the input parameters.

Demonstrate calling the stored procedure to add a new order.

💡 Why This Matters

🌍 Real World

Companies use stored procedures to keep important database operations consistent and secure, avoiding repeated code in many places.

💼 Career

Database developers and administrators often write stored procedures to centralize business logic, improving maintainability and security.

1

Create the orders table

Write a SQL statement to create a table called orders with these columns: order_id as an auto-increment primary key, customer_name as VARCHAR(100), and order_amount as DECIMAL(10,2).

MySQL

-- Your code here to create the orders table

Need a hint?

Use CREATE TABLE with AUTO_INCREMENT for the primary key.

2

Create the stored procedure add_order

Write a SQL statement to create a stored procedure called add_order that takes two input parameters: in_customer_name (VARCHAR(100)) and in_order_amount (DECIMAL(10,2)). The procedure should insert a new row into the orders table using these parameters.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);
-- Your code here to create the stored procedure add_order

Need a hint?

Use CREATE PROCEDURE with IN parameters and an INSERT statement inside BEGIN ... END.

3

Call the stored procedure to add an order

Write a SQL statement to call the stored procedure add_order with 'Alice' as customer_name and 150.75 as order_amount.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

DELIMITER $$
CREATE PROCEDURE add_order(
  IN in_customer_name VARCHAR(100),
  IN in_order_amount DECIMAL(10,2)
)
BEGIN
  INSERT INTO orders (customer_name, order_amount) VALUES (in_customer_name, in_order_amount);
END$$
DELIMITER ;

-- Your code here to call the stored procedure add_order

Need a hint?

Use the CALL statement with the procedure name and parameters.

4

Explain why stored procedures centralize logic

Add a SQL comment explaining why using the stored procedure add_order centralizes the logic for adding orders in the database.

MySQL

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

DELIMITER $$
CREATE PROCEDURE add_order(
  IN in_customer_name VARCHAR(100),
  IN in_order_amount DECIMAL(10,2)
)
BEGIN
  INSERT INTO orders (customer_name, order_amount) VALUES (in_customer_name, in_order_amount);
END$$
DELIMITER ;

CALL add_order('Alice', 150.75);

-- Your comment here explaining centralization of logic

Need a hint?

Write a comment starting with -- explaining that the procedure keeps the insert logic in one place.