PL/SQL Stored Procedures 🤖

Mentor's Note: An Anonymous block is a one-time thing. A Stored Procedure is a program that is Saved in the database. Anyone with permission can call it, from any app, any number of times. It's the ultimate tool for code reuse! 💡

---

🌟 The Scenario: The Coffee Machine ☕

• The Procedure: You write a program called brew_coffee.
• The Input (IN): You tell it how many sugars. 🍬
• The Output (OUT): It gives you a cup of coffee. ☕
• The Result: You don't have to know how the machine works internally; you just "Call" the procedure.

---

💻 1. Creating a Procedure

CREATE OR REPLACE PROCEDURE welcome_user(p_name IN VARCHAR2) AS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello ' || p_name || '! Welcome to Surat Academy.');
END;
/

---

💻 2. Working with Parameters (The 3 Modes) 🛠️

Mode	Direction	Purpose
IN	Input 📥	Passing data into the procedure (Read-only).
OUT	Output 📤	Returning a result back to the caller.
IN OUT	Both 🔄	Passing a value in, changing it, and sending it back.

Example: The Salary Adjuster

CREATE OR REPLACE PROCEDURE adjust_salary(
   p_emp_id IN NUMBER,
   p_inc    IN NUMBER,
   p_new_sal OUT NUMBER -- 📤 Return value
) AS
BEGIN
   UPDATE employees SET salary = salary + p_inc 
   WHERE id = p_emp_id
   RETURNING salary INTO p_new_sal;
END;
/

---

🏗️ Architect's Note: Performance & Security 🛡️

1. Compilation: Procedures are compiled once. When you call them, they run instantly without the overhead of parsing SQL. ⚡
2. Security: You can give an intern permission to "EXECUTE" the give_raise procedure without giving them "UPDATE" permission on the EMPLOYEES table. This keeps your data safe. 🔒
3. Abstraction: If you change the math inside the procedure, you don't have to update your Java or Python code. The app just gets the new result automatically.

---

📈 Learning Path

• ← Back: Exception Handling
• Next: PL/SQL Functions →