Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Oracle Development
Reload this Page

For Loop using 'i'

Discuss For Loop using 'i' in the Oracle Development forum on Dev Shed.
For Loop using 'i' Oracle Development forum discussing administration, Oracle queries, and other Oracle-related topics. Oracle is known as one of the most robust multi-platform relational databases available.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old April 16th, 2008, 12:10 AM
dwkelley86 dwkelley86 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2008
Posts: 9 dwkelley86 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 16 m 13 sec
Reputation Power: 0
For Loop using 'i'
All right...
I'm trying to develop a for-loop to basically do as follows:

for i in 1..4 loop
select value into variablename[i] from table;
end loop;

That's the basic gyst of it; a lot like you would in a language like ActionScript or PHP and whatnot...
Is there a way to accomplish this in SQL?
Reply With Quote
  #2  
Old April 16th, 2008, 03:18 AM
MrFujin's Avatar
MrFujin MrFujin is offline
Lord of the Dance
Dev Shed Beginner (1000 - 1499 posts)
  
Join Date: Oct 2003
Posts: 1,210 MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 5 h 21 m 50 sec
Reputation Power: 316
you cant do that in sql. To create that using a database you would have to use stored procedure.
Reply With Quote
  #3  
Old April 16th, 2008, 07:57 AM
LKBrwn_DBA LKBrwn_DBA is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2006
Posts: 294 LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 9
Talking
You actually would need to code it in PL/SQL, something like this:

Code:
Declare
  I Pls_Integer;
  Type Chr_Typ Is Table Of Varchar2(30)
       Indexed By Pls_Integer;
  Var Chr_Typ;
Begin
  For I In 1..10
  Loop
    Select The_Value Into Var(I) 
      From My_Table Where Id = I;
  End Loop;
End;
/
__________________
Reply With Quote
  #4  
Old April 16th, 2008, 09:00 AM
dwkelley86 dwkelley86 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2008
Posts: 9 dwkelley86 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 16 m 13 sec
Reputation Power: 0
Quote:
Originally Posted by LKBrwn_DBA
You actually would need to code it in PL/SQL, something like this:

Code:
Declare
  I Pls_Integer;
  Type Chr_Typ Is Table Of Varchar2(30)
       Indexed By Pls_Integer;
  Var Chr_Typ;
Begin
  For I In 1..10
  Loop
    Select The_Value Into Var(I) 
      From My_Table Where Id = I;
  End Loop;
End;
/


I'm a litlte confused by your code... where is My_Table?
Actually I'm having a lot of trouble understanding what most of the first half means.
Reply With Quote
  #5  
Old April 16th, 2008, 10:06 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
  
Join Date: Jun 2001
Location: Outside US
Posts: 7,711 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 53 m 59 sec
Reputation Power: 259
Executing a query in a loop is ugly and a performance killer, 99% of times this should be done with a join
Reply With Quote
  #6  
Old April 16th, 2008, 10:15 AM
LKBrwn_DBA LKBrwn_DBA is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2006
Posts: 294 LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level)LKBrwn_DBA User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 16 h 48 m 15 sec
Reputation Power: 9
Cool
Quote:
Originally Posted by dwkelley86
I'm a litlte confused by your code... where is My_Table?
Actually I'm having a lot of trouble understanding what most of the first half means.


Maybe you don't even understand what you are asking for.

Your request seems to be just an exercize in futility with no real purpose whatsoever...or do you have any actual requirements you wish to share with us?

PS: I substituted the Oracle "reserved words" you were using in your example (like 'value' and 'table') with 'the_value' and 'my_table'.
Reply With Quote
  #7  
Old April 16th, 2008, 11:29 AM
dwkelley86 dwkelley86 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2008
Posts: 9 dwkelley86 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 16 m 13 sec
Reputation Power: 0
Quote:
Originally Posted by LKBrwn_DBA
Maybe you don't even understand what you are asking for.

Your request seems to be just an exercize in futility with no real purpose whatsoever...or do you have any actual requirements you wish to share with us?

PS: I substituted the Oracle "reserved words" you were using in your example (like 'value' and 'table') with 'the_value' and 'my_table'.


Again, I can't hlep but take your first statement as an insult with that litlte smiley you put in.
In any case, I solved the problem and this is no longer an issue...
Reply With Quote
  #8  
Old April 16th, 2008, 12:28 PM
MrFujin's Avatar
MrFujin MrFujin is offline
Lord of the Dance
Dev Shed Beginner (1000 - 1499 posts)
  
Join Date: Oct 2003
Posts: 1,210 MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level)MrFujin User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 5 h 21 m 50 sec
Reputation Power: 316
Quote:
Originally Posted by dwkelley86
In any case, I solved the problem and this is no longer an issue...


with SQL or ? would be nice if you can post the solution, maybe other can benefit from it too.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Oracle Development > For Loop using 'i'

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway.