我想先做一个判断,列表里有没有符合条件的记录,如果有则删除。
然后再插入一条记录~
但是我这么写,它说我是无效sql语句,大家帮我看看是哪写错了~~~~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.99\MeterPicture\UploadFile\001003004001\dfd3ca79-77cb-4d6b-af8e-df58c292e145.doc',to_date('2011-11-16 09:18:41','yyyy-mm-dd HH24:MI:SS'),'900016','陈赛玉','doc','0','2')
然后再插入一条记录~
但是我这么写,它说我是无效sql语句,大家帮我看看是哪写错了~~~~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.99\MeterPicture\UploadFile\001003004001\dfd3ca79-77cb-4d6b-af8e-df58c292e145.doc',to_date('2011-11-16 09:18:41','yyyy-mm-dd HH24:MI:SS'),'900016','陈赛玉','doc','0','2')
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.99\MeterPicture\UploadFile\001003004001\dfd3ca79-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;
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.99\MeterPicture\UploadFile\001003004001\dfd3ca79-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
2、insert语句后面少了分号
3、这个需求可以用merge,一句SQL就能实现。
请按PL/SQL存储过程的语法来改编吧。
去百度搜下,很多例子,merge是关现对同一个表同时进行insert,update,delete的操作。
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);
有的,新版本中是支持delete操作的,具体是哪个版本增加的功能我就不记得了,估计是10G