Home » SQL & PL/SQL » SQL & PL/SQL » Semantics
Semantics [message #226866] Mon, 26 March 2007 16:03 Go to next message
tawright915
Messages: 4
Registered: March 2007
Junior Member
In my plsql script, when ever I do an update I'm incrementing my count 1 time: v_count := v_count + 1;

How ever someone tells me that I should use SQL%rowcount instead for a more accurate count: v_count := v_count + SQL%rowcount.

Are we talking semantics here? Don't they both do basically the same thing?

Thanks
Re: Semantics [message #226869 is a reply to message #226866] Mon, 26 March 2007 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It depends upon what v_count is counting; number of UPDATE statements issued or number of rows updated.
A single UPDATE statement can change 0 - N rows; where N = total row count in the table.
So it depends upon what you are trying to count.
Re: Semantics [message #226871 is a reply to message #226869] Mon, 26 March 2007 16:25 Go to previous messageGo to next message
tawright915
Messages: 4
Registered: March 2007
Junior Member
Good point:
FOR record in GetRecords LOOP
EXIT WHEN GetRecords%notfound;

--Update table here
update my_tbl
set row_stat = 01
where student_id = record.student_id
and student_number = record.student_number;
v_count := v_count + 1;
END LOOP;

I guess each time the update is made I want a count.....so basically isn't the rowcount a count of all records retrieved by the fetch?

What's the difference?
Re: Semantics [message #226874 is a reply to message #226866] Mon, 26 March 2007 16:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I guess each time the update is made I want a count
Of WHAT do you want the count? (count of UPDATE statements executed or sum of rows changed by all UPDATE statements)

>so basically isn't the rowcount a count of all records retrieved by the fetch?NO, IMO. If the WHERE on the UPDATE is NOT satisfied; ZERO rows would be updated.

It does NOT have to be the case that "count of all records retrieved by the fetch" equals SUM(rowcount) from UPDATE.


>What's the difference?
Between what & what?
Re: Semantics [message #226876 is a reply to message #226874] Mon, 26 March 2007 16:53 Go to previous messageGo to next message
tawright915
Messages: 4
Registered: March 2007
Junior Member
anacedent wrote on Mon, 26 March 2007 16:38
>I guess each time the update is made I want a count
Of WHAT do you want the count? (count of UPDATE statements executed or sum of rows changed by all UPDATE statements)

A count of updates.


>so basically isn't the rowcount a count of all records retrieved by the fetch?NO, IMO. If the WHERE on the UPDATE is NOT satisfied; ZERO rows would be updated.

okay...so the difference between a regular COUNT = COUNT + 1 is that the count will be incremented not matter if the WHERE clause is satisfied, where ROWCOUNT will only be incremented if the WHERE clause is satisfied.


It does NOT have to be the case that "count of all records retrieved by the fetch" equals SUM(rowcount) from UPDATE.


>What's the difference?
Between what & what?
A regular count and the rowcount.



Re: Semantics [message #226879 is a reply to message #226866] Mon, 26 March 2007 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>>Of WHAT do you want the count? (count of UPDATE statements executed or sum of rows changed by all UPDATE statements)
>A count of updates.
Which of the 2 choices in line #1 meets your criteria in line #2?

>ROWCOUNT will only be incremented
ROWCOUNT is NOT "incremented".
SQL%ROWCOUNT is set to the number of records changed by the preceding DML (INSERT/UPDATE/DELETE) statement; which could be between 0 and N inclusive; where N is total number of rows in table.
Re: Semantics [message #226880 is a reply to message #226879] Mon, 26 March 2007 17:16 Go to previous messageGo to next message
tawright915
Messages: 4
Registered: March 2007
Junior Member
Dude...or dudet
I think your reading into this way too much......
The count is incremented in the previous script reguardless of the UPDATE statement. So picking whether it's option one or two is moo..
"count of UPDATE statements executed or sum of rows changed by all UPDATE statements"
But if it will allow you to sleep better at night...then a count of UPDATE statements...since that is currently what it is doing.

So basically the question is this......what is the difference between a counter of some sort.....and the rowcount.

It's simple. If you are saying that the rowcount will not work reguardless of the satifaction of the WHERE clause then, what are you saying and what is it used for....and since a counter will count regaurdless of the WHERE clause then what will work if I want a count of all updates?

Don't break it down to GREEK.

What is the difference between a counter of some sort and using rowcount?

Thanks
Re: Semantics [message #226884 is a reply to message #226880] Mon, 26 March 2007 18:09 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
tawright915 wrote on Mon, 26 March 2007 23:16
Dude...or dudet
I think your reading into this way too much......
The count is incremented in the previous script reguardless of the UPDATE statement. So picking whether it's option one or two is moo..

Moo?

tawright915 wrote on Mon, 26 March 2007 23:16
So basically the question is this......what is the difference between a counter of some sort.....and the rowcount.


The counter increments by 1 regardless of whether zero rows or a thousand rows were actually updated. I think what you might need is

v_count := v_count + SQL%ROWCOUNT;

tawright915 wrote on Mon, 26 March 2007 23:16
Don't break it down to GREEK.

Greek??

I'm still not sure what any of this is supposed to have to do with semantics, unless theere is some hidden message in the Moo and Greek references that I'm just not getting.

[Updated on: Mon, 26 March 2007 18:09]

Report message to a moderator

Previous Topic: Field length..
Next Topic: table design idea
Goto Forum:
  


Current Time: Sun Dec 04 00:08:41 CST 2016

Total time taken to generate the page: 0.13554 seconds