IF-THEN-ELSE Statement
There are three different syntaxes for these types of statements.
Syntax #1: IF-THEN
IF condition THEN
{...statements...}
END IF;
Syntax #2: IF-THEN-ELSE
IF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
Syntax #3: IF-THEN-ELSIF
IF condition THEN
{...statements...}
ELSIF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
Here is an example of a function that uses the IF-THEN-ELSE statement:
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, we've created a function called IncomeLevel. It has one parameter called name_in and it returns a varchar2. The function will return the income level based on the employee's name
Case Statement
Syntax
The syntax for the case statement is:CASE [ expression ]expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.
result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.
Note
If no condition is found to be true, then the case statement will return the value in the ELSE clause.If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.
You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.
Applies To
- Oracle 11g, Oracle 10g, Oracle 9i
For Example
You could use the case statement in an SQL statement as follows: (includes the expression clause)select table_name,Or you could write the SQL statement using the case statement like this: (omits the expression clause)
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
select table_name,The above two case statements are equivalent to the following IF-THEN-ELSE statement:
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from all_tables;
IF owner = 'SYS' THENThe case statement will compare each owner value, one by one.
result := 'The owner is SYS';
ELSIF owner = 'SYSTEM' THEN
result := 'The owner is SYSTEM'';
ELSE
result := 'The owner is another value';
END IF;
One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.
Your SQL statement would look as follows:
select table_name,With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;
For Example
Here is an example that demonstrates how to use the case statement to compare different conditions:select
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
from suppliers;
Frequently Asked Questions
Question: Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.
Answer: Yes, below is an example of a case statement that evaluates two different fields.
select supplier_id,So if supplier_name field is IBM and the supplier_type field is Hardware, then the case statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the casestatement will return South office.
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;
GOTO Statement
For example:
GOTO label_name;Then later in the code, you would place your label and code associated with that label.
Label_name: {statements}
Loop Statement
LOOPYou would use a LOOP statement when you are not sure how many times you want the loop body to execute and you want the loop body to execute at least once.
{.statements.}
END LOOP;
The LOOP statement is terminated when it encounters either an EXIT statement or when it encounters an EXIT WHEN statement that evaluated to TRUE.
Let's take a look at an example:
LOOPIn this example, the LOOP would terminate when the monthly_value exceeded 4000.
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
FOR Loop
FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;
You would use a FOR Loop when you want to execute the loop body a fixed number of times.
Let's take a look at an example.
FOR Lcntr IN 1..20
LOOP
LCalc := Lcntr * 31;
END LOOP;
This example will loop 20 times. The counter will start at 1 and end at 20.
The FOR Loop can also loop in reverse. For example:
FOR Lcntr IN REVERSE 1..15This example will loop 15 times. The counter will start at 15 and end at 1. (loops backwards)
LOOP
LCalc := Lcntr * 31;
END LOOP;
CURSOR FOR Loop
FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;
You would use a CURSOR FOR Loop when you want to fetch and process every record in a cursor. The CURSOR FOR Loop will terminate when all of the records in the cursor have been fetched.
Here is an example of a function that uses a CURSOR FOR Loop:
CREATE OR REPLACE Function TotalIncome
( name_in IN varchar2 )
RETURN varchar2
IS
total_val number(6);
cursor c1 is
select monthly_income
from employees
where name = name_in;
BEGIN
total_val := 0;
FOR employee_rec in c1
LOOP
total_val := total_val + employee_rec.monthly_income;
END LOOP;
RETURN total_val;
END;
In this example, we've created a cursor called c1. The CURSOR FOR Loop will terminate after all records have been fetched from the cursor c1.
While Loop
The syntax for the WHILE Loop is:
WHILE conditionYou would use a WHILE Loop when you are not sure how many times you will execute the loop body. Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.
LOOP
{.statements.}
END LOOP;
Let's take a look at an example:
WHILE monthly_value <= 4000In this example, the WHILE Loop would terminate once the monthly_value exceeded 4000.
LOOP
monthly_value := daily_value * 31;
END LOOP;
Repeat Until Loop
Oracle doesn't have a Repeat Until loop, but you can emulate one. The syntax for emulating a REPEAT UNTIL Loop is:
LOOPYou would use an emulated REPEAT UNTIL Loop when you do not know how many times you want the loop body to execute. The REPEAT UNTIL Loop would terminate when a certain condition was met.
{.statements.}
EXIT WHEN boolean_condition;
END LOOP;
Let's take a look at an example:
LOOPIn this example, the LOOP would repeat until the monthly_value exceeded 4000.
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
Exit Statement
The syntax for the EXIT statement is:
EXIT [WHEN boolean_condition];The EXIT statement is most commonly used to terminate LOOP statements.
Let's take a look at an example:
LOOPIn this example, the LOOP would terminate when the monthly_value exceeded 4000.
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
0 comments:
Post a Comment