Stored Functions in PL-SQL

What is a Stored Function?

A stored function is a named PL/SQL block that returns a single value after execution. It is similar to a stored procedure, but a function must return a value, while a procedure does not.

Stored functions are useful for:
✅ Performing calculations and returning results.
✅ Simplifying complex queries by encapsulating logic.
✅ Reusing code across multiple applications.
✅ Enhancing performance by reducing redundant computations.

---

Difference Between Stored Functions and Procedures

Feature	Stored Procedure	Stored Function
Returns a Value?	No (uses OUT parameters)	Yes (must return a value)
Used in SQL Queries?	No	Yes
Called in PL/SQL?	Yes	Yes
Main Purpose	Performs operations without returning a value	Computes and returns a single value

---

Syntax for Creating a Stored Function

CREATE OR REPLACE FUNCTION function_name 
(param1 datatype, param2 datatype)  
RETURN return_datatype  
IS  
    -- Declaration Section
BEGIN  
    -- Execution Section
    RETURN value;  
END function_name;

📌 Key Points:

• RETURN return_datatype → Specifies the data type of the returned value.
• RETURN value; → The function must return a value.

---

1. Creating a Simple Stored Function

Example: Function to Calculate the Square of a Number

CREATE OR REPLACE FUNCTION get_square (p_num NUMBER)  
RETURN NUMBER  
IS  
    v_result NUMBER;
BEGIN  
    v_result := p_num * p_num;
    RETURN v_result;
END get_square;

📌 Explanation:

• Takes a number as input (p_num).
• Computes the square of the number (p_num * p_num).
• Returns the result (v_result).

Executing the Function

DECLARE
    v_square NUMBER;
BEGIN
    v_square := get_square(5);
    DBMS_OUTPUT.PUT_LINE('Square of 5: ' || v_square);
END;

🔹 Output: Square of 5: 25

---

2. Using a Stored Function in SQL Queries

Stored functions can be used in SQL queries, unlike procedures.

Example: Function to Get Total Salary of an Employee

CREATE OR REPLACE FUNCTION get_total_salary (emp_id NUMBER)  
RETURN NUMBER  
IS  
    v_salary NUMBER;
BEGIN  
    SELECT Salary INTO v_salary FROM Employees WHERE EmployeeID = emp_id;
    RETURN v_salary;
END get_total_salary;

📌 Using the Function in a SQL Query

SELECT EmployeeID, Name, get_total_salary(EmployeeID) AS TotalSalary
FROM Employees;


🔹 Explanation:

• The function get_total_salary retrieves an employee’s salary.
• It can be directly used inside a SELECT statement.

---

3. Stored Function with Multiple Parameters

Example: Function to Calculate Discounted Price

CREATE OR REPLACE FUNCTION get_discounted_price (p_price NUMBER, p_discount NUMBER)  
RETURN NUMBER  
IS  
    v_final_price NUMBER;
BEGIN  
    v_final_price := p_price - (p_price * p_discount / 100);
    RETURN v_final_price;
END get_discounted_price;

📌 Executing the Function

DECLARE
v_price NUMBER;
BEGIN
v_price := get_discounted_price(1000, 10);
DBMS_OUTPUT.PUT_LINE('Final Price: ' || v_price);
END;


🔹 Output: Final Price: 900

---

4. Stored Function with Exception Handling

Example: Handling NO_DATA_FOUND Exception

CREATE OR REPLACE FUNCTION get_employee_name (p_id NUMBER)  
RETURN VARCHAR2  
IS  
    v_name Employees.Name%TYPE;
BEGIN  
    SELECT Name INTO v_name FROM Employees WHERE EmployeeID = p_id;
    RETURN v_name;
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        RETURN 'Employee Not Found';
END get_employee_name;

📌 Explanation:

• If EmployeeID does not exist, it returns “Employee Not Found” instead of throwing an error.

Executing the Function

DECLARE
    v_emp_name VARCHAR2(50);
BEGIN
    v_emp_name := get_employee_name(105);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;

🔹 Output: Employee Name: Employee Not Found (if ID 105 doesn’t exist).

---

5. Dropping a Stored Function

To remove a function:

DROP FUNCTION function_name;


📌 Example:

DROP FUNCTION get_square;


---

Advantages of Stored Functions

✅ Reusability – Functions can be reused in different SQL queries.
✅ Performance Improvement – Reduces redundant calculations.
✅ Security – Restricts direct table access.
✅ Modular Programming – Encapsulates logic for cleaner code.

---

6. Calling a Stored Function from Another Function

Example: Nested Function Calls

CREATE OR REPLACE FUNCTION get_final_salary (emp_id NUMBER, bonus NUMBER)  
RETURN NUMBER  
IS  
    v_total_salary NUMBER;
BEGIN  
    v_total_salary := get_total_salary(emp_id) + bonus;
    RETURN v_total_salary;
END get_final_salary;

📌 Explanation:

• get_final_salary calls another function (get_total_salary) to get the salary.
• Adds a bonus and returns the final salary.

Executing the Function

SELECT get_final_salary(101, 500) FROM dual;

---

7. Using Stored Functions in Triggers

Functions can be used inside triggers for validation.

Example: Preventing Salary Below Minimum

CREATE OR REPLACE FUNCTION check_min_salary (p_salary NUMBER)  
RETURN BOOLEAN  
IS  
BEGIN  
    IF p_salary < 3000 THEN  
        RETURN FALSE;  
    ELSE  
        RETURN TRUE;  
    END IF;  
END check_min_salary;

Trigger Using the Function

CREATE OR REPLACE TRIGGER enforce_min_salary  
BEFORE INSERT OR UPDATE ON Employees  
FOR EACH ROW  
BEGIN  
    IF NOT check_min_salary(:NEW.Salary) THEN  
        RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be less than 3000.');
    END IF;  
END enforce_min_salary;

📌 Explanation:

• The function check_min_salary checks if the salary is valid.
• The trigger enforce_min_salary uses the function to validate salary before insertion.

---

Conclusion

✅ Stored Functions are used to compute and return values in PL/SQL.
✅ They are different from procedures because they must return a value.
✅ They can be used in SQL queries and inside other PL/SQL blocks.
✅ Functions improve reusability and performance.