Introduction to Stored Routines

In this lesson, you will learn how to encapsulate frequently executed SQL logic directly within your database to improve performance and code reuse.

Moving Logic Closer to the Data

Up until now, you have mostly interacted with the database by sending SQL queries from your application code. While this is standard, it often results in "chattiness," where the application and the database must communicate back and forth multiple times to complete a single business task.

A Stored Routine is a set of SQL statements stored inside the database server itself. Instead of sending a long, complex script from your application every time you need to perform an operation, you simply call the routine by name. Think of it as a function that lives inside your database rather than in your application code.

Why Use Stored Routines?

There are two primary benefits to this approach:

1. Performance: Because the logic resides on the database server, it avoids the network latency of sending multiple queries over the wire. The database engine can also pre-compile and optimize the execution plan for the routine, making it faster to run repeatedly.
2. Encapsulation and Reuse: If you have a specific task—like calculating a complex discount or updating several related tables—you can write it once as a routine. Any application or user with the right permissions can call that routine, ensuring the logic remains consistent everywhere.

A Conceptual Illustration

Imagine you frequently need to archive a customer's record. Instead of writing five separate INSERT and DELETE queries from your application every time, you could define a routine:

-- Conceptual structure of a stored routine
CREATE PROCEDURE ArchiveCustomer(customer_id INT)
BEGIN
    INSERT INTO archived_customers SELECT * FROM customers WHERE id = customer_id;
    DELETE FROM customers WHERE id = customer_id;
END;

Once defined, your application simply executes CALL ArchiveCustomer(101);. You have effectively hidden the complexity of the archiving process, reduced the traffic between your app and the database, and ensured that the "archive" process is always handled the exact same way.

Key Takeaway: Stored routines allow you to bundle complex SQL logic inside the database, reducing network overhead and promoting consistent, reusable execution of common tasks.