Home » SQL & PL/SQL » SQL & PL/SQL » Procedure (Oracle IDS 10g,Windows XP)
Procedure [message #400485] Tue, 28 April 2009 06:51 Go to next message
anugraha
Messages: 15
Registered: February 2009
Junior Member
Hi All !

I have created the procedure

  1    CREATE OR REPLACE PROCEDURE earn_bal_up (el in bal_assign.leavetype%TYPE) AS
  2   num number;
  3          CURSOR cur IS
  4       SELECT existing_balance from bal_assign where leavetype=el;
  5        begin
  6      open cur;
  7      loop
  8     fetch cur into num;
  9    exit when cur%notfound;
 10     if num>=300 then
 11  update bal_assign set existing_balance=315 where leavetype=el;
 12  else
 13     update bal_assign set existing_balance=existing_balance+15 where leavetype=el;
 14        end if;
 15        end loop;
 16   close cur;
 17*       END earn_bal_up;
 18  /

Procedure created.

SQL> call earn_bal_up('Earn Leave');

Call completed.


Here my else clause coding is not working even when the if condition doesnt satisfy ..

Pls help to overcome the same ..

Thanks and Regards
anu

Re: Procedure [message #400488 is a reply to message #400485] Tue, 28 April 2009 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Here my else clause coding is not working even when the if condition doesnt satisfy ..

Prove what you claim.
First correctly indent your code. If you write your code like this you will ALWAYS have bug you can't see.

Clue: take care of NULL.

Regards
Michel
Re: Procedure [message #400490 is a reply to message #400488] Tue, 28 April 2009 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or maybe FETCH didn't ... well, fetch anything?
Re: Procedure [message #400491 is a reply to message #400485] Tue, 28 April 2009 07:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Here my else clause coding is not working even when the if condition doesnt satisfy ..

I would disagree with you - it shall be "working" as you coded it.

I see here one major problem - if there are multiple rows with the same LEAVETYPE, you UPDATE all of them in every LOOP pass - so they are overwritten again and again. In the end, the last UPDATE result (which is not deterministic - it may vary with every execution) is saved.

Have a look at CURRENT OF clause of UPDATE statement in PL/SQL User's Guide and Reference documentation book. Or, get rid of that LOOP at all and do it in one UPDATE statement, something like:
UPDATE bal_assign
SET existing_balance = CASE WHEN existing_balance >= 300 THEN 315
                            ELSE existing_balance + 15
                       END
WHERE leavetype=el;
Re: Procedure [message #400582 is a reply to message #400491] Tue, 28 April 2009 23:50 Go to previous message
anugraha
Messages: 15
Registered: February 2009
Junior Member
Hey Thanks a lot !

Its working for me now ..

Thanks and Regards
anu
Previous Topic: partition user dictionary
Next Topic: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning?
Goto Forum:
  


Current Time: Sat Dec 10 03:16:54 CST 2016

Total time taken to generate the page: 0.15348 seconds