Procedure vs Function | PLSQL

In the context of Oracle SQL, the basic differences between a function and a procedure are essential concepts that interviewers often explore. Here are the key distinctions

1. Purpose

• Function: Designed to compute and return a single value. Functions are typically used for calculations and can be part of SQL expressions.
• Procedure: Primarily intended to perform a specific task or set of operations, which may or may not return a value. Procedures can execute a series of SQL statements and are often used for operations like updating data or managing transactions.

2. Return Value

• Function: Must return a value using the RETURN statement. The return type is specified in the function declaration.
• Procedure: Does not return a value directly. Instead, it can return values through output parameters (defined as OUT parameters).

3. Invocation

• Function: Can be called within SQL statements (e.g., SELECT, WHERE, etc.). This allows functions to be used in expressions and to compute values on-the-fly.
• Procedure: Called using the EXECUTE statement or from another PL/SQL block. Procedures cannot be called from within SQL statements.

4. Parameters

• Function: Can have input parameters and must return a single value. Typically defined with IN parameters.
• Procedure: Can have IN, OUT, or IN OUT parameters, providing more flexibility in passing and returning multiple values.

5. Usage Context

• Function: Ideal for calculations, transformations, or any operation that needs to produce a value that can be used in SQL queries.
• Procedure: Best suited for executing complex business logic, batch processing, or performing tasks that do not necessarily require a return value.

6. Example Syntax

Function:

CREATE OR REPLACE FUNCTION calculate_bonus (
    p_salary IN NUMBER
) RETURN NUMBER IS
BEGIN
    RETURN p_salary * 0.10; -- 10% bonus
END;

Procedure:

CREATE OR REPLACE PROCEDURE give_bonus (
    p_employee_id IN NUMBER,
    p_bonus OUT NUMBER
) IS
BEGIN
    -- Logic to calculate bonus and assign it to p_bonus
    p_bonus := 1000; -- Example bonus
END;

Conclusion

Understanding these differences is crucial for effectively using PL/SQL in Oracle databases and for communicating your knowledge during an interview. Be prepared to provide examples or discuss scenarios where you might choose one over the other based on the task requirements.