Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, karthick.

Asked: October 16, 2018 - 8:27 am UTC

Last updated: October 16, 2018 - 9:55 am UTC

Version: 11g

Viewed 1000+ times

You Asked

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

and Chris said...

the select statement shows increment result for last three records

You'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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.