cheack whether given number is palindrome or not.

write a pl/sql program to cheack whether given number is palindrome or not.

set serveroutput on;
Declare
a number(4);
b number(4);
n number(4);
Begin
a:=&a;
b:=0;
n:=a;
while n <>0
loop
b:=10*b+n mod 10;
n:=n/10;
end loop;
if a=b then
dbms_output.put_line('given number is palindromial number ');
else
dbms_output.put_line('given number is not palindromial number ');
end if;
end;
/

Sum of n numbers

set serveroutput on;
Declare
a number(4);
n number(4);
s number:=0;
Begin
n:=&n;
a:=1;
loop
s:=s+a;
exit when(a=n);
a:=a+1;
end loop;
dbms_output.put_line('Sum of n numbers = '||s);
end;
/

Fibonacci Series of N term

set serveroutput on;
Declare
n number(4);
a number:=1;
b number:=1;
c number:=0;
Begin
n:=&n;
dbms_output.put_line('Fibonacci Series is : ');
for i in 1..n
loop
c:=a+b;
a:=b;
b:=c;
dbms_output.put_line(c);
end loop;
end;
/

Sum of 10 numbers

set serveroutput on;
Declare
a number(4);
s number:=0;
Begin
a:=1;
loop
s:=s+a;
exit when(a=10);
a:=a+1;
end loop;
dbms_output.put_line('Sum of 10 numbers = '||s);
end;
/

Fibonacci series

set serveroutput on;
Declare
a number(4);
b number(4);
c number(4);
i number(4);
Begin
a:=0;
b:=1;
dbms_output.put_line('Fibonacci series: =' ||a||','||b);
i:=0;
while i<=7
loop
c:=a+b;
a:=b;
b:=c;
dbms_output.put_line(c);
i:=i+1;
end loop;
end;
/

Sum of 100 Numbers

 Sum of 100 Numbers

  declare
  a number;
  s number default 0;
  begin
  a:=1;
  loop
  s:=s+a;
  exit when(a=100);
  a:=a+1;
 end loop;
 dbms_output.put_line('Sum of 100 numbers = '||s);
 end;
SQL> /

Sum of 100 numbers = 5050

Greatest of Three Numbers

 Greatest of Three Numbers


  declare
  a number;
  b number;
  c number;
  begin
  a:=&a;
  b:=&b;
  c:=&c;
 if(a>b) and (a>c) then
 dbms_output.put_line('A is greater ');
 elsif (b>a) and (b>c) then
 dbms_output.put_line(' B is Greater ');
 else
 dbms_output.put_line('C is Greater');
 end if;
 end;
SQL> /
Enter value for a: 20
old   a:=&a;
new   a:=20;

Enter value for b: 15
old  b:=&b;
new  b:=15;

Enter value for c: 3
old  c:=&c;
new  c:=3;

A is greater

Sum of Odd Numbers using for loop

Sum of Odd Numbers using for loop


 1  declare
 2     n number;
 3     sum1 number default 0;
 4     endvalue number;
 5  begin
 6     endvalue:=&endvalue;
 7     n:=1;
 8     for n in 1..endvalue
 9     loop
10         if mod(n,2)=1
11           then
12           sum1:=sum1+n;
13         end if;
14     end loop;
15    dbms_output.put_line(' sum  =  '||sum1);
16* end;


SQL> /
Enter value for endvalue: 30
old   6: endvalue:=&endvalue;
new   6: endvalue:=30;

sum  =  225

Addition of Two Numbers

 Addition of Two Numbers
  declare
  a number;
  b number;
  c number;
  begin
  a:= &a;
  b:= &b;
  c:= a+b;
  dbms_output.put_line('Sum of '|| a ||' and  ' || b ||' is ' || c);
 10* end;

SQL> /
Enter value for a: 20
old   a:= &a;
new   a:= 20;
Enter value for b: 30
old   b:= &b;
new   b:= 30;


Sum of 20 and 30 is 50

Locking of PL/SQL

Locking
It is important from database management system’s point of view to ensure that two user are
not modifying the same data at the time in a destructive manner.
Let us consider the following example to understand what will happen if two users are trying to update the same data at the same time.
Assume we have PRODUCTS table with details of products. Each product is having quantity on
hand (QOH). Transactions such as sales and purchases are going to modify QOH column of
the table.
The following are the steps that might take place when two transactions – one sale and one
purchase – are taking place.

1. Assume QOH of product 10 is 20 units.
2. At 10:00 USER1 has sold 5 units and updated QOH as follows but has not committed the
change. After UPDATE command QOH will be 15.
update products set qoh = qoh – 5 where prodid = 10;
3. At 10:11 USER2 has purchased 10 units and updated QOH as follows and committed. After
UPDATE command QOH will be 25 as 10 it added to 15.
update products set qoh = qoh + 10 where prodid = 10;
4. If at 10:12 USER1 has rolled back the UPDATE then data that was there before the
UPDATE should be restored i.e.20. But the actual data should be 30 as we added 10 units
to it at 10:11 and committed.
As you can see in the above example, if two users are trying to update the same row at the
same time the data may be corrupted. As shown in the above example at the end of the
process QOH should be actually 30 but it is not only 20.
It is possible to ensure that two transactions are interfering with each other by locking the
rows that are being modified so that only one transaction at a time can make the change.
Oracle ensures that only one transaction can modify a row at a time by locking the row once
the row is updated. The lock will remain until the transaction is completed.
Oracle also ensures that other users will not see the changes that are not committed. That
means if transaction T1 has updated a row then until the transaction is committed no other
transaction in the system will see the changes made by T1. Instead other transactions will see
only the data that was existing before the change was made.
The following scenario will illustrate the process in detail.
1. Assume QOH of product 10 is 20.
2. Transaction T1 has issued UPDATE command to update QOH of product 10. Oracle locks
the row that is updated and does not allow other transactions to update the row. However,
it is possible to read the data from the row.
update products set qoh = qoh + 5 where prodid = 10;

3. If T1 has issued SELECT to retrieve data then it will get 25 in QOH of product 10.
4. If T2 has issued SELECT command, it will see only 20 in QOH of product 10. This is
because no uncommitted changes will be available to other transactions.
5. If T2 is trying to update product 10 then Oracle will cause transaction T2 to wait until
transaction T1 (that holds lock on this row) is completed. Oracle will wait for lock to be
released indefinitely.
6. If transaction T1 is committed then change is made permanent and lock will be released.
Now it is possible for other transactions to see the updated data and even update the row
if required.
The following are the important points to be remembered about Oracle’s locking
mechanism.
* Locking in Oracle is automatic and transparent. That means we never need to ask Oracle
to lock row that is being modified. Locking is transparent means user is not aware of the
locking process. It happens automatically and in the background.
* Oracle locks the row that is being updated. That means locking is row-level. Other levels
that are in user are - page-level and table-level.
* Oracle releases locks held by a transaction when transaction is completed either
successfully – using COMMIT – or unsuccessfully – using ROLLBACK.
* If a transaction is trying to update a row and if row is already locked then Oracle will wait
for the row that is locked to be unlocked indefinitely. It is because of the fact that rows are
locked for a small duration in a typical production system. So Oracle prefers to wait to
cause any error.
* It is possible to lock table manually using LOCK TABLE command.
Locking the rows that are being updated is an important part of Oracle. It ensures that no two
transactions can update the same row at the same time. Locking mechanism is followed by all
database management systems. But some smaller database management systems follow
page-level locking where not the exact row that is being modified is locked instead the entire
page in which the row exists is locked.

VARIABLE DECLARATIONS

VARIABLE DECLARATIONS
The complete syntax for a variable declaration is as follows:
[attribute_list] [accessibility] [Shared] [Shadows] [ReadOnly]
Dim [WithEvents] name [(bounds_list)] [As [New] type]
[= initialization_expression]
All declarations have only one thing in common: They contain a variable’s name. Other than the
name, different declarations may have nothing in common. Variable declarations with different
forms can use or omit any other piece of the general declaration syntax. For example, the following
two declarations don’t share a single keyword:
Dim i = 1 ‘ Declare private Integer named i. (Option Explicit Off)
Public j As Integer ‘ Declare public Integer named j.
The many variations supported by a variable declaration make the general syntax rather
intimidating. In most cases, however, declarations are straightforward. The previous two
declarations are fairly easy to understand.

VARIABLES of PL/SQL

VARIABLES
Variables are among the most fundamental building blocks of a program. A variable is a program object that stores a value. The value can be a number, letter, string, date, structure containing other values, or an object representing both data and related actions.
When a variable contains a value, the program can manipulate it. It can perform arithmetic operations on numbers, string operations on strings (concatenation, calculating substrings,
f nding a target within a string), date operations (f nd the difference between two dates, add a
time period to a date), and so forth.

Four factors determine a variable’s exact behavior:
➤ Data type determines the kind of data it can hold (integer, character, string, and so forth).
➤ Scope def nes the code that can access the variable. For example, if you declare a variable
inside a For loop, only other code inside the For loop can use the variable. If you declare a
variable at the top of a subroutine, all the code in the subroutine can use the variable.
➤ Accessibility determines what code in other modules can access the variable. If you declare
a variable at the module level (outside of any subroutine in the module) and you use the
Private keyword, then only the code in the module can use the variable. If you use the Public
keyword, then code in other modules can use the variable as well.
➤ Lifetime determines how long the variable’s value is valid. A variable inside a subroutine
that is declared with a normal Dim statement is created when the subroutine begins and is
destroyed when it exits. If the subroutine runs again, it creates a new copy of the variable
and its value is reset. If the variable is declared with the Static keyword, however, the same instance of the variable is used whenever the subroutine runs. That means the variable’s
value is preserved between calls to the subroutine.
For example, a variable declared within a subroutine has scope equal to the subroutine. Code outside of the subroutine cannot access the variable. If a variable is declared on a module level outside any subroutine, it has module scope. If it is declared with the Private keyword, it is accessible only to code within the module. If it is declared with  the Public keyword, then it is also accessible to code outside of the module.
Visibility is a concept that combines scope, accessibility, and lifetime. It determines whether a certain piece of code can use a variable. If the variable is accessible to the code, the code is within the variable’s scope, and the variable is within its lifetime (has been created and not yet destroyed), then the variable is visible to the code.

Types of constraints

Types of constraints
Constraints can be given at two different levels. If the constraint is related to a single column
the constraint is given at the column level otherwise constraint is to be given at the table
level. Base on the where a constraint is given, constraint are of two types:
Column Constraints
Table Constraints

Column Constraint
A constraint given at the column level is called as Column Constraint. It defines a rule for a
single column. It cannot refer to column other than the column at which it is defined. A typical
example is PRIMARY KEY constraint when a single column is the primary key of the table.

Table Constraint
A constraint given at the table level is called as Table Constraint. It may refer to more than
one column of the table. A typical example is PRIMARY KEY constraint that is used to define
composite primary key. A column level constraint can be given even at the table level, but a
constraint that deals with more than one column must be given only at the table level.
The following is the syntax of CONSTRAINT clause used with CREATE TABLE and ALTER TABLE
commands.
[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
| REFERENCES [schema.] table [(column)]
 [ON DELETE CASCADE]
| CHECK (condition) }

The following is the syntax of table constraint.
[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
| FOREIGN KEY (column [,column] ...)
 REFERENCES [schema.] table [(column [,column] ...)]
 [ON DELETE CASCADE]
| CHECK (condition) }
The main difference between column constraint and table constraint is that in table constraint
we have to specify the name of the column for which the constraint is defined whereas in
column constraint it is not required as constraint is given on immediately after the column.

NOT NULL Constraint
Used to prevent any null value from entering into column. This is automatically defined for
column with PRIMARY KEY constraint.
The following example shows how you can define course name as not null column using NOT
NULL constraint.
CREATE TABLE COURSES
( ...,
 name varchar2(20)
 CONSTRAINT courses_name_nn NOT NULL,
 ...
);
CONSTRAINT option is used to given a name to constraint. The convention followed here is
TABLENAME_COLUMN_TYPE. 

PRIMARY KEY Constraint
This constraint is used to define the primary key of the table. A primary key is used to
uniquely identify rows in a table. There can be only one primary key in a table. It may consist
of more than one column. If primary key is consisting of only one column, it can be given as
column constraints otherwise it is to be given as table constraint.
Note: You have to use table constraint to define composite primary key.
Oracle does the following for the column that has PRIMARY KEY constraint.
Creates a unique index to enforce uniqueness. We will discuss about indexes later in this
book.
Defines NOT NULL constraint to prevent null values.
The following example shows how to use PRIMARY KEY constraint at column level.
CREATE TABLE COURSES
( ccode varchar2(5) CONSTRAINT courses_pk PRIMARY KEY,
 ... );

The following example shows how to define composite primary key using PRIMARY KEY
constraint at the table level.
CREATE TABLE COURSE_FACULTY
 ( ...,
 CONSTRAINT COURSE_FACULTY_PK PRIMARY KEY (ccode, faccode)
 );

UNIQUE Constraint
Enforces uniqueness in the given column(s). Oracle automatically creates a unique index for
this column.
The following example creates unique constraint on NAME column of COURSES table.
CREATE TABLE courses
( ... ,
 name varchar2(20)
 CONSTRAINT courses_name_u UNIQUE,
 ... );
If two or more columns collective should be unique then UNIQUE constraint must be given at
the table level.

FOREIGN KEY Constraint
A foreign key is used to join the child table with parent table. FOREIGN KEY constraint is used
to provide referential integrity, which makes sure that the values of a foreign key are derived
from parent key.  It can be defined either at the table level or at the column level.
If a foreign key is defined on the column in child table then Oracle does not allow the parent
row to be deleted, if it contains any child rows. However, if ON DELETE CASCADE option is
given at the time of defining foreign key, Oracle deletes all child rows while parent row is
being deleted.
The following example defines foreign key constraint for CCODE of COURSE_FACULTY table.
CREATE TABLE course_faculty
(ccode varchar2(5)
 CONSTRAINT course_faculty_ccode_fk REFERENCES  courses(ccode),
 ...
);
Note: When the name of the column in the referenced table is same as the foreign key then
column need not be given after the table name. It means  REFERENCES courses in the
above example will suffice.

Table level constraint is used when foreign key is a composite foreign key.
ON DELETE CASCADE option
As mentioned earlier, after a foreign key is defined, Oracle will NOT allow any parent row to be
deleted if it has dependent rows in the child table.
For example, if CCODE in COURSE_FACULTY table is defined as foreign key referencing CCODE
column of COURSES table then it is NOT possible to delete rows from COURSES table if
dependent rows exists in COURSE_FACULTY table.
However, by using ON DELETE CASCADE it is possible to delete all child rows while parent row
is being deleted.
The following code shows how to use ON DELETE CASCADE option.
CREATE TABLE course_faculty
(ccode varchar2(5)
 CONSTRAINT course_faculty_ccode_fk REFERENCES  courses(ccode)
 ON DELETE CASCADE,
 ...
);

CHECK Constraint
Defines the condition that should be satisfied before insertion or updation is done.
The condition used in CHECK constraint may NOT contain:
A reference to pseudo column SYSDATE
Subquery
If it is given as column constraint, it can refer only to current column. But if it is given as table
constraint, it can refer to more than one column of the table. In neither case it can refer to a
column of other tables.
The following example shows how to create CHECK constraint to make sure GRADE column of
COURSE_FACULTY contains letters A, B and C only.

CREATE TABLE course_faculty
 ( ...,
 grade char(1) CONSTRAINT course_faculty_grade_chk
 CHECK ( grade in (‘A’,’B’,’C’) ),
 ...
 );
The above CHECK constraint does not allow any other characters other than A, B and C. It
must be noted that character comparison is always case sensitive. So to ignore case
differences you can convert GRADE to uppercase before comparison made as follows:
CREATE TABLE course_faculty
 ( ...,
 grade char(1) CONSTRAINT course_faculty_grade_chk
 CHECK ( upper(grade)  in (‘A’,’B’,’C’) ),
 ...
 );
The following is an example of CHECK constraint at table level. The constraint makes sure the
starting date (STDATE) of a batch is less than or equal to ending date (ENDDATE) of the
batch.
CREATE TABLE batches
 ( ...,
 CONSTRAINT batches_dates_chk
 CHECK ( stdate <= enddate),
 );

Looping Statements / Iterative Statements:

Looping Statements / Iterative Statements:
'A loop' is a part of code of a program which is executed repeatedly. A loop is used using condition. The repetition is done until condition becomes true.
A loop declaration and execution can be done in following ways:-
1. Check condition to start a loop
2. Initialize loop with declaring a variable.
3. Executing statements inside loop.
4. Increment or decrement of value of a variable.

Types of looping statements:
Basically, the types of looping statements depend on the condition checking mode. Condition checking can be made in two ways as: Before loop and after loop. So, there are two types of looping statements.
1. Entry controlled loop
2. Exit controlled loop

1. Entry controlled loop:
In such type of loop, the test condition is checked first before the loop is executed.
Some common examples of this looping statements are :
a. while loop
b. for loop

2. Exit controlled loop :
In such type of loop, the loop is executed first. Then condition is checked after block of statements are executed. The loop executed at least one time compulsorily.
Some common example of this looping statement is:
a. do-while loop

While loop:
This is an entry controlled looping statement. It is used to repeat a block of statements until condition becomes true.

Syntax:
while(condition)
{
statements;
increment/decrement;
}
In above syntax, the condition is checked first. If it is true, then the program control flow goes inside the loop and executes the block of statements associated with it. At the end of loop increment or decrement is done to change in variable value. This process continues until test condition satisfies.

Program :
#include <stdio.h>
#include <conio.h>
void main()
{
int a;
clrscr();
a=1;
while(a<=5)
{
printf("\n Programming in C");
a+=1;     // i.e. a = a + 1
}
getch();
}


Output :
 Programming in C
 Programming in C
 Programming in C
 Programming in C
 Programming in C_


For loop:
This is an entry controlled looping statement.
In this loop structure, more than one variable can be initialized. One of the most important features of this loop is that the three actions can be taken at a time like variable initialization, condition checking and increment/decrement. The for loop can be more concise and flexible than that of while and do-while loops.
Syntax:

for(initialization; test-condition; increament/decreament)
{
statements;
}

In above syntax, the given three expressions are seperated by ';' (Semicolon)

Features :
1. More concise
2. Easy to use
3. Highly flexible
4. More than one variable can be initilized.
5. More than one increments can be applied.
6. More than two conditions can be used.

Program :
#include <stdio.h>
#include <conio.h>
void main()
{
int a;
clrscr();
for(i=0; i<5; i++)
{
printf("\n Programming in C");
}
getch();
}

Output :
 Programming in C
 Programming in C
 Programming in C
 Programming in C
 Programming in C_

Do-While loop:
This is an exit controlled looping statement.
Sometimes, there is need to execute a block of statements first then to check condition. At that time such type of a loop is used. In this, block of statements are executed first and then condition is checked.

Syntax:
do
{
statements;
(increment/decrement);
}while(condition);

In above syntax, the first the block of statements are executed. At the end of loop, while statement is executed. If the resultant condition is true then program control goes to evaluate the body of a loop once again. This process continues till condition becomes true. When it becomes false, then the loop terminates.
Note: The while statement should be terminated with ; (semicolon).

Program :
#include <stdio.h>
#include <conio.h>
void main()
{
int a;
clrscr();
a=1;
do
{
printf("\n Programming in C");
a+=1;      // i.e. a = a + 1
}while(a<=5);
a=6;
do
{
printf("\n\n Programming in C");
a+=1;      // i.e. a = a + 1
}while(a<=5);
getch();
}

Output:
 Programming in C
 Programming in C
 Programming in C
 Programming in C
Programming in C

 Programming in C_

Data Constraints

Data Constraints
Constraints are used to implement standard 
and business rules. Data integrity of the database must be maintained. In order to ensure
data has integrity we have to implement certain rules or constraints. As these constraints are
used to maintain integrity they are called as integrity constraints.
Standard rules
Standard constraints are the rules related to primary key and foreign key. Every table must
have a primary key. Primary key must be unique and not null. Foreign key must derive its
values from corresponding parent key. These rules are universal and are called as standard
rules.
Business rules
These rules are related to a single application. For example, in a payroll application we may
have to implement a rule that prevents any row of an employee if salary of the employee is
less than 2000. Another example is current balance of a bank account
Must be greater than or equal to 500.
Once the constraints are created, Oracle server makes sure that the constraints are not
violated whenever a row is inserted, deleted or updated. If constraint is not satisfied then the
operation will fail.
Constraints are normally defined at the time of creating table. But it is also possible to add
constraints after the table is created using ALTER TABLE command. Constraints are stored in the Data Dictionary (a set of tables which stores information regarding database).
Each constraint has a name; it is either given by user using CONSTRAINT option or assigned
by system. In the later case, the name is SYS_Cn; where n is a number.

Note: It is recommended that you use constraint name so that referring to constraint will be
easier later on.

Count Prime 1 to 100 in PL/SQL Program

set serveroutput on;
Declare
i number(4);
j number(4);
prime number:=1;
count number:=0;
Begin
i:=1;
while i<=100
loop
prime:=1;
j:=2;
while j<=i/2
loop
if i mod j=0 then
prime:=0;
end if;
j:=j+1;
end loop;
if prime=1 then
count:=count+1;
end if;
i:=i+1;
end loop;
dbms_output.put_line('counting the prime number is = '||count);
End;
/

AirthematicOperation

set serveroutput on;
Declare
a number(4);
b number(4);
c number(4);
choice number(4);
wrong_choice exception;
Begin
a:=&a;
b:=&b;
choice:=&choice;
case choice
when 1 then 
c:=a+b;
dbms_output.put_line('Addition is ' ||c);
when 2 then 
c:=a-b;
dbms_output.put_line('Subtraction is ' ||c);
when 2 then 
c:=a*b;
dbms_output.put_line('Multiplication is ' ||c);
when 4 then 
c:=a/b;
dbms_output.put_line('Division is ' ||c);
else
raise wrong_choice;
end case;
exception
when wrong_choice then
dbms_output.put_line('Please enter valid choice');
end;
/

DECISION STATEMENTS

DECISION STATEMENTS

A decision or conditional statement represents a branch in the program. It marks a place

where the program can execute one set of statements or another, or possibly no statements at all, depending on some condition. These include several kinds of If statements, Choose

statements, and Select Case statements.

Single-Line If Then

The single-line If Then statement has two basic forms. The f rst allows the program to execute a

single statement if some condition is True. The syntax is as follows:

If condition Then statement

If the condition is True, the program executes the statement. In the most common form of singleline If Then statements, the statement is a single simple command (such as assigning a value to a

variable or calling a subroutine).

The single-line If Then statement can also include Else If clauses. For example, the following code

examines the variable X. If X is 1, the program sets variable txt to “One.” If X has the value 2, the

program sets txt to “Two.” If X is not 1 or 2, the program sets txt to a question mark.

Dim txt As String

If X = 1 Then txt = “One” Else If X = 2 Then txt = “Two” Else txt = “?”

The code can include as many Else If clauses as we like. However, confusing code such as the

preceding example can lead to puzzling bugs that are easy to avoid if we use multiline If Then

statements instead.

Multiline If Then

A multiline If Then statement can execute more than one line of code when a condition is True.

The syntax for the simplest form of the multiline If Then statement is as follows:

If condition Then

 statements ...

End If

If the condition is True, the program executes all the commands that come before the End If

statement.

Like the single-line If Then statement, the multiline version can include Else If and Else clauses.

For possibly historical reasons, ElseIf is spelled as a single word in the multiline If Then statement.

The syntax is as follows:

If condition1 Then

 statements1 ...

ElseIf condition2

 statements2 ...

Else

 statements3 ...

End If

If the f rst condition is True, the program executes the f rst set of statements. If the f rst condition is

False, the code examines the second condition and, if that one is True, the code executes the second

set of statements. The program continues checking conditions until it f nds one that is True and it

executes the corresponding code.

If the program reaches an Else statement, it executes the corresponding code. If the program reaches the End If statement without f nding a True condition or an Else clause, it doesn’t execute any of the statement blocks.

Select Case

The Select Case statement lets a program execute one of several pieces of code depending on a single

value. The basic syntax is as follows:

Select Case test_value

 Case comparison_expression1

 statements1

 Case comparison_expression2

 statements2

 Case comparison_expression3

 statements3

 ...

 Case Else

 else_statements

End Select

If test_value matches comparison_expression1, the program executes the statements in

the block statements1. If test_value matches comparison_expression2, the program executes the statements in the block statements2. The program continues checking the expressions in the

Case statements in order until it matches one, or it runs out of Case statements.

If test_value doesn’t match any of the expressions in the Case statements, the program executes

the code in the else_statements block. Note that you can omit the Case Else section. In that

case, the program executes no code if test_value doesn’t match any of the expressions.

Select Case is functionally equivalent to an If Then Else statement. The following code does the

same thing as the previous Select Case code:

If test_value = comparison_expression1 Then

 statements1

ElseIf test_value = comparison_expression2 Then

 statements2

ElseIf test_value = comparison_expression3 Then

 statements3

...

Else

 else_statements

End If

Select Case is sometimes easier to understand than a long If Then Else statement. It is often faster as well, largely because Select Case doesn’t need to reevaluate test_value for every Case statement. If test_value is a simple variable, the difference is insignif cant, but if test_value represents a slow function call, the difference can be important. For example, suppose test_value represents a function that opens a database and looks up a value. The Select Case version will f nd the value once and use it in each comparison, whereas the If Then version would reopen the database for each comparison.

Factorial

set serveroutput on;
declare
i number(5);
j number (5);
f number(10);
begin
i:=&i;
f:=1;
for j in 1..i
loop
f:=f*j;
end loop;
dbms_output.put_line('Factorial of '||i|| ' is '  ||f);
end;
/