New to SQL here...
We have a CURSOR declared thusly:
EXEC SQL
DECLARE ALL-ADJSTMTS-CSR CURSOR FOR
SELECT ACCT.ACCOUNT_ID
, ACCT.APPL_ID
, ACCT.BRANCH_NUMBER
, ACCT.CATEGORY_CODE
, ACCT.OPEN_DATE
, ADJ.STATUS_IND
, ADJ.PRICE_GROUP_DETAIL_ID
, ADJ.ADJUSTMENT_AMT
, ADJ.INVOICE_ID
FROM ACCOUNTS.ACCOUNT_ADJUSTMENTS ADJ
JOIN ACCOUNTS.ACCOUNT_MASTER ACCT
ON ACCT.ACCOUNT_ID = ADJ.ACCOUNT_ID
WHERE ACCT.APPL_CODE = :ACCT-APPL-CODE
AND ACCT.BRANCH_NUMBER = :BCH-BRANCH-NBR
AND ADJ.STATUS_IND = 'O'
END-EXEC
After fetching a row we may need to update the following columns:
ADJ.STATUS_IND
ADJ.INVOICE_ID
ADJ.LAST_ID
The first thought was to use WHERE CURRENT OF <cursor-name>, ie:
EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE CURRENT OF ALL-ADJSTMTS-CSR
END-EXEC
You can't use the FOR UPDATE clause in the DECLARE, however, if the SELECT
joins two tables (and other reasons, of course). This makes sense. So
instead we just do a read-only cursor and update, when necessary, like
this:
EXEC SQL
UPDATE ACCOUNTS.ACCOUNT_ADJUSTMENTS
SET STATUS_IND = :ACCT-ADJ-STATUS-IND
, INVOICE_ID = :ACCT-ADJ-INVOICE-ID
, LAST_ID = :ACCT-ADJ-LAST-ID
WHERE ADJUSTMENT_ID = :ACCT-ADJ-ADJUSTMENT-ID
END-EXEC
Is this standard practice, or am I missing an (obvious) other way to do it?
Thanks,
Frank