PL/SQL Boolean

Introduction to PL/SQL Boolean

PL/SQL Boolean is the scalar datatype present in PL/ SQL which can store the logical values which can be either TRUE or FALSE. Many systems consider 1 value as the TRUE and 0 value as FALSE. The use of Boolean values is mainly done while using conditional expressions and statements where if the specified condition evaluates to true then only the block of code mentioned inside the conditional statement is executed else not. Variables used for storing the values can store the value of ay data type such as CHAR, NUMBER, BOOLEAN, etc. When Boolean datatype is assigned to the variable then it can either store true or false values in it.

In this article, we will study the Boolean data type in PL/ SQL and how it can be used in conditions and query statements for condition specification and its implementation along with the help of certain examples.

Syntax:

The syntax of declaring a variable with Boolean datatype for storing Boolean values inside the memory is as shown below –

Name of variable BOOLEAN;

When name of the variable is the variable name that we are going to declare as a Boolean data type variable. Besides this variable declaration in Boolean datatype, the Boolean values are also used for comparing the values of columns with the Boolean value of true or false while retrieving the column values. Note that the Boolean variable specified here can also have a NULL value instead of TRUE or FALSE. The default value of the Boolean variable when not initialized is NULL.

These conditions can also be used in where clause for constraint or restriction specification or even in the select clause while retrieving the values true or false depending on the fulfillment of the expression condition inside the values being retrieved in the select statement. The Boolean datatype is not available in SQL, it’s only present in PL/SQL, and for SQL it is only present in oracle version 12cR1 and later versions.

Boolean representation in PL/SQL queries –

The Boolean values are stored in the form of tiny int with one byte either 0 or 1 value in it. 1 value corresponds to TRUE value in Boolean while 0 is for FALSE value in PL/ SQL. Let us consider a small example where we will try to retrieve different values or true and false Boolean values in small caps or in capitals or in any case. For implementing this consider the following statement in PL/ SQL which has the output as 0s and 1s because true translates to 1 and false to 0.

SELECT true, TRUE, True, false, FALSE, False;

The execution of the above statement in PL/SQL is as shown below showing 1 when retrieved true Boolean value and 0 when retrieved false Boolean value.

Examples

Let us now consider certain examples where we will retrieve the rows of a particular table only when a certain logical condition evaluates to true. The Boolean values are mostly used with logical conditions as they may evaluate to true or false which are ultimately represented as 1 and 0 respectively. Let us understand the implementation of Boolean values with logical conditions with the help of examples.

Test data:

Consider one table whose name is customers_details that stores the information about all the customers and their contact details. The contents of the table can be checked by using the following query statement –

SELECT * FROM [customers_details];

The output of the execution of the above query statement is as shown below which shows the rows contained by customers_details table –

We will consider this table to study the implementation of Boolean values with logical conditions in PL/ SQL.

Example #1

Suppose we want to retrieve only those records involving f_name, l_name, store_id, and mobile_number whose store_id is not equal to VEGETABLES. In this case, we can make the use of the following query statement with the logical condition as store id should not be equal to vegetables which will evaluate to Boolean value true or false. If the condition evaluates to true for that row only then it will be taken for the final result or else when it’s false then record is not included in the final result –

SELECT f_name, l_name, store_id, mobile_number FROM [customers_details] WHERE store_id <> "VEGETABLES";

The output of the execution of the above query statement is as shown below displaying all the rows of customers whose store_id is not equal to VEGETABLES –

Example #2

We can retrieve the result showing the 1s and 0s for true and false evaluation of the same condition used above by using the condition in the select statement instead of where clause as shown in the below query statement. Let us consider an example where we will use the NOT EQUAL operator in the SELECT statement and try to retrieve the value which we get after comparing the store id column value with electronics using the NOT EQUAL operator. This means that if the store id will not be equal to ELECTRONICS then it will return true or Boolean representation of true is also sometimes considered as 1. When the store id will be equal to ELECTRONICS the returned value will be false or 0 which is also a representation of the Boolean value of false. Our query statement will then become –

SELECT f_name, l_name, store_id != "ELECTRONICS", mobile_number FROM [customers_details] ;

The output of the above statement will retrieve 1 for customers whose store id is not equal to electronics and 0 for electronics store id for customers as shown in the below image where 0 corresponds to false and 1 corresponds to true Boolean value –

Conclusion

We can make use of Boolean values in PL/ SQL for mentioning the same in conditional statements while using the PL/SQL programs or blocks of coding such as if condition. We can also make the use of logical operators and conditions which evaluate to either true or false value which will be deciding factor of whether to include the row in the final result set or not. Internally a Boolean value is stored in the form of tiny int which is one-byte value which means that 0 will represent FALSE value while 1 will be for TRUE value respectively.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL Boolean” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PL/SQL Collections
2. Loops in PL/SQL
3. PL/SQL Data Types
4. Triggers in PL/SQL