• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to move to next reocrd in for loop end loop construct

 
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello all:
I have the following code in my stored proc to get the entire recordset. I am checking each record in this "TEMPREC" and i m also checking the condition of varialeb "i_copy_Notes". if it is equal to 1 then i want to move to next record. When i use "NEXT" . the compiler flags that next must be declared.
Please let me know how to move to next record.
Thanks

FOR TEMPREC IN
(
SELECT
DP_SER,
GRD_POS,
TS_POS,
ASG_ID,
DESCS,
GRD_ID
FROM DCGRD_POS
WHERE CLLI = i_FROM_CLLI AND SUBSYS = i_SUBSYS AND BAY_ID = v_OLD_BAY_ID ORDER BY GRD_ID
)
LOOP

IF v_OLD_GRDID = TEMPREC.GRD_ID THEN
v_NEW_GRDID := v_NEW_GRDID;

IF i_COPY_NOTES = 1 THEN
NEXT;
END IF;
ELSE
v_OLD_GRDID := TEMPREC.GRD_ID;
SELECT DCGRD_POS_GRD_ID.NextVal INTO v_NEW_GRDID FROM DUAL;
END IF;

INSERT INTO DCGRD_POS VALUES
(
i_TO_CLLI,
DCGRD_POS_ROW_SER.NextVal,
TEMPREC.DP_SER,
v_NEW_GRDID,
v_NEW_BAY_ID,
TEMPREC.GRD_POS,
DECODE(i_COPY_NOTES,1, TEMPREC.TS_POS,''),
TEMPREC.ASG_ID,
DECODE(i_COPY_NOTES,1,TEMPREC.DESCS,'UASGN'),
i_CURRENT_ISSUE,
-1,
i_SUBSYS,
SYSDATE,
i_USERID
);

END LOOP;NEXT
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In Forms there is a NEXT_RECORD command that moves to next record.
In PL/SQL you need to Fetch the next record, this can be done in a few ways
1. Open the Cursor,
2. Fetch into a variable/record that is of %rowtype of the cursor.
3. After going through the records you Close the cursor.
other way
1. There is a FOR loop command that autopmatically will place the row into the decalred record of %rowtype of the cursor.
For exact syntax, look at Oracle's OTN site, or search in Google's newgroups for the syntax.
Mark
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic