判断表在oracle数据库中是否存在?
declare v_count int;
begin
select count(*) into v_count from tab where Tname = 'tongji_tmp ' and tabType = 'table ';
if v_count > 0 then --表存在则做Drop操作
drop table tongji_tmp;
end if;
create table tongji_tmp as select DptId, sum as pCount, 0 as fMarriage,0 as sMarriage,0 as oneChild, 0 as twoChild, 0 as oneBoy, 0 as oneGile, 0 as twoBoy, 0 as twoGile from Dptid_tmp;
select * from tongji_tmp;
end;
本人现在刚学oracle,上面的语句执行后报:出现符号“Drop”在需要下列之一时:
下面是begin case declare之类的。
也就if 语句块中不能执行,请高手帮个忙。
公司也是首次使用oracle,同事也都不清楚,在网上也没能搜到,可能是这个问题除了我别人都会的原因吧。
------解决方案--------------------DECLARE
BEGIN
FOR T IN (SELECT 'TRUNCATE TABLE '||TABLE_NAME TRUN_TABLE, 'DROP TABLE '||TABLE_NAME DROP_TABLE
FROM USER_TABLES WHERE TABLE_NAME IN( '大写表名1 ', '大写表名2 ')) LOOP
EXECUTE IMMEDIATE T.TRUN_TABLE;
EXECUTE IMMEDIATE T.DROP_TABLE;
END LOOP;
END;
/
------解决方案--------------------在匿名块中ddl语句不能直接执行,用动态sql:
....
if v_count > 0 then --表存在则做Drop操作
execute immediate 'drop table tongji_tmp ';
end if;
....
后面的create语句也像这样写。