Home » SQL & PL/SQL » SQL & PL/SQL » Loops ....
Loops .... [message #251752] Mon, 16 July 2007 09:50 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Hi All,

Am new to Pl /sql.
I have the following Query .....

when are loops generally used?

can we just insert new data using loops or is ot possible to update large amount of data using loops?

for eg: if we want to insert new employees but with different job_id's in different departments is it possible to do it through loops? or is it neccesary to insert employees with the same job-id's together and then move to a dfferent one?

ihope my question is clear.

Regards,
avril.
Re: Loops .... [message #251756 is a reply to message #251752] Mon, 16 July 2007 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
when are loops generally used?

They are too often badly used, so don't look at any code to get a good exemple or maybe Oracle documentation but I'm not sure there are not still old exemples.

Quote:
can we just insert new data using loops or is ot possible to update large amount of data using loops?

You can do what you want in loop but please have a look at bulk operations.

Quote:
if we want to insert new employees but with different job_id's in different departments is it possible to do it through loops?

It is a job for insert one by one, there is no loop.
There must be never loop for this kind of task.

Regards
Michel
Re: Loops .... [message #251772 is a reply to message #251756] Mon, 16 July 2007 11:01 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
thanks for the prompt reply.

but do u mean that to insert the data we need to it by one by one insert for same job_ids?



what can be an alternative to using loops to insert or update data?


is it possible to use cursors all the time and perform all operations that can be performed by using a loop?
Re: Loops .... [message #251775 is a reply to message #251772] Mon, 16 July 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but do u mean that to insert the data we need to it by one by one insert for same job_ids?

If you want to insert N employees I don't see any other way than using N inserts.
What do you want to do? A large dummy insert followed by N updates? What is the interest?

Quote:
is it possible to use cursors all the time and perform all operations that can be performed by using a loop?

What is the difference between using cursor or loop for you?

Regards
Michel


Re: Loops .... [message #251779 is a reply to message #251775] Mon, 16 July 2007 11:21 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
thanks for the prompt reply.


according to me with a cursor user can fetch all the data at a time in the cursor.

while using loops it does a row-wise insert which doesnt seem as efficient as cursors.

correct me if i am wrong.

regards,
avril.

Re: Loops .... [message #251780 is a reply to message #251779] Mon, 16 July 2007 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you fetch data from a cursor?
Isn't it in a loop?

Regards
Michel
Re: Loops .... [message #251804 is a reply to message #251752] Mon, 16 July 2007 13:05 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
also if you are using a cursor to generate a loop and then doing inserts, replace it with a simple select insert. For example you could have something like

begin
cursor junk is
select col1,col2,col3
from my_table;

begin
 for pnt in junk loop
   insert into my_table2(col1,col2,col3) values(pnt.col1,col2,col3);
end loop;
end;



or you could replace the entire code with

insert into my_table2(col1,col2,col3)
select col1,col2,col3
from my_table;
Previous Topic: PLS-00201 identifier must be declared
Next Topic: error 00920 ??
Goto Forum:
  


Current Time: Mon Dec 05 02:27:58 CST 2016

Total time taken to generate the page: 0.12042 seconds