Conditional Rollback (Oracle PLSQL to TD SP)

Database

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

1 REPLY
Senior Apprentice

Re: Conditional Rollback (Oracle PLSQL to TD SP)

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