TechFAQ

PL/SQL Tutorials - CONTROL STRUCTURES
In this we try to understand IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF-THEN.

Let me remind you that Boolean means to me not TRUE/FALSE but also NULL. This is true always.

IF-THEN and ELSE when you have a Boolean like condition to validate the data. Try this.

SQL> Begin
2 For I in 1.. 10 loop
3 If mod(i,2) <> 0 then
4 Dbms_output.put_line(' I am an odd number :'||i);
5 else
6 Dbms_output.put_line(' I am an even number :'||i);
7 End if;
8 End loop;
9* end;
SQL> /
I am an odd number :1
I am an even number :2
I am an odd number :3
I am an even number :4
I am an odd number :5
I am an even number :6
I am an odd number :7
I am an even number :8
I am an odd number :9
I am an even number :10

PL/SQL procedure successfully completed.

SQL>

Let you play with IF..THEN, ELSIF..THEN and ELSE.
These control structures give more manageability with data. So we use this when we have more conditions to check to validate data or business requirement or a rule.

SQL> begin
2
3 for i in 1..10 loop
4
5 if i=3 or i=6 or i=9 then
6 dbms_output.put_line(' This is divisible by 3: '||i);
7 elsif i=2 or i=4 or i=6 or i=8 or i=10 then
8 dbms_output.put_line(' This is divisible by 2: '||i);
9 else
10 dbms_output.put_line(' Is this a prime number?? '||i);
11 end if;
12
13 end loop;
14 end;
15 /
Is this a prime number?? 1
This is divisible by 2: 2
This is divisible by 3: 3
This is divisible by 2: 4
Is this a prime number?? 5
This is divisible by 3: 6
Is this a prime number?? 7
This is divisible by 2: 8
This is divisible by 3: 9
This is divisible by 2: 10

PL/SQL procedure successfully completed.

Another example of finding prime numbers?

SQL> ed
Wrote file afiedt.buf

1 begin
2 for i in 1..100 loop
3 if i in (1,5,7) then
4 dbms_output.put_line(' Is this a prime number?? '||i);
5 end if;
6 if i not in (1,5,7) then
7 if mod(i,3)=0 or mod(i,6)=0 or mod(i,9)=0 then
8 null;
9 --dbms_output.put_line(' This is divisible by 3: '||i);
10 elsif mod(i,2)=0 or mod(i,4)=0 or mod(i,8)=0 then
11 null;
12 --dbms_output.put_line(' This is divisible by 2: '||i);
13 elsif mod(i,5)=0 or mod(i,10)=0 then
14 null;
15 --dbms_output.put_line(' This is divisible by 5: '||i);
16 elsif mod(i,7)=0 then
17 null;
18 else
19 dbms_output.put_line(' Is this a prime number?? '||i);
20 end if;
21 end if;
22 end loop;
23* end;
SQL> /
Is this a prime number?? 1
Is this a prime number?? 5
Is this a prime number?? 7
Is this a prime number?? 11
Is this a prime number?? 13
Is this a prime number?? 17
Is this a prime number?? 19
Is this a prime number?? 23
Is this a prime number?? 29
Is this a prime number?? 31
Is this a prime number?? 37
Is this a prime number?? 41
Is this a prime number?? 43
Is this a prime number?? 47
Is this a prime number?? 53
Is this a prime number?? 59
Is this a prime number?? 61
Is this a prime number?? 67
Is this a prime number?? 71
Is this a prime number?? 73
Is this a prime number?? 79
Is this a prime number?? 83
Is this a prime number?? 89
Is this a prime number?? 97

PL/SQL procedure successfully completed.

SQL>

Now let us know how to declare a variable and print the value for the variable using PLSQL.

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_global_name varchar2(100);
3 begin
4 select global_name
5 into v_global_name
6 from global_name;
7 dbms_output.put_line(v_global_name);
8* end;
SQL> /
GENSTG

PL/SQL procedure successfully completed.

SQL>

A variable can be declared in the declare section.
The same select statement you have used. But there is one new key word INTO.
One can assign value to the variable which is also called initialization of the variable. Let us try with an example to assign value and print that value.

SQL> set serveroutput on size 1000000
SQL> declare
2
3 l_variable number;
4
5 begin
6
7 l_variable:=2;
8
9 dbms_output.put_line(l_variable);
10
11 end;
12 /
2

PL/SQL procedure successfully completed.

SQL>

SQL> ed
Wrote file afiedt.buf

1 declare
2 l_variable number := 2;
3 begin
4 dbms_output.put_line(l_variable);
5* end;
6 /
2

PL/SQL procedure successfully completed.

SQL>

So you can do this way also and there are many ways. You can do all these things with nobody?s help when once you have a break to start writing the code. No barriers than the barriers laid down by Oracle and PLSQL engine. You are the Tensing Narkey, self taught mountaineer to conquer.