Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql question
pl/sql question [message #35827] Wed, 17 October 2001 12:07 Go to next message
Merle
Messages: 1
Registered: October 2001
Junior Member
Description
A new column has been added to a table (slegsbii). I need to spin through each record and populate it with a value from another table (biih_new). Is it possible to then update the record of the cursor that i am currently on (slegsbii)?? below is my code. is there a better way???

/*populate new column "biih_Id" in slegbii table: spins through the slegbii table,
grabs 3 keys, does a lookup on the biih table to grab
the biih_id...and updates the slegsbii.biih_id field with biih.biih_Id*/
DECLARE
CURSOR test_cur
IS
SELECT invoice, biih_data_src_c, biih_rec_mtn_ts
FROM slegsbii
WHERE
AND biih_id IS NULL
FOR UPDATE NOWAIT;

hold_biih_id VARCHAR2 (22);
biidl_val test_cur%ROWTYPE;
BEGIN
OPEN test_cur;

LOOP
FETCH test_cur INTO biidl_val;
EXIT WHEN test_cur%NOTFOUND;
DBMS_OUTPUT.put_line (biidl_val.invoice);
DBMS_OUTPUT.put_line (biidl_val.biih_data_src_c);
DBMS_OUTPUT.put_line (biidl_val.biih_rec_mtn_ts);
DBMS_OUTPUT.put_line (' ');

BEGIN
SELECT biih_id
INTO hold_biih_id
FROM new_biih
WHERE (invoice = biidl_val.invoice
AND biih_data_src_c = biidl_val.biih_data_src_c
AND biih_rec_mtn_ts = biidl_val.biih_rec_mtn_ts)
AND BIIH_REC_STUS_C = 'A';

--if biih_id is not null, then update the slegsbii table with that value

IF hold_biih_id is not null then
UPDATE slegsbii set biih_Id = hold_biih_id
WHERE invoice = biidl_val.INVOICE and biih_data_src_c = biidl_val.biih_data_src_c
and biih_rec_mtn_ts = biidl_val.biih_rec_mtn_ts;
END IF

*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
'key not found '
|| biidl_val.invoice
|| biidl_val.biih_data_src_c
|| biidl_val.biih_rec_mtn_ts
);
--goto end_loop;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'error '
|| SQLCODE
|| ' '
|| biidl_val.invoice
|| biidl_val.biih_data_src_c
|| biidl_val.biih_rec_mtn_ts
);
--goto end_loop;
END;
END LOOP;

--<<end_loop>>

DBMS_OUTPUT.put_line ('end');
CLOSE test_cur;
END;

----------------------------------------------------------------------
Re: pl/sql question [message #35829 is a reply to message #35827] Wed, 17 October 2001 12:37 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I would recommend doing this in a single SQL statement instead of a cursor loop/variables/multiple updates/exception handling. Something like:

update slegsbii s
set biih_id =
(select biih_id
from new_biih n
where n.invoice = s.invoice
and n.biih_data_src_c = s.biih_data_src_c
and n.biih_rec_mtn_ts = s.biih_rec_mtn_ts
and n.biih_rec_stus_c = 'A'
and n.biih_id is not null)
and exists
(select 1
from new_biih n
where n.invoice = s.invoice
and n.biih_data_src_c = s.biih_data_src_c
and n.biih_rec_mtn_ts = s.biih_rec_mtn_ts
and n.biih_rec_stus_c = 'A'
and n.biih_id is not null);

----------------------------------------------------------------------
Previous Topic: Oracle Stored Procedure
Next Topic: Insufficient privileges !?
Goto Forum:
  


Current Time: Sat Jun 12 20:52:57 CDT 2021