%rowtype in a cursor [message #803] |
Tue, 05 March 2002 05:49 |
Jenn
Messages: 6 Registered: November 2001
|
Junior Member |
|
|
Hi,
I am trying to do the following, and the procedure runs clean, but nothing is updated.
Here is the cursor:
declare
v_oldbadge badgeassign%rowtype;
v_newbadge badgeassign%rowtype;
cursor badgeassign_cursor is
select * from badgeassign;
begin
OPEN badgeassign_cursor;
LOOP
FETCH badgeassign_cursor INTO v_newbadge;
EXIT WHEN badgeassign_cursor%NOTFOUND;
IF v_newbadge.personid = v_oldbadge.personid and v_newbadge.badgeassignid = v_oldbadge.badgeassignid
THEN
UPDATE badgeassign
set badgeassign.effectivedtm = v_oldbadge.effectivedtm
where badgeassign.badgeassignid = v_newbadge.badgeassignid;
DELETE from badgeassign where badgeassign.badgeassignid = v_oldbadge.badgeassignid;
END IF;
v_oldbadge := v_newbadge;
END LOOP;
CLOSE badgeassign_cursor;
end;
/
It appears as if I never enter the IF statement.
Any suggestions?
Jenn
|
|
|
Re: %rowtype in a cursor [message #806 is a reply to message #803] |
Tue, 05 March 2002 06:13 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
v_oldbadge has not been been assigned a value for the first time loop is executed (i.e. for first row of the cursor).
and from next row onwoards,the statement "v_oldbadge := v_newbadge;" is executing for each row.i do not think ever the if statement will evaluate to TRUE.
change ur logic.
by the way, what u want do ? what is ur idea behind the logic ?
|
|
|
Re: %rowtype in a cursor [message #1182 is a reply to message #803] |
Tue, 09 April 2002 05:52 |
DW
Messages: 2 Registered: November 2001
|
Junior Member |
|
|
All you are doing is randomly selecting all records from the badgeassign table. You either need to do an order by or some other join criteria. Not sure what you are attempting to do with this procedure. My guess is that if you order by person Id on the badgeassign table you might get the desired results.
|
|
|