|Lesson 6||Syntax 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.
- If it is Friday, the function returns
- Otherwise, it returns
CREATE OR REPLACE FUNCTION TGIF_TEST
RETURN BOOLEAN AS
IF TO_CHAR(SYSDATE,'DAY') = 'FRIDAY' THEN
If needed, you can declare a variable within the function that has a Boolean datatype.
Returning Functions Syntax Quiz
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.
-- 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.
- A composite type has internal components that can be manipulated individually, such as the elements of an array, record, or table.
- 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.
- A reference type holds values, called pointers, that designate other program items. These types include REF CURSORS and REFs to object types.
- 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:
- 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.