SQL Stored Procedures 🤖

Mentor's Note: Think of a Stored Procedure like a "Microwave Preset". Instead of manually setting the power and time every time you want to pop popcorn, you just press one button. A Stored Procedure saves a complex script so you can "play" it again with a single command. 💡

---

🌟 The Scenario: The Daily Backup 📁

Imagine you have to move all "Cancelled Orders" to a "History" table every night.

• Manual: Write 5 different queries every night. (Exhausting! 🥵)
• Procedure: Write the logic once, save it as archive_orders(), and just call it. ✅

---

💻 1. The Basic Syntax

MySQL / PostgreSQL

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM students;
END //
DELIMITER ;

-- To Run:
CALL GetAllStudents();

SQL Server / Oracle

CREATE PROCEDURE GetEmployeeSalary
AS
BEGIN
    SELECT emp_name, salary FROM employees;
END;

-- To Run:
EXEC GetEmployeeSalary;

---

💻 2. Using Parameters (Inputs) 📥

You can pass data into a procedure to make it dynamic.

-- Scenario: Create a procedure to give a specific employee a raise
CREATE PROCEDURE GiveRaise (IN emp_id INT, IN raise_amt DECIMAL)
BEGIN
    UPDATE employees 
    SET salary = salary + raise_amt 
    WHERE id = emp_id;
END;

-- To Run:
CALL GiveRaise(101, 5000);

---

🎨 Visual Logic: The Black Box

---

📊 Why use Stored Procedures?

1. Efficiency: The code is compiled once and runs faster. ⚡
2. Security: You can let users "Run the Procedure" without giving them access to the "Raw Tables". 🛡️
3. Consistency: Ensures the exact same math is used by every app (Web, Mobile, Admin).

---

📈 Learning Path

• ← Back: SQL Indexes
• Next: SQL Security (Injection) →