CodeSnip: Practical Use of "for update of" in Oracle Cursor
page 3 of 5
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25279/ 40


Let us examine the following code.

Listing 1

procedure multiple_table_update
      /* Declaring variables*/
   v_coupon_id                  varchar2(15);
   validate_coupon_count        number;
   low_count_error              EXCEPTION;
   Live_coupons_left            number;
   coupons_used                 number;
   coupons_merged               number;
   count_original_coupons       number;
   count_persons                number;
   Error_code                   varchar2(400);
   Error_message                varchar2(400);
      /* declaring cursor for setting explicit locking on 
         coupon field before the updating*/
   cursor  cur_person 
   select person_id,coupon from person_table_1 where coupon is NULL
   for update of coupon;
      /* declaring cursor for selecting unused coupons one at a time */
   cursor cur_coupon
   select coupon_id from coupon_table
   where flag ='N' and rownum = 1;
       /* Selecting the number of coupons before merging */
    select count(coupon_id) into count_original_coupons 
from coupon_table where    flag='N';
' number of coupons present before merging');
    /* Selecting the number of coupons required before merging */
    select count(PERSON_ID) into count_persons from person_table_1;
    dbms_output.put_line(count_persons||' number of Persons to be merged');
    /* if number of coupons found less than required, control passes 
        to user defined exception */
select (count_original_coupons - count_persons) 
into validate_coupon_count from dual;
        validate_coupon_count < 0 then
        RAISE low_count_error;
        /*coupon merging started */
        dbms_output.put_line('coupon merging started');
        /*open cur_ person cursor */
        for person_rec in cur_person
         /*open, fetch and direct first coupon in coupon_table in to a variable*/
    open cur_coupon;
          fetch cur_coupon into v_coupon_id;
          close cur_coupon;
/*update current row of person_table_1 table by putting the coupon in to it*/
          update person_table_1
          set coupon = v_coupon_id
          where current of cur_person;
           /*mark used coupon by setting flag to ‘Y’ so can’t be used again*/
          update coupon_table set flag='Y'
          where coupon_id=v_coupon_id;
          dbms_output.put_line(v_coupon_id||' '||cur_person%rowcount||
            ' Coupon fetched  '||'merged  '||'and Flag Updated ');
        end loop;
select count(coupon_id) into Live_coupons_left from coupon_table where flag='N';
        select count(coupon) into coupons_merged from person_table_1;
        dbms_output.put_line(coupons_merged||'Coupons merged successfully and '||
          Live_coupons_left||' coupons left');
    end if;
          /*Catching exception for insufficient coupons*/
      WHEN low_count_error then
      dbms_output.put_line('Failed due to Insufficient coupons');    
      WHEN OTHERS then
      /*Catching exception for other reasons and rolling back incomplete merge*/
     dbms_output.put_line('Failed due to '||Error_message|| 
        '  having error code'||Error_code ); 
end multiple_table_update;

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-04-15 7:12:13 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search