Syntax for Functions returning Boolean Value

You saw the basic syntax of a function in a previous lesson. Here is a simple example of a function that returns a Boolean value.
The function checks today's day of the week.

1. If it 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;

If needed, you can declare a variable within the function that has a Boolean datatype.

In PL/SQL, the syntax for a function that returns a Boolean value can be structured as follows:

FUNCTION function_name (
  parameter1 IN datatype,
  parameter2 IN datatype,
  ...
) RETURN BOOLEAN IS
  -- Declare variables if needed
BEGIN
  -- Function logic here
  RETURN boolean_expression;
END function_name;

Here's a breakdown:

• FUNCTION: Keyword to define a function.
• function_name: Name of your function.
• parameter1, parameter2: Input parameters. IN specifies that these are input-only parameters. You can also use OUT or IN OUT for different parameter behaviors.
• RETURN BOOLEAN: Indicates that the function will return a Boolean value (TRUE, FALSE, or NULL in PL/SQL).
• IS: Keyword to start the function's declaration block.
• BEGIN: Marks the start of the executable section of the function.
• RETURN boolean_expression: The Boolean expression or variable to return. This could be TRUE, FALSE, or a condition that evaluates to one of these.
• END function_name;: Ends the function body, with the function name for clarity.

Here's a simple 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 checks if the input number is greater than 10 and returns TRUE if it is, FALSE otherwise. Remember, in PL/SQL, NULL can also be a valid return value for a BOOLEAN function if the logic leads to an undefined state.

For example, the words BEGIN and END are reserved. Often, reserved words are written in upper case for readability. Trying to redefine a reserved word causes a compilation error. Instead, you can embed reserved words as part of a longer identifier. For example:

 
DECLARE
-- end BOOLEAN; the use of "end" is not allowed; causes compilation error
end_of_game BOOLEAN; -- allowed

In addition to reserved words, there are keywords that have special meaning in PL/SQL. PL/SQL keywords can be used for identifiers, but this is not recommended.

Predefined PL/SQL datatypes are grouped into composite, LOB, reference, and scalar type categories.

1. A composite type has internal components that can be manipulated individually, such as the elements of an array, record, or table.
2. A LOB type holds values, called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data. LOB types include BFILE, BLOB, CLOB, and NCLOB.
3. A reference type holds values, called pointers, that designate other program items. These types include REF CURSORS and REFs to object types.
4. A scalar type has no internal components. It holds a single value, such as a number or character string. The scalar types fall into four families, which store number, character, Boolean, and date/time data. The scalar families with their datatypes are:
5. A PL/SQL Number Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT

Returning Functions Syntax Quiz

The next lesson wraps up this module and tells you what to expect in the upcoming module.