PLSQL Functions FAQS

1. What is a function in Oracle PL/SQL?

A function is a named PL/SQL block that returns a single value.

·        Can be stored in the database for reuse

·        Can have input parameters

·        Can be used in SQL statements (if deterministic and no DML)

2. What is the syntax of a function?

CREATE OR REPLACE FUNCTION function_name (

   p_input IN NUMBER

) RETURN NUMBER

IS

   v_result NUMBER;

BEGIN

   v_result := p_input * 2;

   RETURN v_result;

END function_name;

·        RETURN specifies the data type of the output

·        IS or AS begins the function body

3. How do you call a function?

From PL/SQL Block

DECLARE

   v_output NUMBER;

BEGIN

   v_output := function_name(10);

   DBMS_OUTPUT.PUT_LINE('Result: ' || v_output);

END;

From SQL Statement

SELECT function_name(salary) 

FROM employees 

WHERE department_id = 10;

Functions can be used in SQL if they don’t perform DML on the table being queried.

4. What are function parameter types?

Parameter Type	Description
IN	Passes value to function; default type; cannot be modified inside
OUT	Not allowed in functions
IN OUT	Rarely used; can pass value in and modify it for return (usually avoided in functions)

5. Can functions have default parameter values?

Yes. Example:

CREATE OR REPLACE FUNCTION greet_user(

   p_name IN VARCHAR2 DEFAULT 'Guest'

) RETURN VARCHAR2

IS

BEGIN

   RETURN 'Hello, ' || p_name;

END;

Calling without parameter uses default 'Guest'.

6. Difference between function and procedure

Feature	Function	Procedure
Returns value	Yes, always (single value)	No
Can be used in SQL	Yes, if deterministic	No
Purpose	Compute and return value	Perform action
Parameters	Only IN recommended	IN, OUT, IN OUT allowed

7. What are advantages of using functions?

·        Reusable logic that returns values

·        Can be called from SQL or PL/SQL

·        Supports modular programming

·        Encapsulates complex calculations

·        Improves maintainability

8. Can functions call other functions or procedures?

Yes. Functions can:

·        Call other functions

·        Call procedures (though cannot be used in SQL if it modifies data)

Example:

v_total := calculate_bonus(salary);

9. How can you handle exceptions in functions?

Use EXCEPTION block:

CREATE OR REPLACE FUNCTION divide_numbers(a NUMBER, b NUMBER) RETURN NUMBER IS

   v_result NUMBER;

BEGIN

   v_result := a / b;

   RETURN v_result;

EXCEPTION

   WHEN ZERO_DIVIDE THEN

      RETURN NULL;

   WHEN OTHERS THEN

      RETURN -1;

END;

10. Can functions improve performance?

Indirectly:

·        Reduce network round-trips by performing logic on the server

·        Can use BULK COLLECT in functions returning collections

·        Modular SQL execution can optimize queries

11. Common mistakes with functions

·        Performing DML on tables being queried → cannot be called from SQL

·        Forgetting RETURN statement

·        Using OUT parameters (not allowed)

·        Not handling exceptions

·        Excessive row-by-row processing instead of bulk operations

12. Best practices for functions

·        Keep functions deterministic for SQL usage

·        Avoid DML inside functions if used in queries

·        Always RETURN a value

·        Use modular code and naming conventions

·        Use BULK COLLECT for collection returns

·        Handle exceptions properly

13. Can functions return complex types?

Yes, functions can return:

·        Scalar types (NUMBER, VARCHAR2, DATE)

·        RECORD types

·        TABLE or VARRAY collections

·        REF CURSOR (for multi-row result sets)

Example returning REF CURSOR:

CREATE OR REPLACE FUNCTION get_employees(p_dept IN NUMBER) RETURN SYS_REFCURSOR IS

   v_cursor SYS_REFCURSOR;

BEGIN

   OPEN v_cursor FOR

      SELECT * FROM employees WHERE department_id = p_dept;

   RETURN v_cursor;

END;

14. How to debug a function?

·        Use DBMS_OUTPUT.PUT_LINE statements

·        Write debug output to a table

·        Test with small datasets

·        Use Oracle SQL Developer debugger