I have to migrate all procedures from Oracle to DB2 database. I'd like to know, how do I do SET syntax inside of IF as Oracle works.
Variable has been using in Oracle is ps_iPkCooperativa and are receiving 0.
Oracle Code :
BEGIN
SELECT CCOOP
INTO ps_iPkCooperativa
FROM COOP
EXCEPTION
WHEN NO_DATA_FOUND
THEN ps_iPkCooperativa := 0;
END;
How do I do this in DB2 ?
you can make such a conversion to db2 :
BEGIN ATOMIC
DECLARE ps_iPkCooperativa INT;
SELECT CCOOP
INTO ps_iPkCooperativa
FROM COOP;
SIGNAL SQLSTATE '02000' -- means no_data_found
SET MESSAGE_TEXT='No Data Found ';
SET ps_iPkCooperativa = 0;
END@
where
The ATOMIC compound statement, as the name suggests, can be thought of as a singular whole—if any unhandled error conditions arise within it, all statements which have been executed up to that point are considered to have failed as well and are therefore rolled back. ATOMIC compound statements cannot be nested inside other ATOMIC compound statements.
In addition, you cannot use SAVEPOINTs or issue explicit COMMITs or ROLLBACKs from within an ATOMIC compound statement.
NOTE
COMMIT, ROLLBACK, SAVEPOINTS and nested ATOMIC compound statements are not allowed within an ATOMIC compound statement.