日期:2014-05-17  浏览次数:20893 次

这段plsql语句为什么不对?
我想先做一个判断,列表里有没有符合条件的记录,如果有则删除。
然后再插入一条记录~
但是我这么写,它说我是无效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.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')



------解决方案--------------------
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.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;

------解决方案--------------------
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.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

------解决方案--------------------
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);