Re: Store Procedure Help

Comments embedded.
Aaron Rouse wrote:
> I am trying to create a pretty simple stored procedure but when I try
> to add my incrementing value I get errors on the = sign in the compile
> process. What I need to do is insert the CD_SYSKEY_NEXT value I pull
> from TMS_SYSKEY into my SLB_ABR_TEST table and then increment that by
> one for each loop instance then insert the last value into the
> TMS_SYSKEY table for future use. However I am to the point below and
> am unsure how to correct it and move on with the procedure:
>
> CREATE OR REPLACE procedure SP_SLB_TEST AS
> CURSOR LOC IS
> SELECT CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG FROM
> LOCATIONS WHERE CD_LOC NOT IN (SELECT CD_LOC FROM LOCATIONS_DTL);
I sure hope you don't have a lot of records in LOCATIONS_DTL.

>
> CURSOR NEWID IS
> SELECT MAX(CD_SYSKEY_NEXT) AS LOC_ID FROM TMS_SYSKEY WHERE
> NM_TABLE = 'LOCATIONS_DTL';

Are you really storing *every* CD_SYSKEY_NEXT in this table ... or only the highest one so far for the LOCATIONS_DTL table? If you're not storing every one, then you don't need the max() function.

>
> loopLOC_ID NUMBER(3);
>
> loopLOC_ID := NEWID.LOC_ID;

Replace this with:

   open newid;
   fetch loc_id into looploc_id;
   close newid;

>
> BEGIN
> FOR i IN LOC LOOP
> -- loopLOC_ID would be inserted into ID_LOC
> -- Insert in LOCATIONS_DTL
> INSERT INTO SLB_ABR_TEST
> (CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG)
> VALUES
> (i.CD_LOC, i.NM_LST_UPDT, i.DT_LST_UPDT, i.CD_SEG);
> -- loopLOC_ID := loopLOC_ID + 1;

Uncomment this line.

> COMMIT;

Probably better to move this outside the FOR loop.

> END LOOP;
> -- Need to insert the loopLOC_ID into TMS_SYSKEY
Add this:

   update tms_syskey
     set cd_syskey_next = looploc_id
   where nm_table = 'LOCATIONS_DTL';

> END SP_SLB_TEST;

You might want to look up the use of cursors in the PL/SQL doc set. You have to fetch a record ... you can't just refer to it like it's a variable (eg, newid.loc_id) until after you do. Received on Thu Jan 09 2003 - 18:14:02 CST