PL/SQL Functions 🧮¶

Mentor's Note: A Procedure does something. A Function returns something. If you need to calculate a tax, format a name, or check a balance and get an answer back, you use a Function. 💡

🌟 The Scenario: The Calculator 📱¶

Imagine a calculator app. - You type 10 + 5. - You press the = button. - The app performs the math and gives you the answer (15). - A Function is that calculator inside your database.

💻 1. Creating a Function¶

A function must have a RETURN clause in its header and at least one RETURN statement in its body.

CREATE OR REPLACE FUNCTION get_annual_salary(p_monthly_sal NUMBER) 
RETURN NUMBER IS
   v_annual NUMBER;
BEGIN
   v_annual := p_monthly_sal * 12;
   RETURN v_annual; -- 🚀 Hand the answer back
END;
/

💻 2. Using Functions in SQL 🛠️¶

Unlike Procedures, you can use Functions directly inside your SELECT statements!

SELECT first_name, get_annual_salary(salary) AS "Yearly_Income"
FROM employees;

📊 Procedure vs. Function¶

Feature	Procedure	Function
Return Value	Optional (via OUT)	Mandatory ✅
Usage in SQL	No ❌	Yes (in SELECT/WHERE) ✅
Main Purpose	Execute business logic	Perform calculations
Call Method	`EXEC` or `CALL`	Part of an expression

🛡️ 3. Deterministic Functions (Architect's Note)¶

If your function always returns the same result for the same input (like a Tax calculator), you should mark it as DETERMINISTIC. - The Architect's Secret: This tells Oracle to "Cache" the result. If you run it 1,000 times with the same input, Oracle only calculates it once and reuses the answer. This is a massive Speed Booster! ⚡