All Forums Database
ehsanabbasi 3 posts Joined 08/10
10 Jan 2013
Conditional Rollback (Oracle PLSQL to TD SP)

Hi,
I am converting and Oracle PL/SQL Code that supports COMMIT and ROLLBACK. I know we can use BT, ET statements to enclose the transactions. But what about if I want to do conditional Commit and rollback. Like COMMIT if A=B and RollBack if A<>B.
Example of Oracle PLSQL Code to be converted is as follows:
DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;
   ...
   WHEN OTHERS THEN  -- handles all other errors
      ROLLBACK;
END;  -- exception handlers and block end here
 
I don't want to use ANSI standard.
Regards,
Ehsan

dnoeth 4628 posts Joined 11/04
13 Jan 2013

Hi Eshan,
in your example you simply remove the COMMITs and the WHEN OTHERS THEN ROLLBACK, because any error automatically rolls back.
Of course you can simply avoid the division-by-zero error:
SELECT price / NULLIF(earnings,0) INTO pe_ratio FROM stocks
Dieter

Dieter

You must sign in to leave a comment.