elaine3839
02-26-2003, 06:45 PM
Hi,
In my PL/SQL code, I need to check the number of rows being updated by an update statement. How can I do that?
To be compliant with Sql Server code, the logic I am going to adopt is (I used different color to represent different level of the IF..ELSE statemnt):
UPDATE records where id = xxx
IF got error returned
THEN return -1
ELSE
check the number of rows being updated
IF NO ROW being updated
THEN
check if still get rows with id = xxx
IF not THEN return 1// no row with id =xxx exist so nothing to update
ELSE return 2 // rows with id=xxx were not updated
END IF
ELSE return 0 // rows are updated as they should be
END IF
END IF
In SQL, there is a global variable @@rowcount that shows the number of rows being affected by last statement. Since I can't find a counterpart in Oracle, how do I achieve this?
Thanks,
Elaine
In my PL/SQL code, I need to check the number of rows being updated by an update statement. How can I do that?
To be compliant with Sql Server code, the logic I am going to adopt is (I used different color to represent different level of the IF..ELSE statemnt):
UPDATE records where id = xxx
IF got error returned
THEN return -1
ELSE
check the number of rows being updated
IF NO ROW being updated
THEN
check if still get rows with id = xxx
IF not THEN return 1// no row with id =xxx exist so nothing to update
ELSE return 2 // rows with id=xxx were not updated
END IF
ELSE return 0 // rows are updated as they should be
END IF
END IF
In SQL, there is a global variable @@rowcount that shows the number of rows being affected by last statement. Since I can't find a counterpart in Oracle, how do I achieve this?
Thanks,
Elaine