Re: Help with Cursor Update/delete/insert

From: <gcoyle_at_cbs.webramp.net>
Date: Mon, 20 Sep 1999 02:13:44 GMT
Message-ID: <37e596b4.16754359@news.supernews.com>

On Fri, 17 Sep 1999 21:24:12 GMT, michael_bialik_at_my-deja.com wrote:

Michael,

Thanks for all your help. I've run into some errors (listed at the bottom) that are stumping me.

Any help would be great.

Thanks again for your reponse.

GC

SQL> r
  1 declare

  2  loc_lastname MC_DIR.LASTNAME%TYPE;
  3  loc_firstname MC_DIR.FIRSTNAME%TYPE;
  4  loc_number MC_DIR.NUMBER_%TYPE;
  5  loc_trancode GCTEMP.TRAN_CODE%TYPE;
  6   cursor compare_tables is
  7      select lastname,firstname,number_,TRAN_CODE

  8 from Mc_Dir MC, gctemp GC
  9 where

 10     UPPER(mc.lastname)(+) = UPPER(gc.last_name) and
 11     UPPER(mc.firstname)(+) = UPPER(gc.first_name)and
 12     UPPER(mc.number_)(+) = UPPER(gc.extsn) ;

 13 begin
 14 dbms_output.enable;
 15 open compare_tables;
 16 loop

 17      fetch compare_tables
 18        into loc_lastname,loc_firstname,loc_number,loc_trancode;
 19          /*if compare_tables%notfound then 
 20           exit; */
 21         if loc_trancode = 'A' then if loc_number = null
 22                  insert into mc_dir
(mc.lastname,mc.firstname,mc.number_)
 23                   values (loc_lastname,loc_firstname,loc_number);
 24                 commit;
 25           elsif loc_trancode = 'D' then
 26             delete from mc_dir
 27                  where mc_dir.lastname = loc_lastname and
 28                   mc_dir.firstname = loc_firstname and
 29                    mc_dir.number_ = loc_number;
 30                    commit;
 31          end if;

 32 dbms_output.put_line('Processed student: '||loc_lastname );  33 end loop;
 34 close compare_tables;
 35* end;
declare
*
ERROR at line 1:

ORA-06550: line 22, column 17: 
PLS-00103: Encountered the symbol "INSERT" when expecting one of the
following: 

* & - + / mod rem then an exponent (**) and or || The symbol "then" was substituted for "INSERT" to continue.

ORA-06550: line 33, column 6: 
PLS-00103: Encountered the symbol "LOOP" when expecting one of the
following: 

if

ORA-06550: line 36, column 0: 
PLS-00103: Encountered the symbol ";" when expecting one of the
following: 

begin function package pragma procedure form

SQL> spool off

*********OLD MESSAGE****
>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 Sun Sep 19 1999 - 21:13:44 CDT