| Checking number of rows affected by DML ? ( MsSQL @@ROWCOUNT equiv ) [message #370482] |
Thu, 02 December 1999 13:42  |
JOHN
Messages: 182
Registered: April 1998
|
Senior Member |
|
|
Hi,
Being new to PL/SQL Im writing a routine which requires me checking the following code.
UPDATE TABLE table_name
SET CREATED_BY = user_id
, CREATED_WHEN = SYSDATE
WHERE RSN_fieldname = record_RSN
in Ms SQL I would use the @@ROWCOUNT command
IF @@ROWCOUNT = 0 THEN ... perform another function
The NO_DATA_FOUND seems to be the closest match in Oracle to this check. Is there a way to check the number of rows affected by a DML statement ?
Do you have to check the NO_DATA_FOUND in an EXCEPTION block ? If not how would you go about this to perform the following (assuming you didnt want the nice'n'tidy method of using EXCEPTION)...
sql statement
check
sql statement
sql statement
sql statement
check
sql statement
sql statement
check
Thanks in advance for any help you can give !!
John.
|
|
|
|
|
|
|
|
|
|
| Re: Checking number of rows affected by DML ? ( MsSQL @@ROWCOUNT equiv ) [message #370491 is a reply to message #370482] |
Fri, 03 December 1999 21:46  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
John,
One way would be by counting the rows after the statement executes, then testing the count as in:
DECLARE
v_count NUMBER(10);
v_today DATE;
BEGIN
v_today := SYSDATE;
UPDATE TABLE table_name
SET CREATED_BY = user_id,
CREATED_WHEN = SYSDATE
WHERE RSN_fieldname = record_RSN;
SELECT COUNT (*)
INTO v_count
FROM table_name
WHERE created_by = user_id
AND created_when = v_today
AND rsn_fieldname = record_rsn;
IF v_count = 0 THEN
perform another function
END IF;
END;
/
I've stored SYSDATE to a variable before performing the UPDATE and SELECT to avoid the possibility of the second changing between the time the update is performed and the time the count is taken, because then the dates would not match.
Hope this helps,
Paul
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
