Help with Cursor Update/delete/insert

From: <gcoyle_at_cbs.webramp.net>
Date: Wed, 15 Sep 1999 20:02:18 GMT
Message-ID: <37dff819.16466463@news.supernews.com>

Hi,

I need to select each record from gctemp and process each record based on the trans_code into the mc_dir table.

The trans_codes do the following:

{Peudo Code
if loc_trancode = 'A' and not found in mc_dir

              insert into mc_dir

if loc_trancode = 'D' then

              delete from mc_dir
if loc_trancode = 'A' then

              Update mc_dir }

I query using:

select lastname,firstname,number_,TRAN_CODE,uid_  from Mc_Dir MC, gctemp GC
  where

   UPPER(mc.lastname) = UPPER(gc.last_name) and
   UPPER(mc.firstname) = UPPER(gc.first_name) and
   UPPER(mc.number_) = UPPER(gc.extsn) ;

----------------Gives entries in both tables------------

I can's seem to update with the fetch records and i have not started to syntax for records not found in MC_dir but found in gctemp do insert with fetched records.

Any help would be great See source below, Thanks,
GC

declare

loc_lastname MC_DIR.LASTNAME%TYPE;
loc_firstname MC_DIR.FIRSTNAME%TYPE;
loc_number_ MC_DIR.NUMBER_%TYPE;
loc_trancode GCTEMP.TRAN_CODE%TYPE;
loc_uid  mc_dir.uid_%type;

 cursor compare_tables is

    select lastname,firstname,number_,TRAN_CODE,uid_  from Mc_Dir MC, gctemp GC
  where

   UPPER(mc.lastname) = UPPER(gc.last_name) and
   UPPER(mc.firstname) = UPPER(gc.first_name) and
   UPPER(mc.number_) = UPPER(gc.extsn) ;

begin
dbms_output.enable;
 open compare_tables;
  loop
    fetch compare_tables
      into
loc_lastname,loc_firstname,loc_number_,loc_trancode,loc_uid;

       if compare_tables%notfound
        then 
           exit;
         elsif loc_trancode = 'D' then
              delete from mc_dir
                 where 
                  mc_dir.lastname = loc_lastname and
                  mc_dir.firstname = loc_firstname and
                  mc_dir.number_ = loc_number_;
                  commit; 

/* the problem statement**************/

           elsif loc_trancode = 'A' then   
           Update mc_dir 
           set = mc_dir.lastname = loc_lastname and
                   mc_dir.firstname = loc_firstname and
                   mc_dir.number_ = loc_number_;
                  commit;              
exit;
                     
        end if;
   

  dbms_output.put_line('Processed student: '||loc_lastname );  end loop;
 close compare_tables;
end;
/ Received on Wed Sep 15 1999 - 15:02:18 CDT