Re: Help with Cursor Update/delete/insert

From: <michael_bialik_at_my-deja.com>
Date: Fri, 17 Sep 1999 21:24:12 GMT
Message-ID: <7rubhf$sve$1@nnrp1.deja.com>

Hi.

 The statement :
 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)

 does not select any new entries from GCTEMT table (  that must be inserted into MC_DIR ) because  WHERE condition is NOT met.
 Use OUTER JOIN :

 select

   gc.lastname  new_lname,
   gc.firstname new_fname,
   gc.extsn     new_number,
   mc.lastname  old_lname,
   mc.firstname old_fname,
   mc.number_   old_number,
   gc.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) ;

 Now you will be able to use following logic :

 IF TRAN_CODE = 'A' THEN
   IF old_number IS NULL /* Row does NOT exists in MC_DIR*/      INSERT INTO MC_DIR ...
   ELSE
     UPDATE MC_DIR SET ...
   END IF;
 ELSIF TRAN_CODE = 'D' THEN
   IF old_number IS NULL /* Row does NOT exists in MC_DIR*/      /* Do nothing OR issue an error */    ELSE
     DELETE FROM MC_DIR ...
   END IF;
 ELSE /* Un-expected TRAN_CODE */
  RAISE application_error...
 END IF;  Michael.

In article <37dff819.16466463_at_news.supernews.com>,   gcoyle_at_cbs.webramp.net wrote:
> 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;
> /
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 17 1999 - 16:24:12 CDT