PL/SQL Programs

Program-1
Write a PL/SQL block that will accept an account number from the user, check if the users balance is less than minimum balance, then deduct Rs. 100/- from the balance.
Solution
create table acct_master(acct_no number(5) primary key,acct_name varchar2(10),balance number(10));

insert into acct_master values(1,'aaa',1000)
insert into acct_master values(2,'bbb',100)
insert into acct_master values(3,'ccc',1100)
insert into acct_master values(4,'ddd',700)
insert into acct_master values(5,'eee',1700)


DECLARE
xacct_no number(5);
xmin_bal number(5):=1000;
xbalance number(5);
BEGIN
xacct_no:=&xacct_no;
select balance into xbalance from acct_master where acct_no=xacct_no;

IF(xbalance < xmin_bal) THEN
update acct_master set balance=balance-100 where acct_no=xacct_no;
xbalance:=xbalance-100;
dbms_output.put_line('Rs 100 is deducted and current balance is'||xbalance);

ELSE
dbms_output.put_line('Current balance is'||xbalance);
END IF;

END;

Program-2

Write a PL/SQL block that will accept student id number from the user, and check is student attendance is less than 80% then display message that student cannot appear in exam.
Solution
create table student(stud_id number(5)primary key,stud_name varchar2(10),stud_att number(5));

insert into student values(1,'hitesh',120);
insert into student values(2,'kamlesh',160);
insert into student values(3,'kayur',190);
insert into student values(4,'mahesh',110);
insert into student values(5,'suresh',115);

DECLARE
xstud_id number(5);
xstud_att number(5);
xtotal_days number(3):=200;
BEGIN
xstud_id:=&xstud_id;
select stud_att into xstud_att from student where stud_id=xstud_id;
IF(xstud_att<(xtotal_days*0.80)) THEN
dbms_output.put_line('This student can not attend exam');
ELSE
dbms_output.put_line('This student can attend exam');
END IF;
END;

Program-3
Create a loop that display odd numbers from 1 to 100.
Solution
DECLARE
i number(5):=1;
j number(5);
BEGIN
while(i<=100)
LOOP
j:=mod(i,2);
IF(j!=0) THEN
dbms_output.put_line(i);
END IF;
i:=i+1;
END LOOP;
END;

Program-4
Write a PL/SQL block that invert any given number
Solution:-
DECLARE
given_number number(6);
str_length number(2);
inverted_number number(6);
i number(2):=1;
BEGIN
given_number:=&given_number;
str_length := length(given_number);
for i IN reverse 1..str_length
LOOP
inverted_number:=inverted_number||substr(given_number,i,1);
END LOOP;
dbms_output.put_line('the given number='|| given_number);
dbms_output.put_line('the inverted number='|| inverted_number);
END;

Program-5
Write a PL/SQL block that calculate the area of circle for a value of radius varying from 3 to 7. Store the radius and the corresponding values of calculated area in an empty tale name areas.
Solution:-
create table areas(redius number(2),area number(6,2));

DECLARE
pi constant number(3,2):=3.14;
radius number(2);
area number(10,2);
BEGIN
radius:=3;
while(radius<=7)
LOOP
area:=pi*power(radius,2);
Insert into areas values(radius,area);
radius:=radius+1;
END LOOP;
dbms_output.put_line('Records are successfully inserted');
END;

Program-6
Write a PL/SQL block of code that if there are no transaction taken place in the last 365 days then mark the account status as inactive and then record the account number, opening date and type of account in the table.
Solution:-
create table branch_master
(
branch_no varchar2(2) primary key,
branch_name varchar2(10)
);
insert into branch_master values('b1','sbi_delhi');
insert into branch_master values('b2','sbi_mumbai');
insert into branch_master values('b3','sbi_ahd');

create table acc_mstr
(
acc_no varchar2(7),
cur_bal number(8),
dt date,type varchar2(2),
status varchar2(1),
branch_no varchar2(2) references branch_master
);

ACC_NO CUR_BAL DT TY S BR
------- ---------- --------- -- - -------------
sbi101 20000 25-NOV-08 sa a b1
sbi102 10000 20-DEC-07 ca i b1
sbi103 8000 01-JAN-08 ca a b1

create table in_active_acc(acc_no varchar2(7),dt date,type varchar2(2));
DECLARE
xacc_no varchar2(7);
xdt date;
xtype varchar2(2);
BEGIN
xacc_no:='&xacc_no';
select dt into xdt from acc_mstr where acc_no=xacc_no;
IF((sysdate-xdt)>365)THEN
update acc_mstr set status='i' where acc_no=xacc_no;
select dt,type into xdt,xtype from acc_mstr where acc_no=xacc_no;
insert into in_active_acc(acc_no,dt,type) values(xacc_no,xdt,xtype);
dbms_output.put_line('Account number:'||xacc_no||'is inactive');
ELSE
Update acc_mstr set status='A' where acc_no=xacc_no;
dbms_output.put_line('Account number'||xacc_no||'is active');
END if;
END;

Program-7
Write a PL/SQL block of code that first withdraws an amount of Rs. 1,000. Then deposits an amount of RS. 1,40,000. Update the current balance of all the accounts in the bank does not exceed Rs. 2,00,000. If the balance exceed Rs. 2,00,000 then undo the deposit just made.
Solution:-
create table trans_mstr
(
trans_no varchar2(5),
acct_no varchar2(5),
dt date,
type varchar2(2),
particular varchar2(15),
dr_cr varchar2(2),
amt number(8),
balance number(9)
);
create table acct_mstr(acct_no varchar2(5),curbal number(10));
insert into acct_mstr values('ca10',8000);
insert into acct_mstr values('ca11',80000);

DECLARE
mbal number(8,2);
BEGIN
insert into trans_mstr values('t100','ca10','04-jul-2004','c','telephone','w',1000,31000);
update acct_mstr set curbal=curbal-1000 where acct_no='ca10';

savepoint no_update;
insert into trans_mstr values('t101','ca10','04-jul-2004','c','deposite','s',140000,171000);
update acct_mstr set curbal=curbal+140000 where acct_no='ca10';
select sum(curbal) into mbal from acct_mstr;

if mbal>200000 then
rollback to savepoint no_update;
end if;
commit;
END;