PLSQL Functions

Function is a named PL/SQL block that can return a value and can be used in SQL statements. Functions can take parameters, perform operations, and return results.

Features:

1. Reusable Code: Functions allow you to encapsulate logic that can be reused in different parts of your application or database queries.
2. Return Values: They can return a single value of a specific data type, such as NUMBER, VARCHAR2, etc.
3. Input Parameters: Functions can take zero or more input parameters, allowing for dynamic calculations based on input values.
4. PL/SQL Support: They can include complex logic, such as loops and conditional statements, making them powerful for data processing.
5. Use in SQL Statements: Functions can be called directly within SQL statements, enabling their use in SELECT, WHERE, and ORDER BY clauses.
6. Overloading: You can create multiple functions with the same name but different parameter types or counts, allowing for flexibility in usage.
7. Integration with Triggers and Procedures: Functions can be called from triggers and stored procedures, enhancing their utility in the database.
8. Security: Functions can encapsulate business logic and help manage access to underlying data by controlling how data is processed and returned.

Simple Example for function:

CREATE OR REPLACE FUNCTION calculate_area(radius IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN 3.14 * radius * radius;
END;
/

Run: SELECT calculate_area(5) AS area FROM dual;

Example:

CREATE OR REPLACE FUNCTION increment_salary(emp_id IN NUMBER, increment_amount IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
UPDATE employees
SET salary = salary + increment_amount WHERE employee_id = emp_id;
COMMIT;  -- Commit the changes
RETURN 'Salary updated successfully.';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Employee not found.';
WHEN OTHERS THEN
RETURN 'An error occurred: ' || SQLERRM;
END;
/

Run: SELECT increment_salary(1, 5000) FROM dual;

Drop the Function:

DROP FUNCTION function_name;

Difference procedure and function:

Function	Procedure
Must return a value	Does not return a value
Can be used in SQL	Cannot be used in SQL
Calculations	Actions and processes
Input parameters only	IN, OUT, IN OUT parameters
Can raise exceptions	Can raise exceptions