Syntax for Functions Returning a Boolean Value

In PL/SQL, a function can return a Boolean value to indicate whether a condition evaluates to TRUE, FALSE, or NULL. Boolean-returning functions are commonly used to encapsulate business rules, validation logic, and conditional checks within stored program units.

This lesson focuses on identifying the correct syntax for defining a PL/SQL function that returns a Boolean value and understanding how that return value is produced.

Example: Boolean Function Logic

The following example demonstrates a simple function that evaluates the current day of the week:

1. If today is Friday, the function returns TRUE.
2. Otherwise, it returns FALSE.

CREATE OR REPLACE FUNCTION tgif_test
RETURN BOOLEAN AS
BEGIN
  IF TO_CHAR(SYSDATE, 'DAY') = 'FRIDAY' THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

A Boolean variable may also be declared inside the function body if intermediate logic requires it.

Correct PL/SQL Syntax for a Boolean Function

The general syntax for a PL/SQL function that returns a Boolean value is shown below:

FUNCTION function_name (
  parameter1 IN datatype,
  parameter2 IN datatype
) RETURN BOOLEAN IS
  -- Optional variable declarations
BEGIN
  -- Executable logic
  RETURN boolean_expression;
END function_name;

Key elements of this syntax include:

• FUNCTION introduces the function definition.
• function_name uniquely identifies the function.
• RETURN BOOLEAN specifies the function’s return datatype.
• IS begins the declaration section.
• BEGIN starts the executable section.
• RETURN sends a Boolean value back to the caller.
• END function_name; completes the function definition.

Simple Boolean Return Example

FUNCTION is_greater_than_ten (
  p_number IN NUMBER
) RETURN BOOLEAN IS
BEGIN
  RETURN p_number > 10;
END is_greater_than_ten;

This function evaluates a numeric input and returns TRUE if the value exceeds ten. If the expression evaluates to neither true nor false, the function may return NULL, which is valid for PL/SQL Boolean logic.

Reserved Words and Identifiers

Certain words in PL/SQL, such as BEGIN and END, are reserved and cannot be used as identifiers. Attempting to redefine a reserved word results in a compilation error.

DECLARE
-- end BOOLEAN; -- invalid: END is a reserved word
end_of_game BOOLEAN; -- valid identifier
BEGIN
  NULL;
END;

While some PL/SQL keywords can technically be used as identifiers, doing so is discouraged because it reduces code clarity and maintainability.

Boolean Datatype in Context

BOOLEAN is a scalar PL/SQL datatype. Scalar types hold a single value and include numeric, character, Boolean, and date/time families. Boolean values are used exclusively within PL/SQL and cannot be stored directly in database table columns.

Returning Functions Syntax Quiz

The next lesson concludes this module and prepares you for the topics covered in the next section.