Roll no:33,Name:-Pranshav Jobanputra Div:-D
Tutorial-9
1)Create a PL/SQL block to create table
st_master(enroll,name,branch,semester)&ins
ert data into st_master?
-> Code:- Declare
V_enroll varchar(20);
V_name varchar(20);
V_branch varchar(50);
V_semester varchar(10);
Begin
Dbms_output.Put_line(‘Enter student
enrollment number: ‘);
V_enroll :=:enroll;
Dbms_output.Put_line(‘Enter student branch:
‘);
V_branch:=:branch;
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Dbms_output.Put_line(‘Enter student
semester number: ‘);
V_semester :=:semester;
Insert into
st_master(enroll,name,branch,semester)Valu
es(v_enroll,v_name,v_branch,v_semester);
Commit;
Dbms_output.Put_line(‘Data inserted
successfully. ‘);
Exception
When other then
Dbms_output.Put_line(‘error: ‘||SQLerrm);
Rollback;
End;
Output:-
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
2)Create PL/SQL block to update details in
st_master?
->Declare
V_enroll varchar(20);
V_name varchar(20);
V_branch varchar(50);
V_sem varchar(10);
Begin
Dbms_output.Put_line(‘Enter student
enrollment number: ‘);
V_enroll :=:enroll;
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Dbms_output.Put_line(‘Enter student name
number: ‘);
V_name :=:name;
Dbms_output.Put_line(‘Enter student
branch number: ‘);
V_branch :=:branch;
Dbms_output.Put_line(‘Enter student sem
number: ‘);
V_sem :=:sem;
Update st_master,
Set name = v_name;
Branch = v_branch;
Sem = v_sem;
Where enroll = v_enroll;
If SQL%rowcount >0 then
Commit;
Dbms_output.Put_line(‘error: ‘||SQLerrm);
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Rollback;
End;
Output:-
3)Write a PL/SQL block that delete the data of
student from st_master for enroll entered by
user?
->Code:- Declare
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
V_enroll varchar(20);
Begin
Dbms_output.Put_line(‘Enter student
enrollment number: ‘);
V_enroll :=:enroll;
Delete from st_master,
Where enroll = v_enroll;
If SQL%rowcount > 0 then
Commit;
Dbms_output.Put_line(‘student record
delete successfully.‘);
Else
Dbms_output.Put_line(‘no Student found
with the given enrollment:‘);
End if;
Exception
When other then
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Dbms_output.Put_line(‘error:‘||SQLerrm);
Rollback;
End;
Output:-
4)Write a PL/SQL block to print hello world on
screen?
->Begin
Dbms_output.Put_line(‘Hello world’);
End;
Output:-
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
5)Write a PL/SQL block to perform basic
arithmetic operations on data entered by
user?
->Code:-Declare
V1 integer;
V2 integer;
Sum integer;
Begin
Dbms_output.Put_line(‘Enter value1: ‘);
V1:=:v1;
Dbms_output.Put_line(‘Enter value2: ‘);
V2 :=:v2;
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Sum:=v1+v2;
Dbms_output.Put_line(‘The addition is: ‘||
sum);
End;
Output:-
6)Write a PL/SQL block to create a table
circle_master(sr_no,radius,area,circumferenc
e)from radius 1 to 10?
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
->Code:-Begin
For radius in 1..10 loop
Area := 3.1459*radius*radius;
Circumference:=2*3.1459*radius;
End loop;
For rec in (Select * from circle_master)loop
Dbms_output.Put_line(‘sr_no: ‘||
rec.sr_no,’radius:= ‘||rec.radius,’Area: ‘||
rec.area,’circumference:= ‘||
rec.circumference);
End loop;
End;
Output:-
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
7)Write a PL/SQL block to check the number
entered by user is prime or not?
-> Code:-Declare
Num number;
Is_prime Boolean:=true;
I number;
Begin
Dbms_output.Put_line(‘Enter a number: ‘);
Accept num prompt ‘ number: ‘;
If num < = 1 then
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Is_prime := false;
Else
For I in 2.Trunc(SQRT(num))loop
If mod(num,i)= 0 then
Is_prime:= false;
Exit;
End if;
End loop;
End if;
If is_prime then
Dbms_output.Put_line(‘number is prime’);
Else
Dbms_output.Put_line(‘number is not
prime’);
End if;
End;
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Output:-
8)Write a PL/SQL block to check the number
entered by user is Armstrong or not?
-> Code:-
Declare
Num number;
Sum_of_powers number:=0;
Temp number;
Digit number;
Sum_digits number;
Begin
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Dbms_output.Put_line(‘Enter a number: ‘);
Accept num prompt ‘number: ‘;
Temp:=num;
Num_digits:=length (num);
While temp > 0 loop
Digit:=mod(temp,10);
Sum_of_powers:=Sum_of_powers+power(dig
it,num_digits);
Temp:= trunk(temp /10);
End loop;
If sum_of_power = num then
Dbms_output.Put_line(‘number is
armstrong’);
Else
Dbms_output.Put_line(‘number is not
armstrong’);
End if;
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
End;
Output:-
End Tutorial
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
Roll no:33,Name:-Pranshav Jobanputra Div:-D
Enrollment:23SDSCE01235,Subject:-DBMS,academicyear:2024-25
