Archive for February, 2010

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,654 hits

RSS World News

  • Why would Trump invite Putin back for more? July 20, 2018
    There will be verdicts, votes -- and now Vlad? And it'll all end with a victory parade of sorts. This fall is going to be *lit* in Washington.
  • Trump is inviting Putin to Washington July 20, 2018
    President Donald Trump hopes to welcome Russian President Vladimir Putin in Washington this fall, White House press secretary Sarah Sanders said in a statement Thursday.
  • See intel chief stunned by Putin invite July 20, 2018
    Director of National Intelligence Dan Coats reacted to breaking news that Russian President Vladimir Putin is scheduled for a visit to Washington in the fall, according to the White House.
  • Jake Tapper: What the hell is going on? July 19, 2018
    CNN's Jake Tapper reacts to White House press secretary Sarah Sanders' statement that says President Trump disagrees with Putin's "sincere" proposal to interrogate Americans.
  • Reversals deepen questions about Trump and Putin's meeting July 19, 2018
    When President Donald Trump insisted he meet his Russian counterpart one-on-one, he cited the potential for leaks as rationale to bar aides from attending the session.
Advertisements