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! ⚡