Re: Running Insert Within Loop?

BTW I found this at asktom.oracle.com to be of good info in explaining this...

http://asktom.oracle.com/pls/ask/f?p=4950:8:634903::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1164655862293,%7Btemp%7D%20and%20%7Btables%7D

billiauk_at_yahoo.co.uk (Adrian Billington) wrote in message news:<dee17a9f.0203270037.5618b342_at_posting.google.com>...
> I've supplied two solutions - PL/SQL and SQL. The PL/SQL solution
> "tidies" up your code and the SQL solution is what you should be
> doing. If you can do it in one SQL statement without resorting to
> procedural logic, then you should do so.
>
> I don't know much about SQLServer but I do know that SQLServer guys
> use "temporary tables" a lot as storage mechanisms for intermediate
> results. We don't have anything like this in Oracle - there are
> discussions about this on asktom.oracle.com. The reason I mention it
> is because the code you posted has what looks to be "working tables".
>
> Anyway, in the PL/SQL I've taken your explicit cursor and manual loop
> and turned it into an implicit "CURSOR FOR LOOP". We use these when we
> want to cycle through a cursor row by row. It handles all of the
> opening and closing of the cursor and will exit when there are no rows
> to fetch. It also provides you with a record based on the cursor - so
> this need not be declared either. You can also use these CURSOR FOR
> LOOPS with a cursor SQL declared in the DECLARE section. To reference
> this - FOR myrec IN mycursor LOOP (where mycursor is the SQL cursor
> you declared previously).
>
> Hope this helps. But you should really try to do this in SQL.
>
> Regards
>
> Adrian
>
> PL/SQL
> -------------------------------------------
> BEGIN
> FOR st_rec IN (SELECT state
> FROM master
> WHERE operation_type in (1,3)
> ORDER BY state) LOOP
>
> INSERT INTO tmptbl_table1a_totals
> SELECT state1
> , COUNT(state1)
> , SUM(prod1)
> , COUNT(state2)
> , SUM(prod2)
> FROM tmptbl_table1a_year1
> , tmptbl_table1a_year2
> WHERE state1 = state2
> AND state1 = st_rec.state
> GROUP BY state1;
>
> END LOOP;
> END;
> /
>
> SQL
> ---------------------------------
> INSERT INTO tmptbl_table1a_totals
> SELECT a.state1
> , COUNT(a.state1)
> , SUM(a.prod1)
> , COUNT(b.state2)
> , SUM(b.prod2)
> FROM tmptbl_table1a_year1 a
> , tmptbl_table1a_year2 b
> , master c
> WHERE a.state1 = b.state2
> AND b.state2 = c.state
> AND c.operation_type in (1,3)
> GROUP BY a.state1
> /
Received on Wed Mar 27 2002 - 17:28:43 CET