SQL Solutions: PL/SQL Programs

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;

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