Home » SQL & PL/SQL » SQL & PL/SQL » How to Output the updated row (Oracle 9i)
How to Output the updated row [message #404234] Thu, 21 May 2009 01:35 Go to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
i have a PL/SQL Block that will update a row uisng FOR LOOP CURSOR and then should show the Updated row one at a time.

Below is the code,What i m confused/want the logic to be implemented inside the FOR LOOP to show the updated row


begin
	   for record in (select sal from emp where deptno='20')
	   LOOP
	   update emp set sal=sal + 1000 where sal=record.sal;
	   dbms_output.put_line( record.sal );
	   END LOOP;
END;

Re: How to Output the updated row [message #404248 is a reply to message #404234] Thu, 21 May 2009 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your code already shows it via dbms_output, just add/concatenate the columns you want to display.

Regards
Michel
icon4.gif  Re: How to Output the updated row [message #404256 is a reply to message #404234] Thu, 21 May 2009 02:30 Go to previous messageGo to next message
vigneshbemba
Messages: 17
Registered: April 2007
Location: chennai
Junior Member
Even after that if you are unable to see the output ,then checkout whether set serveroutput is 'ON'
Re: How to Output the updated row [message #404258 is a reply to message #404234] Thu, 21 May 2009 02:32 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
what i want is the updated sal value not the old value
Re: How to Output the updated row [message #404268 is a reply to message #404234] Thu, 21 May 2009 02:52 Go to previous messageGo to next message
vigneshbemba
Messages: 17
Registered: April 2007
Location: chennai
Junior Member
Then ,change your dbms_output.put_line(record.sal); to dbms_output.put_line(sal);
Re: How to Output the updated row [message #404273 is a reply to message #404258] Thu, 21 May 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is sal+1000

Regards
Michel
Re: How to Output the updated row [message #404306 is a reply to message #404273] Thu, 21 May 2009 05:18 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
	   update emp set sal=sal + 1000 where sal=record.sal;


I guess you do understand that this code will not neccessarily only update the record that your cursor selected but all thos records which satisfy the where condition.

[Edit] - small typo corrected

[Updated on: Thu, 21 May 2009 05:23]

Report message to a moderator

Re: How to Output the updated row [message #404311 is a reply to message #404234] Thu, 21 May 2009 05:28 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Firstly to the code: are you aware that in may update one row multiple times under some conditions, e.g.
SQL> select empno, ename, sal from emp where deptno = 20;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975
      7902 FORD             3000
      7369 SMITH             800
      7788 SCOTT            4000
      7876 ADAMS            1100

SQL> begin
  2    for record in (select sal from emp where deptno='20')
  3    LOOP
  4    update emp set sal=sal + 1000 where sal=record.sal;
  5    END LOOP;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select empno, ename, sal from emp where deptno = 20;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            3975
      7902 FORD             5000
      7369 SMITH            1800
      7788 SCOTT            5000
      7876 ADAMS            2100

SQL> 
As FORD has salary 1000 less than SCOTT and it is updated before SCOTT, so it is added 2000.
You should use better WHERE condition for the UPDATE statement than the one on SAL; e.g. on primary key (EMPNO) or use WHERE CURRENT_OF clause.

Secondly, you may use RETURNING INTO clause to get the value(s) from the updated row. It is documented in PL/SQL User's Guide and Reference, available e.g. online on http://tahiti.oracle.com/.

UPDATE Statement for Oracle 9.2 is placed here: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems52.htm#36432

[Edit: typo]

[Updated on: Thu, 21 May 2009 05:29]

Report message to a moderator

Previous Topic: How to find Name of the private procedures/functions inside the package?
Next Topic: Calculating values from previous record on different column
Goto Forum:
  


Current Time: Fri Dec 02 16:13:43 CST 2016

Total time taken to generate the page: 0.38328 seconds