Data which has been fetched using a rowset positioning cursor can be subsequently modified by using positioned UPDATEs and DELETEs. You can either modify all of the rows in the rowset or just one specific row in the rowset depending up on requirment.
IF you specify WHERE CURRENT OF CURSOR, all of the rows in the rowset will be affected (same hold true for delete operation).
For example the following SQL statement will impact the current rowset.
UPDATE EMP SET SALARY = 1000 WHERE CURRENT OF C1;
DELETE EMP WHERE CURRENT OF C1;
Inorder to modify a single row in rowset, you need to use FOR CURSOR CSR FOR ROW n OF ROWSET clause. This clause direct DB2 to updated specific row in rowset. For example.
UPDATE EMP SET SALARY = 1000 FOR CURSOR C1 FOR ROW :HV OF ROWSET;
DELETE EMP FOR CURSOR C1 FOR ROW 3 OF ROWSET;
Inserting Multiple Row.
Similar to fetch processing, Insert processing can be used in conjunction with the FOR n ROWS Clause. Consider using this feature to insert multiple rows with a single staement when your application program required to insert bulk data into DB2 table.
By coding you program to fill up an array with data to be inserted and then using INSERT .... FOR n ROWS, the insert process can be made more efficient. For example.
INSERT INTO EMP_TBL FOR :n ROWS VALUES(:HV-ARRAY1, :HV-ARRAY2,......) ATOMIC;
By specifying ATOMIC, if one row fails, every row fails. On the other hand specifying NOT ATOMIC allows each row to fail or succeed on its accord. Take row count and row size into consideration before using specify ATOMIC.
IF you specify WHERE CURRENT OF CURSOR, all of the rows in the rowset will be affected (same hold true for delete operation).
For example the following SQL statement will impact the current rowset.
UPDATE EMP SET SALARY = 1000 WHERE CURRENT OF C1;
DELETE EMP WHERE CURRENT OF C1;
Inorder to modify a single row in rowset, you need to use FOR CURSOR CSR FOR ROW n OF ROWSET clause. This clause direct DB2 to updated specific row in rowset. For example.
UPDATE EMP SET SALARY = 1000 FOR CURSOR C1 FOR ROW :HV OF ROWSET;
DELETE EMP FOR CURSOR C1 FOR ROW 3 OF ROWSET;
Inserting Multiple Row.
Similar to fetch processing, Insert processing can be used in conjunction with the FOR n ROWS Clause. Consider using this feature to insert multiple rows with a single staement when your application program required to insert bulk data into DB2 table.
By coding you program to fill up an array with data to be inserted and then using INSERT .... FOR n ROWS, the insert process can be made more efficient. For example.
INSERT INTO EMP_TBL FOR :n ROWS VALUES(:HV-ARRAY1, :HV-ARRAY2,......) ATOMIC;
By specifying ATOMIC, if one row fails, every row fails. On the other hand specifying NOT ATOMIC allows each row to fail or succeed on its accord. Take row count and row size into consideration before using specify ATOMIC.
No comments:
Post a Comment