Both Oracle and PostgreSQL use cursors to encapsulate a query and process every individual row. However, there are minor syntax differences that must be handled properly during migration from Oracle to PostgreSQL:
%NOTFOUNDare used to find whether an SQL statement affected one or more rows in Oracle PL/SQL. It must be converted into
NOT FOUNDin PostgreSQL code.
%ROWCOUNTelement represents number of rows processed by an SQL statement in Oracle PL/SQL. PostgreSQL provides equivalent
ROW_COUNTfor the same purpose.
REF CURSORis Oracle data type that represents the cursor, every cursor variable must be defined with a type derived from
REF CURSOR. PostgreSQL provides equivalent
REFCURSORfor the same purpose.
Oracle allows to iterate rows in cursor using the following cycle:
FOR record_variable IN cursor_variable LOOP ... END LOOP;
In PostgreSQL the following construction may be used for the same purpose:
LOOP FETCH FROM cursor_variable INTO record_variable; EXIT WHEN NOT FOUND; ... END LOOP;