Home » SQL & PL/SQL » SQL & PL/SQL » Outer inner loop (PL/SQL TOAD)
Outer inner loop [message #581649] Tue, 09 April 2013 11:20 Go to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
Can someone please help me get started with doing an inner and outer loop?
Sorry but I am unable to provide code because I don't know where to begin.
So I'll use crude pseudo code. What I want to do, in effect, is a cartesian join,
but I need to do it NOT as a join but as a nested loop.

I am using these three tables:

TABLE: CUSTOMERS
COLUMN: CUSTOMER_NAME
BILL
BOB
SUE

TABLE: PRODUCTS
COLUMN: PRODUCT_NAME
WIDGET
DOODAD

TABLE: CUSTOMERS_PRODUCTS (EMPTY TABLE)
COLUMN: CUSTOMER_NAME
COLUMN: PRODUCT_NAME


And here's the nested loop I'd like to do.
The results will be 6 ( 3 * 2) new records inserted into CUSTOMERS_PRODUCTS :


-- OUTER LOOP --

FOR...CUSTOMER...GET EACH CUSTOMER_NAME one-at-a-time...


-- INNER LOOP --

FOR...PRODUCTS...GET EACH PRODUCT_NAME one-at-a-time...

INSERT into CUSTOMERS_PRODUCTS (CUSTOMER_NAME, PRODUCT_NAME)
SELECT CUSTOMERS.CUSTOMER_NAME,PRODUCTS.PRODUCT_NAME from PRODUCTS;

END LOOP;

END LOOP;



Thx.



Re: Outer inner loop [message #581650 is a reply to message #581649] Tue, 09 April 2013 11:28 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
bws93222 wrote on Tue, 09 April 2013 18:20
What I want to do, in effect, is a cartesian join,

Why try to do something in PL/SQL when it can be done in SQL?

bws93222 wrote on Tue, 09 April 2013 18:20

but I need to do it NOT as a join but as a nested loop.

And you think that a join is never translated as a nested loop?


Regards,
Dariyoosh
Re: Outer inner loop [message #581651 is a reply to message #581650] Tue, 09 April 2013 11:30 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
I may not be using terms clearly but I would like the resulting code to be
in the format I described (using an inner and outer loop).
(One of the problems I am having is that all loop examples I have seen use
numerical counters--and I don't think they are applicable here.)

[Updated on: Tue, 09 April 2013 11:33]

Report message to a moderator

Re: Outer inner loop [message #581652 is a reply to message #581651] Tue, 09 April 2013 11:37 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
bws93222 wrote on Tue, 09 April 2013 18:30
I may not be using terms clearly but I would like the resulting code to be
in the format I described


Maybe I don't read it correctly but what I understand from your pseudocode
-- OUTER LOOP --

FOR...CUSTOMER...GET EACH CUSTOMER_NAME one-at-a-time...


-- INNER LOOP --

FOR...PRODUCTS...GET EACH PRODUCT_NAME one-at-a-time...

INSERT into CUSTOMERS_PRODUCTS (CUSTOMER_NAME, PRODUCT_NAME)
SELECT CUSTOMERS.CUSTOMER_NAME,PRODUCTS.PRODUCT_NAME from PRODUCTS;


For me this is a SQL Cartesian product

INSERT into CUSTOMERS_PRODUCTS (CUSTOMER_NAME, PRODUCT_NAME)
SELECT t1.customer_name, t2.product_name
FROM customers t1 CROSS JOIN products t2;



Regards,
Dariyoosh
Re: Outer inner loop [message #581653 is a reply to message #581649] Tue, 09 April 2013 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58565
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

Michel Cadot wrote on Sun, 10 February 2013 17:25
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

And FEEDBACK to those that help you thing you did not do but for one topic; do you think we owe you answers and help and it is not worth to thank us and tell us if it solve your problem?
...


Re: Outer inner loop [message #581655 is a reply to message #581652] Tue, 09 April 2013 12:07 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
yes, the end result is the same as a cartesian cross join but I don't want to use that format.
I need instead to explicitly use FOR or WHILE (or any other kind of) LOOPs

[NOTE: I was able to find an alternative solution to my problem--but if anyone
still wants to contribute an answer, feel free and I will note it for the future.]

Thx for your time!

[Updated on: Tue, 09 April 2013 12:20]

Report message to a moderator

Re: Outer inner loop [message #581658 is a reply to message #581655] Tue, 09 April 2013 12:30 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
bws93222 wrote on Tue, 09 April 2013 19:07
yes, the end result is the same as a cartesian cross join but I don't want to use that format.

Confused ?!?!

bws93222 wrote on Tue, 09 April 2013 19:07

I need instead to explicitly use FOR or WHILE (or any other kind of) LOOPs

One possible (PL/SQL solution just because you insist and I don't understand why) would be to define two nested tables at schema level one for product names and the other one for the customer names. Then inside your PL/SQL block you put all the customers names and the products names in their corresponding nested tables and finally you explore the elements in those tables as a nested loop, for example the extern loop for customers name and the inner loop for products name.

Take a look at oracle online doc: Nested tables


Regards,
Dariyoosh
Re: Outer inner loop [message #581661 is a reply to message #581658] Tue, 09 April 2013 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
row by row is SLOW by SLOW.
do not use PL/SQL when same results can be obtained by plain SQL.
Re: Outer inner loop [message #581662 is a reply to message #581661] Tue, 09 April 2013 12:40 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
BlackSwan wrote on Tue, 09 April 2013 19:37
row by row is SLOW by SLOW.
do not use PL/SQL when same results can be obtained by plain SQL.


That is precisely what I told him at my first comment, yet it seems that he doesn't care about that and I don't understand why? Shocked

Regards,
Dariyoosh
Re: Outer inner loop [message #581666 is a reply to message #581662] Tue, 09 April 2013 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
You can lead some folks to knowledge, but you can't make them think.
Re: Outer inner loop [message #581669 is a reply to message #581666] Tue, 09 April 2013 13:05 Go to previous messageGo to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
...well, there are some other checks that need to be done individually which I left out for the sake of simplicity.
For example, when inserting the new record, I need to check that a duplicate record doesn't already exist.
If it already exists, I want to skip it rather than generate a unique constraint error.
Re: Outer inner loop [message #581671 is a reply to message #581669] Tue, 09 April 2013 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
All of which will make the processing even slower.
LOG ERROR is an alternative
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#BGBDIGAH
Re: Outer inner loop [message #581672 is a reply to message #581671] Tue, 09 April 2013 13:14 Go to previous message
bws93222
Messages: 27
Registered: April 2009
Junior Member
It will only be run once and there aren't that many records involved so speed is not a high consideration.

[Updated on: Tue, 09 April 2013 13:16]

Report message to a moderator

Previous Topic: Selecting fields containing both text and characters
Next Topic: Generate command to Rename log file from ASM to non-ASM
Goto Forum:
  


Current Time: Fri Jul 25 11:21:39 CDT 2014

Total time taken to generate the page: 0.24843 seconds