Oracle Functions returning Boolean Value

In Oracle PL/SQL, a function that returns a Boolean value can have a variety of uses. They often serve as decision-making mechanisms, acting as flag bearers in a logic execution flow. However, it is important to note that Boolean values, and hence functions that return Boolean values, can only be used in PL/SQL, not in SQL statements.
Here are some key uses for functions that return Boolean values in Oracle PL/SQL:

1. Conditional Statements: These functions can be employed in conditional expressions such as IF, ELSE, and ELSIF within PL/SQL blocks. They provide a clean, readable way to encapsulate complex conditional logic.
2. Loop Control: Boolean functions can also be used as control mechanisms in iterative loops like WHILE and FOR loops, acting as a termination condition.
3. Input Validation: In cases where user input or database data needs to be validated, functions returning a Boolean value can be used to create reusable validation checks.
4. Flow Control: They can be leveraged as switch operators in the CASE statement, aiding in controlling the program flow based on different conditions.
5. Data Filtering: Although Boolean values can't be used directly in SQL statements, Boolean functions can be employed within a PL/SQL context to conditionally filter data from cursors or collections.
6. Access Control: Functions returning Boolean values can be used for determining user permissions or access rights. This is useful in scenarios where there are different user roles and access levels.
7. Business Rules Implementation: Complex business rules can be encapsulated in Boolean functions to create more maintainable and reusable code. These rules can be validated at any time by simply invoking the function.
8. Assertions: Boolean functions can serve as assertion checks in the code, validating that certain conditions are met before allowing the code to proceed.

While designing such functions, it is crucial to ensure that the function is deterministic, meaning it should provide the same output for the same set of inputs, to maintain data consistency and to prevent any unforeseen side effects.
Remember that a Boolean function in PL/SQL can only return three possible values: TRUE, FALSE, or NULL. Therefore, the use of such functions must be adequately handled to cover all three scenarios. Especially, the NULL case should be addressed carefully, as it can sometimes lead to unexpected results.

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.

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.

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:

DECLARE
l_eligible_for_discount BOOLEAN :=
customer_in.balance > min_balance AND
customer_in.pref_type = 'MOST FAVORED' AND
customer_in.disc_eligibility;

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