Here we are trying to update the coupon field of person_table_1 table by using the coupons from coupon_id field of coupon_table table. So we are required to hold the coupon field of person_table_1 and coupon_id field of coupon_table simultaneously.

In order to avoid any tampering of coupons while merging in to person_table_1, a lock has been set on coupon column protecting against any accidental DML operations. Care has been taken so that in case of in sufficient number of coupons no merging is done. In that case, control passes into user exception for catching.

When the loop runs for first time a coupon from coupon table flows in to a variable. Then by using the "current of" cursor the coupon is merged into first coupon field of person_table_1 table. Then the used coupon’s status has been set to "Y" in coupon_table after being merged successfully. Loop continues until all the coupons in person_table_1 table are updated.

When all the coupons are merged successfully "commit" is done. Commit frees the lock set upon the coupon field of person_table_1. In case of any other errors that may happen in between, they could be handled by "when others" and rollback is done to cancel incomplete merge.

Structures of tables before coupon merging is done

PERSON_TABLE_1 (contains 20 person_id) before merging.

Figure 1

COUPON_TABLE (contains 21 unmerged coupons with status "N")

Figure 2

Executing the procedure

Listing 2

Exec multiple_table_update;

Figure 3

Structures of tables after coupon merging is done

PERSON_TABLE_1 after merging.

Figure 4

COUPON_TABLE (contains 1 live coupon with status "N")

Figure 5