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:
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);
EXCEPTION -- exception handlers begin
WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
WHEN OTHERS THEN -- handles all other errors
END; -- exception handlers and block end here
I don't want to use ANSI standard.
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