Posts Tagged ‘record terupdate

15
Feb
10

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 :

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

nmbrowsaffected := sql%rowcount;

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

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!

Advertisements



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.