PL/SQL
Programs
1. Write a PL/SQL program to display
message on the screen.
begin
dbms_output.put_line('This is my first PL/SQL
Program');
end;
/
Output-
This is my first PL/SQL Program
2. Write a PL/SQL program to display Multiline message
on the screen
begin
dbms_output.put_line('This is my first PL/SQL
Program');
dbms_output.put_line('PL/SQL is a procedural
Language');
dbms_output.put_line('I like PL/SQL');
end;
/
Output
–
This is my first PL/SQL Program
PL/SQL is a procedural Language
I like PL/SQL
3. Write a PL/SQL program using
put() procedure.
begin
dbms_output.put('Testing');
dbms_output.put('PUT Procedure');
dbms_output.put_line('');
end;
/
Output
–
Testing put procedure
4.Write
a PL/SQL program to display addition of two numbers.
declare
num1
number;
num2 number;
num3 number;
begin
num1:=20;
num2:=15;
num3:=num1+num2;
dbms_output.put_line('The sum of '||num1||'
and '||num2||' is
'||num3);
end;
/
Output-
The sum of
20 and 15
is 35
5.
Write a PL/SQL program to display addition, subtraction, multiplication and
division of two numbers
declare
num1 number;
num2 number;
num3 number;
begin
num1:=20;
num2:=15;
num3:=num1+num2;
dbms_output.put_line('The sum of '||num1||'
and '||num2||' is
'||num3);
num3:=num1-num2;
dbms_output.put_line('The subtraction of '||num1||'
and '||num2||' is'||num3);
num3:=num1*num2;
dbms_output.put_line('The multiplication of '||num1||'
and '||num2||'is '||num3);
num3:=num1/num2;
dbms_output.put_line('The division of '||num1||'
and '||num2||' is
'||num3);
end;
/
OUTPUT
-
The sum of
20 and 15
is 35
The subtraction of
20 and 15
is 5
The multiplication of 20
and 15 is 300
The division of
20 and 15
is 1.33333333333333333333333333333333333333
6. Write a
PL/SQL program to display addition, subtraction, multiplication and division of
two numbers and numbers will be entered by user.
declare
num1 number;
num2 number;
num3 number;
begin
num1:=&num1;
num2:=&num2;
num3:=num1+num2;
dbms_output.put_line('The sum of '||num1||'
and '||num2||' is
'||num3);
num3:=num1-num2;
dbms_output.put_line('The subtraction of '||num1||'
and '||num2||' is '||num3);
num3:=num1*num2;
dbms_output.put_line('The multiplication of '||num1||'
and '||num2||'is '||num3);
num3:=num1/num2;
dbms_output.put_line('The division of '||num1||'
and '||num2||' is
'||num3);
end;
/
OUTPUT
-
Enter value for num1: 2
old
6: num1:=&num1;
new
6: num1:=2;
Enter value for num2: 3
old
7: num2:=&num2;
new
7: num2:=3;
The sum of
2 and 3
is 5
The subtraction of
2 and 3
is -1
The multiplication of 2
and 3 is 6
The division of
2 and 3
is
.6666666666666666666666666666666666666667
7.
Write a PL/SQL program to display grade of student after entering marks of
student using if statement.
declare
aggr number(5,2);
begin
aggr:=&aggregate;
if aggr>=80 and aggr<=100 then
dbms_output.put_line('Student scored A grade');
end if;
if aggr>=60 and aggr<=80 then
dbms_output.put_line('Student scored B grade');
end if;
if aggr>=40 and aggr<=60 then
dbms_output.put_line('Student scored C grade');
end if;
if aggr>=0 and aggr<=40 then
dbms_output.put_line('Student scored F grade');
end if;
end;
/
OUTPUT
-
Enter value for aggregate: 50
old 4:
aggr:=&aggregate;
new 4:
aggr:=50;
Student scored C grade
8. Write a
PL/SQL program to display grade of student after entering marks of student
using if statement.
declare
aggr number(5,2);
begin
aggr:=&aggregate;
if aggr>=80 and aggr<=100 then
dbms_output.put_line('Student scored A grade');
if aggr>=60 and aggr<=80 then
dbms_output.put_line('Student scored B grade');
if aggr>=40 and aggr<=60 then
dbms_output.put_line('Student scored C grade');
if aggr>=0 and aggr<=40 then
dbms_output.put_line('Student scored F grade');
else
dbms_output.put_line('You have enter invalid
number');
end if;
end if;
end if;
end if;
end;
/
9. Write a
PL/SQL program to check whether entered number is even or odd using if-else
statement.
declare
x number;
begin
x:=&number;
if mod(x,2)=0 then
dbms_output.put_line('The number '||x||'is even');
else
dbms_output.put_line('The number '||x||'is odd');
end if;
end;
/
OUTPUT
-
Enter value for number: 5
old 4:
x:=&number;
new 4:
x:=5;
The number 5 is odd
10. Write a
PL/SQL program to find largest number of three numbers using if-elsif
statement.
declare
x number;
y number;
z number;
begin
x:=&number1;
y:=&number2;
z:=&number3;
if x>z then
if x>y then
dbms_output.put_line('x is largest');
end if;
elsif
y>z then
dbms_output.put_line('y is largest');
else
dbms_output.put_line('z is largest');
end if;
end;
/
OUTPUT
Enter value for number1: 8
old 6:
x:=&number1;
new 6:
x:=8;
Enter value for number2: 6
old 7:
y:=&number2;
new 7:
y:=6;
Enter value for number3: 3
old 8:
z:=&number3;
new 8:
z:=3;
x is largest
11. Write a
PL/SQL program to find largest number of three numbers using if statement.
declare
x number;
y number;
z number;
begin
x:=&number1;
y:=&number2;
z:=&number3;
if x>y and x>z then
dbms_output.put_line('x is largest');
end if;
if y>x and y>z then
dbms_output.put_line('y is largest');
end if;
if z>x and z>y then
dbms_output.put_line('y is largest');
end if;
end;
/
OUTPUT
-
Enter value for number1: 8
old 6:
x:=&number1;
new 6:
x:=8;
Enter value for number2: 9
old 7:
y:=&number2;
new 7:
y:=9;
Enter value for number3: 5
old 8:
z:=&number3;
new 8:
z:=5;
y is largest
12. Write a
PL/SQL program to find largest number of three numbers using if-elsif
statement.
declare
x number;
y number;
z number;
begin
x:=&number1;
y:=&number2;
z:=&number3;
if x>y
and x>z then
dbms_output.put_line('x is largest');
elsif
y>x and y>z then
dbms_output.put_line('y is largest');
else
dbms_output.put_line('z is largest');
end if;
end;
/
13.Write
a PL/SQL program to find whether the entered day is holiday or not using case statement.
declare
day varchar2(20);
begin
day:='&day';
case
when day='Sunday' then
dbms_output.put_line('holiday');
when day='Monday' then
dbms_output.put_line('No holiday');
when day='Tuesday' then
dbms_output.put_line('No holiday');
when day='Wednesday' then
dbms_output.put_line('No holiday');
when day='Thursday' then
dbms_output.put_line('No holiday');
when day='Friday' then
dbms_output.put_line('No holiday');
when day='Saturday' then
dbms_output.put_line('No holiday');
else
dbms_output.put_line('Wrong input');
end case;
end;
/
OUTPUT
Enter value for day: monday
old 4:
day:='&day';
new 4:
day:='monday';
No holiday
