MERGE INTO t_uri_statistics t
USING
(SELECT COUNT(*) c FROM t_uri_statistics WHERE uri = '/ls/home' AND to_char(time,'yyyy-MM') = to_char(to_date('2009-08-03','yyyy-MM-dd'),'yyyy-MM') AND platid = -1 AND mnc = '01') s
ON (s.c <> 0)
WHEN MATCHED THEN
UPDATE SET t.cnt = t.cnt+1 WHERE uri ='/ls/home' AND time = to_date(substr('2009-08-01',1,7),'yyyy-MM') AND platid = -1 AND mnc = '01'
WHEN NOT MATCHED THEN
INSERT VALUES(SEQ_URI_STATISTIC_ID.NEXTVAL,'/ls/home',1,to_date(substr('2009-08-01',1,7),'yyyy-MM'),-1,'01')
这条MERGE语句 添上值后 可以正确的插入数据库
MERGE INTO t_uri_statistics t
USING (SELECT COUNT(*) c FROM t_uri_statistics WHERE uri =? AND to_char(time,'yyyy-MM') = to_char(to_date(?,'yyyy-MM-dd'),'yyyy-MM') AND platid = ? AND mnc =?) s
ON (s.c <> 0)
WHEN MATCHED THEN
UPDATE SET t.cnt = t.cnt+1 WHERE uri =? AND time = to_date(substr(?,1,7),'yyyy-MM') AND platid = ? AND mnc = ?
WHEN NOT MATCHED THEN
INSERT VALUES(SEQ_URI_STATISTIC_ID.NEXTVAL,?,?,to_date(substr(?,1,7),'yyyy-MM'),?,?)
把参数换成问号就不好用了憋了3个小时没憋出来~~` 请高人指点一下。。
SQL> select * from test; AAA
----------
1
2SQL> select * from test2; AAA
----------
1SQL> var v1 number
SQL> exec :v1:=999PL/SQL 过程已成功完成。SQL> merge into test2
2 using test on (test.aaa=test2.aaa)
3 when not matched then
4 insert values(:v1);1 行已合并。SQL> select * from test2; AAA
----------
1
999SQL>
比如你在sqlplus下执行了一遍,对t_uri_statistics的数据已经变更,而不commit;
那么你的程序去做的时候,这个merge就要一直等你sqlplus下的commit或者rollback,事务不提交则一直等下去。