Oracle Learning - 10

Oracle/PLSQL: Case Statement

In Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:
CASE expression
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result END
expression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
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.

For Example:
You could use the case statement in an SQL statement as follows:
select table_name,
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;

The above case statement is equivalent to the following IF-THEN-ELSE statement:
IF owner = 'SYS' THEN
result := 'The owner is SYS';
ELSIF owner = 'SYSTEM' THEN
result := 'The owner is SYSTEM'';
ELSE
result := 'The owner is another value';
END IF;

The case statement will compare each owner value, one by one.

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,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END
from all_tables;
With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

Oracle/PLSQL: GOTO Statement

The GOTO statement causes the code to branch to the label after the 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

Oracle/PLSQL: Loop Statement

The syntax for the LOOP statement is:
LOOP
{.statements.}
END LOOP;
You 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.
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:
LOOP
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
In this example, the LOOP would terminate when the monthly_value exceeded 4000.

Read more »

Oracle Learning - 9

Oracle/PLSQL: IS NULL

In other languages, a null value is found using the = null syntax. However in PLSQL to check if a value is null, you must use the "IS NULL" syntax.
To check for equality on a null value, you must use "IS NULL".
For example,
IF Lvalue IS NULL then
.
END IF;
If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.

You can also use "IS NULL" in an SQL statement. For example:
select * from suppliers
where supplier_name IS NULL;
This will return all records from the suppliers table where the supplier_name contains a null value.

Oracle/PLSQL: IS NOT NULL

In other languages, a not null value is found using the != null syntax. However in PLSQL to check if a value is not null, you must use the "IS NOT NULL" syntax.
For example,
IF Lvalue IS NOT NULL then
.
END IF;
If Lvalue does not contain a null value, the "IF" expression will evaluate to TRUE.

You can also use "IS NOT NULL" in an SQL statement. For example:
select * from suppliers
where supplier_name IS NOT NULL;
This will return all records from the suppliers table where the supplier_name does not contain a null value.

Oracle/PLSQL: 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.

Read more »