Home » SQL & PL/SQL » SQL & PL/SQL » update records using cursor (merged 3)
update records using cursor (merged 3) [message #385559] Tue, 10 February 2009 02:30 Go to next message
nipa.pillai
Messages: 11
Registered: February 2009
Junior Member
This is the code i have written.Though the code compiles successfully the value of the commission is not getting updated in the emp table.. please help
declare
cursor cur
is
SELECT e.empno,
       e.ename,
       e.job,
	e.mgr,
	e.hiredate,
	e.sal,
	e.deptno,
	e.comm,
	d.dname
   FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO for update;
BEGIN
for rec in cur
loop
  if rec.dname='SALES' then
     update emp set comm=0 where current of cur;
     dbms_output.put_line('commission updated');
  end if ;
end loop;

END;


[EDITED by LF: [color] tags substituted with [code] tags]

[Updated on: Tue, 10 February 2009 15:19] by Moderator

Report message to a moderator

Re: update records using cursor [message #385563 is a reply to message #385559] Tue, 10 February 2009 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use a single UPDATE statement?

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: update records using cursor [message #385573 is a reply to message #385559] Tue, 10 February 2009 02:47 Go to previous messageGo to next message
icmohsin
Messages: 12
Registered: January 2009
Location: Mumbai
Junior Member
Try this out


declare
cursor cur
is
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.deptno,
e.comm,
d.dname
FROM tt e,DEPT d WHERE e.DEPTNO=d.DEPTNO for update;
BEGIN
for rec in cur
loop
if rec.dname='SALES' then
update tt set comm=0 where DEPTNO = REC.DEPTNO; dbms_output.put_line('commission updated');
end if ;
end loop;
END;
Re: update records using cursor [message #385574 is a reply to message #385563] Tue, 10 February 2009 02:48 Go to previous messageGo to next message
nipa.pillai
Messages: 11
Registered: February 2009
Junior Member
the problem is am using two tables and i have to write a pl/sql block.So for that i used cursor. Though am getting messages that commission has been updated its not actually getting updated in the emp table.
Re: update records using cursor [message #385575 is a reply to message #385559] Tue, 10 February 2009 02:50 Go to previous messageGo to next message
philackerley
Messages: 4
Registered: November 2005
Junior Member
Hi,

It all looks fine and I ran it as well; I suspect you need autocommit on in the session running the code or alternatively perform an explicit COMMIT, try that!
Phil Razz
Re: update records using cursor [message #385576 is a reply to message #385574] Tue, 10 February 2009 02:54 Go to previous messageGo to next message
nipa.pillai
Messages: 11
Registered: February 2009
Junior Member
Formated code
declare
cursor cur
is
SELECT e.empno,
       e.ename,
       e.job,
	e.mgr,
	e.hiredate,
	e.sal,
	e.deptno,
	e.comm,
	d.dname
   FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO for update;
BEGIN
for rec in cur
loop
if rec.dname='SALES' then
update emp set comm=0 where current of cur;
dbms_output.put_line('commission updated');
end if ;
end loop;

END;
Re: update records using cursor [message #385578 is a reply to message #385574] Tue, 10 February 2009 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the problem is am using two tables and i have to write a pl/sql block.

Why if you can do it in a single SQL statement?
For instance:
update emp set comm=0 
where deptno = (select deptno from dept where dname='SALES')
/


Regards
Michel

[Updated on: Tue, 10 February 2009 03:01]

Report message to a moderator

Re: update records using cursor [message #385581 is a reply to message #385573] Tue, 10 February 2009 03:12 Go to previous messageGo to next message
nipa.pillai
Messages: 11
Registered: February 2009
Junior Member
I cant use this where clause because if i use it there would be two where clauses. In for update syntax i need to use this where current of cur.
Re: update records using cursor [message #385583 is a reply to message #385578] Tue, 10 February 2009 03:15 Go to previous messageGo to next message
nipa.pillai
Messages: 11
Registered: February 2009
Junior Member
Can use this single query but like i said i need to use this inside a pl/sql block so for that i need to use a cursor.
Re: update records using cursor [message #385586 is a reply to message #385578] Tue, 10 February 2009 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That = probably wants to be an IN:
update emp set comm=0 
where deptno IN (select deptno from dept where dname='SALES')
/


There is absolutely no advantage to doing this in an anonymous Pl/Sql block.

If for some reason (say because your teacher had told you to do it that way) you still wanted to do it in a pl/sql block, then the pl/sql to write would be:
BEGIN
  update emp 
  set comm=0 
  where deptno IN (select deptno 
                   from   dept 
                   where dname='SALES');
END;
/

Re: update records using cursor [message #385590 is a reply to message #385586] Tue, 10 February 2009 03:36 Go to previous messageGo to next message
nipa.pillai
Messages: 11
Registered: February 2009
Junior Member
Am particularly asked to use cursors.. Sad
Re: update records using cursor (merged 3) [message #385679 is a reply to message #385559] Tue, 10 February 2009 08:59 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
What happens when you run your code?
Do you see your 'commission updated' message?
Do you get an error?
Have you got any departments with a dname of SALES?
Re: update records using cursor (merged 3) [message #385683 is a reply to message #385559] Tue, 10 February 2009 09:16 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Previous Topic: Materialized views
Next Topic: outer join / normal select / analytical function
Goto Forum:
  


Current Time: Tue Dec 06 12:16:11 CST 2016

Total time taken to generate the page: 0.06276 seconds