REQUEST COMPLIMENTARY SQLS*PLUS LICENCE
Oracle IF-THEN-ELSE operator
In Oracle, the IF-THEN-ELSE operator is used to execute code when the condition is TRUE (true), or execute another code when the condition is FALSE (false).
Syntax (IF-THEN)
IF THE CONDITION THEN
{...is executed when the TRUE is true...}
END IF;
Use IF-THEN syntax if you want to execute operators only when the condition is true.
Syntax (IF-THEN-ELSE)
IF THE CONDITION THEN
{...is executed when the TRUE is true...}
ELSE
{...is performed when the FALSE is false...}
END IF;
If you want to execute one set of sentences when the condition is true, use IF-THEN-ELSE syntax, or another set of sentences if the condition is false.
Syntax (IF-THEN-ELSIF)
IF CONDITION1 THEN
{...is performed when CONDITION 1 is true (TRUE)...}
ELSIF CONDITION2 THEN
{...is performed when CONDITION2 is true (TRUE)...}
ELSE
{...is executed when both: NOTICE1 and NOTICE2 are false (FALSE)...}
END IF;
- Use IF-THEN-ELSIF-ELSE syntax if you want to execute one set of sentences when NOTE1 is true (TRUE);
- another set of sentences when NOTE2 is true (TRUE); or a third set of sentences when all previous conditions (i.e.: NOTE1 and NOTE2) are false (FALSE).
Note:
- After the condition is true (TRUE), the IF-Then-Else operator will execute the declared code and will not evaluate the condition further.
- If the condition is false (FALSE), the IF-Then-Else operator’s ELSE part is executed.
- It is important to note that the ELSIF and ELSE parts are additional.
Below is an example of using the IF-Then-Else operator in the Oracle function body:
CREATE OR REPLACE Function IncomeLevel
( name_in IN varchar2 )
RETURN varchar2
IS
monthly_value number(6);
ILevel varchar2(20);
cursor c1 is
SELECT monthly_income
FROM employees
WHERE name = name_in;
BEGIN
open c1;
fetch c1 into monthly_value;
close c1;
IF monthly_value <= 4000 THEN
ILevel := 'Low Income';
ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
ILevel := 'Avg Income';
ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
ILevel := 'Moderate Income';
ELSE
ILevel := 'High Income';
END IF;
RETURN ILevel;
END;
In this example of IF-THEN-ELSE operator, we created IncomeLevel function. It has one parameter name_in and returns varchar2. The function will return ILevel (income level) based on the employee’s name.
PL/SQL tutorial: IF THEN ELSE (IF-ELSE) Statement in Oracle
MORE NEWS
PreambleNoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...
PreambleMongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...
5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...
The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...
PreambleMS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...
First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...
PreambleAtom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...
PreambleMongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...
FlexibilityOne of the most advertised features of MongoDB is its flexibility. Flexibility, however, is a double-edged sword. More flexibility means more...
PreambleSQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...
PreambleWriting an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...
PreambleOracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...
PreambleIBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...
What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....
PreambleIf you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...
PreambleStarting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...