Newbie PL/SQL
Date: Sat, 10 Feb 2001 19:41:17 GMT
Message-ID: <xPgh6.78857$R5.3784299_at_news1.frmt1.sfba.home.com>
Howdy,
I'm trying to do my first PL/SQL, and I'm getting stuck. I have two tables
(MBM and BCP) that are identical except BCP doesn't have a constraint. I
use this table to SQLLDR a flat file.
In my process, I want to add a row to MBM if it doesn't exist and update if
it does exist. The BCP table contains about 13k rows, so as a test, I only
wanted to do 10. However, when I run the process, it appears the loop only happens 4 times. I confirmed this by displaying a field from my cursor after each loop.
Below is the code I wrote. Besides the error I'm getting, I believe the code is mighty inefficient and would appreciate any help I could get.
TIA
Patrick Hatcher
------START-------
cl scr
Set Serveroutput ON
DECLARE
CURSOR upc_cursor IS
Select *
from bcp_acceptance;
upc_record upc_cursor%Rowtype;
v_upc bcp_acceptance.upc_number%TYPE;
BEGIN
FOR upc_record in upc_cursor LOOP
FETCH upc_cursor INTO upc_record;
DBMS_OUTPUT.PUT_LINE ('UPC from cursor.....'||upc_record.upc_number);
EXIT WHEN upc_cursor%ROWCOUNT>10 or upc_cursor%NOTFOUND;
Begin
/* check if the record exists. If it does update it otherwise add it */ Select upc_number into v_upc from mbm_acceptance where upc_number = upc_record.upc_number;
if v_upc IS NOT NULL THEN
update mbm_acceptance
Set appl_id = upc_record.appl_id;
DBMS_OUTPUT.PUT_LINE ('Record updated.....');
else
insert into mbm_acceptance (Company_ID,UPC_number, appl_id)
values(upc_record.company_id,upc_record.upc_number,upc_record.appl_id);
DBMS_OUTPUT.PUT_LINE ('....Record added');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
insert into mbm_acceptance (Company_ID,UPC_number, appl_id)
values(upc_record.company_id,upc_record.upc_number,upc_record.appl_id);
DBMS_OUTPUT.PUT_LINE ('Exception Record
updated.....'||upc_record.upc_number);
END;
END LOOP;
END;
/
Received on Sat Feb 10 2001 - 20:41:17 CET