Is the cursor variable updated when updating a row?

Question

In the code below (stored procedure body), whether the value of the cursor field is automatically updated after UPDATE or not? If not, is the Close / Open command sufficient again or not?

I didn't find any description that included this, it was just the FOR SELECT cursors in all of them.

DECLARE VARIABLE FCU_VALIDATE TYPE OF COLUMN FCU_CTRL.FCU_VAL_WHEN_IMP;

DECLARE FCU_DOC_MSTR CURSOR FOR
    (SELECT * FROM FCU_DOC_MSTR
     WHERE FCU_DOC_APN = :APNUMBER
       AND FCU_DOC_ID  = :DOCID);
BEGIN
     OPEN FCU_DOC_MSTR;
     
     FETCH FIRST FROM FCU_DOC_MSTR;
     -- CHECK CONTROL FILE SETTINGS
     
     FCU_VALIDATE = COALESCE((SELECT FCU_VAL_WHEN_IMP FROM FCU_CTRL
                              WHERE FCU_INDEX1 = 1), FALSE);
     
     IF (FCU_VALIDATE = TRUE) THEN
     BEGIN
          -- IF EXIST INVALID ITEM DETAIL LINE, SET DOCUMENT STATUS TO INVALID
          IF ((SELECT COUNT(*) FROM FCU_ITEM_DET
               WHERE FCU_ITEM_APN     = :FCU_DOC_MSTR.FCU_DOC_APN
                 AND FCU_ITEM_DOC_ID  = :FCU_DOC_MSTR.FCU_DOC_ID 
                 AND FCU_ITEM_STATUS != '0') > 0) THEN
              UPDATE FCU_DOC_MSTR
                 SET FCU_DOC_STATUS = '90'
              WHERE CURRENT OF FCU_DOC_MSTR;  
     END

     -- CHECK DOCUMENT STATUS IS IMPORTED AND NO ERROR EXIST SET STATUS TO IMPORTED
     IF (FCU_DOC_MSTR.FCU_DOC_STATUS = '99') THEN
        UPDATE FCU_DOC_MSTR
           SET FCU_DOC_STATUS = '0'
        WHERE CURRENT OF FCU_DOC_MSTR;   
      
     IF (FCU_VALIDATE = TRUE) THEN
     BEGIN   
         IF (FCU_DOC_MSTR.FCU_DOC_STATUS = '0') THEN
            UPDATE FCU_DOC_MSTR
               SET FCU_DOC_STATUS = '1'
            WHERE CURRENT OF FCU_DOC_MSTR;     
         
         -- UPDATE FILE STATUS   
         IF ((SELECT COUNT(*) FROM FCU_DOC_MSTR
              WHERE FCU_DOC_FILE_ID = :FCU_DOC_MSTR.FCU_DOC_FILE_ID
                AND FCU_DOC_STATUS != '1') > 0) THEN
             UPDATE FCU_FILE_MSTR
                SET FCU_FILE_STATUS = '90'
             WHERE FCU_FILE_ID = :FCU_DOC_MSTR.FCU_DOC_FILE_ID;
         ELSE
             UPDATE FCU_FILE_MSTR
                SET FCU_FILE_STATUS = '1'
             WHERE FCU_FILE_ID = :FCU_DOC_MSTR.FCU_DOC_FILE_ID;
     END   
      
     CLOSE FCU_DOC_MSTR;
END

Answer 1

If the update is done through the cursor (using UPDATE ... WHERE CURRENT OF _cursor_name_), then the cursor record variable for the current row is also updated.

See this fiddle for a demonstration.

This was not documented in the Firebird 3.0 Release Notes, but it was documented in the doc/sql.extensions/README.cursor_variables.txt included with your Firebird installation. This is also been documented in the Firebird 3.0 Language Reference, under FETCH:

Reading from a cursor variable returns the current field values. This means that an UPDATE statement (with a WHERE CURRENT OF clause) will update not only the table, but also the fields in the cursor variable for subsequent reads. Executing a DELETE statement (with a WHERE CURRENT OF clause) will set all fields in the cursor variable to NULL for subsequent reads