need to repeat process until the all data fetched [message #611690] |
Mon, 07 April 2014 05:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I am Write code for re filling some data for student with new sit_number . I write this code but get first record only and i need to repeat process until the all data fetched.
Please find the below attached code.
Thank you,
|
|
|
|
|
|
Re: need to repeat process until the all data fetched [message #611695 is a reply to message #611694] |
Mon, 07 April 2014 06:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/68b0ba068684db8e6bf035f30bbd40d5?s=64&d=mm&r=g) |
mehboob110233
Messages: 55 Registered: February 2014 Location: Pakistan
|
Member |
![mahboob30699@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
BEGIN
for i in ( SELECT a. stud_id,
a.user_stud_id,
a.school_year_id,
a.school_act_year_id,
a.school_act_year_deptid
FROM std_study_level_students a
WHERE user_stud_id NOT IN (SELECT user_stud_id
FROM std_outgoing)
AND school_year_id = (SELECT Max (school_year_id)
FROM std_study_level_students)
ORDER BY a.school_act_year_id,
a.school_act_year_deptid DESC,
To_number(user_stud_id)
loop
INSERT INTO std_numbers_sit_detial
(numbers_sit_id_det,
user_numbers_sit_id_det,
numbers_sit_id,
user_stud_id,
stud_id,
number_sit,
school_year_id,
unversity_year,
school_act_year_id,
user_school_act_year_deptid,
semester_id)
VALUES (std_numbers_sit_detial_sq_1.NEXTVAL,
std_numbers_sit_detial_sq_2.NEXTVAL,
:STD_NUMBERS_SIT.numbers_sit_id,
i.user_stud_id,
i.stud_id,
Std_sit_numbers(i.school_year_id, i.school_act_year_deptid,
:STD_NUMBERS_SIT.numbers_sit_id),
i.school_year_id,
:STD_NUMBERS_SIT.unversity_year,
i.school_act_year_id,
i.school_act_year_deptid,
:STD_NUMBERS_SIT.semester_id);
COMMIT;
END;
I think it work for you.....
|
|
|
Re: need to repeat process until the all data fetched [message #611698 is a reply to message #611694] |
Mon, 07 April 2014 07:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Mon, 07 April 2014 12:00Hi cookiemonster, did you download the file?
My post states that not everyone can/will download the file and asks you to post the code directly in code tags as you normally do.
So why haven't you just posted the code directly as asked?
|
|
|
|
|
Re: need to repeat process until the all data fetched [message #611735 is a reply to message #611734] |
Tue, 08 April 2014 05:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
As Cookiemonster said, rewrite it as a single statement; you don't need PL/SQL for that. Something likeINSERT INTO std_numbers_sit_detial (numbers_sit_id_det,
user_numbers_sit_id_det,
numbers_sit_id,
user_stud_id,
stud_id,
number_sit,
school_year_id,
unversity_year,
school_act_year_id,
user_school_act_year_deptid,
semester_id)
(SELECT std_numbers_sit_detial_sq_1.NEXTVAL,
std_numbers_sit_detial_sq_2.NEXTVAL,
:STD_NUMBERS_SIT.numbers_sit_id,
a.user_stud_id,
a.stud_id,
Std_sit_numbers (a.school_act_year_id,
a.school_act_year_deptid,
:STD_NUMBERS_SIT.numbers_sit_id),
a.school_year_id,
:STD_NUMBERS_SIT.unversity_year,
a.school_act_year_id,
a.school_act_year_deptid,
:STD_NUMBERS_SIT.semester_id
FROM std_study_level_students a
WHERE user_stud_id NOT IN (SELECT user_stud_id FROM std_outgoing)
AND school_year_id =
(SELECT MAX (school_year_id) FROM std_study_level_students));
|
|
|
|