When using a cursor in a DB2 database, you must be cautious when using commit. This article introduces some precautions for using a cursor in DB2 for your reference and hopes to help you.
When using a cursor in DB2, you must use commit with caution. This may be because the session is closed after the commit is completed, causing db2 to report an unopened cursor error, in this case, commands such as commit cannot be included when the cursor is used, which may cause a cursor error.
When using the while LOOP cursor, we must declare an exception so that we can determine whether the cursor has reached the last point, that is, the cursor will not be able to find a new volume after it, which must be processed here, otherwise, the cursor will keep repeating the last message.
The following statement is required:
Declare continue handler for not found
Begin
Set v_notfound = 1;
End;
When the cursor is opened, give v_notfound = 0 and then go through the loop. As long as the cursor does not find a value, it will assign 1 to v_notfound so that the loop can be controlled, and achieve what we want.
DB2 also needs to note that when the type is changed to char, the system will fill him with spaces, which is something we should trim for him.
When a cursor calls a stored procedure, if the called stored procedure has a problem, the system also reports an error that the cursor is not opened.