SQL & PL/SQL

Hi –

My question is in regards to using for…loops with insert (and update statements) in general. The INSERT statement I have is exactly what I would use in straight SQL.

I am confused at the purpose of using LOOPS in this scenario (perhaps I shouldn’t) as in

FOR v_orderinfo in (Select …) LOOP
INSERT into temp
Values (v_orderinfo)

Versus doing what I have done in the procedure.

Also, how can I use the cursor c_ordlist with both the update and insert since I declared it as Select for Update…??

Any advice is much appreciated (as well as how to make this code better)
thanks


CREATE OR REPLACE PROCEDURE ord_up
(p_dt calendar-tbl.trans_date%TYPE)

IS
v_new_dt NUMBER ;

CURSOR c_ordlist IS
SELECT *
FROM jtest_whl_bu
WHERE order IN
(SELECT order FROM ordtemp)
FOR UPDATE OF orddt;

BEGIN

EXECUTE IMMEDIATE 'truncate table ordbackup’;

--Create a backup copy of data
INSERT INTO orbackup
SELECT * FROM ordertable;
COMMIT;


FOR v_ordinfo IN c_ordlist LOOP

--get the new desired date id
SELECT orddt
INTO v_new_dt
FROM calendar_tbl
WHERE orddt = p_dt;


UPDATE ordertable
SET orddt = v_new_da
WHERE CURRENT OF c_ordlist;

END LOOP;

COMMIT;

END;