Re: Running Insert Within Loop?
Date: 27 Mar 2002 00:37:33 -0800
Message-ID: <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
END LOOP;
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;
/
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 - 09:37:33 CET