Assignment No. 1: Data Type, PLSQL Block and Control Structure
SET A:
1. Write a PL/SQL block to accept a number and display multiplication table of the given number.
ANS:
declare
num number:=:num;
i number:= 1;
total number;
begin
loop
total := num * i;
dbms_output.put_line(num || ' * ' || i || ' = ' || total);
i := i + 1;
if(i > 10) then
exit;
end if;
end loop;
end;
/
==========================================================================
2. Write a PL/SQL block which will accept student details, calculate the class using per value and insert the record into Student (rno, sname, class, per, class) table.
ANS
create table student_table
(rno number,
sname varchar2(30),
class varchar2(20),
per number);
select * from student_table;
--------------------------------------
declare
rno number;
sname varchar2(30):=:sname;
class varchar2(10);
per number;
begin
rno:=:rno;
per:=:per;
if(per>=80)then
class := 'A+';
elsif(per>=60)then
class := 'A';
elsif(per >= 50) then
class := 'B';
elsif(per >= 35) then
class := 'C';
else
class := 'Fail';
end if;
dbms_output.put_line(class);
insert into student_table (rno, sname, class, per)
values (rno, sname, class, per);
end;
/
==========================================================================
3. Write a PL/SQL block which will accept two numbers from user, check whether numbers are positive or negative. If positive number then display only the odd numbers between the entered numbers.
ANS
declare
n1 number:=:n1;
n2 number:=:n2;
i number;
begin
if(n1 <= 0 AND n2 <= 0)then
dbms_output.put_line('Please Enter Positive Number, Greater than 0');
end if;
for i IN n1..n2 loop
if i mod 2 !=0 then
dbms_output.put_line(i);
end if;
end loop;
end;
/
==========================================================================
SET B:
1. Write a PL/SQL block which will accept roll number of a student and display record of student from student table( use %ROWTYPE attribute)
ANS
declare
v_rollNo number:=:v_rollNo;
record student_bca%rowtype;
begin
select * into record from student_bca where rollNo = v_rollNo;
dbms_output.put_line('Roll No = '|| record.rollNo);
dbms_output.put_line('Class = '|| record.class);
dbms_output.put_line('Student Name = '|| record.name);
dbms_output.put_line('Age = '|| record.age);
dbms_output.put_line('Address = '|| record.addr);
end;
/
-----------------------------------------------------------------------------------------------------------------------------
create table student_bca (
rollNo int primary key,
class varchar(10),
name varchar2(30),
age int,
addr varchar(100)
);
drop table student_bca;
insert into student_bca (rollNo, class, name, age, addr)
values
(102, 'TY', 'Raj', 25, 'Nagar');
select * from student_bca;
==========================================================================
2. Write a PL/SQL block which will accept roll number from student, select name and percentage of the student and calculate grade using percentage value. Display the record.(use %TYPE)
ANS
declare
v_rollno student_bca.rollno%type;
v_name student_bca.name%type;
v_per student_bca.percentage%type;
v_gread varchar2(10);
begin
v_rollno :=:r;
select name, percentage INTO v_name, v_per from student_bca where rollno = v_rollno;
if v_per >=90 AND v_per < 100 THEN
v_gread := 'A+';
elsif v_per >= 70 THEN
v_gread := 'A';
elsif v_per >= 60 THEN
v_gread := 'B';
elsif v_per >= 50 THEN
v_gread := 'C';
elsif v_per >= 35 THEN
v_gread := 'D';
else
v_gread := 'Fail';
end if;
dbms_output.put_line('Roll No : ' || v_rollno);
dbms_output.put_line('Name : ' || v_name);
dbms_output.put_line('Percentage : ' || v_per);
dbms_output.put_line('Gread : ' || v_gread);
end;
/
select * from student_bca;
=========================================================================
3. Write a PL/SQL block which will display ‘FYBBA(CA)’ ten times on screen. Odd index number must display ‘FYBBA(CA)’ and even index number position in reverse case ‘fybba(ca)’.
ANS
declare
low_str varchar(10):= 'fybba(ca)';
upp_str varchar(10):= 'FYBBA(CA)';
i number;
begin
for i IN 1..10 loop
if i mod 2 = 0 then
dbms_output.put_line(low_str);
else
dbms_output.put_line(upp_str);
end if;
end loop;
end;
/
==========================================================================
0 Comments