1 Aditya College of Engineering & Technology
DATABASE MANAGEMENT SYSTEMS LAB
EXPERIMENT NO: 7 DATE:
Write a PL/SQL Code using Basic Variable, Anchored Declarations,
and Usage of Assignment Operation.
Introduction
The PL/SQL programming language was developed by Oracle Corporation in the late
1980s as procedural extension language for SQL.
PL/SQL is not a stand-alone programming language; it is a tool within the Oracle
programming environment.
PL/SQL is a block-structured language that is PL/SQL programs are divided into
logical blocks of code.
Structure of a PL/SQL block:
DECLARE
<declaration/initialization of variables>
BEGIN
<executable statements>
EXCEPTION
<exception handling code>
END; --- end of the program.
Declarations: This section starts with the keyword DECLARE. It is an optional section and
defines all variables, cursors, subprograms, and other elements to be used in the program.
Executable block: This section is enclosed between the keywords BEGIN and END and it is a
mandatory section. It consists of the executable PL/SQL statements of the program. It
should have at least one executable line of code
Exception Handling: This section starts with the keyword EXCEPTION. This section is again
optional and contains exception(s) that handle errors in the program.
DATA TYPES
1. Number
2. char
3. varchar2
4. date
5. %type it is to continue the variable data type same as in table
VARIABLE DECLARATION
Syntax for declaring a variable is:
variable_name datatype:=initial_value;
Initializing Variables
Regd. No:
Database Management Systems Lab
2 Aditya College of Engineering & Technology
a integer := 10;
Example:
msg char(20) := ‘hello world’;
Control structures
Conditional statements
Repetitive statements
Case statements
Conditional statements
1. Simple IF
Syntax:
IF condition THEN
Statements;
END IF;
2. IF-ELSE
Syntax:
IF condition THEN
Statements;
ELSE
Statements;
END IF;
3. IF-ELSE LADDER
Syntax:
IF condition THEN
Statements;
ELSIF condition THEN
Statements;
…
ELSE
Statements;
END IF;
4. CASE STATEMENTS
Syntax:
Case selector
WHEN 'value1' THEN Statements;
WHEN 'value2' THEN Statements;
WHEN 'value3' THEN Statements;
...
ELSE Statements; default case
END CASE;
LOOPS
1. BASIC LOOP
Regd. No:
Database Management Systems Lab
3 Aditya College of Engineering & Technology
2. WHILE LOOP
3. FOR LOOP
1. BASIC LOOP
Syntax:
LOOP
Statements;
END LOOP;
2. WHILE LOOP
Syntax:
WHILE condition LOOP
Statements;
END LOOP;
3. FOR LOOP
Syntax:
FOR variable_name IN/IN REVERSE start_value .. end_value
LOOP
Statements;
END LOOP; //for j in reverse 10..1
Loop
end loop
Regd. No:
Database Management Systems Lab
4 Aditya College of Engineering & Technology
EXCEPTIONS
Error condition during a program execution is called an exception in PL/SQL.
There are two types of exceptions:
System-defined exceptions
User-defined exceptions
Syntax:
DECLARE
<Declarations section>
BEGIN
<Executable Statements>
EXCEPTION
WHEN exception1 THEN
exception1-handling-statements;
WHEN exception2 THEN
exception2-handling-statements;
WHEN exception3 THEN
exception3-handling-statements;
........
END;
1. Write a PL/SQL Program to display "Hello World" message
Aim:- To display "Hello World" message
Source Code:-
Set serveroutput on;
begin
dbms_output.put_line(‘Hello World’);
end;
Output:-
2. Write a PL/SQL program to find given number is even or odd
Aim:- To find given number is even or odd
Source code:-
Set serveroutput on;
Declare
n int:= &n;
begin
if mod(n,2)=0 then
dbms_output.put_line('Given number is even');
else
dbms_output.put_line('Given number is odd');
end if;
end;
Output:-
Regd. No:
Database Management Systems Lab
5 Aditya College of Engineering & Technology
3. Write a PL/SQL program to find biggest of 3 numbers
Aim:- To find biggest of 3 numbers
Source Code:-
Set serveroutput on;
Declare
a int:= &a;
b int:= &b;
c int:= &c;
begin
if (a>b) and (a>c) then
dbms_output.put_line('A is big');
elsif (b>c) then
dbms_output.put_line('B is big');
else
dbms_output.put_line('C is big');
end if;
end;
Output:-
4. Write a PL/SQL program to find a sum of 1 to n number
Aim:- To find a sum of 1 to n number
Source Code:-
Set serveroutput on;
Declare
n int:=&n;
s int:=0;
i int:=1;
begin
loop
if i=n then
s:=s+i;
Exit;
end if;
sum:=s +i;
i:=i+1;
end loop;
dbms_output.put_line('Sum of 1 to ' || n || ' number is ' || s);
end;
Output:-
Regd. No:
Database Management Systems Lab
6 Aditya College of Engineering & Technology
Using FOR Loop
Declare
n int:=&n;
s int:=0;
i int;
begin
for i in 1..10
loop
s := s+i;
end loop;
dbms_output.put_line('Sum of 1 to ' || n || ' number is '|| s);
end;
Output
5. Write PL/SQL program to find multiplication table for a given 'n' value
Aim:- To find multiplication table for a given 'n' value
Source Code:-
Set serveroutput on;
Declare
n int:=&n;
res int:=0;
i int:=1;
begin
loop
res:=(n*i);
dbms_output.put_line(n||'*'||i||'='||res);
Exit when i=10;
i:=i+1;
end loop;
end;
Regd. No:
Database Management Systems Lab
7 Aditya College of Engineering & Technology
Using FOR Loop
Declare
n int:= &n;
s int:=0;
i int;
begin
for i in 1..10
loop
dbms_output.put_line(n || ‘ X ‘ || i || ‘ = ‘ || n*i );
end loop;
end;
Output:-
Regd. No:
Database Management Systems Lab
