Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Insert loop
Dynamic Insert loop [message #234619] Wed, 02 May 2007 13:03 Go to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
Hi... I'm using a stored procedure and will set a value for housemembers. I can insert into the house, but I want to dynamically insert into the housefund as well - the number of times that need to be inserted is dependent on @housemembers
I can't seem to get right the for statement... Can someone please modify the code...

Thanks.

Procedure Add_family (@housemembers in int)
is

begin

insert into house
(housesequencenumber,
fathername,
housemembers)
values
(1,'joe', @housemembers)

/* can't figure out how to write the for statement here.. */
for i = 1 to @housemembers
insert into housefund
(housefundsequencenumber,
fathername)
values
(1,'joe')
next for /* or however it is done */

END Add_family;
Re: Dynamic Insert loop [message #234623 is a reply to message #234619] Wed, 02 May 2007 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can't seem to get right the for statement

It seems to me you rather never tried to read a PL/SQL book and you just want us to make your job.

Regards
Michel
Re: Dynamic Insert loop [message #234625 is a reply to message #234623] Wed, 02 May 2007 13:18 Go to previous messageGo to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
I'm trying not to get mad by your comment... I'm looking at 1 or 2 lines of code - I didn't ask for you to write the procedure... I'm trying to figure how to do this without using a cursor.
Re: Dynamic Insert loop [message #234629 is a reply to message #234625] Wed, 02 May 2007 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but there is so many errors (as there would be if I try to compile a C program with a Perl compiler) that I can't give you just "1 or 2 lines of code". The whole procedure is not PL/SQL and so is wrong.

Regards
Michel
Re: Dynamic Insert loop [message #234631 is a reply to message #234629] Wed, 02 May 2007 13:32 Go to previous messageGo to next message
Alexander77
Messages: 8
Registered: May 2007
Junior Member
I figured it out myself - was this so hard to answer????


for i in 1 .. @parameter
loop
(insert statement....);
end loop;
Re: Dynamic Insert loop [message #234637 is a reply to message #234619] Wed, 02 May 2007 13:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>was this so hard to answer????
With free advice, you get what you paid for it.
If you are ever dissatisfied with any response here, you are entitled to a complete & 100% refund.
Re: Dynamic Insert loop [message #234638 is a reply to message #234631] Wed, 02 May 2007 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, you corrected one error.
See, you can do it by yourself and, more, you have learned something you don't forget now.

Regards
Michel

Re: Dynamic Insert loop [message #234714 is a reply to message #234638] Thu, 03 May 2007 00:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are sure you are on Oracle, right? You don't have a SQLServer database by accident?
Previous Topic: Date Function Returning Uncertain results
Next Topic: Hirarchial Order..
Goto Forum:
  


Current Time: Sun Dec 04 18:42:57 CST 2016

Total time taken to generate the page: 0.08273 seconds