User-Defined Functions «Prev  Next»
Lesson 6Syntax of functions returning a Boolean value
Objective Identify correct syntax of a function that returns a Boolean value.

Syntax for Oracle 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.

Returning Functions Syntax Quiz

Click the Quiz link below to test your knowledge of the material covered in this module.
Returning Functions Syntax-Quiz

Reserved Words

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.

Overview of Predefined PL/SQL Datatypes

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

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