Home » SQL & PL/SQL » SQL & PL/SQL » rollback to savepoint
rollback to savepoint [message #216964] Wed, 31 January 2007 01:38 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello orafaq team

sir i want to update the salaries of blake and clark by 2000 /1500. then check to see that the total salary does not exceed 57000. if total salary is greater then 57000 then undo the updates made toi the salaries of blake and clark.

i have done query -

declare
 v_total_salary number(9);
begin
 insert into emp4 values('e005', 'john', 1000);
  dbms_output.put_line('values inserted to table emp4);
    update emp4 set sal=sal+2000
      where emp_name='blake';
 dbms_output.put_line('values of blake inserted to table emp4');
    update emp4 set sal=sal+1500
      where emp_name='clark';
 dbms_output.put_line('values of clark inserted to table emp4');
 select sum(sal) into v_total_salary from emp4;
 dbms_output.put_line('values sum(sal)is '||v_total_salary);
   if v_total_salary>57000 then
  rollback;
dbms_output.put_line('sum(sal) is '||v_total_salary);
end if;
commit;
end;
/

sql>/
values inserted to table emp4
values of blake inserted to table emp4
values of clark inserted to table emp4
values sum(sal)is 61000
sum(sal) is 61000
sql>/
values inserted to table emp4
values of blake inserted to table emp4
values of clark inserted to table emp4
values sum(sal)is 61000
sum(sal) is 61000




declare
 v_total_salary number(9);
begin
 insert into emp4 values('e005', 'john', 1000);
  dbms_output.put_line('values inserted to table emp4);
   savepoint no_update;
    update emp4 set sal=sal+2000
      where emp_name='blake';
 dbms_output.put_line('values of blake inserted to table emp4');
    update emp4 set sal=sal+1500
      where emp_name='clark';
 dbms_output.put_line('values of clark inserted to table emp4');
 select sum(sal) into v_total_salary from emp4;
 dbms_output.put_line('values sum(sal)is '||v_total_salary);
   if v_total_salary>57000 then
  rollback to savepoint no_update;
dbms_output.put_line('sum(sal) is '||v_total_salary);
end if;
commit;
end;
/

sql>/
values inserted to table emp4
values of blake inserted to table emp4
values of clark inserted to table emp4
values sum(sal)is 61000
sum(sal) is 61000

sql>/
values inserted to table emp4
values of blake inserted to table emp4
values of clark inserted to table emp4
values sum(sal)is 62000
sum(sal) is 62000



sir i want to know that why my result not rollback to savepoint.
why updation takes place.
while using rollback i can manage to stop the updation .
please reply me back
thanks

regards


Re: rollback to savepoint [message #216967 is a reply to message #216964] Wed, 31 January 2007 01:45 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMO you have a bug in your logic.
Try following:

declare
v_total_salary number(9);
begin
insert into emp4 values('e005', 'john', 1000);
dbms_output.put_line('values inserted to table emp4);
savepoint no_update;
update emp4 set sal=sal+2000
where emp_name='blake';
dbms_output.put_line('values of blake inserted to table emp4');
update emp4 set sal=sal+1500
where emp_name='clark';
dbms_output.put_line('values of clark inserted to table emp4');
select sum(sal) into v_total_salary from emp4;
dbms_output.put_line('values sum(sal)is '||v_total_salary);
if v_total_salary>57000 then
rollback to savepoint no_update;
/* COMMENTED dbms_output.put_line('sum(sal) is '||v_total_salary); */end if;
/* New line */ select sum(sal) into v_total_salary from emp4;
/* New line */ dbms_output.put_line('sum(sal) is '||v_total_salary);

commit;
end;
Re: rollback to savepoint [message #217074 is a reply to message #216964] Wed, 31 January 2007 10:33 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello michael_bialik

i unable to restrict the updation. it will still update the sal of clark and blake. my if condition not workin.
use of rollback to savepoint no_update to working.
my logic failed.

please suggest me some help


regards
Re: rollback to savepoint [message #217083 is a reply to message #216964] Wed, 31 January 2007 10:56 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

In your first example you issue a rollback which rolls back everything INCLUDING this statement
insert into emp4 values('e005', 'john', 1000);


In your rollback to savepoint example you are rolling back to a point AFTER the above insert. Hence your variable value is going up by 1000.

It's worth putting in michael_bialik's changes as well as you are displaying the value in the variable you previously selected and not the value in the database at that point.

Re: rollback to savepoint [message #217111 is a reply to message #216964] Wed, 31 January 2007 13:13 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Why not do something like

update emp4
set sal = sal + decode(name,'Blake',2000,'Clark',1500,0)
where name in ('Blake','Clark')
and (select sum(sal)from emp4) + 3500 <= 57000;


This way the update is never even done.
Re: rollback to savepoint [message #217427 is a reply to message #216964] Fri, 02 February 2007 02:43 Go to previous message
haikasi
Messages: 7
Registered: October 2006
Junior Member
hi,
have you set AUTOCOMMIT OFF
Previous Topic: normal view and force view
Next Topic: what is mean by query optimization? and Explain plan?How we optimize the query??
Goto Forum:
  


Current Time: Fri Dec 02 22:52:47 CST 2016

Total time taken to generate the page: 0.10218 seconds