This example discounts the price (PRICE) values by 10% while using a cursor (CR1) to retrieve all rows from a stock table (STOCK).

    :
EXEC SQL BEGIN DECLARE SECTION;
  char xpcode[5] ; ..........................................1
  char xpname[17]; ............................................1
  char xcolor[3];  ............................................1
  int xprice;   ..............................................1
  int xsquantity;  ...........................................1
EXEC SQL END DECLARE SECTION;
   :
EXEC SQL DECLARE CR1 CURSOR FOR
    SELECT * FROM STOCK
     FOR UPDATE OF PRICE; ...............................2
EXEC SQL OPEN CR1; ......................................3
EXEC SQL FETCH CR1
INTO:xpcode,:xpname,:xcolor,:xprice,:xsquantity;.......4
EXEC SQL UPDATE STOCK
    SET PRICE=0.9*:xprice
    WHERE CURRENT OF CR1; .............................5
EXEC SQL CLOSE CR1; ...................................6
   :

Explanation:

1. Declares an embedded function to be used in retrieval, update, and insertion.
2. Declares cursor CR1. FOR UPDATE OF column-name is specified in this statement because cursor CR1 will be used to update only the PRICE column.
3. Opens cursor CR1.
4. Fetches the value from the PRICE column of the row indicated by cursor CR1 and places the value in embedded variable (:xprice).
5. Discounts the PRICE value by 10% (0.9*:xprice).
6. Closes cursor CR1.

This example updates a stock table (STOCK) while using a cursor (CR1) to retrieve all rows from the table. The example then inserts a row without using the cursor (CR1).

    :
EXEC SQL BEGIN DECLARE SECTION;
  char xpcode[5]; ......................................1
  char xpname[17] ; ....................................1
  char xcolor[3] ; .....................................1
  int xprice ; ........................................1
  int xsquantity ; ....................................1
EXEC SQL END DECLARE SECTION;
          :
EXEC SQL DECLARE CR1 CURSOR FOR
    SELECT * FROM STOCK
      FOR UPDATE;       .................................2
EXEC SQL OPEN CR1;         ..................................3
EXEC SQL FETCH CR1
 INTO:xpcode,:xpname,:xcolor,:xprice,:xsquantity; ......4
EXEC SQL UPDATE STOCK
    SET QUANTITY=:xsquantity+100
    WHERE CURRENT OF CR1; .............................5
EXEC SQL INSERT INTO STOCK
VALUES(:xpcode,:xpname,:xcolor,:xprice,:xsquantity); ...6
EXEC SQL CLOSE CR1; ....................................7
          :

Explanation:

1. Declares an embedded function to be used in retrieval.
2. Declares cursor CR1. Cursor CR1 is used to update the table, and the FOR UPDATE clause, is specified for row insertion without using cursor CR1.
3. Opens cursor CR1.
4. Fetches the values from the row indicated by cursor CR1 and places the values in the embedded variables.
5. Adds 100 to the QUANTITY value.
6. Inserts a row into the STOCK table without using cursor CR1.
7. Closes cursor CR1.

This example modifies the price (PRICE) values to 50% of the original values while using a cursor (CR1) to retrieve all rows from a stock table (STOCK). The cursor (CR1) is left open and is used for another manipulation.

      :
EXEC SQL BEGIN DECLARE SECTION:
  char xpcode[5] ; .....................................1
  char xpname[17] ; ....................................1
  char xcolor[3] ; .....................................1
  int xprice ;     ......................................1
  int xsquantity ; ....................................1
END DECLARE SECTION ;
      :
EXEC SQL LOCK TABLE STOCK
    IN EXCLUSIVE MODE UNTIL DISCONNECT; .................2
      :
EXEC SQL DECLARE CR1 CURSOR WITH HOLD FOR
    SELECT * FROM STOCK
      FOR UPDATE OF PRICE ..............................3
EXEC SQL OPEN CR1; ....................................4
EXEC SQL FETCH CR1
  INTO :xpcode,:xpname,:xcolor,:xprice,:xsquantity; ....5
EXEC SQL UPDATE STOCK SET PRICE=0.5*:xprice
  WHERE CURRENT OF CR1; ................................6
Decision for executing next COMMIT statement in
1000-row units                       ...................7
EXEC SQL COMMIT;  ......................................8
Execution of the next CLOSE statement after all rows
have been updated  .....................................9
EXEC SQL CLOSE CR1; ....................................10
      :

Explanation:

1. Declares an embedded variable (for example, :xprice) to be used in retrieval and update.
2. Locks the STOCK table with a LOCK statement in which UNTIL DISCONNECT is specified, so that a holdable cursor can be used. This statement also specifies a lock mode (IN EXCLUSIVE MODE) because the cursor is used for updating the table.
3. Declares cursor CR1. The cursor declaration specifies WITH HOLD because the cursor is a holdable cursor. The PRICE column is specified in the FOR UPDATE OF clause because PRICE is the only column to be updated.
4. Opens cursor CR1.
5. Fetches the value from the PRICE column in the row indicated by cursor CR1 and places it in embedded variable (:xprice).
6. Modifies the PRICE value to 50% of the original value (0.5*:xprice).
7. Specifies the decision for executing the next COMMIT statement for each 1,000 rows to be updated or for continuing the update process if the COMMIT statement is not to be executed.
8. Commits the update process.
9. Specifies the decision for executing the next CLOSE statement if there are no rows to be updated or continuing the update process if there are still rows to be updated.
10. Closes cursor CR1.