Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question about iterating through a collection

 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a PL/SQL stored procedure and I have an inner loop inside of an outer loop. Suppose there is a certain iteration of the outer loop where outer loop's number_of_sales does not match any of the inner loop's number_of_sales. How would I create an if condition where the outer loop's number_of_sales went through all of the inner loop's number_of_sales, but there was no match?

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Honestly I have no clue what you are trying to do here. Although it's only 13 lines, it seems to be complex stuff as you have 2 for loops and 2 if statements.

Secondly it seems weird that you have tables and trying to loop through them instead of writing some query with a join condition. And because it seems you need to do something when no match is found, you'll require a left outer join
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can almost certainly do this with a SQL query, probably using an outer join as Roel says. Stop trying to describe how you think the computer should match the records inside the query processor. Instead describe the business logic you want your query to implement. SQL is a declarative query language, so you need to describe what your query should do, not how the query engine should do it.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:I have a PL/SQL stored procedure and I have an inner loop inside of an outer loop. Suppose there is a certain iteration of the outer loop where outer loop's number_of_sales does not match any of the inner loop's number_of_sales. How would I create an if condition where the outer loop's number_of_sales went through all of the inner loop's number_of_sales, but there was no match?



1) Although INNER and OUTER used to be great names, the SQL standard unfortunately uses those as keywords now. It is therefore safer and less confusing to not use them as variable names.
2) As chris webster pointed out, you are trying to tell the RDBMS how to do its job. Especially with Oracle, its better to let the DB do it. Further more, by creating an implicit CURSOR in your code, even if your logic mimics what the optimizer decides to do, it is inherently slower, as the PL/SQL engine needs to be invoked for each record.

What your doing here is a simple join inside an UPDATE statement or two (unknown, because "do something" does not explain which table or which column.)
Please say which RDBMS you are using (Oracle or PostgreSQL) and what "do something" means.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic