User-Defined Functions «Prev  Next»

Lesson 5Defining functions returning a Boolean value
ObjectiveList uses of a Function that returns a Boolean Value

Oracle Functions returning Boolean Value

As you probably know, Boolean is a datatype that contains a True or False value. Because of this, you can use functions that return a Boolean value in places where you need a condition evaluated. Basically, you use this kind of function within an IF-THEN-ELSE statement, or in a WHILE loop statement. A function helps you encapsulate complex logic and allows you to re-use this complex logic without coding it multiple times.

Oracle Function Examples

Here are some examples of tasks where you might use a function that returns a Boolean value.
  1. You might use a multi-purpose procedure to handle users and roles. This procedure can call a function to check for an existing user with the same name. The procedure can then issue the CREATE USER command only when needed.
  2. When confirming a user's security access to your Forms application, you might use a PL/SQL block that calls a function to perform the needed validation. This function could be called from within all your forms.
  3. Ater entering information into the database, verify that your client's insurance claim falls within certain pre-determined rules for reasonable costs. The complex logic within the function can be re-used by many different applications.

Remember, you cannot use this kind of function in plain SQL commands.You must use them within a PL/SQL block.

Function - Part of Executable Statement

A function is a module that returns data through its RETURN clause, rather than in an 1) OUT or 2) IN OUT argument. Unlike a procedure call, which is a standalone executable statement, a call to a function can exist only as part of an executable statement, such as
  1. an element in an expression or
  2. the value assigned as the default in a declaration of a variable.

Because a function returns a value, it is said to have a datatype. A function can be used in place of an expression in a PL/SQL statement having the same datatype as the function. Functions are particularly important constructs for building modular code. For example, every single business rule or formula in your application should be placed inside a function. Every single-row query should also be defined within a function, so that it can be easily and reliably reused.
Note: Some programmers prefer to rely less on functions, and more on procedures that return status information through the parameter list. If this holds true for you, make sure that your
  1. business rules,
  2. formulas, and
  3. single-row queries
are tucked away into your procedures.


PL/SQL supports a three-value Boolean datatype. A variable of this type can have one of only three values:
  1. TRUE,
  2. FALSE, and
  3. NULL.
Booleans help us write very readable code, especially involving complex logical expressions. Here is an example of a Boolean declaration, along with an assignment of a default value to that variable:
l_eligible_for_discount BOOLEAN :=
customer_in.balance > min_balance AND
customer_in.pref_type = 'MOST FAVORED' AND

The next lesson shows you the syntax of a stored function that returns a Boolean value.