db2存储过程

删表时判断是否存在的存储过程

CREATE procedure EXISTSTVF(FLAG VARCHAR(1),TNAME VARCHAR(50))
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
EXTERNAL ACTION
begin atomic
--该函数用于查询表、视图、函数是否存在
--FLAG='T'表示查询表是否存在，FLAG='V'表示查询视图是否存在，FLAG='F'表示查询函数是否存在
declare Fint int default 0;
declare x varchar(100);
if(Flag='T') then
set Fint = (select count(*) from sysibm.systables where name = upper(TName));
elseif(Flag='V') then
set Fint = (select count(*) from sysibm.sysviews where name = upper(TName));
else
set Fint = (select count(*) from sysibm.sysfunctions where name = upper(TName));
end if;

if(Flag='T' and Fint = 1) then
set x = 'drop table '||TNAME;
execute immediate x;
elseif(Flag='V' and Fint = 1) then
set x = 'drop view '||TNAME;
execute immediate x;
elseif(Flag='F' and Fint = 1) then
set x = 'drop function '||TNAME;
execute immediate x;
end if;
end


附件中还有几个例子