Table: Emp
Emp_no Name Balance
1001 John 3000
1002 Joly 4000
1003 Tony 2000
1004 Doly 500
SQL> create table
emp(Emp_no number(5),Name varchar2(10),Balance number(10,2));
SQL> begin
insert into emp values('1001','John',3000);
insert into emp values('1002','Joly',4000);
insert into emp values('1003','Tony',2000);
insert into emp values('1004','Doly',500);
end;
/
SQL> declare
debit number(10);
emp_no number(5);
a
number(5);
b
number(5);
c
number(5);
d
number(5);
begin
emp_no:=&emp_no;
debit:=&debit;
select balance into a from emp where emp_no='1001';
select balance into b from emp where emp_no='1002';
select balance into c from emp where emp_no='1003';
select balance into d from emp where emp_no='1004';
if
emp_no=1001 then
if
debit<a-500 then
update emp set balance=balance-debit where emp_no=1001;
else
dbms_output.put_line('Your balance is not sufficient');
end if;
elsif
emp_no=1002 then
if
debit<b-500 then
update emp set balance=balance-debit where emp_no=1002;
else
dbms_output.put_line('Your balance is not sufficient');
end
if;
elsif
emp_no=1003 then
if
debit<c-500 then
update emp set balance=balance-debit where emp_no=1003;
else
dbms_output.put_line('Your balance is not sufficient');
end
if;
elsif
emp_no=1004 then
if
debit<d-500 then
update emp set balance=balance-debit where emp_no=1004;
else
dbms_output.put_line('Your balance is not sufficient');
end
if;
else
dbms_output.put_line('Not valid emp_no');
end
if;
end;
/
SQL> select * from emp;
output:
Enter value for emp_no: 1004
old 10: emp_no:=&emp_no;
new 10: emp_no:=1004;
Enter value for debit: 100
old 11: debit:=&debit;
new 11: debit:=100;
Your balance is not sufficient
