This section shows examples in which a cursor is used.
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
:
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
:
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
: