SQL Rowcount

Semuanya berawal minggu lalu. Karena lama ga pake jurus yang satu ini jadi harus digugel ulang ( dari kata kerja “gugel” ditambah awalan “di-” ).

So, kasusnya waktu itu adalah mengetahui jumlah record yang terupdate di database,
pada saat dilakukan UPDATE statement dari Oracle forms. Cara yang sepintas terpikir adalah melakukan select count dengan kondisi / where yang ada. Tidak efisien bukan? tentu saja. Bisa-bisa diomelin om DBA kalo mesin tiba2 ngadat.

Begini kurang lebih cara pakai jurusnya :

nmbrowsaffected number := 0;
update xxx
set col_a = ...
where ...;

nmbrowsaffected := sql%rowcount;

if nmbrowsaffected = 0 then
message('No effect bro..');
end if;

Cukup mudah bukan.. saya saja bisa, apalagi anda… sebagai catatan, sql rowcount ini hanya menghitung open dml/syntax yang terakhir dilakukan. Jadi apabila ada update sebelum update, yang terhitung hanya jumlah record terupdate yang terakhir.

Selain itu, untuk DML lainnya kurang lebih perlakuannya begini :

Statement %ROWCOUNT value
FETCH Number of rows returned by the fetched cursor, incremented 1 time for each successful fetch.
SELECT INTO 1, even if TOO_MANY_ROWS is raised
UPDATE Number of rows effected
DELETE Number of rows effected
INSERT Number of rows effected

Hanya harap diingat.
ada pantangannya dalam melakukan jurus ini…
yaitu pengguna jurus wajib menggunakan komputer…… waspadalah!


0 Responses to “SQL Rowcount”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog Stats

  • 36,380 hits

RSS World News

  • After ISIS, 'Mosul can never be the same' October 17, 2017
    A few days ago, Mahmoud Saeed boarded a plane for a trip that is sure to be filled with trepidation.
  • What's next for ISIS after the fall of Raqqa? October 18, 2017
    US-backed forces in Raqqa said Tuesday that major military operations against ISIS had ended as they worked to clear out the remnants of sleeper cells and mines in the ruins of the Syrian city.
  • Video shows Raqqa execution spot October 18, 2017
    This exclusive drone footage shows empty streets and destroyed buildings in Raqqa, a city on the brink of being liberated from ISIS.
  • ISIS arrests: How many are from travel ban countries? October 17, 2017
    They called it the "US Project." Their goal, allegedly, was to carry out an ISIS terror attack on New York during Ramadan that would "show them what they hadn't seen in 2001."
  • US appeals for calm as allies clash in Iraq October 17, 2017
    The United States has made an urgent call for calm in northern Iraq as Iraqi forces and Kurdish fighters -- two of Washington's key allies in the region -- clash over disputed territory.

%d bloggers like this: