Jump to content

What's wrong with this PL/SQL statement?

ravindra chary's Photo
Posted Oct 21 2010 11:10 AM
2731 Views

declare
cur_sal emp.sal%type;
emp_no emp.empno%type;
min_sal constant number(7,2):=5000.00;
bonus number(4):=100;
count number:=0;
begin
loop
emp_no:=&employeenumber;
count:=count+1;
select sal into cur_sal from emp
where emp_no=employeenumber;
if cur_sal>min_sal then
update emp
set sal:=sal+bonus
where empno=emp_no;
end if;
exit when count=5;
end loop;
end;

The above pl/sql code is to add bonus to employee who had sal>min_sal.... actually this code has to prompt 5 times....but in practically....it prompts for the first time and from next time onwards "emp_no:=&employeenumber" statement omitted and every time only one record will update for 5 times...
for ex: if we enter emp_no as 7964... employee record who had empno 7964 is updating 5 times automatically........yyyyyyyyyyyy?????????????

Tags:
0 Subscribe


1 Reply

0
  jm50's Photo
Posted Nov 18 2010 11:01 PM

this appears to be a script you run with sqlplus with a pl/sql block inside. Unlike sql commands sqlplus does not execute a pl/sql block its self, it sends it to the PL/SQL engine. All sqlplus is doing is putting in your first entered value and sending all the commands to PLSQL where the loop runs 5 times. There is no way (I know of) to get this code to prompt you each time through the loop.