PL/SQL Functions - In Depth Notes

Objectives

• Understand the concept of stored functions in PL/SQL.
• Create PL/SQL blocks containing functions.
• Identify different ways to invoke functions.
• Create a PL/SQL block to invoke functions that have parameters.
• Outline the steps for developing functions.
• Distinguish between procedures and functions.

Definition of a Stored Function

• A stored function is a named PL/SQL block (subprogram) that accepts optional IN parameters and must return exactly one value.
• It can only be called as part of a SQL or PL/SQL expression.
• Functions must avoid certain operations to prevent side effects, such as:
  • Any kind of DML or DDL.
  • COMMIT or ROLLBACK statements.
  • Altering global variables.

Purpose of Functions in PL/SQL

• Functions are vital for creating modular, reusable, and maintainable code.
• They can be stored in the database as schema objects for repeated execution.

Characteristics of Functions

• Must have a RETURN clause in the function header and at least one RETURN statement in the executable section.
• In SQL expressions, functions are subject to specific restrictions to manage side effects.

Syntax for Creating Functions

CREATE [OR REPLACE] FUNCTION function_name(
    [parameter1 [mode1] datatype1, ...]
) RETURN datatype IS|AS
    [local_variable_declarations; ...]
BEGIN
    -- actions;
    RETURN expression;
END [function_name];

• The function header resembles a procedure header, but:
  • The parameter mode can only be IN.
  • The RETURN clause is included instead of OUT mode.

Example of a Stored Function with a Parameter

CREATE OR REPLACE FUNCTION get_sal(p_id IN employees.employee_id%TYPE)
  RETURN NUMBER IS
    v_sal employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id;
    RETURN v_sal;
END get_sal;

Invoking Functions

Ways to Invoke Functions

1. As part of PL/SQL expressions
   • Use local variables to store returned values.
2. As parameters to another subprogram
   • Pass functions as parameters between subprograms.
3. In SQL statements
   • Invoke as single-row functions in SQL expressions.

Example of Invoking Functions

• As a PL/SQL expression:

DECLARE
    v_sal employees.salary%TYPE;
BEGIN
    v_sal := get_sal(100);
END;

• As a parameter:

DBMS_OUTPUT.PUT_LINE(get_sal(100));

• In a SQL statement:

SELECT job_id, get_sal(employee_id) FROM employees;

Benefits and Restrictions of Functions

Benefits

• Functions allow for quick transformations and format changes (e.g., displaying values in different formats).
• Extend functionality within PL/SQL applications.

Restrictions

• PL/SQL types may not align with SQL types, and size limits differ.
• Functions used in SQL cannot use certain modes (e.g., OUT, IN OUT).

Differences Between Procedures and Functions

Summary

• Learned to define and develop stored functions.
• Understood various invocation methods and the differences between procedures and functions.