Posts Tagged ‘sql rowcount

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!




Blog Stats

  • 36,255 hits

RSS World News

  • Helicopter that dropped grenades on Venezuela court found June 29, 2017
    Venezuelan President Nicolas Maduro has condemned what he called an "armed terrorist attack," which saw gunfire and grenades launched from a helicopter over the capital's Supreme Court building.
  • Expert: 'Democracy has been suspended' in Venezuela June 29, 2017
    CNN's Jim Sciutto speaks with Phil Gunson, a senior analyst for International Crisis Group, about human rights abuses in Venezuela.
  • Soldier shoots protester dead at point-blank range June 23, 2017
    His name was David José Vallenilla. At 22, he became the latest of at least 75 fatalities tied to civil unrest in the streets of Venezuela, a country mired in economic crisis and political instability.
  • Pence: Venezuela is a cautionary tale June 15, 2017
    Vice President Mike Pence cited the upheaval in Venezuela as a cautionary tale in an address to leaders of Central American nations on Thursday, saying authoritarianism is causing "untold suffering" in the South American nation.
  • Venezuelans flee to the US in search of better lives June 11, 2017
    The decision to come to the United States wasn't an easy one. Carolina, a 33-year-old mother of three, said it was perhaps one of the toughest decisions of her life -- one taken after many sleepless nights in her Barquisimeto, Venezuela, apartment.