您当前的位置: 我想先做一个判断，列表里有没有符合条件的记录，如果有则删除。
然后再插入一条记录~
但是我这么写，它说我是无效sql语句，大家帮我看看是哪写错了~~~~
- SQL code
IF EXISTS(select * from TB_FILE_ELESIGN where FD_MSEQ='201111111622' and FD_SEGMENT='2')
delete from TB_FILE_ELESIGN where FD_MSEQ='201111111622' and FD_SEGMENT='2';
insert into TB_FILE_ELESIGN( FD_SEQ,FD_MSEQ, FD_NAME, FD_URL,FD_UPLOADTIME,FD_UPLOADERNO, FD_UPLOADERNAME,FD_FILETYPENAME,FD_ISDELETE,FD_SEGMENT)
values(FILEUP_SEQ.nextVal,'201111111622','新建 Microsoft Word 文档.doc','http://172.28.18.99MeterPictureUploadFile 01003004001dfd3ca79-77cb-4d6b-af8e-df58c292e145.doc',to_date('2011-11-16 09:18:41','yyyy-mm-dd HH24:MI:SS'),'900016','陈赛玉','doc','0','2')
------解决方案--------------------
Oracle的语法要用pl/sql才行.
- SQL code
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT (*)
INTO v_count
FROM tb_file_elesign
WHERE fd_mseq = '201111111622' AND fd_segment = '2';
IF v_count > 0
THEN
DELETE FROM tb_file_elesign
WHERE fd_mseq = '201111111622' AND fd_segment = '2';
END IF;
INSERT INTO tb_file_elesign
(fd_seq, fd_mseq,
fd_name,
fd_url,
fd_uploadtime,
fd_uploaderno, fd_uploadername, fd_filetypename, fd_isdelete,
fd_segment
)
VALUES (fileup_seq.NEXTVAL, '201111111622',
'新建 Microsoft Word 文档.doc',
'http://172.28.18.99MeterPictureUploadFile 01003004001dfd3ca79-77cb-4d6b-af8e-df58c292e145.doc',
TO_DATE ('2011-11-16 09:18:41', 'yyyy-mm-dd HH24:MI:SS'),
'900016', '陈赛玉', 'doc', '0',
'2'
);
COMMIT;
END;
------解决方案--------------------
- SQL code
declare l_cnt int;
select count(1) into l_cnt from TB_FILE_ELESIGN where FD_MSEQ='201111111622' and FD_SEGMENT='2';
IF l_cnt >0 then
delete from TB_FILE_ELESIGN where FD_MSEQ='201111111622' and FD_SEGMENT='2';
insert into TB_FILE_ELESIGN( FD_SEQ,FD_MSEQ, FD_NAME, FD_URL,FD_UPLOADTIME,FD_UPLOADERNO, FD_UPLOADERNAME,FD_FILETYPENAME,FD_ISDELETE,FD_SEGMENT)
values(FILEUP_SEQ.nextVal,'201111111622','新建 Microsoft Word 文档.doc','http://172.28.18.99MeterPictureUploadFile 01003004001dfd3ca79-77cb-4d6b-af8e-df58c292e145.doc',to_date('2011-11-16 09:18:41','yyyy-mm-dd HH24:MI:SS'),'900016','陈赛玉','doc','0','2');
end if
------解决方案--------------------
1、楼主的语句缺少then和end if两个关键词
2、insert语句后面少了分号
3、这个需求可以用merge，一句SQL就能实现。
------解决方案--------------------
- SQL code
http://blog.chinaunix.net/space.php?uid=16981447&do=blog&cuid=430716
------解决方案--------------------
可以用Merge into
- SQL code
/*涉及到两个表关联的例子*/ merge into fzq1 aa --fzq1表是需要更新的表 using fzq bb -- 关联表 on (aa.id=bb.id) --关联条件 when matched then --匹配关联条件，作更新处理 update set aa.chengji=bb.chengji+1, aa.name=bb.name --此处只是说明可以同时更新多个字段。 when not matched then --不匹配关联条件，作插入处理。如果只是作更新，下面的语句可以省略。 insert values( bb.id, bb.name, bb.sex,bb.kecheng,bb.chengji);