declare
i number;
j varchar2(30);
cursor raise is select name, salary from emp;
begin
open raise;
loop
fetch raise into j,i;
i :=i+15;
dbms_output.put_line(i||' '||j);
exit when raise%notfound;
end loop;
end;
/
I am trying to increment the value by 15 for all records in salary column, but the select statement shows increment result for last three records. pls let me know what is the error and how to modify the program
the select statement shows increment result for last three recordsYou're going to have to give a complete test case. It looks fine to me:
declare
i number;
j varchar2( 30 );
cursor raise is
select ename, sal
from scott.emp;
begin
open raise;
loop
fetch raise
into j, i;
i := i + 15;
dbms_output.put_line( i || ' ' || j );
exit when raise%notfound;
end loop;
end;
/
815 SMITH
1615 ALLEN
1265 WARD
2990 JONES
1265 MARTIN
2865 BLAKE
2465 CLARK
3015 SCOTT
5015 KING
1515 TURNER
1115 ADAMS
965 JAMES
3015 FORD
1315 MILLER
1330 MILLER
But in any case, this is the Wrong Way. Looping through rows to set values is SLOOOOOOOOOOOOOOOOOOOOOOOOOOWWWWW.
If you just want to display what the new values will be, increment inside the query.
If you want to update the table, have one update that changes all the values.
select ename, sal, sal * 1.15 new_sal
from scott.emp;
ENAME SAL NEW_SAL
SMITH 800 920
ALLEN 1600 1840
WARD 1250 1437.5
JONES 2975 3421.25
MARTIN 1250 1437.5
BLAKE 2850 3277.5
CLARK 2450 2817.5
SCOTT 3000 3450
KING 5000 5750
TURNER 1500 1725
ADAMS 1100 1265
JAMES 950 1092.5
FORD 3000 3450
MILLER 1300 1495
update scott.emp
set sal = sal * 1.15;
select ename, sal
from scott.emp;
ENAME SAL
SMITH 920
ALLEN 1840
WARD 1437.5
JONES 3421.25
MARTIN 1437.5
BLAKE 3277.5
CLARK 2817.5
SCOTT 3450
KING 5750
TURNER 1725
ADAMS 1265
JAMES 1092.5
FORD 3450
MILLER 1495
Remember: one update changing N rows is much faster than N updates changing one row!
PS - raise is a reserved word. Which makes it a Bad Choice for a variable name.