You Asked
Hi Tom,
consider this:
create table test (
id number(10),
username varchar2(100),
reason varchar2(100),
timestamp_ number(10),
cnt number(10),
CONSTRAINT test_pk primary key (id)
);
insert into test(id,username,reason,timestamp_,cnt) values (1,'aca','bad pass.',500,0);
insert into test(id,username,reason,timestamp_,cnt) values (2,'beca','bad pass.',1000,0);
insert into test(id,username,reason,timestamp_,cnt) values (3,'aca','bad pass.',1010,1);
insert into test(id,username,reason,timestamp_,cnt) values (4,'aca','unknown',1020,0);
Can I do this pseudo code in one sql statement:
select max(id) into l_id from test where username=:username and timestamp>:timestamp-:fixed_delay;
--if found update else insert
if (l_id is not null) then update test set cnt=cnt+1 where id=l_id
else
insert into test (id,username,reason,timestamp_,cnt) values (:id,:username,:reason,:timestamp_,0)
end if
if input data is :
:username = 'aca',:reason ='bad pass.',:timestamp_=1030,:fixed_delay =60
it should update record id=3,
:username = 'aca',:reason ='bad pass.',:timestamp_=1030,:fixed_delay =600
it should update record id=3,
:username = 'aca',:reason ='bad pass.',:timestamp_=1030,:fixed_delay =10 it should insert new record.
so, if there is a record with same username and reason in last <fixed_delay> seconds then update last record else insert new_one. We can not relay on fact that there will be just one record for update in given period. So we have to be exact and update only last record that match criteria. Timestamps in real examples are unix timestamp, in this example I used smaller numbers (500-1020) for clarity.
It is easy to write stored procedure for this but is there a way to use merge statement.
and Tom said...
well, it should NEVER be three sql statements. It should have just been:
update
if (sql%rowcount = 0)
then
insert
end if;
I hate to see people "look to see if there is data to update, if so, update it - else insert"
It should always be "let the update see if there is data to update, if not, insert it" at most.
that and the logic just doesn't work in a multi-user environment (that max(id) junk, ugh - man oh man). Also, :id in the insert would be NULL - no logic for that (assume you must have meant to use sequence.nextval)
Your record id=3, I think you meant id=4 given the above data:
p_ > :timestamp_-:fixed_delay ) t2
6 on (t2.id = test.id)
7 when matched then update set cnt = cnt+1
8 when not matched then insert(id,username,reason,timestamp_,cnt)
9 values(seq.nextval,:username,:reason,:timestamp_,0);
1 row merged.
ops$tkyte@ORA10GR2> select * from test;
ID USERNAME REASON TIMESTAMP_ CNT
---------- -------------------- -------------------- ---------- ----------
1 aca bad pass. 500 0
2 beca bad pass. 1000 0
3 aca bad pass. 1010 1
4 aca unknown 1020 1
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :username := 'aca';:reason :='bad pass.';:timestamp_:=1030;:fixed_delay :=600 ;
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> merge into test
2 using ( select max(id) id
3 from test
4 where username = :username
5 and timestamp_ > :timestamp_-:fixed_delay ) t2
6 on (t2.id = test.id)
7 when matched then update set cnt = cnt+1
8 when not matched then insert(id,username,reason,timestamp_,cnt)
9 values(seq.nextval,:username,:reason,:timestamp_,0);
1 row merged.
ops$tkyte@ORA10GR2> select * from test;
ID USERNAME REASON TIMESTAMP_ CNT
---------- -------------------- -------------------- ---------- ----------
1 aca bad pass. 500 0
2 beca bad pass. 1000 0
3 aca bad pass. 1010 1
4 aca unknown 1020 2
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :username := 'aca';:reason :='bad pass.';:timestamp_:=1030;:fixed_delay :=10 ;
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> merge into test
2 using ( select max(id) id
3 from test
4 where username = :username
5 and timestamp_ > :timestamp_-:fixed_delay ) t2
6 on (t2.id = test.id)
7 when matched then update set cnt = cnt+1
8 when not matched then insert(id,username,reason,timestamp_,cnt)
9 values(seq.nextval,:username,:reason,:timestamp_,0);
1 row merged.
ops$tkyte@ORA10GR2> select * from test;
ID USERNAME REASON TIMESTAMP_ CNT
---------- -------------------- -------------------- ---------- ----------
1 aca bad pass. 500 0
2 beca bad pass. 1000 0
3 aca bad pass. 1010 1
4 aca unknown 1020 2
12 aca bad pass. 1030 0
Rating
(17 ratings)
We're not taking comments currently, so please try again later if you want to add a comment.